物理DG以WR方式打开 - 测试COPY功能后恢复 standby应用日志。

最新推荐文章于 2024-07-19 15:30:59 发布

kaertiger

最新推荐文章于 2024-07-19 15:30:59 发布

阅读量1k

点赞数

分类专栏： DG 文章标签：测试 archive database performance sql system

本文链接：https://blog.csdn.net/kaertiger/article/details/6997133

版权

DG 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

物理Data Guard配合FLASH BACK 功能可以将备库置于read/write模式测试应用或者处理报表
完成测试报表后，依旧可以flashback到read/write时的状态，继续用作standby database 。




本次试验目的：
1.测试 maximize performance 模式下，以R/W状态打开操作，并利用flash back 恢复standby DB
2.试验 Copy From to 功能。


环境 ：oracle 10.2.0.4.0
---------------------------------------------
Primary : 192.168.121.128    
dbname:karldb
tnsname: karldb.primary
---------------------------------------------
Standby : 192.168.121.129	 
dbname:karldb
tnsname: karldb.standby




****************************Part One：R/W打开Standby 库***************************


前言：当前DG已经搭建成功，且运行正常。




一.查看主库参数:


SQL> show parameter log_archive_dest_state_2;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE




SQL> col name format a50
SQL> set linesize 120
SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 7;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/archive/1_14_762489444.dbf                    14 YES NO
karldb.standby                                             14 YES YES
/home/oracle/archive/1_13_762489444.dbf                    13 YES NO
karldb.standby                                             13 YES YES
/home/oracle/archive/1_12_762489444.dbf                    12 YES NO
karldb.standby                                             12 YES YES


6 rows selected.




SQL> show parameter log_archive_dest


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/home/oracle/archive
log_archive_dest_2                   string      service=karldb.standby








二.查看备库参数
SQL> set linesize 120
SQL> col name format a50
SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 3;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/standby_archive/1_14_762489444.dbf            14 YES YES		//显然到这里已经应用了所有primary库上所有的archive log
/home/oracle/standby_archive/1_13_762489444.dbf            13 YES YES




SQL> select name,open_mode,protection_mode from v$database;


NAME                                               OPEN_MODE  PROTECTION_MODE
-------------------------------------------------- ---------- --------------------
KARLDB                                             MOUNTED    MAXIMUM PERFORMANCE




三.在primary库上手动归档当前redo


SQL> alter system archive log current;


System altered.


SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 7;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/archive/1_15_762489444.dbf                    15 YES NO
/home/oracle/archive/1_14_762489444.dbf                    14 YES NO
karldb.standby                                             14 YES YES
/home/oracle/archive/1_13_762489444.dbf                    13 YES NO
karldb.standby                                             13 YES YES
/home/oracle/archive/1_12_762489444.dbf                    12 YES NO




此时查看 standby库上的应用归档情况:
SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 3;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/standby_archive/1_14_762489444.dbf            14 YES YES		//这里仍然是14，原因是此时没有打开standby 库的listener.
/home/oracle/standby_archive/1_13_762489444.dbf            13 YES YES


//此时查看 主库 alert log :
==================================================================================
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Sun Nov 20 22:55:11 2011
Errors in file /home/oracle/admin/karldb/bdump/karldb_arc1_5631.trc:
ORA-12541: TNS:no listener
PING[ARC1]: Heartbeat failed to connect to standby 'karldb.standby'. Error is 12541.
....
==================================================================================
OK,不要着急，我们继续！


四.修改priamry 参数， log_archive_dest_state_2设置为DEFER后可以延迟归档到standby库


SQL> alter system set log_archive_dest_state_2=DEFER;


System altered.


SQL> show parameter log_archive_dest_state_2;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER




五.检查defer作用。
1.开启 standby 库的listener
oracle@localhost ~]$ lsnrctl start;


2.归档 primary 库 当期redo log


SQL> alter system archive log current;


System altered.


SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 7;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/archive/1_16_762489444.dbf                    16 YES NO		//未apply
/home/oracle/archive/1_15_762489444.dbf                    15 YES NO		//未apply
/home/oracle/archive/1_14_762489444.dbf                    14 YES NO
karldb.standby                                             14 YES YES
/home/oracle/archive/1_13_762489444.dbf                    13 YES NO
karldb.standby                                             13 YES YES


6 rows selected.




3.等待一小会，因为处于maximize performance ，要间隔一小会查看archive log 是否被应用。


SQL> select * from (
  2  select name,sequence#,archived,applied 
  3  from v$archived_log
  4  order by sequence# desc )
  5  where rownum < 3;


NAME                                                SEQUENCE# ARC APP
-------------------------------------------------- ---------- --- ---
/home/oracle/standby_archive/1_14_762489444.dbf            14 YES YES	//还是14，说明primary log_archive_dest_state_2=defer 起作用了。
/home/oracle/standby_archive/1_13_762489444.dbf            13 YES YES






六.打开standby 的flash back
1.
SQL> alter system set db_recovery_file_dest_size =4G;


System altered.


2.  
SQL> alter system set db_recovery_file_dest = '/ora_data/flashback';


System altered.


SQL> show parameter db_recovery_file_dest;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /ora_data/flashback
db_recovery_file_dest_size           big integer 4G




七.cancel standby database 恢复模式并设置 restore point
SQL> alter database recover managed standby database cancel;


Database altered.


SQL> create restore point current_apply_mode guarantee flashback database;


Restore point created.


SQL> set linesize 120
SQL> col name format a50
SQL> select scn,name from v$restore_point;


       SCN NAME
---------- --------------------------------------------------
    806425 CURRENT_APPLY_MODE
	
八.激活standby 库
SQL> alter database activate standby database;


Database altered.


九.FORCE启standby到mount状态。
SQL> startup mount force
ORACLE instance started.


Total System Global Area  356515840 bytes
Fixed Size                  2020736 bytes
Variable Size             121637504 bytes
Database Buffers          226492416 bytes
Redo Buffers                6365184 bytes
Database mounted.


十.修改standby库到 MAXIMIZE PERFORMANCE模式并打开到read/write状态
1.SQL> alter database set standby database to maximize performance;


Database altered.


2.SQL> alter database open;


Database altered.


SQL> select name,open_mode,protection_mode from v$database;


NAME                                               OPEN_MODE  PROTECTION_MODE
-------------------------------------------------- ---------- --------------------
KARLDB                                             READ WRITE MAXIMUM PERFORMANCE	




完成这一步就是可以开始使用standby库进行测试和报表了

下篇将继续实验COPY功能。

链接索引

物理DG以WR方式打开 - 测试COPY功能后 恢复 standby应用日志。(一)

物理DG以WR方式打开 - 测试COPY功能后 恢复 standby应用日志。(二)
物理DG以WR方式打开 - 测试COPY功能后 恢复 standby应用日志。(三)

kaertiger

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
物理DG以WR方式打开 - 测试COPY功能后恢复 standby应用日志。

物理Data Guard配合FLASH BACK 功能可以将备库置于read/write模式测试应用或者处理报表完成测试报表后，依旧可以flashback到read/write时的状态，继续用作standby database 。本次试验目的：1.测试 maximize performance 模式下，以R/W状态打开操作，并利用flash back 恢复standby DB2.试
复制链接

扫一扫