合 OGG从入门到高可用系列
- 前言部分
- 导读
- 实验环境介绍
- 实验部分
- 实验目标
- 准备工作，在source和target端都配置
- 确保/etc/hosts配置正确
- dbca创建2套新环境用于实验
- 配置环境变量、listener和tnsnames.ora文件
- 数据库设置
- 安装ogg软件，配置及启动mgr管理进程，source和target端做相同的操作
- 安装OGG软件
- 配置及启动mgr管理进程
- 准备测试用户及表
- 初始化数据
- source端添加extract进程
- target端添加replicat进程
- source端启动extract进程，查看日志输出
- target端查看日志，并且数据量验证
- 配置OGG的实时同步功能
- 开启hr用户下所有表的附加日志
- 在source上添加及配置extract进程，进程的名字不能超过8个字符；添加trail文件，文件名前缀不能超过2个字符
- 添加并配置extract进程
- 添加trail文件，文件名前缀不能超过2个字符
- 添加并启动pump进程
- 在target端添加检查表，配置replicat进程
- 添加检查表
- 添加并启动replicat进程
- 测试OGG的实时同步功能
- 源库插入数据分析
- 抽取trail文件中可打印的内容分析
- 删除测试
【OGG】OGG的单向DML复制配置(一)
前言部分
导读
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① OGG的单向DML实时复制功能
② 数据库的静默安装
③ OGG的安装
④ OGG下数据的初始化工作
实验环境介绍
| 项目 | source db | target db |
|---|---|---|
| db 类型 | 单实例 | 单实例 |
| db version | 11.2.0.3 | 11.2.0.3 |
| db 存储 | FS type | FS type |
| ORACLE_SID | ogg1 | ogg2 |
| db_name | ogg1 | ogg2 |
| 主机IP地址： | 192.168.59.129 | 192.168.59.130 |
| OS版本及kernel版本 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
| OGG版本 | 11.2.1.0.1 64位 | 11.2.1.0.1 64位 |
| OS hostname | orcltest | rhel6_lhr |
实验部分
实验目标
配置2台服务器，搭建OGG,实现hr用户下的数据dml复制功能。
准备工作，在source和target端都配置
确保/etc/hosts配置正确
首先在source和target端确保/etc/hosts中的主机名到ip地址的解析是正确的，且127.0.0.1没有指向本主机名，这里以target端为例：
[oracle@rhel6_lhr ~]$ hostname
rhel6_lhr
[oracle@rhel6_lhr ~]$ more /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.59.130 rhel6_lhr
[oracle@rhel6_lhr ~]$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:C0:A7:56
inet addr:192.168.59.130 Bcast:192.168.59.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fec0:a756/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:650153 errors:0 dropped:0 overruns:0 frame:0
TX packets:160873 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:824479355 (786.2 MiB) TX bytes:16495312 (15.7 MiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:104417 errors:0 dropped:0 overruns:0 frame:0
TX packets:104417 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:26855022 (25.6 MiB) TX bytes:26855022 (25.6 MiB)
[oracle@rhel6_lhr ~]$
dbca创建2套新环境用于实验
source端：
[oracle@orcltest ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ogg1 -sid ogg1 -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/ogg1/ogg1.log" for further details.
[oracle@orcltest ~]$
[oracle@orcltest ~]$ ps -ef|grep ora_
oracle 20620 1 0 10:18 ? 00:00:00 ora_pmon_ogg1
oracle 20622 1 0 10:18 ? 00:00:00 ora_psp0_ogg1
oracle 20624 1 0 10:19 ? 00:00:00 ora_vktm_ogg1
oracle 20628 1 0 10:19 ? 00:00:00 ora_gen0_ogg1
oracle 20630 1 0 10:19 ? 00:00:00 ora_diag_ogg1
oracle 20632 1 0 10:19 ? 00:00:00 ora_dbrm_ogg1
oracle 20634 1 0 10:19 ? 00:00:00 ora_dia0_ogg1
oracle 20636 1 0 10:19 ? 00:00:00 ora_mman_ogg1
oracle 20638 1 0 10:19 ? 00:00:00 ora_dbw0_ogg1
oracle 20640 1 0 10:19 ? 00:00:00 ora_lgwr_ogg1
oracle 20642 1 0 10:19 ? 00:00:00 ora_ckpt_ogg1
oracle 20644 1 0 10:19 ? 00:00:00 ora_smon_ogg1
oracle 20646 1 0 10:19 ? 00:00:00 ora_reco_ogg1
oracle 20648 1 0 10:19 ? 00:00:00 ora_mmon_ogg1
oracle 20650 1 0 10:19 ? 00:00:00 ora_mmnl_ogg1
oracle 20652 1 0 10:19 ? 00:00:00 ora_d000_ogg1
oracle 20654 1 0 10:19 ? 00:00:00 ora_s000_ogg1
oracle 20690 1 0 10:19 ? 00:00:00 ora_arc0_ogg1
oracle 20692 1 0 10:19 ? 00:00:00 ora_arc1_ogg1
oracle 20694 1 0 10:19 ? 00:00:00 ora_arc2_ogg1
oracle 20696 1 0 10:19 ? 00:00:00 ora_arc3_ogg1
oracle 20698 1 0 10:19 ? 00:00:00 ora_qmnc_ogg1
oracle 20729 1 0 10:19 ? 00:00:00 ora_cjq0_ogg1
oracle 20744 1 0 10:19 ? 00:00:00 ora_q000_ogg1
oracle 20746 1 0 10:19 ? 00:00:00 ora_q001_ogg1
oracle 20768 19494 0 10:21 pts/0 00:00:00 grep ora_
[oracle@orcltest ~]$
target端：
[oracle@rhel6_lhr ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ogg2 -sid ogg2 -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u01/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u01/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
31% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ogg2/ogg2.log" for further details.
You have new mail in /var/spool/mail/oracle
[oracle@rhel6lhr ~]$ ps -ef|grep ora\
oracle 12227 1 0 20:12 ? 00:00:00 ora_pmon_ogg2
oracle 12229 1 0 20:12 ? 00:00:00 ora_psp0_ogg2
oracle 12231 1 1 20:12 ? 00:00:03 ora_vktm_ogg2
oracle 12235 1 0 20:12 ? 00:00:00 ora_gen0_ogg2
oracle 12237 1 0 20:12 ? 00:00:00 ora_diag_ogg2
oracle 12239 1 0 20:12 ? 00:00:00 ora_dbrm_ogg2
oracle 12241 1 0 20:12 ? 00:00:00 ora_dia0_ogg2
oracle 12243 1 0 20:12 ? 00:00:00 ora_mman_ogg2
oracle 12245 1 0 20:12 ? 00:00:00 ora_dbw0_ogg2
oracle 12247 1 0 20:12 ? 00:00:00 ora_lgwr_ogg2
oracle 12249 1 0 20:12 ? 00:00:00 ora_ckpt_ogg2
oracle 12251 1 0 20:12 ? 00:00:00 ora_smon_ogg2
oracle 12253 1 0 20:12 ? 00:00:00 ora_reco_ogg2
oracle 12255 1 0 20:12 ? 00:00:00 ora_mmon_ogg2
oracle 12257 1 0 20:12 ? 00:00:00 ora_mmnl_ogg2
oracle 12259 1 0 20:12 ? 00:00:00 ora_d000_ogg2
oracle 12261 1 0 20:12 ? 00:00:00 ora_s000_ogg2
oracle 12289 1 0 20:12 ? 00:00:00 ora_arc0_ogg2
oracle 12291 1 0 20:12 ? 00:00:00 ora_arc1_ogg2
oracle 12293 1 0 20:12 ? 00:00:00 ora_arc2_ogg2
oracle 12295 1 0 20:12 ? 00:00:00 ora_arc3_ogg2
oracle 12297 1 0 20:12 ? 00:00:00 ora_qmnc_ogg2
oracle 12330 1 0 20:12 ? 00:00:00 ora_cjq0_ogg2
oracle 12347 1 0 20:13 ? 00:00:00 ora_q000_ogg2
oracle 12349 1 0 20:13 ? 00:00:00 ora_q001_ogg2
oracle 12492 7669 0 20:15 pts/2 00:00:00 grep ora_
[oracle@rhel6_lhr ~]$
配置环境变量、listener和tnsnames.ora文件
该步骤确保ORACLE和OGG的环境变量正确，listener启动即可。
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 | [oracle@orcltest ~]$ more .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export PATH=$PATH:/sbin/:$HOME/bin # for oracle user unset USERNAME export EDITOR=vi export ORACLE_SID=ogg1 export ORACLE_BASE=/u02/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib export PATH=$ORACLE_HOME/bin:$PATH export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib export TNS_ADMIN=$ORACLE_HOME/network/admin umask 022 #export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8" export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export SQLPATH=$ORACLE_HOME/sqlplus/admin alias sqlplus='rlwrap sqlplus' alias rman='rlwrap rman' alias asmcmd='rlwrap asmcmd' alias alert_log='tail -200f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log' export OGG_HOME=/u01/gg11 export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$LD_LIBRARYPATH alias ggsci='rlwrap ggsci' export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss' [oracle@orcltest ~]$ [oracle@orcltest ~]$ more $TNS_ADMIN/tnsnames.ora # tnsnames.ora Network Configuration File: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. OGG1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg1) ) ) OGG2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg2) ) ) [oracle@orcltest ~]$ [oracle@orcltest ~]$ tnsping ogg1 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-JUN-2015 10:29:54 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg1))) OK (0 msec) [oracle@orcltest ~]$ tnsping ogg2 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-JUN-2015 10:30:02 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg2))) OK (0 msec) [oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:30:13 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@orcltest ~]$ sqlplus sys/lhr@ogg2 as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:30:21 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@orcltest ~]$ |
数据库设置
这里需要确保source端数据库运行在归档模式，且开启最小数据库附加日志，可以不用打开force logging,但是建议打开这个功能，然后在source和target端都创建用于复制的数据库账号ggusr，为了方便，这里直接赋予dba权限，如果对数据库安全要求高，可以去查询ogg文档，赋予复制需要的最小权限！
source端：
[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 10:31:52 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ogg1
db_unique_name string ogg1
global_names boolean FALSE
instance_name string ogg1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ogg1
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;
NAME SUPPLEME FOR
--------- -------- ---
OGG1 NO NO
SQL> alter database add supplemental log data ;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;
NAME SUPPLEME FOR
