写在前面:
最终实现环境: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
数据库设置为 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;
确保已成功安装同版本数据库软件、监听程序;
配置静态监听,这里的_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.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)
log_archive_config
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 搭建成功完成。
来源: http://www.linuxidc.com/Linux/2016-12/137777.htm