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日志观察是否正常
