04.Deploying 11G RAC to standalone ADG using DG_broker

原创 ByteHouse 2024-06-18

1.配置 dg_broker

IP	hostname	db_unique_name	DG Broker role	switchover role	failover role
192.168.10.10	sljj-01	sljj	主库	从库	从库
192.168.10.20	sljj-02	sljj	主库	从库	从库
192.168.10.30	jcpt-database	sljj2dg	从库	主库	从库

备库存储设置

[root@jcpt-osb ~]# pvs
  PV         VG          Fmt  Attr PSize   PFree
  /dev/sda   oracle      lvm2 a--u  21.83t 1.83t
  /dev/sdb2  VolGroup_OS lvm2 a--u 557.91g    0 
[root@jcpt-osb ~]# vgs
  VG          #PV #LV #SN Attr   VSize   VFree
  VolGroup_OS   1   3   0 wz--n- 557.91g    0 
  oracle        1   2   0 wz--n-  21.83t 1.83t
[root@jcpt-osb ~]# lvs
  LV          VG          Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  LogVol02    VolGroup_OS -wi-ao---- 349.91g                                                    
  LogVol_root VolGroup_OS -wi-ao---- 200.00g                                                    
  LogVol_swap VolGroup_OS -wi-ao----   8.00g                                                    
  archivelog  oracle      -wi-ao----   5.00t                                                    
  oradata     oracle      -wi-ao----  15.00t                                                    
[root@jcpt-osb ~]#

挂载点信息：

[root@jcpt-osb ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup_OS-LogVol_root
                      197G   11G  176G   6% /
tmpfs                  63G  8.0K   63G   1% /dev/shm
/dev/sdb1             477M   75M  373M  17% /boot
/dev/mapper/VolGroup_OS-LogVol02
                      345G  4.5G  323G   2% /u01
/root/Oracle Linux-R6-U10-Server-x86.iso
                      3.8G  3.8G     0 100% /mnt
/dev/mapper/oracle-oradata
                       15T  6.3M   15T   1% /u01/app/oracle/oradata
/dev/mapper/oracle-archivelog
                      5.0T   61M  4.8T   1% /u01/app/oracle/archivelog
[root@jcpt-osb ~]#

使用以下方法可以添加多个备库，具体配置的差异由你来确定

2.安装oracle数据库软件

略…

3.主数据库配置

3.1.设置数据库归档

查看数据库是否运行在归档模式：

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     12889
Current log sequence           12890
SQL>

数据库已经开启归档，下面的操纵可以忽略。

如上所示未开启归档，可按下面方法开启数据库归档

RAC模式下用常规方式关闭归档的时候，报错：ORA-01126: database must be mounted EXCLUSIVE，需要用一下步骤进行处理：
当想要多个实例同时mount数据库，CLUSTER_DATABASE参数必须是TRUE;当CLUSTER_DATABASE参数为FALSE时，同时只能有一个实例能mount数据库。

# 设置数据库为非集群模式（仅节点1）
SQL> show parameter cluster
SQL> alter system set cluster_database=false scope=spfile;

# 关闭所有节点数据库实例
srvctl stop database -d sljj

# 仅在节点1操作
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

# 查看归档状态
SQL> archive log list;

# 设置数据库为集群模式（仅节点1）
SQL> alter system set cluster_database=true scope=spfile;
sql> shutdown immediate

# 此前是在节点1操作,节点2并没有开启归档,选择打开集群模式后,重新数据库,节点二也就打开了归档．
# 打开所有节点数据库实例
srvctl start database -d sljj

设置归档路径

SQL> alter system set  log_archive_dest_1='location=+FRA' sid='*';

系统已更改。

SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            D:\app\Administrator\archivelog
最早的联机日志序列     18
下一个存档日志序列   20
当前日志序列           20
SQL>

调整归档日志文件命名方式

sql>
alter system set log_archive_format ='arch_%s_%R_%T.arc' scope=spfile;

3.2.设置数据库闪回

验证是否开启闪回

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------
NO

SQL>
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 440700M
SQL>

数据库已经开启flashback，那么下面步骤可忽略。

如上显示，该数据库未开启flashback，可按下面方法开启。

a. 配置 db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='+FRA';

System altered.

SQL> show parameter db_recovery_file_dest

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest             string            D：\app\Administrator\fast_recovery_area
db_recovery_file_dest_size         big integer        12732M
SQL>

b. 开启闪回

SQL> alter database flashback on;

数据库已更改。

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------
YES

SQL>

3.3.设置数据库强制归档

验证是否开启focelogging

SQL> select force_logging from v$database;

FORCE_LOGGING
------------------------------------------------------------------------------
NO

SQL>

数据库已经开启force logging，那么下面步骤可忽略。

如上可以看出数据库未开启，则按下面步骤执行：

SQL> alter database force logging;

数据库已更改。

SQL>
SQL> select force_logging from v$database;

FORCE_LOGGING
------------------------------------------------------------------------------
YES

SQL>

3.4.添加 STANDBY 日志文件

通过下面语句可以查询主库在线日志的大小和组数：

SQL> select thread#,group#,members,bytes/1024/1024 from v$log order by thread#;

   THREAD#     GROUP#    MEMBERS BYTES/1024/1024/1024
---------- ---------- ---------- --------------------
         1          1          2                   512
         1          2          2                   512
         2          3          2                   512
         2          4          2                   512

SQL>
SQL> select GROUP#, STATUS, MEMBER from v$logfile;

    GROUP# MEMBER
------------------------------------------------------------------------------------------
         2 +DATA/sljj/onlinelog/group_2.434.1163948771
         2 +FRA/sljj/onlinelog/group_2.271.1163948773
         1 +DATA/sljj/onlinelog/group_1.433.1163948767
         1 +FRA/sljj/onlinelog/group_1.270.1163948771
         3 +DATA/sljj/onlinelog/group_3.437.1163948841
         3 +FRA/sljj/onlinelog/group_3.272.1163948843
         4 +DATA/sljj/onlinelog/group_4.438.1163948843
         4 +FRA/sljj/onlinelog/group_4.273.1163948847

8 rows selected.

SQL>

通过下面的语句可以查询备库Standby日志的大小和组数：

SQL> select thread#,group#,bytes/1024/1024/1024 from v$standby_log;

no rows selected

SQL>

Note：这里我们查出来主库的redo日志组数为4组，所以我们standby log创建5组；因为官方建议是比主库多一组，没有硬性要求。

创建standby logfile

alter database add standby logfile thread 1 group 11 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 1 group 12 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 1 group 13 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 1 group 14 ('+REDO','+FRA') size 512m;

alter database add standby logfile thread 2 group 21 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 2 group 22 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 2 group 23 ('+REDO','+FRA') size 512m;
alter database add standby logfile thread 2 group 24 ('+REDO','+FRA') size 512m;

Note：注意thread 数要跟主库对应上。

确认 standby redolog file

SQL> select thread#,group#,bytes/1024/1024 from v$standby_log;

   THREAD#     GROUP# BYTES/1024/1024
---------- ---------- ---------------
         1         11             512
         1         12             512
         1         13             512
         1         14             512
         2         21             512
         2         22             512
         2         23             512
         2         24             512

8 rows selected.

SQL>

3.5.主库参数配置

step 1.修改 db_unique_name

alter system set db_unique_name='sljj' scope=spfile;

step 2.LOG_ARCHIVE_CONFIG

SQL> alter system set log_archive_config='DG_CONFIG=(sljj,sljj2dg)';

System altered.

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
sljj
sljj2dg

SQL>

LOG_ARCHIVE_CONFIG：它为Data Guard提供安全性检查：两个数据库之间的连接是允许的。
sljj，sljj2dg 分别是主库和备库的DB_UNIQUE_NAME，它表示两个数据库之间可以进行归档传输。

初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志，并指定Data Guard配置的惟一数据库名，默认值为SEND，RECEIVE，NODG_CONFIG。

当设置该参数为SEND时，会激活发送归档日志到远程位置；
当设置该能数为NOSEND时，会禁止发送归档日志到远程位置；
当设置该参数为RECEIVE时，会激活接收远程归档日志；
当设置该参数为NORECEIVE时，会禁止接收远程归档日志；
当设置该参数为DG_CONFIG时，可以最多指定9个惟一数据库名；
当设置该参数为NODG_CONFIG时，会禁止指定惟一数据库名。

step 3.启用自动备用文件管理

alter system set standby_file_management='AUTO';

启用自动备用文件管理后，主数据库上的操作系统文件添加和删除操作将复制到备用数据库上。STANDBY_FILE_MANAGEMENT仅适用于物理备用数据库。

step 4.文件转换

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/sljj','+data/sljj/datafile', '/u01/app/oracle/oradata/sljj','+IMAGE/sljj/datafile' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/sljj','+REDO/sljj/onlinelog', '/u01/app/oracle/oradata/sljj','+FRA/sljj/onlinelog' scope=spfile;

System altered.

SQL>

DB_FILE_NAME_CONVERT：在备用数据库上，该参数允许在逻辑上将数据文件从主数据库位置移到备用数据库位置。如果两个系统的磁盘结构和布局不同，该操作是必需的。只有备用数据库成为主数据库后，才运行执行该转换。一旦切换到备用数据库或在发生故障后转移到备用数据库，就会将这些值强制写入控制文件和数据文件头。
LOG_FILE_NAME_CONVERT：与DB_FILE_NAME_CONVERT的功能相同。

step 5.FAL_SERVER + FAL_CLIENT
FAL_SERVER：如果当物理备用遇到重做间隔时无法连接到主数据库。便会询问其他某个备用数据库，为此，将FAL_SERVER参数定义为存在备用服务器的TNS名称列表。

alter system set fal_server='sljj';

FAL_CLIENT：在备用数据库standby，我们传递名称standby作为客户端名称，这样primary可以反向链接standby并发送缺少的归档文件。

alter system set fal_client='sljj2dg';

必须在FAL服务器的TNS名称文件中定义standby，以便Data Guard可以连接到此备用数据库。
FAL_SERVER与FAL_CLIENT仅对物理备库有效。

step 6.重新启动数据库，可以先不重启，因当前角色为主库。

lsnrctl reload

3.6.修改监听配置文件

Oracle RAC 监听器的配置与单实例稍有不同,但原理和实现方法基本上是相同的。在Oracle中 tns进程用于为指定网络地址上的一个或多个Oracle 实例提供服务注册，并响应来自客户端对该服务提出的连接请求。一旦连接请求到达，并派生出一个服务器进程建立服务器与用户端之间的连接(专有服务器dedicated server)或转发服务请求(共享服务器模式shared server)。如果监听器知道多于一个实例提供所请求的服务，则可能会根据客户端与服务器端相关配置将请求定位到较低负载的实例为其提供服务。因此合理正确配置监听器以及tnsnames是Oracle RAC实现负载均衡以及failover的前提。

oracle rac listener.ora配置文件中的信息是Grid Infrastructure安装过程中Agent自行添加的(During the Grid Infrastructure installation, the (default) node VIP listener is always created referencing the public network), 在 listener.ora 仅指定了PROTOCOL=IPC的信息，而没有指定监听的地址、端口等信息。

实际上11.2 GI的LISTENER 监听器配置默认受到11.2新引入的endpoints_listener.ora配置文件的管理。11.2 GI中监听器的地址和端口信息被移到了 endpoints_listener.ora中， “Line added by Agent”说明是由Oraagent 进程更新的记录。

使用 endpoints_listener.ora的情况下不应使用lsnrctl管理LISTENER，而需使用srvctl或crsctl工具管理，否则lsnrctl将不会识别endpoints_listener.ora中的信息，造成监听没有在必要地址、端口上工作。

在11.2 RAC中listener.ora仅记录LISTENER的IPC条目。这样做的目的是方便dbca配置数据库参数及tnsnames.ora配置文件。

我们可以使用netca图形化工具或者 srvctl 命令行工具添加监听配置；如果仅仅是手动在listener.ora中添加记录的话是无法被注册为Cluster Ready Service的服务的，将不会被CRS管理。

[grid@sljj01 admin]$ cat endpoints_listener.ora 
LISTENER_SLJJ01=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sljj01-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=92.236.125.207)(PORT=1521)(IP=FIRST))))               # line added by Agent
[grid@sljj01 admin]$

节点1：

[grid@sljj01 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER  =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sljj)
      (SID_NAME = sljj1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sljj_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
      (ENVS="TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

节点2：

[grid@sljj02 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER  =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sljj)
      (SID_NAME = sljj2)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sljj_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
      (ENVS="TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

加入如上信息到listener.ora配置文件中(SID_LIST_($LISTENER_NAME)，并重启监听即完成静态注册：

srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

查看监听注册的服务

[grid@sljj02 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUN-2024 23:40:46

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-JUN-2024 23:40:13
Uptime                    0 days 0 hr. 0 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/sljj02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=92.236.125.208)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=92.236.125.210)(PORT=1521)))
Services Summary...
Service "sljj" has 2 instance(s).
  Instance "sljj2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "sljj2", status READY, has 1 handler(s) for this service...
Service "sljjXDB" has 1 instance(s).
  Instance "sljj2", status READY, has 1 handler(s) for this service...
Service "sljj_DGMGRL" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[grid@sljj02 admin]$

3.7.修改TNS配置文件

[oracle@sljj01 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SLJJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sljj-cluster)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sljj)
    )
  )

SLJJ2DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jcpt-database)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sljj2dg)
      (UR = A)
    )
  )

# 可根据实际情况增加
SLJJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sljj-cluster)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sljj)
          (FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASE)(RETIRES = 20)(DELAY = 15))
      (UR = A)
    )
  )

创建认证配置文件 sqlnet.ora

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

4.备数据库配置

4.1.修改参数

step 1.创建 pfile

create pfile='/home/oracle/pfile.bak' from spfile;

step 2.修改 db_unique_name

alter system set db_unique_name='sljj2dg' scope=spfile;

step 3.LOG_ARCHIVE_CONFIG

SQL> alter system set log_archive_config='DG_CONFIG=(sljj2dg,sljj)';

System altered.

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
sljj
sljj2dg

SQL>

step 3.启用自动备用文件管理

alter system set standby_file_management='AUTO';

step 4.文件转换

SQL> alter system set db_file_name_convert='+data/sljj/datafile/','/u01/app/oracle/oradata/sljj/', '+IMAGE/sljj/datafile/','/u01/app/oracle/oradata/sljj/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='+REDO/sljj/onlinelog/','/u01/app/oracle/oradata/sljj/','+FRA/sljj/onlinelog/','/u01/app/oracle/oradata/sljj/' scope=spfile;

System altered.

备端的磁盘组写前面

step 5.FAL_SERVER + FAL_CLIENT

alter system set fal_server='sljj2dg';
alter system set fal_client='sljj';

step 6.重新启动数据库，可以先不重启，因当前角色为主库。

lsnrctl reload

4.2.拷贝密码文件

4.3.创建相应的文件目录

根据上面修改的参数文件，为备库创建相应的文件目录

/u01/app/oracle/fast_recovery_area/orcl
/u01/app/oracle/admin/orcl/adump
/u01/app/oracle/oradata/sljj

4.4.修改监听配置文件

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jcpt-database)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sljj2dg)
      (SID_NAME = sljj)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sljj2dg_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sljj)
      (ENVS="TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

4.5.修改TNS配置文件

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SLJJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sljj-cluster)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sljj)
    )
  )

SLJJ2DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jcpt-database)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sljj2dg)
      (UR = A)
    )
  )

创建认证配置文件 sqlnet.ora

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

重新启动数据库到 nomount 状态，然后再启动监听服务

lsnrctl start

5.验证监听和TNS配置

主库:

sqlplus sys/admin123@sljj as sysdba

sqlplus sys/admin123@sljj2dg as sysdba

备库:

sqlplus sys/admin123@sljj as sysdba

sqlplus sys/admin123@sljj2dg as sysdba

注意：该步骤一定要在主备库上都能通过才能执行下面步骤

6.恢复数据库

# 连接数据库
rman target sys/admin123@sljj auxiliary sys/admin123@sljj2dg nocatalog

# 备份数据
duplicate target database for standby from active database nofilenamecheck;

7.使用 dg_broker

7.1.启用 dg_broker（主备）

dg_broker_config_file1 以及 dg_broker_config_file2 对应路径必须先存在(不存在需要先创建，否则启动dg_broker_start提示ORA-16604: Data Guard broker configuration file inaccessible 。)

step 1.查看 DG_BROKER_CONFIG_FILE文件

SQL> set linesize 1000
SQL> show parameter broker

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1sljj.dat
dg_broker_config_file2		     string	 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2sljj.dat
dg_broker_start 		     boolean	 FALSE
SQL>

可以用默认的路径，也可以自己指定。如果是RAC环境，把这个文件把到共享存储或者ASM中。

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DATA/SLJJ/BROKER/dr1sljj.dat' SCOPE=BOTH;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+DATA/SLJJ/BROKER/dr2sljj.dat' SCOPE=BOTH;

指定配置文件路径后，如果该文件夹不存在需要手工创建。

DGMGRL> CREATE CONFIGURATION jcppt AS PRIMARY DATABASE IS sljj CONNECT IDENTIFIER IS sljj;
Error: ORA-16571: Data Guard configuration file creation failure

Failed.
DGMGRL>

ORA-16571错误为没有配置文件夹告警日志错误如下：

<msg time='2024-06-18T08:24:55.086+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='sljj01' host_addr='92.236.125.207' module=''
 pid='64139'>
 <txt>Data Guard Broker (DMON) failed to save its configuration metadata to file &quot;+DATA/sljj/broker/dr1sljj.dat&quot;
 </txt>
</msg>
<msg time='2024-06-18T08:24:55.086+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='sljj01' host_addr='92.236.125.207' module=''
 pid='64139'>
 <txt>Additional information may be found in &quot;/u01/app/oracle/diag/rdbms/sljj/sljj1/trace/drcsljj1.log&quot;
 </txt>
</msg>

step 2.重置 LOG_ARCHIVE_DEST_n 信息
所有节点均需配置，这步开始，已有主从同步会中断。

ALTER SYSTEM set LOG_ARCHIVE_DEST_2='';
ALTER SYSTEM set LOG_ARCHIVE_DEST_3='';

使用 reset 需要重启。

step 3.开启 dg_broker_start 为TRUE,默认是FALSE

alter system set dg_broker_start=true;

7.2.创建 dg_broker

step 1.创建DGMGRL主库配置文件
使用 dgmgrl 远程登陆主库进行配置

[oracle@sljj01 ~]$ dgmgrl sysdg/admin123
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>
DGMGRL> CREATE CONFIGURATION jcppt AS PRIMARY DATABASE IS sljj CONNECT IDENTIFIER IS sljj;
Configuration "jcppt" created with primary database "sljj"
DGMGRL>

查看配置

DGMGRL> show configuration;

Configuration - jcppt

  Protection Mode: MaxPerformance
  Databases:
    sljj - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>

step 2.启用主库配置

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>
DGMGRL> show configuration;

Configuration - jcppt

  Protection Mode: MaxPerformance
  Databases:
    sljj - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

step 3.增加备库：

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

查看配置：

DGMGRL> show configuration;

Configuration - jcppt

  Protection Mode: MaxPerformance
  Databases:
    sljj    - Primary database
    sljj2dg - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

step 4.启用当前配置:

DGMGRL> enable Database sljj2dg;
Enabled.
DGMGRL>

step 6.查看配置详细信息:

DGMGRL> show configuration;

Configuration - jcppt

  Protection Mode: MaxPerformance
  Databases:
    sljj    - Primary database
    sljj2dg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

step 5.查看配置详细信息:

DGMGRL> show configuration verbose;

Configuration - jcppt

  Protection Mode: MaxPerformance
  Databases:
    sljj    - Primary database
    sljj2dg - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

获取信息中包含任何告警都需要进行处理。

step 6.通过dgmgrl查看主备实例详细

前面使用单引号增加database,后面查询一律要使用。

主库信息：

DGMGRL> show database verbose sljj;

Database - sljj

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    sljj1
      Error: ORA-16737: the redo transport service for standby database "sljj2dg" has an error

    sljj2
      Error: ORA-16737: the redo transport service for standby database "sljj2dg" has an error

  Properties:
    DGConnectIdentifier             = 'sljj'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
ERROR

DGMGRL>

备库信息：

DGMGRL> show database verbose sljj2dg;

Database - sljj2dg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   8 minutes 35 seconds (computed 0 seconds ago)
  Apply Lag:       40 minutes 17 seconds (computed 0 seconds ago)
  Apply Rate:      10.97 MByte/s
  Real Time Query: ON
  Instance(s):
    sljj

  Properties:
    DGConnectIdentifier             = 'sljj2dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+data/sljj/datafile, /u01/app/oracle/oradata/sljj, +IMAGE/sljj/datafile, /u01/app/oracle/oradata/sljj'
    LogFileNameConvert              = '+REDO/sljj/onlinelog, /u01/app/oracle/oradata/sljj, +FRA/sljj/onlinelog, /u01/app/oracle/oradata/sljj'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'sljj'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcpt-database)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sljj2dg_DGMGRL)(INSTANCE_NAME=sljj)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/archivelog'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>

8.备库重新启用数据库闪回

验证是否开启闪回

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> show parameter db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 400G
SQL>

数据库已经开启flashback，那么下面步骤可忽略。

如上显示，该数据库未开启flashback，可按下面方法开启。
a. 创建文件夹
b. 配置 db_recovery_file_dest

SQL> alter system set db_recovery_file_dest_size='5G';

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> show parameter db_recovery_file_dest

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest             string            /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size         big integer        5G
SQL>

c. 开启闪回

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------
YES

SQL>

FQ 1：未配置闪回目录

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

SQL> show parameter db_

FQ 2：日志应用未停止

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL>

9.配置与主备切换

9.1.配置最高可用模式

edit database 'sljj' set property 'LogXptMode'='SYNC';
edit database 'sljj2dg' set property 'LogXptMode'='SYNC';
edit configuration set protection mode as maxavailability;

查看

show configuration;

可以看到已经改变为最高可用模式了

9.2.主备切换

连接

dgmgrl
connect sys/Oracle123@sljj

Note：注意一定要使用密码连接。

开启转换

DGMGRL> switchover to sljj2dg
Performing switchover NOW, please wait...
New primary database "sljj2dg" is opening...
Operation requires start up of instance "sljj" on database "sljj"
Starting instance "sljj"...
Connected to an idle instance.
ORACLE instance started.
Connected to "sljj"
Database mounted.
Database opened.
Connected to "sljj"
Switchover succeeded, new primary is "sljj2dg"
DGMGRL>

Note：可以看到切换是相当的方便，在切换的过程中注意观察主备库之间alert日志，监测实时过程。

查看配置

show configuration;

Configuration - rickadg

  Protection Mode: MaxAvailability
  Members:
  rickdg - Primary database
    rick   - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

DGMGRL>

Note：等待一会，进行查看，可以看到角色已经互换了。

9.3.同步测试

10.配置Observer（可选）

同时ob机器需要安装好同版本的oracle软件

ObserverConnectIdentifier用于指定Observer应如何连接并监视主备库。如果希望Observer使用不同于发送重做数据连接串的连接标识符，请为主库和目标备库设置此属性（可选）。

DGMGRL> start observer
[W000 2024-05-08T09:25:45.030+08:00] FSFO target standby is standby01
Observer 'primary' started
[W000 2024-05-08T09:25:45.169+08:00] Observer trace level is set to USER
[W000 2024-05-08T09:25:45.169+08:00] Try to connect to the primary.
[W000 2024-05-08T09:25:45.169+08:00] Try to connect to the primary primary.
[W000 2024-05-08T09:25:45.238+08:00] The standby standby01 is ready to be a FSFO target
[W000 2024-05-08T09:25:45.238+08:00] Connection to the primary restored!
[W000 2024-05-08T09:25:47.238+08:00] Disconnecting from database primary.

通过登录到观察者计算机并运行DGMGRL，最多启动三个观察者。以具有SYSDG或SYSDBA权限的用户身份连接到配置，然后发出Start observer命令。请注意，在发出命令后将不会得到DGMGRL提示。

DGMGRL> show observer;

配置 - ditie

无观察程序。

DGMGRL>

启动OBSERVER后台进程

DGMGRL> CONNECT sysdg@primary;
Password:
已连接到 "primary"
以 SYSDG 身份连接。
DGMGRL>
DGMGRL> START OBSERVER observer1 IN BACKGROUND FILE IS D:\app\Administrator\broker\fsfo.dat LOGFILE IS D:\app\Administrator\broker\observer.log CONNECT IDENTIFIER IS primary
已连接到 "primary"
已使用连接标识符 "primary" 提交命令 "START OBSERVER"
DGMGRL> show observer

配置 - ditie

  主:                 primary
  活动目标:           standby

观察程序 "observer1" - 主

  主机名:                       primary-database
  上次对主观察程序进行试通:     1 秒前
  上次对目标进行试通:           1 秒前

DGMGRL>

出于安全考虑，Oracle建议使用此命令格式；没有可见的凭据。这种做法可防止系统上的其他用户使用实用程序（例如UNIX ps实用程序）来显示连接凭据。它还防止明文密码在用户的终端上可见。

从脚本启动观察程序时，Oracle建议您使用支持“connect/”的方法，这样数据库连接凭据就不必嵌入脚本中。如果您选择使用客户端Oracle钱包作为安全的外部密码存储，请确保添加主数据库和快速启动故障切换目标备用数据库的凭据。添加每个数据库的凭据时指定的数据库连接字符串必须与ObserverConnectIdentifer或DGConnectIdentifier数据库属性匹配。

当启动多个观察程序时，启用快速启动故障切换后，一个观察程序是主观察程序，其余观察程序是备份观察程序。只有主观察者才能与Data
Guard代理协调快速启动故障切换。如果主数据库和目标备用数据库保持连接，但与主观察器的连接丢失，则代理会尝试指定一个备份观察器作为新的主观察者。

停止observer

DGMGRL> stop observer  observer1
观察程序已停止。
DGMGRL> show observer

配置 - ditie

无观察程序。
DGMGRL>

启动 observer

DGMGRL> start observer "observer1"
[W000 2024-05-29T11:00:50.990+08:00] FSFO target standby is standby
观察程序 'observer1' 已启动
[W000 2024-05-29T11:00:51.085+08:00] Observer trace level is set to USER
[W000 2024-05-29T11:00:51.085+08:00] Try to connect to the primary.
[W000 2024-05-29T11:00:51.085+08:00] Try to connect to the primary primary.
[W000 2024-05-29T11:00:51.105+08:00] The standby standby is ready to be a FSFO target
[W000 2024-05-29T11:00:52.125+08:00] Connection to the primary restored!
[W000 2024-05-29T11:00:54.141+08:00] Disconnecting from database primary.
^C

新会话查看 observer

DGMGRL> show observer

配置 - ditie

  主:                 primary
  活动目标:           standby

观察程序 "observer1" - 主

  主机名:                       primary-database
  上次对主观察程序进行试通:     9 秒前
  上次对目标进行试通:           9 秒前

DGMGRL>

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

02.Data Guard（共14篇）

Data Guard 相关