逻辑DG之Standby角色转换

时间：2020-11-09 来源：乌哈旅游

Primary： 1.检查当前Primary数据库的初始化参数： SQL show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string orcl_192.168.1.222 fal_server string orcls_192.168.1.2

Primary：
1.检查当前Primary数据库的初始化参数：
SQL> show parameter fal

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string orcl_192.168.1.222
fal_server string orcls_192.168.1.223
SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

log_archive_dest_1 string location=/u01/arch_orcl

log_archive_dest_2 string service=orcls_192.168.1.223 lg
wr async valid_for=(online_log
files, primary_role) db_unique
_name=orclldg
2.修改log_archive_dest_1
SQL> alter system set log_archive_dest_1='location=/u01/arch_orcl valid_for=(online_logfiles,all_roles) db_unique_name=orclpre';

System altered.
Standby：
1.检查当前Standby数据库的初始化参数：
SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=/u01/arch_ldg valid_f
or=(online_logfiles,all_roles)
db_unique_name=orclldg
log_archive_dest_10 string
log_archive_dest_2 string service=orcl_192.168.1.222 arc
h valid_for=(online_logfiles,
primary_role) db_unique_name=o
rclpre
log_archive_dest_3 string location=/u01/arch_std valid_f
or=(standby_logfiles,standby_r
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ole) db_unique_name=orclldg
2.修改log_archive_dest_2为lgwr async
SQL> alter system set log_archive_dest_2='service=orcl_192.168.1.222 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orclpre';

System altered.
3.log_archive_dest_2已经为异步传输
SQL> show parameter archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ole) db_unique_name=orclldg
4.检查Primary是否配置了Standby Redologs
SQL> select * from v$standby_log;

no rows selected
添加：
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl/stdredo01.log' SIZE 50m;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl/stdredo02.log' SIZE 50m;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl/stdredo03.log' SIZE 50m;

Database altered.
5.检查当前Primary数据库状态：
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
把Primary置为准备转换状态：
SQL> alter database prepare to switchover to logical standby;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
再次查看状态变为准备中……，时刻准备着接受来自逻辑Standby数据库（未来新的Primary数据库）发来的REDO数据。
6.把当前的逻辑Standby转换为Primary
SQL> alter database prepare to switchover to primary;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
再次查看状态变为准备中……
7.查看Primary数据库
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY
此次检查结果关系到switchover转换是否能够成功。
8.把当前的Primary转换成逻辑Standby
SQL> alter database commit to switchover to logical standby;

Database altered.
9.逻辑Standby在接收到前Primary的转换消息，并完成相关的REDO数据之后，会自动暂停ＳＱＬ应用。如果之前没有启动ＲＥＤＯ应用，现在启动还不晚。
SQL> alter database start logical standby apply;

Database altered.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER 等会……

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY
10.转换逻辑Ｓｔａｎｄｂｙ为新的Ｐｒｉｍａｒｙ数据库
SQL> alter database commit to switchover to primary;

Database altered.
启动新的逻辑Ｓｔａｎｄｂｙ（之前的Ｐｒｉｍａｒｙ）ＳＱＬ应用
SQL> alter database start logical standby apply immediate;

Database altered.
测试：
在新的Prｉｍａｒｙ里面插入测试数据：
SQL> select * from scott.test;

ID
----------
1
2
3

SQL> insert into scott.test values(4);

1 row created.

SQL> commit;

Commit complete.

检查新的standby：
SQL> select * from scott.test;

ID
----------
1
2
3

SQL> select * from scott.test;

ID
----------
1
2
3

SQL> select * from scott.test;

ID
----------
1
2
3
可能会延迟……
SQL> select * from scott.test;

ID
----------
1
2
3
4
逻辑Ｓｔａｎｄｂｙ角色转换成功……