以下大部分都在standby执行，主库执行(两次)的会提示
【standby read write方式打开测试】
检查standby状态
sql> select name,database_role,open_mode,switchover_status from v$database;
name database_role open_mode switchover_status
——— —————- ——————– ——————–
testdb physical standby mounted not allowed
flashback_on要打开，以便闪回
sql> select flashback_on from v$database;
flashback_on
——————
no
sql> alter database flashback on;
database altered.
闪回恢复区设置，其中大小根据短期产生日志的大小估算
sql> set line 200
sql> show parameter db_recovery_file_dest
name type value
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 167280m
取消日志应用，刚提前cancel了
sql> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
error at line 1:
ora-16136: managed standby recovery not active
创建还原点
sql> create restore point restore_point_readonly guarantee flashback database;
restore point created.
主库日志切换，暂时日志同步
sql> alter system archive log current;
system altered.
sql> alter system set log_archive_dest_state_2=defer;
system altered.
激活standby为read write并open
sql> alter database activate standby database;
database altered.
sql> select name,open_mode,database_role,db_unique_name from v$database;
name open_mode database_role db_unique_name
——— ——————– —————- ——————————
testdb mounted primary testdbdg2
sql> alter database open;
database altered.
sql> set timing on;
sql> select name,open_mode,database_role,db_unique_name from v$database;
name open_mode database_role db_unique_name
——— ——————– —————- ——————————
testdb read write primary testdbdg2
elapsed: 00:00:00.00
write测试
sql> create table scott.t as select * from dba_objects;
table created.
elapsed: 00:00:02.33
sql> select count(*) from scott.t;
count(*)
———-
98160
elapsed: 00:00:00.01
sql> truncate table scott.t;
table truncated.
elapsed: 00:00:09.43
sql> select count(*) from scott.t;
count(*)
———-
0
elapsed: 00:00:00.00
sql> drop table scott.t;
table dropped.
elapsed: 00:00:01.44
测试完成了之后再回到某一个时间点，这个时间点要确定好
【闪回测试】
sql> shutdown immediate
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 4.2758e+10 bytes
fixed size 2262656 bytes
variable size 2.7380e+10 bytes
database buffers 1.5301e+10 bytes
redo buffers 74420224 bytes
database mounted.
可以select sysdate- 5/1440 from dual; 确定需要闪回的时间
sql> flashback database to timestamp sysdate- 5/1440;
flashback complete.
elapsed: 00:02:19.84
sql>
sql> alter database open resetlogs;
database altered.
elapsed: 00:02:02.26
sql> select count(*) from scott.t;
count(*)
———-
98160
elapsed: 00:00:00.03
【还原测试】(到standby状态)
sql> startup mount force
oracle instance started.
total system global area 4.2758e+10 bytes
fixed size 2262656 bytes
variable size 2.7380e+10 bytes
database buffers 1.5301e+10 bytes
redo buffers 74420224 bytes
database mounted.
sql> flashback database to restore point restore_point_readonly;
flashback complete.
再次回到physical standby
sql> alter database convert to physical standby;
database altered.
再次强制mount，应用日志
sql> startup mount force
oracle instance started.
total system global area 4.2758e+10 bytes
fixed size 2262656 bytes
variable size 2.7380e+10 bytes
database buffers 1.5301e+10 bytes
redo buffers 74420224 bytes
database mounted.
sql> alter database recover managed standby database disconnect from session;
database altered.
主库(执行)启用日志
sql> alter system set log_archive_dest_state_2=enable;
system altered.
sql> alter system archive log current;
system altered.
主备alter日志观察是否正常