15005002056@139.com
oracle11gR2 部署DataGuard
1.1测试环境说明
* 操作系统 oracle linux 7.6 * 虚拟化软件系统 vmware workstion 17 * 数据库版本 oracle database 11g R2 * 主机名 dg11g.dgtest1.com dg11g.dgtest2.com * ip 规划 192.0.2.21 (DG1) 192.0.2.12(DG2) * Home目录规划 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/ * 单台vmware 虚拟机配置 2C 4G * 主库备库目录一致
1.2 部署oracle databas 11gR2
1 部署后按照数据库软件和dbca创建数据库实例 orcl 2 同理部署第二台操作系统和安装数据库软件(不需要创建实例)
1.2.1 准备工作
1.2.1.1 查看内存
grep SwapTotal /proc/meminfo grep MemTotal /proc/meminfo
1.2.1.2 安装缺失的软件包
yum install -y libstdc* libaio* make* sysstat*
1.2.1.3 修改参数
cat /proc/sys/net/ipv4/ip_local_port_range echo 9000 65500 > /proc/sys/net/ipv4/ip_local_port_range
1.2.1.3 创建用户组和用户,目录
# /usr/sbin/groupadd oinstall # /usr/sbin/groupadd -g 502 dba # /usr/sbin/useradd -u 502 -g oinstall -G dba oracle # passwd oracle # /usr/sbin/usermod -g oinstall -G dba oracle # mkdir -p /u01/app/oracle # chown -R oracle:oinstall /u01/app/oracle # chmod -R 775 /u01/app/oracle
1.3 主库配置
1.3.1 主库配置dataguard参数
alter database archivelog; alter system set standby_file_management=auto; alter system set dg_broker_start=true; alter system set temp_undo_enabled=true; alter system set local_listener='';
1.3.2 主库修改db_unique为orcl1
alter system set db_unique_name=orcl1 scope=spfile;
1.3.3 开归档,开闪回,强记日志
alter database flashback on; alter database force logging; alter database open; select log_mode,flashback_on,force_logging from v$database;
1.3.4 添加standby 日志文件
col member for a60; select group #,member from v$logfile; SQL> select group#,bytes/1024/1024 Mb from v$log; GROUP# MB ---------- ---------- 1 50 2 50 3 50 alter database add standby logfile group 4 '/u01/app/oracle/oradata/PROD5/redo04.log' size 50m; alter database add standby logfile group 5 '/u01/app/oracle/oradata/PROD5/redo05.log' size 50m; alter database add standby logfile group 6 '/u01/app/oracle/oradata/PROD5/redo06.log' size 50m; alter database add standby logfile group 7 '/u01/app/oracle/oradata/PROD5/redo07.log' size 50m; 查看 SQL> select group#,member from v$logfile; GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_3_krkr8b9r_.log 2 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_2_krkr89oh_.log 1 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_1_krkr8977_.log GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 4 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_4_krkr8bsl_.log 5 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_5_krkr8cd9_.log 6 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_6_krkr8co8_.log GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 7 /u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_7_krkr8cyh_.log
1.3.5 拷贝主库文件到备库主机
主库生成pfile文件
greate pfile from spfile;
拷贝pfile文件,密码文件到备用节点主机.
SCP /u01/app/oracle/product/11.2.0/dbhome_1/dbs initorcl.ora orapworcl 192.0.2.12:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
1.4 备库配置
1.4.1修改pfile文件
oracle@dg11 dbs]$ cat initorcl.ora orcl.__db_cache_size=620756992 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=620756992 orcl.__sga_target=922746880 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=251658240 orcl.__streams_pool_size=0 *.audit_file_dest='oracle@dg11 dbs]$ cat initorcl.ora orcl.__db_cache_size=620756992 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=620756992 orcl.__sga_target=922746880 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=251658240 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='test1.com' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4070572032 *.db_unique_name='ORCL2' 修改此参数ORCL2 *.dg_broker_start=TRUE *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=1542455296 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'
1.4.2 创建备库相关路径
mkdir -p /u01/app/oracle/admin/orcl/adump mkdir -p /u01/app/oracle/flash_recovery_area/orcl/ mkdir -p /u01/app/oracle/flash_recovery_area/
1.4.3 备库创建Spfile文件
create spfile from pfile
1.4.4 备库启动到nomount模式
startup nmount
1.5 配置监听
1.5.1 静态静听(主备库)
1.5.1.1 备库配置
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=ORCL2.test2.com) (SID_NAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC= (GLOBAL_DBNAME=ORCL2_DGMGRL.test2.com) (SID_NAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )
1.5.1.2 主库配置
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=ORCL1.test1.com) (SID_NAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) (SID_DESC= (GLOBAL_DBNAME=ORCL1_DGMGRL.test1.com) (SID_NAME=orcl) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )
1.5.2 动态静听配置
1.5.2.1 主库配置
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.test1.com) ) ) ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL1.test1.com) ) ) ORCL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL2.test2.com) ) )
1.5.2.2 备库配置
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.test1.com) ) ) ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL1.test1.com) ) ) ORCL2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL2.test2.com) ) )
1.6 Rman恢复备库
connect sys/oracle@orcl1 auxiliary database sys/oracle@orcl2 DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
1.7 配置DG_BROKER
1.7.1 配置主库配置
dgmgrl connect sys/oracle@orcl1 CREATE CONFIGURATION DG11 AS PRIMARY DATABASE IS orcl1 CONNECT IDENTIFIER IS orcl1
1.7.2 配置备库配置
ADD DATABASE orcl2 AS CONNECT IDENTIFIER IS orcl2
1.7.3 检查配置
DGMGRL> show configuration Configuration - dg11 Protection Mode: MaxAvailability Databases: orcl2 - Primary database orcl1 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS DGMGRL>
1.8 测试主备切换
1.8.1 检查备库情况
SQL> select PROTECTION_MODE,SWITCHOVER_STATUS,GUARD_STATUS,OPEN_MODE from v$database; PROTECTION_MODE SWITCHOVER_STATUS GUARD_S OPEN_MODE -------------------- -------------------- ------- -------------------- MAXIMUM PERFORMANCE NOT ALLOWED NONE READ ONLY WITH APPLY
1.8.2 测试主库情况
SQL> select PROTECTION_MODE,SWITCHOVER_STATUS,GUARD_STATUS,OPEN_MODE from v$database; PROTECTION_MODE SWITCHOVER_STATUS GUARD_S OPEN_MODE -------------------- -------------------- ------- -------------------- MAXIMUM AVAILABILITY TO STANDBY NONE READ WRITE
1.8.3 测试切换
查看现在状态 DGMGRL> show configuration Configuration - dg11 Protection Mode: MaxAvailability Databases: orcl1 - Primary database orcl2 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS 切换主备库 DGMGRL> switchover to orcl2 Performing switchover NOW, please wait... New primary database "orcl2" is opening... Operation requires shutdown of instance "orcl" on database "orcl1" Shutting down instance "orcl"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "orcl" on database "orcl1" Starting instance "orcl"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "orcl2" 查看最终状态 DGMGRL> show configuration; Configuration - dg11 Protection Mode: MaxAvailability Databases: orcl2 - Primary database orcl1 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS
1.9 配置自动故障切换
1.9.1 开启observer
DGMGRL> startup observer
1.9.2 配置faill-over
ENABLE FAST_START FAILOVER
1.9.3 查看配置状态
DGMGRL> show configuration Configuration - dg11 Protection Mode: MaxAvailability Databases: orcl2 - Primary database orcl1 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS
1.10 部署遇到的问题
1.10.1 Question 1
Linux centos7安装Oracle 11g 报错解决方案
问题 Error in invoking target ‘install’ of makefile ‘/opt/oracle/11g/ctx/lib/ins_ctx.mk’. See ‘/opt/oraInventory/logs/installActions2016-08-07_02-58-49AM.log’ for details. 查看日志后注意到日志有以下错误： INFO: /lib64/libstdc++.so.5: undefined reference to `memcpy@GLIBC_2.14' INFO: collect2: error: ld returned 1 exit status 解决方案： 在makefile中添加链接libnnz11库的参数 修改$ORACLE_HOME/sysman/lib/ins_emagent.mk，将 $(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL) -lnnz11 建议修改前备份原始文件 [oracle@ysserver ~]$ cd $ORACLE_HOME/sysman/lib [oracle@ysserver lib]$ cp ins_emagent.mk ins_emagent.mk.bak [oracle@ysserver lib]$ vi ins_emagent.mk 进入vi编辑器后 命令模式输入/NMECTL 进行查找，快速定位要修改的行 在后面追加参数-lnnz11 第一个是字母l 后面两个是数字1
1.10.2 Question 2
RMAN-05541: no archived logs found in target database
RMAN> duplicate target databaseto orcl from active database nofilenamecheck; Starting Duplicate Db at 23-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/23/201510:57:12 RMAN-05541: no archived logs found in target database 出现这种错误很可能是你刚刚将主数据库切换为归档模式然后紧接着就利用RMAN的duplicate创建standby数据库导致的。原因就在于主数据库切换为归档模式之后还未发生过切换，手动在主数据库上切换一次就可以了 SYS@orcl>alter system switch logfile;
1.10.3 Question 3
ORA-16826: apply service state is inconsistent
SOLUTION Workaround : Check on which node MRP process is running. On Standby Make sure Standby redo logs files are created on standby. Please reference Interaction Between the Data Guard Broker and a Data Guard Configuration Doc ID 249703.1 DGMGRL> edit database set state=apply-off; DGMGRL> edit database set state=apply-on; or SQL>alter database recover managed standby database cancel; SQL> alter database recover managed standby database using current logfile disconnect; Verify Broker Configuration DGMGRL> show configuration Configuration - orcl1_site Protection Mode: MaxPerformance Databases: orcl1 - Primary database dgorcl1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: **SUCCESS <<<<<<<<<<<<<<<<<< ** DGMGRL> show database dgorcl1 Database - dgorcl1 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: OFF Instance(s): dgorcl11 (apply instance) dgorcl12** Database Status: SUCCESS <<<<<<<<<<<<<<<<<<<<<<** How to avoid this issue 1. This Problem occurs if Managed Recovery is started manually which should not be done at all if there is an active Data Guard Broker Configuration**. ** Typically the Data Guard Broker should start Managed Recovery . 2. It can also happen if there are no Standby Redo Logs in Place or they are incorrect configured - reference Usage, Benefits and Limitations of Standby Redo Logs (SRL) Doc ID 219344.1
