goldengate DML 单向复制

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:528

本实验在两台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

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】