Oracle login produces a 01033 Error

The restoration method for damaged online logs is not clear to everyone. Online logs are divided into current online logs and non-current online logs. the corruption of Non-current online logs is compared.

The restoration method for damaged online logs is not clear to everyone. Online logs are divided into current online logs and non-current online logs. the corruption of Non-current online logs is compared.

How to restore damaged online logs

Corrupt non-current online logs

It is clear that online logs are divided into current online logs and non-current online logs. the corruption of Non-current online logs is relatively simple. Generally, you can solve the problem by using the clear command.

1. Start the database, encountered a ORA-00312 or ORA-00313 error, such

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

ORA-00312: online log 1 thread 1: 'd: \ Oracle \ ORADATA \ TEST \ REDO01.LOG'

Here we know that the data file in log group 1 is corrupted.

You can see more detailed information from the alarm File

2. view the V $ log view

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

GROUP # SEQUENCE # ARCHIVED STATUS

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

1 1 YES INACTIVE

2 2 YES INACTIVE

3 3 NO CURRENT

The Group is not in the current status and has been archived.

3. Use the CLEAR command to recreate the log file.

SQL> alter database clear logfile group 1;

If the log group has not been archived, use

SQL> alter database clear unarchived logfile group 1;

4. Open the database and back up the database again.

SQL> alter database open;

Note:

1. If the damaged online log file is not the current one, you can rebuild the log file with only clear. However, if the database is archived but the log is not archived, you need to force clear.

2. We recommend that you clear the database, especially after force clear.

3. This method is applicable to archive and non-archive databases.

5.1.2 damage the current online log

There are two types of log corruption in archive mode,

1. The database is shut down normally, and no pending transactions in the log file need to be recovered by the instance. If the current log group is damaged, you can directly use alter database clear unarchived logfile group n for reconstruction.

2. There are active transactions in the log group, the database needs to be restored by media, and the log group needs to be used for synchronization. There are two ways to remedy this problem.

A. The best way is to ensure Database Consistency through Incomplete recovery. However, this method requires that the archive method and available backup be available.

B. Forced recovery may cause Database Inconsistency.

The following describes the two restoration methods.

5.1.2.1 restore through backup

1. A similar error occurs when you open the database.

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

ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.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. view the V $ log and find 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. failed to find clear

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: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'

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

Until SCN recovery or until cnacel recovery can be used to obtain the nearest scn.

Recover database until cancel

Select auto first, restore the available archived logs as much as possible, and then try again

Recover database until cancel

The input cancel is incomplete, that is, it is restored twice.

For example:

SQL> recover database until cancel;

Auto

......

SQL> recover database until cancel;

Cancel;

5. Use alter database open resetlogs to open a database

Note:

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

2. This method is suitable for archiving databases and full backup of available databases.

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

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

5.1.2.2 if no backup is available, perform forced recovery

1. A similar error occurs when you open the database.

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

ORA-00312: online log 1 thread 1: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.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. view the V $ log and find 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. failed to find clear

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: 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG'

4. Drop the database

SQL> shutdown immediate

5. In init Add the following parameters to. ora:

_ Allow_resetlogs_upload uption = TRUE

6. Restart the database and use until cancel to restore the database.

SQL> recover database until cancel;

Cancel

If an error occurs, ignore it and send it.

SQL> alter database open resetlogs;

7. Execute a full export immediately after the database is opened.

8. Shut down the database and remove the _ all_resetlogs_upt parameter.

9. Rebuilding the database

10. import and restore

11. Perform analyze table... validate structure cascade;

Note:

1. There is no way to restore the database. We recommend that you do not use this method in general, because this method may cause Database Inconsistency.

2. This method also loses data, but the lost data does not have much data in the previous method, mainly because the submitted or not submitted data that has not been written to the data file.

3. We recommend that you strictly perform the above 7 to 11 steps after successful execution to check and analyze the database.

4. Make a full backup of the database after completing all the tasks.

5. It is recommended that the online log files be stored on different disks to avoid this situation, because any data loss is unacceptable for production.