oracle rac与oracle单实例做dataguard
一、环境准备与DG规划:
主备库系统:
主库:oracle rac11g(11.2.0.4) 备库:oracle单实例11g(11.2.0.4)
主机名:rac1、rac2 主机名:rac-dg
ip:192.168.42.231、192.168.42.232 ip:192.168.42.233
DB_NAME:racdb DB_NAME:racdb
DB_UNIQUE_NAME:rac12 DB_UNIQUE_NAME:single
控制文件:+DATA 控制文件:/u01/app/oracle/oradata/racdb/
数据文件:+DATA 数据文件:/u01/app/oracle/oradata/racdb/
归档路径:+RCY 归档路径:/u01/app/oracle/admin/racdb/archive/
联机日志路径:+DATA/racdb/onlinelog/ 联机日志路径:/u01/app/oracle/oradata/racdb/
监听位置:/u01/app/11.2.0/grid/ 监听位置:/u01/app/oracle/product/11.2.0/
network/admin/listener.ora db_1/network/admin/listener.ora
二、配置参数文件
1、从官方文档上面找到参数例子,根据实际情况进行修改
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl','/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
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=30
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT=
'/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
修改后的参数:
主库:
DB_NAME=racdb
DB_UNIQUE_NAME=rac12
LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac12,single)'
LOG_ARCHIVE_DEST_1=
'LOCATION="+RCY"
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=rac12'
LOG_ARCHIVE_DEST_2=
'SERVICE=single ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=single'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=arch_%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=single
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/racdb/','+DATA/racdb/datafile/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/racdb/','+DATA/racdb/onlinelog/'
STANDBY_FILE_MANAGEMENT=AUTO
备库:
DB_NAME=racdb
DB_UNIQUE_NAME=single
LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac12,single)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/admin/racdb/archive/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=single'
LOG_ARCHIVE_DEST_2=
'SERVICE=rac12 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=rac12'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=arch_%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=rac12
DB_FILE_NAME_CONVERT='+DATA/racdb/datafile/','/u01/app/oracle/oradata/racdb/'
LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/racdb/'
STANDBY_FILE_MANAGEMENT=AUTO
2、分别将主备库的参数写到参数文件中
主库:
create pfile='/home/oracle/initracdb.ora'from spfile;
vi initracdb.ora(添加主库的参数到spfile中)
shutdown immediate
startup pfile='/home/oracle/initracdb.ora'
create spfile='+DATA/racdb/spfileracdb.ora'from pfile='/home/oracle/initracdb.ora';
重启库
同理将参数配置到备库spfile中
三:配置hosts(主库两节点和备库都要配置)
vi /etc/hosts
四、配置主备库的tns
vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
配置完两个rac节点分别与单实例库互ping
tnsping rac-dg(tnsping ip也可)
tnsping rac-scan(tnsping ip或tnspinghostname都可以)
五、为主库添加standby 日志组
查询日志组
添加五组standby日志
六、rman备份主库,duplicate到备库
主库rman登录
rman target /
backup device type disk format'/u01/app/oracle/rman/%U' database plus archivelog;
backup device type disk format '/u01/app/oracle/rman/controlfile.ctl'current controlfile for standby;
备库sqlplus / as sysdba
startup nomount
exit
rman target sys/oracle@rac12 auxiliarysys/oracle
duplicate target database for standbynofilenamecheck;
输出以下内容:
RMAN> duplicatetarget database for standby nofilenamecheck;
Starting Duplicate Dbat 2016/07/21 12:41:59
using target databasecontrol file instead of recovery catalog
allocated channel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1: SID=17 device type=DISK
contents of MemoryScript:
{
restore clone standby controlfile;
}
executing MemoryScript
Starting restore at 2016/07/2112:42:00
using channelORA_AUX_DISK_1
channelORA_AUX_DISK_1: starting datafile backup set restore
channelORA_AUX_DISK_1: restoring control file
channelORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/rman/controlfile.ctl
channel ORA_AUX_DISK_1:piece handle=/u01/app/oracle/rman/controlfile.ctl tag=TAG20160721T121845
channelORA_AUX_DISK_1: restored backup piece 1
channelORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/racdb/control01.ctl
output filename=/u01/app/oracle/oradata/racdb/control02.ctl
Finished restore at2016/07/21 12:42:01
contents of MemoryScript:
{
sql clone 'alter database mount standbydatabase';
}
executing MemoryScript
sql statement: alterdatabase mount standby database
RMAN-05529: WARNING:DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk grouponly.
contents of MemoryScript:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/racdb/system.259.913225403";
set newname for datafile 2 to
"/u01/app/oracle/oradata/racdb/sysaux.260.913225407";
set newname for datafile 3 to
"/u01/app/oracle/oradata/racdb/undotbs1.261.913225411";
set newname for datafile 4 to
"/u01/app/oracle/oradata/racdb/undotbs2.263.913225421";
set newname for datafile 5 to
"/u01/app/oracle/oradata/racdb/users.264.913225423";
restore
clone database
;
}
executing Memory Script
executing command:SET NEWNAME
renamed tempfile 1 to+data in control file
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
Starting restore at2016/07/21 12:42:07
using channelORA_AUX_DISK_1
channelORA_AUX_DISK_1: starting datafile backup set restore
channelORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channelORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/racdb/system.259.913225403
channelORA_AUX_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/racdb/sysaux.260.913225407
channelORA_AUX_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/racdb/undotbs1.261.913225411
channelORA_AUX_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/racdb/undotbs2.263.913225421
channelORA_AUX_DISK_1: restoring datafile 00005 to/u01/app/oracle/oradata/racdb/users.264.913225423
channelORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/rman/0prb8ig1_1_1
channelORA_AUX_DISK_1: piece handle=/u01/app/oracle/rman/0prb8ig1_1_1tag=TAG20160721T121809
channelORA_AUX_DISK_1: restored backup piece 1
channelORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at2016/07/21 12:42:23
contents of MemoryScript:
{
switch clone datafile all;
}
executing MemoryScript
datafile 1 switchedto datafile copy
input datafile copyRECID=1 STAMP=917786543 filename=/u01/app/oracle/oradata/racdb/system.259.913225403
datafile 2 switchedto datafile copy
input datafile copyRECID=2 STAMP=917786543 filename=/u01/app/oracle/oradata/racdb/sysaux.260.913225407
datafile 3 switchedto datafile copy
input datafile copyRECID=3 STAMP=917786543 file name=/u01/app/oracle/oradata/racdb/undotbs1.261.913225411
datafile 4 switchedto datafile copy
input datafile copyRECID=4 STAMP=917786543 filename=/u01/app/oracle/oradata/racdb/undotbs2.263.913225421
datafile 5 switchedto datafile copy
input datafile copyRECID=5 STAMP=917786543 filename=/u01/app/oracle/oradata/racdb/users.264.913225423
Finished Duplicate Dbat 2016/07/21 12:42:27
将主库的密码文件拷贝到备库
主库:
cd /u01/app/oracle/product/11.2.0/db_1/dbs/
scp orapwracdb1 rac-dg:/u01/app/oracle/product/11.2.0/db_1/dbs/
备库:
cd /u01/app/oracle/product/11.2.0/db_1/dbs/
mv orapwracdb1 orapwracdb
sqlplus / as sysdba
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database using currentlogfile disconnect from session;
select controlfile_type,open_mode from v$database;
select sequence#,applied from v$archived_log;
输出结果如下,为搭建成功;
主库再多切几个归档,查看备库同步情况。
---------------------------------------------------------------------------到此,rac---单实例dataguard搭建完成;