ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

goldengate DML 单向复制

内容发布:wjl7813795| 发布时间:2015-3-25 20:44:33
本实验在两台oracle 10g数据库服务器间实现goldengate 单向复制的配置!
一:环境介绍
db1:source端
ip地址:192.168.1.121
数据库版本:10.2.0.1 64 bit
操作系统版本:OEL 5.8 x86_64t
Oracle_sid=source
ogg版本:Oracle GoldenGate V11.2.1.0.1 for Oracle 10g on Linux x86-64
db2: target端
ip地址:192.168.1.122
数据库版本:10.2.0.1 64 bit
操作系统版本:OEL 5.8 x86_64
ORACLE_SID=devdb
ogg版本:Oracle GoldenGate V11.2.1.0.1 for Oracle 10g on Linux x86-64

====准备工作===
设置环境变量 和 tnsnames.ora  文件

node2-> cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export PS1="`/bin/hostname -s`-> "
export EDITOR=vi
export ORACLE_SID=devdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib  
export GGATE=$ORACLE_BASE/ogg
umask 022
export NLS_LANG=american_america.AL32UTF8
export LANG=en_US
alias  sql='rlwrap sqlplus'
alias  sqlplus='rlwrap sqlplus'
alias  lsnrctl='rlwrap lsnrctl'
alias  rman='rlwrap rman'
node2-> source .bash_profile    huozhe shi  node2-> . ./.bash_profile
node2-> mkdir $GGATE
node2-> cp -r fbo_ggs_Linux_x64_ora10g_64bit.tar $GGATE
node1-> pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
node1-> cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
wjl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.121)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sourcedb)
    )
  )
devdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )
node1-> tnsping sourcedb
node1-> tnsping devdb

(2)数据库的归档模式设置! 源端和目标端都一样
源端
SQL> select log_mode from v$database ;
LOG_MODE
------------
ARCHIVELOG
SQL> select supplemental_log_data_min from v$database;  
SUPPLEME
--------
NO
SQL> alter database force logging;  
Database altered.
SQL> alter database add supplemental log data;  
Database altered.
SQL> select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME   OPEN_MODE  FOR SUPPLEME
--------- ---------- --- --------
SOURCEDB  READ WRITE YES YES
创建goldengate表空间、用户及赋予相关权限
SQL> select name from V$datafile
  2  union all
  3  select name from V$tempfile
  4  /
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/sourcedb/system01.dbf
/u01/app/oracle/oradata/sourcedb/undotbs01.dbf
/u01/app/oracle/oradata/sourcedb/sysaux01.dbf
/u01/app/oracle/oradata/sourcedb/users01.dbf
/u01/app/oracle/oradata/sourcedb/example01.dbf
/u01/app/oracle/oradata/sourcedb/temp01.dbf
6 rows selected.
SQL> create tablespace  ogg datafile '/u01/app/oracle/oradata/sourcedb/ogg.dbf' size 100m autoextend on;
Tablespace created.
SQL> create user goldengate identified by goldengate default tablespace ogg temporary tablespace temp account unlock;
User created.
SQL> grant connect,resource,dba to goldengate;  
Grant succeeded.

目标端
SQL> create tablespace  ogg datafile '/u01/app/oracle/oradata/devdb/ogg.dbf' size 100m autoextend on;
Tablespace created.
SQL>  create user ogg identified by ogg default tablespace ogg temporary tablespace temp account unlock;
User created.
SQL> grant connect,resource,dba to ogg;
Grant succeeded.
(3) 安装goldengate 软件

node1-> cd $GGATE
node1-> ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (node1) 1> create subdirs  
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files                /u01/app/oracle/ogg/dirprm: already exists
Report files                   /u01/app/oracle/ogg/dirrpt: created
Checkpoint files               /u01/app/oracle/ogg/dirchk: created
Process status files           /u01/app/oracle/ogg/dirpcs: created
SQL script files               /u01/app/oracle/ogg/dirsql: created
Database definitions files     /u01/app/oracle/ogg/dirdef: created
Extract data files             /u01/app/oracle/ogg/dirdat: created
Temporary files                /u01/app/oracle/ogg/dirtmp: created
Stdout files                   /u01/app/oracle/ogg/dirout: created
GGSCI (node1) 4> edit params ./GLOBALS  全局性设置可有可无  
GGSCHEMA goldengate
checkpointtable goldengate.checkpointtable                     -------------这一点是目标端的全局性设置

GGSCI (node1) 4> view param mgr                      ------------源端和目标端的端口设置需要一致
port 7839
--DYNAMICPORTLIST 7840-7850
--AUTOSTART EXTRACT *
--AUTORESTART EXTRACT *
--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
--LAGREPORTHOURS 1
--LAGINFOMINUTES 30
--LAGCRITICALMINUTES 45
GGSCI (node1) 5> start mgr
Manager started.
GGSCI (node1) 8> info mgr
Manager is running (IP port node1.7839).


GGSCI (node1) 6> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING  

(4) 准备测试用户
源端
create user user01 identified by user01;
grant connect,resource to user01;
conn user01/user01
create table t1(id int primary key,name varchar2(30));

     
目标端:      
create user user01 identified by user01;
grant connect,resource to user01;
conn user01/user01
create table t1(id int primary key,name varchar2(30));
(5)在源端表级附加日志
GGSCI (node1) 10> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (node1) 11> add trandata user01.*
Logging of supplemental redo data enabled for table USER01.T1.
GGSCI (node1) 12> info trandata user01.*   
Logging of supplemental redo log data is enabled for table USER01.T1.
Columns supplementally logged for table USER01.T1: ID.
     
=======
(6) 源端 添加 extract 进程
GGSCI (node1) 13> add ext exta, tranlog , begin now
EXTRACT added.

GGSCI (node1) 15> add exttrail /u01/app/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20
EXTTRAIL added.

GGSCI (node1) 16> add extract dpea, EXTTRAILSOURCE /u01/app/oracle/ogg/dirdat/la   ----datapump 进程
EXTRACT added.

GGSCI (node1) 17> add rmttrail /u01/app/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20
RMTTRAIL added.

(7)编辑 extract 进程 及 datapump 进程
GGSCI (node1) 19> edit param exta
GGSCI (node1) 20> view param exta
EXTRACT exta
setenv ( NLS_LANG = AMERICAN_AMERICA.AL32UTF8 )
setenv (ORACLE_SID = sourcedb)
USERID goldengate, PASSWORD goldengate
EXTTRAIL /u01/app/oracle/ogg/dirdat/la
dynamicresolution
table user01.*;
GGSCI (node1) 21> start extract exta
Sending START request to MANAGER ...
EXTRACT EXTA starting

GGSCI (node1) 22> info exta
EXTRACT    EXTA      Last Started 2015-03-01 09:06   Status RUNNING
Checkpoint Lag       00:18:44 (updated 00:00:04 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-03-01 08:48:15  Seqno 5, RBA 3456000
                     SCN 0.0 (0)

GGSCI (node1) 23> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     STOPPED     DPEA        00:00:00      00:10:09   
EXTRACT     RUNNING     EXTA        00:18:44      00:00:07  

GGSCI (node1) 30> edit param dpea
GGSCI (node1) 31> view param dpea
extract dpea
setenv ( NLS_LANG = AMERICAN_AMERICA.AL32UTF8 )
passthru
--REPORT AT 01:59
--reportrollover at 02:00
rmthost 192.168.1.122, mgrport 7839, compress
rmttrail /u01/app/oracle/ogg/dirdat/ra
dynamicresolution
table user01.*;

GGSCI (node1) 32> start extract dpea
Sending START request to MANAGER ...
EXTRACT DPEA starting

GGSCI (node1) 34> info dpea
EXTRACT    DPEA      Last Started 2015-03-01 09:17   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/app/oracle/ogg/dirdat/la000000
                     First Record  RBA 0

GGSCI (node1) 35> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     DPEA        00:00:00      00:00:08   
EXTRACT     RUNNING     EXTA        00:00:00      00:00:03

(8)
目标端 添加 replicate进程
GGSCI (node2) 11> add rep repa, exttrail /u01/oracle/ogg/dirdat/ra, nodbcheckpoint
REPLICAT added.
GGSCI (node2) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
REPLICAT    STOPPED     REPA        00:00:00      00:00:04   

GGSCI (node2) 13> edit param repa
GGSCI (node2) 14> view param repa
replicat repa
---setenv (NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8')
----setenv (ORACLE_SID = devdb)
userid ogg, password ogg
--REPORT AT 01:59
--reportrollover at 02:00
reperror default,abend
discardfile /u01/app/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
assumetargetdefs
--allownoopupdates
dynamicresolution
--INSERTALLRECORDS
map user01.*, target user01.*;
不知道为啥加上语言环境字符集 该进程居然不能起来,很奇怪!

(9) 源端测试用户插入数据测试
SQL> show user
USER is "USER01"
SQL> select * from cat;
TABLE_NAME         TABLE_TYPE
------------------------------ -----------
T1          TABLE
SQL> select * from t1;
no rows selected
SQL> desc t1
Name        Null?    Type
----------------------------------------- -------- ----------------------------
ID        NOT NULL NUMBER(38)
NAME          VARCHAR2(30)
SQL> insert into t1 values(0,'xiaoming');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- ------------------------------
  0 xiaoming

GGSCI (node1) 37> sh ls -ltr dirdat
total 8
-rw-rw-rw- 1 oracle oinstall 1020 Mar  1 09:10 la000000
-rw-rw-rw- 1 oracle oinstall 1214 Mar  1 09:54 la000001

SQL> insert into t1 values(1,'jack');
1 row created.
SQL> commit;
Commit complete.

GGSCI (node1) 38> stats dpea
Sending STATS request to EXTRACT DPEA ...
Start of Statistics at 2015-03-01 09:59:41.
Output to /u01/app/oracle/ogg/dirdat/ra:
Extracting from USER01.T1 to USER01.T1:
*** Total statistics since 2015-03-01 09:54:24 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Daily statistics since 2015-03-01 09:54:24 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Hourly statistics since 2015-03-01 09:54:24 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Latest statistics since 2015-03-01 09:54:24 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
End of Statistics.

GGSCI (node1) 39> stats exta
Sending STATS request to EXTRACT EXTA ...
Start of Statistics at 2015-03-01 09:59:53.
Output to /u01/app/oracle/ogg/dirdat/la:
Extracting from USER01.T1 to USER01.T1:
*** Total statistics since 2015-03-01 09:54:23 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Daily statistics since 2015-03-01 09:54:23 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Hourly statistics since 2015-03-01 09:54:23 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
*** Latest statistics since 2015-03-01 09:54:23 ***
Total inserts                               2.00
Total updates                               0.00
Total deletes                               0.00
Total discards                              0.00
Total operations                            2.00
End of Statistics.

GGSCI (node1) 40> sh ls -ltr dirdat
total 8
-rw-rw-rw- 1 oracle oinstall 1020 Mar  1 09:10 la000000
-rw-rw-rw- 1 oracle oinstall 1345 Mar  1 09:58 la000001



上一篇:goldengate的双活实现
下一篇:Goldengate同步序列问题
回复

使用道具 举报

内容发布:njcnchome1| 发布时间:2015-3-26 00:32:41
不错不错,好文档...........
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表