DataGuard角色转换switchover（一主两备）

最新推荐文章于 2024-08-09 11:36:05 发布

cuimiwan3140

最新推荐文章于 2024-08-09 11:36:05 发布

阅读量110

点赞数

文章标签：数据库

角色切换之前，检查好参数设置

----主库参数

sys@ORCL>show parameter fal_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_server string ORCLPS1, ORCLPS2

sys@ORCL>show parameter fal_client

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_client string ORCL

sys@ORCL>show parameter standby_file

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

sys@ORCL>select group#,type,member from v$logfile where type='STANDBY';

GROUP# TYPE MEMBER

---------- ------- --------------------------------------------------

4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log

5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log

6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log

7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log

----备库参数

sys@ORCL>show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=orcl async valid_for=(

online_logfile,primary_role) d

b_unique_name=orcl

sys@ORCL>show parameter log_archive_dest_3

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_3 string service=orclps2 async valid_fo

r=(online_logfile,primary_role

) db_unique_name=orclps2

sys@ORCL>show parameter standby_file_man

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

1. switch over

此时主库为orcl，备库为orclps1和orclps2

1.1 前期准备

----主库上确认日志传输完整(no gap)

sys@ORCL>select status,gap_status from v$archive_dest_status where dest_id in (2,3);

STATUS GAP_STATUS

--------- ------------------------

ERROR RESOLVABLE GAP

VALID NO GAP

------需要等备库传输完毕，如果一直这个状态需要查找原因

sys@ORCL>select db_unique_name,type,database_mode,synchronization_status,status,gap_status from v$archive_dest_status where dest_id in (2,3);

DB_UNIQUE_ TYPE DATABASE_MODE SYNCHRONIZATION_STATUS STATUS GAP_STATUS

---------- -------------- --------------- ---------------------- --------- ------------------------

orclps1 PHYSICAL OPEN_READ-ONLY OK VALID NO GAP

orclps2 PHYSICAL MOUNTED-STANDBY OK VALID NO GAP

----备库上确定有两个lag

sys@ORCL>select * from v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME

--------------- -------------------- ------------------------------ -------------------- ------------------------------

transport lag +00 00:00:00 day(2) to second(0) interval 04/26/2016 00:18:26 04/26/2016 00:18:25

apply lag +00 00:00:00 day(2) to second(0) interval 04/26/2016 00:18:26 04/26/2016 00:18:25

apply finish ti +00 00:00:00.000 day(2) to second(3) interval 04/26/2016 00:18:26

estimated start 9 second 04/26/2016 00:18:26

up time

1.2 主库确定转为备库

----主库查看状态

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

--------- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL READ WRITE PRIMARY TO STANDBY orcl

----备库查看状态

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

----- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED orclps1 orcl

----主库转为物理备库

sys@ORCL>alter database commit to switchover to physical standby with session shutdown;

ERROR:

ORA-01034: ORACLE not available

Process ID: 5247

Session ID: 139 Serial number: 261

Database altered.

sys@ORCL>startup

1.3 备库orclps2转为主库，备库orclps1不变

----此时备库的状态

idle>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NA

----- ---------- -------------------- -------------------- ------------------------- --------------------

ORCL MOUNTED PHYSICAL STANDBY TO PRIMARY orclps2 orcl

idle>alter database commit to switchover to primary with session shutdown;

Database altered.

idle>alter database open;

Database altered.

idle>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NA

----- ---------- -------------------- -------------------- ------------------------- --------------------

ORCL READ WRITE PRIMARY RESOLVABLE GAP orclps2 orcl

1.4 原主库转为备库后，开启日志应用

----此时的原主库状态

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

--------- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL READ ONLY PHYSICAL STANDBY RECOVERY NEEDED orcl orclps2

sys@ORCL>alter database recover managed standby database using current logfile disconnect;

Database altered.

----再次查看状态

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

--------- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED orcl orclps2

1.5 备库orclps1处理(只要参数设置正确，可以不用处理，只需看一下日志是否应用)

----此时orclps2主库状态

idle>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NA

----- ---------- -------------------- -------------------- ------------------------- --------------------

ORCL READ WRITE PRIMARY SESSIONS ACTIVE orclps2 orcl

----orclps1备库状态为（会自动识别orclps2主库）

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

----- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL MOUNTED PHYSICAL STANDBY NOT ALLOWED orclps1 orclps2

----如果orclps1日志没有应用，需要先取消原主库传输日志应用，然后再应用新主库日志。或者直接重启数据库，再开启日志应用

sys@ORCL>shut immediate

sys@ORCL>startup mount

sys@ORCL>alter database recover managed standby database using current logfile disconnect;

Database altered.

----此时orclps1状态如下

sys@ORCL>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_ PRIMARY_DB_UNIQUE_NAME

----- -------------------- ---------------- -------------------- ---------- ------------------------------

ORCL MOUNTED PHYSICAL STANDBY NOT ALLOWED orclps1 orclps2

----orclps2主库的状态

idle>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NA

----- ---------- -------------------- -------------------- ------------------------- --------------------

ORCL READ WRITE PRIMARY RESOLVABLE GAP orclps2 orcl

idle>select name,open_mode,database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NA

----- ---------- -------------------- -------------------- ------------------------- --------------------

ORCL READ WRITE PRIMARY TO STANDBY orclps2 orcl

1.6 现主库切换日志，查看备库是否应用

----orclps2主库切换日志

idle>alter system switch logfile;

System altered.

----orclps1备库日志应用情况

sys@ORCL>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

---------- ---------

75 YES

76 YES

77 IN-MEMORY

76 rows selected.

----orcl备库日志应用情况

sys@ORCL>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

---------- ---------

71 YES

72 YES

72 NO

73 YES

74 YES

75 YES

76 YES

77 IN-MEMORY

196 rows selected.

----此时注意数据库保护模式，根据需要修改

idle>select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

----------------------------------------------------------------------------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

------------->这里我将最大性能修改为最高可用

idle>alter system set log_archive_dest_2='service=orclps1 sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orclps1';

idle>alter system set log_archive_dest_3='service=orcl sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orcl';

idle>alter database set standby database to maximize availability;

idle>select protection_mode,protection_level from v$database;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2114717/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2114717/

cuimiwan3140

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DataGuard角色转换switchover（一主两备）

角色切换之前，检查好参数设置 ----主库参数 sys@ORCL>show parameter fal_server NAME...
复制链接

扫一扫