Trace on Standby: Tracing MRP Process

1 min read

Apr 26, 2020 12:00:00 AM

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

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Trace on Standby: Tracing MRP Process

Share this

Share this

More resources

After logon trigger in user schema and FRA full situation hang the database

Select vs Assign – How To Assign PL/SQL Variables

Disabling Triggers in Oracle 11.2.0.4

Ready to unlock value from your data?