How to fix issue when the online redo logs are missing?

If for some reason the online redo log were lost or when cloning the copy of shutdown database you don’t have the on-line redo logs, you would need to open the database with “RESETLOGS” option which will create the online redo logs.

Example:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 356517584 bytes
Database Buffers 155189248 bytes
Redo Buffers 8052736 bytes
SQL> alter database mount;

Database altered.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

-- shows online redo logs have been recreated
SQL> select member from v$logfile;

MEMBER
—————————————–
E:\ORACLE\DB\TESTDB\ORADATA\REDO01A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO01B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG

6 rows selected.

One has perform “RECOVER DATABASE UNTIL CANCEL” before opening the database, if one doesn’t Oracle will generate the following error when trying to open with resetlogs:
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.