Linux平台 Oracle 11g DG测试环境快速搭建参考

环境现状：两台虚拟主机A和B：1. A机器已安装ASM存储的Oracle 11g 实例参考：http://www.linuxidc.com/Linux/2015-04/115721.htm2. B机器已安装系统，配置以及目录结构均和A机器保持一致/u01 + 3块ASM盘DG部署规划：

	primary	standby
主机	JY-DB	JY-DBS
db_name	jyzhao	jyzhao
db_unique_name	jyzhao	jyzhao_s
instance_name	jyzhao	jyzhao_s
存储	+DATA1	+DATA1
归档	/u01/app/oracle/product/11.2.0/db_1/dbs/arch	/u01/app/oracle/product/11.2.0/db_1/dbs/arch
DGMGRL	jyzhao_dgmgrl	jyzhao_s_dgmgrl
GRID_HOME	/u01/app/11.2.0/grid	/u01/app/11.2.0/grid
ORACLE_HOME	/u01/app/oracle/product/11.2.0/db_1	/u01/app/oracle/product/11.2.0/db_1

一、前期准备1.1. A机器打包拷贝/u01/app到B机器（包含了grid和oracle软件安装目录）# tar -zcvf app.tar.gz app # scp app.tar.gz 192.168.99.160:/u01/root@192.168.99.160"s password:app.tar.gz 100% 3564MB 54.8MB/s 01:05B机器解压，解压前确保第二步操作已完成。 # pwd/u01[root@JY-DBS u01]# lsapp.tar.gz lost+found[root@JY-DBS u01]# tar -zxvf app.tar.gz解压完成后，检查权限是正确的# ls -lhtotal 3.5Gdrwxrwxr-x. 7 oracle oinstall 4.0K Mar 13 14:47 app-rw-r--r--. 1 root root 3.5G Mar 15 22:28 app.tar.gz1.2. B机器配置用户，系统参数，安装依赖包，用户环境变量，ASM磁盘配置用户，系统参数，安装依赖包，用户环境变量注：在第一步解压前做完，会发现权限都是正确的不用费心再改root用户执行脚本# /u01/app/oraInventory/orainstRoot.sh# /u01/app/11.2.0/grid/root.sh# /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
配置has需要建立asm磁盘组
环境变量：vi $ORACLE_HOME/dbs/init+ASM.ora *.asm_diskstring="/dev/mapper/ora*"*.asm_power_limit=1*.diagnostic_dest="/u01/app/grid"*.instance_type="asm"*.large_pool_size=12M*.remote_login_passwordfile="EXCLUSIVE" $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 16 10:51:02 2015 Copyright （c） 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startupORA-01078: failure in processing system parametersORA-29701: unable to connect to Cluster Synchronization Service $ crsctl stat res -t--------------------------------------------------------------------------------NAME TARGET STATE SERVER STATE_DETAILS --------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.ons OFFLINE OFFLINE jy-dbs --------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.cssd 1 OFFLINE OFFLINE ora.diskmon 1 OFFLINE OFFLINE ora.evmd 1 ONLINE ONLINE jy-dbs $ crsctl start resource ora.cssdCRS-2672: Attempting to start "ora.cssd" on "jy-dbs"CRS-2672: Attempting to start "ora.diskmon" on "jy-dbs"CRS-2676: Start of "ora.diskmon" on "jy-dbs" succeededCRS-2676: Start of "ora.cssd" on "jy-dbs" succeeded$ crsctl status res -t--------------------------------------------------------------------------------NAME TARGET STATE SERVER STATE_DETAILS --------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.ons OFFLINE OFFLINE jy-dbs --------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.cssd 1 ONLINE ONLINE jy-dbs ora.diskmon 1 OFFLINE OFFLINE ora.evmd 1 ONLINE ONLINE jy-dbs   $ sqlplus / as sysasm SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 16 10:55:39 2015 Copyright （c） 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startupASM instance started Total System Global Area 1135747072 bytesFixed Size 2260728 bytesVariable Size 1108320520 bytesASM Cache 25165824 bytesORA-15110: no diskgroups mounted SQL> select status from v$instance;STATUS------------------------STARTED col description for a35col process for a35set linesize 120select sid, serial#, process, name, description from v$session join v$bgprocess using（paddr）; col path for a45col name for a30 select group_number, disk_number, mount_status, name, path from v$asm_disk order by group_number, disk_number; GROUP_NUMBER DISK_NUMBER MOUNT_STATUS NAME PATH------------ ----------- -------------- ------------------------------ --------------------------------------------- 0 0 CLOSED /dev/mapper/ora_vg-lv_asm3 0 1 CLOSED /dev/mapper/ora_vg-lv_asm2 0 2 CLOSED /dev/mapper/ora_vg-lv_asm1 查看A机器的磁盘组信息：select group_number, name, type, total_mb, free_mb from v$asm_diskgroup GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB------------ ------------------------------------------------------------ ------------ ---------- ---------- 1 DATA1 EXTERN 30708 29017 B机器创建ASM磁盘组DATA1：select group_number, name, type, total_mb, free_mb from v$asm_diskgroup; no rows selected CREATE DISKGROUP data1 EXTERNAL REDUNDANCY DISK "/dev/mapper/ora*";Diskgroup created. select group_number, name, type, total_mb, free_mb from v$asm_diskgroup; GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB------------ ------------------------------------------------------------ ------------ ---------- ---------- 1 DATA1 EXTERN 30708 30654
至此，准备工作结束。二、DG部署配置1. primary database 配置
确保将数据库的force_logging打开，设置为归档模式，数据库闪回打开
SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DATA1/jyzhao/datafile/system.256.874084601+DATA1/jyzhao/datafile/sysaux.257.874084601+DATA1/jyzhao/datafile/undotbs1.258.874084601+DATA1/jyzhao/datafile/users.259.874084601 SQL> select force_logging from v$database;FOR---NO SQL> alter database force logging; Database altered. SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. SQL> startup mountORACLE instance started. Total System Global Area 1620115456 bytesFixed Size 2253704 bytesVariable Size 1006636152 bytesDatabase Buffers 603979776 bytesRedo Buffers 7245824 bytesDatabase mounted.SQL> alter database archivelog; Database altered. SQL> alter database flashback on;alter database flashback on*ERROR at line 1:ORA-38706: Cannot turn on FLASHBACK DATABASE logging.ORA-38709: Recovery Area is not enabled. SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/product/11.2.0/db_1/dbs/archOldest online log sequence 12Next log sequence to archive 14Current log sequence 14 $ mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs/arch SQL> alter database flashback on;alter database flashback on*ERROR at line 1:ORA-38706: Cannot turn on FLASHBACK DATABASE logging.ORA-38709: Recovery Area is not enabled. SQL> show parameter db_recover NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0SQL> alter system set db_recovery_file_dest_size=5G; System altered. SQL> alter system set db_recovery_file_dest="/u01/app/oracle/product/11.2.0/db_1/dbs/arch"; System altered. SQL> select status from v$instance; STATUS------------MOUNTED SQL> alter database flashback on; Database altered.
按之前的规划设置数据库的参数主要是db_unique_name, log_archive_config, log_archive_dest_1, log_archive_dest_2, log_archive_format, fal_server, fal_client, standby_file_management的设置，standby logfile的添加，密码文件的创建。
alter system set db_unique_name="jyzhao" scope=spfile; alter system set log_archive_config="DG_CONFIG=（jyzhao,jyzhao_s）" scope=spfile; alter system set log_archive_dest_1="LOCATION=/u01/app/oracle/product/11.2.0/db_1/dbs/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=jyzhao" scope=spfile; alter system set log_archive_dest_2="SERVICE=jyzhao_s ASYNC LGWR VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=jyzhao_s" scope=spfile;<br>alter system set log_archive_format="arch_%r_%t_%s.arc" scope=spfile;alter system set fal_server=jyzhao_s scope=spfile;alter system set fal_client=jyzhao scope=spfile;alter system set standby_file_management=AUTO;alter database add standby logfile group 4 size 50M;alter database add standby logfile group 5 size 50M;alter database add standby logfile group 6 size 50M;alter database add standby logfile group 7 size 50M; rm /u01/app/oracle/product/11.2.0/db_1/dbs/orapwjyzhaoorapwd file=$ORACLE_HOME/dbs/orapwjyzhao password=oracle entries=10 ignorecase=Y
grid用户配置监听
--listener.oraDGL = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DB）（PORT = 1521））） SID_LIST_DGL = （SID_LIST = （SID_DESC = （GLOBAL_DBNAME = jyzhao）（ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1）（SID_NAME = jyzhao）    ）    （SID_DESC =    （GLOBAL_DBNAME = jyzhao_dgmgrl）    （ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1）    （SID_NAME = jyzhao））   ）    ADR_BASE_DGL = /u01/app/grid
oracle用户配置tnsnames.ora
--tnsnames.ora
LINUXIDC = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DB）（PORT = 1521））（CONNECT_DATA = （SERVER = DEDICATED）（SERVICE_NAME = jyzhao ））） LINUXIDC_S = （DESCRIPTION =    （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DBS）（PORT = 1521））    （CONNECT_DATA =    （SERVER = DEDICATED）    （SERVICE_NAME = jyzhao_s）））
grid用户重启监听：lsnrctl stop dgllsnrctl start dgl
oracle用户测试连接：sqlplus sys/oracle@jyzhao as sysdbasqlplus sys/oracle@JY-DB/jyzhao_dgmgrl as sysdbasqlplus sys/oracle@JY-DB/jyzhao as sysdba SQL> show parameter audi NAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest string /u01/app/oracle/admin/jyzhao/a dump重启primaryshutdown immediatestartup
2.机器B：standby数据库配置：grid用户配置监听
--listener.oraDGL = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DBS）（PORT = 1521））） SID_LIST_DGL = （SID_LIST = （SID_DESC = （GLOBAL_DBNAME = jyzhao_s）（ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1）（SID_NAME = jyzhao_s）    ）    （SID_DESC =    （GLOBAL_DBNAME = jyzhao_s_dgmgrl）    （ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1）    （SID_NAME = jyzhao_s）<br> ）   ）    ADR_BASE_DGL = /u01/app/grid
grid用户启动监听
$ lsnrctl start dgl
oracle用户配置tnsnames.ora
--tnsnames.oraLINUXIDC = （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DB）（PORT = 1521））（CONNECT_DATA = （SERVER = DEDICATED）（SERVICE_NAME = jyzhao ））） LINUXIDC_S = （DESCRIPTION =    （ADDRESS = （PROTOCOL = TCP）（HOST = JY-DBS）（PORT = 1521））    （CONNECT_DATA =    （SERVER = DEDICATED）    （SERVICE_NAME = jyzhao_s））） echo "db_name=jyzhao" >> $ORACLE_HOME/dbs/initjyzhao_s.oraecho $ORACLE_SID sqlplus / as sysdbastartup nomount oracle用户测试连接：sqlplus sys/oracle@jyzhao as sysdbasqlplus sys/oracle@jyzhao_s as sysdbasqlplus sys/oracle@JY-DBS/jyzhao_s_dgmgrl as sysdbasqlplus sys/oracle@JY-DBS/jyzhao_s as sysdba
3.机器A操作 duplicate数据库到机器B
验证到机器B可以登录
$ sqlplus sys/oracle@jyzhao_s as sysdba
vi duplicate_dg.sqlduplicate target databasefor standbyfrom active databaseDORECOVERspfileset db_unique_name="jyzhao_s"set log_archive_dest_1="location=/u01/app/oracle/product/11.2.0/db_1/dbs/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES）DB_UNIQUE_NAME=jyzhao_s"set log_archive_dest_2="SERVICE=jyzhao ASYNC LGWRVALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=jyzhao"set standby_file_management="AUTO"set fal_server="jyzhao"set fal_client="jyzhao_s"set control_files="+DATA1"set memory_target="0"set sga_target="600M";  [oracle@JY-DB ~]$ rman target / auxiliary sys/oracle@jyzhaos cmdfile=duplicate_standby.sql Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 16 23:21:37 2015 Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LINUXIDC （DBID=2463175424）connected to auxiliary database: LINUXIDC （not mounted） RMAN> duplicate target database2> for standby3> from active database4> DORECOVER5> spfile6> set db_unique_name="jyzhao_s"7> set log_archive_dest_1="location=/u01/app/oracle/product/11.2.0/db_1/dbs/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES）8> DB_UNIQUE_NAME=jyzhao_s"9> set log_archive_dest_2="SERVICE=MACDBN ASYNC LGWR10> VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=jyzhao"11> set standby_file_management="AUTO"12> set fal_server="jyzhao"13> set fal_client="jyzhao_s"14> set control_files="+DATA1"15> set memory_target="0"16> set sga_target="600M";17>Starting Duplicate Db at 16-MAR-15using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=111 device type=DISK……Recovery Manager complete.[oracle@JY-DB ~]$
4. B机器 srvctl add数据库jyzhao_s 注：开始_s有问题，是因为添加指定的参数不足：
srvctl add database -d jyzhao_s -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA1/LINUXIDC_S/spfilejyzhao_s.ora -n jyzhao -i jyzhao_ssrvctl modify database -d jyzhao_s -r PHYSICAL_STANDBY
三、DG切换测试3.1 手动switchover1主库切换成standby，启动到mountselect OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;alter database commit to switchover to physical standby;2.备份切换成primary，启动到openselect OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;alter database commit to switchover to primary;
3.新的备份执行日志应用
alter database recover managed standby database using current logfile disconnect from session;
3.2 Data Guard Broker 快速switchover
SQL> show parameter dg_broker_start NAME TYPE VALUE------------------------------------ ----------- ------------------------------dg_broker_start boolean FALSESQL> alter system set dg_broker_start = true; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE------------------------------------ ----------- ------------------------------dg_broker_start boolean TRUE配置dgmgrlcreate CONFIGURATION jydb as primary database is jyzhao CONNECT IDENTIFIER IS jyzhao;
add database jyzhao_s as CONNECT IDENTIFIER IS jyzhao_s MAINTAINED AS PHYSICAL;
enable configuration;
show configuration;
switchover to jyzhao_s;
switchover to jyzhao;
show database verbose jydb更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址