1. 首页
  2. IT资讯

RMAN: Tablespace Point In Time Recovery (TSPITR)

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 originaltarget 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:#tnsnamesaux.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 sys/change_on_install@server1_rcrsh815RMAN>connect clone sys/change_on_install@aux

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: replicateRMAN-00569: ================error message stack follows================RMAN-00601: fatal error in recovery managerRMAN-03012: fatal error during compilation of commandRMAN-03013: command type: recoverRMAN-03015: error occurred in stored script pre_tspitrRMAN-03002: failure during compilation of commandRMAN-03013: command type: replicateRMAN-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 areusing 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: hostexp: Command not found.RMAN-06134: host command completeRMAN-00569: ================error message stack follows================RMAN-00601: fatal error in recovery managerRMAN-03012: fatal error during compilation of commandRMAN-03013: command type: recoverRMAN-03015: error occurred in stored script post_tspitrRMAN-03002: failure during compilation of commandRMAN-03013: command type: hostRMAN-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 endof TSPITR giving the misleading impression that a process was terminated unsuccessfully (despite the fact that tablespace(s) was recovered).

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

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

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 sys/change_on_install@aux

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: allocateRMAN-03023: executing command: allocateRMAN-08030: allocated channel: c1RMAN-08500: channel c1: sid=10 devtype=SBT_TAPERMAN-08526: channel c1: MMS Version 2.1.2.1

RMAN-03022: compiling command: allocateRMAN-03023: executing command: allocateRMAN-08030: allocated channel: c2RMAN-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 clauseset until logseq 56 thread 1;# restore the controlfilerestore clone controlfile to clone_cf;# replicate the controlfilereplicate clone controlfile from clone_cf;# mount the controlfilesql clone ‘alter database mount clone database’;# archive current online log for tspitr to a resent until timesql ‘alter system archive log current’;# resync catalog after controlfile restoreresync catalog;}RMAN-03021: executing script: Memory Script

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

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

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

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

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

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

RMAN-03027: printing stored script: Memory Script{# generated tablespace point-in-time recovery script# set the until clauseset until logseq 56 thread 1;# set a destination filename for restoreset newname for datafile 1 to ‘/u05/home/rsupport/crashdb/aux/system01.dbf’;# set a destination filename for restoreset newname for datafile 2 to ‘/u05/home/rsupport/crashdb/aux/rbs01.dbf’;# set a destination filename for restoreset newname for datafile 6 to ‘/u05/home/rsupport/crashdb/aux/tspitr01.dbf’;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 6;switch clone datafile all;#online the datafiles restored or flippedsql clone “alter database datafile 1 online”;#online the datafiles restored or flippedsql clone “alter database datafile 2 online”;#online the datafiles restored or flippedsql clone “alter database datafile 6 online”;# make the controlfile point at the restored datafiles, then recover themrecover 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: set

RMAN-03022: compiling command: set

RMAN-03022: compiling command: set

RMAN-03022: compiling command: restore

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

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

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

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

RMAN-03022: compiling command: sqlRMAN-06162: sql statement: alter database datafile 6 onlineRMAN-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 recoveryRMAN-08515: archivelog filename=/u05/home/rsupport/crashdb/rcrsh815/data/arch/_55.arc thread=1 sequence=55RMAN-08055: media recovery complete

RMAN-03022: compiling command: recover(4)

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

RMAN-03027: printing stored script: Memory Script{# export the tablespaces in the recovery sethost “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 listsql “alter database rename file ”/u05/home/rsupport/crashdb/rcrsh815/tspitr01.dbf” to ”/u05/home/rsupport/crashdb/aux/tspitr01.dbf””;# shutdown clone before importshutdown clone immediate# import the tablespaces in the recovery sethost “imp userid =’sys/change_on_install@server1_rcrsh815 as sysdba’ point_in_time_recover=y file=tspitr_a.dmp”;# online/offline the tablespace importedsql “alter tablespace TSPITR online”;sql “alter tablespace TSPITR offline”;# resync catalog after tspitr finishedresync 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

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 – ProductionWith the Partitioning and Java optionsPL/SQL Release 8.1.5.0.0 – ProductionExport done in US7ASCII character set and WE8ISO8859P1 NCHAR character setserver 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 recoveryExport terminated successfully without warnings.RMAN-06134: host command complete

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

RMAN-03022: compiling command: shutdownRMAN-06405: database closedRMAN-06404: database dismountedRMAN-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

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 – ProductionWith the Partitioning and Java optionsPL/SQL Release 8.1.5.0.0 – Production

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

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

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

RMAN-03022: compiling command: resyncRMAN-03023: executing command: resyncRMAN-08002: starting full resync of recovery catalogRMAN-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.

RELATED DOCUMENTS

Oracle8i/9i Recovery Manager User’s Guide and Reference

The Oracle documentation is available online at http://otn.oracle.com and http://tahiti.oracle.com.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84986/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/194896.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code