Unregister database in RMAN catalog database without RMAN

Sometimes you remove a database and forgot to unregister the database in the RMAN catalog database.

This can happen in an environment where you clone databases quite often for testing purposes.
In this case you can't easily connect via RMAN to the catalog database and issue a unregister database command.

 But there is a solution out there which basically was the only solution in the beginning of RMAN catalog database:

1. First, go to the database you wish to unregister and find the DBID:

 SQL> select dbid,name from v$database;

DBID       NAME 
—————–     —————————— 
1122334455 ORCL 

2. Second, connect to the recovery catalog database as schema owner and get the DB_KEY of the database :

 SQL> select db_key,dbid,name from rc_database where dbid = 1122334455;

DB_KEY    DBID       NAME 
—————–    —————–     —————————— 
888888888 1122334455 ORCL

3. Thrird, You can use the PL_SQL Procedure dbms_rcvcat.unregisterdatabase(db_key,db_id) to unregister the database.
Please provide the db_key and db_id from the previous query. Connected as the RMAN catalog owner run:
SQL> execute dbms.rcvcat.unregisterdatabase(888888888 , 1122334455);
 PL/SQL procedure successfully completed.

 Your database is now removed from the catalog database.

Finding OS process ID for a killed session

If as database session gets killed, it's value of the PADDR column in V$SESSION cannot be matched with the ADDR from V$PROCESS to find the corresponding process ID ( given that you are using dedicated server). The stateobject of the session is moved to a different parent process so the memory address is change to reflect that. This means the original process is still around but cannot be found joining the V$SESSION and V$PROCESS because the sessions stateobject has been moved to a pseudo process and so the PADDR has been changed.
It is therefore no longer possible to identify the process that has been killed and terminate it at OS level. 

Under some circumstances if a session gets killed, the session is not getting cleaned up immediately but keeps showing up in V$SESSION. There can be several reasons for this: rollback operations etc.

Example for killing a session via sqlplus (issued as SYS ) :

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' ;

<SID> and <SERIAL#> in the above example are taken from the columns SID, SERAL# from the V$SESSION view.

Example killing a session with SID=28 and SERIAL=33455

ALTER SYSTEM KILL SESSION '28,33455' IMMEDIATE;

To find processes which have no matching PADDR in V$SESSION you can issue this query to find the corresponding OS process IDs:

select 
  * 
from 
  v$process 
where 
  addr not in (select paddr from v$session) 
  and addr not in (select paddr from v$shared_server) 
  and addr not in (select paddr from v$bgprocess) 
  and program!= 'PSEUDO';

Connection problems: inbound connection timed out (ORA-3136)

A couple of days ago I ran into error some messages in the alert_<SID>.log which read about like this:

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 11-JUL-2012 08:54:24
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192..168..018.13)(PORT=64811))
WARNING: inbound connection timed out (ORA-3136)

The application accessing the database is running on a Redhat Linux application server using a JDBC connection. The database also resides on a Linux box but the database site was not the limiting factor which you can see further down this Blog.
The application server was virtual and just had enough resource to run the application. 
The application opened several database connections at the same time ( within 1 minute )


This error usually comes from connection attempts which try to open a database connection and do not send the credentials in time. Usually the listener spawns a new server process and hands off the connection to the new server process. But if the listener does not get the connect credentials in time, it throws an error to avoid a DOS attack. This timeout, which could be set via the INBOUND_CONNECT_TIMEOUT sqlnet.ora parameter and the listener parameter INBOUND_CONNECT_TIMEOUT in the listener.ora file, has
a default value of 60 seconds if not set to a different value.

Usually one would think that is enough time for the client to send the username/password. 
Well, in fact that's true but in this case the client really had problems sending the username/password combination.

After some research it turned out the root cause of the problem is the JDBC driver using the java.security.SecureRandom class which in turn is using the /dev/random device under Linux.
The /dev/random device is using a pool or cache of bytes which the Linux Kernel gathers from various sources to produce a random sequence of bytes for applications which use cryptographic procedures.
Each read from /dev/random drains the pool with requested amount of bits/bytes.
If the pool is completely empty the program accessing the /dev/random device blocks until the requested number of bytes are available. 

One can check this by simply executing: "cat /dev/random " a couple of times. If you are getting blocking results you might face an issue when connecting to a oracle database.

Even though you are not using "advanced encryption" the connect credentials ( username, password ) are always sent encrypted to the database. Therefore the JDBC client is using encryption even though you are not using advanced encryption functionality.

There is another Linux device  /dev/urandom which is not blocking when the pool is empty. Instead of blocking this device reuses the bytes until the pool is filled up again.

Unfortunately the JDBC driver uses the SecureRandom Class of the java standard library which depends on /dev/random.
But fortunately you can change what Implementation the JDBC driver is using by putting the following line in your java program before opening the connection :

System.setProperty("java.security.egd", "file:///dev/urandom");

or you can use a java property setting when starting your java program:

-Djava.security.egd=file:///dev/urandom

 Here is a interesting link which explains the 2 workarounds above.

This makes the JDBC driver use the /dev/urandom device which is not blocking.

Alternatively you could set the listener or sqlnet.ora parameter INBOUND_CONNECT_TIMEOUT to a appropriate higher value for your systems.


Another solution could be approaching the problem from the Linux side:
There is a Linux daemon "rngd" which can help filling up the /dev/random device with entropy.
This daemon can be used to pull entropy from hardware accelerators or other devices and feed it into the entropy pool on which /dev/random relies.  
In our case we could just pull the data from the /dev/urandom device which is non-blocking. 

 Use the following command to run this service on /dev/urandom:

rngd -r /dev/urandom 

The process will start as daemon which means it will run in background.

Please note that all of this possible solutions will decrease the level of security !
For long secure term solutions please consider adding a hardware accelerator.

ORACLE: SQL execution statistics

Oracle provides several tools to see the internals of the SQL optimizer.

To see what's going on behind the scenes you had a couple of options in the past:

1. Using SQL Trace ( event 10046 )
The use of SQL Trace is probably the best option available because there is no way getting more detailed data about the execution of a SQL Statement. But this requires some preparation ( enable / disable the SQL Trace ) and analysis of the RAW Tracefile or using TKPROF to distill the information from the RAW File.

2. Using SQL*Plus "set autotrace"
This is a very easy to use method but it has some caveats regarding using type conversions

3. The use of V$SQL_WORKAREA
Additional information about extended execution plan statistics are available about the Row-Source operations of the SQL statement.

Now a new option is available if you are running Oracle 11g: The GATHER_PLAN_STATISTICS Hint

Like all other optimizer hints, this hint is placed as comment like /*+ GATHER_PLAN_STATISTICS */
There is another possibility for gathering the execution plan statistics using the init.ora parameter statistics_level=all which is set to 'typical' by default but not recommended to set to 'all' on a production system since there is some overhead involved.

Here is an example of how the hint is used:

SQL> select /*+ gather_plan_statistics */ * from t1 where id1 = 100 

Some Versions of oracle seem to be affected by a Bug and the resulting  numbers are incorrect. To avoid this possibility you can also set the instance/session  parameter "statistics_level" to ALL ( default is TYPICAL ).
At instance level the setting to ALL could cause some overhead to your database so  I would recommend setting it at session level, so your session only is affected.

alter session set statistics_level=all;

To lookup the information the DBMS_XPLAN Package is used

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

Yesterday -- The Backup Song

A couple of days ago I stumbled across this funny song:

Yesterday -- The Backup Song 


Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Suddenly,
There's not half the files there used to be,
And there's a milestone hanging over me
The system crashed so suddenly.

I pushed something wrong
What it was I could not say.
Now all my data's gone
and I long for yesterday-ay-ay-ay.

Yesterday,
The need for back-ups seemed so far away.
I knew my data was all here to stay,
Now I believe in yesterday.

ORACLE: Purge a cursor from the shared pool

Have you ever wanted to purge a specific SQL statement from the shared pool.
This comes in handy when you want to force a hard parse for a specific statement.


1. find the sql_id from the specific statement in the shared pool

select sql_id from v$sql where sql_text='sql text';

2. find the address + hash value of this particular statement:

SELECT address||','||hash_value, version_count  FROM v$sqlarea WHERE sql_id = 'sql id';

3. purge the statement from the shared pool using the above address+hash:

EXECUTE dbms_shared_pool.purge('address + hash','C',1);

A short example of the above steps, assumed you are looking for the sql statement:
select * from t1 where object_id=3000;

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

SQL_ID        
------------- 
79xnbb98drf3m 

Elapsed: 00:00:00.026

SQL> SELECT address||','||hash_value FROM v$sqlarea WHERE sql_id = '79xnbb98drf3m';

ADDRESS||','||HASH_VALUE                          
------------------------------------------------- 
27197058,1356576883                               

Elapsed: 00:00:00.003

SQL> EXECUTE dbms_shared_pool.purge('27197058,1356576883','C',1);

PL/SQL procedure successfully completed.

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

no rows selected