Resource Busy Ora-00054

Use following query to find blockers

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

Use following query to get info of session holding lock.

set lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
 , 0, 'Not Blocking'
 , 1, 'Blocking'
 , 2, 'Global') status
, decode(v.locked_mode
 , 0, 'None'
 , 1, 'Null'
 , 2, 'Row-S (SS)'
 , 3, 'Row-X (SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sid
order by oracle_username
, session_id
/

Show which row is locked

select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 
    ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

Then select the row with that rowid...

select * from <table> where rowid=<rowid>;

List locks

column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id
, lock_type
, mode_held
, mode_requested
, blocking_others
, lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

RMAN Duplicate database

Using RMAN to copy database from one machine to another.

Node1-----SR or source database
Node2-----DT or destination database.

Backups are on SBT tapes and using EMC Networker Module for Oracle.

Initial requirements.

Oracle installation version must be same on both nodes and both nodes are having same OS version.

I am taking an example of refresh from PROD to QA environment in which most OS and DB versions are same.

Networker client is installed on both nodes and configured for backup

Networker settings for source database on networker server must have set remote access list so that the database backups can be accessed by destination node.(Very important)

RMAN catalog database is already configured and backups are done using catalog.

on Destination

Shutdown the destination database.

sql> shutdown immediate


Go to directory where datafiles ,redo , control files are located and delete them(take backup to a different location before deleting).

$ cd /oracle/oradata/DT/
$cp /oracle/oradata/DT/ *    /backup/DT/
$ rm -f   *

Do not delete initDT.ora file if its deleted then create one with appropriate parameters.

Edit initDT.ora file and update following parameters for conversion of paths when data files are copied.

DB_FILE_NAME_CONVERT=('/oracle/oradata/SR','/oracle/oradata/DT')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/aSR','/oracle/oradata/DT')

Backup source database to tapes (Not required if backup already exists on tapes and want to restore old backups)

on SOURCE database

Login as oracle or db owner UID.

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle     (If  tape pool is used)

rman>
rman>connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
run {
allocate  channel t1 type 'SBT_TAPE';
allocate  channel t2 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1, NSR_DATA_VOLUME_POOL=oracle)';
backup database plus archivelog;
release channel t1;
release channel t2;
}

 Once database backup is complete on production.

Now you can start restore on Destination as follows.

connect to NODE2 as oracle or oracle installation owner UID

set environment for destination database DT

$ sqlplus / as sysdba

SQL> startup nomount pfile=/oracle/dbs/initDT.ora                     (path to init param file created above)
SQL> exit

Here
target= source database (SR)
catalog = same database as used during source backup
auxillary = destination database (DT)


set environment for DT database and following parameters

$export NSR_SERVER=NWSERVER
$export NSR_CLIENT=NODE1------------------> (Very important this must be NODE1 only since backups are taken there)
$export NSR_KEEPALIVE_WAIT=10
$export NSR_DATA_VOLUME_POOL=oracle

$rman
rman> connect target sys/oracle@SR
rman>connect rcvcat rmanprod/rmanprod@rmanprod
rman>connect auxiliary /
run {
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
configure channel device type 'SBT_TAPE' parms  'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2)';
allocate auxiliary channel t1 type 'SBT_TAPE' debug=5 trace=2 parms 'ENV=(NSR_DEBUG_FILE=/tmp/nsr_channel1.log,NSR_DEBUG_LEVEL=2,NSR_DATA_VOLUME_POOL=oracle)';
debug on;
send 'NSR_ENV=(NSR_SERVER=NWSERVER,NSR_CLIENT=NODE1)';
set until time "to_date('2011-06-07:17:00:00','YYYY-MM-DD:hh24:mi:ss')";
duplicate target database to DT;
debug off;
release channel t1;
}

you can remove debug and also set untill if you dont need them.

Once duplicate is complete you can comment out the file conversion parameters added in init param file of DT.

Additional commands


Restart networker client service as ROOT user

#/sbin/service networker stop

#/sbin/service networker status

#ps -ef|grep nsr

#/sbin/service networker start




 Errors encountered.

Could not locate the LNM save file 'him8opgk_1_1' on server 'kap-us-297nw2'. (2:9:0)

Solution. Make sure you have the right tape volumes accessible for networker server.