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.

The Challenge: Enabling Traces on a Standby Database

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

The Solution: Using Oradebug for Process Tracing

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

Step-by-Step Tracing with Oradebug

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: oracle@standby-srvr (MRP0)
 
 SQL> oradebug unlimit
 
 Statement processed.
 
 SQL> oradebug Event 10046 trace name context forever, level 12
 
 Statement processed.
 

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

Stopping the Trace and Locating the File

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.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?