OPEN RESETLOGS WITHOUT RECOVERY

OPEN RESETLOGS WITHOUT RECOVERY

Amazing Oracle Trick I found today while I am Browsing OTN , I see to post it here .

What is Open reset log mode : 

Online redo logs are re-created . The log sequence is reset to 1.

If the database is running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists. 

First I will show its Normal Database this mean not in recovery mode :

sqlplus / as sysdba

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit  

Now Lets Open it :

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database;
ORA-00283: recovery session cancelled due to errors
ORA-00264: no recovery required


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


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

Database altered.

SQL>