模拟生产搭建Standby RAC实验环境(11.2.0.4 DG)
2017-08-10 13:06 AlfredZhao 阅读(2170) 评论(1) 编辑 收藏 举报模拟生产搭建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配置简明版](http://www.cnblogs.com/jyzhao/p/4968164.html)》文章中，已经简明介绍过单实例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
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](http://www.oracledba.org/11gR2/dr/11gR2_dataguard_RAC_to_RAC.html)》，不得不说，这种Standby RAC的施工，最终实施成功很容易，但做到每一步实施都规范就很难，我做的过程中也在很多细节做了进一步的说明和延伸。
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
