为了支持应用的压力测试,将一个1级standby DB读写打开供测试使用。使用完毕后flashback回到读写打开前的状态,继续作为1级standby DB工作。
此方法在上家公司接触过,不过没有具体实践,这次正好有机会实际操作,记录下来。
关键字:DG flashback
[@more@]设置闪回并将STANDBY DB读写打开
1停止STANDBY DB的recover
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2设置STANDBY DB的flashback
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;
SQL>ALTER SYSTEM SET db_recovery_file_dest='/flashback' SCOPE=BOTH;
SQL> alter system set db_flashback_retention_target=4320 scope=both;
3打开STANDBY DB的flashback
SQL>alter database flashback on;
4创建并检查闪回点
SQL>CREATE RESTORE POINT flashback_point GUARANTEE FLASHBACK DATABASE;
col name for a30
col time for a35
SQL> select NAME,SCN,TIME from v$restore_point;
检查flashback_area下是否生成了闪回文件。
5停止主备库的日志同步
PRIMARY DB
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
STANDBY DB
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
6激活并读写打开STANDBY DB
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> select CONTROLFILE_TYPE from v$database;--结果应由standby变为current
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN;
7创建一个测试表
SQL>create table test_flash as select object_id,object_name from dba_objiects where rownum<2;
SQL>select count(1) from test_flash;
现在可以提供给测试使用了。
闪回并恢复到STANDBY DB状态。
1重启数据库
在确定应用全部停止后重启DB到mount
SQL>shutdown immediate
SQL>startup mount
2闪回DB到激活前
SQL> FLASHBACK DATABASE TO RESTORE POINT flashback_point;
3只读打开验证测试表
SQL>alter database open read only;
SQL> select count(1) from test_flash;--提示表或视图不存在,即可验证闪回成功。
4转换DB到STANDBY状态
SQL>shutdown immediate
SQL>startup mount
SQL> select controlfile_type from v$database;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> shutdown immediate
SQL> STARTUP MOUNT;
SQL> select controlfile_type from v$database;
5开启主备库日志传输
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='enable';
6开启STANDBY DB日志recover
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7确认DG工作正常
在PRIMARY DB切换一个日志
SQL>alter system switch logfile;
在主备库检查sequence#
SQL>select max(sequence#) from v$log_history;
8清除闪回点并关闭闪回
SQL> DROP RESTORE POINT flashback_point;
SQL>alter database flashback off;
SQL>select a.DB_UNIQUE_NAME,a.FLASHBACK_ON from v$database a;
SQL>ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=BOTH;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21129591/viewspace-1055503/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21129591/viewspace-1055503/