--**************
模拟表误删的恢复
--**************
RMAN> delete backup;
RMAN> backup database; --全库备份
Select * From V$LOG; --GROUP# 3 CURRENT
Alter System Switch Logfile; --产生6次归档日志
Select current_scn From v$database; --当前SCN 656943
Drop Table T_SCN; --误删除的表
--删除之后,还执行创建表,插入数据等操作
Create Table t3(Id Int) Tablespace users;
Insert Into t3 Values(1);
Alter System Switch Logfile; --产生归档日志
--*************************************************
不完全恢复 恢复到过去一个SCN 该SCN后的数据会丢失
--*************************************************
RMAN> shutdown Immediate
RMAN> startup Mount
RMAN> restore database;
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 657220
2 657220
3 657220
4 657220
5 657220
6 657220
7 657220
已选择7行。
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 654699
2 654699
3 654699
4 654699
5 654699
6 654699
7 654699
SQL> recover database until change 656943;
ORA-00279: 更改 654699 (在 02/11/2014 14:15:40 生成) 对于线程 1 是必需的
ORA-00289: 建议: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_42_%U_.ARC
ORA-00280: 更改 654699 (用于线程 1) 在序列 #42 中
指定日志: {=suggested | filename | AUTO | CANCEL}
Auto
Select Sequence#,first_change#,next_change# From v$archived_log; -- scn 656943 在sequence# 48中
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 656949
2 656949
3 656949
4 656949
5 656949
6 656949
7 656949
SQL> alter database open resetlogs; --打开不完全恢复后的数据库
数据库已更改。
SQL> select * from t_scn; --表T_SCN恢复
ID SCN
---------- ----------
1 608386
1 609310
2 609320
3 610263
4 611165
5 611210
6 611345
已选择7行。
SQL> select * from t3; --T3没有恢复
select * from t3
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
--*******************************T3表无法恢复,只适合为了找回T_SCN表,另搭建一套环境恢复
--*****************************
被迫的不完全恢复 如REDO日志文件current状态的损坏
--*****************************
Select * From t_scn
Alter System Switch Logfile; --产生2次归档日志
Insert Into t_scn Values(11,11);
Alter System Switch Logfile; --产生2次归档日志
Select * From v$log --group# = 2,seq=5 current
Insert Into t_scn Values(100,100);
--****模拟日志损坏
SQL> shutdown immediate
删除group# = 2 第二组日志 REDO02.Log
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 272629760 bytes
Fixed Size 1248476 bytes
Variable Size 79692580 bytes
Database Buffers 184549376 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员
ORA-00312: 联机日志 2 线程 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO02.LOG'
--**此时可以强制打开,但是可能会造成数据不一直,oracle建议此时进行不完全恢复
RMAN> Restore Database;
RMAN> recover database;
/*
archive log thread 1 sequence 42 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_42_9HMP2ZWS_.AR
archive log thread 1 sequence 43 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_43_9HMP31F5_.AR
archive log thread 1 sequence 44 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_44_9HMP36WS_.AR
archive log thread 1 sequence 45 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_45_9HMP3CLK_.AR
archive log thread 1 sequence 46 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_46_9HMP3DTV_.AR
archive log thread 1 sequence 47 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_47_9HMP3RGN_.AR
archive log thread 1 sequence 48 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_48_9HMPBTZ7_.AR
archive log thread 1 sequence 1 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_1_9HMS50D6_.ARC
archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_2_9HMS513F_.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_3_9HMS7XNJ_.ARC
archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_4_9HMS7YDP_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_42_9HMP2ZWS_.ARC thread=1 sequence=42
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_43_9HMP31F5_.ARC thread=1 sequence=43
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_44_9HMP36WS_.ARC thread=1 sequence=44
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_45_9HMP3CLK_.ARC thread=1 sequence=45
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_46_9HMP3DTV_.ARC thread=1 sequence=46
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_47_9HMP3RGN_.ARC thread=1 sequence=47
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_48_9HMPBTZ7_.ARC thread=1 sequence=48
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_1_9HMS50D6_.ARC thread=1 sequence=1
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2014_02_11\O1_MF_1_2_9HMS513F_.ARC thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/11/2014 17:27:21
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO02.LOG'
ORA-27041: unable to open file
*/
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 658211
2 658211
3 658211
4 658211
5 658211
6 658211
7 658211
已选择7行。
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 658093
2 658093
3 658093
4 658093
5 658093
6 658093
7 658093 --Select Sequence#,first_change#,next_change# From V$ARCHIVED_LOG --最大的NEXT_CHANGE# = 658093
已选择7行。
--**SCN = 658093 后面的日志丢失,此时无法打开数据库,使用resetlogs尝试
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01139: RESETLOGS 选项仅在不完全数据库恢复后有效 --***必须在recover database时候使用until,
--如recover database until change 658093;或者使用until cancel
RMAN> restore database;
recover database Until Cancel --部分恢复,直到输入cancel 在SQL中进行恢复
Auto
Cancel
SQL> alter database open Resetlogs;
数据库已更改。
--****启动数据库成功
Select * From t_scn --11 11恢复成功 100 100丢失
--**************************
不完全恢复 模拟INACTIVE的日志损坏
--**************************
Shutdown Immediate
Select * From v$log --group# 1 current 删除第三组日志
startup
/*
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 272629760 bytes
Fixed Size 1248476 bytes
Variable Size 79692580 bytes
Database Buffers 184549376 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
ORA-00312: 联机日志 3 线程 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO03.LOG'
*/
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 5 52428800 1 NO CURRENT 658618 11-2月 -14
3 1 3 52428800 1 YES INACTIVE 658613 11-2月 -14
2 1 4 52428800 1 YES INACTIVE 658615 11-2月 -14
SQL> alter database drop logfile group 3; --将第三组日志从控制文件中删除
SQL> alter database open;
数据库已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 5 52428800 1 YES INACTIVE 658618 11-2月 -14
2 1 6 52428800 1 NO CURRENT 658909 11-2月 -14
Alter Database Add Logfile Group 3 'C:\oracle\product\10.2.0\oradata\testDB\REDO03.LOG' Size 50M; --重新创建REDO日志,REDO日志大小最好相同,方便维护
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15810196/viewspace-1146677/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15810196/viewspace-1146677/