Oracle - 11GR2 - RAC - to - RAC - DATAGUARD - 配置方案

ORACLE 11GR2 RAC TO RAC DATAGUARD 配置方案

--by lxx

1.1 前言

本文记录oracle 11gr2 Dataguard physical standby的配置过程。 主备数据库都是oracle 11gr2 rac环境，参数文件，控制文件，数据文件和归档日志都保存在ASM DG上面。采用Maximum Performance方式，即日志同步采取lgwr noaffirm方式。

1.2 环境规划

环境如下：

操作系统 主机名 数据库版本 集群版本 DB_NAME DB_UNIQUE_NAME instance_name service_name ORACLE_HOMEorcl1,orcl2 orcl /oracle/app/grid orcl1,orcl2 orclstd /oracle/app/grid 主库 Oracle Linux Server release 6.3 orcl1，orcl2 oracle 11.2.0.3 GI 11.2.0.3 orcl orcl 备库 Oracle Linux Server release 6.3 orclstd1，orclstd2 oracle 11.2.0.3 GI 11.2.0.3 orcl orclstd (GI) ORACLE_HOME(DB) 数据存储方式 文件管理 spfile目录 控制文件目录 数据文件目录 归档日志目录 监听端口 /oracle/app/oracle/product/11.2.0/db_1 ASM OMF +DATA +DATA +DATA +DATA 1521 /oracle/app/oracle/product/11.2.0/db_1 ASM OMF +DATA +DATA +DATA +DATA 1521

1.3 环境准备

主库：

? 双节点 11g R2 Grid Infrastructure (11.2.0.3)已经安装配置完毕； ? 双节点Oracel RAC Software (11.2.0.3)已经安装配置完毕； ? 集群数据库”orcl”已经创建于ASM 上； ? 数据库运行于归档模式；

备库：

? 双节点 11g R2 Grid Infrastructure (11.2.0.3)已经安装配置完毕 ? 双节点Oracel RAC Software (11.2.0.3)已经安装配置完毕；

主备库：

在各节点/etc/hosts加入两端主机名IP映射关系。

192.168.0.11 orcl1 192.168.0.13 orcl1-vip 10.0.0.11 orcl1-priv 192.168.0.12 orcl2 192.168.0.14 orcl2-vip 10.0.0.12 orcl2-priv 192.168.0.15 cluster-scan 192.168.0.21 orclstd1 192.168.0.23 orclstd1-vip 10.0.0.21 orclstd1-priv 192.168.0.22 orclstd2 192.168.0.24 orclstd2-vip 10.0.0.22 orclstd2-priv 192.168.0.25 clusterstd-scan

1.4 主库配置

1.4.1 打开force logging

SQL> ALTER DATABASE FORCE LOGGING; 1.4.2创建 Standby Redo 日志

Standy log 的推荐数目为：

(# of online redo logs per primary instance + 1) * # of instances

目前有两个节点，每个节点有两个日志组，每个日志成员大小为 64m，每个日志组里都有一个成员。所以下面给每个节点添加三个日志组：

select group#,thread#,bytes/1024/1024 \ from v$log; alter system set standby_file_management=manual scope=both sid='*'; alter database add standby logfile thread 1 group 5 ('+DATA') size 64m; alter database add standby logfile thread 1 group 6 ('+DATA') size 64m; alter database add standby logfile thread 1 group 7 ('+DATA') size 64m; alter database add standby logfile thread 2 group 8 ('+DATA') size 64m; alter database add standby logfile thread 2 group 9 ('+DATA') size 64m; alter database add standby logfile thread 2 group 10 ('+DATA') size 64m; alter system set standby_file_management=auto scope=both sid='*'; select group#,thread#,status from v$standby_log; 1.4.3 设置参数文件

--设置主备库数据文件路径对应关系 alter system set db_file_name_convert='+DATA','+DATA' scope=spfile sid='*'; alter system set log_file_name_convert='+DATA','+DATA' scope=spfile sid='*'; alter system set fal_client='orcl' scope=spfile sid='*'; alter system set fal_server='orclstd' scope=spfile sid='*'; --log_archive_config指定数据库唯一名 alter system set log_archive_config='DG_CONFIG=(orcl,orclstd)' scope=spfile sid='*'; alter system set log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile sid='*'; alter sid='*'; alter system set log_archive_dest_state_1=ENABLE scope=spfile sid='*'; alter system set log_archive_dest_state_2=ENABLE scope=spfile sid='*'; alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*'; alter system set log_archive_max_processes=10 scope=spfile sid='*'; alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile sid='*'; alter system set standby_file_management=auto scope=spfile sid='*'; --上面是归档切换时候采用apply redo的方式，如果改为lgwr传输方式，则使用下面语句： alter --重启数据库生效: $ srvctl stop database -d orcl $ srvctl start database -d orcl system set log_archive_dest_2='SERVICE=orclstd LGWR ASYNC NOAFIRM VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd' scope=spfile sid='*'; system set log_archive_dest_2='SERVICE=orclstd DB_UNIQUE_NAME=orclstd' ARCH scope=spfile VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 1.4.4 设置DB的tnsnames.ora

--增加主备库的服务 orcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (UR=A) ) ) orcl1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A) ) ) orcl2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl2-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A) ) ) orclstd = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = clusterstd-scan)(PORT = 1521)) (CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) orclstd1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstd1-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) orclstd2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstd2-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) 1.5 备库配置

1.5.1 创建备库pfile 参数文件

在主库生成pfile, 并复制备库节点上。并修改以下参数:

*.audit_file_dest='/oracle/app/oracle/admin/orcl/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.0.0' *.control_files='+DATA/orclstd/controlfile/current.256.869873351' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA','+DATA' *.db_name='orcl' *.db_unique_name='orclstd' *.diagnostic_dest='/oracle/app/oracle' *.fal_client='orclstd' *.fal_server='orcl' orcl1.instance_number=1 orcl2.instance_number=2 *.log_archive_config='DG_CONFIG=(orcl,orclstd)' *.log_archive_dest_1='LOCATION=+DATA DB_UNIQUE_NAME=orclstd' *.log_archive_dest_2='SERVICE=orcl DB_UNIQUE_NAME=orcl' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=10 *.log_file_name_convert='+DATA','+DATA' *.open_cursors=300 *.pga_aggregate_target=156237824 *.processes=150 *.remote_listener='clusterstd-scan:1521' *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=469762048 *.standby_file_management='AUTO' orcl2.thread=2 orcl1.thread=1 orcl1.undo_tablespace='UNDOTBS1' orcl2.undo_tablespace='UNDOTBS2' ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

1.5.2 创建相关目录

在备库的两个节点上创建目录：

mkdir -p /oracle/app/oracle/admin/orcl/adump chmod 755 /oracle/app/oracle/admin/orcl/adump

1.5.3 创建密码文件

从主库节点对应复制密码文件到备库节点上

$cd $ORACLE_HOME/dbs $scp orapw* 192.168.0.21:/oracle/app/oracle/product/11.2.0/db_1/dbs $scp orapw* 192.168.0.22:/oracle/app/oracle/product/11.2.0/db_1/dbs --修改第二个节点的密码文件名 $mv orapworcl1 orapworcl2

1.5.4 设置DB的tnsnames.ora

orcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (UR=A) ) ) orcl1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A) ) ) orcl2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl2-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (UR=A) ) ) orclstd = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = clusterstd-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) orclstd1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstd1-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) orclstd2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orclstd2-vip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstd) (UR=A) ) ) 1.5.5 配置监听

添加静态注册服务名

vi /oracle/app/11.2.0/grid/network/admin/listener.ora --添加： SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclstd ) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1) (SID_NAME =orcl1) ) ) ADR_BASE_LISTENER = /oracle/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent --重启监听器并查看： srvctl stop listener -n orclstd1 srvctl start listener -n orclstd1 lsnrctl status

1.5.6 创建ASM路径

$asmcmd cd data mkdir orclstd 1.5.7 启动备库

--使用修改好的参数文件启动standby实例到nomount状态 sqlplus \startup nomount pfile='/home/oracle/initorclstd.ora'

1.5.8 复制库

使用11G的新特性，在线方式的RMAN DUPLICATE技术。

--在主库节点上执行 --创建复制脚本 $ more duplicate_act_standby.rcv duplicate target database for standby from active database spfile set db_unique_name='orclstd' set control_files='+DATA/orclstd/controlfile/current.256.869873351' set db_file_name_convert='+DATA','+DATA' set log_file_name_convert='+DATA','+DATA' set log_archive_config='DG_CONFIG=(orcl,orclstd)' set log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd' set log_archive_dest_2='SERVICE=orcl ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' set fal_client='orclstd' set fal_server='orcl' set remote_listener='clusterstd-scan:1521' set standby_file_management='AUTO' nofilenamecheck; --执行复制操作 $rman --复制完成后，备库实例自动启动到mount状态 select instance_name,status from v$instance; --检查数据文件和redo日志等相关信息 set line 1000 col name format a50 col member format a50 select name from v$datafile_header; select * from v$log; target sys/oracle@orcl1 auxiliary sys/oracle@orclstd1 cmdfile=duplicate_act_standby.rcv log=rman.log select * from v$logfile; show parameter control; show parameter spfile; show parameter db_name; show parameter db_unique_name;

1.5.9 备库RAC配置

--配置初始化参数文件 vi $ORACLE_HOME/dbs/initorcl1.ora spfile='+DATA/orclstd/parameterfile/spfileorcl.ora' vi $ORACLE_HOME/dbs/initorcl2.ora spfile='+DATA/orclstd/parameterfile/spfileorcl.ora' --备库注册到ocr oracle用户执行： srvctl add database -d orclstd -n orcl -o /oracle/app/oracle/product/11.2.0/db_1 -p +DATA/orclstd/parameterfile/spfileorcl.ora -r physical_standby srvctl add instance -d orclstd -i orcl1 -n orclstd1 srvctl add instance -d orclstd -i orcl2 -n orclstd2 srvctl config database -d orclstd alter database recover managed standby database cancel; shutdown immediate; srvctl start database -d orclstd select name, database_role, open_mode from gv$database; --database_role 为PHYSICAL STANDBY， open_mode 为read only 1.6 Dataguard 日常管理 1.6.1 DG数据库检查

--检查主备库的状态 set line 1000 col db_unique_name format a10 col open_mode format a15 col database_role format a20 col STANDBY_BECAME_PRIMARY_SCN format '999999999999999' select name, db_unique_name, open_mode, protection_mode, database_role, switchover_status, standby_became_primary_scn from gv$database; --主库database_role 为PRIMARY， open_mode 为READ WRITE --备库database_role 为PHYSICAL STANDBY， open_mode 为READ ONLY --检查是否有gap日志 select inst_id,thread#,low_sequence#,high_sequence# from gv$archive_gap; --检查远程归档日志的状态 set pagesize 999 set line 1000 col dest_name format a30 col error format a30 col DESTINATION format a30 select inst_id, status, type, recovery_mode, SYNCHRONIZATION_STATUS, SYNCHRONIZED, GAP_STATUS, error, ARCHIVED_THREAD#, APPLIED_THREAD#, ARCHIVED_SEQ#, APPLIED_SEQ# from gv$archive_dest_status where DEST_NAME='LOG_ARCHIVE_DEST_2'; --查询主库有哪些日志没有被传输到备库 SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#); --检查dg 日志传输情况 col MESSAGE format a50 SELECT * FROM V$DATAGUARD_STATUS; --检查归档日志目标和进程 select target,archiver,process from v$archive_dest where dest_id=2; --检查dg 相关进程状态 select process, client_process, sequence#, status from v$managed_standby; --检查主备库日志是否同步 --检查主备库日志序列 Select max(sequence#) from v$log; --检查归档日志序列 select sequence#, first_time, next_time, applied from v$archived_log; --检查redo日志历史记录 select thread#, max (sequence#) from v$log_history group by thread#; --检查归档日志信息 select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

1.6.2 DG数据库启动关闭

--启动 先起备库，后起主库 srvctl start database –d dbname 备库启动日志应用服务 sql> alter database recover managed standby database using current logfile disconnect; --关闭 先关主库，后关备库 srvctl stop database –d dbname 备库关闭日志应用服务 sql> alter database recover managed standby database cancel; srvctl stop database –d dbname 1.6.3 主备库切换

在RAC状态下，主从数据库的切换时主备库只能有一个实例在运行状态。在切换前需关掉其他节点，在切换完成后再启动其他节点。

--查询主备库状态 sql> select database_role,switchover_status from v$database; --将主库切换到备库状态 sql> alter database commit to switchover to physical standby [with session shutdown]; --如果主库状态为 “sessions active”， 则需要在命令后加上 with session shutdown 语句。 --重起原数据库到mount 状态。 srvctl stop database -d dbname srvctl start database -d dbname --在原备库上执行切换命令 sql> alter database commit to switchover to primary with session shutdown; --shutdown 并startup 新主数据库。 srvctl stop database -d dbname srvctl start database -d dbname --在新的备库启动 redo apply. sql>alter database recover managed standby database using current logfile disconnect; 1.6.4 故障转移

在某些情况下，不能执行正常的切换操作。如源端主数据库停掉，不能在短时间内恢复或数据库无法恢复甚至需要重建，需要启用备库。值得注意的是，这

种操作是destructive操作，一旦执行，备库需要重建。运行此操作需慎重。

sql> alter database recover managed standby database finish force; sql> alter database activate standby database [skip standby logfile]; sql> shutdown immediate; sql> startup mount; sql> alter database open resetlogs;

1.6.5 数据同步测试

主库： create tablespace test datafile '+data' size 128m autoextend off; create user test identified by test default tablespace test; grant connect,resource to test; conn test/test create table t1 as select * from user_objects; select count(*) from test.t1; conn / as sysdba alter system checkpoint; alter system archive log current; 备库： select count(*) from test.t1; 主库： drop user test cascade; drop tablespace test including contents and datafiles; alter system checkpoint; alter system archive log current; 备库： select count(*) from test.t1; select name from v$tablespace where name='TEST';

1.7 参考

Technical_Architecture_of_11g_R2_RAC_primary_to_RAC_standby_DataGuard配置指南(中

文)

Data Guard Concepts and Administration

Step By Step of Configuring Oracle 11gR2 (11.2.0.1) RAC to RAC Dataguard

本文来源：https://www.bwwdw.com/article/vbto.html