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.
14 comments:
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!
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 ?
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"
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
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
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
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.
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 ?
Hi
Does the dbid of the database change if I issue open resetlogs ?
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.
Thanks,
Suppose the database is already in catalog.
If I do resetlogs, why do I have to register database to recovery catalog again ?
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
Thanks a ton!
This is real good information...
Post a Comment