Flashback issue while restoring the DB
Flashback issue while restoring the DB with restore point
Issue:
DB restoration failed to Guarantied Restore point which was created for Application upgrade activity in Development env.
Root Cause:
Flash back was disabled for one tablespace level before creating the restore point and no errors received during restore point creation.
There is a limitation of ‘FLASHBACK DATABASE’ if the flashback is disabled for tablespace.
Trouble shooting :
Verified all the tablespaces flashback status and found one tablespace flashback status was set to NO.
Any errors while Restore point Creation:
While creating the restore point , there were no errors found.
Work around: Complete step by step procedure
1)Put the APPS datafile offline
SQL> alter database datafile 6 offline;
2 )flashback database to restore point
SQL> flashback database to restorepoint BEFORE_DEPLYOMENT;
3) Put the APPSdatafile datafile online
SQL> alter database datafile 6 online;
4) get the change #
col name form a15
select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;
5) Restore datafile (for which flashback status=NO) & Recover database until the change# in the above o/p.
rman target /
catalog=RMAN/rman@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))(CONNECT_DATA=(SID=RMAN)(SERVER=DEDICATED)))
run
{
allocate channel ch1 type ‘sbt_tape’ parms=’ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ch2 type ‘sbt_tape’ parms=’ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore datafile 6 until change#;
recover database until change change#;
release channel ch1;
release channel ch2;
}
6) alter database open resetlogs;
7) Verify data from application.
How to avoid this kind of issue?
Verify if FLASHBACK setting for any tablespace level is set to NO by using below command.
| select * from v$tablespace where FLASHBACK_ON=’NO’; |
output should be “no rows selected”