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:
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...
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 ?
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
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
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
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
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
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.
Fahim,
You'd have an inconsistency between the controlfile and the data dictionary.
You could try the scenario in a test environment.
Hemant
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
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.
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.
Post a Comment