RMAN TSPITR – Examining the driver script

In this article, we’ll take a look at what Oracle does when
you perform a tablespace point-in-time recovery (TSPITR) – and also what it
doesn’t do, depending on the version being used. One of the errors can be
vexing, but once you know where and how to fix it, the point-in-time recovery
process works much better. Working “much better” doesn’t necessarily mean
perfectly, but more than likely, you’ll be at a place where finishing the
recovery is fairly straightforward.

During an RMAN session, you’ll see output where RMAN is
“printing” or using a stored memory script. The print part is correct as the
output is printed to the terminal/session window, but from memory is not
entirely accurate. One example of the output is shown below, and you can see
how Oracle has taken your input (via script and init.ora parameters) and fed it
into the RMAN session. In my example, I am recovering tablespace USERS until logseq

RMAN> run {
2> allocate auxiliary channel dev1 type ‘sbt_tape’;
3> set newname for datafile ‘/u065/oradata/train/system01.dbf’ to
4> set newname for datafile ‘/u064/oradata/train/users01.dbf’ to
5> set newname for datafile ‘/u065/oradata/train/rbs01.dbf’ to
6> recover tablespace users until logseq 499 thread 1;
7> }

The RMAN output (partial) of what I provided is reflected

RMAN-03027: printing stored script: Memory Script
# set the until clause
set until logseq 499 thread 1;
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log for tspitr to a resent until time
sql ‘alter system archive log current’;
# resync catalog after controlfile restore
resync catalog;

Note: The “resent” time is really a “recent” time. The same typo
has existed for close to decade now.

The code to generate this output, and other RMAN output for
that matter, resides in a file named recover.bsq. The file is located in
ORACLE_HOME/rdbms/admin and is directly editable, although a note at the top
says otherwise. Within the file, around line 8509 in the 10g version and line 4523 in 8i, you can see the start of what looks to be boilerplate
text using variable resolution. The block of code looks as such:

# tspitr_0: pre_tspitr script. This member is used once.
define tspitr_0
# set the until clause
set until &1&;

# restore the controlfile
restore clone controlfile to clone_cf;

# replicate the controlfile
replicate clone controlfile from clone_cf;

# mount the controlfile
sql clone ‘alter database mount clone database’;

# archive current online log for tspitr to a resent until time
sql ‘alter system archive log current’;

# tspitr_sy_ct: resync catalog
define tspitr_sy_ct
# resync catalog after controlfile restore
resync catalog;

Note that the set until clause is required to resolve at
least three different inputs (time, SCN, and log sequence number). Continuing
on through the tspitr_X “defines” in the C-like code, the procedural steps to
rename files, flip them, online/offline files, and alter database are easy to
correlate to an RMAN session’s output.

You would think that the TSPITR code from Oracle would be
robust, where user input or editing of the stored script wouldn’t be necessary.
Interestingly enough, between sections 7 and 8 we see the following “oh by the
way” instructions for a user.

# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone “create temporary tablespace temp
# tempfile ”/tmp/aux_tspitr_tmp.dbf” size 500K”;

Depending upon your environment/situation, you may have to
edit the recover.txt (although you can edit recover.bsq file and not have the
changes overwritten) and uncomment the two lines associated with creating a
temporary tablespace. Okay, that is one fix related to the export failing. The
explanation for why a temporary tablespace is needed is that the sort area (in
memory) is not large enough, and just like any other database (i.e., operating
normally, not needing any recovery), if sorts cannot be done in memory, then
Oracle goes to the temp tablespace.

Your mileage may vary on this, but uncomment the “create
temporary tablespace” statement and use what is suggested. If you encounter
another error, more than likely it is due to the fact the tempfile size is not
large enough (the second fix). Instead of trying to tweak the size parameter so
you just miss encountering the error, go big early and avoid the problem
altogether. How big is big enough? That may be trial and error, but it is quite
likely to be on the order of megabytes (e.g., 5MB). The point is this: don’t
waste time setting it to 600K, then 700K, and so on.

Two points are in order here. First, this problem is
supposedly fixed in 10gR2 and above, so you may never have to edit this file –
at least for this reason. The second is that Oracle Corp. provides conflicting
instructions on which file to edit: recover.bsq or recover.txt. As mentioned,
the actual file says to edit the text version, but in a MetaLink note (“ORA-25153:
Temporary Tablespace is Empty encountered on RMAN TSPITR,” Doc ID 263483.1),
the instructions are:

Edit recover.bsq (RMAN library file) in $ORACLE_HOME/rdbms/admin
(location is platform specific) and make the following changes. NOTE: It is
recommended to take a backup of this file.

For sure, you can edit the recover.bsq file, but do make a
copy of the original beforehand. If you want to experiment with the file, try
adding your own comment lines here and there. Commented lines (those beginning
with #) within the >>> to the <<< blocks are output to the
RMAN session. For example, if seeing “PLUG HERE” grates on your sense of
grammar, feel free to replace it with your own comment.

The instructional value of looking through all the tspitr_X
sections is that you can follow the process at a higher level (as opposed to
filtering out the RMAN output). If something goes wrong, you’ll have a better
idea of where in the process the failure took place.

While on the subject of the recover.bsq file, a search on MetaLink
(yes, I’m avoiding writing the clunky sounding “My Oracle Support”) for this
file returns 29 entries (as of the publish date of this article). An
interesting theme among the hits is that of the wrong version of the file being
used. And like many other notes, check them for accuracy before making changes
or running scripts.

One note related to upgrading the recovery catalog has you fix
one error but only to introduce another via the changed script. The suggested
change in Doc ID 833046.1 (“RMAN-600 [3040] TRYING TO UPGRADE THE RECOVERY
CATALOG”) has an error in the SELECT clause (extra comma at the end of the last
column selected). Not that you would be stressed for time during some hugely
critical upgrade or recovery process or anything like that as there is always
enough time to fix technical notes from Oracle.

In Closing

The recover.bsq file is interesting in that it provides you
with the nitty-gritty of what RMAN does during a recovery. All of the steps
aren’t necessarily listed in the documentation, or in third-party books for
that matter, and if you can read code at even a basic level, you can understand
what is taking place when RMAN does its magic. Knowing about this file ahead of
time can save you hours of searching for resolution of RMAN errors, and
probably during a time when you really can’t afford those extra hours.


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles