1. 首页
  2. IT资讯

ORACLE11GR2 RAC DATABASE+STANDLONE DATAGUARD配置摘要

                                                  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会优先于这两个参数。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1370104/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183382.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code