sqlplus 下恢復active 日誌丟失的情況

haozg_oracle發表於2012-07-16
SQL
在sqlplus 命令列下，恢復active redo 丟失的情況,檢查如果資料檔案在
1、啟動資料庫
SQL> startup
ORACLE instance started.
Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
Database opened.
SQL>
2、檢查redo日誌情況
SQL> select group#,sequence#,status from v$log;
    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 CURRENT
         2          2 INACTIVE
         3          3 INACTIVE
 
SQL>
3、做操作
SQL> connect haozg/haozg
Connected.
SQL>
SQL>
SQL> select * from test22;
AAB001          AAB003
--------------- --------------------
soft            0001
soft            0001
soft            0001
SQL>
SQL> insert into test22 values('xxx','002');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit
  2  ;
Commit complete.
SQL> select * from test22;
AAB001          AAB003
--------------- --------------------
soft            0001
soft            0001
soft            0001
xxx             002
xxx             002
xxx             002
6 rows selected.
SQL> select group#,sequence#,status from v$log;
    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 CURRENT
         2          2 INACTIVE
         3          3 INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status from v$log;
    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          4 ACTIVE
         2          5 CURRENT
         3          3 INACTIVE
注意sequence# 為4 的日誌為ACTIVE狀態，group# 為1.
SQL> connect / as sysdba
Connected.
4、不一致停庫
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> SQL>
5、啟動到mount
SQL> startup mount;
ORACLE instance started.
Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
6、刪除active 日誌檔案，由於是在歸檔模式，所以active 日誌 已經完成了歸檔。
[oracle@haozg onlinelog]$ rm -f o1_mf_1_7z24osgo_.log
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
           1118809
           1118809
           1118809
           1118809
           1118809
7、檢查 scn 的情況
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
           1118809
           1118809
           1118809
           1118809
           1118809
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1118808      1119116          1          4 ACTIVE
      1098796      1118808          3          3 INACTIVE
      1119116   2.8147E+14          2          5 CURRENT
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/oracle/flash_recovery_area/ORA11/onlinelog/o1_mf_1_7z24osgo_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter database add logfile group 1;
alter database add logfile group 1
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ora11 (thread 1)
ORA-00312: online log 1 thread 1:
'/oracle/flash_recovery_area/ORA11/onlinelog/o1_mf_1_7z24osgo_.log'

SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1118808      1119116          1          4 ACTIVE
      1098796      1118808          3          3 INACTIVE
      1119116   2.8147E+14          2          5 CURRENT
7、對資料庫做不完全恢復。切記是不完全恢復。
SQL> recover database until sequence 6;
ORA-00277: illegal option to the UNTIL recovery flag SEQUENCE
注意不支援上面的寫法。sqlplus 下不支援 until sequence的寫法。
SQL> recover database until cancel;
ORA-00279: change 1118809 generated at 07/06/2012 11:51:04 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_4_787915613.dbf
ORA-00280: change 1118809 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/archivelog/1_4_787915613.dbf
ORA-00279: change 1119116 generated at 07/06/2012 11:53:27 needed for thread 1
ORA-00289: suggestion : /oracle/archivelog/1_5_787915613.dbf
ORA-00280: change 1119116 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/archivelog/1_4_787915613.dbf' no longer needed for
this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/flash_recovery_area/ORA11/onlinelog/o1_mf_2_7zdhq0rl_.log  ----手工指定當前redo日誌
Log applied.
Media recovery complete.
這裡要注意：recover databae until cancel 會自動找歸檔日誌，但是不會自動找current redo 日誌，所以需要手工指定。
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1118808      1119116          1          4 ACTIVE
      1098796      1118808          3          3 INACTIVE
      1119116   2.8147E+14          2          5 CURRENT
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
           1119126
           1119126
           1119126
           1119126
           1119126
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
           1119126
           1119126
           1119126
           1119126
           1119126
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
8、不完全恢復完成，以resetlogs 方式開啟資料庫
SQL> alter database open resetlogs;
Database altered.
總結：一定要注意 在sqlplus 下 recover database until cancel 恢復命令的特點。在歸檔和current redo沒有丟失的情況下。
      不會丟失資料。如果有歸檔日誌或者是線上日誌丟失的時候，要用該命令做不完全恢復。
      在做oracle的恢復時，一定要要找出原因，然後在確定是做完全恢復還是不完全恢復，根據問題決定採用的恢復命令。
      控制檔案，資料檔案件，日誌檔案，scn的檢查等，一定要先分析原因。不要輕易的resetlogs。
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23062014/viewspace-735595/，如需轉載，請註明出處，否則將追究法律責任。

相關文章