Trace on Standby: Tracing MRP Process

Posted in: Technical Track

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: [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,
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.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Lead Database Consultant
Well known in the Oracle community in Latin America and Europe where he participates regularly in technology events, Matheus is actually the youngest Oracle ACE Director in the world. Lead Database Consultant at Pythian, Matheus is a Computer Scientist by PUCRS and has been working as an Oracle DBA for the last 10 years.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *