Trace on Standby: Tracing MRP Process

Sounds simple, right? And it really is, although it might not seem obvious.

Recently, I ran into an issue when trying to start a trace in a standby. The idea was to start a trace in MRP to monitor performance. However, when trying to use DBMS_MONITOR.session_trace_enable, the following error occurred:

oracle:standby-srvr /tmp: sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Fev 21 14:07:56 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> exec DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE);
BEGIN DBMS_MONITOR.session_trace_enable(session_id =>1970, serial_num=>55126, waits=>TRUE, binds=>TRUE); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR.SESSION_TRACE_ENABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Pretty much what we might expect, right? Don’t panic. You can accomplish the same thing using oradebug, as shown in the example below:

SQL> select process,pid from V$managed_standby where process like '%MRP%';

PROCESS   PID
--------- ------------------------
MRP0      45693

SQL> oradebug setospid 19564

Oracle pid: 105, Unix process pid: 19564, image: [email protected] (MRP0)

SQL> oradebug unlimit

Statement processed.

SQL> oradebug Event 10046 trace name context forever, level 12

Statement processed.

You can apply the method to any process/sid you need. If you want to get the spid from a sid, do this:

select p.spid,b.sid, p.pid
from v$session b, v$process p
where b.paddr=p.addr
and sid=&sid
/

To stop, use the usual oradebug syntax:

SQL> oradebug setospid 19564
SQL> oradebug Event 10046 trace name context off
SQL> oradebug tracefile_name

Are you experiencing additional issues with your MRP process? Contact Pythian (using the chat feature in lower-right corner of the page) and let us help you.