08 November, 2008

OPEN RESETLOGS without really doing a Recovery

The OPEN RESETLOGS command does a check to see if it has been called after an Incomplete Recovery and does not execute if the database is in a NORMAL shutdown state.


Here is how the "Incomplete Recovery" could be simulated :
(I learnt this trick from HJR (dizwell) on forums.oracle.com !)

First I confirm that the database is a NORMAL shutdown :

ora10204>sqlplus
/ as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 8 23:23:43 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name:
startup
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 I simulate a Recovery and then do an OPEN RESETLOGS :

ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 8 23:25:43 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: / 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>



The first "RECOVER DATABASE" command failed with an ORA-264 error. This is explained as :


00264, 00000, "no recovery required"
// *Cause: An attempt was made to perform media recovery on files that do not // need any type of recovery.
// *Action: Do not attempt to perform media recovery on the selected
// files. Check to see that the filenames were entered properly.
// If not, retry the command with the proper filenames.


The "UNTIL CANCEL" in the second RECOVER DATABASE was an instruction to Oracle that I was attempting an Incomplete Recovery.
Therefore, the OPEN RESETLOGS after that allowed me to proceed !
These are the messages in the alert.log relating to the second (simulated Incomplete Recovery) :


Sat Nov 8 23:26:37 2008
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
Sat Nov 8 23:26:37 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:38 2008
parallel recovery started with 2 processes
Media Recovery Not Required
Sat Nov 8 23:26:38 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
SUCCESS: diskgroup DG_1_2G was dismounted
Sat Nov 8 23:26:38 2008
Completed: ALTER DATABASE RECOVER database until cancel
Sat Nov 8 23:26:43 2008
alter database open resetlogs
Sat Nov 8 23:26:46 2008
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:48 2008
RESETLOGS after complete recovery through change 99552446
Resetting resetlogs activation ID 4148073936 (0xf73e95d0)
Sat Nov 8 23:26:53 2008
Setting recovery target incarnation to 11
Sat Nov 8 23:26:53 2008
SUCCESS: diskgroup DG_1_2G was dismounted
SUCCESS: diskgroup DG_1_2G was mounted
Sat Nov 8 23:26:54 2008
Assigning activation ID 4150071358 (0xf75d103e)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=4290
Sat Nov 8 23:26:54 2008
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=24, OS id=4292
Sat Nov 8 23:26:54 2008
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Successful open of redo thread 1


No Redo actually needed to be applied yet, Oracle did do an OPEN RESETLOGS and change the activation ID and reset the Log Sequence Number to 1.

Here we can see that this was the 11th incarnation of this database :


SQL> select dbid, name, created, resetlogs_change#, resetlogs_time, activation#, recovery_target_incarnation#, last_open_incarnation#, current_scn
2 from v$database;

DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME
---------- --------- ------------------ ----------------- ------------------
ACTIVATION# RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN
----------- ---------------------------- ---------------------- -----------
4137213278 ORT24FS 14-JUN-08 23:28:30 99552447 08-NOV-08 23:26:46
4150071358 11 11 99553429


SQL>
SQL> select incarnation#, resetlogs_change#, resetlogs_time, status, resetlogs_id from v$database_incarnation where incarnation#=11;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------ ------- ------------
11 99552447 08-NOV-08 23:26:46 CURRENT 670289206

SQL>



Therefore, it is possible to simulate an Incomplete Recovery to fool the ALTER DATABASE OPEN RESETLOGS into thinking that Recovery is Incomplete -- even though we know we have not lost any transactions as the last shutdown was a NORMAL shutdown.

25 comments:

Anonymous said...

Hi Hemant,
The Purpose of this type of recovery is, one can say, can be used to just Re-initiate LOG SEQUENCE NUMBER.
But is it really necessory?
is there any limit for maximum of LOG SEQUENCE NUMBER?

Regards!

Hemant K Chitale said...

I didn't purport to explain *why* someone would want to reset Log Sequence numbers.
The DBA might be wanting to discard all his redo logs -- eg they were on a disk / filesystem that got damaged [someone mistakenly ran "mkfs" or "rm *" on it ?] after the last SHUTDOWN NORMAL during a hardware / OS maintenance activity / filesystem reorganisation ?

Pascal said...

Hi Hemant;
1-)
when I restore controlfile and datafiles and perform media recovery,
Why do I always need to open resetlog?

2-)
Is there a difference between
"alter database open"
and
"alter database open noresetlogs"

Hemant K Chitale said...

Pascal,
Qn 1 :
A RESETLOGS is required in either of

a. Recovery using BACKUP CONTROLFILE
b. Incomplete Recovery

If you are using a Backup controlfile (whether from a Binary Backup or actually via a CREATE CONTROLFILE), the Controlfile is not current -- therefore it does not have the database SCN and LogSequenceNumbers. The RESETLOGS updates this information back to the controlfile (normally, a Recovery is the other way round with the controlfile's SCN, being the highest, driving the Recovery).
Also, a Resetlogs is required in both cases to ensure that the older Redo Logs (e.g. they might still be on disk) are not used -- the Resetlogs creates a new Incarnation of the database.

Qn 2:
There is no difference between an ALTER DATABASE OPEN; and an ALTER DATABASE OPEN NORESETLOGS;.
The NORESETLOGS is the default action in an OPEN if you do not specify RESETLOGS.

(Obviously, Oracle check to see if you have used a Backup Controlfile and/or done an Incomplete Recovery, in which case it does not allow you to OPEN without the RESETLOGS).

Hemant K Chitale

Cantalopian said...

Ahhhh this is great!
'Recovery Until Cancel' is the magic incantation that allowed me to recover my database.
Feeling much better now.
Thank you 100 times!

-Steve

Anonymous said...

Hi Hemant,

I was reading all your blogs, excellent collection of testcases and useful information !

Could you please tell me, when I dont use a backup controlfile (say for e.g: I am recreating the control using CREATE CONTROLFILE .. NORESETLOGS) in which case I do not have to use OPEN RESETLOGS if my last shutdown was a proper shutdown. We know that Oracle is creating a new controlfile hence it does not have the logsequence number, and we are not using RESETLOGS as well to update this in the controlfile. Then how does this work when the controlfile is new and not using RESETLOGS?

Following shows an example of this:

http://www.ardentperf.com/2007/05/15/controlfile-recovery-without-resetlogs/

Thanks,
Rijesh

Anonymous said...

Hi Hemant,

Thanks for sharing your knowledge.


I am unable to rollforward the database using the backup of the binary controlfile whereas I can rollforward if I create a controlfile from the trace.
What is the reason for this ?

Here is the example:



SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

1-)Take a binary controlfile backup

SQL> alter database backup controlfile to '/oracle/admin/orcl/control_orcl.ctl';

Database altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDO_TBS
USERS
TEMP

2-) Put database in hotbackup mode

SQL>alter tablespace users begin backup;
Tablespace altered.
SQL>alter tablespace system begin backup;
Tablespace altered.
SQL>alter tablespace undo_tbs begin backup;
Tablespace altered.



SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/admin/orcl/datafiles/system.dbf
/oracle/admin/orcl/datafiles/undo.dbf
/oracle/admin/orcl/datafiles/users.dbf


3-) Backup everything

cp redo01.dbf /oracle/admin/orcl/backup
cp redo02.dbf /oracle/admin/orcl/backup
cp system.dbf /oracle/admin/orcl/backup
cp temp.dbf /oracle/admin/orcl/backup
cp undo.dbf /oracle/admin/orcl/backup
cp users.dbf /oracle/admin/orcl/backup


4-) Stop backup mode

alter tablespace users end backup;
SQL>Tablespace altered
alter tablespace system end backup;
SQL>Tablespace altered
alter tablespace undo_tbs end backup;
SQL>Tablespace altered


SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


5-) Specify recovery time
SQL> !date
Fri Nov 4 12:45:35 NZDT 2011

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

6-) Restore controlfile from the binary backup which was taken in step 1

cp control_orcl.ctl control01.ctl
cp control_orcl.ctl control02.ctl

7-) Restore everything from the backup taken in step 3

cp system.dbf /oracle/admin/orcl/datafiles
cp temp.dbf /oracle/admin/orcl/datafiles
cp undo.dbf /oracle/admin/orcl/datafiles
cp users.dbf /oracle/admin/orcl/datafiles
cp redo01.dbf /oracle/admin/orcl/datafiles
cp redo02.dbf /oracle/admin/orcl/datafiles


SQL> startup mount;
ORACLE instance started.

Total System Global Area 143618592 bytes
Fixed Size 737824 bytes
Variable Size 79691776 bytes
Database Buffers 62914560 bytes
Redo Buffers 274432 bytes
Database mounted.


6-) Recover database to date mentioned in step 5

RECOVER automatic DATABASE USING BACKUP CONTROLFILE until TIME '2011-11-04:12:45:35';
ORA-00279: change 67323 generated at needed for thread 1


Specify log: {=suggested | filename | AUTO | CANCEL}



Note that, same recover command works if I create a controlfile from the trace.

Hemant K Chitale said...

The "roll-forward" using the binary controlfile is happening. You have to supply the log file name.
Why is it that your output doesn't show the prompted file name ?

Anonymous said...

Hi

Does the dbid of the database change if I issue open resetlogs ?

Hemant K Chitale said...

Anonymous,
No, a simple RESETLOGS does not change the DBID.
Since 9i, Oracle has provided the "DBNEWID" utility ($ORACLE_HOME/bin/nid) that allows a change of the DBID.
Note : You cannot select a value for the DBID (as you can for DB_NAME), the DBID is automatically generated.

Anonymous said...

Thanks,
Suppose the database is already in catalog.

If I do resetlogs, why do I have to register database to recovery catalog again ?

Anonymous said...
This comment has been removed by a blog administrator.
Hemant K Chitale said...

Anonymous,
Which document / instructions do you read that state that you have to re-register the database after a RESETLOGS ?
The Incarnation changes (which you'd see in V$DATABASE and V$DATABASE_INCARNATION) but why do you think you need to re-register the database. I don't think so.
Hemant

Anonymous said...

Thanks a ton!

This is real good information...

Unknown said...

Hi Hemanth,
I tried to ran the duplicate script after transferring new rman backup to standby. Even after transferring the backup, duplicate command and was unable to open the database. What might be the possible cause ?

Hemant K Chitale said...

Raghuram,
Without seeing your commands and the output, results and messages / error messages, I cannot make a guess as to the "possible cause".

I suggest you open a new thread under the Oracle Database - High Availability - Recover Manager "space" at https://forums.oracle.com/community/developer/english/oracle_database/high_availability/recovery_manager_(rman)_2

Hemant K Chitale

venkat said...

Hi Hemant
Its Wonderfull topic Now I have stuck with DB I canot open it It is corrupted Could u guide me how to recover it?
ven

venkat said...

Hi Hemant
I got the following errors when I open the DB

ORA-00283 & ORA-16433

Alert Log:
Thu Jan 16 15:13:47 2014
ALTER DATABASE RECOVER automatic DATABASE using backup controlfile until time '2014-01-16'
Media Recovery Start
started logmerger process
Thu Jan 16 15:13:47 2014
Media Recovery failed with error 16433
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER automatic DATABASE using backup controlfile until time '2014-01-16' ...
Thu Jan 16 15:18:43 2014
db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

Thanks
Venkat

Hemant K Chitale said...

ORA-283 is simply that there was an *error*. What the error was in this case may well have been displayed on the terminal session where you issued the RECOVER command. That would have been one or more messages that accompanied the ORA-283.

Whenever doing a RESTORE or RECOVER you must watch *both* the terminal session where you issue the command and the alert log file.

Since this is a RECOVER DATABASE attempt,you should be logging an SR with Oracle Support. Do NOT expect a blogger to respond immediately. Do NOT expect me to have access to your enivornment, your setup, your backup scripts and restore and recover scripts, commands and messages -- Oracle Support can access these with your help. Oracle Support can then advice you.

Hemant K Chitale

Hemant K Chitale said...

Venkat,
The ORA-16433 might well mean that Oracle couldn't *write* to the database files. Maybe it doesn't have write permission.


Hemant K Chitale

Fahim said...

Hi Hemant,
I want some clarification when only all control files are lost dafiles/redofiles are intact and a new datafile is added after backup controlfile(no autobackup).
does recover database commmad will register that file info into controlfile.

Hemant K Chitale said...

Fahim,
You'd have an inconsistency between the controlfile and the data dictionary.

You could try the scenario in a test environment.

Hemant

Leon said...

Hi Mr Hemant,

Time1 --> full database backup where database is in archivelog mode. (Eg: 8am full backup)
--> shutdown

Time2 --> startup mount;
--> alter database to noarchivelog mode
--> alter database open;
--> Perform data changing and commit.
--> shutdown
During time 2 there are no arcvhivelog running.


Time3 --> startup mount;
--> alter database back to archivelog mode
--> alter database open;
--> perform data changing and commit.
--> alter system switch logfile (to create a latest archivelog)

If my database crash.
Can I do recovery from Time1 untill the last archivelog at time3.

Thanks

Hemant K Chitale said...

Leon,
If your data changes at Time 2 have cycled through and overwritten all the online redo logs, you won't be able to recover to the Time 3 archivelog. So, keep your transactions at Time 2 and see that Oracle hadn't overwritten the online redo logs as it cycles through them.

Vimal Rathinasamy said...

Hi Hemant,

Thanks a lot for the article. With your permission, I have one suggestion which one of the comments (Rajesh) already pointed out. It is also supported by Oracle documentation: When you have a newly created control file for recovery and perform a Complete recovery owing to the availability of Online Redo Logs - You need not use Resetlogs option to open the database. This is because this is a brand new control file and not an old control file from backup (which will have SCN/Sequence nos out of sync). Nevertheless, your articles are awesome. Thanks again.