几种RMAN恢复场景

前期准备：

做一次全库备份：

[oracle@localhost ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 8 11:15:52 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1448371606)RMAN> backup database;Starting backup at 08-SEP-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=30 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/user01.dbfchannel ORA_DISK_1: starting piece 1 at 08-SEP-16channel ORA_DISK_1: finished piece 1 at 08-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T111600_cx1ozkdb_.bkp tag=TAG20160908T111600 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 08-SEP-16channel ORA_DISK_1: finished piece 1 at 08-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T111600_cx1p208k_.bkp tag=TAG20160908T111600 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 08-SEP-16RMAN> list backupset；List of Backup Sets===================BS Key  Type LV Size       Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------14      Full    1.11G      DISK        00:01:07     08-SEP-16              BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T111600        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T111600_cx1ozkdb_.bkp  List of Datafiles in backup set 14  File LV Type Ckp SCN    Ckp Time  Name  ---- -- ---- ---------- --------- ----  1       Full 1070506    08-SEP-16 /u01/app/oracle/oradata/orcl/system01.dbf  2       Full 1070506    08-SEP-16 /u01/app/oracle/oradata/orcl/sysaux01.dbf  3       Full 1070506    08-SEP-16 /u01/app/oracle/oradata/orcl/undotbs01.dbf  4       Full 1070506    08-SEP-16 /u01/app/oracle/oradata/orcl/user01.dbf  5       Full 1070506    08-SEP-16 /u01/app/oracle/oradata/orcl/example01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------15      Full    9.36M      DISK        00:00:04     08-SEP-16              BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: TAG20160908T111600        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T111600_cx1p208k_.bkp  SPFILE Included: Modification time: 08-SEP-16  SPFILE db_unique_name: ORCL  Control File Included: Ckp SCN: 1070546      Ckp time: 08-SEP-16

丢失spfile的恢复

删除spfile。

[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/[oracle@localhost dbs]$ lshc_orcl.dat  init.ora  lkORCL  orapworcl  snapcf_orcl.f  spfileorcl.ora[oracle@localhost dbs]$ rm spfileorcl.ora [oracle@localhost dbs]$ 

开始恢复

RMAN> restore spfile from autobackup;Starting restore at 08-SEP-16using channel ORA_DISK_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 09/08/2016 11:20:48RMAN-06564: must use the TO clause when the instance is started with SPFILE--其实这个时候如果实例还在启动，可以不用关库恢复，从memory中恢复。RMAN> shutdown immediatedatabase closeddatabase dismountedOracle instance shut downRMAN> startup nomountconnected to target database (not started)startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'starting Oracle instance without parameter file for retrieval of spfileOracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2260088 bytesVariable Size                281019272 bytesDatabase Buffers             780140544 bytesRedo Buffers                   5517312 bytesRMAN> restore spfile from autobackup;Starting restore at 08-SEP-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKchannel ORA_DISK_1: looking for AUTOBACKUP on day: 20160908channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160907channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160906channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160905channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160904channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160903channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160902channel ORA_DISK_1: no AUTOBACKUP in 7 days foundRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 09/08/2016 11:21:42RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or pieceRMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T111600_cx1p208k_.bkp';Starting restore at 08-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T111600_cx1p208k_.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 08-SEP-16RMAN> shutdown immediateOracle instance shut downRMAN> startup openRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "open": expecting one of: "dba, force, mount, newline, nomount, pfile, ;"RMAN-01007: at line 1 column 9 file: standard inputRMAN> startupconnected to target database (not started)Oracle instance starteddatabase mounteddatabase openedTotal System Global Area     768294912 bytesFixed Size                     2257192 bytesVariable Size                486543064 bytesDatabase Buffers             276824064 bytesRedo Buffers                   2670592 bytes

演示从memory恢复。

[oracle@localhost 2016_09_08]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/[oracle@localhost dbs]$ lshc_orcl.dat  init.ora  lkORCL  orapworcl  snapcf_orcl.f  spfileorcl.ora[oracle@localhost dbs]$ rm spfileorcl.ora[oracle@localhost ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 8 11:26:24 2016Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create spfile from memory;create spfile from memory*ERROR at line 1:ORA-32002: cannot create SPFILE already being used by the instanceSQL> alter system set db_files = 2000 scope = spfile;alter system set db_files = 2000 scope = spfile*ERROR at line 1:ORA-01565: error in identifying file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> create spfile from memory;create spfile from memory*ERROR at line 1:ORA-32002: cannot create SPFILE already being used by the instanceSQL> create spfile = '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from memory;create spfile = '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from memory*ERROR at line 1:ORA-32002: cannot create SPFILE already being used by the instanceSQL> create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCL.ora' from memory;File created.SQL> alter system set db_files = 2000 scope = spfile;alter system set db_files = 2000 scope = spfile*ERROR at line 1:ORA-01565: error in identifying file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

这个时候我把之前的创建的新的spfileORCL.ora改名字

[oracle@localhost dbs]$ pwd/u01/app/oracle/product/11.2.0/dbhome_1/dbs[oracle@localhost dbs]$ mv spfileORCL.ora spfileorcl.ora

重新执行下面命令

SQL> alter system set db_files = 2000 scope = spfile;System altered.

spfile和pfile都是在数据库实例启动的时候才会用到，同时也是在数据库做一些修改的时候才会用到，所以，可以在中途移动这些文件。

丢失控制文件的恢复

[oracle@localhost dbs]$ cd /u01/app/oracle/oradata/orcl/[oracle@localhost orcl]$ lscontrol01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbfexample01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    user01.dbf[oracle@localhost orcl]$ rm control01.ctl [oracle@localhost orcl]$ cd /u01/app/oracle/fast_recovery_area/orcl/[oracle@localhost orcl]$ rm control02.ctl

这里面如果在linux环境下，可以不用备份恢复。

首先看看数据库状态。

[oracle@localhost fd]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 8 14:28:02 2016Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITE

这个时候我们删除控制文件。

[oracle@localhost ~]$ rm /u01/app/oracle/oradata/orcl/control01.ctl [oracle@localhost ~]$ rm /u01/app/oracle/fast_recovery_area/orcl/control02.ctl [oracle@localhost ~]$ 

其实我们能发现，linux中数据库依然能做操作。，并没有宕机。windows则会宕机。

windows

C:\Users\Csong>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on 星期四 9月 8 14:35:05 2016Copyright (c) 1982, 2013, Oracle.  All rights reserved.连接到:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select open_mode from v$datbase;select open_mode from v$datbase*第 1 行出现错误:ORA-03113: 通信通道的文件结尾进程 ID: 8960会话 ID: 203 序列号: 3

linux

这个时候看看控制文件的进程号

[oracle@localhost ~]$ ps -ef | grep genoracle   12990     1  0 14:25 ?        00:00:00 ora_gen0_orcloracle   13907 13701  0 14:40 pts/1    00:00:00 grep --color=auto gen[oracle@localhost ~]$ cd /proc/12990/fd/[oracle@localhost fd]$ lltotal 0lr-x------. 1 oracle oinstall 64 Sep  8 14:41 0 -> /dev/nulll-wx------. 1 oracle oinstall 64 Sep  8 14:41 1 -> /dev/nulll-wx------. 1 oracle oinstall 64 Sep  8 14:41 10 -> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.loglr-x------. 1 oracle oinstall 64 Sep  8 14:41 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msbl-wx------. 1 oracle oinstall 64 Sep  8 14:41 2 -> /dev/nulllrwx------. 1 oracle oinstall 64 Sep  8 14:41 256 -> /u01/app/oracle/oradata/orcl/control01.ctl (deleted)lrwx------. 1 oracle oinstall 64 Sep  8 14:41 257 -> /u01/app/oracle/fast_recovery_area/orcl/control02.ctl (deleted)lr-x------. 1 oracle oinstall 64 Sep  8 14:41 3 -> /dev/nulllr-x------. 1 oracle oinstall 64 Sep  8 14:41 4 -> /dev/nulllr-x------. 1 oracle oinstall 64 Sep  8 14:41 5 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msblr-x------. 1 oracle oinstall 64 Sep  8 14:41 6 -> /proc/12990/fdlr-x------. 1 oracle oinstall 64 Sep  8 14:41 7 -> /dev/zerolrwx------. 1 oracle oinstall 64 Sep  8 14:41 8 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_orcl.datlrwx------. 1 oracle oinstall 64 Sep  8 14:41 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkORCL

我们发现有两个连接，状态是deleted。

我们可以cp恢复。

[oracle@localhost fd]$ cp 256 /u01/app/oracle/oradata/orcl/control01.ctl[oracle@localhost fd]$ cp 257 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl.

这里操作数据库是可以的，但是关闭实例还是会报错。

SQL> shutdown immediateDatabase closed.ORA-03113: end-of-file on communication channelProcess ID: 14010Session ID: 1 Serial number: 3SQL> startup open;ORA-24324: service handle not initializedORA-01041: internal error. hostdef extension doesn't existSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@localhost fd]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 8 14:58:53 2016Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup openORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area  768294912 bytesFixed Size    2257192 bytesVariable Size  490737368 bytesDatabase Buffers  272629760 bytesRedo Buffers    2670592 bytesDatabase mounted.Database opened.

用RMAN恢复。

[oracle@localhost fd]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 8 15:06:18 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1448371606)RMAN> backup database;Starting backup at 08-SEP-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=38 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/user01.dbfchannel ORA_DISK_1: starting piece 1 at 08-SEP-16channel ORA_DISK_1: finished piece 1 at 08-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T150623_cx23hhqc_.bkp tag=TAG20160908T150623 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 08-SEP-16channel ORA_DISK_1: finished piece 1 at 08-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T150623_cx23klbk_.bkp tag=TAG20160908T150623 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 08-SEP-16

这个时候删除controlfile

RMAN> shutdown abortOracle instance shut downRMAN> startup nomountconnected to target database (not started)Oracle instance startedTotal System Global Area     768294912 bytesFixed Size                     2257192 bytesVariable Size                490737368 bytesDatabase Buffers             272629760 bytesRedo Buffers                   2670592 bytesRMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T150623_cx23klbk_.bkp';Starting restore at 08-SEP-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/oradata/orcl/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctlFinished restore at 08-SEP-16RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1RMAN> recover database;Starting recover at 08-SEP-16Starting implicit crosscheck backup at 08-SEP-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKCrosschecked 1 objectsFinished implicit crosscheck backup at 08-SEP-16Starting implicit crosscheck copy at 08-SEP-16using channel ORA_DISK_1Crosschecked 9 objectsFinished implicit crosscheck copy at 08-SEP-16searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_ncsnf_TAG20160908T150623_cx23klbk_.bkpusing channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/orcl/redo02.logarchived log file name=/u01/app/oracle/oradata/orcl/redo02.log thread=1 sequence=5media recovery complete, elapsed time: 00:00:00Finished recover at 08-SEP-16RMAN> alter database open resetlogs;database opened

数据文件损坏

[oracle@localhost orcl]$ rm user01.dbf RMAN> sql 'alter database datafile 4 offline';sql statement: alter database datafile 4 offlineRMAN> restore datafile 4;Starting restore at 08-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/user01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T150623_cx23hhqc_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T150623_cx23hhqc_.bkp tag=TAG20160908T150623channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 08-SEP-16RMAN> recover datafile 4;Starting recover at 08-SEP-16using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_08/o1_mf_1_5_cx23rpnb_.arcarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_08/o1_mf_1_5_cx23rpnb_.arc thread=1 sequence=5media recovery complete, elapsed time: 00:00:00Finished recover at 08-SEP-16RMAN> sql 'alter database datafile 4 online';sql statement: alter database datafile 4 online

表空间损坏

只是文件损坏。

RMAN> sql 'alter tablespace users offline';sql statement: alter tablespace users offlineRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03009: failure of sql command on default channel at 09/08/2016 15:19:27RMAN-11003: failure during parse/execution of SQL statement: alter tablespace users offlineORA-01116: error in opening database file 4ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/user01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3RMAN> sql 'alter tablespace users offline immediate';sql statement: alter tablespace users offline immediateRMAN> restore tablespace users;Starting restore at 08-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/user01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T150623_cx23hhqc_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_08/o1_mf_nnndf_TAG20160908T150623_cx23hhqc_.bkp tag=TAG20160908T150623channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 08-SEP-16RMAN> recover tablespace users;Starting recover at 08-SEP-16using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_08/o1_mf_1_5_cx23rpnb_.arcarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_08/o1_mf_1_5_cx23rpnb_.arc thread=1 sequence=5media recovery complete, elapsed time: 00:00:00Finished recover at 08-SEP-16RMAN> sql 'alter tablespace users online';sql statement: alter tablespace users online

表空间删除

我这里创建了一个表空间后，又做了一次全库备份。

如果需要删除表空间后的其他数据库数据，则使用TSPITR，或者异机恢复后导回原库。

如果后面的数据无所谓，则可以全库基于时间点的恢复。

使用RMAN TSPITR恢复drop的表空间。

创建表空间

<pre name="code" class="sql">SQL> create tablespace Csong datafile '/u01/app/oracle/oradata/orcl/Csong.dbf' size 200m;Tablespace created.

做全备

RMAN> backup database;Starting backup at 09-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/orcl/Csong.dbfinput datafile file number=00007 name=/u01/app/oracle/oradata/orcl/liyuanyuan.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 09-SEP-16channel ORA_DISK_1: finished piece 1 at 09-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_nnndf_TAG20160909T161517_cx4vwqcz_.bkp tag=TAG20160909T161517 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 09-SEP-16channel ORA_DISK_1: finished piece 1 at 09-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_ncsnf_TAG20160909T161517_cx4vyrbr_.bkp tag=TAG20160909T161517 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 09-SEP-16

创建一个新的表空间liyuanyuan，创建tablespace 为liyuanyuan的表Csong

<pre name="code" class="sql">SQL> create tablespace liyuanyuan datafile '/u01/app/oracle/oradata/orcl/liyuanyuan.dbf' size 200m;Tablespace created.SQL> create table Csong(id number(10)) tablespace liyuanyuan;Table created.SQL> insert into Csong select level from dual connect by level <= 200000;200000 rows created.

删掉表空间Csong

SQL> drop tablespace Csong including contents and datafiles;Tablespace dropped。

RMAN TSPITR恢复

RMAN> recover tablespace Csong until time "to_date('2016-09-09 16:17:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/u01/app/oracle';Starting recover at 09-SEP-16using channel ORA_DISK_1RMAN-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='kchD'initialization parameters used for automatic instance:db_name=ORCLdb_unique_name=kchD_tspitr_ORCLcompatible=11.2.0.4.0db_block_size=8192db_files=200sga_target=1Gprocesses=80db_create_file_dest=/u01/app/oraclelog_archive_dest_1='location=/u01/app/oracle'#No auxiliary parameter file usedstarting up automatic instance ORCLOracle instance startedTotal System Global Area    1068937216 bytesFixed Size                     2260088 bytesVariable Size                281019272 bytesDatabase Buffers             780140544 bytesRedo Buffers                   5517312 bytesAutomatic instance createdList of tablespaces that have been dropped from the target database:Tablespace Csongcontents of Memory Script:{# set requested point in timeset until  time "to_date('2016-09-09 16:17:00','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 09-SEP-16allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 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/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_ncsnf_TAG20160909T161517_cx4vyrbr_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_ncsnf_TAG20160909T161517_cx4vyrbr_.bkp tag=TAG20160909T161517channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/ORCL/controlfile/o1_mf_cx4w30db_.ctlFinished restore at 09-SEP-16sql 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('2016-09-09 16:17:00','yyyy-mm-dd hh24:mi:ss')";# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile  1 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  6 to  "/u01/app/oracle/oradata/orcl/Csong.dbf";# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile  1, 3, 2, 6;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/ORCL/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 09-SEP-16using channel ORA_AUX_DISK_1channel 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 /u01/app/oracle/ORCL/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/ORCL/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/ORCL/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/Csong.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_nnndf_TAG20160909T161517_cx4vwqcz_.bkpchannel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_09/o1_mf_nnndf_TAG20160909T161517_cx4vwqcz_.bkp tag=TAG20160909T161517channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15Finished restore at 09-SEP-16datafile 1 switched to datafile copyinput datafile copy RECID=5 STAMP=922119602 file name=/u01/app/oracle/ORCL/datafile/o1_mf_system_cx4w37no_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=6 STAMP=922119602 file name=/u01/app/oracle/ORCL/datafile/o1_mf_undotbs1_cx4w37sj_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=7 STAMP=922119602 file name=/u01/app/oracle/ORCL/datafile/o1_mf_sysaux_cx4w37qw_.dbfcontents of Memory Script:{# set requested point in timeset until  time "to_date('2016-09-09 16:17:00','yyyy-mm-dd hh24:mi:ss')";# online the datafiles restored or switchedsql clone "alter database datafile  1 online";sql clone "alter database datafile  3 online";sql clone "alter database datafile  2 online";sql clone "alter database datafile  6 online";# recover and open resetlogsrecover clone database tablespace  "CSONG", "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  3 onlinesql statement: alter database datafile  2 onlinesql statement: alter database datafile  6 onlineStarting recover at 09-SEP-16using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_09/o1_mf_1_2_cx4w365j_.arcarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_09/o1_mf_1_2_cx4w365j_.arc thread=1 sequence=2media recovery complete, elapsed time: 00:00:00Finished recover at 09-SEP-16database openedcontents of Memory Script:{# online the tablespaces that will be exportedsql clone 'alter tablespace  CSONG online';# make read only the tablespace that will be exportedsql clone 'alter tablespace  CSONG read only';# create directory for datapump importsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle''";# create directory for datapump exportsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle''";}executing Memory Scriptsql statement: alter tablespace  CSONG onlinesql statement: alter tablespace  CSONG read onlysql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle''sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle''Performing export of metadata...   EXPDP> Starting "SYS"."TSPITR_EXP_kchD":     EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK   EXPDP> Master table "SYS"."TSPITR_EXP_kchD" successfully loaded/unloaded   EXPDP> ******************************************************************************   EXPDP> Dump file set for SYS.TSPITR_EXP_kchD is:   EXPDP>   /u01/app/oracle/tspitr_kchD_33345.dmp   EXPDP> ******************************************************************************   EXPDP> Datafiles required for transportable tablespace CSONG:   EXPDP>   /u01/app/oracle/oradata/orcl/Csong.dbf   EXPDP> Job "SYS"."TSPITR_EXP_kchD" successfully completed at Fri Sep 9 16:21:34 2016 elapsed 0 00:00:59Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone immediate}executing Memory Scriptdatabase closeddatabase dismountedOracle instance shut downPerforming import of metadata...   IMPDP> Master table "SYS"."TSPITR_IMP_kchD" successfully loaded/unloaded   IMPDP> Starting "SYS"."TSPITR_IMP_kchD":     IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK   IMPDP> Job "SYS"."TSPITR_IMP_kchD" successfully completed at Fri Sep 9 16:22:08 2016 elapsed 0 00:00:04Import completedcontents of Memory Script:{# make read write and offline the imported tablespacessql 'alter tablespace  CSONG read write';sql 'alter tablespace  CSONG offline';# enable autobackups after TSPITR is finishedsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';}executing Memory Scriptsql statement: alter tablespace  CSONG read writesql statement: alter tablespace  CSONG offlinesql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;Removing automatic instanceAutomatic instance removedauxiliary instance file /u01/app/oracle/ORCL/datafile/o1_mf_temp_cx4w5sxh_.tmp deletedauxiliary instance file /u01/app/oracle/ORCL/onlinelog/o1_mf_3_cx4w5qxn_.log deletedauxiliary instance file /u01/app/oracle/ORCL/onlinelog/o1_mf_2_cx4w5q1g_.log deletedauxiliary instance file /u01/app/oracle/ORCL/onlinelog/o1_mf_1_cx4w5o9c_.log deletedauxiliary instance file /u01/app/oracle/ORCL/datafile/o1_mf_sysaux_cx4w37qw_.dbf deletedauxiliary instance file /u01/app/oracle/ORCL/datafile/o1_mf_undotbs1_cx4w37sj_.dbf deletedauxiliary instance file /u01/app/oracle/ORCL/datafile/o1_mf_system_cx4w37no_.dbf deletedauxiliary instance file /u01/app/oracle/ORCL/controlfile/o1_mf_cx4w30db_.ctl deletedFinished recover at 09-SEP-16

校验

SQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSEXAMPLECSONGLIYUANYUAN8 rows selected.SQL> select count(1) from Csong;  COUNT(1)----------    200000

其实通过恢复的信息能看到，数据库自动创建了一个auxiliary实例（实例名未指定的话则随机），然后在新的实例上全库基于时间点恢复了之前的备份，然后EXPDP导出了还原的tablespace，然后IMPDP回target库。

基于时间点的不完全恢复

RMAN> backup database;Starting backup at 10-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/orcl/Csong.dbfinput datafile file number=00007 name=/u01/app/oracle/oradata/orcl/liyuanyuan.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 10-SEP-16channel ORA_DISK_1: finished piece 1 at 10-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_nnndf_TAG20160910T165650_cx7lpm3c_.bkp tag=TAG20160910T165650 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 10-SEP-16channel ORA_DISK_1: finished piece 1 at 10-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_ncsnf_TAG20160910T165650_cx7lrq70_.bkp tag=TAG20160910T165650 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 10-SEP-16RMAN> shutdown immediatedatabase dismountedOracle instance shut downRMAN> startup mountconnected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area     768294912 bytesFixed Size                     2257192 bytesVariable Size                503320280 bytesDatabase Buffers             260046848 bytesRedo Buffers                   2670592 bytesRMAN> run{2> set until time "to_date('2016-09-10 17:30:00','yyyy-mm-dd hh24:mi:ss')";3> restore database;4> recover database;5> alter database open resetlogs;6> }executing command: SET until clauseStarting restore at 10-SEP-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKdatafile 6 not processed because file is offlineskipping datafile 1; already restored to file /u01/app/oracle/oradata/orcl/system01.dbfskipping datafile 2; already restored to file /u01/app/oracle/oradata/orcl/sysaux01.dbfskipping datafile 3; already restored to file /u01/app/oracle/oradata/orcl/undotbs01.dbfskipping datafile 4; already restored to file /u01/app/oracle/oradata/orcl/users01.dbfskipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl/example01.dbfskipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl/liyuanyuan.dbfrestore not done; all files read only, offline, or already restoredFinished restore at 10-SEP-16Starting recover at 10-SEP-16using channel ORA_DISK_1datafile 6 not processed because file is offlinestarting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 10-SEP-16database opened

基于日志序列的不完全恢复

先做一次全库备份

RMAN> backup database;Starting backup at 10-SEP-16using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/orcl/Csong.dbfinput datafile file number=00007 name=/u01/app/oracle/oradata/orcl/liyuanyuan.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 10-SEP-16channel ORA_DISK_1: finished piece 1 at 10-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_nnndf_TAG20160910T181024_cx7q0k03_.bkp tag=TAG20160910T181024 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:25channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 10-SEP-16channel ORA_DISK_1: finished piece 1 at 10-SEP-16piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_ncsnf_TAG20160910T181024_cx7q397p_.bkp tag=TAG20160910T181024 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-SEP-16

切换日志

SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> archive log list;Database log mode       Archive ModeAutomatic archival       EnabledArchive destination       USE_DB_RECOVERY_FILE_DESTOldest online log sequence     14Next log sequence to archive   16Current log sequence       16

现在做恢复。恢复到10

RMAN> restore database until sequence 10 thread 1;Starting restore at 10-SEP-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKdatafile 6 not processed because file is offlinechannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbfchannel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/liyuanyuan.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_nnndf_TAG20160910T181024_cx7q0k03_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_09_10/o1_mf_nnndf_TAG20160910T181024_cx7q0k03_.bkp tag=TAG20160910T181024channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:25Finished restore at 10-SEP-16RMAN> recover database until sequence 10 thread 1;Starting recover at 10-SEP-16using channel ORA_DISK_1datafile 6 not processed because file is offlinestarting media recoveryarchived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_5_cx7q44y7_.arcarchived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_6_cx7q49l0_.arcarchived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_7_cx7q4d4g_.arcarchived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_8_cx7q4dt5_.arcarchived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_9_cx7q4fjf_.arcarchived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_5_cx7q44y7_.arc thread=1 sequence=5archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_6_cx7q49l0_.arc thread=1 sequence=6archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_7_cx7q4d4g_.arc thread=1 sequence=7archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_8_cx7q4dt5_.arc thread=1 sequence=8archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_09_10/o1_mf_1_9_cx7q4fjf_.arc thread=1 sequence=9media recovery complete, elapsed time: 00:00:02Finished recover at 10-SEP-16RMAN> alter database open resetlogs;database opened