sqlplus 下恢复active 日志丢失的情况

在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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23062014/viewspace-735595/