前提
每年很多项目多会有年度切换演练,有的叫BCP。在一个企业的核心系统中数据库在切换演练过程中就扮演了很重要的角色。而ADG就是数据库切换演练中的主角。
ADG主备切换大致过程:
1.10g,11g主先切成备,备再切成主。这是原来的主库角色是备库,原来的备库角色是主库。
切换回来的顺序相同。
2.12c对ADG进行了改进,增加一键切换功能,即在主库切成备库后,备库自动shut immediate,并不需要切换成主库。在read only模式下open之后直接开启mpr0即可。对于RAC集群备库而言,当主库切换备库后,备库的数据库资源状态为mount closed。这时不允许关闭数据库。直接在read only模式下open后,开启mpr0进行即可。
3.19c和11g相同
知识点
1.双rac主备库切换过程中,主库集群关闭节点,留一个节点。备库与主库相同。
2.在切换前,检查主备库状态。主库SWITCHOVER_STATUS为TO STANDBY,备库SWITCHOVER_STATUS为NOT ALLOWED为正常状态。
主库
SQL> select DATABASE_ROLE,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
备库
SQL> select DATABASE_ROLE,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
双RAC主备切换流程
主库
1.停止数据库第二节点
srvctl stop instance -i orcl19c2 -db orcl19c
2.检查数据库切换状态
select DATABASE_ROLE,switchover_status from v$database;
set wrap off
column host_name for a20
select host_name,instance_name,database_status from v$instance;
3.主库切换为备库
alter database commit to switchover to standby with session shutdown;
shutdown immediate;
startup nomount;
4.启动mpr0进程
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;
5.停止日志传输
alter system set log_archive_dest_state_2=defer;
6.启动第二节点
srvctl start instance -i orcl19c2 -db orcl19c
备库
1.停止备库第二节点
srvctl stop instance -i orcl19c2 -db orcl19cadg
2.检查数据库切换状态
select DATABASE_ROLE,switchover_status from v$database;
set wrap off
column host_name for a20
select host_name,instance_name,database_status from v$instance;
3.备库切换成主库
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup;
4.开启日志传输
alter system set log_archive_dest_state_2=enable;
5.启动第二节点
srvctl start instance -i orcl19c2 -db orcl19cadg
切换后主库第一节点日志切换测试
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 70
Next log sequence to archive 71
Current log sequence 71
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 79
Next log sequence to archive 80
Current log sequence 80
2022-09-01T15:17:23.126990+08:00
TT03 (PID:36294): SRL selected for T-1.S-70 for LAD:2
2022-09-01T15:17:39.486807+08:00
Thread 1 cannot allocate new log, sequence 71
Checkpoint not complete
Current log# 2 seq# 70 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:17:42.578936+08:00
Thread 1 advanced to log sequence 71 (LGWR switch)
Current log# 1 seq# 71 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:17:43.167161+08:00
TT03 (PID:36294): SRL selected for T-1.S-71 for LAD:2
2022-09-01T15:17:43.888794+08:00
ARC2 (PID:32150): Archived Log entry 59 added for T-1.S-70 ID 0x5eee20b2 LAD:1
2022-09-01T15:17:50.867738+08:00
Process termination requested for pid 46709 [source = rdbms], [info = 2] [request issued by pid: 31606, uid: 1601]
2022-09-01T15:19:41.037266+08:00
Process termination requested for pid 47609 [source = rdbms], [info = 2] [request issued by pid: 31606, uid: 1601]
2022-09-01T15:22:25.391815+08:00
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
Current log# 1 seq# 71 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:26.385940+08:00
Thread 1 advanced to log sequence 72 (LGWR switch)
Current log# 2 seq# 72 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:26.912686+08:00
ARC3 (PID:32168): Archived Log entry 64 added for T-1.S-71 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:27.234372+08:00
TT03 (PID:36294): SRL selected for T-1.S-72 for LAD:2
2022-09-01T15:22:27.925223+08:00
Thread 1 cannot allocate new log, sequence 73
Checkpoint not complete
Current log# 2 seq# 72 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:29.414518+08:00
Thread 1 advanced to log sequence 73 (LGWR switch)
Current log# 1 seq# 73 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:29.662040+08:00
TT03 (PID:36294): SRL selected for T-1.S-73 for LAD:2
2022-09-01T15:22:29.754787+08:00
ARC0 (PID:32080): Archived Log entry 67 added for T-1.S-72 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:43.283426+08:00
Thread 1 advanced to log sequence 74 (LGWR switch)
Current log# 2 seq# 74 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:43.370557+08:00
ARC1 (PID:32136): Archived Log entry 68 added for T-1.S-73 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:43.536130+08:00
TT03 (PID:36294): SRL selected for T-1.S-74 for LAD:2
2022-09-01T15:22:44.289143+08:00
Thread 1 cannot allocate new log, sequence 75
Checkpoint not complete
Current log# 2 seq# 74 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:46.221380+08:00
Thread 1 advanced to log sequence 75 (LGWR switch)
Current log# 1 seq# 75 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:46.503095+08:00
TT03 (PID:36294): SRL selected for T-1.S-75 for LAD:2
2022-09-01T15:22:46.831884+08:00
ARC2 (PID:32150): Archived Log entry 73 added for T-1.S-74 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:47.041320+08:00
Thread 1 cannot allocate new log, sequence 76
Checkpoint not complete
Current log# 1 seq# 75 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:47.355773+08:00
Thread 1 advanced to log sequence 76 (LGWR switch)
Current log# 2 seq# 76 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:47.651886+08:00
TT03 (PID:36294): SRL selected for T-1.S-76 for LAD:2
2022-09-01T15:22:47.847627+08:00
ARC3 (PID:32168): Archived Log entry 75 added for T-1.S-75 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:48.162486+08:00
Thread 1 cannot allocate new log, sequence 77
Checkpoint not complete
Current log# 2 seq# 76 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:50.774310+08:00
Thread 1 advanced to log sequence 77 (LGWR switch)
Current log# 1 seq# 77 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:50.836857+08:00
ARC0 (PID:32080): Archived Log entry 76 added for T-1.S-76 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:51.014042+08:00
TT03 (PID:36294): SRL selected for T-1.S-77 for LAD:2
2022-09-01T15:22:51.501332+08:00
Thread 1 cannot allocate new log, sequence 78
Checkpoint not complete
Current log# 1 seq# 77 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:55.327749+08:00
Thread 1 advanced to log sequence 78 (LGWR switch)
Current log# 2 seq# 78 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:22:55.764457+08:00
TT03 (PID:36294): SRL selected for T-1.S-78 for LAD:2
2022-09-01T15:22:55.909549+08:00
ARC1 (PID:32136): Archived Log entry 81 added for T-1.S-77 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:57.097027+08:00
Thread 1 advanced to log sequence 79 (LGWR switch)
Current log# 1 seq# 79 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:22:57.342453+08:00
ARC2 (PID:32150): Archived Log entry 83 added for T-1.S-78 ID 0x5eee20b2 LAD:1
2022-09-01T15:22:57.393514+08:00
TT03 (PID:36294): SRL selected for T-1.S-79 for LAD:2
2022-09-01T15:22:57.946456+08:00
Thread 1 cannot allocate new log, sequence 80
Checkpoint not complete
Current log# 1 seq# 79 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_1.282.1114197171
2022-09-01T15:23:00.822619+08:00
Thread 1 advanced to log sequence 80 (LGWR switch)
Current log# 2 seq# 80 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_2.269.1114197173
2022-09-01T15:23:01.088778+08:00
ARC3 (PID:32168): Archived Log entry 84 added for T-1.S-79 ID 0x5eee20b2 LAD:1
2022-09-01T15:23:01.229456+08:00
TT03 (PID:36294): SRL selected for T-1.S-80 for LAD:2
切换后主库第二节点日志切换测试
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 41
Next log sequence to archive 41
Current log sequence 41
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 49
Next log sequence to archive 50
Current log sequence 50
2022-09-01T15:12:56.763523+08:00
TT04 (PID:4501): SRL selected for T-2.S-41 for LAD:2
2022-09-01T15:16:47.369218+08:00
Thread 2 advanced to log sequence 42 (LGWR switch)
Current log# 4 seq# 42 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_4.259.1114197175
2022-09-01T15:16:48.602344+08:00
Thread 2 cannot allocate new log, sequence 43
Checkpoint not complete
Current log# 4 seq# 42 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_4.259.1114197175
2022-09-01T15:16:48.899889+08:00
ORACLE Instance orcl19c2 - Cannot allocate log, archival required
Thread 2 cannot allocate new log, sequence 43
All online logs need archiving
Examine archive trace files for archiving errors
Current log# 4 seq# 42 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_4.259.1114197175
2022-09-01T15:16:55.678879+08:00
NOTE: ASMB mounting group 2 (FRA)
NOTE: Assigned CGID 0x10009 for group 2
NOTE: ASMB process initiating disk discovery for grp 2 (reqid:0)
2022-09-01T15:17:02.461174+08:00
NOTE: Assigning number (2,0) to disk (/dev/sdf1)
SUCCESS: mounted group 2 (FRA)
NOTE: grp 2 disk 0: FRA_0000 path:/dev/sdf1
2022-09-01T15:17:03.849474+08:00
TT03 (PID:4481): SRL selected for T-2.S-42 for LAD:2
2022-09-01T15:17:05.824600+08:00
ARC2 (PID:2334): Archived Log entry 43 added for T-2.S-41 ID 0x5eee20b2 LAD:1
2022-09-01T15:17:06.373295+08:00
Thread 2 advanced to log sequence 43 (LGWR switch)
Current log# 3 seq# 43 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_3.260.1114197173
2022-09-01T15:17:08.358400+08:00
TT03 (PID:4481): SRL selected for T-2.S-43 for LAD:2
2022-09-01T15:17:09.346129+08:00
ARC3 (PID:2338): Archived Log entry 45 added for T-2.S-42 ID 0x5eee20b2 LAD:1
2022-09-01T15:17:11.780996+08:00
NOTE: dependency between database orcl19cadg and diskgroup resource ora.FRA.dg is established
2022-09-01T15:17:14.994625+08:00
Process termination requested for pid 10802 [source = rdbms], [info = 2] [request issued by pid: 1360, uid: 1601]
2022-09-01T15:17:26.468074+08:00
Thread 2 cannot allocate new log, sequence 44
Checkpoint not complete
Current log# 3 seq# 43 mem# 0: +DATA/ORCL19CADG/ONLINELOG/group_3.260.1114197173
2022-09-01T15:17:26.729275+08:00
Process termination requested for pid 15891 [source = rdbms], [info = 2] [request issued by pid: 1360, uid: 1601]
2022-09-01T15:17:28.426305+08:00
Thread 2 advanced to log sequence 44 (LGWR switch)
Current log# 4 seq# 44 mem# 0: +DATA/ORCL