read write方式打开PHYSICAL STANDBY，闪回和还原测试

以下大部分都在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日志观察是否正常