ORACLE 11G RAC ADG

最终实现环境：11.2.0.4版本 2节点RAC + 1节点DG
本文旨在弄清楚整个搭建过程中涉及到的基础概念；
本文安装maximum performance mode（最大性能模式）也是DG默认的数据保护模式；

前提环境：
1).主库已安装11.2.0.4版本 RAC数据库

#public ip 192.168.56.150 jyrac1 192.168.56.152 jyrac2 #private ip 10.10.10.11 jyrac1-priv 10.10.10.12 jyrac2-priv #virtual ip 192.168.56.151 jyrac1-vip 192.168.56.153 jyrac2-vip #scan ip 192.168.56.160 jyrac-scan

2).备库已安装同版本软件、监听程序

192.168.56.158 jydg

1.主库准备工作

数据库设置为force logging模式

SQL> alter database force logging;

数据库设置为归档模式（RAC数据库更改归档模式参见文档：How To Enable/Disable Archive Logging In RAC Environment for 10.2 and higher version (文档 ID 1186764.1)）

SQL> alter database archivelog;

检查

SQL> select inst_id, name, open_mode, log_mode, force_logging from gv$database;

2.物理备库准备工作

确保已成功安装同版本数据库软件、监听程序；
配置静态监听，这里的ORACLE_HOME是 /u01/app/oracle/product/11.2.0/dbhome_1

SID_LIST_LISTENER =
   (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = jyzhao_s)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
          (SID_NAME = jyzhao_s)   
        )   
    ) 

3.创建物理备库

3.1 配置tnsnames.ora
主库上配置【@all rac nodes 】：

JYZHAO_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.158)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao_s)
    )
  )

JYZHAO1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

JYZHAO2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

备库上配置：

JYZHAO_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.158)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao_s)
    )
  )

JYZHAO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.160)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

JYZHAO1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

JYZHAO2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyzhao)
    )
  )

3.2 修改主库参数
FAL_CLIENT(确定11g dg官方安装文档中这个参数没有再提，因此只需要设定fal_server)

FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

log_archive_config

LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.

log_archive_dest_3（这里的日志传输链路选择log_archive_dest_3,根据需求规范可改选其他的）
查看相关日志传输链路是否存在错误信息：
select dest_id, dest_name, error from v$archive_dest where dest_id=3;
select inst_id, error from gv$archive_dest where dest_id=3;

例如：
在主库上对部分参数做适当的修改调整。

alter system set FAL_SERVER='jyzhao_s'; alter system set log_archive_config= 'DG_CONFIG=(jyzhao,jyzhao_s)'; alter system set log_archive_dest_3='SERVICE=jyzhao_s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=jyzhao_s';

在11.2官档中，提到其中LGWR参数已过时，Specify SYNC or ASYNC. ASYNC is the default if neither attribute is specified.

--设置db_unique_name, log_archive_config(这里主库的db_unique_name，出于对现有的生产环境最小影响考虑没有改)

alter system set db_unique_name='jyzhao' scope=spfile; alter system set log_archive_config= 'DG_CONFIG=(jyzhao,jyzhao_s)';

--归档日志目录

alter system set log_archive_dest_3='SERVICE=jyzhao_s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=jyzhao_s'; select dest_id, dest_name, error from v$archive_dest where dest_id=3;

--归档日志文件命名规则
--DG的fal_server和fal_client

alter system set fal_server='jyzhao_s';

--设置standby_file_management为自动

alter system set standby_file_management=AUTO; 

--设置备库日志文件组，数量一般为目标库日志文件组+1(当原环境是RAC环境时，如何修改？)

alter database add standby logfile thread 1 group 11 size 50M; alter database add standby logfile thread 1 group 12 size 50M; alter database add standby logfile thread 1 group 13 size 50M; alter database add standby logfile thread 2 group 21 size 50M; alter database add standby logfile thread 2 group 22 size 50M; alter database add standby logfile thread 2 group 23 size 50M; 

3.3 创建备库密码文件
主库密码文件拷贝到备库相应的目录下
scp $ORACLE_HOME/dbs/orapw* xxx.xxx.xxx.xxx:/.../dbs/

[oracle@jyrac2 dbs]$ scp $ORACLE_HOME/dbs/orapwjyzhao2 192.168.56.158:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwjyzhao_s

--确认 orapwjyzhao_s 确认standby database的db_name为jyzhao； 然后db_unique_name, service_names, instance_name都是jyzhao_s

3.4 创建pfile文件
从主库的spfile中导出pfile文件，传输到备库并做适当的修改调整。
create pfile='/tmp/pfile.ora' from spfile;

在备库修改如下：

--原版(去掉RAC实例1，2的相关设置参数)：
[oracle@jydg tmp]$ more pfile.ora 
*.audit_file_dest='/opt/app/oracle/admin/jyzhao/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA1/jyzhao/controlfile/current.260.919999027','+FRA1/jyzhao/controlfile/current.256.919999029' *.db_block_size=8192 *.db_create_file_dest='+DATA1' *.db_domain='' *.db_name='jyzhao' *.db_recovery_file_dest='+FRA1' *.db_recovery_file_dest_size=4621074432 *.diagnostic_dest='/opt/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=jyzhaoXDB)' *.fal_server='jyzhao_s' *.log_archive_config='DG_CONFIG=(jyzhao,jyzhao_s)' *.log_archive_dest_3='SERVICE=jyzhao_s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=jyzhao_s' *.log_archive_dest_state_3='enable' *.memory_max_target=314572800 *.memory_target=314572800 *.open_cursors=300 *.processes=150 *.remote_listener='jyrac-scan:1521' *.remote_login_passwordfile='exclusive' *.standby_file_management='AUTO' --修正后(主要确认路径是否都存在)：
[oracle@jydg tmp]$ more pfile.ora 
*.audit_file_dest='/u01/app/oracle/admin/jyzhao/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/control01.ctl','/u01/app/oracle/fra/control02.ctl' db_unique_name='jyzhao_s' DB_FILE_NAME_CONVERT='+DATA1/jyzhao/datafile','/u01/app/oracle/oradata','+DATA1/jyzhao/tempfile/','/u01/app/oracle/oradata/' LOG_FILE_NAME_CONVERT='+DATA1/jyzhao/onlinelog','/u01/app/oracle/oradata','+FRA1/jyzhao/onlinelog','/u01/app/oracle/oradata' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/fra' *.db_name='jyzhao' *.db_recovery_file_dest='/u01/app/oracle/fra' *.db_recovery_file_dest_size=4621074432 *.diagnostic_dest='/u01/app/oracle' *.fal_server='jyzhao' *.log_archive_config='DG_CONFIG=(jyzhao_s,jyzhao)' *.log_archive_dest_3='SERVICE=jyzhao LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=jyzhao' *.log_archive_dest_state_3='enable' *.memory_max_target=314572800 *.memory_target=314572800 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' *.standby_file_management='AUTO'

3.5 启动备库到nomount
su - oracle

mkdir -p /u01/app/oracle/admin/jyzhao/adump
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/fra
startup nomount pfile='/tmp/pfile.ora' create spfile from pfile='/tmp/pfile.ora';

这里参数文件也可以只指定db_name='jyzhao_s' 但是这样的话就在duplicate时指定参数了。

3.6 使用duplicate搭建备库
例如：

--duplication script, running at jyzhao_s
--这里是在备库执行脚本；连接的是原环境RAC的节点2；
su - oracle
mkdir -p /home/oracle/mirgration_scripts
cd /home/oracle/mirgration_scripts
vi  /home/oracle/mirgration_scripts/duplicate.sql

run { allocate channel p1 type disk; allocate channel p2 type disk; allocate auxiliary channel s1 type disk; allocate auxiliary channel s2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
}

nohup rman target sys/oracle@jyzhao2 auxiliary sys/oracle@jyzhao_s @/home/oracle/mirgration_scripts/duplicate.sql log=/home/oracle/mirgration_scripts/d1.log &
--实验发现，duplicate主库时，如果日志传送链路没有打开，那么是恢复不成功滴！

3.7 在OPEN状态下进行日志应用
例如：

sqlplus / as sysdba --alter database recover managed standby database cancel; alter database open; alter database recover managed standby database using current logfile disconnect from session; --alter database recover managed standby database disconnect from session; --这种不需要standby logfile

3.8 确认ADG状态正常
主库日志类似如下信息：

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3 ******************************************************************
Wed Nov 30 04:48:42 2016
Archived Log entry 268 added for thread 2 sequence 115 ID 0x9603a618 dest 1:
LNS: Standby redo logfile selected for thread 2 sequence 116 for destination LOG_ARCHIVE_DEST_3

备库日志类似如下信息：

Media Recovery Waiting for thread 2 sequence 116 (in transit)

也可以查看主备库的当前SCN，或者查询备库的v$dataguard_stats视图。

--主备库都可查询当前SCN：
SQL> select current_scn||'' from v$database;

--备库查询v$dataguard_stats视图：
SQL> set lines 1000 SQL> select * from v$dataguard_stats;

以上检查确认没有问题，则说明ADG搭建成功完成。