12C-Dg 切换新花样

2021-03-15 09:14:08

12C DG切换一二三
虽然做12C的数据库维护有几年了，DG也搭建，故障处理也做不少，12C的切换有了新东西还是次测试下这个过程。

这个验证，会测试目标库的数据库版本，是否数据同步以及是否启动redo apply
整个过程为
1 主切备验证，如果不成功处理问题（参看官方文档的角色切换）
SQL> alter database switchover to xxxx20dg verify;

Database altered

select database_role,db_unique_name from v$database;

2 主备切换，在主库操作如下
SQL> alter database switchover to xxxx20dg

primary日志
2021-03-12T10:25:18.713919+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 76 secs.
2021-03-12T10:25:28.696892+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 86 secs.
2021-03-12T10:25:38.676900+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 96 secs.
2021-03-12T10:25:48.659898+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 106 secs.
2021-03-12T10:25:58.643883+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 116 secs.
2021-03-12T10:26:08.624894+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 126 secs.
2021-03-12T10:26:18.753897+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 136 secs.
2021-03-12T10:26:28.737892+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 146 secs.
2021-03-12T10:26:38.715889+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 156 secs.
2021-03-12T10:26:48.697909+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 166 secs.
2021-03-12T10:26:58.675903+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 176 secs.
2021-03-12T10:27:08.651896+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 186 secs.
2021-03-12T10:27:18.781888+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 196 secs.
2021-03-12T10:27:28.763884+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 206 secs.
2021-03-12T10:27:38.740880+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 216 secs.
2021-03-12T10:27:48.718900+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 226 secs.
2021-03-12T10:27:58.698898+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 236 secs.
2021-03-12T10:28:08.675898+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 246 secs.
2021-03-12T10:28:18.804906+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 256 secs.
2021-03-12T10:28:28.788889+08:00
CKPT (ospid: 141403) waits for event 'shutdown after switchover to standby' for 266 secs.
2021-03-12T10:29:13.090775+08:00
Switchover complete. Database shutdown required
USER (ospid: 193034): terminating the instance
2021-03-12T10:29:14.103461+08:00
Instance terminated by USER, pid = 193034
TMI: dbsdrv switchover to target END 2021-03-12 10:29:14.103508
Completed: alter database switchover to xxxx20dg
Shutting down instance (abort) (OS id: 193034)
License high water mark = 66
2021-03-12T10:29:14.144568+08:00
Instance shutdown complete (OS id: 193034)

standby:日志
oracle@host-bbb:[/oracle/diag/rdbms/xxxx20dg/xxxx20dg1/trace]tail -f alert_xxxx20dg1.log

Active process 199153 user 'oracle' program 'oracle@host-bbb (TNS V1-V3)', waiting for 'SQL*Net message from client'

Active process 177939 user 'oracle' program 'oracle@host-bbb (TNS V1-V3)', waiting for 'SQL*Net message from client'

Active process 199153 user 'oracle' program 'oracle@host-bbb (TNS V1-V3)', waiting for 'SQL*Net message from client'

2021-03-12T10:24:10.425379+08:00
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
2021-03-12T10:29:09.881609+08:00
Dumping diagnostic data in directory=[cdmp_20210312102909], requested by (instance=3, osid=207683 (CKPT)), summary=[abnormal instance termination].
2021-03-12T10:29:12.051329+08:00
Reconfiguration started (old inc 24, new inc 26)
List of instances (total 2) :
1 2
Dead instances (total 1) :
3
My inst 1
publish big name space - dead or down/up instance detected, invalidate domain 0
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
2021-03-12T10:29:12.076259+08:00
IPC Send timeout to 3.3 inc 24 for msg type 151 from opid 26
2021-03-12T10:29:12.080305+08:00
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
2021-03-12T10:29:12.125516+08:00
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-03-12T10:29:12.125574+08:00
LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-03-12T10:29:12.125581+08:00
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
2021-03-12T10:29:12.251636+08:00
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
2021-03-12T10:29:12.319514+08:00
Reconfiguration complete (total time 0.3 secs)
2021-03-12T10:29:12.914151+08:00
The Time Management Interface (TMI) is being enabled for role
transition information. This will result in messages being
output to the alert log file with the prefix 'TMI: '. This is
being enabled to make the timing of the various stages of the
role transition available for diagnostic purposes. This
output will end when the role transition is complete.
2021-03-12T10:29:12.941090+08:00
Network throttle feature is disabled as mount time

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
2021-03-12T10:29:24.963114+08:00
Reconfiguration started (old inc 26, new inc 28)
List of instances (total 3) :
1 2 3
New instances (total 1) :
3
My inst 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
2021-03-12T10:29:25.105799+08:00
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-03-12T10:29:25.105801+08:00
LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-03-12T10:29:25.105813+08:00
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
2021-03-12T10:29:25.214376+08:00
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
2021-03-12T10:29:25.279716+08:00
Reconfiguration complete (total time 0.3 secs)
2021-03-12T10:29:40.235693+08:00

* instance 3 validates domain 0
2021-03-12T10:29:42.876839+08:00
Using STANDBY_ARCHIVE_DEST parameter default value as /gd
2021-03-12T10:29:42.877094+08:00
ARC1: Becoming the 'no SRL' ARCH
2021-03-12T10:29:52.617219+08:00
TT00: Relinquishing Active Gap Manager role

切换完成后：新备关闭状态，新主mount状态

新主状态
SQL> select database_role,db_unique_name from v$database;

DATABASE_ROLE DB_UNIQUE_NAME
---------------- ------------------------------
PRIMARY xxxx20dg
SQL> select status from v$instance;

STATUS
------------
MOUNTED

新备状态：关闭状态

3 打开新主库，逐个打开其他实例。
SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
xxxx20 READ WRITE PRIMARY MAXIMUM PERFORMANCE NOT ALLOWED

4 打开新备库，新备库启动实现ADG
[oracle@host-aaa ~]$srvctl start database -db xxxx20;
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
xxxx20 READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE TO PRIMARY

5 开启新备库的redo apply进程 mrp

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

其他，这里遇到一个问题，由于之前主库没有配置log_archive_dest_2，同时配置了dgbroker,后续发现后重新配置了log_archive_dest_2，但是如上主备切换后新主（之前备库）参数log_archive_dest_2为空，此时如何更改重新启动数据库也会，这里是dgbroker的问题，删除了dgbroker，配置如下参数，使得新主备数据同步。
log_archive_dest_2='service="xxxx20pri"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="xxxx20" net_timeout=30','valid_for=(online_logfile,all_roles)' sid='*';

下一细节我们详细分析相关的几个视图。