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

环境:RHEL 6.5 + Oracle 11.2.0.4 GI、DB

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 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 sys/oracle@mynas1
DUPLICATE TARGET DATABASE FOR STANDBY from active database 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.ora,指定spfile位置:

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的施工,最终实施成功很容易,但做到每一步实施都规范就很难,我做的过程中也在很多细节做了进一步的说明和延伸。

This entry was posted in Oracle专项实施 and tagged , . Bookmark the permalink.