oracle中flashback_on_standby详细介绍

作者:袖梨 2022-06-29

配置flashbash database参数

 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库建表测试表并同步到standby

SQL> conn enmotech/enmotech123
Connected.
 
USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  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            OPID  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端查看表

SQL> conn enmotech/enmotech123
Connected.
 
USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  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
主库端进行删除数据

SQL>
SQL>
SQL> DELETE FROM T_DB_INFO_BAK;
 
69 ROWS deleted.
 
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            OPID  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查看数据

SQL>  SELECT COUNT(*) FROM T_DB_INFO_BAK;
 
  COUNT(*)
----------
         0
查看闪回日志

[oracle@oel7 dg]$ ls -ltr fra/ORCLDG/flashback/
-rw-r----- 1 oracle oinstall 52436992 Sep 15 17:28 fra/ORCLDG/flashback/o1_mf_bzhs6frd_.flb
-rw-r----- 1 oracle oinstall 52436992 Sep 15 17:33 fra/ORCLDG/flashback/o1_mf_bzhs6bnk_.flb
standby端停止应用并闪回

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            OPID  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
数据正常找回来.
查看alert日志

 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 is NOT 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 space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
应用归档追起日志

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 be 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

相关文章

精彩推荐