19c DG broke搭建


参考:
http://blog.data-alchemy.org/posts/oracle-dataguard-broker/
https://oracledbwr.com/oracle-cloud-deploying-oracle-19c-data-guard-physical-standby/
https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/examples-using-data-guard-broker-DGMGRL-utility.html#GUID-D9018A5C-8C7A-4F6C-A7D3-B14E5AF7D4BC

环境细节

ENV Detail Primary Standby
DB Unique orcl orcl_stdy
DB Name orcl orcl
hostname:Server IP db1:192.168.32.172 db2:192.168.32.173

hosts已经配置好

主库配置

1.开启归档和附加日志

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;



alter database force logging;
-- Make sure at least one logfile is present.
alter system switch logfile;



SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING LOG_MODE
————————————— ————
YES                    ARCHIVELOG

2.添加备库日志

Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.

在主数据库上创建备用重做日志(在switchovers的情况下)。备用重做日志应该至少与最大的在线重做日志一样大,并且与在线重做日志相比,每个线程应该有一个额外的组

备用的redo日志大小和redo大小一样,每个thread对应一个standbylogfile,oracle建议主库多一个

个人测试,备用redo组的大小和主库比不能一个大一个小,可能会导致备库间歇性的同步慢

#查询日志组数
select thread#,group#,members,bytes/1024/1024/1024 from v$log order by thread#;
select thread#,group#,bytes/1024/1024/1024 from v$standby_log;
SELECT group#, members, bytes/1024/1024, status FROM v$log;


#如果使用OMF
alter database add standby logfile thread 1 group 10 size 200m;
alter database add standby logfile thread 1 group 11 size 200m;
alter database add standby logfile thread 1 group 12 size 200m;
alter database add standby logfile thread 1 group 13 size 200m;


#如果未使用OMF
alter database add standby logfile thread 1 group 10 ('/u01/oradata/cdb1/standby_redo01.log') size 200m;
alter database add standby logfile thread 1 group 11 ('/u01/oradata/cdb1/standby_redo02.log') size 200m;
alter database add standby logfile thread 1 group 12 ('/u01/oradata/cdb1/standby_redo03.log') size 200m;
alter database add standby logfile thread 1 group 13 ('/u01/oradata/cdb1/standby_redo04.log') size 200m;

#如果是RAC,则对于的两个thread都要添加

3.如果使用闪回

4.检测参数配置

dbname和db_unique

备库的dbname和主库可以相同,但db_unique_name不同

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcl

ALTER SYSTEM SET log_archive_config='dg_config=(orcl,orcl_stdy)' SCOPE=both;
ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcl' SCOPE=both;
ALTER SYSTEM SET log_archive_dest_2='service=orcl_stdy async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_stdy' SCOPE=both;
alter system set log_archive_dest_state_1=enable; 
alter system set log_archive_dest_state_2=enable; 

#默认alter system set log_archive_max_processes=4; 
#默认alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

ALTER SYSTEM SET fal_server='orcl_stdy' SCOPE=both;
ALTER SYSTEM SET fal_client='orcl' SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;





#备库可以先改,也可以配置完成后再改
#alter system set db_unique_name='orcl_stdy' scope=spfile; 
alter system set log_archive_config='DG_CONFIG=(orcl_stdy,orcl,)'; 
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=orcl_stdy valid_for=(ALL_LOGFILES,ALL_ROLES)' scope=spfile sid='*'; 
alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC db_unique_name=orcl valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; 
alter system set log_archive_dest_state_1=enable; 
alter system set log_archive_dest_state_2=enable; 
ALTER SYSTEM SET fal_server='orcl';
ALTER SYSTEM SET fal_client='orcl_stdy';
ALTER SYSTEM SET log_archive_config='dg_config=(orcl_stdy,orcl)' SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;

##如果需要路径转换
SQL> alter system set db_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;
SQL> alter system set log_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;

###
#*.db_unique_name='orclstd'
#*.fal_client='orclstd'
#*.fal_server='orcl'
#*.log_archive_config='DG_CONFIG=(orclstd,orcl)'
#*.log_archive_dest_1和2中的db_unique_name顺序

tns配置,两台一样

添加:

orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

orcl_stdy =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_stdy)
    )
  )

listener.ora

添加

实际环境中注意sid_name

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3/dbhome_1)
      (SID_NAME = orcl)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = orcl_stdy)
      (ORACLE_HOME = /u01/app/oracle/product/19.3/dbhome_1)
      (SID_NAME = orcl)
    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

备库

拷贝密码文件,或者重建

orapwd file=orapwdb19c password=orac#123 entries=10

参数文件,rman duplicate搭建备库

#可以主库上导出,修改相关参数后给备库使用,
create pfile='/home/oracle/pfile.bak' from spfile;
startup nomount pfile='/u01/app/oracle/product/19.3/dbhome_1/dbs/initorcl.ora';
#我这里直接使用自己的,在配置dg_broke时会自动修改备库参数



#登录测试
[oracle@db2 ~]$ rman target sys/Oracle123@orcl auxiliary sys/Oracle123@orcl_stdy

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 3 09:27:17 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1716960432)
connected to auxiliary database: ORCL (not mounted)

RMAN>
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stdy' COMMENT 'Is standby'
  nofilenamecheck;

参考:

#手动修改db_unique_name='orcl_stdy'后,使用:
duplicate target database for standby nofilenamecheck from active database;

#(上门和下面这种会将数据路径默认转换到oradata下的ORCL_STDY)
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stdy' COMMENT 'Is standby'
  nofilenamecheck;

#手动尝试多次后,发现/u01/app/oracle/oradata下面生成的数据文件目录名是db_unique_name的值
#暂且记录,后续在研究

#如果需要转换文件位置,提前在参数文件设置,或者复制时设置(log_file_name_convert不设置,日志默认是在$ORACLE_HOME/dbs下)
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stdy' COMMENT 'Is standby'
    set db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'
    set log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/'


参考:

#备库至少在nomount
#rman target sys/Oracle123@orcl auxiliary sys/Oracle123@orcl_stdy
#或者
#rman target sys/Oracle123@orcl auxiliary /


#或者参数文件修改后
#rman target sys/Oracle123@orcl auxiliary sys/Oracle123@orcl_stdy
#duplicate target database for standby nofilenamecheck from active database;


set项参数注意配置
run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'db19c','db19cstby'
set db_name='db19c'
set db_unique_name='db19cstby'
set db_file_name_convert='/u01/app/oracle/oradata/DB19C','/u01/app/oracle/oradata/DB19CSTBY'
set log_file_name_convert='/u01/app/oracle/oradata/DB19C','/u01/app/oracle/oradata/DB19CSTBY'
set control_files='/u01/app/oracle/oradata/DB19CSTBY/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='db19cstby'
set fal_server='db19c'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(db19c,db19cstby)'
set compatible='19.0.0.0.0'
set memory_target='6420m'
nofilenamecheck;
}

#parameter_value_convert 'orcl','orcl_stdy'

run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
set db_name='orcl'
set db_unique_name='orcl_stdy'
set fal_client='orcl_stdy'
set fal_server='orcl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(orcl,orcl_stdy)'
set compatible='19.0.0.0.0'
nofilenamecheck;
}


FOR STANDBY:这告诉DUPLICATE命令将用于备用,因此它不会强制更改DBID。
FROM ACTIVE DATABASE:副本将直接从源数据文件创建,不需要额外的备份步骤。
DORECOVER:副本将包括恢复步骤,将备用数据恢复到当前时间点。
SPFILE:允许我们在从源服务器复制SPFILE时重置SPFILE中的值。
NOFILENAMECHECK:不检查目标文件位置。

配置BREOK

上面已完成后已经有了主备库,现在使用Data Guard Broker来管理

配置DGMGRL的监听用于自启

DataGuard Broker 需要一个静态服务才能重新启动实例,因此我们必须在两个服务器listener.ora中添加一个名db_unique_name_DGMGRL的service

listener.ora添加:
主:
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=orcl)
     (GLOBAL_DBNAME=orcl_DGMGRL)
     (ORACLE_HOME=/u01/app/oracle/product/19.3/dbhome_1)
     (ENVS="TNS_ADMIN=/u01/app/oracle/product/19.3/dbhome_1/network/admin")))

备:
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=orcl)
     (GLOBAL_DBNAME=orcl_stdy_DGMGRL)
     (ORACLE_HOME=/u01/app/oracle/product/19.3/dbhome_1)
     (ENVS="TNS_ADMIN=/u01/app/oracle/product/19.3/dbhome_1/network/admin")))

---------------------------------------
tnsnames.ora添加:
主:
orcl_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_DGMGRL)
    )
  )


备:
orcl_stdy_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_stdy_DGMGRL)
    )
  )
---------------------------------------------------------
dgbroke检测一下
[oracle@db1 ~]$ dgmgrl sys/Oracle123
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jan 13 15:56:12 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR ALL
Oracle Clusterware is not configured on database "orcl".
Connecting to database "orcl" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "orcl".

Oracle Clusterware is not configured on database "orcl_stdy".
Connecting to database "orcl_stdy" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stdy_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "orcl_stdy".



启用Data Guard Broker

#主备都启用

alter system set dg_broker_start=true;

主库上注册

[oracle@db1 ~]$ dgmgrl sys/Oracle123@orcl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Jan 3 11:25:01 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.

#注册
#create configuration my_dg_config as primary database is orcl connect identifier is orcl;

DGMGRL> create configuration my_dg_config as primary database is orcl connect identifier is orcl;
Configuration "my_dg_config" created with primary database "orcl"


添加备库

add database orcl_stdy as connect identifier is orcl_stdy;


DGMGRL> add database orcl_stdy as connect identifier is orcl_stdy;
Database "orcl_stdy" added

启用

enable configuration;

DGMGRL> enable configuration;
Enabled.
##查看配置
DGMGRL> show configuration;

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl      - Primary database
    orcl_stdy - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 32 seconds ago)

#查看数据库
DGMGRL> show database orcl

Database - orcl

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
SUCCESS
#查看备库
DGMGRL> show database orcl_stdy

Database - orcl_stdy

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    orcl

Database Status:
SUCCESS





#ORA-16778: redo transport error for one or more members可以尝试切一下日志

检查状态

Primary Side:-

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN          chennai              PRIMARY                  MAXIMUM AVAILABILITY

Standby Side:-

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN   delhi  PHYSICAL           STANDBY                 MAXIMUM AVAILABILITY

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY

switchover和failover

后面在补充

补充

DG BROKE登录

#必要时使用密码登录,否则某些验证操作将报错
[oracle@db1 ~]$ dgmgrl sys/Oracle123
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jan 13 15:16:23 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.
DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR ORCL
Oracle Clusterware is not configured on database "orcl".
Connecting to database "orcl" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "orcl".

备库上启动停止MRP

-- Stop managed recovery.
alter database recover managed standby database cancel;

-- Start managed recovery.
alter database recover managed standby database disconnect;

启停

dg broke下启动:
两边先启动监听,主库startup,备库startup,dg broke会自动恢复

DG配置文件

#dg_broker_config_file可以在开始手动设置
SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.3/d
                                                 bhome_1/dbs/dr1orcl.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3/d
                                                 bhome_1/dbs/dr2orcl.dat
dg_broker_start                      boolean     TRUE
SQL>

DG进程

#会在mount后启动,DMON进程
[oracle@db1 ~]$ ps -ef | grep dmon
oracle    2017     1  0 09:48 ?        00:00:00 ora_dmon_orcl
oracle    9694  1752  0 10:08 pts/1    00:00:00 grep --color=auto dmon
[oracle@db2 ~]$ ps -ef | grep dmon
oracle    2325     1  0 09:49 ?        00:00:00 ora_dmon_orcl
oracle    3733  1981  0 10:08 pts/0    00:00:00 grep --color=auto dmon



ALTER DATABASE   MOUNT
2025-01-13T09:48:55.497516+08:00
Using default pga_aggregate_limit of 2048 MB
2025-01-13T09:48:57.232568+08:00
.... (PID:2030): Redo network throttle feature is disabled at mount time
2025-01-13T09:48:57.246339+08:00
Successful mount of redo thread 1, with mount id 1718089349
2025-01-13T09:48:57.246961+08:00
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:2030): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]
Completed: ALTER DATABASE   MOUNT
2025-01-13T09:48:57.335477+08:00
ALTER DATABASE OPEN
Data Guard Broker initializing...
2025-01-13T09:49:01.511182+08:00
Starting Data Guard Broker (DMON)
Starting background process INSV
2025-01-13T09:49:01.536098+08:00
INSV started with pid=38, OS id=2065
2025-01-13T09:49:04.658757+08:00
Starting background process NSV2
2025-01-13T09:49:04.683831+08:00
NSV2 started with pid=39, OS id=2088
2025-01-13T09:49:05.543795+08:00
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
2025-01-13T09:49:08.052335+08:00
Starting background process RSM0
2025-01-13T09:49:08.076495+08:00
RSM0 started with pid=40, OS id=2116
2025-01-13T09:49:08.614298+08:00
Data Guard: broker startup completed
Data Guard: primary database controlfile verified
Ping without log force is disabled:
  instance mounted in exclusive mode.

posted @   EverEternity  阅读(19)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示