RMAN> set dbid <...>;
RMAN> connect target;
RMAN> reset database to incarnation <...>;
RMAN> list incarnation;
Now check the available backups that can be used with the above incarnation.
RMAN> list backup recoverable;
SYMPTOMS
You would like to restore a database to a point-in-time, but the backup being used is from before the last resetlogs.
The problem is that when a database is opened with resetlogs, the incarnation of the database is also reset and RMAN keeps a record of the incarnation of the
database in the Recovery Catalog.
CHANGES
CAUSE
Backups were taken from a prior incarnation.
SOLUTION
Before we can restore a backup from before the last resetlogs, we need to reset the database to the old incarnation number.
Here are the steps to reseting the correct incarnation and restoring the database to a point in time before the last resetlogs.
Note: Target database should be nomounted.
1. Start RMAN and connect only to the catalog database.
% rman catalog rman/(password)@(Connect string of catalog)
2. Execute the command that lists all the incarnation values for the databases in the recovery catalog.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 2 <dbname1> 579966833 NO 1 03-MAY-99
224 225 <dbname1> 579966833 YES 92402 05-MAY-99
We can see that one of the databases had been open with resetlogs and a new incarnation was started (DB_ID 579966833). We should look at the column
DATABASE INC KEY. We can see the original incarnation for this database was 2 and was reset to what is still the current incarnation of 225. 2 is the
database incarnation that has to be set in order to restore a backup from before the last resetlogs.
3. We first need to let RMAN know what database ID we will be dealing with, so we execute the following command:
RMAN> Set dbid 579966833;
4. Now we need to connect to the target instance so we can verify the database ID.
RMAN> connect target
RMAN-06005: connected to target database:<dbname1> (DBID=579966833)
5. Since we are connected to both the recovery catalog and target database, we need to tell RMAN to reset the database incarnation to 2.
We do this with the following command:
RMAN> reset database to incarnation 2;We can now see that the current incarnation has been set back to 2.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 2 <dbname1> 579966833 YES 1 03-MAY-99
224 225 <dbname1> 579966833 NO 92402 05-MAY-99
6. After resetting the database, issue restore and recover commands to restore and recover the database files from the prior incarnation, then open the database
with the RESETLOGS option. You need start from restoring a previous incarnation control file when the database is in nomount state. When the database is reset to
the previous incarnation, the catalog will automatically picks up a right control file. After restoring the control file, the database must be mounted for datafiles restore.
run {
set until time 'Jul 8 1999 07:55:00'; # set time to just before data was lost
allocate channel dev1 type disk;
shutdown abort;
startup nomount;
restore controlfile;
alter database mount; # mount database after restoring control file
restore database;
recover database;
alter database open resetlogs; # this command automatically resets the database
# so that this incarnation is the new incarnation
}
Solution Explanation:
=====================
Once we set the database to the prior incarnation, RMAN will allow a restore and recover of the database using a backup from before the last resetlogs.
Solution References:
====================
Oracle8i Recovery Manager User’s Guide, Release 1 (8.1.6) - Part No. A76990-01
Oracle9i Recovery Manager User’s Guide, Release 1 (9.0.1) - Part No. A9013
For 10g, 11g related issues with Incarnation and FRA ( Fast/Flash Recovery Area )
Please see
Note 965122.1: RMAN RESTORE FAILS WITH RMAN-06023 BUT THERE ARE BACKUPS AVAILABLE
--------------------------------
How to Recover Through a Resetlogs Command Using RMAN (Doc ID 237232.1)
Recovery through a RESETLOGS occurs in two sequential phases:
1.Recovery Before RESETLOGS
2.Recovery After RESETLOGS
Recovery Before RESETLOGS:
In the first phase, restore the database and recover it to the state it was in
immediately prior to the RESETLOGS command.
1.Obtain the RESETLOGS SCN using one of these methods:
- Running a LIST INCARNATION command and subtracting 1 from the value in the
Reset SCN column. Also, note down the database incarnation keys of all
incarnations as listed in the Inc Key column.
- Examine the alert_SID.log from the time of the RESETLOGS, and search for the
word RESETLOGS. Look for a line such as this one: RESETLOGS after incomplete
recovery UNTIL CHANGE 1234. Use this value as-is.
- By using a control file from after the RESETLOGS (either the current control
file or a backup made after opening RESETLOGS), run the query 'SELECT
(RESETLOGS_CHANGE#)-1 FROM V$DATABASE;'
2.Shutdown the database with the ABORT option.
3.If the current control files are not lost, then copy them to a different
location. You will need them again in a later step. Do not make the copy with
RMAN, but use operating system commands after the database has been shut down.
4.Start RMAN and connect to the target database and recovery catalog.
$rman target user/pwd@target catalog user/pwd@catalog
5.Startup NOMOUNT.
RMAN> startup nomount;
6.Run the LIST INCARNATION command to obtain the incarnation key of the prior
incarnation (if do not already have it).
7.Run the RESET DATABASE TO INCARNATION inc_key command where inc_key is the key
of the prior incarnation.
8.Execute a RUN command with the following subcommands:
a.Issue a SET UNTIL SCN resetlogs_scn command, where resetlogs_scn is the SCN
obtained from the first step.
b.After allocating all necessary channels (if you do not have automatic
channels configured), restore a backup of the control file created before the
RESETLOGS and then mount it.
c.Restore datafile backups of files needing recovery from backups created
before the RESETLOGS. Only restore datafiles that require recovery
d.Take all datafiles that do not need to be recovered offline by running the
SQL statement ALTER DATABASE DATAFILE ... OFFLINE.
e.Start SQL*Plus and query V$DATAFILE to make sure that all datafiles are
pointing to valid locations and only files that need to be recovered are online.
f.Perform an incomplete media recovery of the database using RECOVER DATABASE.
9.Shutdown the database with the IMMEDIATE option
Recovery After RESETLOGS:
After the previous phase is complete, follow this procedure.
1.Restore either of the following:
- The current control files from the saved location in step 3 of "
Recovery
Before RESETLOGS"
- A backup control file copy from after the RESETLOGS if all copies of the
current control file are lost (note that using a backup control file here
requires another OPEN RESETLOGS after recovery completes)
2. Startup NOMOUNT.
RMAN> startup nomount
3.Reset the database to the most recent incarnation. Obtain the incarnation key
from the LIST INCARNATION output.
RMAN> reset database incarnation to <nr>;
4.Mount the database.
RMAN> alter database mount
5.Recover the database to the desired point with the RECOVER command.
RMAN> recover database;
6.Open the database after media recovery is complete, specifying RESETLOGS only
if you used a backup control file or performed incomplete recovery.
RMAN> alter database open [resetlogs];
7.Back up the database to avoid performing this procedure in the future.
RMAN> backup database;
How to duplicate a database to a previous Incarnation (Doc ID 293717.1)
For example:
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 801701888 bytes
Fixed Size 2257520 bytes
Variable Size 494931344 bytes
Database Buffers 301989888 bytes
Redo Buffers 2523136 bytes
RMAN> restore controlfile from '/<path of controlfile backuppiece>/cf_scs12lrh_1_1';
--------------------------------
Database has incarnation 1, 2, 3
In incarnation 2:
I created a db file, created a user, populated data.
The control file associated with incarnation 2 knows about it.
I took backup called T1.
I PITRed database with open resetlogs command.
Incarnation 3 is created.
In incarnation 3 (current):
I want to recover the database PRIOR TO the last open resetlogs time.
So in this situation:
Which control file knows about the structural changes and old backup T1 -- Current control file or incarnation 2's control file?
If I reset database to incarnation 2, restore control file, perform restore/recovery to prior-to the last resetlogs time, wouldn't I get all the structural changes?
If I reset database to incarnation 2, use the current control file, perform restore/recovery to prior-to the last resetlogs time -- it will not work, wouldn't it?
Many thanks,
Joy
Hello Joy,
1. I would split this into 2 parts - The structural changes would be known by both the current and the incarnation 2's control file (since the structural changes (add DB file) occurred during the time frame of incarnation 2. On the old backup T1, it depends on your backup retention policy and as well as if an exclusive RMAN catalog DB is being used. If your retention policy (and redundancy) has crossed since the time of taking the backup T1 to current time frame (in incarnation 3) and if there are any deletion of obsolete backups (through RMAN 'delete obsolete') those have to be factored in as well.
2. Yes you would get all of the DB file related structural changes along with data changes cincluded in the T1 backup
3. Yes (with above caveat in 1 above)
Hope this helps.
Cheers
Suresh
Hi Joy,
Below doc would be useful.
How to Recover Through a Resetlogs Command Using RMAN (Doc ID 237232.1)
How to duplicate a database to a previous Incarnation (Doc ID 293717.1)
Thanks,
Renu
Please mark your question as answered or helpful if you find the above solution useful / correct, so that other community members can find the information easily. See Did Your Question Get Answered in My Oracle Support Community Note 1180503.1 document on how to do this.
Flag
Quote
Off Topic
Like
MeowMeow
MeowMeow Posts: 126 Bronze Badge
Jan 7, 2019 9:38PM edited Jan 7, 2019 9:38PM
Hi Suresh,
Thank you for your help.
Last Friday I did the testing below (PITR to previous incarnation WITHOUT restoring the control file associated with the previous incarnation), the media recovery failed with ORA-19912: cannot recover to target incarnation 14.
Here is the result. Confusing.
Thank you,
Joy
shutdown abort;
startup mount;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
6328185 6328193 CHYEND 2489374880 PARENT 1 2018-08-08:10:55:28
6328185 6328195 CHYEND 2489374880 PARENT 5881767458273 2018-12-19:11:40:06
6328185 6328197 CHYEND 2489374880 PARENT 5881767541420 2018-12-19:13:36:22
6328185 6328186 CHYEND 2489374880 PARENT 5881767609152 2018-12-19:15:59:50
6328185 6401209 CHYEND 2489374880 ORPHAN 5881767864098 2018-12-26:11:28:50
6328185 6404765 CHYEND 2489374880 PARENT 5881767864098 2018-12-26:12:06:55
6328185 6364368 CHYEND 2489374880 ORPHAN 5881767864101 2018-12-21:10:13:28
6328185 6412335 CHYEND 2489374880 ORPHAN 5881782279592 2018-12-26:14:23:25
6328185 6415574 CHYEND 2489374880 ORPHAN 5881782279592 2018-12-26:15:04:42
6328185 6418808 CHYEND 2489374880 ORPHAN 5881782279592 2018-12-26:15:39:29
6328185 6411243 CHYEND 2489374880 ORPHAN 5881782279802 2018-12-26:13:45:42
6328185 6452038 CHYEND 2489374880 ORPHAN 5881782279592 2018-12-31:08:50:12
6328185 6467315 CHYEND 2489374880 ORPHAN 5881782279592 2019-01-02:08:27:04
6328185 6472323 CHYEND 2489374880 ORPHAN 5881782279592 2019-01-02:12:25:29
6328185 6480463 CHYEND 2489374880 ORPHAN 5881796305280 2019-01-02:15:31:54
***6328185 6488434 CHYEND 2489374880 PARENT 5881782279592 2019-01-03:11:37:55
6328185 6497318 CHYEND 2489374880 ORPHAN 5881796900811 2019-01-04:10:48:47
6328185 6499524 CHYEND 2489374880 ORPHAN 5881796903656 2019-01-04:14:27:38
6328185 6520079 CHYEND 2489374880 CURRENT 5881796899222 2019-01-07:13:01:20
reset database to incarnation 6488434;
restore database until time "to_date('2019-01-04:10:19:16', 'yyyy-mm-dd:hh24:mi:ss')" preview summary;
(restore completed)
recover database until time "to_date('2019-01-04:10:19:16', 'yyyy-mm-dd:hh24:mi:ss')";
(Failed)
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/04/2019 11:10:04
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until time '2019/01/04 10:19:16' using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 14
// *Cause: The control file is not in the recovery path of the target
// incarnation, and does not contain enough information as to how
// to recover to the target incarnation.
// *Action: Restore the latest control file from the target incarnation and
// retry.
Recovery was done with current control file.
Control file checkpoint SCN (shown in v$database) <> restored datafile header SCN (show in v$datafile)
control file checkpoint SCN
col checkpoint_change# for 9999999999999999999999
select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
-----------------------
5881796911965
Datafile header SCN
set lines 200
select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH where HXFIL=1;
FILE_NUM FILE_NAME SCN STATUS SEQUENCE
---------- ---------------------------------------- -------------------- ---------- ----------
1 /u02/chyend/system01.dbf 5881796841536 8192 104
Flag
Quote
Off Topic
Like
SureshMuddaveerappa
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,829 Tanzanite
Jan 7, 2019 10:21PM edited Jan 7, 2019 10:21PM
Hi Joy,
This is due to the caveat I was mentioning earlier. Typically I always prefer to use the control file from the associated backup. In this case the control file of backup T1.
RMAN> connect target /
-- Note now rman should report "connected to target database: <sid> (not mounted)"
RMAN> restore controlfile from '...';
-- here you would be using the desired control file
Later proceed as earlier.
Cheers
Suresh
Flag
Quote
Off Topic
Like
MeowMeow
MeowMeow Posts: 126 Bronze Badge
Jan 7, 2019 10:31PM edited Jan 7, 2019 10:31PM
Thank you Suresh.
So, you think, when doing PITR to a point or SCN that is prior to the open resetlogs time, OR to a point or SCN that is after the open resetlogs time, using backups taken within the prior incarnation -->
it's always good to restore the control file from backup associated with that incarnation?
Flag
Quote
Off Topic
Like
SureshMuddaveerappa
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,829 Tanzanite
Jan 7, 2019 10:37PM edited Jan 7, 2019 10:37PM
Hello Joy,
That's correct.
In general during any backup I would also include the control file. So at the time of restore make use of the same control file that was backed up along with rest of the DB files.
Please keep posted how it goes.
