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

ORA-00313 open failed for members of log group solution

Summary of experience:

Online logs are divided into current online logs and non-current online logs. The damage of non-current online logs is relatively simple. Generally, the problem can be solved through the clear command.

Corruption of non-current online logs:

1. Start the database and encounter an ORA-00312 or ORA-00313 error, such as:

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

ORA-00312: online log 3 thread 1:'/ opt/oracle/db04/oradata/ORCL/redo01.log'

From here, we know that the data file of log group 1 is corrupted or missing.

More detailed information can be seen from the alarm file

2. View the V$log view:

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

GROUP# SEQUENCE# ARC STATUS

-

1 54 YES INACTIVE

2 55 NO CURRENT

3 53 YES INACTIVE

As you can see, the group is not in its current state and has been archived.

3. Rebuild the log file with the CLEAR command

SQL > alter database clear logfile group 3

If the log group has not been archived, you need to use the

SQL > alter database clear unarchived logfile group 3

4. Open the database and back up the database again

SQL > alter database open

Description:

1) if a non-current online log file is damaged, usually only clear is needed to rebuild the log file, but if the database is archived but the log has not been archived,

Need to force clear.

2) it is recommended that clear, especially after forced clear, make a full backup of the database.

3) this method is suitable for archived and non-archived databases.

Corrupt the current online log:

There are two cases of damage to the current log in archive mode

First, the database is shut down normally, and there are no outstanding transactions in the log file that need to be recovered by the instance. If the current log group is damaged, you can use alter database clear unarchived directly.

Logfile group n to rebuild.

Second, there are active transactions in the log group, the database needs media recovery, and the log group needs to be used for synchronization. There are two remedies.

a. The best way is to ensure database consistency through incomplete recovery, but this approach requires archived and available backups

b. Through mandatory recovery, but may result in database inconsistencies.

The following are used to illustrate these two recovery methods

5.1.2.1 restore by backup

1. When you open the database, you will encounter a similar error

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

ORA-00312: online log 1 thread 1: 'DVR ORACLEORADATESTREDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file

2. Check the V$log and find that it is the current log

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

GROUP# SEQUENCE# ARCHIVED STATUS

--

1 1 NO CURRENT

2 2

YES INACTIVE

3 3 YES INACTIVE

3. Clear was found to be unsuccessful.

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 thread 1

ORA-00312: online log 1 thread 1: 'DVR ORACLEORADATESTREDO01.LOG'

4. Copy the full backup of the valid database and not completely restore the database.

You can recover with until scn or with until cnacel by getting the most recent SCN

Recover database until cancel

First select auto, restore as many archive logs as possible, and then re-

Recover database until cancel

This time, type cancel to complete the incomplete recovery, that is to say, restore twice.

Such as:

SQL > recover database until cancel

Auto

……

SQL > recover database until cancel

Cancel

5. Open the database with alter database open resetlogs

Description:

1. The database recovered by this method is consistent and incomplete, and the transaction data in the current online log will be lost.

2. This method is suitable for archiving databases and there are full backups of databases available.

3. After the recovery is successful, remember to make a full backup of the database again.

4. It is recommended that online log files must be mirrored on different disks to avoid this situation, because any loss of data is not allowed for production.

If there is no backup, perform a mandatory restore

1. When you open the database, you will encounter a similar error

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

ORA-00312: online log 1 thread 1: 'DVR ORACLEORADATESTREDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file

2. Check the V$log and find that it is the current log

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

GROUP# SEQUENCE# ARCHIVED STATUS

--

1 1 NO CURRENT

2 2 YES INACTIVE

3 3 YES INACTIVE

3. Clear was found to be unsuccessful.

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 thread 1

ORA-00312: online log 1 thread 1: 'DVR ORACLEORADATESTREDO01.LOG'

4. Drop the database down

SQL > shutdown immediate

5. Add the following parameters to init.ora

_ allow_resetlogs_corruption=TRUE

6. Restart the database and use until cancel to recover

SQL > recover database until cancel

Cancel

If something goes wrong, ignore it and issue

SQL > alter database open resetlogs

7. Execute a full export as soon as the database is opened

8. Shutdown database, remove the _ all_resetlogs_corrupt parameter

9. Rebuild the library

10. Import and complete the recovery

11. It is recommended that ANALYZE TABLE... VALIDATE STRUCTURE CASCADE be implemented.

Description:

1. This recovery method is a recovery method after which there is no way, and it is generally recommended not to use it, because this method may lead to database inconsistency.

2. This method also loses data, but the data lost is not as much as that of the previous method, mainly committed or uncommitted data that has not been written to the data file.

3. After success, it is recommended that the above steps 7 to 11 be strictly carried out to complete the inspection and analysis of the database.

4. Make a full backup of the database after it is all completed

5. It is recommended that online log files must be mirrored on different disks to avoid this situation, because any loss of data is not allowed for production.

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.