模拟生产搭建Standby RAC实验环境(11.2.0.4 DG)

共计 21584 个字符，预计需要花费 54 分钟才能阅读完成。

模拟生产搭建 Standby RAC 实验环境(11.2.0.4 DG)

环境：RHEL 6.5 + Oracle 11.2.0.4 GI、DB

• 1. 需求背景介绍
• 2. 准备工作
• 3. 主库配置
• 4. 备库配置
• 5. 备库恢复数据库
• 6. 检查同步状态
• 7. 添加资源

1. 需求背景介绍

目前，我有一套正常运行的 Oracle 11.2.0.4 RAC 实验环境。这是一套 VirtualBox 的虚拟化环境，整套环境均放在我的台式机服务器的 D 盘（SSD 固态盘）。也就是说，这套 RAC 环境的系统磁盘和共享磁盘均在本地 D 盘。

目前出于容灾的考虑，计划在我的 NAS 环境中构造一套 DG 灾备环境，这里规划也是一个 2 节点的 Standby RAC.
也就是说，灾备环境的这套 RAC，系统磁盘和共享磁盘都在 NAS 环境中，我这里的 NAS 映射到台式机的 Z 盘。

整个模拟灾备的实验中，做到了硬件级别的分离，这也就更加真实的模拟了生产中的灾备环境。

现在我们来看下现有环境的基本信息：
Primary RAC 主要信息如下：

#public ip
192.168.1.50  jyrac1
192.168.1.52  jyrac2
#virtual ip
192.168.1.51  jyrac1-vip
192.168.1.53  jyrac2-vip
#scan ip
192.168.1.60  jyrac-scan

#private ip
10.10.10.50    jyrac1-priv
10.10.10.52    jyrac2-priv

Primary RAC 集群各资源如下：

[grid@jyrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.FRA1.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.LISTENER.lsnr
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.OCR1.dg
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.asm
               ONLINE  ONLINE       jyrac1                   Started             
               ONLINE  ONLINE       jyrac2                   Started             
ora.gsd
               OFFLINE OFFLINE      jyrac1                                       
               OFFLINE OFFLINE      jyrac2                                       
ora.net1.network
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.ons
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
ora.registry.acfs
               ONLINE  ONLINE       jyrac1                                       
               ONLINE  ONLINE       jyrac2                                       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jyrac1                                       
ora.cvu
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac1.vip
      1        ONLINE  ONLINE       jyrac1                                       
ora.jyrac2.vip
      1        ONLINE  ONLINE       jyrac2                                       
ora.jyzhao.db
      1        ONLINE  ONLINE       jyrac1                   Open                
      2        ONLINE  ONLINE       jyrac2                   Open                
ora.oc4j
      1        ONLINE  ONLINE       jyrac1                                       
ora.scan1.vip
      1        ONLINE  ONLINE       jyrac1          

2. 准备工作

目前需要在 NAS 环境中进行灾备环境 RAC 的集群软件 GI 安装配置和 RAC 数据库软件的安装。
关于安装部分这里不在赘述，如果有问题可以参考官档或者我之前总结的 Oracle 11g RAC 的安装部署相关章节。

• Oracle 安装部署，版本升级，应用补丁快速参考

最终这套 Oracle 11.2.0.4 Standby RAC 实验环境，主要信息如下：

#public ip
192.168.1.61  jystdrac1
192.168.1.63  jystdrac2
#virtual ip
192.168.1.62  jystdrac1-vip
192.168.1.64  jystdrac2-vip
#scan ip
192.168.1.65  jystdrac-scan

#private ip
10.10.10.61    jystdrac1-priv
10.10.10.63    jystdrac2-priv

Standby RAC 集群各资源如下：

[grid@jystdrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.FRA.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.OCR.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.asm
               ONLINE  ONLINE       jystdrac1                Started             
               ONLINE  ONLINE       jystdrac2                Started             
ora.gsd
               OFFLINE OFFLINE      jystdrac1                                    
               OFFLINE OFFLINE      jystdrac2                                    
ora.net1.network
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.ons
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.registry.acfs
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jystdrac1                                    
ora.cvu
      1        ONLINE  ONLINE       jystdrac1                                    
ora.jystdrac1.vip
      1        ONLINE  ONLINE       jystdrac1                                    
ora.jystdrac2.vip
      1        ONLINE  ONLINE       jystdrac2                                    
ora.oc4j
      1        ONLINE  ONLINE       jystdrac1                                    
ora.scan1.vip
      1        ONLINE  ONLINE       jystdrac1           

可以看到，Standby RAC 集群各资源除了还未建立的数据库和实例资源，其他一切正常，至此，准备工作结束。

3. 主库配置

之前在《Oracle 11g DG 配置简明版》文章中，已经简明介绍过单实例 11g DG 的搭建过程。
这里是 RAC 环境，其实基本思路一样的，但我这里实验更真实模拟生产实施标准，细化描述下整个过程。

主库配置操作主要有：

• 数据库归档模式
• Force Logging
• 主库参数文件修改
• 创建 SRLs
• 备份数据库
• 创建备库参数文件
• 更新 tnsnames.ora 文件

3.1 数据库归档模式
我这里主库的 RAC 环境已经是归档模式。
如果不是，这样修改：

srvctl stop database -d jyzhao
sqlplus / as sysdba
startup mount
alter database archivelog;
alter database open;
srvctl start database -d jyzhao

3.2 Force Logging
这个步骤对于 DG 来说非常重要，且需要确认是在 Force Logging 之后备份数据库，防止使用之前的备份出现缺失 nologging 操作的部分，造成数据库的数据不一致。

alter database force logging;
select FORCE_LOGGING FROM V$DATABASE;

3.3 主库参数文件修改
使用下面语句查询现在 DG 相关参数的设置情况：

set linesize 500
col value for a70
col name for a50
 
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
               'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
                     'log_file_name_convert', 'standby_file_management');

参数设定值：

DB_NAME=jyzhao
DB_UNIQUE_NAME=jyzhao
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jyzhao,mynas)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jyzhao'
LOG_ARCHIVE_DEST_2='SERVICE=mynas ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=mynas
DB_FILE_NAME_CONVERT='+data/mynas','+data1/jyzhao'
LOG_FILE_NAME_CONVERT='+data/mynas','+data1/jyzhao','+fra/mynas','+fra1/jyzhao'
STANDBY_FILE_MANAGEMENT=AUTO

注：对于 db_unique_name, 我这里开始规划的主备库分别是 pcssd 和 mynas。
pcssd：说明这个库在我的 PC 机上的 SSD 存储上。
mynas：说明这个库在我的 NAS 存储上。
最后，由于考虑到生产环境一般要求对主库环境影响最小，所以主库的 db_unique_name 最终选择不更改, 还是保留 jyzhao。
此外，这里 LOG_ARCHIVE_DEST_2 使用的是归档进程传输，后续可以根据需要改成 LGWR 传输，这样主备库的延迟可以更低。

修改参数值：

--alter system set db_unique_name='jyzhao' scope=spfile;  
alter system set log_archive_config= 'DG_CONFIG=(jyzhao,mynas)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jyzhao';
alter system set log_archive_dest_2='SERVICE=mynas ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set FAL_SERVER='mynas';
alter system set db_file_name_convert='+data/mynas','+data1/jyzhao' scope=spfile;
alter system set log_file_name_convert='+data/mynas','+data1/jyzhao','+fra/mynas','+fra1/jyzhao' scope=spfile;
alter system set standby_file_management=AUTO; 

3.4 创建 SRLs
确认当前 v$log 信息：

SYS@jyzhao1 >select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------
         1          1        121   52428800        512          2 YES INACTIVE               3784808 08-AUG-17         3822869 09-AUG-17
         2          1        122   52428800        512          2 NO  CURRENT                3822869 09-AUG-17      2.8147E+14
         3          2         97   52428800        512          2 YES INACTIVE               3822868 09-AUG-17         3841980 09-AUG-17
         4          2         98   52428800        512          2 NO  CURRENT                3841980 09-AUG-17      2.8147E+14

创建存放 SRL 的目录并添加 SRLs：

ASMCMD> mkdir +fra1/jyzhao/STANDBYLOG

--alter system set standby_file_management=manual scope=both sid='*';
 
alter database add standby logfile thread 1 group 11 '+fra1/jyzhao/standbylog/standby_group_11.log' size 52428800;
alter database add standby logfile thread 1 group 12 '+fra1/jyzhao/standbylog/standby_group_12.log' size 52428800;
alter database add standby logfile thread 1 group 13 '+fra1/jyzhao/standbylog/standby_group_13.log' size 52428800;
alter database add standby logfile thread 2 group 21 '+fra1/jyzhao/standbylog/standby_group_21.log' size 52428800;
alter database add standby logfile thread 2 group 22 '+fra1/jyzhao/standbylog/standby_group_22.log' size 52428800;
alter database add standby logfile thread 2 group 23 '+fra1/jyzhao/standbylog/standby_group_23.log' size 52428800;
 
--alter system set standby_file_management=auto scope=both sid='*';

添加完 SRLs 之后，可以查询到：

SYS@jyzhao1 >select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME    LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------ ------------ ------------
        11 UNASSIGNED                                        1          0   52428800        512          0 YES UNASSIGNED
        12 UNASSIGNED                                        1          0   52428800        512          0 YES UNASSIGNED
        13 UNASSIGNED                                        1          0   52428800        512          0 YES UNASSIGNED
        21 UNASSIGNED                                        2          0   52428800        512          0 YES UNASSIGNED
        22 UNASSIGNED                                        2          0   52428800        512          0 YES UNASSIGNED
        23 UNASSIGNED                                        2          0   52428800        512          0 YES UNASSIGNED

6 rows selected.

3.5 备份数据库

mkdir -p /public/hotback/jyzhao/standby
 
rman target / 
run
{allocate channel ch1 type disk;
     backup database format '/public/hotback/jyzhao/standby/dbbackup_for_stndby_%U';
     backup current controlfile for standby format '/public/hotback/jyzhao/standby/control_for_standby.ctl';
     release channel ch1;
}

3.6 创建备库参数文件

create pfile='pfile_for_standby.txt' from spfile;

3.7 更新 tnsnames.ora 文件

JYZHAO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

JYZHAO1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
      (SID = jyzhao1)
    )
  )

JYZHAO2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
      (SID = jyzhao2)
    )
  )

LISTENER_JYZHAO1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jyrac1-vip)(PORT = 1521)))
LISTENER_JYZHAO2=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jyrac2-vip)(PORT = 1521)))

MYNAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.62)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
    )
  )


MYNAS1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.62)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
      (SID = jyzhao1)
    )
  )


MYNAS2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
      (SID = jyzhao2)
    )
  )

4. 备库配置

备库配置操作主要有：

• 拷贝主库 RMAN 备份和参数文件
• 拷贝密码文件
• 创建备库需要的目录
• 修改备库参数文件
• 拷贝 tnsnames.ora 文件
• 创建 ASM 相关目录

4.1 拷贝主库 RMAN 备份和参数文件
我这里备份所在的空间可以直接 nfs 挂载到备库，不需要拷贝。

4.2 拷贝密码文件

scp $ORACLE_HOME/dbs/orapwjyzhao1 192.168.1.61:$ORACLE_HOME/dbs/orapwjyzhao1
scp $ORACLE_HOME/dbs/orapwjyzhao1 192.168.1.63:$ORACLE_HOME/dbs/orapwjyzhao2

4.3 创建备库需要的目录
On jystdrac1:

mkdir -p /opt/app/oracle/admin/mynas/adump
mkdir -p /opt/app/oracle/diag/rdbms/mynas/jyzhao1
cd /opt/app/oracle/diag/rdbms/mynas/jyzhao1
mkdir trace cdump

On jystdrac2:

mkdir -p /opt/app/oracle/admin/mynas/adump
mkdir -p /opt/app/oracle/diag/rdbms/mynas/jyzhao2
cd /opt/app/oracle/diag/rdbms/mynas/jyzhao2
mkdir trace cdump

4.4 修改备库参数文件

*.audit_file_dest='/opt/app/oracle/admin/mynas/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/mynas/controlfile/current.260.919999027','+FRA/mynas/controlfile/current.256.919999029'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+data1/jyzhao','+data/mynas'
*.db_name='jyzhao'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='mynas'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jyzhaoXDB)'
*.fal_server='jyzhao'
jyzhao2.instance_number=2
jyzhao1.instance_number=1
*.java_jit_enabled=TRUE
jyzhao1.local_listener='LISTENER_JYZHAO1'
jyzhao2.local_listener='LISTENER_JYZHAO2'
*.log_archive_config='DG_CONFIG=(jyzhao,mynas)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mynas'
*.log_archive_dest_2='SERVICE=jyzhao ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jyzhao'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+data1/jyzhao','+data/mynas','+fra1/jyzhao','+fra/mynas'
*.memory_target=536870912
*.open_cursors=300
*.processes=150
*.remote_listener='jyrac-scan:1521'
*.remote_login_passwordfile='exclusive'
jyzhao2.thread=2
jyzhao1.thread=1
jyzhao2.undo_tablespace='UNDOTBS2'
jyzhao1.undo_tablespace='UNDOTBS1'

注意：我这里的环境备库和主库的磁盘组名称有区别，一定要仔细区分清楚，比如控制文件的全路径确认写正确。
4.5 拷贝 tnsnames.ora 文件
拷贝 tnsnames.ora 文件到备库 2 个节点：

scp $ORACLE_HOME/network/admin/tnsnames.ora 192.168.1.61:$ORACLE_HOME/network/admin/tnsnames.ora
scp $ORACLE_HOME/network/admin/tnsnames.ora 192.168.1.63:$ORACLE_HOME/network/admin/tnsnames.ora

注：这里拷贝后需要修改 LOCAL_LISTENER 参数 (仅在备库端所有节点操作). 这里的 LOCAL_LISTENER 参数是设置的 tnsnames 中配置的别名。

LISTENER_JYZHAO1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jystdrac1-vip)(PORT = 1521)))
LISTENER_JYZHAO2=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jystdrac2-vip)(PORT = 1521)))

4.6 创建 ASM 相关目录

ASMCMD> mkdir +data/MYNAS
ASMCMD> cd +data/MYNAS
ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG
 
ASMCMD> mkdir +fra/MYNAS
ASMCMD> cd +fra/MYNAS
ASMCMD> mkdir ARCHIVELOG CONTROLFILE ONLINELOG STANDBYLOG

5. 备库恢复数据库

• 使用启动实例到 nomount 状态
• 初始化 standby 数据库
• 确认已创建 ORLs 和 SRLs
• 确认备库 tnsnames.ora
• 启动 MRP
• 创建 spfile 然后使用 spfile 启动数据库

5.1 使用启动实例到 nomount 状态
在备库节点 1 操作：

sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/pfile_for_standby.txt

5.2 初始化 standby 数据库
这里常用的两种方案：直接 duplicate 创建；使用 RMAN 手工恢复。

①使用 duplicate 命令创建 standby 数据库:

rman target sys/oracle@jyzhao1 auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

②使用 RMAN 恢复 standby 数据库：

rman target /
restore controlfile from '/public/hotback/jyzhao/standby/control_for_standby.ctl';
alter database mount;
restore database;

根据实际情况或自己更熟悉的方式选择任意一种即可。
这里的恢复操作实际在实验过程中碰到一个问题，具体内容可参考：《案例：Oracle 报错 ASM 磁盘组不存在或没有 mount》。

5.3 确认已创建 ORLs 和 SRLs
查询 v$logfile

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
----------------------------------------------------------------
+DATA/mynas/onlinelog/group_2.267.951608745
+FRA/mynas/onlinelog/group_2.258.951608751
+DATA/mynas/onlinelog/group_1.266.951608731
+FRA/mynas/onlinelog/group_1.257.951608737
+DATA/mynas/onlinelog/group_3.268.951608757
+FRA/mynas/onlinelog/group_3.259.951608763
+DATA/mynas/onlinelog/group_4.269.951608769
+FRA/mynas/onlinelog/group_4.260.951608775
+FRA/mynas/standbylog/standby_group_11.log
+FRA/mynas/standbylog/standby_group_12.log
+FRA/mynas/standbylog/standby_group_13.log
+FRA/mynas/standbylog/standby_group_21.log
+FRA/mynas/standbylog/standby_group_22.log
+FRA/mynas/standbylog/standby_group_23.log

14 rows selected.

在 ASMCMD 中查询没有也会后续自动创建的，确定路径没问题就可以，路径如果有问题，很可能是之前的 convert 参数设置有问题。

5.4 确认备库 tnsnames.ora
确认 tnsnames.ora 配置正确(on all standby nodes)：

JYZHAO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

JYZHAO1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
      (SID = jyzhao1)
    )
  )

JYZHAO2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
      (SID = jyzhao2)
    )
  )

LISTENER_JYZHAO1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jystdrac1-vip)(PORT = 1521)))
LISTENER_JYZHAO2=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jystdrac2-vip)(PORT = 1521)))

MYNAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.62)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
    )
  )

MYNAS1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.62)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
      (SID = jyzhao1)
    )
  )


MYNAS2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynas)
      (SID = jyzhao2)
    )
  )

特别要注意 LISTENER_JYZHAO1 和 LISTENER_JYZHAO2 的配置是否对应备份的信息。

5.5 启动 MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

5.6 创建 spfile 然后使用 spfile 启动数据库
创建 spfile

create spfile='+data/mynas/parameterfile/spfileMYNAS.ora' from pfile='/opt/app/oracle/product/11.2.0/dbhome_1/dbs/pfile_for_standby.txt';

在备库两个节点修改 init

On jystdrac1:
 
[oracle@jystdrac1 dbs]$ cat initjyzhao1.ora 
spfile='+data/mynas/parameterfile/spfileMYNAS.ora'
 
On jystdrac2:
 
[oracle@jystdrac2 dbs]$ cat initjyzhao2.ora
spfile='+data/mynas/parameterfile/spfileMYNAS.ora'

重新启动到 mount 状态可以再次开启 MRP 进程。

6. 检查同步状态

可以在备库根据下面的 SQL 查询相关的信息：

select * from v$archive_gap;
select process, client_process, sequence#, status from v$managed_standby;
select sequence#, first_time, next_time, applied from v$archived_log;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
select thread#, max (sequence#) from v$log_history group by thread#;
select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

实际看同步延迟：

SQL>  select * from v$dataguard_stats;

NAME                             VALUE                                                            UNIT                           TIME_COMPUTED                  DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag                    +00 00:04:36                                                     day(2) to second(0) interval   08/10/2017 09:26:06            08/10/2017 09:25:50
apply lag                        +00 00:05:34                                                     day(2) to second(0) interval   08/10/2017 09:26:06            08/10/2017 09:25:50
apply finish time                +00 00:00:05.432                                                 day(2) to second(3) interval   08/10/2017 09:26:06
estimated startup time           26                                                               second                         08/10/2017 09:26:06

因为我们使用的是归档进程传输，所以会有一组在线日志的延迟，如果我们想在保证不影响主库业务的同时，尽可能更实时的去同步传输，那我们就需要使用 LGWR 传输, 只需要去掉 ARCH 的关键字即可：

-- 之前的设置
alter system set log_archive_dest_2='SERVICE=mynas ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';

-- 修改设置，可以在线修改：
alter system set log_archive_dest_2='SERVICE=mynas VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';

实际上默认的就是使用 LGWR 去 ASYNC 传输，一般这样延迟会很小，大部分状态下都可以接近实时同步。关于这两种方式的差异可以参考《ARCH 和 LGWR 进程同步 DG 日志的区别》。

7. 添加资源

最后我们需要把 Standby RAC 的数据库和实例都添加到 OCR 中，使他们能够方便被 CRS 所管理：

--oracle user:
srvctl add database -d mynas -n jyzhao -o /opt/app/oracle/product/11.2.0/dbhome_1 -c RAC -p +data/mynas/parameterfile/spfileMYNAS.ora -r physical_standby -a DATA,FRA
srvctl add instance -d mynas -i jyzhao1 -n jystdrac1
srvctl add instance -d mynas -i jyzhao2 -n jystdrac2
srvctl start database -d mynas

参数比较多，可以参考帮助说明：

[grid@jystdrac1 ~]$ srvctl add database -h

Adds a database configuration to the Oracle Clusterware.

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]
    -d <db_unique_name>      Unique name for the database
    -o <oracle_home>         ORACLE_HOME path
    -c <type>                Type of database: RAC One Node, RAC, or Single Instance
    -e <server_list>         Candidate server list for RAC One Node database
    -i <inst_name>           Instance name prefix for administrator-managed RAC One Node database (default first 12 characters of <db_unique_name>)
    -w <timeout>             Online relocation timeout in minutes
    -x <node_name>           Node name. -x option is specified for single-instance databases
    -m <domain>              Domain for database. Must be set if database has DB_DOMAIN set.
    -p <spfile>              Server parameter file path
    -r <role>                Role of the database (primary, physical_standby, logical_standby, snapshot_standby)
    -s <start_options>       Startup options for the database. Examples of startup options are OPEN, MOUNT, or 'READ ONLY'.
    -t <stop_options>        Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.
    -n <db_name>             Database name (DB_NAME), if different from the unique name given by the -d option
    -y <dbpolicy>            Management policy for the database (AUTOMATIC, MANUAL, or NORESTART)
    -g "<serverpool_list>"   Comma separated list of database server pool names
    -a "<diskgroup_list>"    Comma separated list of disk groups
    -j "<acfs_path_list>"    Comma separated list of ACFS paths where database's dependency will be set
    -h                       Print usage

最后查看备库资源状态：

[grid@jystdrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.FRA.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.OCR.dg
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.asm
               ONLINE  ONLINE       jystdrac1                Started             
               ONLINE  ONLINE       jystdrac2                Started             
ora.gsd
               OFFLINE OFFLINE      jystdrac1                                    
               OFFLINE OFFLINE      jystdrac2                                    
ora.net1.network
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.ons
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
ora.registry.acfs
               ONLINE  ONLINE       jystdrac1                                    
               ONLINE  ONLINE       jystdrac2                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jystdrac1                                    
ora.cvu
      1        ONLINE  ONLINE       jystdrac1                                    
ora.jystdrac1.vip
      1        ONLINE  ONLINE       jystdrac1                                    
ora.jystdrac2.vip
      1        ONLINE  ONLINE       jystdrac2                                    
ora.mynas.db
      1        ONLINE  ONLINE       jystdrac1                Open                
      2        ONLINE  ONLINE       jystdrac2                Open                
ora.oc4j
      1        ONLINE  ONLINE       jystdrac1                                    
ora.scan1.vip
      1        ONLINE  ONLINE       jystdrac1                                    
[grid@jystdrac1 ~]$ 

确认开启 ADG 实时应用：

SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
JYZHAO    PHYSICAL STANDBY READ ONLY
JYZHAO    PHYSICAL STANDBY READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile disconnect from session;

Database altered.

SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
JYZHAO    PHYSICAL STANDBY READ ONLY WITH APPLY
JYZHAO    PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> set lines 1000                  
SQL> select * from v$dataguard_stats;

NAME                             VALUE                                                            UNIT                           TIME_COMPUTED                  DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag                    +00 00:00:00                                                     day(2) to second(0) interval   08/10/2017 12:40:25            08/10/2017 12:40:22
apply lag                        +00 00:00:00                                                     day(2) to second(0) interval   08/10/2017 12:40:25            08/10/2017 12:40:22
apply finish time                                                                                 day(2) to second(3) interval   08/10/2017 12:40:25
estimated startup time           30                                                               second                         08/10/2017 12:40:25

SQL> /

NAME                             VALUE                                                            UNIT                           TIME_COMPUTED                  DATUM_TIME
-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag                    +00 00:00:00                                                     day(2) to second(0) interval   08/10/2017 12:43:04            08/10/2017 12:43:03
apply lag                        +00 00:00:00                                                     day(2) to second(0) interval   08/10/2017 12:43:04            08/10/2017 12:43:03
apply finish time                +00 00:00:00.000                                                 day(2) to second(3) interval   08/10/2017 12:43:04
estimated startup time           30                                                               second                         08/10/2017 12:43:04

Reference

非常感谢 Bhavin Hingu，我在实验过程中，很多规范参考了他的文章《Step By Step of Configuring Oracle 11gR2 (11.2.0.1) RAC to RAC Dataguard》，不得不说，这种 Standby RAC 的施工，最终实施成功很容易，但做到每一步实施都规范就很难，我做的过程中也在很多细节做了进一步的说明和延伸。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-08/146329.htm