CNDBA社区
第一章 Redo日志文件丢失的恢复
1.1 online redolog file 丢失
联机Redo日志是Oracle数据库中比较核心的文件，当Redo日志文件异常之后，数据库就无法正常启动，而且有丢失据的风险，强烈建议在条件允许的情况下，对Redo日志进行多路镜像。需要注意的是，RMAN不能备份联机Redo日志文件。所以，联机Redo日志一旦出现故障，则只能进行清除日志了。清除日志文件即表明可以重用该文件。
1.1.1 数据库归档/非归档模式下inactive redo异常ORA-00316 ORA-00327
1.1.1.1 例一
SQL> startup mount
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 2 of thread 1, type in header is not log file
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'
SQL> col member for a40
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- -----------------------------------
1 15 3 665697 CURRENT /u01/oracle/oradata/XFF/redo03.log
1 14 2 645619 INACTIVE /u01/oracle/oradata/XFF/redo02.log
1 13 1 625540 INACTIVE /u01/oracle/oradata/XFF/redo01.log
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00327: log 2 of thread 1, physical size less than needed
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database add logfile group 2 ('/u01/oracle/oradata/XFF/redo02.log') size 50M reuse;
Database altered.
1.1.1.2 例二
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:46:16 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 16 3 1209020 CURRENT /u02/app/oracle/oradata/oratest/redo03.log
1 15 1 1209017 INACTIVE /u02/app/oracle/oradata/oratest/redo01.log
1 14 2 1209012 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log
SQL>
SQL> startup force;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15390
Session ID: 125 Serial number: 5
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
告警日志：
Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_lgwr_15484.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:48:39 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 17 1 1229024 CURRENT /u02/app/oracle/oradata/oratest/redo01.log
1 16 3 1209020 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 14 2 1209012 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL>
1.1.2 正常关闭数据库current redo异常ORA-00316 ORA-01623
1.1.2.1 例一
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type in header is not log file
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- ----------------------------------
1 16 1 685918 CURRENT /u01/oracle/oradata/XFF/redo01.log
1 15 3 665697 INACTIVE /u01/oracle/oradata/XFF/redo03.log
1 0 2 0 UNUSED /u01/oracle/oradata/XFF/redo02.log
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> ALTER DATABASE drop logfile group 1;
ALTER DATABASE drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance XFF (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
1.1.2.2 例二
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:52:49 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 20 1 1229346 CURRENT /u02/app/oracle/oradata/oratest/redo01.log
1 19 3 1229343 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 18 2 1229340 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15837
Session ID: 125 Serial number: 5
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
告警日志：
Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_15949.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'
Wed May 06 13:53:47 2015
ARC1 started with pid=21, OS id=15976
USER (ospid: 15949): terminating the instance due to error 313
System state dump requested by (instance=1, osid=15949), summary=[abnormal instance termination].
System State dumped to trace file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_diag_15919.trc
Dumping diagnostic data in directory=[cdmp_20150506135347], requested by (instance=1, osid=15949), summary=[abnormal instance termination].
Instance terminated by USER, pid = 15949
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:54:28 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00350: log 1 of instance oratest (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 21 2 1229347 CURRENT /u02/app/oracle/oradata/oratest/redo02.log
1 19 3 1229343 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 0 1 1229346 UNUSED /u02/app/oracle/oradata/oratest/redo01.log
SQL>
1.1.3 数据库异常关闭current/active redo异常ORA-00316 ORA-01624 ORA-01194
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- -----------------------------------
1 8 2 686310 CURRENT /u01/oracle/oradata/XFF/redo02.log
1 7 1 686294 ACTIVE /u01/oracle/oradata/XFF/redo01.log
1 6 3 686289 INACTIVE /u01/oracle/oradata/XFF/redo03.log
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 XFF (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> ALTER DATABASE drop logfile group 1;
ALTER DATABASE drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance XFF (thread 1)
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> recover database until cancel;
ORA-00279: change 686294 generated at 04/20/2013 01:37:16 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7_813202529.dbf
ORA-00280: change 686294 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/XFF/redo01.log
ORA-00308: cannot open archived log '/u01/oracle/oradata/XFF/redo01.log'
ORA-27047: unable to read the header block of file
Additional information: 2
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
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/oracle/oradata/XFF/system01.dbf'
ORA-01112: media recovery not started
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/oracle/oradata/XFF/system01.dbf'
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 686294 generated at 04/20/2013 01:37:16 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7_813202529.dbf
ORA-00280: change 686294 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
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/oracle/oradata/XFF/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
在这样的情况下,数据库异常关闭,current/active redo异常,通过使用隐含参数可能可以侥幸的恢复数据库,但是也可能导致数据丢失.这里因为是模拟情况,无业务所以在很多较为繁忙的业务系统中,使用隐含参数resetlogs过程中可能还会遇到如下很多常见的错误,进一步增加了恢复难度。
current/active redo异常后附带其他错误
ORA-600[2662]
Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:/app/administrator/diag/rdbms/hzyl/hzyl/trace/hzyl_ora_3388.trc (incident=216664):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:/app/administrator/diag/rdbms/hzyl/hzyl/incident/incdir_216664/hzyl_ora_3388_i216664.trc
Errors in file d:/app/administrator/diag/rdbms/hzyl/hzyl/trace/hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3388): terminating the instance due to error 600
ORA-00600[4000]
Thu Feb 28 19:29:10 2013
SMON: enabling cache recovery
Thu Feb 28 19:29:11 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
Thu Feb 28 19:29:13 2013
Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.3.0]
Thu Feb 28 19:29:13 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
ORA-00704 ORA-00604 ORA-01555
Fri May 4 21:04:21 2012
select ctime, mtime, stime from obj$ where obj# = :1
Fri May 4 21:04:21 2012
Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 40 with name "_SYSSMU40$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1286288
ORA-1092 signalled during: alter database open resetlogs...
current/active redo异常还可能报如下错误
redo文件损坏报错
Started redo scan
Errors in file d:/app/administrator/diag/rdbms/hzyl/hzyl/trace/hzyl_ora_2960.trc (incident=214262):
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:/ORADATA/HZYL/REDO03.LOG'
Incident details in: d:/app/administrator/diag/rdbms/hzyl/hzyl/incident/incdir_214262/hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:/app/administrator/diag/rdbms/hzyl/hzyl/trace/hzyl_ora_2960.trc:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:/ORADATA/HZYL/REDO03.LOG'
ORA-368 signalled during: ALTER DATABASE OPEN...
redo文件被其他实例占用报错
Wed May 16 17:03:11 2012
Started redo scan
Wed May 16 17:03:11 2012
Errors in file /oracle/admin/odsdb/udump/odsdb1_ora_2040024.trc:
ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_2'
ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_1'
ORA-305 signalled during: ALTER DATABASE OPEN...
存储整体异常
Mon Oct 17 09:35:09 2011
Errors in file /oracle/app/admin/orcl/bdump/orcl2_lgwr_348814.trc:
ORA-00340: IO error processing online log 4 of thread 2
ORA-00345: redo log write error block 6732 count 2
ORA-00312: online log 4 thread 2: '/dev/rredo21'
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 6: No such device or address
Additional information: -1
Additional information: 1024
Mon Oct 17 09:35:09 2011
LGWR: terminating instance due to error 340
存储IO异常
Fri Feb 21 08:44:42 2014
Thread 1 advanced to log sequence 591 (LGWR switch)
Current log# 1 seq# 591 mem# 0: J:/ORADATA/ORCL/REDO01.LOG
Fri Feb 21 15:31:20 2014
Errors in file c:/oracle/product/10.2.0/admin/orcl/bdump/orcl_lgwr_10312.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:/ORADATA/ORCL/REDO01.LOG'
使用_disable_logging参数
Sat May 14 23:16:49 2005
Errors in file d:/oracle/admin/rman/bdump/rman_arc0_736.trc:
ORA-16038: log 3 sequence# 72 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:/ORACLE/ORADATA/RMAN/REDO03.LOG'
1.1.3.1 current状态日志丢失
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7 52428800 1 NO CURRENT 661005 11-MAR-15
2 1 5 52428800 1 YES INACTIVE 660997 11-MAR-15
3 1 6 52428800 1 YES INACTIVE 660999 11-MAR-15
SQL> ho ls /u03/app/oracle/oradata/ora1024g/redo01.log
ls: cannot access /u03/app/oracle/oradata/ora1024g/redo01.log: No such file or directory
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/ora1024g/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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 ora1024g (thread 1)
ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/ora1024g/redo01.log'
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ora1024g (thread 1)
ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/ora1024g/redo01.log'
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> recover database using backup controlfile;
ORA-00279: change 662207 generated at 03/12/2015 10:08:02 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_7_%u_.arc
ORA-00280: change 662207 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> startup mount force;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u03/app/oracle/oradata/ora1024g/system01.dbf'
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> startup force;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[[email protected]_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 10:20:50 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
重做日志文件在数据库中是要求最高的组件，首先其对磁盘的IO要求极高，其次一旦CURRENT组发生故障，数据库会立即崩溃，并且100%会发生数据丢失，所以ORACLE建议至少每个组需要两个成员，并且在数据库运行过程中日志文件会一直被锁定，以防不测。
Redo log的恢复分为两种：CURRENT 和 非CURRENT
3.1 CURRENT 情况
造成redo 损坏，很多情况是与突然断电有关。这种情况下是比较麻烦的。
（1）如果有归档和备份，可以用不完全恢复。
SQL>startup mount;
SQL>recover database until cancel; 先选择auto，尽量恢复可以利用的归档日志，然后重新执行：
SQL>recover database until cancel; 这次输入cancel，完成不完全恢复,
用resetlogs打开数据：
SQL>alter database open resetlogs； 打开数据库
（2）强制恢复， 这种方法可能会导致数据不一致
sql>startup mount;
sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
sql>recover database until cancel;
sql>alter database open resetlogs;
运气好的话，数据库能正常打开，但是由于使用_allow_resetlogs_corruption方式打开，会造成数据的丢失，且数据库的状态不一致。因此，这种情况下Oracle建议通过EXP方式导出数据库。重建新数据库后，再导入。
redo 的损坏，一般还容易伴随以下2种错误：ORA-600[2662]（SCN有关）和 ORA-600[4000]（回滚段有关）。
metalink上的两篇文章介绍了两种情况的处理方法：
TECH: Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION` with Automatic Undo Management [ID 283945.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106083.aspx
ORA-600 [2662] Block SCN is ahead of Current SCN [ID 28929.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106130.aspx
这两种情况下的恢复有点复杂，回头单独做个测试，在补充进来。
3.2 非CURRENT 情况
这种情况下的恢复比较简单，因为redo log 是已经完成归档或者正在归档。 没有正在使用。可以通过v$log 查看redo log 的状态。
（1）如果STATUS是INACTIVE,则表示已经完成了归档，直接清除掉这个redo log即可。
SQL>startup mount;
SQL> alter database clear logfile group 3 ;
SQL>alter database open;
（2）如果STATUS 是ACTIVE ，表示正在归档， 此时需要使用如下语句：
SQL>startup mount;
SQL> alter database clear unarchived logfile group 3 ;
SQL>alter database open;
current online log 损坏有两种恢复方法：
（1）如果有归档和备份，可以用不完全恢复。
SQL>startup mount;
SQL>recover database until cancel; 先选择auto，尽量恢复可以利用的归档日志，然后重新执行：
SQL>recover database until cancel; 这次输入cancel，完成不完全恢复,
用resetlogs打开数据：
SQL>alter database open resetlogs； 打开数据库
（2）强制恢复， 这种方法可能会导致数据不一致
sql>startup mount;
sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
sql>recover database until cancel;
sql>alter database open resetlogs;
这里主要看2点：
（1）使用了_allow_resetlogs_corruption 参数
（2）这种情况下，可能会报ORA-600[2662]（SCN有关）和 ORA-600[4000]（回滚段有关）的错误。
使用_allow_resetlogs_corruption参数，强制的打开数据库，可能会导致逻辑的坏块，从而影响数据字典。 所以，即使使用该参数正常打开后，也需要做的一个操作：逻辑导出数据。 重建实例，导入实例。 消除逻辑坏块的可能性。
如果使用_allow_resetlogs_corruption参数启动报了undo segment的错误而无法启动，处理方法参考第二节中undo 的处理情况。 只要DB 能正常open，就导出数据，重建实例，在导入。
1.1.4 其他
以下命令需要在sqlplus中执行：
Which of the following does the recover command not do? 下列哪项是恢复命令不能做？
A. Restore archived redo logs.还原归档重做日志。
B. Apply archived redo logs.
C. Restore incremental backups.
D. Apply incremental backups.
E. Restore datafile images.
The recover command does not restore datafile images. It does restore and apply archived redo logs and incremental backup images during the recovery process.
Answer: A
Which statement about recovering from the loss of a redo log group is true? 有关重做日志组的损失中恢复，哪种说法是真的？
A. If the lost redo log group is ACTIVE, you should first attempt to clear the log file. B.
If the lost redo log group is CURRENT, you must clear the log file.
C. If the lost redo log group is ACTIVE, you must restore, perform cancel-based incomplete recovery, and
open the database using the RESETLOGS option.
D. If the lost redo log group is CURRENT, you must restore, perform cancel-based incomplete recovery, and
open the database using the RESETLOGS option. 如果丢失的重做日志组是最新的，你必须恢复，执行基于
取消的不完全恢复，并使用重置日志选项打开数据库。
Answer: D
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
版权声明：本文为博主原创文章，未经博主允许不得转载。
rman
