Subject: RMAN: Tablespace Point In Time Recovery (TSPITR) Procedure.
Doc ID: Note:109979.1 Type: BULLETIN
Last Revision Date: 11-OCT-2007 Status: PUBLISHED

Tablespace point in time recovery procedure.

Tablespace point in time recovery (TSPITR) with RMAN uses a technique
of cloning a primary database with the minimum physical structure required to
recover a tablespace to the desired point in time. Before RMAN clones it,
you need perform some manual actions to prepare a clone instance.
Those steps are described in detail in the pre-recovery steps of this bulletin.
The rest of TSPITR is performed by RMAN. For better understanding of the
process, I am explaining some terminology:

Primary or target database: a database which needs tablespace point in time
recovery.

Clone database (called also auxiliary database): a separate database constructed
from the primary which consists of an auxiliary and a recovery set.

Recovery set: all datafiles related to tablespace(s) to be recovered .

Auxiliary set: minimum number of datafiles from the primary database restored
into clone database making the clone a fully independent database. These are
the controlfile , the system and rollback segment related datafiles.

The RMAN job restores the auxiliary and recovery sets. Then, it recovers
the clone database to the specified point in time. RMAN opens the clone
database with resetlogs, and it performs an export of the tablespace(s) to be
recovered. Finally, it imports an export dump file into target database
completing recovery process. This is the PSTIPR in nutshell. Now, lets get
the details.

Pre-recovery steps.

Note:
This bulletin concerns UNIX based installations, however, can be referenced for
other operating systems in general.

1. Create the clone(auxiliary) instance:
- Create a new directory to store the clone database auxiliary and recovery sets.
- Copy the target database init.ora and config.ora into that
directory, and rename them as init.ora config.ora
- Create a soft link for init.ora in $ORACLE_HOME/dbs directory
- Set following init.ora and config parameters as follows:

ifile # referenced to config.ora

The compatible and db_names must be the same as the target database

remote_login_passwordfile=exclusive # for pasword file authentication when
logging in as sysdba

lock_name_space= # set to clone instance name

db_file_name_convert=(?target_datafiles_path?,?clone_datafiles_path?)
for example:
db_file_name_convert =('/u05/home/rsupport/crashdb/rcrsh805/data/',
'/u05/home/rsupport/crashdb/rcrsh805/data/aux/')

log_file_name_convert=('target_logfiles_path?,?clone_logfile_patch?)
for example:
log_file_name_convert=('/u05/home/rsupport/crashdb/rcrsh805/data/',
'/u05/home/rsupport/crashdb/rcrsh805/data/aux/')

Note:
Setting the above two init parameters will restore essential files like the
control file, system and rollback segment datafiles into clone database path
during RMAN TSIPTR script execution. The same is true regarding the redo log
files which are created during ?alter database open resetlogs? on the clone
database. You need to use trailing slashes at the end of paths.

Warning:
There is an exception. Recovery set datafiles will be restored to an original
target database location unless you explicitly use
?set newname for datafile x to ?...? ? command in RMAN script.

log_archive_start=false # clone database must be in noarchived mode

Additionally you can change the location for the background_dump_dest and
user_dump_dest to clone database path.

- Set control_files path in config.ora to a new path to avoid
target control files overwriting when RMAN restores the control file for the
database.
- Create a password file for the clone database for remote sysdba connections.
Example: from $ORACLE_HOME/dbs use syntax
orapwd file=orapw password=change_on_install
- Set the ORACLE_SID OS environment variable to the clone instance name.
- Startup the clone instance in nomount mode.
- Set the tns service name for the clone instance. It means that you have to
make an additional entry in a local tnsnames.ora file for that instance. Also,
you need to modify the listener.ora file to include the SID discription in the
SID_LIST_listener part of that configuration file. This is an example of
modifications in tns files:
#tnsnames
aux.ca.oracle.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= server1.ca.oracle.com)(Port= 1521))
(CONNECT_DATA = (SID = aux))
)

#listener
(SID_LIST=
(SID_DESC =
(GLOBAL_DBNAME = aux.ca.oracle.com)
(ORACLE_HOME = /u05/app/oracle/product/8.1.5)
(SID_NAME = aux)
)

Note:
The target database must also use the remote password file authentication,
and the target tns service name must be intact as well on that host. This is
an RMAN requirement. It would be recommended to test remote net8 connections
as sysdba to both target and clone databases.

- Reload listener.

2. Now, it is a time to start an RMAN session and connect to all the instances
involved in TSPITR (catalog, target, and clone databases). This is an example:

rman rcvcat rman/rman target
RMAN>connect clone

3. Construct and execute an RMAN script similar to the one below:

RMAN> run {
2> allocate clone channel c1 type 'SBT_TAPE';
3> allocate clone channel c2 type disk;
4> set newname for datafile 6 to '/u05/home/rsupport/crashdb/aux/tspitr01.dbf';
5> recover tablespace tspitr until logseq 2 thread 1;
6>}

Note:
You restore your backup set from tape, but you still need to allocate a ?disk?
type channel, since RMAN will attempt to replicate clone database control files.
It happens even you have specified one copy of the control file in the
config.ora file. If you don?t do so, the RMAN job will terminate with
the following error:

the pre_tspitr script will failed on coping control file:
RMAN-03022: compiling command: replicate
RMAN-00569: ================error message stack follows================
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03013: command type: recover
RMAN-03015: error occurred in stored script pre_tspitr
RMAN-03002: failure during compilation of command
RMAN-03013: command type: replicate
RMAN-06032: at least 1 channel of TYPE DISK must be allocated to execute a COPY
command

For better understanding what kind of tasks RMAN performs I am attaching the
full RMAN log from a successful TSPITR at the end of this bulletin

Note:
The ?set newname for datafile? should be set prior to the ?recover? command for
all files in the recovery set. The db_file_name_convert init parameter concerns
auxiliary set files only. If you don?t set newnames for those files, RMAN will
restore them to target database location overwriting original ones, and it will
make them part of the clone database. Therefore, a target database tablespace(s)
won?t be recognised and accessible by the target instance any more. In fact,
it will be corrupted from the target database point of view. The ?set newname ??
precaution will eliminate potential danger of loosing the target tablespace(s)
if TSPITR process fails.

Note:
You have a choice of ?recover tablespace until? clause. You can limit point in
time recovery with a time stamp, log sequence #, and SCN #. Remember ,if you are
using the ?until time? clause the NLS_DATE_FORMAT environment variable must be
set prior to launching the RMAN executable.

Note:
The post_tspitr RMAN script executes the ?host? RMAN command to export the
tablespace(s) to be recovered. The ?host? simply spawns a UNIX shell to launch
the exp command. Since the post_tspitr script doesn?t include entire path
$ORACLE_HOME/bin/, you must include the path in the $PATH of the ?oracle?
UNIX account profile. Otherwise, the RMAN script can terminate. Double check
if the .profile or .cshrc ?oracle? UNIX account profile includes that path.
If it defaults to csh look into .cshrc, otherwise look at .profile. It may be
a case of multiple Oracle releases on a box when oraenv or any other technique
is in use to condition the user environment at login time. If a spawn shell
doesn?t have an $ORACLE_HOME/bin path, you can see the following RMAN
termination:

RMAN-03022: compiling command: host
exp: Command not found.
RMAN-06134: host command complete
RMAN-00569: ================error message stack follows================
RMAN-00601: fatal error in recovery manager
RMAN-03012: fatal error during compilation of command
RMAN-03013: command type: recover
RMAN-03015: error occurred in stored script post_tspitr
RMAN-03002: failure during compilation of command
RMAN-03013: command type: host
RMAN-06135: error executing host command: Additional information: 256

Note:
It is not recommended to explicitly release allocated channels with commands:
release channel c1;, release channel c2;, since it can produce errors at the end
of TSPITR giving the misleading impression that a process was terminated
unsuccessfully (despite the fact that tablespace(s) was recovered).

RMAN-03022: compiling command: release
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: release
RMAN-06012: channel: c1 not allocated

Full listing of successful TSPITR with RMAN to recover ?test? table:
% rman rcvcat rman/rman target

Recovery Manager: Release 8.1.5.0.0 - Production

RMAN-06005: connected to target database: RCRSH815 (DBID=363624922)
RMAN-06008: connected to recovery catalog database

RMAN> connect clone

RMAN-06020: connected to auxiliary database

RMAN> run {
2> allocate clone channel c1 type 'SBT_TAPE';
3> allocate clone channel c2 type disk;
4> set newname for datafile 6 to '/u05/home/rsupport/crashdb/aux/tspitr01.dbf';
5> recover tablespace tspitr until logseq 56 thread 1;
6> }

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=10 devtype=SBT_TAPE
RMAN-08526: channel c1: MMS Version 2.1.2.1

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c2
RMAN-08500: channel c2: sid=11 devtype=DISK

RMAN-03022: compiling command: set

RMAN-03022: compiling command: recover

RMAN-03027: printing stored script: Memory Script
{
# set the until clause
set until logseq 56 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;
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=268438896 set_stamp=0 creation_time=01-JAN-88
RMAN-08021: channel c1: restoring controlfile
RMAN-08505: output filename=/u05/home/rsupport/crashdb/aux/control01.ctl
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=backup_6_1 params=NULL
RMAN-08024: channel c1: restore complete

RMAN-03022: compiling command: replicate
RMAN-03023: executing command: replicate
RMAN-08058: replicating controlfile
RMAN-08506: input filename=/u05/home/rsupport/crashdb/aux/control01.ctl

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database mount clone database
RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql

RMAN-03022: compiling command: resync
RMAN-03023: executing command: resync
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete

RMAN-03027: printing stored script: Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until logseq 56 thread 1;
# set a destination filename for restore
set newname for datafile 1 to
'/u05/home/rsupport/crashdb/aux/system01.dbf';
# set a destination filename for restore
set newname for datafile 2 to
'/u05/home/rsupport/crashdb/aux/rbs01.dbf';
# set a destination filename for restore
set newname for datafile 6 to
'/u05/home/rsupport/crashdb/aux/tspitr01.dbf';
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace TSPITR, SYSTEM, RBS;
sql clone "alter database open resetlogs";
# 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 tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=6 set_stamp=397750918 creation_time=16-MAY-00
RMAN-08089: channel c1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to /u05/home/rsupport/crashdb/aux/system01.
dbf
RMAN-08523: restoring datafile 00002 to /u05/home/rsupport/crashdb/aux/rbs01.dbf
RMAN-08523: restoring datafile 00006 to /u05/home/rsupport/crashdb/aux/tspitr01.
dbf
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=backup_6_1 params=NULL
RMAN-08024: channel c1: restore complete

RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=16 stamp=397751896 filename=/u05/home/rsupp
ort/crashdb/aux/tspitr01.dbf

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database datafile 1 online
RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database datafile 2 online
RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database datafile 6 online
RMAN-03023: executing command: sql

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08515: archivelog filename=/u05/home/rsupport/crashdb/rcrsh815/data/arch/_5
5.arc thread=1 sequence=55
RMAN-08055: media recovery complete

RMAN-03022: compiling command: recover(4)

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database open resetlogs
RMAN-03023: executing command: sql

RMAN-03027: printing stored script: Memory Script
{
# export the tablespaces in the recovery set
host "exp userid ='sys/change_on_install@aux as sysdba' point_in_time_recover=
y tablespaces=
TSPITR file=tspitr_a.dmp";
#rename a datafile in both recovery set and setname/setclonename list
sql "alter database rename file ''/u05/home/rsupport/crashdb/rcrsh815/tspitr01.
dbf'' to
''/u05/home/rsupport/crashdb/aux/tspitr01.dbf''";
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host "imp userid ='sys/change_on_install@server1_rcrsh815 as sysdba' point_in
_time_recover=y file=tspitr_a.dmp";
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace TSPITR offline";
# resync catalog after tspitr finished
resync catalog;
}
RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: host

Export: Release 8.1.5.0.0 - Production on Tue May 16 14:38:22 2000

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
RMAN-06134: host command complete

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database rename file ''/u05/home/rsupport/cras
hdb/rcrsh815/tspitr01.dbf'' to ''/u05/home/rsupport/crashdb/aux/tspitr01.dbf''
RMAN-03023: executing command: sql

RMAN-03022: compiling command: shutdown
RMAN-06405: database closed
RMAN-06404: database dismounted
RMAN-06402: Oracle instance shut down

RMAN-03022: compiling command: host

Import: Release 8.1.5.0.0 - Production on Tue May 16 14:38:43 2000

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Export file created by EXPORT:V08.01.05 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing USERTSPITR's objects into USERTSPITR
. . importing table "TEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
RMAN-06134: host command complete

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter tablespace TSPITR online
RMAN-03023: executing command: sql

RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter tablespace TSPITR offline
RMAN-03023: executing command: sql

RMAN-03022: compiling command: resync
RMAN-03023: executing command: resync
RMAN-08002: starting full resync of recovery catalog
RMAN-08004: full resync complete

Note:
Since the RMAN job renames recovered tablespace datafile(s) to be in the clone
database location at the end of TSPITR, it would be inconvenient to keep it this
way. Someone can accidentally delete those files assuming that they belong to
the clone database.

Before TSPITR recovery:

SVRMGR> select file#, name from v$datafile;
FILE# NAME

6 /u05/home/rsupport/crashdb/rcrsh815/tspitr01.dbf

After TSPITR recovery:

SVRMGR> select file#, name, status from v$datafile;
FILE# NAME
STATUS

6 /u05/home/rsupport/crashdb/aux/tspitr01.dbf
OFFLINE

As you can see, the RMAN TSPITR job left recovered tablespace offline,
so you can copy the associated datafile(s) into the original location then use
?alter database rename file ? ? command to update the control file about that
change. Now, you can put that tablespace online.

SVRMGR> alter database rename file '/u05/home/rsupport/crashdb/aux/tspitr01.dbf'
to '/u05/home/rsupport/crashdb/rcrsh815/tspitr01.dbf';
Statement processed.
SVRMGR> alter tablespace tspitr online;
Statement processed.

RMAN: Tablespace Point In Time Recovery (TSPITR)

相關文章