【实施方案】给Oracle RAC搭一个单实例dataguard备库

前言介绍

对dataguard的理解，限于数据的备份，对于负载较高的主库，可以将报表的业务移到备库里来，减少了主库的负载。笔者为了测试用途，给现有的Oracle RAC+ASM 搭建了一个dataguard的备库，使用的文件系统。

搭建步骤

1、建库

备库安装一套Oracle数据库，注意数据库版本和SID要求和主库一致，笔者使用的11.2.0.4.0版本
安装过程省略…

2、修改/etc/hosts

主库和备库

vi /etc/hosts
添加
#主库
#public ip
192.168.1.120        rac1
192.168.1.121        rac2
#private ip
10.0.0.1               rac1-priv
10.0.0.2               rac2-priv
#vip
192.168.1.122        rac1-vip
192.168.1.123        rac2-vip
#scanip
192.168.1.124        scan
#备库
192.168.1.125 racdg

3、主库设置归档&force logging

srvctl stop database -d orcl -o immediate
srvctl start database -d orcl -o mount
alter database archivelog;
alter database open;
alter database force logging;

4、主句添加standby日志组

alter database add standby logfile thread 1 '+DATA' size 50m;
alter database add standby logfile thread 2 '+DATA' size 50m;
#两条语句各执行多次，要求大小不小于redo日志，数量要比redo日志多一组

5、编辑tnsname.ora文件

主库和备库(配置完记得重启监听)

su - oracle
cd $ORACLE_HOME/network/admin
vi tnsname.ora
#追加如下内容
primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.123)(PORT = 1521))
    )     
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.125)(PORT = 1521))
    )     
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR=A)
    )
  )

TAR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

AUX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.125)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)

6、添加静态监听

主库

#节点一
su - grid
cd $ORACLE_HOME/network/admin
vi listener.ora
追加如下内容
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)    
      (SID_NAME = orcl1)
    )
  )

#节点二
su - grid
cd $ORACLE_HOME/network/admin
vi listener.ora
追加如下内容
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)    
      (SID_NAME = orcl2)
    )
  )

#备库
su - oracle
Cd $ORACLE_HOME/network/admin
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /u01/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

7、修改系统参数

主库（记得重启生效）

alter system set db_unique_name='orcl'  scope=spfile sid='*';   
alter system set log_archive_config='dg_config=(orcl,orcldg)'   scope=both sid='*'; 
alter system set log_archive_dest_1='location=+LOG valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both sid='*'; 
alter system set log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role)db_unique_name=orcldg'  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 standby_file_management='auto'   scope=both sid='*'; 
alter system set fal_server='standby'   scope=both sid='*';    

备库（记得重启生效）

alter system set db_unique_name='orcldg'  scope=spfile ;   
alter system set log_archive_config='dg_config=(orcl,orcldg)'   scope=spfile ; 
alter system set log_archive_dest_1='location=/u01/oracle/archive_log  valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'   scope=spfile ; 
alter system set log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=spfile ; 
alter system set log_archive_dest_state_1=enable   scope=spfile ; 
alter system set log_archive_dest_state_2=enable   scope=spfile ; 
alter system set standby_file_management='auto'   scope=spfile ; 
alter system set fal_server='primary'   scope=spfile ; 

#以下两条转换参数较为重要，分别对应数据文件目录和日志文件目录的转换关系，建议手敲，复制容易出问题
alter system set db_file_name_convert=' +DATA/orcl/datafile/','/u01/oracle/oradata/orcl' scope=spfile;
alter system set log_file_name_convert=' +DATA/orcl/onlinelog/','/u01/oracle/archive_log' scope=spfile;

8、备库启动到nomount状态

shutdown immedaite
startup nomount

9、启动复制传输

网上说要在主库进行，但是我一直是在备库执行的，目前也没发现啥问题

su - oracle
rman target sys/oracle@primary auxiliary sys/oracle@standby
duplicate target database for standby from active database nofilenamecheck; 

10、开启实时日志应用

alter database recover managed standby database using current logfile disconnect;

11、测试

省略…

3、遇到的问题

笔者搭建过程主要遇到了三个问题
1、版本不一致，主库11.2.0.4.0，备库11.2.0.1.0，结果传输复制的时候报错，我就重装了数据库
2、db_file_name_convert和log_file_name_convert格式没填好，传输复制的时候老是报错，结果我就手敲了
3、RAC2节点提交修改后，备库无法查询到。最后定位到，是节点二的密码文件不存在，从节点一复制了一个过来就搞定了