ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > DG Broker crete far_sync/prepare database

DG Broker crete far_sync/prepare database

原创 Oracle 作者：zjwmv 时间：2026-07-14 19:38:36 0 删除 编辑

##################################################

dg broker crete far_sync/prepare database 

crete far_sync 是Oracle 20c版本引入, 用于自动创建一个全新的 Far Sync 实例，和之前的 ADD FAR_SYNC（只负责将已存在的实例加进来） 相比，做了进一步的整合。PREPARE DATABASE FOR DATA GUARD 命令提供了一个便捷的途径，来自动完成将主库配置为 Data Guard 环境前的繁琐准备工作，该功能从 Oracle 21c 开始引入。

crete far_sync/prepare database 语法:

DGMGRL> help CREATE FAR_SYNC

Creates a far sync instance

Syntax:

  CREATE FAR_SYNC <db-unique-name>

    AS CONNECT IDENTIFIER IS <connect-identifier>

    [SPFILE

      [PARAMETER_VALUE_CONVERT <string-pair-values>]

      [SET <parameter-name> <value>]

      ...

      [SET <parameter-name> <value>]

      [RESET <parameter-name>]

      ...

      [RESET <parameter-name>]];


DGMGRL> help prepare

Prepare a primary database for a Data Guard environment.

Syntax:

  PREPARE DATABASE FOR DATA GUARD

    [WITH [DB_UNIQUE_NAME IS <db-unique-name>]

          [DB_RECOVERY_FILE_DEST IS <directory-location>]

          [DB_RECOVERY_FILE_DEST_SIZE IS <size>]

          [BROKER_CONFIG_FILE_1 IS <broker-config-file-1-location>]

          [BROKER_CONFIG_FILE_2 IS <broker-config-file-2-location>]

          [RESTART]]

    [SKIP [STANDBY_LOGS]];


概要信息如下:

主机名    db_unique   comments

racnode36 orcl36      主节点  

racnode37 orcl37      far sync节点

racnode38 orcl38      far sync 高可用节点

racnode39 orac39     std节点


OS: rls 8.9 

DB version:  26ai 23.26.1.0.0


##redo流转

orcl36 -> orcl37              -> oracl39

             -> orcl38 alternate


各节点基础环境仍然需要逐步准备的,详细参考 https://blog.itpub.net/15404498/viewspace-3175833/


简要步骤如下

环境准备:

1. 主节点racnode36:

1.1. racnode36 主节点 安装数据库软件 建库(略)

1.2. root用户配置/etc/hosts文件

cat <<EOF>> /etc/hosts  

192.168.11.36   racnode36

192.168.11.37   racnode37

192.168.11.38   racnode38

192.168.11.39   racnode39

EOF


1.3. 参数设置

alter system set db_recovery_file_dest='/u01/app/oracle/archive' scope=both;

alter system set db_recovery_file_dest_size=10G scope=both;

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/ORCL/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl36' scope=both;

alter system set standby_file_management='AUTO' scope=both;

alter system set DG_BROKER_START=TRUE scope=both; ##启动broker后台进程开关

alter system set log_file_name_convert='/oradata/ORCL/','/oradata/ORCL/' scope=both;

alter system set db_file_name_convert='/oradata/ORCL/','/oradata/ORCL/' scope=both;


1.4. 准备far sync需要的文件

1.4.1 准备far sync控制文件

ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/home/oracle/control01.ctl'; 

1.4.2 以pfile的方式导出db参数文件

create pfile='/home/oracle/init_far.ora' from spfile;

1.4.3 将 init_far.ora control32.ctl  密码文件以及listener.ora,tnsnames.ora文件复制到 其他节点(racnode37/38/39)

node=racnode37

##scp /etc/hosts root@${node}:/etc/hosts

scp $ORACLE_HOME/sqlplus/admin/glogin.sql ${node}:$ORACLE_HOME/sqlplus/admin/

scp ~/control01.ctl ~/init_far.ora ${node}:~

cd $ORACLE_HOME/network/admin

scp listener.ora tnsnames.ora ${node}:$ORACLE_HOME/network/admin


1.5.准备归档、FRA目录

#racnode36~39节点,均用root执行

mkdir -p /u01/app/oracle/archive /u01/app/oracle/archive/ORCL/ /oradata/ORCL/  

mkdir -p /u01/app/oracle/admin/orcl/adump  /u01/app/oracle/admin/orcl/adump/dpdump  /u01/app/oracle/admin/orcl/adump/pfile

chown -R oracle:oinstall /u01/app/oracle/archive /oradata/ /u01/app/oracle/admin/orcl

chmod -R 777 /u01/app/oracle/archive



2. 备节点racnode39:

2.1. 数据软件 直接解压

mount /dev/sda /soft

su - oracle -c "tar -zxf /soft/26ai_db.tgz -C /"


2.2. 调整参数、监听、tns文件

hname=$(hostname)

echo ${hname: -1}

sed -i -e "s/orcl36/orcl3${hname: -1}/g" -e "s/racnode36/racnode3${hname: -1}/g" $ORACLE_HOME/network/admin/listener.ora

sed -i 's/19.3.0/23.0.0/g' $ORACLE_HOME/network/admin/listener.ora

sed -i -e "s/racnode36/racnode3${hname: -1}/g" $ORACLE_HOME/network/admin/tnsnames.ora


echo "*.db_name='orcl'" >/home/oracle/init.ora


2.3. 启动数据库到nomount

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/home/oracle/init.ora'


2.4 duplicate standby database实例

rman<<_EOF

connect target sys/oracle@orcl36

connect auxiliary sys/oracle@orcl39

 run {

    allocate channel t1 type disk;

    allocate channel t2 type disk;

    allocate auxiliary channel a1 type disk;

    allocate auxiliary channel a2 type disk;

    duplicate target database for standby from active database nofilenamecheck

       dorecover

       spfile

       PARAMETER_VALUE_CONVERT 'ORCL36','ORCL39','orcl36','orcl39'

       set db_unique_name='orcl39'

       set log_archive_dest_1='LOCATION=/u01/app/oracle/archive/ORCL/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl39'

       set log_archive_dest_2=''

       set log_archive_dest_3=''

       set db_file_name_convert='/oradata/ORCL/','/oradata/ORCL/'

       set log_file_name_convert='/u01/app/oracle/archive/ORCL36','/u01/app/oracle/archive/ORCL39'

       set standby_file_management='MANUAL'

       ;

    sql channel a1 "alter system set standby_file_management='AUTO'";

     }

exit;

_EOF


上述搭建备库的时候 log_file_name_convert 有问题 standby logfile没正确转换过来导致异常


#增加standby日志

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 11 ('/oradata/ORCL/standby_redo11.log') SIZE 20M;

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 12 ('/oradata/ORCL/standby_redo12.log') SIZE 20M;

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 13 ('/oradata/ORCL/standby_redo13.log') SIZE 20M;

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 14 ('/oradata/ORCL/standby_redo14.log') SIZE 20M;




3. far sync节点racnode37/racnode38

3.1. 数据软件 直接解压

mount /dev/sda /soft

su - oracle -c "tar -zxf /soft/26ai_db.tgz -C /"


3.2. 调整参数及其

hname=$(hostname)

echo ${hname: -1}

sed -i -e "s/orcl36/orcl3${hname: -1}/g" ~/init_far.ora

sed -i -e "s/orcl36/orcl3${hname: -1}/g" -e "s/racnode36/racnode3${hname: -1}/g" $ORACLE_HOME/network/admin/listener.ora

sed -i 's/19.3.0/23.0.0/g' $ORACLE_HOME/network/admin/listener.ora

sed -i -e "s/racnode36/racnode3${hname: -1}/g" $ORACLE_HOME/network/admin/tnsnames.ora


3.3. 启动数据库到nomount

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/home/oracle/init_far.ora'



3.4. 为 Far Sync 实例配置 Wallet 认证

##在 orcl37 和 orcl38 所在的主机上执行以下操作。

3.4.1 创建 Wallet 目录并设置权限

mkdir -p $ORACLE_HOME/dbs/wallet

chmod 700 $ORACLE_HOME/dbs/wallet


3.4.2 创建 Auto-Login Wallet

mkstore -wrl $ORACLE_HOME/dbs/wallet -create

##命令执行后会提示你设置并确认一个 Wallet 密码，请务必记住。

##PKI-01002: 口令无效。口令至少要有 8 个字符并且应该同时包含字母和数字 (或特殊字符)。


3.4.3 将数据库凭证添加到 Wallet

mkstore -wrl $ORACLE_HOME/dbs/wallet -createCredential orcl37 sys

mkstore -wrl $ORACLE_HOME/dbs/wallet -createCredential orcl38 sys


## 添加主库的凭证

mkstore -wrl $ORACLE_HOME/dbs/wallet -createCredential orcl36 sys

mkstore -wrl $ORACLE_HOME/dbs/wallet -createCredential orcl39 sys


##查看已添加的凭证

mkstore -wrl $ORACLE_HOME/dbs/wallet -listCredential


3.4.4 修改 sqlnet.ora 文件

##编辑 $ORACLE_HOME/network/admin/sqlnet.ora 文件，添加或修改以下内容，告诉 Oracle 使用 Wallet 进行认证：

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/dbs/wallet)))

SQLNET.WALLET_OVERRIDE = TRUE

SQLNET.WALLET_OVERRIDE=TRUE 会强制使用 Wallet 中的凭证进行连接，而忽略其他认证方式。


3.4.5 验证 Wallet 认证

##配置完成后，在 Far Sync 主机上测试能否无密码登录主库和本地的 Far Sync 实例。

sqlplus /@orcl36 as sysdba

sqlplus /@orcl37 as sysdba

sqlplus /@orcl38 as sysdba

sqlplus /@orcl39 as sysdba

如果能够成功连接，说明 Wallet 认证配置正确。


3.5. far sync创建 由broker实现.


4. data guard broker 管理DG:

##4.1 登入

dgmgrl sys/oracle@orcl36 


##4.2 创建配置组及其主备节点加入

CREATE CONFIGURATION 'orcl_dgconf' AS PRIMARY DATABASE IS 'orcl36' CONNECT IDENTIFIER IS 'orcl36';

ADD DATABASE 'orcl39' AS CONNECT IDENTIFIER IS 'orcl39';


##4.3 主节点dg配置(设置归档、force logging、fra、Flashback Database等 )

validate  database "orcl36";

DGMGRL> validate  database "orcl36";

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:

    Database  Status           Retention Target

    orcl36    Off              1440


  Managed by Clusterware:

    orcl36:  NO

    The static connect identifier allows for a connection to database "orcl36".


DGMGRL>

PREPARE DATABASE FOR DATA GUARD

>    WITH DB_UNIQUE_NAME IS orcl36

>    DB_RECOVERY_FILE_DEST IS '/u01/app/oracle/archive'

>    DB_RECOVERY_FILE_DEST_SIZE IS 10G;

Validating database "orcl36" before executing the command.

  DGM-17552: Database must be restarted after setting static initialization parameters.

Failed.

调整为:

DGMGRL> PREPARE DATABASE FOR DATA GUARD

>    WITH DB_UNIQUE_NAME IS orcl36

>    DB_RECOVERY_FILE_DEST IS '/u01/app/oracle/archive'

>    DB_RECOVERY_FILE_DEST_SIZE IS 10G

>    RESTART;

Validating database "orcl36" before executing the command.

Preparing database "orcl36" for Data Guard.

Initialization parameter DB_FILES set to 1024.

Initialization parameter LOG_BUFFER set to 268435456.

Database must be restarted after setting static initialization parameters.

Shutting down database "orcl36".

Database closed.

Database dismounted.

ORACLE instance shut down.

Starting database "orcl36" to mounted mode.

ORACLE instance started.

Database mounted.

Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.

RMAN ARCHIVELOG deletion policy set to TO SHIPPED TO ALL STANDBY.

Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '10g'.

Initialization parameter DB_RECOVERY_FILE_DEST set to '/u01/app/oracle/archive'.

LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.

Initialization parameter STANDBY_FILE_MANAGEMENT set to 'MANUAL'.

Standby log group 14 will be dropped because it was not configured correctly.

Standby log group 13 will be dropped because it was not configured correctly.

Standby log group 12 will be dropped because it was not configured correctly.

Standby log group 11 will be dropped because it was not configured correctly.

Adding standby log group size 1073741824 and assigning it to thread 1.

Adding standby log group size 1073741824 and assigning it to thread 1.

Adding standby log group size 1073741824 and assigning it to thread 1.

Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.

Force logging mode enabled.

Flashback Database enabled.

Database opened.

Succeeded.


##4.4 创建far sync实例 并激活enable

DGMGRL> CREATE FAR_SYNC orcl37 AS CONNECT IDENTIFIER IS 'orcl37';

Creating far sync instance "orcl37".

Connected to "orcl36"

Connected to "orcl37"

far sync instance "orcl37" created

far sync instance "orcl37" added


CREATE FAR_SYNC orcl38 AS CONNECT IDENTIFIER IS 'orcl38';

DGMGRL> CREATE FAR_SYNC orcl38 AS CONNECT IDENTIFIER IS 'orcl38';

Creating far sync instance "orcl38".

Connected to "orcl36"

Connected to "orcl38"

far sync instance "orcl38" created

far sync instance "orcl38" added


DGMGRL> ENABLE FAR_SYNC orcl37;

Enabled.

DGMGRL> ENABLE FAR_SYNC orcl38;

Enabled.

DGMGRL>


##4.5 dg集群状态

DGMGRL> show configuration


Configuration - orcl_dgconf


  Protection Mode: MaxPerformance

  Members:

  orcl36 - Primary database

    orcl39 - Physical standby database

      Warning: ORA-16809: Multiple warnings detected for the member.


    orcl37 - Far sync instance (disabled)

      ORA-16905: The member was not enabled.


    orcl38 - Far sync instance (disabled)

      ORA-16905: The member was not enabled.


Fast-Start Failover:  Disabled


Configuration Status:

WARNING   (status updated 19 seconds ago)


DGMGRL>


##4.6 配置redo流转

#通过 EDIT DATABASE 和 EDIT FAR_SYNC 命令，配置 RedoRoutes 属性，精确控制 Redo 的传输路径。

#考虑到切换

#实时级联dg,far sync两个节点高可用

EDIT DATABASE 'orcl36' SET PROPERTY RedoRoutes='(LOCAL : (orcl37 PRIORITY=1, orcl38 PRIORITY=1))';

EDIT DATABASE 'orcl39' SET PROPERTY RedoRoutes='(LOCAL : (orcl37 PRIORITY=1, orcl38 PRIORITY=1))';

EDIT FAR_SYNC 'orcl37' SET PROPERTY RedoRoutes='(orcl36 : orcl39 ASYNC)(orcl39 : orcl36 ASYNC)';

EDIT FAR_SYNC 'orcl38' SET PROPERTY RedoRoutes='(orcl36 : orcl39 ASYNC)(orcl39 : orcl36 ASYNC)';

EDIT FAR_SYNC 'orcl37' SET PROPERTY 'LogXptMode'='SYNC';

EDIT FAR_SYNC 'orcl38' SET PROPERTY 'LogXptMode'='SYNC';

EDIT DATABASE 'orcl39' SET PROPERTY LogXptMode='ASYNC';

EDIT DATABASE 'orcl36' SET PROPERTY LogXptMode='ASYNC';


##查看节点信息

show DATABASE verbose orcl36

show DATABASE verbose orcl39

show FAR_SYNC verbose orcl38

##切换保护模式可选

##EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;

##EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;


#最终dg集群结构:

DGMGRL> show configuration;


Configuration - orcl_dgconf


  Protection Mode: MaxPerformance

  Members:

  orcl36 - Primary database

    orcl37 - Far sync instance

      orcl39 - Physical standby database


  Members Not Receiving Redo:

  orcl38 - Far sync instance (alternate of orcl37)


Fast-Start Failover:  Disabled


Configuration Status:

SUCCESS   (status updated 13 seconds ago)


DGMGRL>


##4.6 遭遇STANDBY LOGFILE与REDO LOGFILE大小不一致引起的applying问题

--遭遇一个异常 疏忽导致 STANDBY LOGFILE 大小与 主库redo大小不一致

主库redo 1g  , far sync、standby 的STANDBY LOGFILE 都是20M


dg集群结构 一直warning ，提示备端orcl39 warning 异常

orcl39：

Warning: ORA-16809: Multiple warnings detected for the member.


show database verbose orcl39;  

看到下述warning 

Database Warning(s):

    ORA-16853: apply lag has exceeded specified threshold

    ORA-16855: transport lag has exceeded specified threshold

    ORA-16857: member disconnected from redo source for longer than specified threshold


数据同步一直滞后,平时不更新,随归档切换更新，MRP0进程状态为WAIT_FOR_LOG也证明是not real-time applying

SYS@orcl> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS = 'MRP0';

PROCESS                     STATUS

--------------------------- ------------------------------------------------------------

MRP0                        WAIT_FOR_LOG


借鉴文档 https://www.howtosop.com/recreate-standby-logfiles-how-and-why/ 

最终发现是STANDBY LOGFILE 与主库REDO LOGFILE大小不一致。整个处理一遍redo流恢复。


通过以上搭建过程，可知broker新增create sync ,prepare database 极大简化了dg搭建, 工具高度集成,在问题排查的时候还是需要按照原始步骤一步一步排查,如果是做排除法.......


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15404498/viewspace-3175950/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Duplicate for Standby fails with RMAN-05535
下一篇： oracle 26ai rac silent 安装
请登录后发表评论 登录
全部评论
zjwmv

注册时间：2015-03-05

  • 博文量
    36
  • 访问量
    26766

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593