模拟执行不完全恢复

--模拟执行不完全恢复


0.备份需要是可用的。
1.确定目标还原点：SCN、时间、还原点或日志序列号。
2.相应的设置NLS环境变量。
3.装载数据库。
4.使用SET UNTIL、RESTORE和RECOVER命令准备并运行RUN块。
5.在READONLY模式下打开数据库，并验证恢复点是否为所需要的恢复点。

6.使用RESETLOGS打开数据库。

SCOTT@PROD2> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2016-12-17 15:17:48

SCOTT@PROD2> drop table emp purge;

Table dropped.

--如果事先未查询过时间，可以通过日志挖掘查找到表的相关操作
SYS@PROD2> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 INACTIVE
	 2 CURRENT
	 3 INACTIVE
	 
SYS@PROD2> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/redo03.log
/u01/app/oracle/oradata/PROD2/redo02.log
/u01/app/oracle/oradata/PROD2/redo01.log

SYS@PROD2> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD2/redo02.log');

PL/SQL procedure successfully completed.


SYS@PROD2> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SYS@PROD2> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time,sql_redo from v$logmnr_contents where table_name='EMP';

       SCN TIME
---------- -------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
4299949792 2016-12-17 15:37:11
drop table emp purge;

挖掘到emp表的相关信息

SYS@PROD2> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

挖掘到信息后关闭日志挖掘。

--执行不完全恢复，将数据库恢复到挖掘出的时间点

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     958341120 bytes

Fixed Size                     1348972 bytes
Variable Size                322964116 bytes
Database Buffers             629145600 bytes
Redo Buffers                   4882432 bytes

RMAN> run {
2> set until time "to_date('2016-12-17 15:17:47','yyyy-mm-dd hh24:mi:ss')";  --时间提前1s
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause

Starting restore at 17-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

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 /u01/app/oracle/oradata/PROD2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T222616_d57yd8rg_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T222616_d57yd8rg_.bkp tag=TAG20161216T222616
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:51
Finished restore at 17-DEC-16

Starting recover at 17-DEC-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 17-DEC-16

database opened

SYS@PROD2> conn scott/tiger
Connected.
SCOTT@PROD2> select count(*) from emp;   --数据已经找回

  COUNT(*)
----------
	14