oracle restore spfile, controlfile and datafiles from RMAN

I am demonstrating a scenarion where I am  using ASM and have lost spfile, controlfile and datafiles. Now my job is to restore the database from RMAN backup.

This is little tricky but not impossible. Let me explain you how this will be done:

1. Get the DBID of the database from recovery catalog.
2. Create init<SID>.ora file
3. Connect to RMAN and set the DBID
4. Startup nomount
5. Restore spfile
6. Shutdown immediate
7. Update init<SID>.ora with spfile location
8. Startup nomount
9. Restore controlfile
10. startup mount
11. restore database
12. recover database
13. alter database open resetlogs;

Get the DBID of the database from recovery catalog.

SQL> select distinct DBID, NAME from RC_DATABASE_INCARNATION;
      DBID NAME
---------- --------
2410645064 RAC
1372201017 ORCL

Create init<SID>.ora file

$ cat initRAC1.ora
*.db_name=RAC

Connect to RMAN and set the DBID

[oracle@rac1 dbs]$ rman target / catalog catowner/******@rcat
Recovery Manager: Release 11.2.0
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> set dbid 2410645064;
executing command: SET DBID
database name is "RAC" and DBID is 2410645064
RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1861    ORCL     1372201017       PRIMARY          ORCL              
1       RAC      2410645064       PRIMARY          RAC                
1       RAC      2410645064       STANDBY          RAC_STBY          
RMAN> 

startup nomount

RMAN> startup nomount
Oracle instance started
Total System Global Area     238034944 bytes
Fixed Size                     2227136 bytes
Variable Size                180356160 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5120000 bytes
RMAN> 

Restore spfile

RMAN> restore spfile to '+DATA/RAC/spfileRAC.ora' from tag='TAG20140626T161003';
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/RAC/spfileRAC.ora
channel ORA_DISK_1: reading from backup piece +FRA/rac/autobackup/2014_06_26/s_851271003.263.851271009
channel ORA_DISK_1: piece handle=+FRA/rac/autobackup/2014_06_26/s_851271003.263.851271009 tag=TAG20140626T161003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 30-JUN-14
RMAN>

Shutdown immediate

RMAN> shutdown immediate
Oracle instance shut down
RMAN>

Update init<SID>.ora with spfile location

$cat initRAC1.ora
SPFILE='+DATA/RAC/spfileRAC.ora'

Startup nomount

RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area     793350144 bytes
Fixed Size                     2232552 bytes
Variable Size                394268440 bytes
Database Buffers             394264576 bytes
Redo Buffers                   2584576 bytes
RMAN>

Restore controlfile

RMAN> restore controlfile;
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece +FRA/rac/autobackup/2014_06_27/s_851356825.376.851356835
channel ORA_DISK_1: piece handle=+FRA/rac/autobackup/2014_06_27/s_851356825.376.851356835 tag=TAG20140627T160025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/rac/controlfile/current.262.851600129
Finished restore at 30-JUN-14
RMAN>

startup mount

RMAN> startup mount;
database is already started
database mounted
released channel: ORA_DISK_1
RMAN>

restore database

RMAN> restore database ;
Starting restore at 30-JUN-14
Starting implicit crosscheck backup at 30-JUN-14
allocated channel: ORA_DISK_1
Crosschecked 11 objects
Finished implicit crosscheck backup at 30-JUN-14
Starting implicit crosscheck copy at 30-JUN-14
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 30-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +fra/RAC/AUTOBACKUP/2014_06_27/n_851344744.494.851344751
File Name: +fra/RAC/AUTOBACKUP/2014_06_27/s_851356825.376.851356835
File Name: +fra/RAC/AUTOBACKUP/2014_06_26/s_851271003.263.851271009
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/rac/datafile/system.263.851343649
channel ORA_DISK_1: restoring datafile 00002 to +DATA/rac/datafile/sysaux.268.851343643
channel ORA_DISK_1: restoring datafile 00003 to +DATA/rac/datafile/undotbs1.277.851343653
channel ORA_DISK_1: restoring datafile 00004 to +DATA/rac/datafile/users.273.851343655
channel ORA_DISK_1: restoring datafile 00005 to +DATA/rac/datafile/example.278.851343653
channel ORA_DISK_1: restoring datafile 00006 to +DATA/rac/datafile/undotbs2.274.851343653
channel ORA_DISK_1: restoring datafile 00007 to +DATA/rac/datafile/hdts.279.851343653
channel ORA_DISK_1: reading from backup piece +FRA/rac/backupset/2014_06_26/nnndf0_full_db_rac_0.500.851259117
channel ORA_DISK_1: piece handle=+FRA/rac/backupset/2014_06_26/nnndf0_full_db_rac_0.500.851259117 tag=FULL_DB_RAC
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:05
Finished restore at 30-JUN-14
RMAN>

recover database

RMAN> recover database;

alter database open resetlogs;

RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>

Oracle Dataguard failover and reinstate primary

My scenario is we need to do some changes on database and in case of any issues want our dataguard instance to reinstate primary.

Steps to accomplish this work are:

1. Turn off the Transport
2. Do a log switch on primary to make sure that none of the current changes are going to DR
3. Connect to standby database using dgmgrl from standby node and Failover to DR
4. Shutdown primary database
5. Startup primary database in mount mode
6. Reinstate primary database
7. Switch over to primary database;

Turn off the Transport

DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL> edit database 'RAC' set state=TRANSPORT-OFF;
Succeeded.
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL>

Do a log switch on primary to make sure that none of the current changes are going to DR

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>

Connect to standby database using dgmgrl from standby node and Failover to DR

DGMGRL> connect sys/*****@RAC_STBY
Connected.
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> failover to 'RAC_STBY'
Performing failover NOW, please wait...
Failover succeeded, new primary is "RAC_STBY"
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC_STBY - Primary database
    RAC      - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

Shutdown primary database

[oracle@rac1 ~]$ srvctl stop database -d RAC
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is not running on node rac1
Instance RAC2 is not running on node rac2
[oracle@rac1 ~]$

Startup primary database in mount mode

[oracle@rac1 ~]$ srvctl start database -d RAC -o mount
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
[oracle@rac1 ~]$

Reinstate primary database

DGMGRL> reinstate database 'RAC';
Reinstating database "RAC", please wait...
Operation requires shutdown of instance "RAC1" on database "RAC"
Shutting down instance "RAC1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "RAC1" on database "RAC"
Starting instance "RAC1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "RAC" ...
Reinstatement of database "RAC" succeeded
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC_STBY - Primary database
    RAC      - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> 

Switch over to primary database;

DGMGRL> switchover to 'RAC'
Performing switchover NOW, please wait...
New primary database "RAC" is opening...
Operation requires shutdown of instance "RAC_STBY1" on database "RAC_STBY"
Shutting down instance "RAC_STBY1"...
ORACLE instance shut down.
Operation requires startup of instance "RAC_STBY1" on database "RAC_STBY"
Starting instance "RAC_STBY1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "RAC"
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL> 

Oracle RMAN Backup backupset to disk

At occasions there will be need to backup your previously take backup on to a disk, you can either copy the pieces from ASM or you can use RMAN to do this work and below is how this is done. I have tired taking another copy of existing backups on to disk 

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
3381    B  A  A DISK        26-JUN-14       1       1       NO         FULL_DB_RAC
3382    B  A  A DISK        26-JUN-14       1       1       NO         FULL_DB_RAC
3383    B  A  A DISK        26-JUN-14       1       1       NO         FULL_DB_RAC
3487    B  F  A DISK        26-JUN-14       1       1       NO         FULL_DB_RAC
3518    B  A  A DISK        26-JUN-14       1       1       NO         FULL_DB_RAC
3538    B  F  A DISK        26-JUN-14       1       1       NO         TAG20140626T125712
RMAN> run {
2> allocate channel t1 device type disk format '/home/oracle/test/%d_%p_%I_%T_%U';
3> backup backupset 3381, 3382, 3383, 3487,3518;
4> }
allocated channel: t1
channel t1: SID=101 instance=RAC1 device type=DISK
Starting backup at 26-JUN-14
channel t1: input backup set: count=58, stamp=851258835, piece=1
channel t1: starting piece 1 at 26-JUN-14
channel t1: backup piece +FRA/rac/backupset/2014_06_26/annnf0_full_db_rac_0.407.851258837
piece handle=/home/oracle/test/RAC_1_2410645064_20140626_1qpbqbej_1_2 comment=NONE
channel t1: finished piece 1 at 26-JUN-14
channel t1: backup piece complete, elapsed time: 00:03:43
channel t1: input backup set: count=59, stamp=851258972, piece=1
channel t1: starting piece 1 at 26-JUN-14
channel t1: backup piece +FRA/rac/backupset/2014_06_26/annnf0_full_db_rac_0.495.851258975
piece handle=/home/oracle/test/RAC_1_2410645064_20140626_1rpbqbis_1_2 comment=NONE
channel t1: finished piece 1 at 26-JUN-14
channel t1: backup piece complete, elapsed time: 00:02:52
channel t1: input backup set: count=60, stamp=851259110, piece=1
channel t1: starting piece 1 at 26-JUN-14
channel t1: backup piece +FRA/rac/backupset/2014_06_26/annnf0_full_db_rac_0.491.851259111
piece handle=/home/oracle/test/RAC_1_2410645064_20140626_1spbqbn6_1_2 comment=NONE
channel t1: finished piece 1 at 26-JUN-14
channel t1: backup piece complete, elapsed time: 00:00:15
channel t1: input backup set: count=61, stamp=851259114, piece=1
channel t1: starting piece 1 at 26-JUN-14
channel t1: backup piece +FRA/rac/backupset/2014_06_26/nnndf0_full_db_rac_0.500.851259117
piece handle=/home/oracle/test/RAC_1_2410645064_20140626_1tpbqbna_1_2 comment=NONE
channel t1: finished piece 1 at 26-JUN-14
channel t1: backup piece complete, elapsed time: 00:05:17
channel t1: input backup set: count=62, stamp=851259429, piece=1
channel t1: starting piece 1 at 26-JUN-14
channel t1: backup piece +FRA/rac/backupset/2014_06_26/annnf0_full_db_rac_0.418.851259429
piece handle=/home/oracle/test/RAC_1_2410645064_20140626_1upbqc15_1_2 comment=NONE
channel t1: finished piece 1 at 26-JUN-14
channel t1: backup piece complete, elapsed time: 00:00:27
Finished backup at 26-JUN-14
RMAN>  list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
3381    B  A  A DISK        26-JUN-14       1       2       NO         FULL_DB_RAC
3382    B  A  A DISK        26-JUN-14       1       2       NO         FULL_DB_RAC
3383    B  A  A DISK        26-JUN-14       1       2       NO         FULL_DB_RAC
3487    B  F  A DISK        26-JUN-14       1       2       NO         FULL_DB_RAC
3518    B  A  A DISK        26-JUN-14       1       2       NO         FULL_DB_RAC
3538    B  F  A DISK        26-JUN-14       1       1       NO         TAG20140626T125712
RMAN>
[oracle@rac1 test]$ pwd
/home/oracle/test
[oracle@rac1 test]$ ls -lah
total 3.4G
drwxr-xr-x.  2 oracle oinstall 4.0K Jun 26 14:59 .
drwxr-xr-x. 30 oracle oinstall 4.0K Jun 24 15:25 ..
-rw-r-----.  1 oracle dba      946M Jun 26 14:50 RAC_1_2410645064_20140626_1qpbqbej_1_2
-rw-r-----.  1 oracle dba      873M Jun 26 14:53 RAC_1_2410645064_20140626_1rpbqbis_1_2
-rw-r-----.  1 oracle dba      6.2M Jun 26 14:53 RAC_1_2410645064_20140626_1spbqbn6_1_2
-rw-r-----.  1 oracle dba      1.6G Jun 26 14:58 RAC_1_2410645064_20140626_1tpbqbna_1_2
-rw-r-----.  1 oracle dba      879K Jun 26 14:59 RAC_1_2410645064_20140626_1upbqc15_1_2
[oracle@rac1 test]$ 

Oracle RAC database flash back to SCN in dataguard environment

A scenario where we need to do some changes on database but want to revert back to the previous state of the database and also fix the dataguard instance after reverting to previous state.

There are simple 13 steps to achieve this and those are:

1. Make sure that the FLASHBACK is ON under Primary and DR. Then get the SCN on the database from any of the PRIMARY nodes.
2. Turn off the log transport.
3. Shutdown Primary database and DR database.
4. Start up mount database on one of the nodes under primary side.
5. Log in to database and flashback database to SCN captured in step 1.
6. Shutdown database on Primary side and Start up database on all nodes on primary side.
7. SQLPLUS to primary and get the Resetlog SCN .
8. Turn the log transport on and wait for 5 mins for logs to be shipped to DR side.
9. Start the database on DR.
10. From primary side connect to dgmgrl and show configuration.
11. On DR side create a sqlplus session and flashback database to Resetlog SCN taken in step 7.
12. Shutdown DR and start is up.
13. You are done, have a cup of coffee :)

Blow are the actual commands that I have used in my environment to achieve this outcome:

Make sure that the FLASHBACK is ON under Primary and DR. Then get the SCN on the database from any of the PRIMARY nodes.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> select dbms_flashback.get_system_change_number begin_scn from dual;
            BEGIN_SCN
---------------------
             20778833
SQL>

Turn off the log transport.

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/******@RAC
Connected.
DGMGRL>
DGMGRL> show database 'RAC'    
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL> show database verbose 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
  Properties:
    DGConnectIdentifier             = 'RAC'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/rac_stby, +DATA/rac'
    LogFileNameConvert              = '+FRA/rac_stby, +FRA/rac, +DATA/rac_stby, +DATA/rac'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL>
DGMGRL> edit database 'RAC' set state=TRANSPORT-OFF;
Succeeded.
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL>

Shutdown Primary database and DR database.

[oracle@rac1 ~]$ srvctl stop database -d RAC
[oracle@racdr1 ~]$ srvctl stop database -d RAC_STBY

Start up mount database on one of the nodes under primary side.

srvctl start database -d RAC -o mount -i RAC1

Log in to database and flashback database to SCN captured in step 1.

SQL> flashback database to scn 20778833;
Flashback complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>

Shutdown database on Primary side and Start up database on all nodes on primary side.

[oracle@rac1 ~]$ srvctl stop database -d RAC
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl start database -d RAC
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
[oracle@rac1 ~]$

SQLPLUS to primary and get the Resetlog SCN .

SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
         20778835
SQL>

Turn the log transport on and wait for 5 mins for logs to be shipped to DR side.

DGMGRL> edit database 'RAC' set state=TRANSPORT-ON;
Succeeded.
DGMGRL>

Start the database on DR.

[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
[oracle@racdr1 ~]$  srvctl status database -d RAC_STBY
Instance RAC_STBY1 is running on node racdr1
[oracle@racdr1 ~]$

From primary side connect to dgmgrl and show configuration.

DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database
    RAC_STBY - Physical standby database
      Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

On DR side create a sqlplus session and flashback database to Resetlog SCN taken in step 7.

SQL> flashback database to SCN 20778835;
Flashback complete.
SQL>

Shutdown DR and start is up.

[oracle@racdr1 ~]$ srvctl stop database -d RAC_STBY
[oracle@racdr1 ~]$
[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> 

You are done have a break and get a cup of coffee :)

Trace all of the sessions from a user in oracle

Oracle provide a functionality to trace session or instance. If we have to trace all the sessions of a user then we can not do this at the moment as there is no functionality for this. Do not get disharted, there is a way to trace all of the user sessions.

First of all lets discuss why we need to trace all sessions of a user. If you are using a third party application and the application creates split second sessions with database then we have no time to get the SID and Serial number to start trace on it even we can not change the call to include the sql to enable trace on the session.

So how we do it.

1. Create a log on trigger for a user. It should call a procedure to start tracing for the current session.

2. Create another procedure to stop tracing from those sessions.

3. Once you want to stop the trigger just drop it.

Below are the script that I have used to simulate this tracing.

-- Create table to hold sid and serial

create table hdhillon.tracet (SID number(8),  serial number(8));

-- Procedure to start trace

CREATE OR REPLACE procedure SYS.startTracing
is
v_sid number;
v_serial number;
cursor c_sid is
select
   sys_context('USERENV','SID')
from dual;
BEGIN
open c_sid;
loop
    fetch c_sid into v_sid;
exit when c_sid%NOTFOUND;
select distinct serial# into v_serial from v$session
where SID = v_sid;
        insert into hdhillon.tracet (SID,Serial) values(v_sid,v_serial);
    dbms_session.set_identifier('harvey_trace');
dbms_monitor.session_trace_enable( v_sid, v_serial, TRUE,TRUE);
    end loop;
close c_sid;
END;
/

-- Logon trigger

CREATE OR REPLACE TRIGGER SYS.Harvey_USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'HDHILLON'
  THEN
    startTracing();
    END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

-- Procedure to stop the tracing

CREATE OR REPLACE procedure SYS.stopTracing
is
v_sid number;
v_serial number;
cursor c_sid is
select
   SID, serial
from hdhillon.tracet;
BEGIN
open c_sid;
loop
    fetch c_sid into v_sid, v_serial;
exit when c_sid%NOTFOUND;
    dbms_monitor.session_trace_disable( v_sid, v_serial);
    delete from hdhillon.tracet where sid = v_sid;
    end loop;
close c_sid;
commit;
END;
/

-- Script to generate human readable trace files

select 'trcsess output=myoutput' || SID || '_' || SERIAL || '.trc session=' || SID || '.' || SERIAL from hdhillon.tracet;

--Clear procedures and triggers

DROP TRIGGER SYS.Harvey_USER_TRACE_TRG;
drop procedure  SYS.startTracing ;
drop procedure SYS.stopTracing;
drop procedure SYS.stopTracingRunning;

ORA-02049: timeout: distributed transaction waiting for lock

Very interesting error message coming on my database while trying to update a table using database link

ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from <Database Link Name>

Oracle uses a parameter distributed_lock_timeout if you are using a database link to update some objects. If there are already exists row lock on table and you are trying to acquire the lock on those rows then the wait will be for the number of seconds specified in parameter distributed_lock_timeout.

The solution is remove the locks on table.