Hey all!
Recently I got the following error when trying to start a trace in a standby. The idea was to start a trace in MRP to check on performance, but when trying to use DBMS_MONITOR.session_trace_enable:
oracle:standby-srvr /tmp: sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 19 14:07:56 2018 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
OMG!
Don’t panic, you can accomplish the same using oradebug:
-- In my case: 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. SQL>
You can apply same for any process/sid you need.
In case you desire to get the spid from a sid:
col machine format a30 col process format 999999 select p.spid,b.sid, p.pid from v$session b, v$process p where b.paddr=p.addr and sid=&sid /
Ok, and how to stop then?
Easy:
SQL> oradebug setospid 19564 SQL> oradebug Event 10046 trace name context off SQL> oradebug tracefile_name
Enjoy!