ORACLE11GR2 RAC DATABASE+STANDLONE STANDBY配置摘要
一、目标
为集群数据库配置DATAGUARD，同时实现SWITCH OVER，同时DATAGUARD端并没有使用ASM，其配置方法预计和单库区别不大，主要在于RMAN恢复RAC数据库到单库，同时DATAGUARD互联准备使用SCAN IP而非VIP。
整个安装过程注意数据文件目录的改变，为了避免不必要的麻烦，DATAGUARD端我们使用OMF，官方文档如下：
If the primary database is configured to use OMF, then Oracle recommends that the standby database be configured to use OMF, too.
To do this, set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to appropriate values. Maintenance
and future role transitions are simplified if the same disk group names are used for both the primary and standby databases.
If OMF parameters are set on the standby, then new files on that standby are always created as OMF, regardless of how they were
created on the primary. Therefore, if both the DB_FILE_NAME_CONVERT and DB_CREATE_FILE_DEST parameters are set on the standby,
the DB_CREATE_FILE_DEST parameter takes precedence.
注意到一旦设置了OMF我们的DB_FILE_NAME_CONVERT和log_file_name_convert参数是不需要设置的。因为OMF会优先于这两个参数。
二、环境描述
基础环境（集群端）
服务器型号 Vmware Esx 4 虚拟机
操作系统 Oracle Linux Server release 6.4
数据库版本 Oracle database 11.2.0.3
Grid Infrastructure 版本 Grid Infrastructure 11.2.0.3
PUBLIC IP 192.168.1.141 192.168.1.142
VIP 192.168.1.143 192.168.1.144
PRI IP（双心跳） 10.10.10.3 10.10.10.4 10.10.11.3 10.10.11.4
SCAN IP 192.168.1.145
DATABASE NAME ORA11G
基础环境（STANDBY 端）
服务器型号 Vmware Esx 4 虚拟机
操作系统 Red Hat Enterprise Linux Server release 6.0 (Santiago)
数据库版本 Oracle database 11.2.0.3
IP 192.168.1.170
DATABASE NAME ORA11G
三、配置
1、同单库一样可以首先改写唯一不能静态修改的参数
alter system set db_unique_name='ora11grac' scope=spfile sid='*';
2、启动FORCE LOGGING
ALTER DATABASE FORCE LOGGING;
3、重启RAC数据库,让修改的UNIQUE参数生效，其他的参数就可以动态修改了
srvctl stop database –d ora11g –o immediate
srvctl start database –d ora11g
4、注意修改UNIQUE NAME后SERVICE 会响应的修改为UNIQUE的名字如下:
Services Summary...
Service "ora11gXDB" has 2 instance(s).
Instance "ora11g1", status READY, has 1 handler(s) for this service...
Instance "ora11g2", status READY, has 1 handler(s) for this service...
Service "ora11grac" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...
我们加入一个SERVICE
alter system set service_names=ora11grac,ora11g scope=both sid='*';
然后SCAN_LISTENER SERVICE如下：
Services Summary...
Service "ora11g" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...
Service "ora11gXDB" has 2 instance(s).
Instance "ora11g1", status READY, has 1 handler(s) for this service...
Instance "ora11g2", status READY, has 1 handler(s) for this service...
Service "ora11grac" has 2 instance(s).
Instance "ora11g1", status READY, has 2 handler(s) for this service...
Instance "ora11g2", status READY, has 2 handler(s) for this service...
The command completed successfully
5、我们可以提前配置好TNSNAMES.ORA，RAC 使用SCAN IP进行连接
RAC双节点和DATAUGARD节点同时加入如下：
ora11grac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11grac)
)
)
ora11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.170)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11gdg)
)
)
接下来我们为DATAGUARD端准备环境：
6、首先建立RAC的pfile文件，默认的pfile是指向ASM spfile的一个指针如下：
[oracle@rac2 dbs]$ more initora11g2.ora
SPFILE='+DATA/ora11g/spfileora11g.ora'
所以我们不要用
Create pfile from spfile 而是要注意制定以下路径
create pfile='/home/oracle/pfile.ora' from spfile;
得到RAC数据库的配置文件
*.audit_file_dest='/oracle/app/oracle/admin/ora11g/adump' 更改为
*.audit_file_dest='/home/oradba/ora11g/admin/ora11g/adump'
*.audit_trail='db'
*.cluster_database=true --去掉
*. service_names=ora11grac,ora11g更改为
*. service_names=ora11gdg,ora11g
*.compatible='11.2.0.0.0'
*.control_files='+DATA/ora11g/controlfile/current.262.858666455','+ARCH/ora11g/controlfile/current.314.858666455' 更改为
*.control_files='/bak/ora11g/data/current01.dbf','/bak/ora11g/data/current02.dbf'
*.db_block_size=8192
*.db_create_file_dest='+DATA' --使用OMF可以进行修改
*.db_create_file_dest='/bak/ora11g/data'
*.db_create_online_log_dest_1='+DATA' --使用OMF可以进行修改
*.db_create_online_log_dest_1='/bak/ora11g/data'
*.db_create_online_log_dest_2='+ARCH' --使用OMF可以进行修改
*.db_create_online_log_dest_2='/bak/ora11g/data'
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11grac' 更改为
*.db_unique_name='ora11gdg'
*.diagnostic_dest='/oracle/app/oracle' 更改为
*.diagnostic_dest='/home/oradba/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
ora11g1.instance_number=1 --去掉
ora11g2.instance_number=2 --去掉
*.log_archive_dest_1='LOCATION=+ARCH'更改为
*.log_archive_dest_1='LOCATION=/bak/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11gdg'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=162529280
*.processes=150
*.remote_listener='racscan:1521' --去掉
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.sga_target=488636416
ora11g2.thread=2 --去掉
ora11g1.thread=1 --去掉
ora11g1.undo_tablespace='UNDOTBS1'更改为
*.undo_tablespace='UNDOTBS1'
ora11g2.undo_tablespace='UNDOTBS2' --去掉
最后修改的参数文件如下：
--原始参数
*.audit_file_dest='/home/oradba/ora11g/admin/ora11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.service_names=ora11gdg,ora11g
*.control_files='/bak/ora11g/data/current01.dbf','/bak/ora11g/data/current02.dbf'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora11g'
*.db_unique_name='ora11gdg'
*.diagnostic_dest='/home/oradba/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=162529280
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.sga_target=488636416
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='/bak/ora11g/data'
*.db_create_online_log_dest_1='/bak/ora11g/data'
*.db_create_online_log_dest_2='/bak/ora11g/data'
--加入DATAGUARD相关的参数如下：
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/bak/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11gdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=ora11grac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11grac'
*.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=2
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_SERVER=ora11grac
*.FAL_CLIENT=ora11gdg
7、主库进行RMAN备份，然后备份STANDBY CONTROLFILE，随后传输密码文件，备份文件，STANDBY控制文件到备库
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
8、接下来启动到MOUNT阶段，同时准备进行恢复数据文件，预计使用了OMF,RMAN会使用新的OMF位置索引不用SET NEWNAME也不用SWITCH DATABASE了，因为OMF会自动更新控制文件信息
RMAN> restore database
2> ;
Starting restore at 14-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ora11g/datafile/system.265.858666317
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ora11g/datafile/sysaux.258.858666317
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ora11g/datafile/undotbs1.259.858666317
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ora11g/datafile/users.261.858666319
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ora11g/datafile/undotbs2.272.858666695
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ora11grac/datafile/testpp.276.858711957
channel ORA_DISK_1: reading from backup piece /home/oracle/bak_1_1_01pitrl1_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/bak_1_1_01pitrl1_1_1.bak tag=TAG20140919T193600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 14-MAR-14
可以看到已经恢复成功，并且使用的目录就是我们设置的OMF的数据目录
[root@dg1 datafile]# ls -lrt
total 1409460
-rw-r----- 1 oradba dba 5251072 Mar 14 04:24 o1_mf_users_9l450d89_.dbf
-rw-r----- 1 oradba dba 10493952 Mar 14 04:24 o1_mf_testpp_9l450d6c_.dbf
-rw-r----- 1 oradba dba 26222592 Mar 14 04:24 o1_mf_undotbs2_9l450d5j_.dbf
-rw-r----- 1 oradba dba 78651392 Mar 14 04:24 o1_mf_undotbs1_9l450d48_.dbf
-rw-r----- 1 oradba dba 576724992 Mar 14 04:25 o1_mf_sysaux_9l450d33_.dbf
-rw-r----- 1 oradba dba 744497152 Mar 14 04:25 o1_mf_system_9l450d2b_.dbf
[root@dg1 datafile]# pwd
/bak/ora11g/data/ORA11GDG/datafile
查看控制文件DATAFILE信息：
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_system_9l450d2b_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_sysaux_9l450d33_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_undotbs1_9l450d48_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_users_9l450d89_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_undotbs2_9l450d5j_.dbf
/bak/ora11g/data/ORA11GDG/datafile/o1_mf_testpp_9l450d6c_.dbf
如果本步报错
ORA-19504: failed to create file "+DATA/racdb/datafile/data01.dbf"
ORA-17502: ksfdcre:3 Failed to create file +DATA/racdb/datafile/data01.dbf
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Servic
可以执行如下：
You are restoring or duplicating the target database to a new host using RMAN
The datafiles are not OMF files and you want to make them OMF. Using 'set newname for
datafile to NEW' will generate a new OMF filename for the restored datafile.
This will avoid the manual entry or vi/notepad editing of similar output.
Using this output the datafiles will be restored to the DB_CREATE_FILE_DEST.
If this parameter is not set you must add the correct path as in '/path/NEW'
will direct the files to the new location and give an OMF filename.
run {
set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;
set newname for datafile 6 to NEW;
set newname for datafile 7 to NEW;
restore database ;
switch datafile all;
}
9、在备库增加STANDBY LOGFILE，数量为RAC总LOGFILE GROUP+1
alter database add standby logfile group 7 size 50m;
alter database add standby logfile group 8 size 50m;
alter database add standby logfile group 9 size 50m;
alter database add standby logfile group 10 size 50m;
alter database add standby logfile group 11 size 50m;
alter database add standby logfile group 12 size 50m;
alter database add standby logfile group 13 size 50m;
以下完成RAC主库端的设置：
10、参数修改
DB_UNIQUE_NAME=ora11grac --已经更改
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)' --需要更改
LOG_ARCHIVE_DEST_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11grac' -需要更改
LOG_ARCHIVE_DEST_2='SERVICE=ora11gdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11gdg' -需要更改
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=2 --可以更改
STANDBY_FILE_MANAGEMENT='AUTO' --必须修改
FAL_SERVER=ora11gdg --需要更改
FAL_CLIENT=ora11grac --需要更改
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora11gdg,ora11grac)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11grac' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ora11gdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11gdg' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=2 scope=both sid='*';
alter system set FAL_SERVER=ora11gdg scope=both sid='*';
alter system set FAL_CLIENT=ora11grac scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*';
10、主库也建立的standby logfile
alter database add standby logfile thread 1 group 7 size 50m;
alter database add standby logfile thread 1 group 8 size 50m;
alter database add standby logfile thread 1 group 9 size 50m;
alter database add standby logfile thread 1 group 10 size 50m;
alter database add standby logfile thread 2 group 11 size 50m;
alter database add standby logfile thread 2 group 12 size 50m;
alter database add standby logfile thread 2 group 13 size 50m;
alter database add standby logfile thread 2 group 14 size 50m;
11、备库启动日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
12、启动ACTIVE STANDBY
取消重做应用：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
打开数据库以用于只读访问：
SQL> ALTER DATABASE OPEN;
如果重新
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
恢复到正常模式
13、检查备份数据库状态
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 03/14/2014 05:50:54 03/14/2014 05:50:53
apply lag +00 00:00:00 day(2) to second(0) interval 03/14/2014 05:50:54 03/14/2014 05:50:53
apply finish time +00 00:00:00.000 day(2) to second(3) interval 03/14/2014 05:50:54
estimated startup time 48 second 03/14/2014 05:50:54
SQL> select * from v$managed_standby;
PROCESS PID STATUS CLIENT_PROCESS CLIENT_PID CLIENT_DBID GROUP# RESETLOG_ID THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ---------- ------------ -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ---------- ------------ -------------
ARCH 3367 CLOSING ARCH 3367 4254866583 8 858666457 2 7 1 391 0 0 0
ARCH 3369 CONNECTED ARCH 3369 4254866583 N/A 0 0 0 0 0 0 0 0
RFS 3421 IDLE UNKNOWN 3516 4254866583 N/A 0 0 0 0 0 0 0 0
RFS 3417 IDLE LGWR 11479 4254866583 2 858666457 1 13 6473 1 0 0 0
RFS 3419 IDLE LGWR 7991 4254866583 3 858666457 2 8 6198 1 0 0 0
RFS 3423 IDLE UNKNOWN 3924 4254866583 N/A 0 0 0 0 0 0 0 0
MRP0 3598 APPLYING_LOG N/A N/A N/A N/A 858666457 1 13 6473 102400 0 0 0
14、进行SWITCH 测试
首先关闭一个RAC实例
[oracle@rac1 dbs]$ srvctl stop instance -d ora11g -i ora11g2
查看集群数据库状态
ora.ora11g.db
1 ONLINE ONLINE rac1 Open
2 OFFLINE OFFLINE Instance Shutdown
在剩余节点执行
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
主库进行切换：
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (WITH SESSION SHUTDOWN);（有一点耗时,要关闭数据库到MOUNT阶段）
关闭主库RAC剩余的节点
srvctl stop database -d ora11g
然后启动数据库到MOUNT
srvctl start database -d ora11g -o mount
在备库执行
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
结果应该是
SWITCHOVER_STATUS
--------------------
TO PRIMARY
然后
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (WITH SESSION SHUTDOWN);
最后关闭数据库启动到open
shutdown immediate
startup
最后可以打开备用RAC数据库，让RAC数据库成为ACTIVE STANDBY
2个实例同时执行
alter database open;
最后2个实例同事启动MRP进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
15、最后关注一下切换后RAC数据库中关于STANDBY的进程
查看节点1
PROCESS PID STATUS CLIENT_P CLIENT_PID
--------- ---------- ------------ -------- -------------------------------------
ARCH 16226 CLOSING ARCH 16226
ARCH 16228 CLOSING ARCH 16228
RFS 16307 IDLE LGWR 3729
RFS 16312 IDLE UNKNOWN 3727
MRP0 16563 APPLYING_LOG N/A N/A
节点1的所有进程和单库没有两样
查看节点2
PROCESS PID STATUS CLIENT_P CLIENT_PID
--------- ---------- ------------ -------- -------------------------------------
ARCH 12240 CONNECTED ARCH 12240
ARCH 12242 CONNECTED ARCH 12242
虽然执行了开启MRP进程语句节点的实例并没有MRP进程，所以可以看到进程切换后DATAGUARD的恢复进程和传输进程实际是在THREAD 1进程的
总结：
1、RAC的DATAGUARD 如果使用OMF可以大大简化，进行RMAN恢复的时候OMF会让恢复自动恢复到正确的目录，进行备库OPEN的时候也会自动建立正确的LOGFILE 。其实不管数据库级是否使用OMF，ASM实际都会使用OMF进行文件管理
2、DATAGUARD的恢复进程和传输进程实际是在THREAD 1进程的
3、一旦设置了OMF我们的DB_FILE_NAME_CONVERT和log_file_name_convert参数是不需要设置的。因为OMF会优先于这两个参数。