LEARN HOW OPEN RESETLOGS WITHOUT RECOVERY

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

What is Open resetlog mode 
 
Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis 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 canceled 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> 
 
 
 

 
 
 

3 thoughts on “LEARN HOW OPEN RESETLOGS WITHOUT RECOVERY

  1. SQL> recover database until cancel;ORA-00279: change 363658921 generated at 12/10/2017 04:28:58 needed for thread1ORA-00289: suggestion : E:\ORACLE\ORA92\RDBMS\ARC01331.001ORA-00280: change 363658921 for thread 1 is in sequence #1331Specify log: {=suggested | filename | AUTO | CANCEL}Dear Usama when I run that command this message appred

    Like

  2. SQL> recover database until cancel;ORA-00279: change 363658921 generated at 12/10/2017 04:28:58 needed for thread1ORA-00289: suggestion : E:\ORACLE\ORA92\RDBMS\ARC01331.001ORA-00280: change 363658921 for thread 1 is in sequence #1331Specify log: {=suggested | filename | AUTO | CANCEL}

    Like

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 )

Google+ photo

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

Twitter picture

You are commenting using your Twitter 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.