您当前的位置:首页 > oracle

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

从 10g之后,小规模数据误删除、误操作其实已经有了很好的解决方案,就是flashback技术。Flashback Query和Flashback Drop,就可以从数据记录和数据表这类比较细的粒度。MqPlinux系统宝典

在实际业务场景中,相对比“天塌地陷”的全库恢复场景,单Schema、单Tablespace甚至单数据表的局部恢复更加有施展的空间。出现误操作的时候,用户往往希望一个或者部分数据表恢复到过去的一个时间点。同时又不希望将全库恢复到过去时间,丢失部分数据。MqPlinux系统宝典

RMAN提供了一种实现所谓TSPITR(Tablespace Point-In-Time Recovery)的技术,通过简单的一个语句,就可以在主库不停库(很吸引人)的情况下,利用备份集和连续的归档日志,实现表空间级别的定点恢复。MqPlinux系统宝典

本篇主要介绍进行RMAN TSPITR操作的步骤方法和一些技术细节点,供需要的朋友待查。MqPlinux系统宝典

1、环境背景介绍MqPlinux系统宝典

笔者选择Oracle 11g进行试验,具体版本为11.2.0.4版本。数据库模式为归档模式。MqPlinux系统宝典

SQL> select * from v$version;MqPlinux系统宝典

BANNERMqPlinux系统宝典

--------------------------------------------------------------------------------MqPlinux系统宝典

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionMqPlinux系统宝典

PL/SQL Release 11.2.0.4.0 - ProductionMqPlinux系统宝典

CORE    11.2.0.4.0 ProductionMqPlinux系统宝典

TNS for Linux: Version 11.2.0.4.0 - ProductionMqPlinux系统宝典

NLSRTL Version 11.2.0.4.0 - ProductionMqPlinux系统宝典

SQL> archive log list;MqPlinux系统宝典

Database log mode              Archive ModeMqPlinux系统宝典

Automatic archival            EnabledMqPlinux系统宝典

Archive destination            USE_DB_RECOVERY_FILE_DESTMqPlinux系统宝典

Oldest online log sequence    5MqPlinux系统宝典

Next log sequence to archive  8MqPlinux系统宝典

Current log sequence          8MqPlinux系统宝典

尝试构建一个全新schema,在一个全新的tablespace中。实验数据选择scott用户的数据。MqPlinux系统宝典

SQL> create tablespace testtbl datafile size 10m autoextend on extent management local uniform size 1m segment space management auto;MqPlinux系统宝典

Tablespace createdMqPlinux系统宝典

SQL> create user test identified by test default tablespace testtbl;MqPlinux系统宝典

User createdMqPlinux系统宝典

SQL> grant resource, connect to test;MqPlinux系统宝典

Grant succeededMqPlinux系统宝典

使用之前导出的scott数据,进行remap导入。MqPlinux系统宝典

[oracle@SimpleLinux ~]$ impdp /"/ as sysdba/" dumpfile=scott_20150618.dmp remap_schema=scott:test remap_tablespace=users:testtblMqPlinux系统宝典

Import: Release 11.2.0.4.0 - Production on Thu Jun 18 09:50:17 2015MqPlinux系统宝典

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.MqPlinux系统宝典

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionMqPlinux系统宝典

With the Partitioning, Oracle Label Security, OLAP, Data MiningMqPlinux系统宝典

and Real Application Testing optionsMqPlinux系统宝典

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloadedMqPlinux系统宝典

(篇幅原因,有省略……)MqPlinux系统宝典

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTMqPlinux系统宝典

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMAMqPlinux系统宝典

Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Jun 18 09:50:28 2015 elapsed 0 00:00:08MqPlinux系统宝典

SQL> select owner ,tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;MqPlinux系统宝典

OWNER    TABLESPACE_NAME                  COUNT(*)MqPlinux系统宝典

------------------------------ ------------------------------ ----------MqPlinux系统宝典

TEST        TESTTBL                                5MqPlinux系统宝典

两个数据表。MqPlinux系统宝典

SQL> select count(*) from test.emp;MqPlinux系统宝典

  COUNT(*)MqPlinux系统宝典

----------MqPlinux系统宝典

        14MqPlinux系统宝典

SQL> select count(*) from test.dept;MqPlinux系统宝典

  COUNT(*)MqPlinux系统宝典

----------MqPlinux系统宝典

        4MqPlinux系统宝典

2、数据备份和故障发生MqPlinux系统宝典

归档模式下,需要有一份完全的备份,才能实现恢复操作。MqPlinux系统宝典

RMAN> backup database plus archivelog delete input;MqPlinux系统宝典

Starting backup at 18-JUN-15MqPlinux系统宝典

current log archivedMqPlinux系统宝典

allocated channel: ORA_DISK_1MqPlinux系统宝典

channel ORA_DISK_1: SID=47 device type=DISKMqPlinux系统宝典

(篇幅原因,有省略…….)MqPlinux系统宝典

Starting Control File and SPFILE Autobackup at 18-JUN-15MqPlinux系统宝典

piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp comment=NONEMqPlinux系统宝典

Finished Control File and SPFILE Autobackup at 18-JUN-15MqPlinux系统宝典

SQL> select group#, sequence# from v$log where status='CURRENT';MqPlinux系统宝典

    GROUP#  SEQUENCE#MqPlinux系统宝典

---------- ----------MqPlinux系统宝典

        2        17MqPlinux系统宝典

此时,操作时间为:MqPlinux系统宝典

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;MqPlinux系统宝典

AMqPlinux系统宝典

--------------------MqPlinux系统宝典

2015-06-18 10:51:19MqPlinux系统宝典

                           MqPlinux系统宝典

过了几个小时,误操作发生。MqPlinux系统宝典

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;MqPlinux系统宝典

AMqPlinux系统宝典

--------------------MqPlinux系统宝典

2015-06-18 13:47:29MqPlinux系统宝典

SQL> select group#, sequence# from v$log where status='CURRENT';MqPlinux系统宝典

    GROUP#  SEQUENCE#MqPlinux系统宝典

---------- ----------MqPlinux系统宝典

        3        19MqPlinux系统宝典

误删除发生MqPlinux系统宝典

SQL> drop table test.emp;MqPlinux系统宝典

Table droppedMqPlinux系统宝典

SQL> drop table test.dept;MqPlinux系统宝典

Table droppedMqPlinux系统宝典

--Flashback Drop的机会也没有了。MqPlinux系统宝典

SQL> purge dba_recyclebin;MqPlinux系统宝典

DoneMqPlinux系统宝典

3、RMAN进行表空间TSPITRMqPlinux系统宝典

单表表空间移动,要确保表空间内容“内包”,也就是不会有其他表空间牵扯其中。这个操作我们在可移动表空间的演示中也会进行。MqPlinux系统宝典

SQL>  exec dbms_tts.transport_set_check('testtbl',true);MqPlinux系统宝典

PL/SQL procedure successfully completedMqPlinux系统宝典

SQL> select * from transport_set_violations;MqPlinux系统宝典

VIOLATIONSMqPlinux系统宝典

--------------------------------------------------------------------------------MqPlinux系统宝典

下面进行实际恢复操作,首先需要在目录中创建出一个可访问的空间,进行临时文件容纳。MqPlinux系统宝典

[root@SimpleLinux ~]# cd /MqPlinux系统宝典

[root@SimpleLinux /]# mkdir -p /extend/oradata/auxMqPlinux系统宝典

[root@SimpleLinux /]# chown -R oracle:oinstall /extend/MqPlinux系统宝典

[root@SimpleLinux /]# ls -l | grep extendMqPlinux系统宝典

drwxr-xr-x.  3 oracle oinstall  4096 Jun 18 10:21 extendMqPlinux系统宝典

启动RMAN,登录后执行recover tablespace操作,指定出恢复时间。注意:RMAN的recover tablespace命令本质上是执行一系列的指令脚本,将利用RMAN恢复过程自动化,无需人工介入。MqPlinux系统宝典

[oracle@SimpleLinux ~]$ rman nocatalogMqPlinux系统宝典

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 18 13:48:44 2015MqPlinux系统宝典

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.MqPlinux系统宝典

RMAN> connect target /MqPlinux系统宝典

connected to target database: ORA11G (DBID=4239941846)MqPlinux系统宝典

using target database control file instead of recovery catalogMqPlinux系统宝典

RMAN>  recover tablespace testtbl until time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/extend/oradata/aux';MqPlinux系统宝典

Starting recover at 18-JUN-15MqPlinux系统宝典

allocated channel: ORA_DISK_1MqPlinux系统宝典

channel ORA_DISK_1: SID=44 device type=DISKMqPlinux系统宝典

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-timeMqPlinux系统宝典

List of tablespaces expected to have UNDO segmentsMqPlinux系统宝典

Tablespace SYSTEMMqPlinux系统宝典

Tablespace UNDOTBS1MqPlinux系统宝典

Creating automatic instance, with SID='DDfe' –在目录中创建出一个虚拟的Oracle实例MqPlinux系统宝典

--启动实例的参数都准备好了。MqPlinux系统宝典

initialization parameters used for automatic instance:MqPlinux系统宝典

db_name=ORA11GMqPlinux系统宝典

db_unique_name=DDfe_tspitr_ORA11GMqPlinux系统宝典

compatible=11.2.0.4.0MqPlinux系统宝典

db_block_size=8192MqPlinux系统宝典

db_files=200MqPlinux系统宝典

sga_target=1GMqPlinux系统宝典

processes=80MqPlinux系统宝典

db_create_file_dest=/extend/oradata/auxMqPlinux系统宝典

log_archive_dest_1='location=/extend/oradata/aux'MqPlinux系统宝典

#No auxiliary parameter file usedMqPlinux系统宝典

starting up automatic instance ORA11GMqPlinux系统宝典

Oracle instance startedMqPlinux系统宝典

Total System Global Area    1071333376 bytesMqPlinux系统宝典

Fixed Size                    1369420 bytesMqPlinux系统宝典

Variable Size                281021108 bytesMqPlinux系统宝典

Database Buffers            784334848 bytesMqPlinux系统宝典

Redo Buffers                  4608000 bytesMqPlinux系统宝典

Automatic instance createdMqPlinux系统宝典

--自包检查MqPlinux系统宝典

Running TRANSPORT_SET_CHECK on recovery set tablespacesMqPlinux系统宝典

TRANSPORT_SET_CHECK completed successfullyMqPlinux系统宝典

--第一块脚本片段,利用备份集合和归档日志,恢复数据到虚拟实例上。MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# set requested point in timeMqPlinux系统宝典

set until  time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";MqPlinux系统宝典

# restore the controlfileMqPlinux系统宝典

restore clone controlfile;MqPlinux系统宝典

# mount the controlfileMqPlinux系统宝典

sql clone 'alter database mount clone database';MqPlinux系统宝典

# archive current online log MqPlinux系统宝典

sql 'alter system archive log current';MqPlinux系统宝典

# avoid unnecessary autobackups for structural changes during TSPITRMqPlinux系统宝典

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

executing command: SET until clauseMqPlinux系统宝典

Starting restore at 18-JUN-15MqPlinux系统宝典

allocated channel: ORA_AUX_DISK_1MqPlinux系统宝典

channel ORA_AUX_DISK_1: SID=19 device type=DISKMqPlinux系统宝典

channel ORA_AUX_DISK_1: starting datafile backup set restoreMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring control fileMqPlinux系统宝典

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkpMqPlinux系统宝典

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp tag=TAG20150618T104402MqPlinux系统宝典

channel ORA_AUX_DISK_1: restored backup piece 1MqPlinux系统宝典

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02MqPlinux系统宝典

output file name=/extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctlMqPlinux系统宝典

Finished restore at 18-JUN-15MqPlinux系统宝典

sql statement: alter database mount clone databaseMqPlinux系统宝典

sql statement: alter system archive log currentMqPlinux系统宝典

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# set requested point in timeMqPlinux系统宝典

set until  time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";MqPlinux系统宝典

plsql <<<-- tspitr_2MqPlinux系统宝典

declareMqPlinux系统宝典

  sqlstatement      varchar2(512);MqPlinux系统宝典

  offline_not_needed exception;MqPlinux系统宝典

  pragma exception_init(offline_not_needed, -01539);MqPlinux系统宝典

beginMqPlinux系统宝典

  sqlstatement := 'alter tablespace '||  'TESTTBL' ||' offline immediate';MqPlinux系统宝典

  krmicd.writeMsg(6162, sqlstatement);MqPlinux系统宝典

  krmicd.execSql(sqlstatement);MqPlinux系统宝典

exceptionMqPlinux系统宝典

  when offline_not_needed thenMqPlinux系统宝典

    null;MqPlinux系统宝典

end; >>>;MqPlinux系统宝典

# set destinations for recovery set and auxiliary set datafilesMqPlinux系统宝典

set newname for clone datafile  1 to new;MqPlinux系统宝典

set newname for clone datafile  5 to new;MqPlinux系统宝典

set newname for clone datafile  3 to new;MqPlinux系统宝典

set newname for clone datafile  2 to new;MqPlinux系统宝典

set newname for clone tempfile  1 to new;MqPlinux系统宝典

set newname for datafile  7 to MqPlinux系统宝典

 "/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf";MqPlinux系统宝典

# switch all tempfilesMqPlinux系统宝典

switch clone tempfile all;MqPlinux系统宝典

# restore the tablespaces in the recovery set and the auxiliary setMqPlinux系统宝典

restore clone datafile  1, 5, 3, 2, 7;MqPlinux系统宝典

switch clone datafile all;MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

executing command: SET until clauseMqPlinux系统宝典

sql statement: alter tablespace TESTTBL offline immediateMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

executing command: SET NEWNAMEMqPlinux系统宝典

renamed tempfile 1 to /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_%u_.tmp in control fileMqPlinux系统宝典

Starting restore at 18-JUN-15MqPlinux系统宝典

using channel ORA_AUX_DISK_1MqPlinux系统宝典

--只恢复system, sysaux, undo和目标testtbl表空间。MqPlinux系统宝典

channel ORA_AUX_DISK_1: starting datafile backup set restoreMqPlinux系统宝典

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring datafile 00001 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbfMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring datafile 00005 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbfMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring datafile 00003 to /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_%u_.dbfMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring datafile 00002 to /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_%u_.dbfMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbfMqPlinux系统宝典

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkpMqPlinux系统宝典

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkp tag=TAG20150618T104315MqPlinux系统宝典

channel ORA_AUX_DISK_1: restored backup piece 1MqPlinux系统宝典

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05MqPlinux系统宝典

Finished restore at 18-JUN-15MqPlinux系统宝典

datafile 1 switched to datafile copyMqPlinux系统宝典

input datafile copy RECID=5 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbfMqPlinux系统宝典

datafile 5 switched to datafile copyMqPlinux系统宝典

input datafile copy RECID=6 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbfMqPlinux系统宝典

datafile 3 switched to datafile copyMqPlinux系统宝典

input datafile copy RECID=7 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbfMqPlinux系统宝典

datafile 2 switched to datafile copyMqPlinux系统宝典

input datafile copy RECID=8 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbfMqPlinux系统宝典

--启动虚拟实例数据,完成恢复。MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# set requested point in timeMqPlinux系统宝典

set until  time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";MqPlinux系统宝典

# online the datafiles restored or switchedMqPlinux系统宝典

sql clone "alter database datafile  1 online";MqPlinux系统宝典

sql clone "alter database datafile  5 online";MqPlinux系统宝典

sql clone "alter database datafile  3 online";MqPlinux系统宝典

sql clone "alter database datafile  2 online";MqPlinux系统宝典

sql clone "alter database datafile  7 online";MqPlinux系统宝典

# recover and open resetlogsMqPlinux系统宝典

recover clone database tablespace  "TESTTBL", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;MqPlinux系统宝典

alter clone database open resetlogs;MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

executing command: SET until clauseMqPlinux系统宝典

sql statement: alter database datafile  1 onlineMqPlinux系统宝典

sql statement: alter database datafile  5 onlineMqPlinux系统宝典

sql statement: alter database datafile  3 onlineMqPlinux系统宝典

sql statement: alter database datafile  2 onlineMqPlinux系统宝典

sql statement: alter database datafile  7 onlineMqPlinux系统宝典

Starting recover at 18-JUN-15MqPlinux系统宝典

using channel ORA_AUX_DISK_1MqPlinux系统宝典

starting media recoveryMqPlinux系统宝典

archived 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_.arcMqPlinux系统宝典

channel ORA_AUX_DISK_1: starting archived log restore to default destinationMqPlinux系统宝典

channel ORA_AUX_DISK_1: restoring archived logMqPlinux系统宝典

archived log thread=1 sequence=16MqPlinux系统宝典

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkpMqPlinux系统宝典

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkp tag=TAG20150618T104401MqPlinux系统宝典

channel ORA_AUX_DISK_1: restored backup piece 1MqPlinux系统宝典

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01MqPlinux系统宝典

archived log file name=/extend/oradata/aux/1_16_846081044.dbf thread=1 sequence=16MqPlinux系统宝典

channel clone_default: deleting archived log(s)MqPlinux系统宝典

archived log file name=/extend/oradata/aux/1_16_846081044.dbf RECID=27 STAMP=882712328MqPlinux系统宝典

archived log file name=/u01/app/fast_recovery_area/ORA11G/archivelog/2015_06_18/o1_mf_1_17_br4pw26f_.arc thread=1 sequence=17MqPlinux系统宝典

media recovery complete, elapsed time: 00:00:01MqPlinux系统宝典

Finished recover at 18-JUN-15MqPlinux系统宝典

database openedMqPlinux系统宝典

--将表空间导出来MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# make read only the tablespace that will be exportedMqPlinux系统宝典

sql clone 'alter tablespace  TESTTBL read only';MqPlinux系统宝典

# create directory for datapump importMqPlinux系统宝典

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''MqPlinux系统宝典

/extend/oradata/aux''";MqPlinux系统宝典

# create directory for datapump exportMqPlinux系统宝典

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''MqPlinux系统宝典

/extend/oradata/aux''";MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

sql statement: alter tablespace  TESTTBL read onlyMqPlinux系统宝典

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/extend/oradata/aux''MqPlinux系统宝典

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/extend/oradata/aux''MqPlinux系统宝典

Performing export of metadata...MqPlinux系统宝典

  EXPDP> Starting "SYS"."TSPITR_EXP_DDfe":  MqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLEMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEXMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINTMqPlinux系统宝典

  EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMqPlinux系统宝典

  EXPDP> Master table "SYS"."TSPITR_EXP_DDfe" successfully loaded/unloadedMqPlinux系统宝典

  EXPDP> ******************************************************************************MqPlinux系统宝典

  EXPDP> Dump file set for SYS.TSPITR_EXP_DDfe is:MqPlinux系统宝典

  EXPDP>  /extend/oradata/aux/tspitr_DDfe_54026.dmpMqPlinux系统宝典

  EXPDP> ******************************************************************************MqPlinux系统宝典

  EXPDP> Datafiles required for transportable tablespace TESTTBL:MqPlinux系统宝典

  EXPDP>  /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbfMqPlinux系统宝典

  EXPDP> Job "SYS"."TSPITR_EXP_DDfe" successfully completed at Thu Jun 18 13:54:10 2015 elapsed 0 00:01:13MqPlinux系统宝典

Export completedMqPlinux系统宝典

--关闭虚拟实例MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# shutdown clone before importMqPlinux系统宝典

shutdown clone immediateMqPlinux系统宝典

# drop target tablespaces before importing them backMqPlinux系统宝典

sql 'drop tablespace  TESTTBL including contents keep datafiles cascade constraints';MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

database closedMqPlinux系统宝典

database dismountedMqPlinux系统宝典

Oracle instance shut downMqPlinux系统宝典

--把原来的表空间删除掉MqPlinux系统宝典

sql statement: drop tablespace  TESTTBL including contents keep datafiles cascade constraintsMqPlinux系统宝典

--恢复导入数据MqPlinux系统宝典

Performing import of metadata...MqPlinux系统宝典

  IMPDP> Master table "SYS"."TSPITR_IMP_DDfe" successfully loaded/unloadedMqPlinux系统宝典

  IMPDP> Starting "SYS"."TSPITR_IMP_DDfe":  MqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLEMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEXMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINTMqPlinux系统宝典

  IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMqPlinux系统宝典

  IMPDP> Job "SYS"."TSPITR_IMP_DDfe" successfully completed at Thu Jun 18 13:54:54 2015 elapsed 0 00:00:10MqPlinux系统宝典

Import completedMqPlinux系统宝典

--第三部分脚本,恢复表空间MqPlinux系统宝典

contents of Memory Script:MqPlinux系统宝典

{MqPlinux系统宝典

# make read write and offline the imported tablespacesMqPlinux系统宝典

sql 'alter tablespace  TESTTBL read write';MqPlinux系统宝典

sql 'alter tablespace  TESTTBL offline';MqPlinux系统宝典

# enable autobackups after TSPITR is finishedMqPlinux系统宝典

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';MqPlinux系统宝典

}MqPlinux系统宝典

executing Memory ScriptMqPlinux系统宝典

sql statement: alter tablespace  TESTTBL read writeMqPlinux系统宝典

sql statement: alter tablespace  TESTTBL offlineMqPlinux系统宝典

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;MqPlinux系统宝典

Removing automatic instanceMqPlinux系统宝典

Automatic instance removedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_br4q4ojl_.tmp deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_4_br4q4j17_.log deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_3_br4q4ghl_.log deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_2_br4q4dt5_.log deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_1_br4q4brh_.log deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbf deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbf deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbf deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbf deletedMqPlinux系统宝典

auxiliary instance file /extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctl deletedMqPlinux系统宝典

Finished recover at 18-JUN-15MqPlinux系统宝典

RMAN>MqPlinux系统宝典

上面的步骤可以归纳为如下:MqPlinux系统宝典

ü  在目录中创建一个虚拟临时数据库实例;MqPlinux系统宝典

ü  RMAN自动辅助库AUX创建,利用源数据库的备份集合和归档日志进行全库不完全恢复。恢复范围包括system、sysaux、undo和目标表空间;MqPlinux系统宝典

ü  借助Expdp和Impdp,使用可移动表空间原理步骤,将目标表空间数据导出并导回源数据库;MqPlinux系统宝典

ü  恢复完成;MqPlinux系统宝典

下面可以看实际效果。MqPlinux系统宝典

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;MqPlinux系统宝典

AMqPlinux系统宝典

--------------------MqPlinux系统宝典

2015-06-18 13:55:43MqPlinux系统宝典

SQL> select group#, sequence# from v$log where status='CURRENT';MqPlinux系统宝典

    GROUP#  SEQUENCE#MqPlinux系统宝典

---------- ----------MqPlinux系统宝典

        1        20MqPlinux系统宝典

尝试访问数据表。MqPlinux系统宝典

SQL> select count(*) from test.emp;MqPlinux系统宝典

select count(*) from test.empMqPlinux系统宝典

ORA-00376: 无法读取数据文件 7MqPlinux系统宝典

ORA-01110: 数据文件 7: '/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf'MqPlinux系统宝典

此时,表空间被offline,需要手工进行online操作。MqPlinux系统宝典

SQL> select tablespace_name, status from dba_tablespaces;MqPlinux系统宝典

TABLESPACE_NAME                STATUSMqPlinux系统宝典

------------------------------ ---------MqPlinux系统宝典

SYSTEM                        ONLINEMqPlinux系统宝典

SYSAUX                        ONLINEMqPlinux系统宝典

UNDOTBS1                      ONLINEMqPlinux系统宝典

TEMP                          ONLINEMqPlinux系统宝典

USERS                          ONLINEMqPlinux系统宝典

TESTTBL                        OFFLINEMqPlinux系统宝典

6 rows selectedMqPlinux系统宝典

SQL> alter tablespace testtbl online;MqPlinux系统宝典

Tablespace alteredMqPlinux系统宝典

SQL> select tablespace_name, status from dba_tablespaces;MqPlinux系统宝典

TABLESPACE_NAME                STATUSMqPlinux系统宝典

------------------------------ ---------MqPlinux系统宝典

SYSTEM                        ONLINEMqPlinux系统宝典

SYSAUX                        ONLINEMqPlinux系统宝典

UNDOTBS1                      ONLINEMqPlinux系统宝典

TEMP                          ONLINEMqPlinux系统宝典

USERS                          ONLINEMqPlinux系统宝典

TESTTBL                        ONLINEMqPlinux系统宝典

6 rows selectedMqPlinux系统宝典

SQL> select count(*) from test.emp;MqPlinux系统宝典

  COUNT(*)MqPlinux系统宝典

----------MqPlinux系统宝典

        14MqPlinux系统宝典

SQL> select count(*) from test.dept;MqPlinux系统宝典

  COUNT(*)MqPlinux系统宝典

----------MqPlinux系统宝典

        4MqPlinux系统宝典

5、结论MqPlinux系统宝典

Oracle RMAN的TSPITR在实际中还是很有用处的,特别是当我们没有办法使用Flashback技术(如已经发生了Undo覆盖)的时候。Tablespace的粒度还是比较大,希望以后可以实现Schema或者数据表table级别的的不完全恢复。MqPlinux系统宝典

更多Oracle相关信息见 专题页面 MqPlinux系统宝典



沪ICP备10206494号-4