How do we flashback a database to a guaranteed restore point (GRP) that has 2 tablespaces with flashback off after a RESETLOGS was done?
Flashback fails with the errors below. It fails on the datafiles for the two tablespaces with flashback off.
RMAN-03002: failure of flashback command at 09/27/2013 15:14:24
ORA-38753: Cannot flashback data file 2229; no flashback log data.
ORA-01110: data file 2229: ‘+//datafile/.27472.826360617’
SOLUTION
We need to restore the datafiles for the two tablespaces from a backup, offline those same files, flashback to GRP, online the files again then recover to the GRP SCN.
Note: Because RESETLOGS was done we’ll also have to reset the incarnation. Otherwise that step can be skipped.
1. Restore the datafiles for those two tablespaces from a backup.
Note: The restored files are dated Sept 26th while all other datafiles are current (Sept 28th). The GRP is Sept 27th.
STATUS CHECKPOINT_CHANGE# FUZ CHECKPOINT_TIME COUNT(*)
——- ———————- — —————————– ———–
OFFLINE 104379205366 NO 26-SEP-2013 21:17:26 1
OFFLINE 104379205549 NO 26-SEP-2013 21:19:06 1
OFFLINE 104379205557 NO 26-SEP-2013 21:19:14 1
OFFLINE 104379205610 NO 26-SEP-2013 21:19:57 1
OFFLINE 104379206069 NO 26-SEP-2013 21:21:29 1
OFFLINE 104379206276 NO 26-SEP-2013 21:22:23 1
OFFLINE 104379206428 NO 26-SEP-2013 21:23:10 1
OFFLINE 104379206445 NO 26-SEP-2013 21:23:21 2
OFFLINE 104379206480 NO 26-SEP-2013 21:23:41 2
OFFLINE 104379206520 NO 26-SEP-2013 21:24:16 1
ONLINE 104398694790 YES 28-SEP-2013 08:42:03 2221
11 rows selected.
Database is currently in MOUNT.
2. After the restore make sure those restored files are OFFLINE before the flashback is done or it will fail with the same errors.
ALTER DATABASE DATAFILE ‘+//datafile/.29237.826360609’ offline;
ALTER DATABASE DATAFILE 服务器托管网‘+//datafile/.29345.826360597’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29341.826360579’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29338.826360575’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29337.826360571’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29238.826360611’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29239.826360615’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.29240.826360615’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.12744.826360617’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.34180.826360617’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.33245.826360617’ offline;
ALTER DATABASE DATAFILE ‘+//datafile/.27472.826360617’ offline;
3. Flashback to the guaranteed restore point (GRP).
SQL> FLASHBACK DATABASE TO RESTORE POINT ”;
Starting flashback at 13-09-28 10:20
…
starting media recovery archived log for thread 1 with sequence 843 is already on disk as file +//archivelog/2013_09_27/thread_1_seq_843.33236.827237895
media recovery complete, elapsed time: 00:00:15
Finished flashback at 13-09-28 10:27
4. Online all the datafiles for the two tablespaces.
ALTER DATABASE DATAFILE ‘+//datafile/.29345.826360597’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29341.826360579’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29338.826360575’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29337.826360571’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29238.826360611’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29239.826360615’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.29240.826360615’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.12744.826360617’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.3418服务器托管网0.826360617′ online;
ALTER DATABASE DATAFILE ‘+//datafile/.33245.826360617’ online;
ALTER DATABASE DATAFILE ‘+//datafile/.27472.826360617’ online;
5. Because the database was opened with resetlogs we have to reset the incarnation to before resetlogs was done.
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 2189369331 PARENT 73600365279 11-12-28 01:45
2 2 2189369331 PARENT 104113554484 13-09-17 07:41 3 3 2189369331 CURRENT 104390300886 13-09-28 07:04
RMAN> reset database to incarnation 2;
database reset to incarnation 2
6. Do RECOVER UNTIL CHANGE using the SCN of the GRP +1. The UNTIL recovers to right before the SCN specified.
SQL> select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point;
NAME SCN TIME DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
——————– ——————- ————————————– — —————-
104390296851 27-SEP-13 12.14.11.000000000 PM 2 YES 15728640000
1 row selected.
SQL> RECOVER DATABASE UNTIL CHANGE 104390296852;
ORA-00279: change 104379205366 generated at 09/26/2013 21:17:26 needed for thread 1 ORA-00289:
suggestion : +//archivelog/2013_09_28/thread_1_seq_788.39097.827318825 ORA-00280:
change 104379205366 for thread 1 is in sequence #788 Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 104379207088 generated at 09/26/2013 21:27:03 needed for thread 1 ORA-00289:
suggestion : +//archivelog/2013_09_28/thread_1_seq_789.23582.827318825 ORA-00280:
change 104379207088 for thread 1 is in sequence #789 ORA-00278: log file ‘+//archivelog/2013_09_28/thread_1_seq_788.39097.827318825’ no longer needed for this recovery
….
ORA-00279: change 104390280294 generated at 09/27/2013 11:55:45 needed for thread 1 ORA-00289:
suggestion : +//archivelog/2013_09_27/thread_1_seq_843.33236.827237895 ORA-00280:
change 104390280294 for thread 1 is in sequence #843 ORA-00278: log file ‘+//archivelog/2013_09_27/thread_1_seq_842.21570.827236545’ no longer needed for this recovery Log applied.
Media recovery complete.
SYS> alter database open resetlogs;
Database altered.
7. To enable the flashback on tablespaces:
SQL> alter tablespace flashback on;
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
用的是这个网站 服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net 机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net相关推荐: 怎样成为优秀的后端工程师本文翻译自国外论坛 medium，原文地址：…
