Oracle 教你用DG Broker快速搭建DataGuard

	主机	SID	DB_UNIQUE_NAME
主库	host01	PROD5H1	PROD5H1
备库	host02	PROD5H2	PROD5H2

DG Broker简介

DataGuard Broker能更加容易的管理和维护多个Standby Database。如果有多个备库或者RAC数据库中有个多个实例，如果进行手工配置管理，就会太过于麻烦，Oracle提供了DataGuard Broker工具，可以在一台服务器上对所有数据库进行统一的配置和管理，这些配置会自动同步到各个数据库中。Failover和Switchover可以在DGMGRL工具中是用一条命令切换，大大简化了我们管理的过程。

一.主库环境准备

1.设置环境变量

[oracle@host01 ~]$ export ORACLE_SID=PROD5
[oracle@host01 ~]$ env | grep ORA
ORACLE_SID=PROD5
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/

2.修改主库参数

修改db_unique_name

alter system set db_unique_name=PROD5H1 scope=spfile;

修改参数

alter system set standby_file_management=auto;
alter system set dg_broker_start=true;
alter system set temp_undo_enabled=true;
alter system set local_listener='';
alter system set db_domain='example.com' scope=spfile;

3.添加SRL日志文件

先查看原来的redo log数量和大小，SRL的数量需要大于等于redo log 的数量，大小设置与redo log相同。

col member for a45
select group#, status, type, member from v$logfile;

按情况添加SRL

alter database add standby logfile group 4
'/u01/app/oracle/oradata/PROD5/redo04.log' size 200M;
alter database add standby logfile group 5
'/u01/app/oracle/oradata/PROD5/redo05.log' size 200M;
alter database add standby logfile group 6
'/u01/app/oracle/oradata/PROD5/redo06.log' size 200M;
alter database add standby logfile group 7
'/u01/app/oracle/oradata/PROD5/redo07.log' size 200M;

4.设置归档路径db_recovery_file_dest_size

alter system set db_recovery_file_dest_size=12G;
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

5.主库重启，开启闪回，开启归档，强记日志

shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;

alter database force logging;
alter database open;
select log_mode,flashback_on,force_logging from v$database;

create pfile from spfile;

6.把参数文件传到备库

主库参数文件如下：

PROD5.__data_transfer_cache_size=0
PROD5.__db_cache_size=838860800
PROD5.__inmemory_ext_roarea=0
PROD5.__inmemory_ext_rwarea=0
PROD5.__java_pool_size=0
PROD5.__large_pool_size=16777216
PROD5.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD5.__pga_aggregate_target=419430400
PROD5.__sga_target=1241513984
PROD5.__shared_io_pool_size=67108864
PROD5.__shared_pool_size=301989888
PROD5.__streams_pool_size=0
PROD5.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD5/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/PROD5/control01.ctl','/u01/app/oracle/oradata/PROD5/control02.ctl'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='PROD5'
*.db_recovery_file_dest_size=12884901888
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_unique_name='PROD5H1'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD5XDB)'
*.local_listener=''
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=393m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1176m
*.standby_file_management='AUTO'
*.temp_undo_enabled=TRUE
*.undo_tablespace='UNDOTBS1'
 

二.备库环境准备

1.把参数文件cp到dbs目录下

cp initPROD5.ora $ORACLE_HOME/dbs

2.修改参数文件中的db_unique_name

PROD5.__data_transfer_cache_size=0
PROD5.__db_cache_size=838860800
PROD5.__inmemory_ext_roarea=0
PROD5.__inmemory_ext_rwarea=0
PROD5.__java_pool_size=0
PROD5.__large_pool_size=16777216
PROD5.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD5.__pga_aggregate_target=419430400
PROD5.__sga_target=1241513984
PROD5.__shared_io_pool_size=67108864
PROD5.__shared_pool_size=301989888
PROD5.__streams_pool_size=0
PROD5.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD5/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/PROD5/control01.ctl','/u01/app/oracle/oradata/PROD5/control02.ctl'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='PROD5'
*.db_recovery_file_dest_size=12884901888
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_unique_name='PROD5H2'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD5XDB)'
*.local_listener=''
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=393m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1176m
*.standby_file_management='AUTO'
*.temp_undo_enabled=TRUE
*.undo_tablespace='UNDOTBS1'
 

3.创建所需要的目录

mkdir -p /u01/app/oracle/admin/PROD5/adump
mkdir -p /u01/app/oracle/oradata/PROD5/
mkdir -p /u01/app/oracle/fast_recovery_area

4.备库创建密码文件

orapwd file=orapwPROD5 password=oracle format=12

​​​​​5.备库启动到nomount状态，创建spfile

此时没有控制文件和数据文件，报错是正常现象

三.主备库配置静态监听

1.编辑主库listener.ora

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod5h1_dgmgrl.example.com)
          (SID_NAME=PROD5)
          (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/)
       )
   )

2.编辑备库listener.ora

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=prod5h2_dgmgrl.example.com)
          (SID_NAME=PROD5)
          (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/)
       )
   )

3.编辑主库 tnsnames.ora

PROD5H1_DGMGRL.EXAMPLE.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod5h1_dgmgrl.example.com)
    )
  )

PROD5H2_DGMGRL.EXAMPLE.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod5h2_dgmgrl.example.com)
    )
  )

4.编辑备库tnsnames.ora

PROD5H1_DGMGRL.EXAMPLE.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod5h1_dgmgrl.example.com)
    )
  )

PROD5H2_DGMGRL.EXAMPLE.COM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod5h2_dgmgrl.example.com)
    )
  )

测试监听启动后是否可用

主库：

sqlplus sys/oracle@prod5h1_dgmgrl.example.com as sysdba

sqlplus sys/oracle@prod5h2_dgmgrl.example.com as sysdba

备库：

sqlplus sys/oracle@prod5h1_dgmgrl.example.com as sysdba

sqlplus sys/oracle@prod5h2_dgmgrl.example.com as sysdba

三.rman duplicate 搭建物理备库

rman target sys/oracle@PROD5H1_DGMGRL.example.com auxiliary sys/oracle@PROD5H2_DGMGRL.example.com

duplicate target database for standby from active database dorecover nofilenamecheck;

四.创建broker配置

dgmgrl

connect sys/oracle@prod5h1_dgmgrl.example.com

1.创建主备库配置

CREATE CONFIGURATION OCM AS  PRIMARY DATABASE IS PROD5H1 CONNECT IDENTIFIER IS PROD5H1_dgmgrl.example.com;

add DATABASE PROD5H2 AS CONNECT IDENTIFIER IS PROD5H2_dgmgrl.example.com;

2.启用配置  

enable configuration;

3.查看配置

这个状态就是还没同步过来之前的数据，需要等一段时间。

一段时间过后备库显示SUCCESS 

五.测试主备库数据同步

查看备库test表数据