rman基于备份的控制文件的不完全恢复

本文描述： 通过备份的控制文件来进行数据库的不完全恢复（因为控制文件保存着整个数据库的结构信息，因此要恢复到过去的某个点，就要先恢复那个点所对应的控制文件，然后在进行数据库备份，若未表达清楚可留言后者私信）

试验过程：

(1) 登录sqlplus创建试验表空间tt，以及在其上建表t

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 16 09:08:31 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace tt datafile 'f:\oradata\tt01.dbf' size 10m;

Tablespace created.

SQL> conn scott/tiger
Connected.
SQL> create table t tablespace tt as select * from dept;

Table created.

(2)登录rman压缩备份数据库和控制文件

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期五 6月 16 09:47:37 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1474525861)

RMAN> backup as compressed backupset database format 'g:\rmanbp\db_%db_%T_%s.bak';

Starting backup at 16-6月 -17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=F:\ORADATA\TT01.DBF
input datafile file number=00004 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 16-6月 -17
channel ORA_DISK_1: finished piece 1 at 16-6月 -17
piece handle=G:\RMANBP\DB_ORCLB_20170616_3.BAK tag=TAG20170616T094740 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-6月 -17
channel ORA_DISK_1: finished piece 1 at 16-6月 -17
piece handle=G:\RMANBP\DB_ORCLB_20170616_4.BAK tag=TAG20170616T094740 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-6月 -17

RMAN> backup current controlfile format 'g:\rmanbp\ctl_%d_%T_%s.bak';

Starting backup at 16-6月 -17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-6月 -17
channel ORA_DISK_1: finished piece 1 at 16-6月 -17
piece handle=G:\RMANBP\CTL_ORCL_20170616_5.BAK tag=TAG20170616T095443 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-6月 -17

(3)删除表空间tt(当做是误删，嘎嘎)

SQL> drop tablespace tt including contents and datafiles;

Tablespace dropped.

(4)通过日志挖掘，找出表空间被准确删除时的SCN

SQL> select 'exec dbms_logmnr.add_logfile('''||member||''');' from v$logfile order by 1;

'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''');'
--------------------------------------------------------------------------------
exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG');
exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG');
exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG');

SQL> exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr();

PL/SQL procedure successfully completed.

SQL> select scn,sql_redo from v$logmnr_contents
  2  where sql_redo like '%drop%';

       SCN
----------
SQL_REDO
--------------------------------------------------------------------------------
   1022888
drop table "SCOTT"."T" cascade constraints purge;

   1022914
drop tablespace tt including contents and datafiles;


2 rows selected.

(5)执行备份的控制文件的不完全恢复

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     430075904 bytes

Fixed Size                     2176448 bytes
Variable Size                306186816 bytes
Database Buffers             113246208 bytes
Redo Buffers                   8466432 bytes

RMAN> restore controlfile from 'g:\rmanbp\CTL_ORCL_20170616_5.BAK';

Starting restore at 16-6月 -17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
output file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
Finished restore at 16-6月 -17

RMAN>

(6)将db启动到mount，进行基于SCN的不完全恢复

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> run{
2> set until scn=1022887;
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }

executing command: SET until clause

Starting restore at 16-6月 -17
Starting implicit crosscheck backup at 16-6月 -17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 16-6月 -17

Starting implicit crosscheck copy at 16-6月 -17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-6月 -17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_12\O1_MF_1_68_DMWH9TSN_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_12\O1_MF_1_69_DMWHMLB1_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_12\O1_MF_1_70_DMWJ280G_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_12\O1_MF_1_71_DMWJCPY2_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_72_DMYRSLO5_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_73_DMYV24Y8_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_74_DMYVCR6Q_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_75_DMYVTGMD_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_76_DMZ8PWJ1_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_77_DN155JPS_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_78_DN16YS6W_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_79_DN17JN2C_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_80_DN17RDDN_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_81_DN1FMP5Z_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_82_DN1HKJSX_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_83_DN1Q41L1_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_14\O1_MF_1_84_DN1TLR3X_.ARC
File Name: F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_15\O1_MF_1_1_DN3ZN7YG_.ARC

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to F:\ORADATA\TT01.DBF
channel ORA_DISK_1: reading from backup piece G:\RMANBP\DB_ORCLB_20170616_3.BAK
channel ORA_DISK_1: piece handle=G:\RMANBP\DB_ORCLB_20170616_3.BAK tag=TAG20170616T094740
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 16-6月 -17

Starting recover at 16-6月 -17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
archived log file name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG thread=1 sequence=6
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-6月 -17

sql statement: alter database open resetlogs

RMAN>

(7)登录sqlplus验证

SQL> select file#,name  from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
         3 F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
         4 F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
         5 F:\ORADATA\TT01.DBF

SQL> select * from scott.t where rownum=1;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK

验证成功！