Oracle RMAN实现“一键式”表空间TSPITR

从Oracle 10g之后，小规模数据误删除、误操作其实已经有了很好的解决方案，就是flashback技术。Flashback Query和Flashback Drop，就可以从数据记录和数据表这类比较细的粒度。在实际业务场景中，相对比“天塌地陷”的全库恢复场景，单Schema、单Tablespace甚至单数据表的局部恢复更加有施展的空间。出现误操作的时候，用户往往希望一个或者部分数据表恢复到过去的一个时间点。同时又不希望将全库恢复到过去时间，丢失部分数据。RMAN提供了一种实现所谓TSPITR（Tablespace Point-In-Time Recovery）的技术，通过简单的一个语句，就可以在主库不停库（很吸引人）的情况下，利用备份集和连续的归档日志，实现表空间级别的定点恢复。本篇主要介绍进行RMAN TSPITR操作的步骤方法和一些技术细节点，供需要的朋友待查。1、环境背景介绍笔者选择Oracle 11g进行试验，具体版本为11.2.0.4版本。数据库模式为归档模式。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - ProductionSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 5Next log sequence to archive 8Current log sequence 8尝试构建一个全新schema，在一个全新的tablespace中。实验数据选择scott用户的数据。SQL> create tablespace testtbl datafile size 10m autoextend on extent management local uniform size 1m segment space management auto;Tablespace createdSQL> create user test identified by test default tablespace testtbl;User createdSQL> grant resource, connect to test;Grant succeeded使用之前导出的scott数据，进行remap导入。[oracle@SimpleLinux ~]$ impdp "/ as sysdba" dumpfile=scott_20150618.dmp remap_schema=scott:test remap_tablespace=users:testtblImport: Release 11.2.0.4.0 - Production on Thu Jun 18 09:50:17 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Miningand Real Application Testing optionsMaster table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded（篇幅原因，有省略……）Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMAJob "SYS"."SYS_IMPORT_FULL_01" completed with 1 error（s） at Thu Jun 18 09:50:28 2015 elapsed 0 00:00:08SQL> select owner ,tablespace_name, count（*） from dba_segments where owner="TEST" group by owner, tablespace_name;OWNER TABLESPACE_NAME COUNT（*）------------------------------ ------------------------------ ----------TEST TESTTBL 5两个数据表。SQL> select count（*） from test.emp; COUNT（*）---------- 14SQL> select count（*） from test.dept; COUNT（*）---------- 42、数据备份和故障发生归档模式下，需要有一份完全的备份，才能实现恢复操作。RMAN> backup database plus archivelog delete input;Starting backup at 18-JUN-15current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=47 device type=DISK（篇幅原因，有省略…….）Starting Control File and SPFILE Autobackup at 18-JUN-15piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 18-JUN-15SQL> select group#, sequence# from v$log where status="CURRENT"; GROUP# SEQUENCE#---------- ---------- 2 17此时，操作时间为：SQL> select to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） a from dual;A--------------------2015-06-18 10:51:19 过了几个小时，误操作发生。SQL> select to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） a from dual;A--------------------2015-06-18 13:47:29SQL> select group#, sequence# from v$log where status="CURRENT"; GROUP# SEQUENCE#---------- ---------- 3 19误删除发生SQL> drop table test.emp;Table droppedSQL> drop table test.dept;Table dropped--Flashback Drop的机会也没有了。SQL> purge dba_recyclebin;Done3、RMAN进行表空间TSPITR单表表空间移动，要确保表空间内容“内包”，也就是不会有其他表空间牵扯其中。这个操作我们在可移动表空间的演示中也会进行。SQL> exec dbms_tts.transport_set_check（"testtbl",true）;PL/SQL procedure successfully completedSQL> select * from transport_set_violations;VIOLATIONS--------------------------------------------------------------------------------下面进行实际恢复操作，首先需要在目录中创建出一个可访问的空间，进行临时文件容纳。[root@SimpleLinux ~]# cd /[root@SimpleLinux /]# mkdir -p /extend/oradata/aux[root@SimpleLinux /]# chown -R oracle:oinstall /extend/[root@SimpleLinux /]# ls -l | grep extenddrwxr-xr-x. 3 oracle oinstall 4096 Jun 18 10:21 extend启动RMAN，登录后执行recover tablespace操作，指定出恢复时间。注意：RMAN的recover tablespace命令本质上是执行一系列的指令脚本，将利用RMAN恢复过程自动化，无需人工介入。[oracle@SimpleLinux ~]$ rman nocatalogRecovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 18 13:48:44 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.RMAN> connect target /connected to target database: ORA11G （DBID=4239941846）using target database control file instead of recovery catalogRMAN> recover tablespace testtbl until time "to_date（"2015-06-18 10:51:19","yyyy-mm-dd hh24:mi:ss"）" auxiliary destination "/extend/oradata/aux";Starting recover at 18-JUN-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=44 device type=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-timeList of tablespaces expected to have UNDO segmentsTablespace SYSTEMTablespace UNDOTBS1Creating automatic instance, with SID="DDfe" –在目录中创建出一个虚拟的Oracle实例--启动实例的参数都准备好了。initialization parameters used for automatic instance:db_name=ORA11Gdb_unique_name=DDfe_tspitr_ORA11Gcompatible=11.2.0.4.0db_block_size=8192db_files=200sga_target=1Gprocesses=80db_create_file_dest=/extend/oradata/auxlog_archive_dest_1="location=/extend/oradata/aux"#No auxiliary parameter file usedstarting up automatic instance ORA11GOracle instance startedTotal System Global Area 1071333376 bytesFixed Size 1369420 bytesVariable Size 281021108 bytesDatabase Buffers 784334848 bytesRedo Buffers 4608000 bytesAutomatic instance created--自包检查Running TRANSPORT_SET_CHECK on recovery set tablespacesTRANSPORT_SET_CHECK completed successfully--第一块脚本片段，利用备份集合和归档日志，恢复数据到虚拟实例上。contents of Memory Script:{# set requested point in timeset until time "to_date（"2015-06-18 10:51:19","yyyy-mm-dd hh24:mi:ss"）";# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone "alter database mount clone database";# archive current online log sql "alter system archive log current";# avoid unnecessary autobackups for structural changes during TSPITRsql "begin dbms_backup_restore.AutoBackupFlag（FALSE）; end;";}executing Memory Scriptexecuting command: SET until clauseStarting restore at 18-JUN-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=19 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp tag=TAG20150618T104402channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=/extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctlFinished restore at 18-JUN-15sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag（FALSE）; end;contents of Memory Script:{# set requested point in timeset until time "to_date（"2015-06-18 10:51:19","yyyy-mm-dd hh24:mi:ss"）";plsql <<<-- tspitr_2declare sqlstatement varchar2（512）; offline_not_needed exception; pragma exception_init（offline_not_needed, -01539）;begin sqlstatement := "alter tablespace "|| "TESTTBL" ||" offline immediate"; krmicd.writeMsg（6162, sqlstatement）; krmicd.execSql（sqlstatement）;exception when offline_not_needed then null;end; >>>;# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile 1 to new;set newname for clone datafile 5 to new;set newname for clone datafile 3 to new;set newname for clone datafile 2 to new;set newname for clone tempfile 1 to new;set newname for datafile 7 to "/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf";# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile 1, 5, 3, 2, 7;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clausesql statement: alter tablespace TESTTBL offline immediateexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 18-JUN-15using channel ORA_AUX_DISK_1--只恢复system, sysaux, undo和目标testtbl表空间。channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile（s） to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00005 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkp tag=TAG20150618T104315channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 18-JUN-15datafile 1 switched to datafile copyinput datafile copy RECID=5 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=6 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=7 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=8 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbf--启动虚拟实例数据，完成恢复。contents of Memory Script:{# set requested point in timeset until time "to_date（"2015-06-18 10:51:19","yyyy-mm-dd hh24:mi:ss"）";# online the datafiles restored or switchedsql clone "alter database datafile 1 online";sql clone "alter database datafile 5 online";sql clone "alter database datafile 3 online";sql clone "alter database datafile 2 online";sql clone "alter database datafile 7 online";# recover and open resetlogsrecover clone database tablespace "TESTTBL", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile 1 onlinesql statement: alter database datafile 5 onlinesql statement: alter database datafile 3 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 7 onlineStarting recover at 18-JUN-15using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 17 is already on disk as file /u01/app/fast_recovery_area/ORA11G/archivelog/2015_06_18/o1_mf_1_17_br4pw26f_.arcchannel ORA_AUX_DISK_1: starting archived log restore to default destinationchannel ORA_AUX_DISK_1: restoring archived logarchived log thread=1 sequence=16channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkp tag=TAG20150618T104401channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/extend/oradata/aux/1_16_846081044.dbf thread=1 sequence=16channel clone_default: deleting archived log（s）archived log file name=/extend/oradata/aux/1_16_846081044.dbf RECID=27 STAMP=882712328archived log file name=/u01/app/fast_recovery_area/ORA11G/archivelog/2015_06_18/o1_mf_1_17_br4pw26f_.arc thread=1 sequence=17media recovery complete, elapsed time: 00:00:01Finished recover at 18-JUN-15database opened--将表空间导出来contents of Memory Script:{# make read only the tablespace that will be exportedsql clone "alter tablespace TESTTBL read only";# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ""/extend/oradata/aux""";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ""/extend/oradata/aux""";}executing Memory Scriptsql statement: alter tablespace TESTTBL read onlysql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ""/extend/oradata/aux""sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ""/extend/oradata/aux""Performing export of metadata... EXPDP> Starting "SYS"."TSPITR_EXP_DDfe": EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK EXPDP> Master table "SYS"."TSPITR_EXP_DDfe" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_DDfe is: EXPDP> /extend/oradata/aux/tspitr_DDfe_54026.dmp EXPDP> ****************************************************************************** EXPDP> Datafiles required for transportable tablespace TESTTBL: EXPDP> /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf EXPDP> Job "SYS"."TSPITR_EXP_DDfe" successfully completed at Thu Jun 18 13:54:10 2015 elapsed 0 00:01:13Export completed--关闭虚拟实例contents of Memory Script:{# shutdown clone before importshutdown clone immediate# drop target tablespaces before importing them backsql "drop tablespace TESTTBL including contents keep datafiles cascade constraints";}executing Memory Scriptdatabase closeddatabase dismountedOracle instance shut down--把原来的表空间删除掉sql statement: drop tablespace TESTTBL including contents keep datafiles cascade constraints--恢复导入数据Performing import of metadata... IMPDP> Master table "SYS"."TSPITR_IMP_DDfe" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_DDfe": IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK IMPDP> Job "SYS"."TSPITR_IMP_DDfe" successfully completed at Thu Jun 18 13:54:54 2015 elapsed 0 00:00:10Import completed--第三部分脚本，恢复表空间contents of Memory Script:{# make read write and offline the imported tablespacessql "alter tablespace TESTTBL read write";sql "alter tablespace TESTTBL offline";# enable autobackups after TSPITR is finishedsql "begin dbms_backup_restore.AutoBackupFlag（TRUE）; end;";}executing Memory Scriptsql statement: alter tablespace TESTTBL read writesql statement: alter tablespace TESTTBL offlinesql statement: begin dbms_backup_restore.AutoBackupFlag（TRUE）; end;Removing automatic instanceAutomatic instance removedauxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_br4q4ojl_.tmp deletedauxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_4_br4q4j17_.log deletedauxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_3_br4q4ghl_.log deletedauxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_2_br4q4dt5_.log deletedauxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_1_br4q4brh_.log deletedauxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbf deletedauxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbf deletedauxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbf deletedauxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbf deletedauxiliary instance file /extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctl deletedFinished recover at 18-JUN-15RMAN>上面的步骤可以归纳为如下：ü 在目录中创建一个虚拟临时数据库实例；ü RMAN自动辅助库AUX创建，利用源数据库的备份集合和归档日志进行全库不完全恢复。恢复范围包括system、sysaux、undo和目标表空间；ü 借助Expdp和Impdp，使用可移动表空间原理步骤，将目标表空间数据导出并导回源数据库；ü 恢复完成；下面可以看实际效果。SQL> select to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） a from dual;A--------------------2015-06-18 13:55:43SQL> select group#, sequence# from v$log where status="CURRENT"; GROUP# SEQUENCE#---------- ---------- 1 20尝试访问数据表。SQL> select count（*） from test.emp;select count（*） from test.empORA-00376: 无法读取数据文件 7ORA-01110: 数据文件 7: "/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf"此时，表空间被offline，需要手工进行online操作。SQL> select tablespace_name, status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETESTTBL OFFLINE6 rows selectedSQL> alter tablespace testtbl online;Tablespace alteredSQL> select tablespace_name, status from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETESTTBL ONLINE6 rows selectedSQL> select count（*） from test.emp; COUNT（*）---------- 14SQL> select count（*） from test.dept; COUNT（*）---------- 45、结论Oracle RMAN的TSPITR在实际中还是很有用处的，特别是当我们没有办法使用Flashback技术（如已经发生了Undo覆盖）的时候。Tablespace的粒度还是比较大，希望以后可以实现Schema或者数据表table级别的的不完全恢复。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址