oracle中flashback_on_standby详细介绍

时间：2022-06-29 09:31:28 编辑：袖梨来源：一聚教程网

配置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>
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

推荐专题

最新下载

热门教程

oracle中flashback_on_standby详细介绍

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程