暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle 19c 通过 broker搭建三节点adg，开启故障切换

oracle 19c 通过 broker搭建三节点adg，开启故障切换

原创 四九年入国军 4天前
8
                  
192.168.100.11  dg1  orcl_p
192.168.100.13  dg2  orcl_s


1、主库操作

 select force_logging from v$database;
 alter database force logging;
 
 alter  database archivelog;


select group#,THREAD#,bytes/1024/1024 as M from v$log;
    GROUP#    THREAD#          M
---------- ---------- ----------
         1          1       1024
         2          1       1024
         3          1       1024
select member  from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/ORCL/redo03.log
/oradata/ORCL/redo02.log
/oradata/ORCL/redo01.log


alter database add standby logfile   thread 1  group 11 '/oradata/ORCL/standby_redo11.log' size 1g;
alter database add standby logfile   thread 1  group 12 '/oradata/ORCL/standby_redo12.log' size 1g;
alter database add standby logfile   thread 1  group 13 '/oradata/ORCL/standby_redo13.log' size 1g;
alter database add standby logfile   thread 1  group 14 '/oradata/ORCL/standby_redo14.log' size 1g;
alter database add standby logfile   thread 1  group 15 '/oradata/ORCL/standby_redo15.log' size 1g;

--查看standby log
select group#,THREAD#, bytes/1024/1024 from v$standby_log;

---主库dg 参数修改
alter system set db_unique_name='orcl_p' scope=spfile;
alter system set DB_RECOVERY_FILE_DEST_SIZE=1t;
alter  system set db_recovery_file_dest='/home/oracle/flashdb';
--闪回保留时间，默认24小时
alter system set db_flashback_retention_target=1440;


--主库RAC的原有参数不变，添加下列参数：
--LOG_ARCHIVE_CONFIG后面的是服务名，不是tnsnames.ora的名字
--配置broker时LOG_ARCHIVE_DEST_2需设置为空,配置broker成功后会自动配置
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_p,orcl_s)';
alter system set LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_p';
alter system set  fal_server='orcl_s';
alter system set fal_client='orcl_p';
alter system set standby_file_management=AUTO;
--以下参数设置后可以不立即重启（不生效只会导致自动创建数据文件失败，其余无影响）
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;



--为备库创建参数文件
create pfile='/home/oracle/init_dg.ora' from spfile;


--修改监听：
----global_dbname  对应 service_name	
SID_LIST_listener=
	(SID_LIST=
		(SID_DESC=
			(GLOBAL_DBNAME=orcl_p)
			(SID_NAME=orcl)
			(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db)
			)
		(SID_DESC=
			(GLOBAL_DBNAME=orcl_p_DGMGRL)
			(SID_NAME=orcl)
			(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db)
		)
)

--修改tnsnames.ora

orcl_p =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_p)
    )
  )

orcl_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_s)
    )
  )
  
2、备库操作

 
--复制密码文件
--参数文件
--1、把db_unique_name 修改成orcl_s
--2、删除dg参数

startup nomount  pfile='/home/oracle/init_dg.ora'
create spfile from pfile='/home/oracle/init_dg.ora';

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_p,orcl_s)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_s';
alter system set  fal_server='orcl_p';
alter system set fal_client='orcl_s';
alter system set standby_file_management=AUTO;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;



--修改监听：
----global_dbname  对应 service_name	
SID_LIST_listener=
	(SID_LIST=
		(SID_DESC=
			(GLOBAL_DBNAME=orcl_s)
			(SID_NAME=orcl)
			(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db)
			)
		(SID_DESC=
			(GLOBAL_DBNAME=orcl_s_DGMGRL)
			(SID_NAME=orcl)
			(ORACLE_HOME=/u01/app/oracle/product/19.3.0/db)
		)
)

--修改tnsnames.ora

orcl_p =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_p)
    )
  )

orcl_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_s)
    )
  )

 



--测试
sqlplus "sys/oracle@orcl_p as sysdba"
sqlplus "sys/oracle@orcl_s as sysdba"



rman target  sys/oracle@orcl_p auxiliary  sys/oracle@orcl_s
run{
  allocate channel prmy11 type disk;
  allocate channel prmy12 type disk;
  allocate channel prmy13 type disk;
  allocate channel prmy14 type disk;
  allocate auxiliary channel prmy21 type disk;
  allocate auxiliary channel prmy22 type disk;
  allocate auxiliary channel prmy23 type disk;
  allocate auxiliary channel prmy24 type disk;
  duplicate target database for standby  from active database nofilenamecheck;
  release channel prmy11;
  release channel prmy12;
  release channel prmy13;
  release channel prmy14;
  release channel prmy21;
  release channel prmy22;
  release channel prmy23;
  release channel prmy24;
}

--上面报错

RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.



--备库恢复后需要手工open才能生成standby_redo文件
alter database recover managed standby database disconnect from session;
recover managed standby database cancel;
alter database open;
--这一步会生成redo文件
alter database recover managed standby database using current logfile disconnect from session;



--配置 broker

--主备库执行：
ALTER SYSTEM SET dg_broker_start=true;


dgmgrl sys/oracle@orcl_p 
--创建配置，指定主库
--根据DB_UNIQUE_NAME来配置
--第一个orcl_p指的是主库的 db_unique_name; 第二个的 orcl_p 是tnsnames.ora 中主库得网络服务名称。
CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS orcl_p CONNECT IDENTIFIER IS orcl_p;
--添加备库
ADD DATABASE orcl_s AS CONNECT IDENTIFIER IS orcl_s MAINTAINED AS PHYSICAL;
--配置生效
ENABLE CONFIGURATION;




--查看配置
show  configuration;
show configuration verbose;

--查看指定库的详细配置
show database verbose  orcl_p;



--删除配置
remove configuration;




--切换库
SWITCHOVER TO orcl_s;
switchover to orcl_p;


SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM
  V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1        242 IDLE
N/A      MRP0               1        242 APPLYING_LOG  




FSFO允许代理在主库故障的情况下自动故障转移到先前选择的备库，无需手动执行任何步骤，以便快速可靠地恢复业务。FSFO只能在代理配置中使用，并且只能通过DGMGRL或OEM进行配置。
FSFO支持在最高可用性与最高性能模式下使用。最大可用性模式保证不会丢失任何数据，最高性能模式可保证丢失的数据量不超过 FastStartFailoverLagLimit 属性指定的数据量（单位为秒）



--配置Fast Start Failover
--主备库都需要开闪回
alter system set DB_RECOVERY_FILE_DEST_SIZE=1t;
alter  system set db_recovery_file_dest='/home/oracle/flashdb';
--闪回保留时间，默认24小时
alter system set db_flashback_retention_target=1440;
recover managed standby database cancel;
alter database flashback on;
recover managed standby database using current logfile disconnect from session;

--开启 observer监控
dgmgrl sys/oracle@orcl_s "start observer" &

dgmgrl sys/oracle@orcl_s
ENABLE FAST_START FAILOVER;



FastStartFailoverLagLimit表示,自动故障转移允许的最大数据丢失量（单位为秒），仅在最高性能保护模式时才可使用。
FastStartFailoverLagLimit作为DG可接受的最大延迟时间，备库的apply lag只有在此限制之内，才允许FSFO。
如果无法维持已配置的数据丢失保证，主库上的redo生成将停止。为了避免长时间停顿，Observer或者目标备库可能会在第一次记录到无法发生FSFO之后，允许主库继续生成redo。此时若主库故障，将无法发生FSFO。
--默认值为30秒，最小值为10秒
edit configuration set property faststartfailoverlaglimit=60;


show fast_start failover;

Fast-Start Failover:  Disabled

  Protection Mode:    MaxPerformance
  Lag Limit:          30 seconds                 --从库得日志应用晚于主库多久，在这个范围内才允许故障切换              

  Threshold:          30 seconds                 --当主库无效时，observer需要等待多久才进行故障切换
  Active Target:      (none)
  Potential Targets:  (none)
  Observer:           (none)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)


--切换到高可用性模式
--broker默认就是这种模式
--这种模式在不影响主库可用的前提下，提供最高级别的数据库保护策略：
edit database orcl_p set property LogXptMode=SYNC;
edit database orcl_s set property LogXptMode=SYNC;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
--验证 show configuration ;

--切换最大性能模式:
--这种模式下不能开启快速故障转移
edit database orcl_p set property LogXptMode=ASYNC;
edit database orcl_s set property LogXptMode=ASYNC;
EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance; 



--多备库需要确定每个库得failover对象：
edit database orcl_p set property 'FastStartFailoverTarget'='orcl_s';
edit database orcl_s set property 'FastStartFailoverTarget'='orcl_p';


--修改网络超时时间默认30秒，改成60秒
edit database orcl_s set property NetTimeout=60;
edit database orcl_p set property NetTimeout=60;




--新增第二个备库orcl_b
ADD DATABASE orcl_b AS CONNECT IDENTIFIER IS orcl_b MAINTAINED AS PHYSICAL;
enable configuration;


DGMGRL>  show  configuration ;

Configuration - dg_config

  Protection Mode: MaxAvailability
  Members:
  orcl_p - Primary database
    orcl_s - (*) Physical standby database 
    orcl_b - Physical standby database 


--备注：
----备库的log_archive_dest_n 里面都是空，只有主库才有值

----switch 切换的时候要用dgmgrl 登录主库操作，否则会报错
墨力计划oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论