RAC 主库配置单实例ADG

共计 8281 个字符，预计需要花费 21 分钟才能阅读完成。

写在前面：
最终实现环境：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
主库上配置：

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;

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

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_domain=''
*.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 搭建成功完成。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-12/137777.htm

RAC 主库配置单实例ADG

1. 主库准备工作

2. 物理备库准备工作

3. 创建物理备库

选择PHP与Python，可以考虑这三个问题

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

介绍ansible的Ad-hoc与commands模块

Linux安装使用pidstat命令以对进程数据进行监控

linux下使用tree命令以树形结构显示文件目录结构

简单了解dd、ext3grep、extundelete与linux数据恢复

运维监控工具zabbix概述

大神教你：在Ubuntu上安装MongoDB数据库

Linux安装使用pidstat命令以对进程数据进行监控

ansible常用模块简单介绍