模拟生产搭建Standby RAC实验环境(11.2.0.4 DG)
环境:RHEL 6.5 + Oracle 11.2.0.4 GI、DB
目前,我有一套正常运行的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
目前需要在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集群各资源除了还未建立的数据库和实例资源,其他一切正常,至此,准备工作结束。
之前在《Oracle 11g DG配置简明版》文章中,已经简明介绍过单实例11g DG的搭建过程。
这里是RAC环境,其实基本思路一样的,但我这里实验更真实模拟生产实施标准,细化描述下整个过程。
主库配置操作主要有:
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.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.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进程。
可以在备库根据下面的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日志的区别》。
最后我们需要把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
非常感谢Bhavin Hingu,我在实验过程中,很多规范参考了他的文章《Step By Step of Configuring Oracle 11gR2 (11.2.0.1) RAC to RAC Dataguard》,不得不说,这种Standby RAC的施工,最终实施成功很容易,但做到每一步实施都规范就很难,我做的过程中也在很多细节做了进一步的说明和延伸。
来源: http://www.linuxidc.com/Linux/2017-08/146329.htm