Oracle 26ai搭建ADG Far Sync日志备库

作者IT邦德中国DBA联盟(ACDU)成员15年DBA工作经验Oracle、PostgreSQL ACECSDN博客专家及B站知名UP主全网粉丝15万擅长主流Oracle、MySQL、PG、高斯及Greenplum备份恢复安装迁移性能优化、故障应急处理文章目录1.DG Far Sync概述2.配置环境3.Far Sync实例3.1 在主库上创建far sync实例的控制文件3.2 创建Far Sync实例的pfile文件3.3 在主库上以pfile启动并配置standbylogfile3.4 生成spfile文件3.5 在far sync实例上配置pfile文件3.6 创建监听和tnsnames.ora3.7 启动far sync到mount模式4.备库搭建1.DG Far Sync概述Active Data Guard Far Sync是Oracle 12c引入的新功能也称为Far Sync Standby。传统的ADG主要由主备库组成redo数据直接传输到备库待备库成功写入standbylog file才能保证数据的最大保护。如果主备库之间的网络带宽不足、备库性能较差等因素不可避免会导致主备库之间的同步延迟此时一旦出现主库宕机的情况极易出现数据丢失。12c开始可以在主备库之间添加一个Far Sync实例,redo数据先由主库传输到Far Sync实例再由于Far Sync实例转发给备库。Far Sync实例只有密码文件、init参数文件和控制文件而没有数据文件只负责接收归档和redo而不需要应用所以主库到Far Sync实例的同步非常迅速基本没有延迟。考虑到可能发生Data Guard 角色转换即switchover/failover可以在距离备库较近的地方也配置Far Sync实例这个Far Sync实例只有在当前的备库切换为主库后才启用。考虑到Far Sync实例的单点故障可以在距离主库交近的地点配置2个Far Sync实例起到备用的作用。2.配置环境强制日志、开归档mkdir -p /u01/recoverymkdir -p /u01/archSQL set linesize 300SQL select name,open_mode,log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;SQL alter database force logging;数据库开归档如果想开快速恢复区的设置做如下操作alter system set db_recovery_file_dest_size1800g;alter system set db_recovery_file_dest‘/u01/recovery’;开归档的方式如下alter system set log_archive_dest_1‘location/u01/arch’;SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE ARCHIVELOG;ALTER DATABASE OPEN;alter system switch logfile;3.Far Sync实例3.1 在主库上创建far sync实例的控制文件ALTER DATABASE CREATE FAR SYNCINSTANCE CONTROLFILE AS ‘/home/oracle/control01.ctl’;3.2 创建Far Sync实例的pfile文件create pfile‘/home/oracle/pfile.init’ from spfile;DB_UNIQUE_NAMEoradbDB_FILE_NAME_CONVERT/oradbst/,/oradb/LOG_FILE_NAME_CONVERT/oradbst/,/oradb/FAL_SERVERoradbstLOG_ARCHIVE_CONFIGDG_CONFIG(oradb,oradbfs,oradbst)LOG_ARCHIVE_DEST_1LOCATION/u01/arch VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEoradbLOG_ARCHIVE_DEST_2SERVICEoradbfs SYNC AFFIRM VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEoradbfsLOG_ARCHIVE_DEST_STATE_2ENABLELOG_ARCHIVE_DEST_3SERVICEoradbst ASYNC NOAFFIRM VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEoradbstLOG_ARCHIVE_DEST_STATE_3ALTERNATEstandby_file_managementAUTO3.3 在主库上以pfile启动并配置standbylogfilestartup pfile‘/home/oracle/pfile.init’;alter system set standby_file_managementmanual;添加standbylogfilealter database add standby logfile group 4 (‘/u01/app/oracle/oradata/ORADB/standby_redo04.log’) size 200M;alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/ORADB/standby_redo05.log’) size 200M;alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/ORADB/standby_redo06.log’) size 200M;alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/ORADB/standby_redo07.log’) size 200M;alter system set standby_file_managementAUTO;3.4 生成spfile文件create spfile from pfile‘/home/oracle/pfile.init’;将上面的控制文件和参数文件和密码文件复制到Far Sync实例所在的服务器。将控制文件存放在/u01/app/oracle/oradata/oradbfs/control01.ctlscp $ORACLE_HOME/dbs/orapworadb192.168.2.12:/u01/app/oracle/product/23.26.1/dbhome_1/dbs/scp $ORACLE_HOME/dbs/orapworadb192.168.2.11:/u01/app/oracle/product/23.26.1/db_1/dbs/dbs3.5 在far sync实例上配置pfile文件DB_UNIQUE_NAMEoradbfsDB_FILE_NAME_CONVERT/ORADB/,/oradbfs/,/oradbst/,/oradbfs/LOG_FILE_NAME_CONVERT/ORADB/,/oradbfs/,/oradbst/,/oradbfs/FAL_SERVERoradbLOG_ARCHIVE_CONFIGDG_CONFIG(oradb,oradbfs,oradbst)LOG_ARCHIVE_DEST_1LOCATION/u01/arch VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEoradbfsLOG_ARCHIVE_DEST_2SERVICEoradbst ASYNC VALID_FOR(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAMEoradbstLOG_ARCHIVE_DEST_STATE_2ENABLEstandby_file_managementAUTOservice_namesoradb3.6 创建监听和tnsnames.ora将备库和far sync实例创建好监听后一起写入主库的tnsnames.ora中并拷贝至每个节点–tns配置如下oradb(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.2.14)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEoradb)))oradbfs(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.2.12)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEoradb)))oradbst(DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST192.168.2.11)(PORT1521))(CONNECT_DATA(SERVERDEDICATED)(SERVICE_NAMEoradb)))–监听配置如下SID_LIST_LISTENER(SID_LIST(SID_DESC(GLOBAL_DBNAMEoradb)(ORACLE_HOME/u01/app/oracle/product/23.26.1/dbhome_1)(SID_NAMEoradb)))SID_LIST_LISTENER(SID_LIST(SID_DESC(GLOBAL_DBNAMEoradb)(ORACLE_HOME/u01/app/oracle/product/23.26.1/db_1)(SID_NAMEoradb)))3.7 启动far sync到mount模式export ORACLE_SIDoradbstartup nomount pfile‘/home/oracle/pfile.init’;rman target sys/oracleoradb auxiliary sys/oracleoradbfsduplicate target database for farsync from active database;4.备库搭建在standby数据库上创建pfile文件control_files/u01/app/oracle/oradata/oradbst/control01.ctlDB_UNIQUE_NAMEoradbstDB_FILE_NAME_CONVERT/ORADB/,/oradbst/LOG_FILE_NAME_CONVERT/ORADB/,/oradbst/FAL_SERVERoradbfs,oradLOG_ARCHIVE_CONFIGDG_CONFIG(oradb,oradbfs,oradbst)LOG_ARCHIVE_DEST_1LOCATION/u01/arch VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEoradbstLOG_ARCHIVE_DEST_2SERVICEoradbfs ASYNC VALID_FOR(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAMEoradbLOG_ARCHIVE_DEST_STATE_2ENABLEstandby_file_managementAUTOservice_namesoradb恢复备库数据库export ORACLE_SIDoradbstartup nomount pfile‘/home/oracle/pfile.init’;rman target sys/oracleoradb auxiliary sys/oracleoradbstduplicate target database for standby from active database dorecover nofilenamecheck;在备库启动Managed Recovery ProcessALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;在主库上查询日志应用情况set lines 300col dest_name for a20select dest_name,status,error from v$archive_dest;将备库启动到ADG模式SQL alter database recover managed standby database cancel;SQL alter database open;SQL alter pluggable database all open;SQL alter database recover managed standby database USING CURRENT LOGFILE disconnect from session;SQL select open_mode from v$database;