Oracle Flashback_on_standby in detail

Configure Flashbash Database Parameters

ALTER system SET db_recovery_file_dest= '/dg/fra ';
ALTER system SET db_recovery_file_dest_size=10g;
ALTER system SET db_flashback_retention_target=1440;
ALTER DATABASE flashback on;

Primary library to build table test table and sync to standby

Sql> Conn enmotech/enmotech123
Connected.

USERNAME inst_name host_name SID serial# VERSION started SPID OPI D cpid saddr paddr
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
Enmotech ORCL oel7 1 7 11.2.0.4.0 20150915 2943 19 2608 00000000972e96c0 0000000097b9e610


Sql> SELECT * from Tab;

Tname Tabtype Clusterid
------------------------------ ------- ----------
App_name TABLE
T_application_info TABLE
T_application_info_old TABLE
T_db_info TABLE
t_db_info_20150912 TABLE

8 ROWS selected.

Sql> CREATE TABLE T_db_info_bak as SELECT * from T_db_info;

TABLE created.


Sql> SELECT COUNT (*) from T_db_info_bak;

COUNT (*)
----------
69


Sql> Conn/as sysdba;
Connected.

USERNAME inst_name host_name SID serial# VERSION started SPID OPI D cpid saddr paddr
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS ORCL oel7 1 9 11.2.0.4.0 20150915 2947 19 2608 00000000972e96c0 0000000097b9e610


Sql> ALTER system switch logfile;

System altered.

Sql>/

System altered.

Sql>

Sql> SELECT * from Tab;

Tname Tabtype Clusterid
------------------------------ ------- ----------
App_name TABLE
Bin$h4fitjbqc4zguws4qmbv3q==$0 TABLE
Bin$h4fitjbrc4zguws4qmbv3q==$0 TABLE
Bin$h4fitjbsc4zguws4qmbv3q==$0 TABLE
T_application_info TABLE
T_application_info_old TABLE
T_db_info TABLE
t_db_info_20150912 TABLE
T_db_info_bak TABLE

9 ROWS selected.



Sql> SELECT COUNT (*) from T_db_info_bak;

COUNT (*)
----------
69
Standby-side view table

Sql> Conn enmotech/enmotech123
Connected.

USERNAME inst_name host_name SID serial# VERSION started SPID OPI D cpid saddr paddr
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
Enmotech ORCL oel7 1 11 11.2.0.4.0 20150915 2952 19 2608 00000000972e96c0 0000000097b9e610


Sql> SELECT COUNT (*) from T_db_info_bak;

COUNT (*)
----------
69
Delete data on the main library side

Sql>
Sql>
Sql> DELETE from T_db_info_bak;

Deleted ROWS.

Sql> commit;

Commit complete.

Sql> SELECT COUNT (*) from T_db_info_bak;

COUNT (*)
----------
0


Sql>
Sql> Conn/as SYSDBA
Connected.

USERNAME inst_name host_name SID serial# VERSION started SPID OPI D cpid saddr paddr
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS ORCL oel7 1 13 11.2.0.4.0 20150915 2954 19 2608 00000000972e96c0 0000000097b9e610



Sql> ALTER system switch logfile;

System altered.

Sql>/

System altered.
Standby View Data

Sql> SELECT COUNT (*) from T_db_info_bak;

COUNT (*)
----------
0
View Flashback Log

[Oracle@oel7 dg]$ ls-ltr fra/orcldg/flashback/
-RW-R-----1 Oracle oinstall 52436992 SEP 17:28 fra/orcldg/flashback/o1_mf_bzhs6frd_.flb
-RW-R-----1 Oracle oinstall 52436992 SEP 17:33 fra/orcldg/flashback/o1_mf_bzhs6bnk_.flb
Standby end of application and flash back

Sql> Recover managed standby DATABASE cancel;
Media recovery complete.
sql> shutdown immediate;
Ora-01031:insufficient Privileges
Sql> Conn/as SYSDBA
Connected.

USERNAME inst_name host_name SID serial# VERSION started SPID OPI D cpid saddr paddr
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS ORCLDG oel7 1 9 11.2.0.4.0 20150915 2962 19 2611 00000000972e96c0 0000000097b9e610


sql> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
sql> startup Mount;
ORACLE instance started.

Total System Global area 939495424 bytes
Fixed SIZE 2258840 bytes
Variable SIZE 348129384 bytes
DATABASE buffers 583008256 bytes
Redo buffers 6098944 bytes
DATABASE mounted.
Sql> Flashback DATABASE to TIMESTAMP to_date (' 20150915 17:32:00 ', ' YYYYMMDD HH24:MI:SS ');

Flashback complete.

sql> ALTER DATABASE OPEN;

DATABASE altered.

Sql> SELECT COUNT (*) from Enmotech.t_db_info_bak;

COUNT (*)
----------
69
Data back to normal.
View Alert Log

Flashback database to timestamp to_date (' 20150915 17:32:00 ', ' YYYYMMDD HH24:MI:SS ')
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Flashback Media Recovery LOG/DG/ARCHDG/1_344_863192055.DBF
Flashback Media Recovery Log/dg/fra/orcldg/archivelog/2015_09_15/o1_mf_1_345_bzhs8dp7_.arc
Flashback Media Recovery Log/dg/fra/orcldg/archivelog/2015_09_15/o1_mf_1_346_bzhs8dn0_.arc
Flashback Media Recovery Log/dg/fra/orcldg/archivelog/2015_09_15/o1_mf_1_347_bzhs8dpg_.arc
Flashback Media Recovery LOG/DG/ARCHDG/1_348_863192055.DBF
Flashback Media Recovery LOG/DG/ARCHDG/1_349_863192055.DBF
Flashback Media Recovery LOG/DG/ARCHDG/1_350_863192055.DBF
Flashback Media Recovery LOG/DG/ARCHDG/1_351_863192055.DBF
Incomplete Recovery applied until change 3129792 time 09/15/2015 17:32:01
Flashback Media Recovery Complete
Completed:flashback database to timestamp to_date (' 20150915 17:32:00 ', ' YYYYMMDD HH24:MI:SS ')
Tue Sep 15 17:36:43 2015
ALTER DATABASE Open
Audit_trail initialization parameter is changed to OS, as DB isn't compatible for database opened with read-only access
Tue Sep 15 17:36:43 2015
Arc2:becoming The active heartbeat ARCH
Tue Sep 15 17:36:43 2015
Smon:enabling Cache Recovery
Dictionary Check Beginning
Dictionary Check Complete
Database Characterset is ZHS16GBK
No Resource Manager Plan Active
Replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed:alter Database Open
Tue Sep 15 17:36:44 2015
Db_recovery_file_dest_size of 10240 MB is 0.49% used. This is a
User-specified limit on the amount of spaces that'll be used by this
Database for recovery-related files, and does not reflect the amount of
Spaces available in the underlying filesystem or ASM DiskGroup.
Application Archive chasing Log

sql> ALTER database recover managed standby database disconnect from session;

DATABASE altered.

Sql> SELECT COUNT (*) from Enmotech.t_db_info_bak;

COUNT (*)
----------
0
Tue Sep 15 17:38:43 2015
ALTER DATABASE recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLDG)
Tue Sep 15 17:38:44 2015
MRP0 started with pid=25, OS id=3027
Mrp0:background Managed Standby Recovery process started (ORCLDG)
Serial Media Recovery started
Managed Standby Recovery not using real time Apply
Waiting for all non-current orls to is archived ...
All non-current orls have been archived.
Media Recovery log/dg/archdg/1_351_863192055.dbf
Media Recovery log/dg/archdg/1_352_863192055.dbf
Media Recovery waiting for thread 1 sequence 353
Completed:alter database recover managed standby database disconnect from session