oracle RMAN 进行表空间基于时间点的恢复.doc

RMAN TSPITR 使用rman进行表空间基于时间点的恢复 收藏 实例说明： (1)先创建2个表空间。create tablespace user01 datafile +DG1 size 1M;create tablespace user02 datafile +DG1 size 1M; (2)在每个表空间上各创建一张表。create table scott.customers (cust_id int,cust_name varchar2(10) tablespace user01;create table scott.sales (id int,cust_name varchar2(10),sales_amount number(8,2) tablespace user02; (3)在每个表中插入2条记录，提交。检查当前的时间点，待会表空间user01要恢复到当前时间点。 insert into scott.customers values(1,SOCTT);insert into scott.customers values(2,SMITH);insert into scott.sales values(1,SCOTT,8000);insert into scott.sales values(1,SMITH,10000);COMMIT;ALTER SYSTEM SWITCH LOGFILE;ALTER SYSTEM SWITCH LOGFILE;ALTER SYSTEM SWITCH LOGFILE;date2010年 03月 11日 星期四 21:44:52 CST (4) truncate 表1，往表2中插入2条记录。在表空间1中再创建一个表。truncate table scott.customers;insert into scott.sales values(3,SCOTT,6000);insert into scott.sales values(4,BLAKE,6700);commit;create table scott.employee(id int,name varchar2(10) tablespace user01; (5) 利用rman进行表空间1基于时间点的恢复。-rman部分恢复表空间recover tablespace user01 until time to_timestamp(2010-03-11 21:44:52,yyyy-mm-dd hh24:mi:ss) auxiliary destination /home/oracle/backup; (6)将表空间user01 联机， 检查表1的数据是否找回来，检查表2的数据是否是4条，检查新创建的表是否已经不存在。alter tablespace user01 online; select * from scott.customers; CUST_ID CUST_NAME- - 1 SOCTT 2 SMITHselect * from scott.sales; ID CUST_NAME SALES_AMOUNT- - - 1 SCOTT 8000 1 SMITH 10000 3 SCOTT 6000 4 BLAKE 6700select * from dba_tables where owner = SCOTT and table_name=EMPLOYEE; no rows selected 一切如我们所愿，此时，表空间不完全恢复完成。注意：只有自包含的表空间，才能基于单独不完全恢复。所谓自包含，是指该表空间中的对象不依赖于其它表空间中的对象，如该表空间中索引的基本在其它表空间，该表中某些表的lob列放在其它表空间。如在上例中，执行：create index scott.idx_customers on scott.customers(cust_name) tablespace user02;begin dbms_tts.transport_set_check(user02,true);end;select * from transport_set_violations;会提示：Index SCOTT.IDX_CUSTOMERS in tablespace USER02 points to table SCOTT.CUSTOMERS in tablespace USER01.begin dbms_tts.transport_set_check(USER01,user02,true);end;select * from transport_set_violations;不会有任何提示，因为user01/user02表空间作为一个集合，是自包含的。上面这个过程看起来简单，但是数据库在步骤5时，自己做了很多的工作，所有以前人工需要做的它一步也没有少做，具体如下：（1）建立参数文件（2）启动辅助instance到nomount状态（3）恢复辅助instance控制文件（4）启动辅助instance到mount，restore表空间对应的数据文件及辅助文件（表空间system和undo的文件）（5）将上面的几个数据文件online，恢复表空间user01，system和undo（6）open 辅助数据库（resetlogs)（7）exp 导出表空间user01；（8）关闭辅助库（9）imp 表空间user01；（10）删除辅助库对应的数据文件及其它文件。下面贴上恢复表空间user01时rman的log：Starting recover at 11-MAR-10allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=158 devtype=DISKCreating automatic instance, with SID=pudoinitialization parameters used for automatic instance:db_name=TESTASMcompatible=10.2.0.1.0db_block_size=8192db_files=200db_unique_name=tspitr_TESTASM_pudolarge_pool_size=1Mshared_pool_size=110M#No auxiliary parameter file useddb_create_file_dest=/home/oracle/backupcontrol_files=/home/oracle/backup/cntrl_tspitr_TESTASM_pudo.fstarting up automatic instance TESTASMOracle instance startedTotal System Global Area 205520896 bytesFixed Size 1218508 bytesVariable Size 146802740 bytesDatabase Buffers 50331648 bytesRedo Buffers 7168000 bytesAutomatic instance createdcontents of Memory Script:# set the until clauseset until time to_timestamp(2010-03-11 21:44:52,yyyy-mm-dd hh24:mi:ss);# restore the controlfilerestore clone controlfile;# 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;# avoid unnecessary autobackups for structural changes during TSPITRsql begin dbms_backup_restore.AutoBackupFlag(FALSE); end;# resync catalog after controlfile restoreresync catalog;executing Memory Scriptexecuting command: SET until clauseStarting restore at 11-MAR-10allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=37 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/testasm/autobackup/2010_03_11/s_713395242.273.713395243channel ORA_AUX_DISK_1: restored backup piece 1piece handle=+RECOVERYDEST/testasm/autobackup/2010_03_11/s_713395242.273.713395243 tag=TAG20100311T212042channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17output filename=/home/oracle/backup/cntrl_tspitr_TESTASM_pudo.fFinished restore at 11-MAR-10sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;starting full resync of recovery catalogfull resync completereleased channel: ORA_DISK_1released channel: ORA_AUX_DISK_1contents of Memory Script:# generated tablespace point-in-time recovery script# set the until clauseset until time to_timestamp(2010-03-11 21:44:52,yyyy-mm-dd hh24:mi:ss);plsql ;# set an omf destination filename for restoreset newname for clone datafile 1 to new;# set an omf destination filename for restoreset newname for clone datafile 2 to new;# set an omf destination tempfileset newname for clone tempfile 1 to new;# set a destination filename for restoreset newname for datafile 8 to +DG1/testasm/datafile/user01.271.713395239;# rename all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 8;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 8 online;# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace USER01, SYSTEM, UNDOTBS1 delete archivelog;alter clone 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;executing Memory Scriptexecuting command: SET until clausesql statement: alter tablespace USER01 offline for recoverexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed temporary file 1 to /home/oracle/backup/TSPITR_TESTASM_PUDO/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 11-MAR-10allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=39 devtype=DISKcreating datafile fno=8 name=+DG1/testasm/datafile/user01.271.713395239channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /home/oracle/backup/TSPITR_TESTASM_PUDO/datafile/o1_mf_system_%u_.dbfrestoring datafile 00002 to /home/oracle/backup/TSPITR_TESTASM_PUDO/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/testasm/backupset/2010_03_11/nnndf0_tag20100311t132659_0.266.713366821channel ORA_AUX_DISK_1: restored backup piece 1piece handle=+RECOVERYDEST/testasm/backupset/2010_03_11/nnndf0_tag20100311t132659_0.266.713366821 tag=TAG20100311T132659channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06Finished restore at 11-MAR-10datafile 1 switched to datafile copyinput datafile copy recid=4 stamp=713397364 filename=/home/oracle/backup/TSPITR_TESTASM_PUDO/datafile/o1_mf_system_5skxkgkz_.dbfdatafile 2 switched to datafile copyinput datafile copy recid=5 stamp=713397364 filename=/home/oracle/backup/TSPITR_TESTASM_PUDO/datafile/o1_mf_undotbs1_5skxkgo1_.dbfsql statement: alter database datafile 1 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 8 onlineStarting recover at 11-MAR-10using channel ORA_AUX_DISK_1starting media recoveryarchive log thread 1 sequence 14 is already on disk as file +DG1/testasm/1_14_713269994.dbfarchive log thread 1 sequence 15 is already on disk as file +DG1/testasm/1_15_713269994.dbfarchive log thread 1 sequence 16 is already on disk as file +DG1/testasm/1_16_713269994.dbfarchive log thread 1 sequence 17 is already on disk as file +DG1/testasm/1_17_713269994.dbfarchive log thread 1 sequence 18 is already on disk as file +DG1/testasm/1_18_713269994.dbfchannel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=9channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/testasm/backupset/2010_03_11/annnf0_tag20100311t132929_0.268.713366971channel ORA_AUX_DISK_1: restored backup piece 1piece handle=+RECOVERYDEST/testasm/backupset/2010_03_11/annnf0_tag20100311t132929_0.268.713366971 tag=TAG20100311T132929channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01archive log filename=/opt/oracle/product/10g/dbs/arch1_9_713269994.dbf thread=1 sequence=9channel clone_default: deleting archive log(s)archive log filename=/opt/oracle/product/10g/dbs/arch1_9_713269994.dbf recid=13 stamp=713397376channel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=10channel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=11channel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=12channel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=13channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/testasm/backupset/2010_03_11/annnf0_tag20100311t195513_0.270.713390115channel ORA_AUX_DISK_1: restored backup piece 1piece handle=+RECOVERYDEST/testasm/backupset/2010_03_11/annnf0_tag20100311t195513_0.270.713390115 tag=TAG20100311T195513channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03archive log filename=/opt/oracle/product/10g/dbs/arch1_10_713269994.dbf thread=1 sequence=10channel clone_default: deleting archive log(s)archive log filename=/opt/oracle/product/10g/dbs/arch1_10_713269994.dbf recid=14 stamp=713397378archive log filename=/opt/oracle/product/10g/dbs/arch1_11_713269994.dbf thread=1 sequence=11channel clone_default: deleting archive log(s)archive log filename=/opt/oracle/product/10g/dbs/arch1_11_713269994.dbf recid=17 stamp=713397379archive log filename=/opt/oracle/product/10g/dbs/arch1_12_713269994.dbf thread=1 sequence=12channel clone_default: deleting archive log(s)archive log filename=/opt/oracle/product/10g/dbs/arch1_12_713269994.dbf recid=16 stamp=713397379archive log filename=/opt/oracle/product/10g/dbs/arch1_13_713269994.dbf thread=1 sequence=13channel clone_default: deleting archive log(s)archive log filename=/opt/oracle/product/10g/dbs/arch1_13_713269994.dbf recid=15 stamp=713397378archive log filename=+DG1/testasm/1_14_713269994.dbf thread=1 sequence=14archive log filename=+DG1/testasm/1_15_713269994.dbf thread=1 sequence=15archive log filename=+DG1/testasm/1_16_713269994.dbf thread=1 sequence=16archive log filename=+DG1/testasm/1_17_713269994.dbf thread=1 sequence=17archive log filename=+DG1/testasm/1_18_713269994.dbf thread=1 sequence=18media recovery complete, elapsed time: 00:00:05Finished recover at 11-MAR-10database openedcontents of Memory Script:# export the tablespaces in the recovery sethost exp userid =/(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/opt/oracle/product/10g/bin/oracle)(ARGV0=oraclepudo)(ARGS=(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)(ENVS=ORACLE_SID=pudo)(CONNECT_DATA=(SID=pudo) as sysdba point_in_time_recover=y tablespaces= USER01 file=tspitr_a.dmp;# shutdown clone before importshutdown clone immediate# import the tablespaces in the recovery sethost imp userid =/ as sysdba point_in_time_recover=y file=tspitr_a.dmp;# online/offline the tablespace importedsql alter tablespace USER01 online;sql alter tablespace USER01 offline;# enable autobackups in case user does open resetlogs from RMAN after TSPITRsql begin dbms_backup_restore.AutoBackupFlag(TRUE); end;# resync catalog after tspitr finishedresync catalog;executing Memory ScriptExport: Release 10.2.0.1.0 - Production on Thu Mar 11 21:56:42 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)Note: table data (rows) will not be exportedAbout to export Tablespace Point-in-time Recovery objects.For tablespace USER01 . exporting cluster definitions. exporting table definitions. . exporting table CUSTOMERS. exporting referential integrity constraints. exporting triggers. end point-in-time recoveryExport terminated successfully without warnings.host command completedatabase closeddatabase dismountedOracle instance shut downImport: Release 10.2.0.1.0 - Production on Thu Mar 11 21:57:10 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathAbout to import Tablespace Point-in-ti