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 sysdbaConnected 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>
Technology Geek 
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
LikeLike
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}
LikeLike
Dear Usama can u help me my oracle has same message redo log has stack over flow i run the same command & it give the message.
LikeLike