RHEL6.4 + Oracle 11g DG测试环境快速搭建参考
2015-03-30 20:32 AlfredZhao 阅读(2195) 评论(0) 编辑 收藏 举报环境现状:
两台虚拟主机A和B:
1. A机器已安装ASM存储的Oracle 11g 实例
2. 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
|
一、前期准备
二、DG部署配置
- 2.1 primary database 配置
- 2.2 机器B:standby数据库配置
- 2.3 机器A操作 duplicate数据库到机器B
- 2.4 B机器 srvctl add数据库jyzhao_s
三、DG切换测试
一、前期准备
1.1. A机器打包拷贝/u01/app到B机器(包含了grid和oracle软件安装目录)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | # 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 : 05 B机器解压,解压前确保第二步操作已完成。 # pwd /u01 [root@JY-DBS u01]# ls app . tar . gz lost+found [root@JY-DBS u01]# tar -zxvf app . tar . gz 解压完成后,检查权限是正确的 # ls -lh total 3. 5G drwxrwxr-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 . gz |
1.2. B机器配置用户,系统参数,安装依赖包,用户环境变量,ASM磁盘
配置用户,系统参数,安装依赖包,用户环境变量
注:在第一步解压前做完,会发现权限都是正确的不用费心再改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | 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> startup ORA- 01078 : failure in processing system parameters ORA- 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 . cssd CRS- 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' succeeded CRS- 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> startup ASM instance started Total System Global Area 1135747072 bytes Fixed Size 2260728 bytes Variable Size 1108320520 bytes ASM Cache 25165824 bytes ORA- 15110 : no diskgroups mounted SQL> select status from v $instance ; STATUS ------------------------ STARTED col description for a35 col process for a35 set linesize 120 select sid, serial#, process, name, description from v $session join v $bgprocess using(paddr); col path for a45 col 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部署配置
2.1 primary database 配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | 确保将数据库的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 mount ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size 2253704 bytes Variable Size 1006636152 bytes Database Buffers 603979776 bytes Redo Buffers 7245824 bytes Database 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 list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/ 11.2.0 /db_1/dbs/arch Oldest online log sequence 12 Next log sequence to archive 14 Current 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 string db_recovery_file_dest_size big integer 0 SQL> 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; 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/orapwjyzhao orapwd file=$ORACLE_HOME/dbs/orapwjyzhao password=oracle entries=10 ignorecase=Y
grid用户配置监听
--listener.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DGL = (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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | JYZHAO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JY-DB)(PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jyzhao ) ) ) JYZHAO_S = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JY-DBS)(PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jyzhao_s) ) ) |
grid用户重启监听:
1 2 | lsnrctl stop dgl lsnrctl start dgl |
oracle用户测试连接:
1 2 3 4 5 6 7 8 9 10 11 | sqlplus sys/oracle@jyzhao as sysdba sqlplus sys/oracle@JY-DB/jyzhao_dgmgrl as sysdba sqlplus 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 重启primary shutdown immediate startup |
2.2 机器B:standby数据库配置
grid用户配置监听
--listener.ora
$ sqlplus sys/oracle@jyzhao_s as sysdba
vi duplicate_dg.sql
--listener.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DGL = (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.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | JYZHAO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JY-DB)(PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jyzhao ) ) ) JYZHAO_S = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JY-DBS)(PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = jyzhao_s) ) ) |
1 2 3 4 | echo "db_name=jyzhao" >> $ORACLE_HOME/dbs/initjyzhao_s . ora echo $ORACLE_SID sqlplus / as sysdba startup nomount |
1 2 3 4 5 | oracle用户测试连接 : sqlplus sys/oracle@jyzhao as sysdba sqlplus sys/oracle@jyzhao_s as sysdba sqlplus sys/oracle@JY-DBS/jyzhao_s_dgmgrl as sysdba sqlplus sys/oracle@JY-DBS/jyzhao_s as sysdba |
2.3 机器A操作 duplicate数据库到机器B
验证到机器B可以登录$ sqlplus sys/oracle@jyzhao_s as sysdba
vi duplicate_dg.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | duplicate target database for standby from active database DORECOVER spfile set 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 LGWR VALID_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' ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [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: JYZHAO (DBID= 2463175424 ) connected to auxiliary database: JYZHAO ( not mounted) RMAN> duplicate target database 2 > for standby 3 > from active database 4 > DORECOVER 5 > spfile 6 > 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 LGWR 10 > 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- 15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID= 111 device type =DISK …… Recovery Manager complete. [oracle@JY-DB ~]$ |
2.4 B机器 srvctl add数据库jyzhao_s
注:开始_s有问题,是因为添加指定的参数不足:
1 2 3 | srvctl add database -d jyzhao_s -o /u01/app/oracle/product/ 11.2.0 /db_1 -p +DATA1/JYZHAO_S/spfilejyzhao_s . ora -n jyzhao -i jyzhao_s srvctl modify database -d jyzhao_s -r PHYSICAL_STANDBY |
三、DG切换测试
3.1 手动switchover
1主库切换成standby,启动到mount
1 | select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$ database ; |
1 | alter database commit to switchover to physical standby; |
2.备库切换成primary,启动到open
1 | select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$ database ; |
1 | alter database commit to switchover to primary ; |
3.新的备库执行日志应用
1 | alter database recover managed standby database using current logfile disconnect from session; |
3.2 Data Guard Broker 快速switchover
1 2 3 4 5 6 7 8 9 10 | SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE SQL> alter system set dg_broker_start = true ; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE |
配置dgmgrl
1 2 3 4 5 6 7 8 9 10 11 12 13 | create 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 |
References
- Oracle® Data Guard Broker 11g Release 2 (11.2)
- Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2)
- http://t.askmaclean.com/thread-2530-1-1.html
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步