Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to do redolog damage in oracle", which is easy to understand and clear, hoping to help you solve your doubts. Let the editor lead you to study and learn this article "how to do redolog damage in oracle".

How to deal with redolog damage

Damage is not the current online log everyone knows, online log is divided into the current online log and non-current online log, non-current online log damage is relatively simple, generally through the clear command can solve the problem.

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 1 thread 1: 'DVR ORACLEORADATESTREDO01.LOG'

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

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# ARCHIVED STATUS

--

11 YESINACTIVE

22 YESINACTIVE

33 NOCURRENT

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 1

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

SQL > alter database clear unarchived logfile group 1

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, you only need clear to rebuild the log file, but if the database is archived but the log has not been archived, you 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.

There are two cases of corruption of the current log in the current online log archive mode

First, the database is normally closed, there are no outstanding transactions in the log file that need instance recovery, and the damage to the current log group can be rebuilt directly with alter database clear unarchived logfile group n.

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

--

11 NOCURRENT

22 YESINACTIVE

33 YESINACTIVE

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. Open the database and 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

--

11 NOCURRENT

22 YESINACTIVE

33 YESINACTIVE

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.

The above is all the contents of the article "what to do about redolog damage in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.