文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

redo日志损坏

2024-04-02 19:55

关注
一、非正在使用的redo log损坏

1.1 归档模式,不是当前正在日志损坏,数据库打开模式。
模拟损坏:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     951

Next log sequence to archive   953

Current log sequence           953

 

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1        952   52428800        512          1 YES INACTIVE         4059877 24-JAN-18         4064774 24-JAN-18

         2          1        953   52428800        512          1 NO  CURRENT          4064774 24-JAN-18      2.8147E+14

         3          1        951   52428800        512          1 YES INACTIVE         4021213 24-JAN-18         4059877 24-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL>SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log

 

切换日志:

SQL> alter system switch logfile;

 

System altered.

 

SQL>  alter system switch logfile;

 

System altered.

 

SQL> /


查看alert日志提示错误

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc3_2018.trc:

ORA-00313:  ( 1) ?

ORA-00312:  3  1: '/u01/app/oracle/oradata/DBdb/redo03.log'

ORA-27037: ??

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

Additional information: 3

Master archival failure: 313

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc0_2012.trc:

ORA-00313:  ( 1) ?

ORA-00312:  3  1: '/u01/app/oracle/oradata/DBdb/redo03.log'

ORA-27037: ??
日常出现错误,提示不能获得该文件状态,数据无法进行日志切换

解决办法:
1、注意不需要重启数据库,只需要把日志清除即可。
SQL> alter database clear unarchived logfile group 3;

 

Database altered.

 

SQL> SQL>

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

1.2 归档模式,不是当前正在日志损坏,数据库关闭模式。

演示过程:

--先检查日志状态:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1        961   52428800        512          1 YES INACTIVE         4075623 25-JAN-18         4075626 25-JAN-18

         2          1        962   52428800        512          1 NO  CURRENT          4075626 25-JAN-18      2.8147E+14

         3          1        960   52428800        512          1 YES INACTIVE         4075620 25-JAN-18         4075623 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

--关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

--删除日志

SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo01.log

 

SQL>

 

重启数据库:

SQL> startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 14178

Session ID: 1 Serial number: 5

 

SQL>

 

报错,检查日志,如下:

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14158.trc:

ORA-00313: ??????? 1 (???? 1) ???

ORA-00312: ???? 1 ?? 1: '/u01/app/oracle/oradata/DBdb/redo01.log'

ORA-27037: ????????

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

Additional information: 3

 

解决:在数据库启动的时候清除日志,然后open数据库:

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

SQL>

SQL> alter database clear logfile group 1;   

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL>

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1          0   52428800        512          1 YES UNUSED           4075623 25-JAN-18         4075626 25-JAN-18

         2          1        962   52428800        512          1 NO  CURRENT          4075626 25-JAN-18      2.8147E+14

         3          1        960   52428800        512          1 YES INACTIVE         4075620 25-JAN-18         4075623 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

注意,如果删除的日志未归档则加一个参数alter database clear(unarchived) logfile group 1;   

 

1.3 数据库打开模式,非归档模式,非当前日志损坏

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

SQL>

SQL> alter database noarchivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL>

 

--查询:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1          0   52428800        512          1 YES UNUSED           4075623 25-JAN-18         4075626 25-JAN-18

         2          1        962   52428800        512          1 NO  CURRENT          4075626 25-JAN-18      2.8147E+14

         3          1        960   52428800        512          1 YES INACTIVE         4075620 25-JAN-18         4075623 25-JAN-18

 

SQL>  select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

--删除日志,模拟故障:

SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo03.log

 

--切换日志:

SQL> alter system switch logfile;

 

System altered.

 

SQL> alter system switch logfile;

 

System altered.

SQL> /

 

System altered.

 

SQL> /

 

System altered.

 

注意:

在执行dml语句,以及切换日志都成功,数据库日志也没有报错如下:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup 

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 14645

Session ID: 1 Serial number: 5

 

日志信息如下:

Thu Jan 25 05:18:19 2018

Thread 1 advanced to log sequence 995 (LGWR switch)

  Current log# 2 seq# 995 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log

 

Thread 1 advanced to log sequence 996 (LGWR switch)

  Current log# 1 seq# 996 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log

Thu Jan 25 05:18:30 2018

Thread 1 advanced to log sequence 997 (LGWR switch)

  Current log# 3 seq# 997 mem# 0: /u01/app/oracle/oradata/DBdb/redo03.log

 Thu Jan 25 05:19:52 2018

Thread 1 advanced to log sequence 998 (LGWR switch)

  Current log# 2 seq# 998 mem# 0: /u01/app/oracle/oradata/DBdb/redo02.log

Thread 1 advanced to log sequence 999 (LGWR switch)

  Current log# 1 seq# 999 mem# 0: /u01/app/oracle/oradata/DBdb/redo01.log

 

但是数据库启动后,如下:

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:

ORA-00313: ??????? 3 (???? 1) ???

ORA-00312: ???? 3 ?? 1: '/u01/app/oracle/oradata/DBdb/redo03.log'

ORA-27037: ????????

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

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14625.trc:

 

解决:只需要把损坏的日志文件清除日志组即可。

SQL> alter database clear logfile group 3;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

--验证:

SQL>  select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1        999   52428800        512          1 NO  CURRENT          4077012 25-JAN-18      2.8147E+14

         2          1        998   52428800        512          1 NO  INACTIVE         4077009 25-JAN-18         4077012 25-JAN-18

         3          1          0   52428800        512          1 NO  UNUSED           4076978 25-JAN-18         4077009 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

1.4非归档模式、数据库关闭、不是正在使用的日志文件损坏

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1002   52428800        512          1 NO  CURRENT          4077582 25-JAN-18      2.8147E+14

         2          1       1001   52428800        512          1 NO  INACTIVE         4077579 25-JAN-18         4077582 25-JAN-18

         3          1       1000   52428800        512          1 NO  INACTIVE         4077575 25-JAN-18         4077579 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo02.log

 

SQL>

--删除日志,模拟故障

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

--重启:

SQL> startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 14823

Session ID: 1 Serial number: 5

SQL>

 

日志报错:

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_14803.trc:

ORA-00313: ??????? 2 (???? 1) ???

ORA-00312: ???? 2 ?? 1: '/u01/app/oracle/oradata/DBdb/redo02.log'

ORA-27037: ????????

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

 

解决方案:clear日志组2

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

SQL>

SQL> alter database clear logfile group 2;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL>

 

--验证:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1002   52428800        512          1 NO  CURRENT          4077582 25-JAN-18      2.8147E+14

         2          1          0   52428800        512          1 NO  UNUSED           4077579 25-JAN-18         4077582 25-JAN-18

         3          1       1000   52428800        512          1 NO  INACTIVE         4077575 25-JAN-18         4077579 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

总结,对于不是当前使用的归档日志损坏,归档模式需要使用alter database clear unarchived 命令清空日志 组即可。对于非归档模式需要使用alter system clear 日志文件组即可。

 

二、当前正在使用的redo log损坏

2.1 归档模式,数据库open状态、当前正在使用的日志文件损坏

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>    

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

 

Database mounted.

SQL>

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

--检查:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1002   52428800        512          1 NO  CURRENT          4077582 25-JAN-18      2.8147E+14

         2          1          0   52428800        512          1 YES UNUSED           4077579 25-JAN-18         4077582 25-JAN-18

         3          1       1000   52428800        512          1 YES INACTIVE         4077575 25-JAN-18         4077579 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

--触发检查点

SQL> alter system checkpoint;

 

System altered.

 

--切换日志组

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

后台日志报错,如下:

Thu Jan 25 05:41:44 2018

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_arc2_15008.trc:

ORA-00313:  ( 1) ?

ORA-00312:  1  1: '/u01/app/oracle/oradata/DBdb/redo01.log'

ORA-27037: ??

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

Additional information: 3

Master archival failure: 313

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance DBdb - Archival Error

ORA-00313:  ( 1) ?

ORA-00312:  1  1: '/u01/app/oracle/oradata/DBdb/redo01.log'

ORA-27037: ??

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

 

 

解决:由于这个时候,虽然当前日志是正在被使用的,但是我们可以先进行切换日志之后,然后执行clear操作。

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1002   52428800        512          1 NO  INACTIVE         4077582 25-JAN-18         4078722 25-JAN-18

         2          1       1003   52428800        512          1 NO  INACTIVE         4078722 25-JAN-18         4078725 25-JAN-18

         3          1       1004   52428800        512          1 NO  CURRENT          4078725 25-JAN-18      2.8147E+14

 

SQL> alter database clear unarchived logfile group 1;

 

Database altered.

 

 

--验证:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1005   52428800        512          1 NO  CURRENT          4078821 25-JAN-18      2.8147E+14

         2          1       1003   52428800        512          1 YES INACTIVE         4078722 25-JAN-18         4078725 25-JAN-18

         3          1       1004   52428800        512          1 YES ACTIVE           4078725 25-JAN-18         4078821 25-JAN-18

 

SQL>  select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

2.2 归档模式,数据库open状态、当前正在使用的日志文件损坏,并且正常关闭数据库

--查询:

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1005   52428800        512          1 YES ACTIVE           4078821 25-JAN-18         4078866 25-JAN-18

         2          1       1006   52428800        512          1 NO  CURRENT          4078866 25-JAN-18      2.8147E+14

         3          1       1004   52428800        512          1 YES ACTIVE           4078725 25-JAN-18         4078821 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

--删除日志组2,模拟故障:

SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo02.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

--关闭数据库,重启:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 15253

Session ID: 1 Serial number: 5

 

日志报错,信息如下:

ALTER DATABASE OPEN

LGWR: STARTING ARCH PROCESSES

Thu Jan 25 05:50:23 2018

ARC0 started with pid=20, OS id=15255

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_lgwr_15233.trc:

ORA-00313: ??????? 2 (???? 1) ???

ORA-00312: ???? 2 ?? 1: '/u01/app/oracle/oradata/DBdb/redo02.log'

ORA-27037: ????????

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

Additional information: 3

 

解决:只需要进行启动到mount状态下,然后clear损坏日志即可。

SQL> conn / as sysdba

Connected to an idle instance.

SQL>   

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

SQL>

SQL>

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/DBdb/redo02.log'

 

 

SQL> alter database clear unarchived logfile group 2;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL>

 

--验证:

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

SQL>  select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1009   52428800        512          1 NO  CURRENT          4079417 25-JAN-18      2.8147E+14

         2          1       1008   52428800        512          1 YES ACTIVE           4079414 25-JAN-18         4079417 25-JAN-18

         3          1       1007   52428800        512          1 YES ACTIVE           4078867 25-JAN-18         4079414 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

SQL>

 

2.3 归档模式,数据库open状态、当前正在使用的日志文件损坏,并且异常关闭数据库

--检查:

SQL>  select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1009   52428800        512          1 NO  CURRENT          4079417 25-JAN-18      2.8147E+14

         2          1       1008   52428800        512          1 YES ACTIVE           4079414 25-JAN-18         4079417 25-JAN-18

         3          1       1007   52428800        512          1 YES ACTIVE           4078867 25-JAN-18         4079414 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo01.log  /u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

--删除日志组1,模拟故障

SQL> !rm -rf /u01/app/oracle/oradata/DBdb/redo01.log

 

SQL>  !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

 

--模拟异常关机:
SQL>  shutdown abort;

ORACLE instance shut down.

SQL>

 

--重启:

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

SQL> startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/DBdb/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 OPEN

Beginning crash recovery of 1 threads

Started redo scan

Errors in file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_15477.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/DBdb/redo01.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

Aborting crash recovery due to error 313

 

解决:这时候我们有两种办法,一种是使用备份进行恢复,另一种是使用隐含参数。介绍第二种:

SQL> conn / as sysdba

Connected.

SQL>

SQL> startup mount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL>

SQL> select status from v$instance;

 

STATUS

----------

MOUNTED

 

SQL> select ksppinm, ksppstvl from x$ksppi x, x$ksppcv y  where (x.indx = y.indx) and (translate(ksppinm, '_', '#')) like '_allow_resetlogs_corruption';

 

KSPPINM                        KSPPSTVL

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

_allow_resetlogs_corruption    FALSE

 

SQL> 

该参数是在数据库不一致的情况下,重置日志文件。

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>

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             549456976 bytes

Database Buffers          281018368 bytes

Redo Buffers                2371584 bytes

Database mounted.

SQL>

 

--查看查询及日志情况:

SQL> select ksppinm, ksppstvl from x$ksppi x, x$ksppcv y  where (x.indx = y.indx) and (translate(ksppinm, '_', '#')) like '_allow_resetlogs_corruption';

 

KSPPINM                        KSPPSTVL

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

_allow_resetlogs_corruption    TRUE

 

SQL>

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1       1009   52428800        512          1 NO  CURRENT          4079417 25-JAN-18      2.8147E+14

         3          1       1007   52428800        512          1 YES ACTIVE           4078867 25-JAN-18         4079414 25-JAN-18

         2          1       1008   52428800        512          1 YES ACTIVE           4079414 25-JAN-18         4079417 25-JAN-18

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/DBdb/redo03.log

/u01/app/oracle/oradata/DBdb/redo02.log

/u01/app/oracle/oradata/DBdb/redo01.log

 

SQL> !ls /u01/app/oracle/oradata/DBdb/redo*

/u01/app/oracle/oradata/DBdb/redo02.log  /u01/app/oracle/oradata/DBdb/redo03.log

 

--尝试1:

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 DBdb (thread 1)

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/DBdb/redo01.log'

 

--尝试2:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

 

--恢复在resetlogs:

SQL> recover database until cancel;

ORA-00279: change 4079417 generated at 01/25/2018 05:53:46 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc

ORA-00280: change 4079417 for thread 1 is in sequence #1009

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%u_.arc'

ORA-27037: unable to obtain file status

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

Additional information: 3

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_1009_%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/DBdb/system01.dbf'

 

--resetlogs方式打开数据库:

SQL> alter database open resetlogs;

 

Database altered.

 

SQL>  

 

--检查:

SQL> select * from v$log;
 

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME

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

         1          1          1   52428800        512          1 NO  CURRENT          4079418 25-JAN-18      2.8147E+14

         2          1          0   52428800        512          1 YES UNUSED                 0                         0

         3          1          0   52428800        512          1 YES UNUSED                 0                         0

 

SQL> select member from v$logfile;

 

MEMBER

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 资料下载
  • 历年真题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯