Oracle 日志组故障02

当前日志组被破坏分为两种情况，一种是数据库正常关闭，一种是数据库非正常关闭。下面是针对数据库正常关闭状态下的

 

如果数据库被正常关闭，关库之前会CKPT进程通知DBWR将buffer cache里面的脏块写入到datafile里面，写脏块之前肯定要先写redo日志，这些完成之后ckpt会在数据文件头部和控制文件上面更新一个新的检查点，这是一个干净的关库。正常关库当前的redo日志所记录的脏块肯定被写入到数据文件里面了。

 

数据库正常关闭，当前日志组被破坏以后

SQL> select group#,sequence#,members,status from v$log;

 

    GROUP#  SEQUENCE#  MEMBERS STATUS

---------- ---------- ---------- ----------------

 1    19        1 CURRENT

 2    18        1 INACTIVE

 3    15        1 INACTIVE

 4    17        2 INACTIVE

 

SQL> select group#,member from v$logfile;

 

    GROUP# MEMBER

---------- ------------------------------------------------------------

 1 /u01/app/oracle/oradata/oradb/redo01a.log

 2 /u01/app/oracle/oradata/oradb/redo02.log

 3 /u01/app/oracle/oradata/oradb/redo03.log

 4 /disk1/oradata/oradb/redo04a.log

 4 /disk2/oradata/oradb/redo04b.log

 

[oracle@Database2 ~]$ rm -rf /u01/app/oracle/oradata/oradb/redo01a.log  --将当前日志文件删除，因为该日志组只有一个成员，当唯一的成员被破坏会导致整个日志组是不可以访问的，这样数据库就打不开了

 

 

 

[oracle@Database2 trace]$ tail -f alert_oradb.log  --将数据库启动，查看后台日志

Errors in file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_lgwr_3812.trc:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oradb/redo01a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_lgwr_3812.trc:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oradb/redo01a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_3832.trc:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oradb/redo01a.log'

Sat Nov 24 01:42:56 2018

ARC1 started with pid=21, OS id=3836

USER (ospid: 3832): terminating the instance due to error 313

System state dump requested by (instance=1, osid=3832), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_diag_3802_20181124014256.trc

Dumping diagnostic data in directory=[cdmp_20181124014256], requested by (instance=1, osid=3832), summary=[abnormal instance termination].

Instance terminated by USER, pid = 3832

 

 

 

上面可以看到是日志文件出现问题，将库启动到mount状态查看出问题日志文件处于什么状态

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size     2260088 bytes

Variable Size   671089544 bytes

Database Buffers   390070272 bytes

Redo Buffers     5517312 bytes

Database mounted.

SQL> select group#,sequence#,members,status from v$log;

 

    GROUP#  SEQUENCE#  MEMBERS STATUS

---------- ---------- ---------- ----------------

 1    19        1 CURRENT

 4    17        2 INACTIVE

 3    15        1 INACTIVE

 2    18        1 INACTIVE

 

SQL> select name,checkpoint_change#,last_change# from v$datafile;  --查看数据库是否是正常关闭，从v$datafile控制文件的信息里面，注意v$datafile的信息是从控制文件里面获取信息的，会记录每个数据文件具体的检查点信息，CHECKPOINT_CHANGE#记录当前数据文件检查点信息，LAST_CHANGE#是当我们关库以后所生成的检查点信息，一般情况下正常关库以后CHECKPOINT_CHANGE#和LAST_CHANGE#信息保持一致，说明库是正常关库的。（正常关库会生成一个检查点，会通过CKPT进程更新数据文件和控制文件的头部）

 

NAME      CHECKPOINT_CHANGE# LAST_CHANGE#

------------------------------------------------------------ ------------------ ------------

/u01/app/oracle/oradata/oradb/system01.dbf  107730       107730

/u01/app/oracle/oradata/oradb/sysaux01.dbf  107730       107730

/u01/app/oracle/oradata/oradb/undotbs01.dbf  107730       107730

 

 

 

数据库正常关闭，当前日志组打不开导致数据库不能open怎么解决？(当前日志组还没有归档)

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

 

 

SQL> alter database clear logfile group 1;  --可以看到clear会失败，clear针对于非当前日志组才有效，这里不能使用

alter database clear logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance oradb (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oradb/redo01a.log'

 

 

SQL> alter database clear unarchived logfile group 1-- --对于当前日志组还没有做归档就被破坏了，要unarchived

 

Database altered.

 

 

SQL> alter database open;

 

Database altered.

 

SQL> select name,checkpoint_change#,last_change# from v$datafile;  --库打开之后，可以看到LAST_CHANGE#是空值

 

NAME      CHECKPOINT_CHANGE# LAST_CHANGE#

------------------------------------------------------------ ------------------ ------------

/u01/app/oracle/oradata/oradb/system01.dbf  107733

/u01/app/oracle/oradata/oradb/sysaux01.dbf  107733

/u01/app/oracle/oradata/oradb/undotbs01.dbf  107733

 

 

 

SQL> select name,checkpoint_change#,last_change# from v$datafile;

 

NAME      CHECKPOINT_CHANGE# LAST_CHANGE#

------------------------------------------------------------ ------------------ ------------

/u01/app/oracle/oradata/oradb/system01.dbf  107733

/u01/app/oracle/oradata/oradb/sysaux01.dbf  107733

/u01/app/oracle/oradata/oradb/undotbs01.dbf  107733

 

SQL> select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

---------- ---------- ----------------

 1     0 UNUSED

 2    20 CURRENT

 3    15 INACTIVE

 4    17 INACTIVE

 

SQL> alter system switch logfile;

 

System altered.

 

 

SQL>  select group#,sequence#,status from v$log;

 

    GROUP#  SEQUENCE# STATUS

---------- ---------- ----------------

 1    21 CURRENT

 2    20 ACTIVE

 3    15 INACTIVE

 4    17 INACTIVE

 

 

[oracle@Database2 trace]$ ls -l /u01/app/oracle/oradata/oradb/redo01a.log  --可以看到之前删除的日志文件自动生成了

-rw-r-----. 1 oracle oinstall 52429312 Nov 24 02:02 /u01/app/oracle/oradata/oradb/redo01a.log

 

 

 

注意：如果数据库正常关闭后，当前日志组有两个日志文件，删除其中一个，数据库还是可以打开的，因为还有另外一个数据文件保证当前日志组是可以被访问的，但是后台日志会出现报错。