Restoration Methods for deleting online redo logs of Oracle databases

Restoration Methods for deleting online redo logs of Oracle databases

The online redo log of the Oracle database contains Operation Records of all data in the database. We can use the redo log to perform many operations, such as log mining.

Sometimes, for various reasons, our online logs are deleted by mistake or accidentally damaged. How can we restore them? In fact, it is very simple. See the following content:

We can delete online logs to simulate the accidental deletion of logs:

[Oracle @ test orcl] $ rm redo *
[Oracle @ test orcl] $ ls-l redo *
Ls: cannot access redo *: No file or directory
[Oracle @ test orcl] $ sqlplus/as sysdba
SQL> startup mount
The ORACLE routine has been started.
...
The database has been loaded.

Because we only lack online redo logs, the database can be started to the mount state. The mount State database only opens the control file and does not verify the status of each data file, the verification is performed in the open stage.

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-03113: End of the file for the communication channel
Process ID 4607
Session ID: 125 serial number: 5

When the database is opened, an error is reported and the database is forcibly disabled.

Next we will try to open the database using the resetlogs method:

SQL> recover database until cancel;
Media recovery is completed.
SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS Option
SQL> alter database open resetlogs;
The database has been changed.

The resetlogs option must be used only after the database is not completely recovered.

In addition to this method, we can also open the database by clearing logfile, as shown below:

First, start the database to the mount status.

Query the v $ log view:

SQL> select * from v $ log;
GROUP # THREAD # SEQUENCE # bytes blocksize members arc status FIRST_CHANGE # FIRST_TIME NEXT_CHANGE # NEXT_TIME
----------------------------------------------------------------------------------------------------------------------------------------------
1 1 1 134217728 5121 no current 984719 16:04:30 2.8147E + 14
3 1 0 134217728 5121 yes unused 0 0
2 1 0 134217728 5121 yes unused 0 0

If the ARCHIVED field is YES, we can use

Run the alter database clear logfile command to clear the log. If it is No, we can use
Alter database clear unarchived logfile to forcibly clear
SQL> alter database clear logfile group 2;
The database has been changed.
SQL> alter database clear logfile group 3;
The database has been changed.
SQL> alter database clear unarchived logfile group 1;
Alter database clear unarchived logfile group 1
*
Row 3 has an error:
ORA-01624: Log 1 is required for emergency recovery instance orcl (thread 1)
ORA-00312: Online log 1 thread 1: '/app/oradata/orcl/redo01.log'

However, since group 1 is the current online log, and I used shutdown abort to close the database

The status of the data file is inconsistent. You need to use the current log to restore the instance. Therefore, you cannot clear the log by clearing the log command.

If the database file status is consistent, we can use the alter database open command to open the database. However, if such inconsistency occurs, we also need to use resetlogs to open the database, as shown below:

SQL> recover database until cancel;
ORA-00279: Change 984722 (generated at 16:04:43) is required for thread 1
ORA-00289: Recommended:/app/archivelog/orcl_1_890582670.dbf
ORA-00280: Change 984722 (for thread 1) in sequence #1
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '/app/archivelog/orcl_1_890582670.dbf'
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 '/app/archivelog/orcl_1_890582670.dbf'
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 wocould get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/app/oradata/orcl/system01.dbf'
SQL> recover database until cancel
ORA-00279: Change 984722 (generated at 16:04:43) is required for thread 1
ORA-00289: Recommended:/app/archivelog/orcl_1_890582670.dbf
ORA-00280: Change 984722 (for thread 1) in sequence #1
Specified log: {<RET> = suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/app/oradata/orcl/system01.dbf'
ORA-01112: Media recovery not started
SQL> alter database open resetlogs;
Alter database open resetlogs
*
Row 3 has an error:
ORA-01194: File 1 requires more recovery to maintain consistency
ORA-01110: Data File 1: '/app/oradata/orcl/system01.dbf'

However, we found that incomplete Recovery fails. In this case, it is impossible to open a database through resetlogs. Therefore, we can only open a database with inconsistent statuses through implicit parameters of the application, as follows:

SQL> create pfile = '/home/oracle/p2.ora' from spfile;
Add *. _ allow_resetlogs_uption = TRUE to the pfile.
Echo "*. _ allow_resetlogs_uption = TRUE"> p2.ora
Then open the database to the mount state through the new pfile:
SQL> startup mount pfile = '/home/oracle/p2.ora'
The ORACLE routine has been started.
Total System Global Area 334036992 bytes
Fixed Size 2253024 bytes
Variable Size 171970336 bytes
Database Buffers 155189248 bytes
Redo Buffers 4624384 bytes
The database has been loaded.
Then, use the resetlogs method to open the database.
SQL> alter database open resetlogs;
The database has been changed.

Because we started with the temporary pfile, we need to complete the last step and restart the database.

Okay, the database is opened, but it may be faulty because our database is recovered from an exception. We recommend that you back up the database to prevent data loss.