cid pid client_pid os id

To generate a trace file of the RFS process we need to implement the following:

1. In the alert log of a primary database, we find the ARC (archiver) process which was most recent to start the archiver's heartbeat. For example:

...
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
...
ARC6 started with pid=42, OS id=415
...
Thu Jun 14 13:08:02 EST 2012
ARC6: Becoming the heartbeat ARCH
...

In this example the heartbeat archiver process is ARC6, as seen in the primary database's alert log above.

2. In the standby database we find the ID of the RFS process which is communicating with the ARC6 process on the primary. Example:

[yourmachine ~]$ ps -ef | grep arc
oracle 5352 1 0 Jul25 ? 00:00:00 ora_arc0_dg112i1
oracle 5356 1 0 Jul25 ? 00:00:00 ora_arc1_dg112i1
oracle 5358 1 0 Jul25 ? 00:00:08 ora_arc2_dg112i1
oracle 5360 1 0 Jul25 ? 00:00:00 ora_arc3_dg112i1
oracle 5357 1 0 Jul25 ? 00:00:00 ora_arc4_dg112i1
oracle 5359 1 0 Jul25 ? 00:00:08 ora_arc5_dg112i1
oracle 415 1 0 Jul25 ? 00:00:00 ora_arc6_dg112i1

In this example we found that the ARC6 process id is 415. This confirms what we have seen in the primary database's alert log ("id=415").

3. In the standby database we connect to SQLPlus as a user who can query the data dictionary view v$managed_standby. We have to find the RFS process ID corresponding with the archiver process 415. Example:

SQL> select process,pid,client_pid from v$managed_standby;

PROCESS PID CLIENT_PID
--------- ---------- ----------------------------------------
ARCH 10451 10451
ARCH 10453 10453
ARCH 10455 10455
ARCH 10457 10457
RFS 10627 5358
RFS 10597 6201
RFS 10601 23000
RFS 10653 415 <---
RFS 10529 1193
RFS 10603 5360
MRP0 10643 N/A

11 rows selected.

In this example the PID (process ID) is 10653.

4. We have to attach to that RFS using the 'oradebug' utility and set event 10046 trace name context forever to get the SQL/waits, internal queries and other information:

SQL> oradebug setospid 10653
Oracle pid: 25, Unix process pid: 10653, image: oracle@<host machine.com> (ARC6)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.
SQL> -- Keep tracing for some time while the problem is occurring
SQL> -- as required by Oracle Support.
SQL> -- To stop tracing:
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL>