在歸檔模式下的基于時間點的不完全恢復
sql>shutdown immediate
==
對數據進行全備份 full backup database
==
sql> startup
==
修改數據
update test2 set.. 10:44:01
drop table test1 10:45:05 ==>錯誤操作
==
sql>shutdown immediate
==
備份事故現場
注意：
在實際生產環境中，在做恢復的之前，一定要全備份目前的數據庫,即：保存現場
因為在恢復的過程不一定是一次可以恢復到我想得到狀態。
==
restore datafile
現將之前全備份的所有數據文件(*.dbf)Copy回,不要恢復control file, redo logs等
==
sql>startup mount
sql>recover database until time '2009-11-27:10:45:01'
Media recovery complete.
sql>alter database open resetlogs;
Database altered
sql>conn test/test
sql>select table_name from user_tables;
TABLE_NAME
----------
TEST
TEST1
TEST2
==
這里發現之前drop 掉的table test1沒有被刪除了。
接下來又要對整個數據進行一次全備份，因為以前的歸檔日志和數據都沒有用了。
==
在歸檔模式下的基于Cancel的不完全恢復,當前在線日志損壞
sql>shutdown immediate
==
對數據進行全備份 full backup database
==
sql> startup
==
當前狀態
==
sql> select table_name from user_tables
TABLE_NAME
----------
TEST
sql> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
1 1 0 104857600 1 YES UNUSED
2 1 0 104857600 1 YES UNUSED
3 1 1 104857600 1 NO CURRENT 26-N
sql> alter system switch logfile;
sql> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
1 1 2 104857600 1 NO CURRENT 27-N
2 1 0 104857600 1 YES UNUSED
3 1 1 104857600 1 YES ACTIVE 26-N
sql> create table test2 as select * from test;
sql> select table_name from user_tables
TABLE_NAME
----------
TEST
TEST2
sql> alter system switch logfile;
sql> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
1 1 2 104857600 1 YES ACTIVE 27-N
2 1 3 104857600 1 NO CURRENT 27-N
3 1 1 104857600 1 YES INACTIVE 26-N
sql> create table test3 as select * from test;
sql> select table_name from user_tables;
TABLE_NAME
-----------
TEST
TEST2
TEST3
sql> alter system switch logfile;
sql> select * from v$log; ==>日志正好走完一個循環
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS
---------- ---------- ---------- ---------- ---------- --- ---------------- ----
1 1 2 104857600 1 YES INACTIVE 27-N
2 1 3 104857600 1 YES ACTIVE 27-N
3 1 4 104857600 1 NO CURRENT 27-N
sql> create table test4 as select * from test;
sql> select table_name from user_tables;
TABLE_NAME
-----------
TEST
TEST2
TEST3
TEST4
==
修改數據
==
sql>shutdown immediate
==
備份事故現場
注意：
在實際生產環境中，在做恢復的之前，一定要全備份目前的數據庫,即：保存現場
因為在恢復的過程不一定是一次可以恢復到我想得到狀態。
==
restore datafile
現將之前全備份的所有數據文件(*.dbf)Copy回,不要恢復control file, redo logs等
并模擬當前日志文件丟失，可以直接刪除
==
sql>startup mount
sql>recover database until cancel
ORA-00279: change 220847 generated at 11/27/2009 08:28:25
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_1.ARC
ORA-00280: change 220847 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
輸入：auto
ORA-00279: change 221514 generated at 11/27/2009 13:29:04 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_2.ARC
ORA-00280: change 221514 for thread 1 is in sequence #2
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_1.ARC' no longer needed for this
recovery
ORA-00279: change 222100 generated at 11/27/2009 13:32:35 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_3.ARC
ORA-00280: change 222100 for thread 1 is in sequence #3
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_2.ARC' no longer needed for this
recovery
ORA-00279: change 222367 generated at 11/27/2009 13:34:18 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_4.ARC
ORA-00280: change 222367 for thread 1 is in sequence #4
ORA-00278: log file 'G:\ORACLE\ARC\ORCL_1_3.ARC' no longer needed for this
recovery
ORA-00308: cannot open archived log 'G:\ORACLE\ARC\ORCL_1_4.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 靠靠靠靠靠
SQL> recover database until cancel #再次輸入
ORA-00279: change 222367 generated at 11/27/2009 13:34:18 needed for thread 1
ORA-00289: suggestion : G:\ORACLE\ARC\ORCL_1_4.ARC
ORA-00280: change 222367 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel #選Cancel因為此處ORCL_1_4.ARC沒有歸檔
Media recovery cancelled.
SQL> alter database open resetlogs; #一定要 open resetlogs 重新生成log文件
Database altered.
sql>conn test/test
sql>select table_name from user_tables;
TABLE_NAME
----------
TEST
TEST1
TEST2
TEST3
==
這里發現在恢復前當前日志的操作沒有辦法救回。TEST4沒有辦法找回了。
接下來又要對整個數據進行一次全備份，因為以前的歸檔日志和數據都沒有用了。
==