Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Redo log is corrupted

2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

First, the redo log damage 1.1 archive mode that is not in use, is not currently in the log corruption, database open mode.

Simulated damage:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

Toggle logs:

SQL > alter system switch logfile

System altered.

SQL > alter system switch logfile

System altered.

SQL > /

Viewing the alert log indicates an error

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

ORA-00313: 1)?

ORA-00312: 31:'/ u01 *

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: 31:'/ u01 *

ORA-27037:??

Daily error indicates that the status of the file cannot be obtained, and the data cannot be switched to log.

Solution:

1. Note that there is no need to restart the database, you just need to clear the log.

SQL > alter database clear unarchived logfile group 3

Database altered.

SQL > SQL >

SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 Archive mode, not currently in log corruption, database shutdown mode.

Demo process:

-- check the log status first:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-- close the database

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

-- delete the log

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

SQL >

Restart the database:

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 >

Report an error and check the log, as follows:

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

ORA-00313:? 1?

ORA-00312:? 1? 1:'/ u01OnOnOnPlacleUniverse oradataPlacement DBDBDB redo01.log'

ORA-27037:?

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

Additional information: 3

Solution: clear the log when the database starts, and then open the database:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

SQL >

Note that if the deleted log is not archived, add a parameter alter database clear (unarchived) logfile group 1

1.3 Database open mode, non-archive mode, non-current log corruption

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 >

-- query:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

SQL >

-- Delete the log and simulate the fault:

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

-- switch logs:

SQL > alter system switch logfile

System altered.

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

SQL > /

System altered.

Note:

Both the dml statement and the log switch were successful, and the database log did not report an error as follows:

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

The log information is as follows:

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

However, after the database is started, it is as follows:

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:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB 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:

Solution: only need to clear the log group of corrupted log files.

SQL > alter database clear logfile group 3

Database altered.

SQL > alter database open

Database altered.

-- verify:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 non-archived mode, database shutdown, corrupted log files that are not in use

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 >

-- Delete logs and simulate faults

SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

SQL >

-- restart:

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 >

Log error:

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:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB redo02.log'

ORA-27037:?

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

Solution: clear log group 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 >

-- verify:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

SQL >

To sum up, for archive log corruption that is not currently in use, the archive mode requires the alter database clear unarchived command to empty the log group. Alter system clear log filegroups are required for non-archive mode.

Second, the redo log corruption 2.1archiving mode currently in use, the database open status and the log files currently in use are corrupted.

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.

-- check:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-- trigger checkpoint

SQL > alter system checkpoint

System altered.

-- switch log groups

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

An error is reported in the backend log, as follows:

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 *

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 *

ORA-27037:??

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

Solution: because at this time, although the current log is in use, we can switch the log first and then perform the clear operation.

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.

-- verify:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 Archive mode, database open status, log files currently in use are corrupted, and the database is shut down normally

-- query:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

SQL >

-- Delete log group 2 to simulate the failure:

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

SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-- close the database and restart:

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

An error is reported in the log. The information is as follows:

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:'/ u01OnOnOnPlacleUniverse oradataandDBDBDB redo02.log'

ORA-27037:?

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

Additional information: 3

Solution: just boot to the mount state, and then clear corrupts the log.

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:'/ u01Accord oradata online log redo02.log'

SQL > alter database clear unarchived logfile group 2

Database altered.

SQL > alter database open

Database altered.

SQL >

-- verify:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple 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 Archive mode, database open status, log files currently in use are corrupted, and the database is shut down abnormally

-- check:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-- Delete log group 1 to simulate failure

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

SQL >! ls / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-- simulated abnormal shutdown:

SQL > shutdown abort

ORACLE instance shut down.

SQL >

-- restart:

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:'/ u01According to an oracle or oradata, or DBDBDB, redo01.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

SQL >

An error is reported in the acquired log:

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:'/ u01According to an oracle or oradata, or DBDBDB, 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

Solution: at this time, we have two ways, one is to use backup for recovery, and the other is to use implicit parameters. Introduce the second type:

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 >

This parameter resets the log file if the database is inconsistent.

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 >

-- check the query and log:

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

-try 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:'/ u01According to an oracle or oradata, or DBDBDB, redo01.log'

-- try 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

-- restore in 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'/ u01ActionActionoracleUniverse fastfastDBBUBUBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG

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'/ u01ActionActionoracleUniverse fastfastDBBUBUBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG

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:'/ u01Accord oradata system 01.dbf'

-- Open the database in resetlogs mode:

SQL > alter database open resetlogs

Database altered.

SQL >

-- check:

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 00

3 1 0 52428800 512 1 YES UNUSED 00

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 / u01qqapp.oracleUnixoradataUniverse DBdbUniple redo *

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

Rebuild the instance and then use expdp and impdp to export the data into the database

SQL > create directory expdp as'/ opt/app/oracle/oradata'

Directory created.

Then export the data to rebuild the database and import the data.

Summary: the damage to the logs currently in use is generally repaired by backup, and if not, you can only use the second setting implicit parameter _ allow_resetlogs_corruption to recover.

Summary

-- query redo information

Col member for a50

Set lines 120

SELECT thread#

A.sequence#

A.group#

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

A.status

MEMBER

FROM v$log a, v$logfile b

WHERE a.group# = B.GROUP#

ORDER BY a.sequence# DESC

1) inactive redo exception

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 is abnormal and the database is shut down normally

Alter database clear unarchived logfile group 1

ALTER DATABASE drop logfile group 1

Recover database until cancel

Alterdatabaseopenresetlogs

3) current/active redo exception and database shutdown exception

Alter database clear unarchived logfile group 1

ALTER DATABASE drop logfile group 1

Recover database until cancel

If you cannot restore it, you can only set the _ allow_resetlogs_corruption parameter to skip the check.

Alter system set "_ allow_resetlogs_corruption" = true scope=spfile

Shutdown immediate

Startup mount

Alter database open resetlogs

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report