【恢复】Redo日志文件丢失的恢复

第一章 Redo日志文件丢失的恢复




【恢复】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  例二

[oracle@orcltest ~]$ 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

 

 

[oracle@orcltest ~]$ 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  例二

[oracle@orcltest ~]$ 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

 

[oracle@orcltest ~]$ 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

[oracle@rhel6_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)如果STATUSINACTIVE,则表示已经完成了归档,直接清除掉这个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  其他

 

【恢复】Redo日志文件丢失的恢复 

 

 

 

 

 

 

以下命令需要在sqlplus中执行:

【恢复】Redo日志文件丢失的恢复 

 

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

1.1.4.1  联机重做日志文件的恢复(online redo log )

当数据库置为mount状态,且将要转换为open状态时,数据文件,联机日志文件被打开,因此联机日志的丢失可以在mount状态完成

恢复步骤

a. 启动到mount状态(startup mount force)

b. 还原数据库(restore database)

c. 恢复数据库(recover database)

       

下面对删除日志并进行恢复

lion@ORCL> select * from tb2;

 

ID NAME

———- —————

2 Jackson

 

lion@ORCL> select current_scn from v$database;   查看数据库当前的SCN

 

CURRENT_SCN

———–

1020638

 

lion@ORCL> insert into tb2 select 1,‘Johnson’ from dual;   为表tb2新增一条记录

 

lion@ORCL> commit;

 

lion@ORCL> select current_scn from v$database;             数据库当前的SCN发生了变化为

 

CURRENT_SCN

———–

1020685

               

lion@ORCL> select file#, checkpoint_change# from v$datafile_header;  数据文件头部的checkpoint_change

 

FILE# CHECKPOINT_CHANGE#

———- ——————

1            1020368

2            1020368

3            1020368

4            1020368

5            1020368

6            1020368

 

lion@ORCL> ho rm -f $ORACLE_BASE/oradata/orcl/*.log      –删除所有的日志文件  */

                       

lion@ORCL> insert into tb2 select 2,‘wilson’ from dual;    为表插入新记录

 

lion@ORCL> commit;                                        

 

lion@ORCL> select current_scn from v$database;            数据库当前的SCN发生了变化为

 

CURRENT_SCN

———–

1020708

 

lion@ORCL> alter system archive log current;              对日志进行归档时提示错误发生

alter system archive log current

*

ERROR at line 1:

ORA-16038: log 1 sequence# 1 cannot be archived

ORA-00312: online log 1 thread 1: ‘/u01/oracle/oradata/orcl/redo01.log’

               

lion@ORCL> conn / as sysdba

 

sys@ORCL> startup mount force;

 

[oracle@oradb ~]$ uniread rman target / catalog rman/rman@asmdb    退出RMAN后并重新连接

RMAN> run {

2> allocate channel ch1 device type disk;

3> restore database;

4> recover database;

5> release channel ch1;}

 

RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 1020365

 

sys@ORCL> recover database until cancel;                          回到SQLPlus直接使用until cancel来进行恢复

 

sys@ORCL> alter database open resetlogs;                          执行opensetlogs打开数据库

 

sys@ORCL> select * from lion.tb2;                          在日志未完成自动归档前,删除日志的后数据全部丢失

 

ID NAME

———- —————

2 Jackson

 

Which are the correct steps, in order , to deal with the loss of an online redo log if the database has not yet

crashed? 如果数据库尚未崩溃,哪些是按顺序的正确步骤,以处理联机重做日志的丢失?

a. Issue a checkpoint.

b. Shut down the database.

c. Issue an alter database open command to open the database.

d. Startup mount the database.

e. Issue an alter database clear logfile command.

f. Recover all database datafiles.

a.发出一个检查点。

b.关闭数据库。

d.启动挂载数据库。

e.发出改变数据库清除日志文件命令。

c.发出改变数据库打开命令来打开数据库。

A. a, b, c, d

B. b, d, e, c

C. a, b, d, e, c 阿扁得逞

D. b, f, d, f, c

E. b, d, a, c

Answer: C

 

The database is running in the ARCHIVELOG mode. It has three redo log groups with one member each. One  of  the  redo  log  groups  has  become  corrupted.  You  have  issued  the  following  command  during  the recovery of a damaged redo log file:  数据库运行在归档记录模式。它每一个成员都有三个重做日志组。重做日

志组之一已损坏。损坏的重做日志文件的恢复过程中,您已发出以下命令:

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;  改变数据库清除未归档日志文件组3;

Which action should you perform immediately after using this command?

你在使用此命令后应立即执行哪一个活动?

A. You should perform a log switch

B. You should make a backup of the database你应该做一个数据库备份

C. You should switch the database to the NONARCHIVELOG mode

D. You should shut down the database instance and perform a complete database recovery

Answer: B

 

As soon as you discover that you have lost an online redo log, if the database is still functioning, what

should be your first action?

A. Shut down the database

B. Clear the online redo log

C. Back up the database

D. Checkpoint the database

E. Call Oracle support

Answer: D

 

1.1.4.2  Loss of a Redo Log File

If a member of a redo log file group is lost and if the group still has at least one member, note the following results:

1. Normal operation of the instance is not affected.

2. You receive a message in the alert log notifying you that a member cannot be found.

3. You can restore the missing log file by dropping the lost redo log member and adding a new member.

4. If the group with the missing log file has been archived you can clear the log group to re-create the missing file.

一、 第一种办法

Recovering from the loss of a single redo log group member should not affect the running instance.

To perform this recovery:

1.Determine whether there is a missing log file by examining the alert log.

2.Restore the missing file by first dropping the lost redo log member:
SQL> ALTER DATABASE DROP LOGFILE MEMBER’+DATA/orcl/onlinelog/group_1.261.691672257′;
Then add a new member to replace the lost red log member:
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+DATA’ TO GROUP 2;
Enterprise Manager can also be used to drop and re-create the log file member.
Note: If using OMF for your redo log files and you use the above syntax to add a new redo log member to an existing group, that new redo log member file will not be an OMF file. If you want to ensure that the new redo log member is an OMF file, then the easiest recovery option would be to create a new redo log group and then drop the redo log group that had the missing redo log member.

3.If the media failure is due to the loss of a disk drive or controller, rename the missing file.

4.If the group has already been archived, or if you are in NOARCHIVELOG mode, you may choose to solve the problem by clearing the log group to re-create the missing file or files. Select the appropriate group and then select the Clear Logfile action. You can also clear the affected group manually with the following command:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP #;

 

【恢复】Redo日志文件丢失的恢复 

【恢复】Redo日志文件丢失的恢复 

【恢复】Redo日志文件丢失的恢复 

【恢复】Redo日志文件丢失的恢复 

二、 第二种办法

执行:alter database clear logfile group 1;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

 

3.Your database is in ARCHIVELOG mode.You have two online redo log groups,each of which contains one redo member.When you attempt to start the database,you receive the following errors:

ORA-00313:open failed for members of log group 1 of thread 1

ORA-00312:online log 1 thread 1:’D:/REDO01.LOG’

You discover that the online redo log file of the current redo group is corrupted.

Which statement should you use to resolve this issue?

A.ALTER DATABASE DROP LOGFILE GROUP 1;

B.ALTER DATABASE CLEAR LOGFILE GROUP 1;

C.ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

D.ALTER DATABASE DROP LOGFILE MEMBER’D:/REDO01.LOG';

Answer:C

(答案解析

参考:http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osadvsce.htm#BRADV90052

[oracle@rtest~]$oerr ora 313

00313,00000,”open failed for members of log group%s of thread%s”

//*Cause:The online log cannot be opened.May not be able to find file.

//*Action:See accompanying errors and make log available.

[oracle@rtest~]$oerr ora 312

00312,00000,”online log%s thread%s:’%s'”

//*Cause:This message reports the filename for details of another message.

//*Action:Other messages will accompany this message.See the

//associated messages for the appropriate action to take.

你的数据库在归档记录模式。你有两个在线重做日志组,其中每个都包含一个重做成员。当您尝试启动数据库时,您会收到以下错误:

ORA-00313:线程1日志组1成员打开失败

ORA-00312:联机日志1线程1:’D:/REDO01.LOG

你发现当前的重做组联机重做日志文件被损坏。你应该使用哪种说法来解决这个问题?

Clearing Inactive,Unarchived Redo

Clearing a not-yet-archived redo log allows it to be reused without archiving it.This action makes backups unusable if they were started before the last change in the log,unless the file was taken offline before the first change in the log.Hence,if you need the cleared log file for recovery of a backup,then you cannot recover that backup.Clearing a not-yet-archived-redo-log,prevents complete recovery from backups due to the missing log.

To clear an inactive,online redo log group that has not been archived:

If the database is shut down,then start a new instance and mount the database:

SQL>STARTUP MOUNT

Clear the log using the UNARCHIVED keyword.

For example,to clear log group 2,issue the following SQL statement:

SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;)

 

A database has three online redo log groups with one member each. A redo log member with the

status ACTICE is damages while the database is running.

What is the first step you should take to solve this problem?

A.Attempt to Issue a checkpoint.

B.Restart the database using the RESETLOGS option.

C.Drop the redo log number and create it in a different location.

D.Perform and incomplete recovery up to the most recent available redo log.

Answer:A

题目解答  注意在runing 且first

 

1.1.4.3  Loss of a Redo Log Group

Recovering from the Loss of a Redo Log Group

If you have lost an entire redo log group, then all copies of the log files for that group are unusable or gone.

The simplest case is where the redo log group is in the INACTIVE state. That means it is not currently being written to, and it is no longer needed for instance recovery. If the problem is temporary, or you are able to fix the media, then the database continues to run normally, and the group is reused when enough log switch events occur. Otherwise, if the media cannot be fixed, you can clear the log file. When you clear a log file, you are indicating that it can be reused.

If the redo log group in question is ACTIVE, then, even though it is not currently being written to, it is still needed for instance recovery. If you are able to perform a checkpoint, then the log file group is no longer needed for instance recovery, and you can proceed as if the group were in the inactive state.

If the log group is in the CURRENT state, then it is, or was, being actively written to at the time of the loss. You may even see the LGWR process fail in this case. If this happens, the instance crashes. Your only option at this point is to restore from backup, perform cancel-based point-in-time recovery, and then open the database with the RESETLOGS option.

【恢复】Redo日志文件丢失的恢复 

【恢复】Redo日志文件丢失的恢复 

Clearing a Log File

Clear a log file using this command:

ALTER DATABASE CLEAR [UNARCHIVED] LOGFILE GROUP

[UNRECOVERABLE DATAFILE]

When you clear a log file, you are indicating that it can be reused. If the log file has already been archived, the simplest form of the command can be used. Use the following query to determine which log groups have been archived:

SQL> SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;

For example, the following command clears redo log group 3, which has already been archived:

SQL> ALTER DATABASE CLEAR LOFGILE GROUP 3;

If the redo log group has not been archived, then you must specify the UNARCHIVED keyword. This forces you to acknowledge that it is possible that there are backups that rely on that redo log for recovery, and you have decided to forgo that recovery opportunity. This may be satisfactory for you, especially if you take another backup right after you correct the redo log group problem; you then no longer need that redo log file.

It is possible that the redo log is required to recover a data file that is currently offline.

 

ocp

A database is running In ARCHIVBXXMS mode. It has two online redo log groups and each group has one member.A LGWR Input/output (I/O) fells due to permanent media failure that has resulted In the loss of redo log file and the LWGR terminates causing the instance to crash. The steps to recover from the loss of a current redo log group member in the random order are as follow.

1) Restore the corrupted redo log group.

2) Restore from a whole database backup.

3) Perform incomplete recovery.

4) Relocate by renaming the member of the damaged online redo log group to a new location.

5) Open the database with the RESETLOGS option.

6) Restart the database instance.

7) Issue a checkpoint and clear the log.

Identify the option with the correct sequential steps to accomplish the task efficiently.

A. 1, 3, 4, and 5

B. 7, 3, 4. and 5

C. 2, 3, 4, and 5

D. 7, 4, 3. and 5

E. Only 6 is required

Answer: C

 

解答:

To recover from loss of an active online redo log group in ARCHIVELOG mode:

1.  Begin incomplete media recovery, recovering up through the log before the damaged log.

2.  Ensure that the current name of the lost redo log can be used for a newly created file.

If not, then rename the members of the damaged online redo log group to a new location. For example, enter:

3. ALTER DATABASE RENAME FILE “/disk1/oradata/trgt/redo01.log” TO “/tmp/redo01.log”;

4. ALTER DATABASE RENAME FILE “/disk1/oradata/trgt/redo02.log” TO “/tmp/redo02.log”;

5.  Open the database using the RESETLOGS option:

6. ALTER DATABASE OPEN RESETLOGS;

先不完全恢复在更改日志路径后打开数据库 所以 后面为3,4,5到底是还原备份还是清空日志呢?

Losing an Active Online Redo Log Group

If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If the operation is successful, then the active redo log becomes inactive, and you can follow the procedure in “Losing an Inactive Online Redo Log Group”. If the operation is unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.

The current log is the one LGWR is currently writing to. If a LGWR I/O operation fails, then LGWR terminates and the instance fails. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.

所以先还原备份但如果数据库没有关闭情况下,可以执行switch logfile后触发ckpt 然后在clear的。 很明显7 不需要

 

 

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 CLEAR unarchived logfile group 2;

 

Database altered.

 

SQL> ALTER DATABASE CLEAR unarchived logfile group 3;

 

Database altered.

 

SQL>

 

 

1.1.4.4  loss all online redo logs

You have lost all your online redo logs. As a result, your database has crashed. You have tried to restart the database and clear the online redo log files, but when you try to open the database you get the following error.

SQL> startup

ORACLE instance started.

Total System Global Area 167395328 bytes

Fixed Size 1298612 bytes

Variable Size 142610252 bytes

Database Buffers 20971520 bytes

Redo Buffers 2514944 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: ,,/oracle01/oradata/orcl/redo02a.log

ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: ,,/oracle01/oradata/orcl/redo02.log

ORA-27037: unable to obtain file

status Linux Error: 2: No such file or directory Additional information: 3

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

* ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 2 thread

1: ,,/oracle01/oradata/orcl/redo02.log ORA-00312: online log 2 thread

1: ,,/oracle01/oradata/orcl/redo02a.log

What steps must you take to resolve the error?

A. Issue the recover database redo logs command.

B. Issue the Startup Mount command to mount the database.

C. Restore the last full database backup.

D. Perform a point-in-time recovery, applying all archived redo logs that are available.

E. Restore all archived redo logs generated during and after the last full database backup.

F. Open the database using the alter database open resetlogs command.

G. Issue the alter database open command.

A. b, a, f

B. e, b, a, f

C.e, b, a, g 

D.b, a, g

E. c, e, b, d, f

Answer: E

 

 

You are trying to recover your database. During the recovery process, you receive the following error:

ORA-00279: change 5033391 generated at 08/17/2008 06:37:40 needed for thread 1ORA-00289:

suggestion:

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_11_%u_.arc

ORA-00280: change 5033391 for thread 1 is in sequence #11

ORA-00278: log

file ,,/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_10_4bj6wnqm_.arc no longer needed for this recovery Specify log:

{=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

,,/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_11_%u_.arc

ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3

How do you respond to this error? (Choose two.)

A. Restore the archived redo log that is missing and attempt recovery again.

B. Recovery is complete and you can open the database.

C. Recovery needs redo that is not available in any archived redo log. Attempt to apply an online redo log if available.

D. Recover the entire database and apply all archived redo logs again.

E. Recovery is not possible because an archived redo log has been lost.

Answer: AC

 

 

 

1.1.4.5  数据库未挂掉的情况下的恢复

 

参考:RMAN】rm -rf 误操作的恢复过程—-数据库在无备份且open情况下的恢复

 

 

1.2  恢复archivelog介绍

Given the following RMAN commands, choose the option that reflects the order required to restore your currently operational ARCHIVELOG-mode database.

A. restore database;

B. recover database;

C. shutdown immediate

D. startup

E. restore archivelog all;

F. alter database open

A. a, b, c, d, e, f

B. c, b, a, d, e, f

C. c, b, a, d, f

D. c, a, b, d

E. c, a, e, b, d, f

Answer: E

 

 

还原归档日志通常情况下是Oraclerecover时自动完成的当数据库出现问题,但不需要restore只需recover,发现要用到的archivelog已经备份并删除了,因为我们备份archivelog一般是采用delete input,这时先需要restore archivelog,然后才能做recover,下面介绍一下restore archivelog的用法:

 

restore archivelog后面可以跟的参数有”all, from, high, like, logseq, low, scn, sequence, time, until”

1.2.1  恢复归档

备份所有归档日志文件

   RMAN> backup archivelog all delete input;

1.2.1.1  restore archivelog 的各种选项

   1.恢复全部归档日志文件

     RMAN> restore archivelog all;

   2.只恢复58这四个归档日志文件

     RMAN> restore archivelog from logseq 5 until logseq 8;

   3.恢复从第5个归档日志起

     RMAN> restore archivelog from logseq 5;

   4.恢复7天内的归档日志

     RMAN> restore archivelog from time ‘sysdate-7′;

   5. sequence between 写法

     RMAN> restore archivelog sequence between 1 and 3;

   6.恢复到哪个日志文件为止

     RMAN> restore archivelog until logseq 3;

   7.从第五个日志开始恢复

     RMAN> restore archivelog low logseq 5;

   8.到第5个日志为止

     RMAN> restore archivelog high logseq 5;

恢复指定的archivelog:restore archivelog sequence 18;

若归档日志不在本地,则需要恢复相应的归档日志到本地目录

run {allocate channel ci type disk;

    set archivelog destination to ‘/tmp';

   restore archvielog from logseq xxx until logseq xxx;

   release channel ci;

};

1.列出已经备份的archivelog

list backup of archivelog all;

2.预览恢复出程,但不真正恢复,可以在你执行恢复前先看看恢复过程,也可以验证一下你的语法是否写对

restore archivelog all preview;  即在你要执行的restore archivelog命令后加preview

restore archivelog sequence 18 preview;

 

3.恢复指定时间范围的archivelog

  3.1 显示2008-08-13 10:00:002008-08-13 11:00:00之间的archivelog

     list backup of archivelog time between “to_date(‘2008-08-13 10:00:00′,’yyyy-mm-dd hh24:mi:ss’)” and “to_date(‘2008-08-13 11:00:00′,’yyyy-mm-dd hh24:mi:ss’)”;

  3.2 预览恢复2008-08-13 10:00:002008-08-13 11:00:00之间的archivelog

     restore archivelog time between “to_date(‘2008-08-13 10:00:00′,’yyyy-mm-dd hh24:mi:ss’)” and “to_date(‘2008-08-13 11::00′,’yyyy-mm-dd hh24:mi:ss’)” preview;

  3.3 真正恢复2008-08-13 10:00:002008-08-13 11:00:00之间的archivelog

     restore archivelog time between “to_date(‘2008-08-13 10:00:00′,’yyyy-mm-dd hh24:mi:ss’)” and “to_date(‘2008-08-13 11::00′,’yyyy-mm-dd hh24:mi:ss’)”

 

4.恢复指定的archivelog

restore archivelog sequence 18;

恢复sequence为18archivelog

 

5.restore archivelog like恢复模糊查询出来的archivelog,这个只能用于通过catalog的备份,nocatalog的会报错

restore archivelog like ‘%18%';

6.恢复指定sequence范围的archivelog

restore archivelog from sequence 18 until sequence 20;

restore archivelog low sequence 18 high sequence 20;

restore archivelog low logseq 18 high logseq 20;

restore archivelog from logseq 5;

7.指定archivelog的恢复目的地,如你想把archivelog恢复到一个临时目录时有用,但这个必须包含在run{}里面才能用

set archivelog destination to ‘e:/temp';

 

以上基本上可以解决你恢复archivelog的需求,我是在10.2.0.4版本中测试通过的———————————————

以下部分是在本人正式环境中遇到的实际情况,非转载:

备份日志中有以下内容:

通道 t1: 正在指定备份集中的存档日志

输入存档日志线程 =1 序列 =18070 记录 ID=35794 时间戳=671966051

输入存档日志线程 =1 序列 =18071 记录 ID=35796 时间戳=671966351

输入存档日志线程 =1 序列 =18072 记录 ID=35798 时间戳=671966652

输入存档日志线程 =1 序列 =18073 记录 ID=35800 时间戳=671966952

输入存档日志线程 =1 序列 =18074 记录 ID=35802 时间戳=671967249

输入存档日志线程 =1 序列 =18075 记录 ID=35804 时间戳=671967550

输入存档日志线程 =1 序列 =18076 记录 ID=35806 时间戳=671967850

输入存档日志线程 =1 序列 =18077 记录 ID=35808 时间戳=671968151

输入存档日志线程 =1 序列 =18078 记录 ID=35810 时间戳=671968451

 

单独恢复18071 到18076

rman> run

{ allocate channel t1 type ‘sbt_tape’ parms ‘ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

  restore archivelog from logseq 18071 until logseq 18076 ;

  release channel t1;

}

1.备份所有归档日志文件

RMAN> backup archivelog all delete input;

Starting backup at 02-JUN-08

current log archived

using channel ORA_DISK_1

skipping archive log file D:/ARCHPAUL/ARC00001.001; already backed up 1 time(s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_2.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_3.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_4.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_5.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_6.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_7.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_8.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_9.DBF; already backed up 1 time(

s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_10.DBF; already backed up 1 time

(s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_11.DBF; already backed up 1 time

(s)

skipping archive log file D:/ARCHPAUL/PUBTEST_1_12.DBF; already backed up 1 time

(s)

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=13 recid=128 stamp=656353510

channel ORA_DISK_1: starting piece 1 at 02-JUN-08

channel ORA_DISK_1: finished piece 1 at 02-JUN-08

piece handle=D:/BACKUP/2QJHUA76_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

channel ORA_DISK_1: deleting archive log(s)

archive log filename=D:/ARCHPAUL/PUBTEST_1_13.DBF recid=128 stamp=656353510

channel ORA_DISK_1: deleting archive log(s)

archive log filename=D:/ARCHPAUL/ARC00001.001 recid=116 stamp=656352824

archive log filename=D:/ARCHPAUL/PUBTEST_1_2.DBF recid=117 stamp=656353339

archive log filename=D:/ARCHPAUL/PUBTEST_1_3.DBF recid=118 stamp=656353340

archive log filename=D:/ARCHPAUL/PUBTEST_1_4.DBF recid=119 stamp=656353340

archive log filename=D:/ARCHPAUL/PUBTEST_1_5.DBF recid=120 stamp=656353369

archive log filename=D:/ARCHPAUL/PUBTEST_1_6.DBF recid=121 stamp=656353370

archive log filename=D:/ARCHPAUL/PUBTEST_1_7.DBF recid=122 stamp=656353375

archive log filename=D:/ARCHPAUL/PUBTEST_1_8.DBF recid=123 stamp=656353376

archive log filename=D:/ARCHPAUL/PUBTEST_1_9.DBF recid=124 stamp=656353382

archive log filename=D:/ARCHPAUL/PUBTEST_1_10.DBF recid=125 stamp=656353384

archive log filename=D:/ARCHPAUL/PUBTEST_1_11.DBF recid=126 stamp=656353386

archive log filename=D:/ARCHPAUL/PUBTEST_1_12.DBF recid=127 stamp=656353465

Finished backup at 02-JUN-08

Starting Control File and SPFILE Autobackup at 02-JUN-08

piece handle=D:/BACKUP/C-799229701-20080602-0C comment=NONE

Finished Control File and SPFILE Autobackup at 02-JUN-08

第二: restore archivelog 的各种选项

1.restore archivelog all   恢复全部归档日志文件

RMAN> restore archivelog all;

Starting restore at 02-JUN-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=1

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=2

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=3

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=4

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=5

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=6

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=7

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=8

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=9

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=10

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=11

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=12

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=13

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 02-JUN-08

RMAN>

2.只恢复 5到8这四个归档日志文件 

RMAN> restore archivelog from logseq 5 until logseq 8;

Starting restore at 02-JUN-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=5

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=6

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=7

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=8

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 02-JUN-08

3.恢复从第5个归档日志起

RMAN> restore archivelog from logseq 5;

Starting restore at 02-JUN-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=5

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=6

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=7

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=8

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=9

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=10

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=11

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=12

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=13

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 02-JUN-08

RMAN>

4.恢复7天内的归档日志

RMAN> restore archivelog from time ‘sysdate-7′;

Starting restore at 02-JUN-08

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=1

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=2

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=3

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=4

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=5

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=6

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=7

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=8

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=9

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=10

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=11

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=12

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL

channel ORA_DISK_1: restore complete

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=13

channel ORA_DISK_1: restored backup piece 1

piece handle=D:/BACKUP/2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 02-JUN-08