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 ignoredPretty 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_nameAre 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.
