Flashback database and restore point

Operation sequence:
1. alter database recover managed standby database cancel;
2. create restore point standby_flashback_testing guarantee flashback database;
3. flashback database to timestamp — which is earlier than scn of restore point standby_flashback_testing
4. activate standby and open in read write mode
5. flashback database to restore point standby_flashback_testing;

Q: Will the flashback database to restore point succeed?
A: Yes.

SQL> flashback database to scn 783262;

Flashback complete.

SQL> alter database activate standby database;
Database altered.

SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

SQL> alter database set standby database to maximize performance;
Database altered.
SQL> alter database open;
Database altered.

select incarnation#, resetlogs_id, resetlogs_change#, prior_resetlogs_change#, prior_incarnation#
from v$database_incarnation
where status='CURRENT';
INCARNATION# RESETLOGS_ID RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ ------------ ----------------- ----------------------- ------------------
7 911399590 783264 1 1
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ECOLOGY 4104255908 PARENT 1 2016-05-04 16:25:08
2 2 ECOLOGY 4104255908 ORPHAN 322799 2016-05-05 11:00:44
3 3 ECOLOGY 4104255908 ORPHAN 334901 2016-05-05 14:01:59
4 4 ECOLOGY 4104255908 ORPHAN 337010 2016-05-05 14:33:53
5 5 ECOLOGY 4104255908 ORPHAN 347320 2016-05-05 17:13:31
6 6 ECOLOGY 4104255908 ORPHAN 783264 2016-05-09 14:19:58
7 7 ECOLOGY 4104255908 CURRENT 783264 2016-05-09 14:33:10
SQL> startup mount force;
SQL> flashback database to restore point standby_flashback_testing;
Flashback complete.

— information in alert.log

Mon May 09 14:21:03 2016
flashback database to restore point standby_flashback_testing
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Mon May 09 14:21:03 2016
Setting recovery target incarnation to 1
started logmerger process
Parallel Media Recovery started with 24 slaves
Flashback Media Recovery Log /oraarch/1_198_910974308.arc
Flashback Media Recovery Log /oraarch/1_199_910974308.arc
Mon May 09 14:21:03 2016
Incomplete Recovery applied until change 786020 time 05/09/2016 13:41:38
Flashback Media Recovery Complete
Completed: flashback database to restore point standby_flashback_testing

— on primary

SQL> select sequence#, first_change#, next_change# from v$archived_log where sequence# in (198,199);
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
198 697366 785987
198 697366 785987
199 785987 786091
199 785987 786091

— on standby

SQL> select controlfile_type from v$database;
CONTROL
-------
BACKUP

In addition,  ORA-38729 may occur during flashback database with timestamp clause and replacing the timestamp with scn would work, which I suspect may be a bug.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
545118 2016-05-07 10:35:47 1440 629145600 89284608
SQL>
SQL>
SQL> col name for a60
SQL> select * from v$flashback_database_logfile;
NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE
------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------- ------------------- ---------
/oraarch/ecologystd/flashback_recovery_area/ECOLOGYSTD/flash 1 1 1 209715200 334991 2016-05-05 14:02:01 RESERVED
back/o1_mf_clof9ln2_.flb
/oraarch/ecologystd/flashback_recovery_area/ECOLOGYSTD/flash 2 1 2 209715200 541435 2016-05-07 10:07:08 NORMAL
back/o1_mf_clof9os8_.flb
/oraarch/ecologystd/flashback_recovery_area/ECOLOGYSTD/flash 3 1 3 209715200 0 2016-05-08 19:56:25 NORMAL
back/o1_mf_cltmgdpn_.flb

SQL> flashback database to timestamp to_date('2016-05-09 13:00:00','yyyy-mm-dd hh24:mi:ss');

flashback database to timestamp to_date('2016-05-09 13:00:00','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

SQL> flashback database to timestamp sysdate-1/1440;
flashback database to timestamp sysdate-1/1440
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

SQL> flashback database to scn 783262;

Flashback complete.

One more thing that is worth noting is that for different flashback operations, flashback database will restore to the same point of time in history first and recover the database to the target timestamp. For example, the following flashback operation will apply archived log sequence 198,  198-199, 198-202 respectively.

flashback database to timestamp to_date('2016-05-09 13:00:00','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_date('2016-05-09 14:00:00','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_date('2016-05-09 15:00:00','yyyy-mm-dd hh24:mi:ss');

Reference:
https://www.pythian.com/blog/questions-you-always-wanted-to-ask-about-flashback-database/
QUESTIONS YOU ALWAYS WANTED TO ASK ABOUT FLASHBACK DATABASE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s