Oracle 11g DataGuard 角色转换(一)

——Switchover to a Physical Standby Database

【实验环境】

【Switchover前 DG主库、物理备库结构信息】

【实验过程】

1、切换前准备

1.1、主库添加standby日志组

select sequence#,group#,bytes/1024/1024 MB from v$log;

select member from v$logfile;

alter database add standby logfile group 4 '/u01/app/oracle/oradata/DGDB/redo04.std' size 50m;

alter database add standby logfile group 5 '/u01/app/oracle/oradata/DGDB/redo05.std' size 50m;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/DGDB/redo06.std' size 50m;

alter database add standby logfile group 7 '/u01/app/oracle/oradata/DGDB/redo07.std' size 50m;

1.2、初始化参数文件

1. DB_UNIQUE_NAME=beijing

2. LOG_ARCHIVE_CONFIG='DG_CONFIG=(beijing,shanghai)'

3. LOG_ARCHIVE_DEST_1=

4. 'LOCATION=/home/oracle/beijing/

5. VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

6. DB_UNIQUE_NAME=beijing'

7. LOG_ARCHIVE_DEST_2=

8. 'SERVICE=sh LGWR SYNC AFFIRM

9. VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

10. DB_UNIQUE_NAME=shanghai'

11. LOG_ARCHIVE_DEST_STATE_1=ENABLE

12. LOG_ARCHIVE_DEST_STATE_2=ENABLE

13.  

14. FAL_SERVER=sh

15. FAL_CLIENT=bj

16. DB_FILE_NAME_CONVERT='/u02/oradata/sh','/u01/app/oracle/oradata/DGDB'

17. LOG_FILE_NAME_CONVERT='/u02/oradata/sh','/u01/app/oracle/oradata/DGDB'

18. STANDBY_FILE_MANAGEMENT=AUTO

备库添加远程归档路径（红色）

1. DB_NAME=DGDB

2. DB_UNIQUE_NAME=shanghai

3. LOG_ARCHIVE_CONFIG='DG_CONFIG=(beijing,shanghai)'

4. LOG_ARCHIVE_DEST_1=

5. 'LOCATION=/u02/arch/sh/

6. VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

7. DB_UNIQUE_NAME=shanghai'

8. LOG_ARCHIVE_DEST_STATE_1=ENABLE

9.  

10. LOG_ARCHIVE_DEST_2=

11. 'SERVICE=sh LGWR SYNC AFFIRM

12. VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

13. DB_UNIQUE_NAME=beijing'

14. LOG_ARCHIVE_DEST_STATE_2=ENABLE

15.  

16. FAL_SERVER=bj

17. FAL_CLIENT=sh

18. DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DGDB','/u02/oradata/sh'

19. LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DGDB','/u02/oradata/sh'

20. STANDBY_FILE_MANAGEMENT=AUTO

 1.3、主库、备库 tnsnames

more $ORACLE_HOME/network/admin/tnsnames.ora

1.4、重启验证参数配置

show parameter fal
show parameter convert
show parameter standby

备库重启到mount查看参数
show parameter log_archive_dest_2

2、主库切物理备库

2.1、切换前主库查看是否有gap

select status,gap_status from v$archive_dest_status where dest_id=2;

alter database set standby database to maximize performance;
select database_role,protection_mode,protection_level from v$database;

          

2.3、备库 取消日志应用

alter database recover managed standby database cancel;

2.4、查看主备库角色和状态

select database_role,switchover_status from v$database;

2.5、主库切物理备库

alter database commit to switchover to physical standby with session shutdown;

切换完毕后查看主库角色，已经是：PHYSICAL STANDBY 物理备库
select database_role,switchover_status from v$database;

3、原备库切换为新主库

3.1、备库切换前应用日志、查看状态

select database_role,switchover_status from v$database;

alter database recover managed standby database disconnect;

select database_role,switchover_status from v$database;

3.2、原备库 切换为 新主库

alter database commit to switchover to primary;

切换完成后查看，已经是PRIMARY状态
select database_role,switchover_status from v$database;

3.3、新备库 （原主库）关闭远程归档、重启到mount状态应用日志

alter database recover managed standby database disconnect;

3.4、新主库开库、切换模式验证

alter database set standby database to maximize availability;

alter database open;

select database_role,open_mode,protection_mode,protection_level from v$database;

至此switchiover角色转换第二步也已经完成，新的主库shanghai（原备库）已经与原来的主库beijing完成了角色切换。





如果要使用11g DataGuard日志应用与数据读取同步进行，新的备库上可以使用Real-Time Apply方式应用日志。

alter database recover managed standby database using current logfile disconnect;

select database_role,open_mode,protection_mode,protection_level from v$database;

【实验总结】