current redo log损坏的恢复

在非归档模式下缺失Redo Log后的恢复

将之前的归档模式修改为非归档

SQL> shutdown immediate;
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           6

1.Inactive redo log丢失或损坏的恢复

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          10   52428800       512        2 NO  INACTIVE             9783033 06-JUN-19        9783036 06-JUN-19
     2        1          11   52428800       512        1 NO  INACTIVE             9783036 06-JUN-19        9783039 06-JUN-19
     3        1          12   52428800       512        1 NO  CURRENT             9783039 06-JUN-19     2.8147E+14

SQL> col member format a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                          IS_
---------- ------- ------- -------------------------------------------------- ---
     2       ONLINE  /u01/app/oracle/oradata/orcl/redo02.log          NO
     1       ONLINE  /u01/app/oracle/oradata/orcl/redo01.log          NO
     3       ONLINE  /u01/app/oracle/oradata/orcl/redo03.log          NO
     1       ONLINE  /u01/app/oracle/oradata/orcl/redo11.log          NO
[oracle@DSI ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo02.log bs=512 count=20
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.00010096 s, 0.0 kB/s
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size            2257352 bytes
Variable Size          478154296 bytes
Database Buffers      297795584 bytes
Redo Buffers            6791168 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6945
Session ID: 125 Serial number: 5
SQL> exit

alert日志

[oracle@DSI ~]$ tail -f -n 300 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_6925.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
Thu Jun 06 11:18:37 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7006.trc:
ORA-00316: log 2 of thread 1, type 0 in header is not log file
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
Checker run found 2 new persistent data failures
Completed: alter database clear logfile group 2

[oracle@DSI ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          10   52428800       512        2 NO  INACTIVE             9783033 06-JUN-19        9783036 06-JUN-19
     3        1          12   52428800       512        1 NO  CURRENT             9783039 06-JUN-19     2.8147E+14
     2        1          11   52428800       512        1 NO  INACTIVE             9783036 06-JUN-19        9783039 06-JUN-19
SQL> alter database clear logfile group 2;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.
SQL> select count(*) from test.t7;

  COUNT(*)
----------
      1000

2.Active redo log丢失或损坏的恢复

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          17   52428800       512        2 NO  ACTIVE             9792160 06-JUN-19        9792165 06-JUN-19
     2        1          16   52428800       512        1 NO  ACTIVE             9783839 06-JUN-19        9792160 06-JUN-19
     3        1          18   52428800       512        1 NO  CURRENT             9792165 06-JUN-19     2.8147E+14
[oracle@DSI ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo02.log bs=512 count=20
0+0 records in
0+0 records out
0 bytes (0 B) copied, 8.7827e-05 s, 0.0 kB/s

insert into t7 values(11,'AAAAAA');
alter system switch logfile;
SQL> select count(*) from t7;

  COUNT(*)
----------
      1001
进行多次切换后，
SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          26   52428800       512        2 NO  ACTIVE             9792529 06-JUN-19        9792536 06-JUN-19
     2        1          25   52428800       512        1 NO  ACTIVE             9792524 06-JUN-19        9792529 06-JUN-19
     3        1          27   52428800       512        1 NO  CURRENT             9792536 06-JUN-19     2.8147E+14

发现被dd的redo02.log 没有任何影响，只要不关闭数据库
这里分别使用shutdown immediate/shutdown abort进行测试

##shutdown immediate;

SQL> shutdown immediate;
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size            2257352 bytes
Variable Size          478154296 bytes
Database Buffers      297795584 bytes
Redo Buffers            6791168 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 7956
Session ID: 125 Serial number: 5

alert log

ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_7936.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

SQL> set linesize 1000
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          26   52428800       512        2 NO  INACTIVE             9792529 06-JUN-19        9792536 06-JUN-19
     3        1          27   52428800       512        1 NO  CURRENT             9792536 06-JUN-19     2.8147E+14
     2        1          25   52428800       512        1 NO  INACTIVE             9792524 06-JUN-19        9792529 06-JUN-19
SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          26   52428800       512        2 NO  INACTIVE             9792529 06-JUN-19        9792536 06-JUN-19
     2        1           0   52428800       512        1 NO  UNUSED             9792524 06-JUN-19        9792529 06-JUN-19
     3        1          27   52428800       512        1 NO  CURRENT             9792536 06-JUN-19     2.8147E+14
SQL> alter system switch logfile;
SQL> select * from v$log;

##使用shutdown abort测试

SQL> conn test/test
Connected.
SQL> insert into t7 values(11,'AAAAAA');
SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1          32   52428800       512        2 NO  ACTIVE             9793080 06-JUN-19        9793110 06-JUN-19
     2        1          34   52428800       512        1 NO  CURRENT             9793115 06-JUN-19     2.8147E+14
     3        1          33   52428800       512        1 NO  ACTIVE             9793110 06-JUN-19        9793115 06-JUN-19
[oracle@DSI ~]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log bs=512 count=20

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size            2257352 bytes
Variable Size          478154296 bytes
Database Buffers      297795584 bytes
Redo Buffers            6791168 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

---
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8099.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
---

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
ORA-00279: change 9793080 generated at 06/06/2019 15:42:14 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_32_%u_.arc
ORA-00280: change 9793080 for thread 1 is in sequence #32


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u01/app/oracle/fra/ORCL/archivelog/2019_06_06/o1_mf_1_32_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

与归档模式下的active恢复类似，添加2个隐含参数

SQL> shutdown immediate;
SQL> startup pfile='/tmp/pfile.ora' mount;
SQL>  alter database open resetlogs;
SQL> set linesize 1000
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1           1   52428800       512        2 NO  CURRENT             9793081 06-JUN-19     2.8147E+14
     2        1           0   52428800       512        1 YES UNUSED               0              0
     3        1           0   52428800       512        1 YES UNUSED               0              0
SQL> alter system switch logfile;
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1           4   52428800       512        2 NO  INACTIVE             9793443 06-JUN-19        9793446 06-JUN-19
     2        1           5   52428800       512        1 NO  INACTIVE             9793446 06-JUN-19        9793449 06-JUN-19
     3        1           6   52428800       512        1 NO  CURRENT             9793449 06-JUN-19     2.8147E+14

SQL> select count(*) from test.t7;

  COUNT(*)
----------
      1005
[oracle@DSI ~]$ tail -f -n 300 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log