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: '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.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 '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29237.826360609' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29345.826360597' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29341.826360579' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29338.826360575' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29337.826360571' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29238.826360611' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29239.826360615' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29240.826360615' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.12744.826360617' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.34180.826360617' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.33245.826360617' offline;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.27472.826360617' offline;
3. Flashback to the guaranteed restore point (GRP).
SQL> FLASHBACK DATABASE TO RESTORE POINT '<RestorePointName>';
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 +<AsmDiskGroup>/<db_unique_name>/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 '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29345.826360597' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29341.826360579' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29338.826360575' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29337.826360571' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29238.826360611' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29239.826360615' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.29240.826360615' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.12744.826360617' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.34180.826360617' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.33245.826360617' online;
ALTER DATABASE DATAFILE '+<AsmDiskGroup>/<db_unique_name>/datafile/<FileName>.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 <db_name> 2189369331 PARENT 73600365279 11-12-28 01:45
2 2 <db_name> 2189369331 PARENT 104113554484 13-09-17 07:41 <<< We need to be in this incarnation to recover to 9-27-2013. Use the INC KEY.
3 3 <db_name> 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
-------------------- ------------------- -------------------------------------- --- ----------------
<RestorePointName> 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 : +<AsmDiskGroup>/<db_unique_name>/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 : +<AsmDiskGroup>/<db_unique_name>/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 '+<AsmDiskGroup>/<db_unique_name>/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 : +<AsmDiskGroup>/<db_unique_name>/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 '+<AsmDiskGroup>/<db_unique_name>/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:文章来源：https://www.toymoban.com/news/detail-686591.html
SQL> alter tablespace <tablespace_name> flashback on;文章来源地址https://www.toymoban.com/news/detail-686591.html
到了这里，关于incarnation flashback database 时可以 offline 不符合的 datafile的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
