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