沈阳做网站比较好的公司,色彩网站设计师,公司付的网站费怎么做分录,网页设计和网站建设是同一回事吗本文主要分享Oracle一对多(一主多备)的DG环境的switchover切换#xff0c;如何进行主从切换#xff0c;切换后怎么恢复正常同步#xff1f;
1、环境说明
本文的环境为一主两备#xff0c;数据库版本为11.2.0.4#xff0c;主要信息如下#xff1a;
数据库IPdb_unique_n…本文主要分享Oracle一对多(一主多备)的DG环境的switchover切换如何进行主从切换切换后怎么恢复正常同步
1、环境说明
本文的环境为一主两备数据库版本为11.2.0.4主要信息如下
数据库IPdb_unique_name原角色切换前目标角色切换后10.10.10.160ora11g主库备库10.10.10.41dgora11g备库主库10.10.10.47sbora11g备库备库
说明 1当前主库为10.10.10.16010.10.10.41 和10.10.10.47是它的两个备库。
2switchover切换后10.10.10.41变为主库10.10.10.160和10.10.10.47成为它的两个备库。
2、主要参数配置
以下是相关数据库的主要参数配置信息以db_unique_name进行区分
ora11g:
set linesize 500 pages 0
col value for a100
col name for a50
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_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)
/db_file_name_convert /u01/oradata/dgora11g, /u01/oradata/ora11g, /u01/oradata/sbora11g, /u01/oradata/ora11g
log_file_name_convert /u01/oradata/dgora11g, /u01/oradata/ora11g, /u01/oradata/sbora11g, /u01/oradata/ora11g
log_archive_dest_1 location/u01/oradata/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) db_unique_nameora11g
log_archive_dest_2 SERVICEdgora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_namedgora11g
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
fal_server dgora11g, sbora11g
log_archive_config DG_CONFIG(ora11g,dgora11g,sbora11g)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name ora11g
db_unique_name ora11g
dgora11g:
db_file_name_convert /u01/oradata/ora11g, /u01/oradata/dgora11g
log_file_name_convert /u01/oradata/ora11g, /u01/oradata/dgora11g
log_archive_dest_1 location/u01/oradata/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) db_unique_namedgora11g
log_archive_dest_2 SERVICEora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_nameora11g
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
fal_server ora11g
log_archive_config DG_CONFIG(ora11g,dgora11g)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name ora11g
db_unique_name dgora11g
sbora11g:
db_file_name_convert /u01/oradata/ora11g, /u01/oradata/sbora11g
log_file_name_convert /u01/oradata/ora11g, /u01/oradata/sbora11g
log_archive_dest_1 location/u01/oradata/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) db_unique_namesbora11g
log_archive_dest_2 SERVICEora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_nameora11g
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
fal_server ora11g
log_archive_config DG_CONFIG(ora11g,sbora11g)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name ora11g
db_unique_name sbora11g
3、切换前检查
切换前确认备库同步情况有没有延迟
dgora11g:
sysdgora11g select value from v$dataguard_stats where nametransport lag;VALUE
----------------------------------------------------------------
00 00:00:00sysdgora11g select value from v$dataguard_stats where nameapply lag; VALUE
----------------------------------------------------------------
00 00:00:00--检查最新归档到的日志号及是否有gapsysdgora11g SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;THREAD LAST
---------- ----------1 289sysdgora11g SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;no rows selected
sbora11g:
syssbora11g select value from v$dataguard_stats where nametransport lag;VALUE
----------------------------------------------------------------
00 00:00:00syssbora11g select value from v$dataguard_stats where nameapply lag;VALUE
----------------------------------------------------------------
00 00:00:00
syssbora11g SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;THREAD LAST
---------- ----------1 289syssbora11g SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;no rows selected
检查确认两个备库实时同步没有延时后才能切换。
4、switchover切换
在切换时在主库上是不可以选择我要切换到哪个备库的这个选择是在备库上选择的下面进行SWITCHOVER切换。
–主库状态
sysora11g col name for a10;
sysora11g set linesize 100;
sysora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G READ WRITE PRIMARY TO STANDBY YES DISABLED NONE
–备库状态
dgora11g:
sysdgora11g col name for a10;
sysdgora11g set linesize 100;
sysdgora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G MOUNTED PHYSICAL STANDBY NOT ALLOWED YES DISABLED NONE
sbora11g:
syssbora11g col name for a10;
syssbora11g set linesize 100;
syssbora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G MOUNTED PHYSICAL STANDBY NOT ALLOWED YES DISABLED NONE
–将主库转为物理备库
ora11g:
alter database commit to switchover to physical standby with session shutdown;切换后这个数据库已经备关闭启动数据库这个数据库已经变为备库启动MRP进程。
sysora11g startup;
ORACLE instance started.Total System Global Area 3691200512 bytes
Fixed Size 2258680 bytes
Variable Size 1392511240 bytes
Database Buffers 2281701376 bytes
Redo Buffers 14729216 bytes
Database mounted.
Database opened.
sysora11g alter database recover managed standby database using current logfile disconnect from session;Database altered.或 alter database recover managed standby database using current logfile disconnect from session parallel 4; --检查状态可以看到已变为从库
sysora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY YES DISABLED NONE
PS主库切换后两个备库均变为TO PRIMARY状态这时就要选择切换哪个备库为主库了。
dgora11g:
sysdgora11g col name for a10;
sysdgora11g set linesize 100;
sysdgora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G MOUNTED PHYSICAL STANDBY TO PRIMARY YES DISABLED NONE sbora11g:
syssbora11g col name for a10;
syssbora11g set linesize 100;
syssbora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G MOUNTED PHYSICAL STANDBY TO PRIMARY YES DISABLED NONE –备库切换成主库
这里选择sbora11g这个备库切换为主库
syssbora11g alter database commit to switchover to primary with session shutdown;Database altered.syssbora11g alter database open;Database altered.syssbora11g select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G READ WRITE PRIMARY TO STANDBY YES DISABLED NONE
5、新主库参数调整
log_archive_config、log_archive_dest_3还需加上从库dgora11g
syssbora11g show parameter archive;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string DG_CONFIG(ora11g,sbora11g)
log_archive_dest string
log_archive_dest_1 string location/u01/oradata/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) db_unique_namesbora11g
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICEora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_nameora11g
............................................
log_archive_dest_3 string--log_archive_config 需将三节点都加上
alter system set log_archive_configDG_CONFIG(sbora11g,ora11g,dgora11g) scopeboth sid*;
alter system set log_archive_dest_3SERVICEdgora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_namedgora11g scopeboth sid*;
alter system set fal_serverora11g,dgora11g scopeboth sid*;
alter system set log_archive_dest_state_3defer scopeboth sid*;
alter system set log_archive_dest_state_3enable scopeboth sid*;6、备库参数调整
–log_archive_config 若未设置需将三节点都加上
--log_archive_config需将三节点都加上
--dgora11g 修改目标主库为sbora11g
sysdgora11g show parameter config;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/db/dbs/dr1dgora11g.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/db/dbs/dr2dgora11g.dat
log_archive_config string DG_CONFIG(ora11g,dgora11g)
sysdgora11g alter system set log_archive_configDG_CONFIG(sbora11g,ora11g,dgora11g) scopeboth sid*;System altered.--因为dgora11g这个数据库之前的主是ora11g(现在已经变成了备库)需要把dgora11g的主改成sbora11g。alter system set log_archive_dest_2SERVICEsbora11g LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_namesbora11g scopeboth sid*;alter system set FAL_SERVERora11g,sbora11g;若新的主库不在备库的DG_CONFIG配置里主库会报错
ARC3: Archivelog destination LOG_ARCHIVE_DEST_3 disabled: destination Data Guard configuration error
Archived Log entry 29 added for thread 1 sequence 300 ID 0x108ef4ac dest 1:
Fri Aug 09 20:16:12 2024
LNS: Standby redo logfile selected for thread 1 sequence 301 for destination LOG_ARCHIVE_DEST_2--ora11g备库已经配置不用修改
sysora11g show parameter log_archive_config;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG(ora11g,dgora11g,sbora11g)
7、切换后验证
–主库插入测试数据
syssbora11g create table tt0809 as select * from dba_objects;Table created.
–两备库查询
sysora11g select count(*) from tt0809;COUNT(*)
----------86266
sysdgora11g select count(*) from tt0809; COUNT(*)
----------86266
至此主从切换已完成新主库应与两从库正常同步
关注我学习更多的数据库知识
