Unless it's an autobackup, RMAN can't automatically find the CF backup
There is always a backup of CF in backup piece containing DF 1 backup
Always configure controlfile autobackup. The default is OFF; make it ON.
Always create a backup controlfile everyday
Control file :
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile to 'C:\ctl.bk' from autobackup;
restore controlfile from 'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> restore controlfile from autobackup maxdays 20;
SQL> alter database backup controlfile to trace;
Backup script :
Backup Shell script sample
Backup size of each backupset
select HANDLE , sum(BYTES)/1024/1024/1024 from v$backup_piece p
where p.RMAN_STATUS_STAMP in ( select max(pi.RMAN_STATUS_STAMP) from v$backup_piece pi, v$backup_datafile di
where di.set_stamp = pi.set_stamp
and di.set_count = pi.set_count
and di.INCREMENTAL_LEVEL = 0 )
group by handle;
select substr(handle,1,4) rr ,HANDLE , sum(BYTES)/1024/1024/1024 sz from v$backup_piece p
where p.RMAN_STATUS_STAMP in ( select max(pi.RMAN_STATUS_STAMP) from v$backup_piece pi, v$backup_datafile di
where di.set_stamp = pi.set_stamp
and di.set_count = pi.set_count
and di.INCREMENTAL_LEVEL = 0 )
group by handle
order by substr(handle,1,4);
FRA :
db_recovery_file_dest +DG1
db_recovery_file_dest_size 3G
Archive LOCATION =USE_DB_RECOVERY_FILE_DEST
select * from v$flash_recovery_area_usage;
NOTE : You may have to REMOVE BACKUPS or Autobackup of ControlFile from FRA before recovery
RMAN catalogs the files in the FRA and if there is a controlfile autobackup
in the FRA it uses that file – and resets the incarnation of the database –
since the file in the FRA belonged to the previous database which ran on
this box after a RESETLOGS.
The way out is to RESET the incarnation of the database to the previous incarnation, temporary disable FRA or just delete unnecessary backup files from FRA.
Snapshots :
Snapshots are not valid backup solutions and not designed for Oracle data protection.
Snapshots have no awareness of the Oracle block structure .
A block corruption that goes undetected can potentially affect a series of snapshots.
Since snapshots reside on the same array as the source database, they are vulnerable to failures
Snapshots do not require an initial copy, as they are not stored as physical copies of blocks, but rather as pointers to the blocks that existed when the snapshot was created.
Snapshot has the side effect of nullifying all snapshots that were taken after it.
It's not recommended to use snapshots on production database storage.
A restored database snapshot must be recovered to a consistent point-in-time before it can be used.
Which scn to use for recovery
Limitations with Flashback :
Dropping a tablespace or shrinking a data file, cannot be reversed with Flashback Database.
You cannot back up flashback logs to locations outside the fast recovery area
You cannot reverse after you restore or re-create control file.
Block corruption is possible if you reverse to a time in past when NOLOGGING operation was running.
When there are guaranteed restore points in the database, the database compatibility parameter cannot be set to a higher database version
Guaranteed restore Point
select name,database_role,open_mode,flashback_on,log_mode from v$database;
show parameter recovery
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
Alter database flashback off; (Better to off it if you want to restore to only to the starting point. If you want to restore to any time after making changes using flasgback log , then define retention period and enable flashback logs using ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days and then ALTER DATABASE FLASHBACK ON; )
CREATE RESTORE POINT before_changes GUARANTEE FLASHBACK DATABASE;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
select * from V$FLASHBACK_DATABASE_LOG;
select * from V$FLASHBACK_DATABASE_STAT;
select * from V$FLASHBACK_DATABASE_LOGFILE;
flashback database to restore point before_changes ;
select * from V$FLASHBACK_DATABASE_LOG;
select * from V$FLASHBACK_DATABASE_STAT;
select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
alter database open resetlogs;
Database cloning using Sun ZFS.
http://www.oaktable.net/content/database-thin-cloning-allocate-write-zfs
1. From the production server.
http://www.oracle.com/technetwork/articles/systems-hardware-architecture/cloning-solution-353626.pdf
2. From Standby server.
http://www.oracle.com/technetwork/database/features/availability/maa-db-clone-szfssa-172997.pdf
1. FRA to Disk
Backup FRA to disk using RMAN
Find exact SCN Number for Oracle Restore
col fuzz# format 99999999999999999999999999
col chkpnt# format 99999999999999999999999999
select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
(select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date('FEB-06-2016','MON-DD-YYYY')
and file# <> 0
order by completion_time desc
);
FUZZ# CHKPNT#
--------------------------- ---------------------------
0 14185864593835
rman target /
spool log to list_backup.log;
list backup;
exit;
-- or use
select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,first_change#,
to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time, next_change#
from v$archived_log
where completion_time between to_date('FEB-06-2016','MON-DD-YYYY') and sysdate -10 ;
Recover on Prod :
SQL> SHUTDOWN ABORT;
RMAN> CONNECT TARGET /
RMAN> SET DBID <your_DBID>
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP; -- Or restore controlfile from '/bkup/controlf_OTP_0ult6mop_8222_20101117'
ALTER DATABASE MOUNT;
SET UNTIL TIME '2009-07-27:00:00:00'; -- only if C.F restored from backpiece
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; -- only if C.F restored from backpiece
}
Restore and Recover on Test for Cloning
PROD-RMAN> backup current controlfile; -- Transfer it to test environment
SQL> Startup nomount
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/bkup/controlf_OTP_0ult6mop_8222_20101117';
5> }
RMAN> alter database mount;
database mounted
RMAN> catalog start with '/bkup';
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
Restore and Recover on Test for Cloning using duplicate
run
{
set newname for datafile 1 to '/or0700275/a06a/ZTEST/system01.dbf';
set newname for logfile '/or0700021/a03a/WOST/redo01a.log' to '/or0700275/a03a/ZTEST/redo01a.log';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to TEST;
release channel t1;
}
--if fails restore archives
run
{
set archivelog destination to '/or0700275/a02a/ZTEST/arch';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
restore (archivelog low logseq 58041 high logseq 58052 thread 1 all);
release channel t1;
}
recover database using backup controlfile until time '2012-08-05:03:00:00';
alter database open resetlogs;
Oracle IT labs
Recovery scenarios
[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.
RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.
This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.
RMAN> run
2> {
3> allocate channel inc0 device type disk maxpiecesize 100G format '/u02/%d_inc0_%T_%U.bak;
4> backup incremental level 0 database plus archivelog;
5> backup archivelog all delete input;
6> backup current controlfile spfile;
7> }
=====================
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }
RMAN> alter database mount;
database mounted
RMAN> catalog start with '/oracle/backup';
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
=====================
SQL> SHUTDOWN ABORT;
RMAN> CONNECT TARGET /
RMAN> SET DBID <your_DBID>
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP; -- Or Restore from backpiece for incomplete recovery : restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117'
ALTER DATABASE MOUNT;
SET UNTIL TIME '2009-07-27:00:00:00'; -- only if C.F restored from backpiece
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; -- only if C.F restored from backpiece
}
=====================
run
{
set newname for datafile 1 to '/or0700275/a06a/ZTEST/system01.dbf';
set newname for logfile '/or0700021/a03a/WOST/redo01a.log' to '/or0700275/a03a/ZTEST/redo01a.log';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to TEST;
release channel t1;
}
run
{
set archivelog destination to '/or0700275/a02a/ZTEST/arch';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
restore (archivelog low logseq 58041 high logseq 58052 thread 1 all);
release channel t1;
}
recover database using backup controlfile until time '2012-08-05:03:00:00';
alter database open resetlogs;
=====================
http://goldparrot.wordpress.com/2011/05/16/how-to-find-exact-scn-number-for-oracle-restore/
If you have backups from more incarnations in your FRA then implicit crosscheck can always change current incarnation.
You could set correct incarnation, temporary disable FRA or just delete unnecessary backup files from FRA.
http://pro-oracle.blogspot.com/2014/03/oracle-rman-ora-19909-datafile-1.html
You may have to REMOVE BACKUPS or Autobackup ControlFile FROM FRA before recovery
RMAN catalogs the files in the FRA and if there is a controlfile autobackup
in the FRA it uses that file – and resets the incarnation of the database –
since the file in the FRA belonged to the previous database which ran on
this box after a RESETLOGS.
The way out is to RESET the incarnation of the database to the previous incarnation.
There is always a backup of CF in backup piece containing DF 1 backup
Always configure controlfile autobackup. The default is OFF; make it ON.
Always create a backup controlfile everyday
Control file :
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile to 'C:\ctl.bk' from autobackup;
restore controlfile from 'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> restore controlfile from autobackup maxdays 20;
SQL> alter database backup controlfile to trace;
Backup Shell script sample
Backup size of each backupset
select HANDLE , sum(BYTES)/1024/1024/1024 from v$backup_piece p
where p.RMAN_STATUS_STAMP in ( select max(pi.RMAN_STATUS_STAMP) from v$backup_piece pi, v$backup_datafile di
where di.set_stamp = pi.set_stamp
and di.set_count = pi.set_count
and di.INCREMENTAL_LEVEL = 0 )
group by handle;
select substr(handle,1,4) rr ,HANDLE , sum(BYTES)/1024/1024/1024 sz from v$backup_piece p
where p.RMAN_STATUS_STAMP in ( select max(pi.RMAN_STATUS_STAMP) from v$backup_piece pi, v$backup_datafile di
where di.set_stamp = pi.set_stamp
and di.set_count = pi.set_count
and di.INCREMENTAL_LEVEL = 0 )
group by handle
order by substr(handle,1,4);
FRA :
db_recovery_file_dest +DG1
db_recovery_file_dest_size 3G
Archive LOCATION =USE_DB_RECOVERY_FILE_DEST
select * from v$flash_recovery_area_usage;
NOTE : You may have to REMOVE BACKUPS or Autobackup of ControlFile from FRA before recovery
RMAN catalogs the files in the FRA and if there is a controlfile autobackup
in the FRA it uses that file – and resets the incarnation of the database –
since the file in the FRA belonged to the previous database which ran on
this box after a RESETLOGS.
The way out is to RESET the incarnation of the database to the previous incarnation, temporary disable FRA or just delete unnecessary backup files from FRA.
Snapshots :
Snapshots are not valid backup solutions and not designed for Oracle data protection.
Snapshots have no awareness of the Oracle block structure .
A block corruption that goes undetected can potentially affect a series of snapshots.
Since snapshots reside on the same array as the source database, they are vulnerable to failures
Snapshots do not require an initial copy, as they are not stored as physical copies of blocks, but rather as pointers to the blocks that existed when the snapshot was created.
Snapshot has the side effect of nullifying all snapshots that were taken after it.
It's not recommended to use snapshots on production database storage.
A restored database snapshot must be recovered to a consistent point-in-time before it can be used.
Which scn to use for recovery
Limitations with Flashback :
Dropping a tablespace or shrinking a data file, cannot be reversed with Flashback Database.
You cannot back up flashback logs to locations outside the fast recovery area
You cannot reverse after you restore or re-create control file.
Block corruption is possible if you reverse to a time in past when NOLOGGING operation was running.
When there are guaranteed restore points in the database, the database compatibility parameter cannot be set to a higher database version
Guaranteed restore Point
1* Check FRA , disable flashback and create Restore point
select name,database_role,open_mode,flashback_on,log_mode from v$database;
show parameter recovery
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
Alter database flashback off; (Better to off it if you want to restore to only to the starting point. If you want to restore to any time after making changes using flasgback log , then define retention period and enable flashback logs using ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days and then ALTER DATABASE FLASHBACK ON; )
CREATE RESTORE POINT before_changes GUARANTEE FLASHBACK DATABASE;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
2*Make changes
select * from V$FLASHBACK_DATABASE_LOG;
select * from V$FLASHBACK_DATABASE_STAT;
select * from V$FLASHBACK_DATABASE_LOGFILE;
3*Shutdown the db and mount it and provide all archive logs from the 'create restore' to shutdown
4*Revert back to restore point
flashback database to restore point before_changes ;
select * from V$FLASHBACK_DATABASE_LOG;
select * from V$FLASHBACK_DATABASE_STAT;
select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
5*Open database
alter database open resetlogs;
Database cloning using Sun ZFS.
http://www.oaktable.net/content/database-thin-cloning-allocate-write-zfs
1. From the production server.
http://www.oracle.com/technetwork/articles/systems-hardware-architecture/cloning-solution-353626.pdf
2. From Standby server.
http://www.oracle.com/technetwork/database/features/availability/maa-db-clone-szfssa-172997.pdf
1. FRA to Disk
Backup FRA to disk using RMAN
Find exact SCN Number for Oracle Restore
col fuzz# format 99999999999999999999999999
col chkpnt# format 99999999999999999999999999
select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt# from
(select file#, completion_time, checkpoint_change#, absolute_fuzzy_change# from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date('FEB-06-2016','MON-DD-YYYY')
and file# <> 0
order by completion_time desc
);
FUZZ# CHKPNT#
--------------------------- ---------------------------
0 14185864593835
rman target /
spool log to list_backup.log;
list backup;
exit;
-- or use
select sequence#, to_date(first_time,'DD-MON-YYYY HH24:MI:SS') first_time,first_change#,
to_date(next_time,'DD-MON-YYYY HH24:MI:SS') next_time, next_change#
from v$archived_log
where completion_time between to_date('FEB-06-2016','MON-DD-YYYY') and sysdate -10 ;
Recover on Prod :
SQL> SHUTDOWN ABORT;
RMAN> CONNECT TARGET /
RMAN> SET DBID <your_DBID>
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP; -- Or restore controlfile from '/bkup/controlf_OTP_0ult6mop_8222_20101117'
ALTER DATABASE MOUNT;
SET UNTIL TIME '2009-07-27:00:00:00'; -- only if C.F restored from backpiece
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; -- only if C.F restored from backpiece
}
Restore and Recover on Test for Cloning
PROD-RMAN> backup current controlfile; -- Transfer it to test environment
SQL> Startup nomount
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/bkup/controlf_OTP_0ult6mop_8222_20101117';
5> }
RMAN> alter database mount;
database mounted
RMAN> catalog start with '/bkup';
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
Restore and Recover on Test for Cloning using duplicate
run
{
set newname for datafile 1 to '/or0700275/a06a/ZTEST/system01.dbf';
set newname for logfile '/or0700021/a03a/WOST/redo01a.log' to '/or0700275/a03a/ZTEST/redo01a.log';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to TEST;
release channel t1;
}
--if fails restore archives
run
{
set archivelog destination to '/or0700275/a02a/ZTEST/arch';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
restore (archivelog low logseq 58041 high logseq 58052 thread 1 all);
release channel t1;
}
recover database using backup controlfile until time '2012-08-05:03:00:00';
alter database open resetlogs;
Oracle IT labs
Recovery scenarios
[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.
RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.
This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.
RMAN> run
2> {
3> allocate channel inc0 device type disk maxpiecesize 100G format '/u02/%d_inc0_%T_%U.bak;
4> backup incremental level 0 database plus archivelog;
5> backup archivelog all delete input;
6> backup current controlfile spfile;
7> }
=====================
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117';
5> }
RMAN> alter database mount;
database mounted
RMAN> catalog start with '/oracle/backup';
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> SET NEWNAME FOR DATAFILE 1 TO '/oracle/u02/oradata/system/otp/system01.dbf';
5> SET NEWNAME FOR DATAFILE 2 TO '/oracle/u02/oradata/data02/otp/users02.dbf';
6> SET NEWNAME FOR DATAFILE 3 TO '/oracle/u02/oradata/system/otp/sysaux01.dbf';
7> SET NEWNAME FOR DATAFILE 4 TO '/oracle/u02/oradata/data02/otp/users01.dbf';
8> SET NEWNAME FOR DATAFILE 5 TO '/oracle/u02/oradata/index/otp/tools01.dbf';
9> SET NEWNAME FOR DATAFILE 6 TO '/oracle/u02/oradata/data02/otp/users03.dbf';
10> SET NEWNAME FOR DATAFILE 7 TO '/oracle/u02/oradata/system/otp/undotbs01.dbf';
11> SET NEWNAME FOR DATAFILE 8 TO '/oracle/u02/oradata/system/otp/cluser_tbs01.dbf';
12>
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
=====================
SQL> SHUTDOWN ABORT;
RMAN> CONNECT TARGET /
RMAN> SET DBID <your_DBID>
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP; -- Or Restore from backpiece for incomplete recovery : restore controlfile from '/oracle/backup/controlf_OTP_0ult6mop_8222_20101117'
ALTER DATABASE MOUNT;
SET UNTIL TIME '2009-07-27:00:00:00'; -- only if C.F restored from backpiece
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; -- only if C.F restored from backpiece
}
=====================
run
{
set newname for datafile 1 to '/or0700275/a06a/ZTEST/system01.dbf';
set newname for logfile '/or0700021/a03a/WOST/redo01a.log' to '/or0700275/a03a/ZTEST/redo01a.log';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
set until time '2012-08-05:03:00:00';
duplicate target database to TEST;
release channel t1;
}
run
{
set archivelog destination to '/or0700275/a02a/ZTEST/arch';
allocate auxiliary channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/oracle/product/10.2.0/admin/backup/tdpo_WOST.opt)';
restore (archivelog low logseq 58041 high logseq 58052 thread 1 all);
release channel t1;
}
recover database using backup controlfile until time '2012-08-05:03:00:00';
alter database open resetlogs;
=====================
http://goldparrot.wordpress.com/2011/05/16/how-to-find-exact-scn-number-for-oracle-restore/
If you have backups from more incarnations in your FRA then implicit crosscheck can always change current incarnation.
You could set correct incarnation, temporary disable FRA or just delete unnecessary backup files from FRA.
http://pro-oracle.blogspot.com/2014/03/oracle-rman-ora-19909-datafile-1.html
You may have to REMOVE BACKUPS or Autobackup ControlFile FROM FRA before recovery
RMAN catalogs the files in the FRA and if there is a controlfile autobackup
in the FRA it uses that file – and resets the incarnation of the database –
since the file in the FRA belonged to the previous database which ran on
this box after a RESETLOGS.
The way out is to RESET the incarnation of the database to the previous incarnation.
