archive log list; Database log mode ..."/>
溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

redo日志損壞

發(fā)布時(shí)間：2020-08-05 02:47:39 來(lái)源：ITPUB博客 閱讀：132 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫(kù)
一、非正在使用的redo log損壞

1.1 歸檔模式，不是當(dāng)前正在日志損壞，數(shù)據(jù)庫(kù)打開(kāi)模式。
模擬損壞：

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日志提示錯(cuò)誤

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: ??
日常出現(xiàn)錯(cuò)誤，提示不能獲得該文件狀態(tài)，數(shù)據(jù)無(wú)法進(jìn)行日志切換

解決辦法：
1、注意不需要重啟數(shù)據(jù)庫(kù)，只需要把日志清除即可。
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 歸檔模式，不是當(dāng)前正在日志損壞，數(shù)據(jù)庫(kù)關(guān)閉模式。

演示過(guò)程：

--先檢查日志狀態(tài)：

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

 

--關(guān)閉數(shù)據(jù)庫(kù)

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

--刪除日志

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

 

SQL>

 

重啟數(shù)據(jù)庫(kù)：

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>

 

報(bào)錯(cuò)，檢查日志，如下：

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

 

解決：在數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候清除日志，然后open數(shù)據(jù)庫(kù)：

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>

注意，如果刪除的日志未歸檔則加一個(gè)參數(shù)alter database clear(unarchived) logfile group 1;   

 

1.3 數(shù)據(jù)庫(kù)打開(kāi)模式，非歸檔模式，非當(dāng)前日志損壞

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.

 

注意：

在執(zhí)行dml語(yǔ)句，以及切換日志都成功，數(shù)據(jù)庫(kù)日志也沒(méi)有報(bào)錯(cuò)如下：

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

 

但是數(shù)據(jù)庫(kù)啟動(dòng)后，如下：

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.

 

--驗(yàn)證：

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非歸檔模式、數(shù)據(jù)庫(kù)關(guān)閉、不是正在使用的日志文件損壞

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>

 

日志報(bào)錯(cuò)：

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>

 

--驗(yàn)證：

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>

 

總結(jié)，對(duì)于不是當(dāng)前使用的歸檔日志損壞，歸檔模式需要使用alter database clear unarchived 命令清空日志 組即可。對(duì)于非歸檔模式需要使用alter system clear 日志文件組即可。

 

二、當(dāng)前正在使用的redo log損壞

2.1 歸檔模式，數(shù)據(jù)庫(kù)open狀態(tài)、當(dāng)前正在使用的日志文件損壞

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

 

--觸發(fā)檢查點(diǎn)

SQL> alter system checkpoint;

 

System altered.

 

--切換日志組

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

后臺(tái)日志報(bào)錯(cuò)，如下：

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

 

 

解決：由于這個(gè)時(shí)候，雖然當(dāng)前日志是正在被使用的，但是我們可以先進(jìn)行切換日志之后，然后執(zhí)行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.

 

 

--驗(yàn)證：

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 歸檔模式，數(shù)據(jù)庫(kù)open狀態(tài)、當(dāng)前正在使用的日志文件損壞，并且正常關(guān)閉數(shù)據(jù)庫(kù)

--查詢：

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

 

--關(guān)閉數(shù)據(jù)庫(kù)，重啟：

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

 

日志報(bào)錯(cuò)，信息如下：

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

 

解決：只需要進(jìn)行啟動(dòng)到mount狀態(tài)下，然后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>

 

--驗(yàn)證：

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 歸檔模式，數(shù)據(jù)庫(kù)open狀態(tài)、當(dāng)前正在使用的日志文件損壞，并且異常關(guān)閉數(shù)據(jù)庫(kù)

--檢查：

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

 

 

--模擬異常關(guān)機(jī)：
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>

后天日志報(bào)錯(cuò)：

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

 

解決：這時(shí)候我們有兩種辦法，一種是使用備份進(jìn)行恢復(fù)，另一種是使用隱含參數(shù)。介紹第二種：

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> 

該參數(shù)是在數(shù)據(jù)庫(kù)不一致的情況下，重置日志文件。

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

 

--恢復(fù)在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方式打開(kāi)數(shù)據(jù)庫(kù)：

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

/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

 

重建實(shí)例然后使用expdp和impdp，將數(shù)據(jù)導(dǎo)出在導(dǎo)入數(shù)據(jù)庫(kù)
SQL> create directory expdp as '/opt/app/oracle/oradata';
Directory created.
然后導(dǎo)出數(shù)據(jù)重建數(shù)據(jù)庫(kù)，在導(dǎo)入數(shù)據(jù)。

 


總結(jié)：對(duì)于當(dāng)前正在使用的日志的損壞，一般通過(guò)備份來(lái)修復(fù)，如果不行只能采用第二種設(shè)置隱含參數(shù)_allow_resetlogs_corruption來(lái)恢復(fù)。

 

總結(jié)

--查詢redo信息

col member for a50

set lines 120

  SELECT thread#,

         a.sequence#,

         a.group#,

         TO_CHAR (first_change#, '9999999999999999') "SCN",

         a.status,

         MEMBER

    FROM v$log a, v$logfile b

   WHERE a.group# = B.GROUP#

ORDER BY a.sequence# DESC;

 

1) inactive redo異常

alter database clear unarchived logfile group 1;

ALTER DATABASE drop logfile group 1;

Alter database open;

alter database add logfile group 1 ('/oracle/app/oracle/oradata/cus/redo01.log') size 200M reuse;

 

2) current/active redo異常，數(shù)據(jù)庫(kù)正常關(guān)閉

 

alter database clear unarchived logfile group 1;

ALTER DATABASE drop logfile group 1;

recover database until cancel;

alterdatabaseopenresetlogs;

 

 

3）current/active redo異常，數(shù)據(jù)庫(kù)異常關(guān)閉

alter database clear unarchived logfile group 1;

ALTER DATABASE drop logfile group 1;

recover database until cancel;

如果恢復(fù)不了，只能設(shè)置_allow_resetlogs_corruption參數(shù)跳過(guò)檢查

alter system set "_allow_resetlogs_corruption"=true scope=spfile;

shutdown immediate;

startup mount

alter database open resetlogs;

 

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手