Tuning RMAN - RMAN Recipes for Oracle Database 12c - page 511

■ Keep in mind that SQL tracing is entirely different from rMan debug tracing. SQL tracing captures SQL state-
ments (and associated statistics) executed by a session. rMan debug tracing captures information regarding rMan
internal processing. See recipe 17-9 for details on enabling rMan debug tracing.

Note

Solution
Enable SQL tracing for the process of interest. There are two processes where you would want to capture the SQL:

• First default process connected from the RMAN client to the target database

• Process connected to the RMAN recovery catalog

The prior two connections are most likely where you will have issues with long-running SQL statements related
to RMAN. Enabling SQL tracing for these sessions is described in the following two subsections.

First Default Process
Here we enable SQL tracing on the first default process by connecting to the target database and issuing the following
statements:

$ rman target /
RMAN> alter session set events '10046 trace name context forever, level 12';
RMAN> select 'RMAN SQL TRACE FILE' from dual;
RMAN> backup datafile 4;
RMAN> exit;

If you're running Oracle Database 11g or below, then the prior code needs to use the sql command:

RMAN> sql "alter session set events ''10046 trace name context forever,
level 12''";
RMAN> sql "select ''RMAN SQL TRACE FILE'' from dual";
RMAN> backup datafile 4;
RMAN> exit;

The line in the prior code bits where we select a text string from dual is there simply to help find the correct
trace file.
You should now have a trace file in your trace diagnostic directory. See the subsection “Analyzing Trace File
Information” in the Solution section of this recipe for details on extracting information from the trace file.

Recovery Catalog Process
A simple approach to enabling SQL tracing on the recovery catalog process is to create an after-logon trigger (in the
recovery catalog database) that automatically starts tracing for the recovery catalog user when it connects. This next
bit of code shows how to do that:

create or replace trigger trace_rcat
after logon on database
declare
trace_string varchar2(100);