一.表空间按时间点恢复
RMAN的自动表空间按时间点恢复(TSPITR)能让你将数据库中的一个或多个表空间快速恢复到之前的某个时间点,而不会影响数据库中的其它表空间和对象。
为了有效地使用表空间按时间点恢复(TSPITR),需要理解你需要解决的问题是什么,使用TSPITR的主要原因是什么,在执行TSPITR时RMAN将会执行那些操作,何时以及如何使用TSPITR,使用TSPITR有那些限制。
二.执行TSPITR的操作步骤
为了使用RMAN和自动辅助实例来对恢复集执行TSPITR,需要对TSPITR进行一些准备工作,然后执行recover tablespace命令来指定要恢复的表空间和按时间点恢复的目标时间和辅助目标(如果需要的话)。
执行TSPITR,RMAN将会执行以下步骤:
1.如果没有连接到辅助实例,RMAN将创建一个辅助实例,启动并连接到辅助实例。
2.在目标数据库中将要被恢复的表空间置于脱机状态。
3.对辅助实例使用恢复目标时间之前的控制文件备份来还原控制文件。
4.从恢复集和辅助集中将数据文件还原到辅助实例。还原的数据文件可以存储在你指定的目录中或者是文件的原始目录中(对于恢复集文件)或者在辅助目录中(对于辅助集文件,如果你使用了有auxliary destination参数的recover tablespace命令)。
5.在辅助实例中将数据文件还原和恢复到指定的目标时间点。
6.使用resetlogs选项将辅助实例打开
7.导出目标数据库中要恢复表空间的数据字典元数据
8.关闭辅助实例
9.在目标数据库中执行switch命令,因为目标数据库的控制文件现在指示的是在辅助实例中被恢复的恢复集中的数据文件
10.将辅助数据库实的数据字典元数据导入到目标数据库中让恢复的对象可以被访问。
11.删除所有辅助集数据文件
执行完以上步骤,TSPITR处理就已经完成了。对于目标数据库恢复集中的数据文件已经被返回到指定的恢复目标时间点。
三.TSPITR的执行方式
选择好要恢复的表空间和恢复目标时间后,就可以执行RMAN的TSPITR。在执行TSPITR时可以有以下不同的选择:
1.完全自动执行TSPITR--在指定辅助目录后就可以让RMAN来管理TSPITR的所有方面。这是执行TSPITR最简单的方式,除非你需要在执行TSPITR后更好的控制恢复集文件的目录或者在执行TSPITR时控制辅助集文件或者控制通道配置或者控制辅助实例的一些其它方面,否则建议使用这种方式来执行TSPITR。
2.使用自定义自动辅助实例执行TSPITR--在完全自动TSPITR的基础上,自定义一个或多个方面的RMAN行为,比如辅助集目录或者恢复集文件,或者指定初始化参数或者辅助实例的通道配置等。
3.使用自己的辅助实例执行TSPITR--在这种情况下要手动设置,启动,停止和清除辅助实例,也可以使用自动辅助实例自定义TSPITR中的一些方法来管理TSPITR。
完全自动执行TSPITR
当完全自动执行TSPITR时,让RMAN来管理整个过程,并且有两个条件:
1.必须给RMAN指定辅助目录让辅助实例的辅助集数据文件和其它文件使用。
2.必须在目标实例上配置任何需要的通道(当目标实例执行TSPITR时,辅助实例使用相同的通道配置)。
对于TSPITR的基础配置在目标数据库上进行。当执行TSPITR时,恢复集数据文件会被写到目标数据库的当前目录。当从备份中还原文件时在目标数据库上有效的相同配置通道将被辅助实例所用。辅助集数据文件和其它辅助实例文件,会被存储在辅助目录中。
自动TSPITR的辅助目录
oracle建议在使用辅助实例时使用辅助目录。即使你使用其它的方法来重命名一些或者所有的辅助集数据文件,通过设置auxliary destination参数来为没有指定名称的辅助集数据文件提供一个默认的目录。为了指定辅助目录,在磁盘上找到有足够空间的目录来处理辅助集数据文件。在recover tablespace命令中使用auxliary destination参数来指定辅助目录。
这里我使用oracle 10g进行完全自动执行TSPITR的实验,下面来准备实验环境:
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG
创建实验表空间tspitr,用户tspitr,实验表tspitr:
SQL> create tablespace tspitr datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' size 100M autoextend off extent management local segment space management auto; Tablespace created. SQL> create user tspitr identified by "tspitr" default tablespace tspitr temporary tablespace temp; User created. SQL> grant connect,resource to tspitr; Grant succeeded.
这里为了实例的方便使用给tspitr用户授予了DBA权限
SQL> grant dba to tspitr; Grant succeeded. SQL> conn tspitr/tspitr Connected. SQL> create table tspitr as select * from dba_objects; Table created. SQL> select count(*) from tspitr; COUNT(*) ---------- 50678 SQL> select sequence#, status from v$log; SEQUENCE# STATUS ---------- ---------------- 28 INACTIVE 29 CURRENT 27 INACTIVE
在创建完实验表tspitr后,记录一下当前的logseq号为29。并对数据库进行一次全备份。
[root@oracle11g ~]# su - oracle [oracle@oracle11g ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' [oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 16 10:34:12 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: TEST (DBID=2168949517) connected to recovery catalog database RMAN> backup as backupset database plus archivelog delete all input; Starting backup at 16-MAR-15 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=13 recid=20 stamp=872692763 input archive log thread=1 sequence=14 recid=21 stamp=873390068 input archive log thread=1 sequence=15 recid=22 stamp=873477705 input archive log thread=1 sequence=16 recid=23 stamp=873557208 input archive log thread=1 sequence=17 recid=24 stamp=873628193 input archive log thread=1 sequence=18 recid=25 stamp=873881475 input archive log thread=1 sequence=19 recid=26 stamp=873882506 input archive log thread=1 sequence=20 recid=27 stamp=873886639 input archive log thread=1 sequence=21 recid=28 stamp=873886719 input archive log thread=1 sequence=22 recid=29 stamp=873969562 input archive log thread=1 sequence=23 recid=30 stamp=873969604 input archive log thread=1 sequence=24 recid=31 stamp=873974555 input archive log thread=1 sequence=25 recid=32 stamp=873974650 input archive log thread=1 sequence=26 recid=33 stamp=874234950 input archive log thread=1 sequence=27 recid=34 stamp=874315417 input archive log thread=1 sequence=28 recid=35 stamp=874486326 input archive log thread=1 sequence=29 recid=36 stamp=874492547 channel ORA_DISK_1: starting piece 1 at 16-MAR-15 channel ORA_DISK_1: finished piece 1 at 16-MAR-15 piece handle=/u02/ora_test874492549_351 tag=TAG20150316T103548 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26 channel ORA_DISK_1: deleting archive log(s) archive log filename=/u02/1_13_870806981.dbf recid=20 stamp=872692763 archive log filename=/u02/1_14_870806981.dbf recid=21 stamp=873390068 archive log filename=/u02/1_15_870806981.dbf recid=22 stamp=873477705 archive log filename=/u02/1_16_870806981.dbf recid=23 stamp=873557208 archive log filename=/u02/1_17_870806981.dbf recid=24 stamp=873628193 archive log filename=/u02/1_18_870806981.dbf recid=25 stamp=873881475 archive log filename=/u02/1_19_870806981.dbf recid=26 stamp=873882506 archive log filename=/u02/1_20_870806981.dbf recid=27 stamp=873886639 archive log filename=/u02/1_21_870806981.dbf recid=28 stamp=873886719 archive log filename=/u02/1_22_870806981.dbf recid=29 stamp=873969562 archive log filename=/u02/1_23_870806981.dbf recid=30 stamp=873969604 archive log filename=/u02/1_24_870806981.dbf recid=31 stamp=873974555 archive log filename=/u02/1_25_870806981.dbf recid=32 stamp=873974650 archive log filename=/u02/1_26_870806981.dbf recid=33 stamp=874234950 archive log filename=/u02/1_27_870806981.dbf recid=34 stamp=874315417 archive log filename=/u02/1_28_870806981.dbf recid=35 stamp=874486326 archive log filename=/u02/1_29_870806981.dbf recid=36 stamp=874492547 Finished backup at 16-MAR-15 Starting backup at 16-MAR-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf channel ORA_DISK_1: starting piece 1 at 16-MAR-15 channel ORA_DISK_1: finished piece 1 at 16-MAR-15 piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 Finished backup at 16-MAR-15 Starting backup at 16-MAR-15 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=30 recid=37 stamp=874492654 channel ORA_DISK_1: starting piece 1 at 16-MAR-15 channel ORA_DISK_1: finished piece 1 at 16-MAR-15 piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 channel ORA_DISK_1: deleting archive log(s) archive log filename=/u02/1_30_870806981.dbf recid=37 stamp=874492654 Finished backup at 16-MAR-15 Starting Control File and SPFILE Autobackup at 16-MAR-15 piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 16-MAR-15 RMAN> list backup ; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 1197 106.98M DISK 00:00:23 2015-03-16 10:36:12 BP Key: 1198 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103548 Piece Name: /u02/ora_test874492549_351 List of Archived Logs in backup set 1197 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 13 479270 2015-02-13 11:15:05 507890 2015-02-26 14:39:21 1 14 507890 2015-02-26 14:39:21 537468 2015-03-03 16:21:06 1 15 537468 2015-03-03 16:21:06 565660 2015-03-04 16:41:41 1 16 565660 2015-03-04 16:41:41 597853 2015-03-05 14:46:22 1 17 597853 2015-03-05 14:46:22 627695 2015-03-06 10:29:51 1 18 627695 2015-03-06 10:29:51 662451 2015-03-09 08:51:08 1 19 662451 2015-03-09 08:51:08 663522 2015-03-09 09:08:25 1 20 663522 2015-03-09 09:08:25 666107 2015-03-09 10:17:18 1 21 666107 2015-03-09 10:17:18 666153 2015-03-09 10:18:39 1 22 666153 2015-03-09 10:18:39 692585 2015-03-10 09:19:20 1 23 692585 2015-03-10 09:19:20 692853 2015-03-10 09:20:04 1 24 692853 2015-03-10 09:20:04 695060 2015-03-10 10:42:34 1 25 695060 2015-03-10 10:42:34 695112 2015-03-10 10:44:09 1 26 695112 2015-03-10 10:44:09 726710 2015-03-13 11:02:26 1 27 726710 2015-03-13 11:02:26 759353 2015-03-14 09:23:34 1 28 759353 2015-03-14 09:23:34 786212 2015-03-16 08:52:00 1 29 786212 2015-03-16 08:52:00 789814 2015-03-16 10:35:43 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 1226 Full 612.91M DISK 00:01:14 2015-03-16 10:37:32 BP Key: 1244 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103617 Piece Name: /u02/ora_test874492578_361 List of Datafiles in backup set 1226 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/system01.dbf 2 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/users01.dbf 5 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/example01.dbf 6 Full 789843 2015-03-16 10:36:18 /u01/app/oracle/oradata/test/tspitr01.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 1268 2.50K DISK 00:00:01 2015-03-16 10:37:37 BP Key: 1275 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103735 Piece Name: /u02/ora_test874492656_371 List of Archived Logs in backup set 1268 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 30 789814 2015-03-16 10:35:43 789871 2015-03-16 10:37:33 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 1286 Full 6.80M DISK 00:00:01 2015-03-16 10:37:40 BP Key: 1288 Status: AVAILABLE Compressed: NO Tag: TAG20150316T103739 Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp Control File Included: Ckp SCN: 789893 Ckp time: 2015-03-16 10:37:39 SPFILE Included: Modification time: 2015-03-16 08:52:02
模拟误操作:
记录当前时间点的logseq号为31
SQL> select sequence#, status from v$log; SEQUENCE# STATUS ---------- ---------------- 31 CURRENT 29 INACTIVE 30 INACTIVE
进行日志切换,记录truncate操作之前的logseq号为32,并执行truncate操作
SQL> alter system switch logfile; System altered. SQL> select sequence#, status from v$log; SEQUENCE# STATUS ---------- ---------------- 31 ACTIVE 32 CURRENT 30 INACTIVE SQL> truncate table tspitr; Table truncated. SQL> alter system switch logfile; System altered. SQL> select sequence#, status from v$log; SEQUENCE# STATUS ---------- ---------------- 31 ACTIVE 32 ACTIVE 33 CURRENT
在执行truncate table tspitr操作之后,进行日志切换,并记录truncate操作之后的当前logseq号为33。我们这里的恢复目标是将表空间tspitr恢复到logseq=32的时间点。为了执行自动RMAN TSPITR,启动RMAN客户端,连接到目标数据库和恢复目录(如果有的话)。注意,为了使用自动TSPITR在启动RMAN客户端时不要连接到辅助实例。如果在执行recover tablespace命令时,RMAN连接到一个辅助实例,RMAN会假设你将试管理自己单独的辅助实例。
创建辅助目录:
[oracle@oracle11g oradata]$ mkdir auxiliary [oracle@oracle11g oradata]$ ls -lrt total 8 drwxr-x--- 2 oracle oinstall 4096 Mar 16 10:22 test drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:55 auxiliary
如果已经配置了通道,RMAN可以在主实例上从备份中执行还原,那么通过执行recover tablespace ...until ...命令就能执行TSPITR了。这里使用目标数据库中的缺省磁盘通道。
执行下面的命令将表空间tspitr恢复到logseq=32的时间点:
RMAN> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary'; Starting recover at 2015-03-16 11:27:37 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=143 devtype=DISK Creating automatic instance, with SID='hswB' initialization parameters used for automatic instance: db_name=TEST compatible=10.2.0.5.0 db_block_size=8192 db_files=200 db_unique_name=tspitr_TEST_hswB sga_target=180M processes=50 #No auxiliary parameter file used db_create_file_dest=/u01/app/oracle/oradata/auxiliary control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f starting up automatic instance TEST Oracle instance started Total System Global Area 188743680 bytes Fixed Size 1272720 bytes Variable Size 62915696 bytes Database Buffers 121634816 bytes Redo Buffers 2920448 bytes Automatic instance created contents of Memory Script: { # set the until clause set until logseq 32 thread 1; # restore the controlfile restore clone controlfile; # 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'; # avoid unnecessary autobackups for structural changes during TSPITR sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;'; # resync catalog after controlfile restore resync catalog; } executing Memory Script executing command: SET until clause Starting restore at 2015-03-16 11:27:38 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=47 devtype=DISK channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f Finished restore at 2015-03-16 11:27:41 sql statement: alter database mount clone database sql statement: alter system archive log current sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end; starting full resync of recovery catalog full resync complete contents of Memory Script: { # generated tablespace point-in-time recovery script # set the until clause set until logseq 32 thread 1; plsql < <>>; # set an omf destination filename for restore set newname for clone datafile 1 to new; # set an omf destination filename for restore set newname for clone datafile 2 to new; # set an omf destination tempfile set newname for clone tempfile 1 to new; # set a destination filename for restore set newname for datafile 6 to "/u01/app/oracle/oradata/test/tspitr01.dbf"; # rename all tempfiles switch clone tempfile all; # 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", "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 Script executing command: SET until clause sql statement: alter tablespace TSPITR offline for recover executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 2015-03-16 11:27:54 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_%u_.dbf restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_%u_.dbf restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05 Finished restore at 2015-03-16 11:29:00 datafile 1 switched to datafile copy input datafile copy recid=19 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf datafile 2 switched to datafile copy input datafile copy recid=20 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf sql statement: alter database datafile 1 online sql statement: alter database datafile 2 online sql statement: alter database datafile 6 online Starting recover at 2015-03-16 11:29:01 using channel ORA_AUX_DISK_1 starting media recovery archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=30 channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30 channel clone_default: deleting archive log(s) archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874495743 archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31 media recovery complete, elapsed time: 00:00:01 Finished recover at 2015-03-16 11:29:05 database opened contents of Memory Script: { # export the tablespaces in the recovery set host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclehswB\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=hswB^'\)\)\(CONNECT_DATA=\(SID=hswB\)\)\) as sysdba\" point_in_time_recover=y tablespaces= TSPITR file= tspitr_a.dmp'; # shutdown clone before import shutdown clone immediate # import the tablespaces in the recovery set host 'imp userid =\"sys/zzh_2046@test 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"; # enable autobackups in case user does open resetlogs from RMAN after TSPITR sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;'; # resync catalog after tspitr finished resync catalog; } executing Memory Script Export: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:30 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set 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 TSPITR . exporting referential integrity constraints . exporting triggers . end point-in-time recovery Export terminated successfully without warnings. host command complete database closed database dismounted Oracle instance shut down Import: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:46 2015 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path About to import Tablespace Point-in-time Recovery objects... import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS's objects into SYS . importing TSPITR's objects into TSPITR . . importing table "TSPITR" . importing SYS's objects into SYS Import terminated successfully without warnings. host command complete sql statement: alter tablespace TSPITR online sql statement: alter tablespace TSPITR offline sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end; starting full resync of recovery catalog full resync complete Removing automatic instance Automatic instance removed auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_bjdmjfov_.tmp deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_1_bjdmj1o6_.log deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_2_bjdmj44r_.log deleted auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_3_bjdmj6f1_.log deleted Finished recover at 2015-03-16 11:29:59 SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TSPITR OFFLINE 7 rows selected.
TSPITR成功执行完成,RMAN会将表空间脱机,从备份中还原并在辅助实例中将表空间恢复到指定的目标时间点。在整个过程执行完成后,表空间仍然是脱机状态。所有的辅助集数据文件和其它的辅助实例文件会从辅助目录中删除。
TSPITR执行成功后需要执行的操作:
1.必须要备份恢复的表空间然后将表空间置于联机状态。
2.在TSPITR执行成功后立即备份恢复成功的表空间是非常重要的。在对表空间执行TSPITR后,在TSPITR恢复目标时间之前的表空间备份将不能再使用。如果不对恢复的表空间进行备份就开始使用,你将在没有这些表空间备份的情况下运行数据库。对于这个例子,执行以下命令来备份tspitr表空间或者对整个数据库进行备份:
RMAN> backup tablespace tspitr; Starting backup at 2015-03-16 11:40:53 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf channel ORA_DISK_1: starting piece 1 at 2015-03-16 11:40:54 channel ORA_DISK_1: finished piece 1 at 2015-03-16 11:40:55 piece handle=/u02/ora_test874496454_391 tag=TAG20150316T114053 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2015-03-16 11:40:55 Starting Control File and SPFILE Autobackup at 2015-03-16 11:40:55 piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874496456_bjdn69mh_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2015-03-16 11:40:59
然后可以安全的将表空间置于联机状态:
RMAN> sql 'alter tablespace tspitr online'; sql statement: alter tablespace tspitr online starting full resync of recovery catalog full resync complete
现在恢复的表空间就可以供用户使用了。
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TSPITR ONLINE 7 rows selected. SQL> show user USER is "TSPITR" SQL> select count(*) from tspitr; COUNT(*) ---------- 50678
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-1475175/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-1475175/