标签：rac database dg 0.4 col SQL ---------- archive name
文档课题：oracle 11.2.0.4 rac dg主备切换.
1、环境介绍
主库：Oracle 11.2.0.4.0 RAC(双节点)
备库：Oracle 11.2.0.4.0 RAC(双节点)
系统：Centos 7.9 64位
主库IP：
#Public IP (ens33)
192.168.133.210 hisdb1
192.168.133.211 hisdb2
#Private IP (ens37)
192.168.11.110 hisdb1-priv
192.168.11.111 hisdb2-priv
#Virtual IP (ens33)
192.168.133.212 hisdb1-vip
192.168.133.213 hisdb2-vip
#Scan IP (ens33)
192.168.133.214 hisdb-scan
备库IP：
#Public IP (ens33)
192.168.133.220 hisdb3
192.168.133.221 hisdb4
#Private IP (ens37)
192.168.11.120 hisdb3-priv
192.168.11.121 hisdb4-priv
#Virtual IP
192.168.133.222 hisdb3-vip
192.168.133.223 hisdb4-vip
#Scan IP
192.168.133.225 hisdb-scan
2、数据检查
主备切换前检查数据库情况.
--查活动会话分布，主库节点1执行.
SQL> set line 200 pagesize 200
SQL> select inst_id,username,status,count(*) from gv$session group by inst_id,username,status order by 3,4;
INST_ID USERNAME STATUS COUNT(*)
---------- ------------------------------ -------- ----------
2 SYS ACTIVE 1
1 SYS ACTIVE 2
1 ACTIVE 47
2 ACTIVE 47
1 SYS INACTIVE 1
2 SYS INACTIVE 2
6 rows selected.
--查长事务.
set linesize 200
set pagesize 5000
col transaction_duration format a45
with transaction_details as
(select inst_id, ses_addr, sysdate - start_date as diff from gv$transaction)
select s.username,
to_char(trunc(t.diff)) || ' days, ' ||
to_char(trunc(mod(t.diff * 24, 24))) || ' hours, ' ||
to_char(trunc(mod(t.diff * 24 * 60, 24))) || ' minutes, ' ||
to_char(trunc(mod(t.diff * 24 * 60 * 60, 60))) || ' seconds' as transaction_duration,
s.program,
s.terminal,
s.status,
s.sid,
s.serial#
from gv$session s, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
--检查事务
select undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal - undoblocksdone "ToDo",
decode(cputime,
0,
'unknown',
to_char(sysdate + (((undoblockstotal - undoblocksdone) /
(undoblocksdone / cputime)) / 86400),
'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
from v$fast_start_transactions;
--检查job
col schedule_owner for a20
col schedule_name for a30
col owner for a15
col job_name for a30
col job_creator for a15
set line 300
col JOB_ACTION for a40
col COMMENTS for a40
col last_start_date for a40
col last_run_duration for a35
col job_action for a70
select owner,job_name,enabled,JOB_ACTION,LAST_START_DATE,LAST_RUN_DURATION
--,comments
from dba_scheduler_jobs where owner <> 'SYS';
OWNER JOB_NAME ENABL JOB_ACTION LAST_START_DATE LAST_RUN_DURATION
--------------- ------------------------------ ----- ---------------------------------------------------------------------- ---------------------------------------- -----------------------------------
EXFSYS RLM$EVTCLEANUP TRUE begin dbms_rlmgr_dr.cleanup_events; end; 28-NOV-22 07.49.46.770281 AM -07:00 +000000000 00:00:00.015206
EXFSYS RLM$SCHDNEGACTION TRUE begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION'); end; 28-NOV-22 10.21.18.027308 PM +08:00 +000000000 00:00:00.064587
ORACLE_OCM MGMT_CONFIG_JOB TRUE ORACLE_OCM.MGMT_CONFIG.collect_config 28-NOV-22 01.01.01.233236 AM -07:00 +000000000 00:00:00.709359
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE ORACLE_OCM.MGMT_CONFIG.collect_stats 25-NOV-22 09.24.14.100917 PM -07:00 +000000000 00:00:00.216690
--查dg参数.
set linesize 500 pages 100
col value for a90
col name for a30
select name, value
from v$parameter
where name in ('db_name',
'db_unique_name',
'log_archive_config',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_3',
'log_archive_dest_state_3',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management');
NAME VALUE
------------------------------ ------------------------------------------------------------------------------------------
db_file_name_convert
log_file_name_convert
log_archive_dest_1 location=+fra
log_archive_dest_2 service=healdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=healdg
log_archive_dest_3
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 enable
fal_server
log_archive_config dg_config=(heal,healdg)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name heal
db_unique_name heal
16 rows selected.
--查dg进程状态.
col dest_name for a30
col error for a50
set lin 200 pages 100
col applied_scn for 9999999999999
select dest_id,
error,
status,
log_sequence,
applied_scn,
MAX_CONNECTIONS,
NET_TIMEOUT,
COMPRESSION
from v$archive_dest
where dest_id < 5;
DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN MAX_CONNECTIONS NET_TIMEOUT COMPRES
---------- -------------------------------------------------- --------- ------------ -------------- --------------- ----------- -------
1 VALID 31 0 1 0 DISABLE
2 VALID 32 1342449 1 30 DISABLE
3 INACTIVE 0 0 1 0 DISABLE
4 INACTIVE 0 0 1 0 DISABLE
select INST_ID, process, status, thread#, sequence#, block#, blocks
from gv$managed_standby
order by INST_ID;
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 ARCH CLOSING 1 31 1 270
1 ARCH CLOSING 1 29 55296 1901
1 ARCH CLOSING 1 27 1 21810
1 ARCH CLOSING 1 30 36864 1824
1 LNS WRITING 1 32 7213 1
2 ARCH CLOSING 2 23 57344 28
2 ARCH CLOSING 2 21 1 21060
2 ARCH CLOSING 2 22 1 107
2 ARCH CLOSING 2 22 1 107
2 LNS WRITING 2 24 7624 1
10 rows selected.
--查主库状态.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
NOT ALLOWED 当前的数据库不是带有备用数据库的主数据库
PREPARING DICTIONARY 该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备
PREPARING SWITCHOVER 接受用于切换的重做数据时，逻辑备用配置会使用它
RECOVERY NEEDED 备用数据库还没有接收到切换请求
SESSIONS ACTIVE 在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话
SWITCHOVER PENDING 适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库
SWITCHOVER LATENT 切换没有完成并返回到主数据库
TO LOGICAL STANDBY 主数据库已经收到了来自逻辑备用数据库的完整的字典
TO PRIMARY 该备用数据库可以转换为主数据库
TO STANDBY 该主数据库可以转换为备用数据库
3、切换前准备
3.1、关闭备库节点2
ssh 192.168.133.221
su - oracle
sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--主库验证gap.
SELECT LOG_ARCHIVED - LOG_APPLIED + 1 LOGGAP
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1
AND ARCHIVED = 'YES'
AND RESETLOGS_CHANGE# =
(SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2
AND APPLIED = 'YES'
AND RESETLOGS_CHANGE# =
(SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG));
LOGGAP
----------
1
--备库节点1验证备库是否为rta模式，减少gap.
ssh 192.168.133.220
su - oracle
sqlplus / as sysdba
SQL> set line 200
SQL> col dest_name for a30
SQL> select dest_id,dest_name,recovery_mode from gv$archive_dest_status where recovery_mode<>'IDLE';
DEST_ID DEST_NAME RECOVERY_MODE
---------- ------------------------------ -----------------------
1 LOG_ARCHIVE_DEST_1 MANAGED REAL TIME APPLY
说明：RECOVERY_MODE=MANAGED REAL TIME APPLY 为 real time apply，否则不是.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
3.2、配置闪回
SQL> set line 200 pages 100
SQL> col flashback_on for a10
SQL> col current_scn for 9999999999
SQL> col open_mode for a10
SQL> col switchover_status for a20
SQL> col protection_mode for a20
SQL> col name for a20
SQL> select name,current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;
NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR FLASHBACK_ OPEN_MODE SWITCHOVER_STATUS
-------------------- ----------- -------------------- ---------------- --- ---------- ---------- --------------------
HEAL 1402359 MAXIMUM PERFORMANCE PRIMARY YES NO READ WRITE SESSIONS ACTIVE
--查看asm磁盘空间.
SQL> select name,total_mb,free_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB Used%
-------------------- ---------- ---------- ----------
DATA 20479 13810 32.57
FRA 10239 7470 27.04
OCRBK 10239 9843 3.87
配置闪回路径.
SQL> alter system set db_recovery_file_dest_size=5g sid='*';
System altered.
SQL> alter system set db_recovery_file_dest='+fra' sid='*';
System altered.
注意：此处主库两个节点均需配置，否则会导致主备切换后节点2启动报错ORA-01677.
--开启闪回
SQL> alter database flashback on;
Database altered.
SQL> select name,current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;
NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR FLASHBACK_ OPEN_MODE SWITCHOVER_STATUS
-------------------- ----------- -------------------- ---------------- --- ---------- ---------- --------------------
HEAL 1403389 MAXIMUM PERFORMANCE PRIMARY YES YES READ WRITE TO STANDBY
SQL> create restore point prirest;
Restore point created.
--检查闪回点.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col time for a35
SQL> select scn,time,name from v$restore_point
SCN TIME NAME
---------- ----------------------------------- ------------------------------
1403479 29-NOV-22 09.24.04.000000000 AM PRIREST
4、正式切换
4.1、主切备
ssh 192.168.133.210
export ORACLE_SID=heal1
sqlplus / as sysdba
SQL> select trim(database_role) dbrole from v$database;
DBROLE
----------------
PRIMARY
--开启trace，用于发生问题时便于诊断.
SQL> alter system set log_archive_trace=8191 sid='*';
System altered.
SQL> show parameter log_archive_trace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace integer 8191
--执行切换命令后，原主库会自动关闭实例.
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2258040 bytes
Variable Size 352324488 bytes
Database Buffers 494927872 bytes
Redo Buffers 6471680 bytes
Database mounted.
说明：此时新备库节点2实例自动关闭.
4.2、备切主
说明：备库节点2在此前已关闭.
ssh 192.168.133.220
su - oracle
sqlplus / as sysdba
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string healdg1
SQL> select trim(database_role) dbrole from v$database;
DBROLE
----------------
PHYSICAL STANDBY
SQL> alter system set log_archive_trace=8191 sid='*';
System altered.
--执行主备切换命令后，原备库自动启动到mount.
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2258040 bytes
Variable Size 348130184 bytes
Database Buffers 499122176 bytes
Redo Buffers 6471680 bytes
Database mounted.
Database opened.
SQL> col line 200 pagesize 100
SQL> col flashback_on for a10
SQL> col current_scn for 999999999
SQL> col open_mode for a10
SQL> col switchover_status for a20
SQL> col protection_mode for a20
SQL> select current_scn,protection_mode,database_role,force_logging,flashback_on,open_mode,switchover_status from v$database;
CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR FLASHBACK_ OPEN_MODE SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- ---------- --------------------
1426063 MAXIMUM PERFORMANCE PRIMARY YES NO READ WRITE RESOLVABLE GAP
SQL> alter system set log_archive_trace=0 sid='*';
System altered.
5、切换后操作
5.1、启动mrp进程
新备库启动mrp进程.
ssh 192.168.133.210
export ORACLE_SID=heal1
sqlplus / as sysdba
--此前已开启到mount阶段.
SQL> alter database open;
Database altered.
SQL> alter system set log_archive_trace=0 sid='*';
System altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
5.2、新备库启动节点2
因原主库执行主备切换后，会自动关闭实例，此处需重启新备库节点2实例.
ssh 192.168.133.211
export ORACLE_SID=heal2
[oracle@hisdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 09:56:13 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2258040 bytes
Variable Size 352324488 bytes
Database Buffers 494927872 bytes
Redo Buffers 6471680 bytes
Database mounted.
Database opened.
5.3、新主库启动节点2
ssh 192.168.133.221
export ORACLE_SID=healdg2
SQL> startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2258040 bytes
Variable Size 348130184 bytes
Database Buffers 499122176 bytes
Redo Buffers 6471680 bytes
Database mounted.
Database opened.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string healdg2
6、切换后检查
6.1、新备库检查
ssh 192.168.133.210
export ORACLE_SID=heal1
sqlplus / as sysdba
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> set line 200 pagesize 200
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 2 27 1 93
ARCH CLOSING 1 36 12288 645
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 2 26 1 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 37 238 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 37 237 102400
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 2 28 232 1
RFS IDLE 0 0 0 0
13 rows selected.
set lin 200 pages 100
col FLASHBACK_ON for a10
col current_scn for 99999999999999
col open_mode for a20
col SWITCHOVER_STATUS for a20
col PROTECTION_MODE for a20
select current_scn,
protection_mode,
database_role,
force_logging,
FLASHBACK_ON,
open_mode,
switchover_status
from v$database;
CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR FLASHBACK_ OPEN_MODE SWITCHOVER_STATUS
--------------- -------------------- ---------------- --- ---------- -------------------- --------------------
1436775 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES YES READ ONLY WITH APPLY NOT ALLOWED
6.2、新主库检查
ssh 192.168.133.220
export ORACLE_SID=healdg1
sqlplus / as sysdba
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> set lin 200 pages 100
SQL> col FLASHBACK_ON for a10
SQL> col current_scn for 99999999999999
SQL> col open_mode for a20
SQL> col SWITCHOVER_STATUS for a20
SQL> col PROTECTION_MODE for a20
SQL> select current_scn,
2 protection_mode,
3 database_role,
4 force_logging,
5 FLASHBACK_ON,
6 open_mode,
7 switchover_status
8 from v$database;
CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FOR FLASHBACK_ OPEN_MODE SWITCHOVER_STATUS
--------------- -------------------- ---------------- --- ---------- -------------------- --------------------
1437020 MAXIMUM PERFORMANCE PRIMARY YES NO READ WRITE SESSIONS ACTIVE
7、新备库添加参数
在切换成功后，新备库需添加参数以防止新主库添加数据文件时造成新备库宕机.
ssh 192.168.133.210
su - oracle
sqlplus / as sysdba
alter system set db_file_name_convert='+data/healdg/datafile/','+data/heal/datafile/','+data/healdg/tempfile/','+data/heal/tempfile/' sid='*' scope=spfile;
alter system set log_file_name_convert='+data/healdg/tempfile/','+data/heal/tempfile/','+data/healdg/onlinelog/','+data/heal/onlinelog/','+data/healdg/controlfile/','+data/heal/controlfile/' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*';
alter system set fal_server='healdg' sid='*';
alter system set fal_client='heal' sid='*';
参考网址：
http://blog.itpub.net/29785807/viewspace-2874066/
标签：rac,
database,
dg,
0.4,
col,
SQL,
----------,
archive,
name
From： https://blog.51cto.com/u_12991611/5897250