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