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