在非归档模式下缺失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