Trace on Standby: Tracing MRP Process

1 min read
Apr 26, 2020

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 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 - 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%';
 --------- ------------------------
 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.
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,
 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.

Get Email Notifications

No Comments Yet

Let us know what you think