Databases Reference
In-Depth Information
Now inspect the output log file. There will be a timestamp at the very top showing when the connection to RMAN was
initiated. Here is some sample output:
Recovery Manager: Release 12.1.0.0.2 ... on Sun Aug 12 14:27:37 2012
Now search the log file and identify the time that the backup command starts:
Starting backup at 12-aug-2012 14:27:41
That shows in this example that RMAN spent only a few seconds from the time it connected and queried the
repository to the time it started the
backup
command. In this environment, there are no performance issues with
querying the RMAN repository metadata. If the time between the connection and the start of the backup job had been
several minutes, then you would have a performance problem with querying RMAN metadata.
How It Works
When you first connect to RMAN and run commands, the RMAN client needs to communicate with the target database
and the recovery catalog (if using). For example, before an RMAN backup command can execute, the repository needs to
be queried to determine which files need to be backed up and where the backups should be written to.
If you have a performance problem with querying the RMAN metadata, you'll have to dig a little deeper into the
internals of RMAN to determine the exact problem. It could be either a problem with communication between the
RMAN client and the target database, or a problem with querying the recovery catalog (if using).
When you start RMAN, two processes are started on the target database (see Figure
16-1
). One is the first default
process; the other is a polling process. The default process is the one responsible for querying the target database
control file and running any SQL statements issued from within RMAN. If you're using a recovery catalog, then a
server process is started on the recovery catalog server. This process is responsible for handling communication
between the RMAN client and the recovery catalog.
There are two basic techniques for gathering in-depth details on the communication between the RMAN client
and the target database first default process and/or the recovery catalog:
•
Enable RMAN debug tracing
•
Enable SQL tracing
RMAN debug tracing provides information on internal RMAN operations. When working with Oracle Support,
support personnel oftentimes will request that you enable RMAN debug tracing. This topic is covered in detail in
Recipe 17-9.
You can also enable tracing of any SQL that RMAN is running. This is useful to determine whether a particular
SQL query is the cause of performance issues. See Recipe 16-5 for further details on enabling SQL tracing.
If you eliminate the querying of the RMAN metadata as a problem, then you need to focus efforts on where the
backup operation is spending its time. For example, is the performance issue with reading data files or with writing to
the backup pieces (see Recipe 16-6 for further details on isolating read or write performance issues).
Enabling SQL Tracing for an RMAN Session
Problem
You suspect you have performance problems related to RMAN processes that query the RMAN repository. You want
to determine which SQL statements RMAN is executing and how long the queries are running.
