共计 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 相关目录
| mkdir +data/MYNAS | |
| cd +data/MYNAS | |
| mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG | |
| ASMCMD> mkdir +fra/MYNAS | |
| cd +fra/MYNAS | |
| 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
正文完
星哥玩云-微信公众号
