Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 22, 2009

RMAN TSPITR - Examining the driver script

By Steve Callan

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 499.

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 below.

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM