--使用该安装手册时建议将该手册最大化,并关闭txt自动换行功能
--使用xshell登录远端服务器(源数据端和目标数据端数据库服务器)
--使用oracle用户登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ mkdir goldengate
[oracle@sgdb1 ~]$ ll
总计 24
drwxr-xr-x 2 oracle oinstall 4096 01-12 20:25 goldengate
[oracle@sgdb1 ~]$ vi .bash_profile
--按i键进入编辑状态。新增如下内容,无需考虑是否重复
GG_HOME=/home/oracle/goldengate
LD_LIBRARY_PATH=/home/oracle/goldengate:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export GG_HOME LD_LIBRARY_PATH
--按esc键,按shift+: 组合键,输入wq并回车
--上传安装介质:fbo_ggs_Linux_x64_ora10g_64bit.tar至/home/oracle/goldengate文件夹下
[oracle@sgdb1 ~]$ cd goldengate/
[oracle@sgdb1 goldengate]$ ll
总计 267768
-rw-r--r-- 1 oracle oinstall 273920000 01-12 20:33 fbo_ggs_Linux_x64_ora10g_64bit.tar
[oracle@sgdb1 goldengate]$ tar xvf fbo_ggs_Linux_x64_ora10g_64bit.tar
[oracle@sgdb1 goldengate]$ ls
bcpfmt.tpl ddl_filter.sql ddl_session1.sql demo_ora_create.sql jagent.sh pw_agent_util.sh
bcrypt.txt ddlgen ddl_session.sql demo_ora_insert.sql keygen remove_seq.sql
cfg ddl_informix.tpl ddl_setup.sql demo_ora_lob_create.sql libicudata.so.38 replicat
chkpt_ora_create.sql ddl_mss.tpl ddl_sqlmx.tpl demo_ora_misc.sql libicui18n.so.38 retrace
cobgen ddl_mysql.tpl ddl_status.sql demo_ora_pk_befores_create.sql libicuuc.so.38 reverse
convchk ddl_nopurgeRecyclebin.sql ddl_staymetadata_off.sql demo_ora_pk_befores_insert.sql libxerces-c.so.28 role_setup.sql
db2cntl.tpl ddl_nssql.tpl ddl_staymetadata_on.sql demo_ora_pk_befores_updates.sql libxml2.txt sequence.sql
ddl_access.tpl ddl_ora10.sql ddl_sybase.tpl dirjar logdump server
ddl_cleartrace.sql ddl_ora10upCommon.sql ddl_tandem.tpl emsclnt marker_remove.sql sqlldr.tpl
ddlcob ddl_ora11.sql ddl_tracelevel.sql extract marker_setup.sql tcperrs
ddl_db2_os390.tpl ddl_ora9.sql ddl_trace_off.sql fbo_ggs_Linux_x64_ora10g_64bit.tar marker_status.sql UserExitExamples
ddl_db2.tpl ddl_oracle.tpl ddl_trace_on.sql freeBSD.txt mgr usrdecs.h
ddl_ddl2file.sql ddl_pin.sql defgen ggMessage.dat notices.txt zlib.txt
ddl_disable.sql ddl_purgeRecyclebin.sql demo_more_ora_create.sql ggsci params.sql
ddl_enable.sql ddl_remove.sql demo_more_ora_insert.sql help.txt prvtclkm.plb
[oracle@sgdb1 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (sgdb1) 1> create subdirs
Creating subdirectories under current directory /home/oracle/goldengate
Parameter files /home/oracle/goldengate/dirprm: created
Report files /home/oracle/goldengate/dirrpt: created
Checkpoint files /home/oracle/goldengate/dirchk: created
Process status files /home/oracle/goldengate/dirpcs: created
SQL script files /home/oracle/goldengate/dirsql: created
Database definitions files /home/oracle/goldengate/dirdef: created
Extract data files /home/oracle/goldengate/dirdat: created
Temporary files /home/oracle/goldengate/dirtmp: created
Veridata files /home/oracle/goldengate/dirver: created
Veridata Lock files /home/oracle/goldengate/dirver/lock: created
Veridata Out-Of-Sync files /home/oracle/goldengate/dirver/oos: created
Veridata Out-Of-Sync XML files /home/oracle/goldengate/dirver/oosxml: created
Veridata Parameter files /home/oracle/goldengate/dirver/params: created
Veridata Report files /home/oracle/goldengate/dirver/report: created
Veridata Status files /home/oracle/goldengate/dirver/status: created
Veridata Trace files /home/oracle/goldengate/dirver/trace: created
Stdout files /home/oracle/goldengate/dirout: created
GGSCI (sgdb1) 2>
--以上步骤在源端和目标端分别执行一次
--源数据端配置
--新建linux窗口,使用oracle用户再次登录稽查监控数据库主机操作系统
--ogg为oracle实例名,需修改
[oracle@sgdb1 ~]$ export ORACLE_SID=ogg
[oracle@sgdb1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jan 12 20:42:29 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--确认数据库是否处于归档模式,如已开启请跳过开启步骤,如不确定请按如下步骤依次执行命令
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 419430920 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14680064 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--至此归档模式已开启
--开启数据库的附加日志,如已开启请跳过开启步骤,如不确定请按如下步骤依次执行命令
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
--至此数据库的附加日志已开启
--创建数据库用户,'/data/suypower/goldengate/goldengate02.dbf'为表空间存放路径
SQL> create tablespace goldengate datafile '/data/suypower/goldengate/goldengate02.dbf' size 10240M;
Tablespace created.
SQL> create user goldengate identified by "goldengate" default tablespace goldengate;
User created.
SQL> grant resource, connect, dba to goldengate;
Grant succeeded.
--打开plsql,使用goldengate用户登录ogg(ogg为goldengate用户所在实例)
--创建测试表,各网省实施时此时应完成准备区建表操作
create table ogg_test
( ogg_id int primary key,
name char(30)
);
--插入测试数据,各网省实施时此时应完成准备区数据初始化工作
insert into goldengate.ogg_test values(1,'test1');
insert into goldengate.ogg_test values(2,'test2');
insert into goldengate.ogg_test values(3,'test3');
--切换linux窗口至ggsci下
--上传mgr.prm、extk1.prm、dpk1.prm至/home/oracle/goldengate/dirprm目录下
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 2> edit params mgr
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 3> edit params extk1
--连接到数据库
GGSCI (sgdb1) 6> dblogin userid goldengate,password goldengate
Successfully logged into database.
--指定表
GGSCI (sgdb1) 7> add trandata GOLDENGATE.OGG_TEST
***** added.
--增加抽取进程组
GGSCI (sgdb1) 8> add extract extk1,tranlog,begin now
EXTRACT added.
--建立抽取进程和队列文件关联关系
GGSCI (sgdb1) 9> add exttrail ./dirdat/k1, extract extk1, megabytes 200
EXTTRAIL added.
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 4> edit params dpk1
--增加传输进程组
GGSCI (sgdb1) 10> add extract dpk1, exttrailsource ./dirdat/k1
EXTRACT added.
--建立传输进程和目标端队列文件关联关系
GGSCI (sgdb1) 11> ADD RMTTRAIL ./dirdat/k1, EXTRACT dpk1, MEGABYTES 200
RMTTRAIL added.
--启动管理进程、抽取进程和传输进程
GGSCI (sgdb1) 14> start mgr
Manager started.
GGSCI (sgdb1) 15> start extract extk1
Sending START request to MANAGER ...
EXTRACT EXTK1 starting
GGSCI (sgdb1) 16> start extract dpk1
Sending START request to MANAGER ...
EXTRACT DPK1 starting
GGSCI (sgdb1) 25> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPK1 00:00:00 00:15:56
EXTRACT RUNNING EXTK1 00:00:00 00:00:01
--数据初始化,该步骤与goldengate无关
--新建linux窗口,使用oracle用户再次登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ pwd
/home/oracle
[oracle@sgdb1 ~]$ mkdir oggdata
[oracle@sgdb1 ~]$ ll
总计 24
drwxr-xr-x 16 oracle oinstall 4096 01-13 09:18 goldengate
drwxr-xr-x 2 oracle oinstall 4096 01-13 09:50 oggdata
--切换至刚才打开的plsql,使用goldengate用户登录ogg(ogg为goldengate用户所在实例)
--执行以下语句,建立ggs和sglaw用户
create user ggs identified by "ggs" default tablespace goldengate;
grant resource, connect, dba to ggs;
create user sglaw identified by "sglaw" default tablespace goldengate;
grant resource, connect, dba to sglaw;
--创建directory用于执行数据泵操作
CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/home/oracle/oggdata';
grant read ,write on DIRECTORY DATA_PUMP to ggs;
grant read ,write on DIRECTORY DATA_PUMP to sglaw;
--源端获取数据库当前的SCN,并记下SCN号
select dbms_flashback.get_system_change_number from dual; --576717
--切换至刚才新建的linux窗口,使用oracle用户登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ cd oggdata/
[oracle@sgdb1 oggdata]$ pwd
/home/oracle/oggdata
-- sglaw/sglaw@ogg 不解释可能需要修改,576717为刚才获取的SCN号
[oracle@sgdb1 oggdata]$ expdp sglaw/sglaw@ogg directory=DATA_PUMP dumpfile=DP_SGLAW.DMP flashback_scn=576717
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 13 January, 2012 10:06:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SGLAW"."SYS_EXPORT_SCHEMA_01": sglaw/********@ogg directory=DATA_PUMP dumpfile=DP_SGLAW.DMP flashback_scn=576717
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Master table "SGLAW"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SGLAW.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/oggdata/DP_SGLAW.DMP
Job "SGLAW"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:06:50
--以下操作在目标端执行
--新建linux窗口,使用oracle用户登录目标端稽查监控数据库主机操作系统
[oracle@cepri3 ~]$ mkdir oggdata
[oracle@cepri3 ~]$ cd oggdata/
[oracle@cepri3 oggdata]$ pwd
/home/oracle/oggdata
--将源数据端生成的DP_SGLAW.DMP上传到目标端/home/oracle/oggdata
--登录数据库
[oracle@cepri3 oggdata]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 13 10:13:25 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--执行以下语句,建立ggs和sglaw用户
SQL> create user ggs identified by "ggs";
User created.
SQL> grant resource, connect, dba to ggs;
Grant succeeded.
SQL> create user sglaw identified by "sglaw";
User created.
SQL> grant resource, connect, dba to sglaw;
Grant succeeded.
SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cepri3 oggdata]$ impdp sglaw/sglaw DUMPFILE=DATA_PUMP:DP_SGLAW.DMP
Import: Release 10.2.0.5.0 - 64bit Production on Friday, 13 January, 2012 10:36:54
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SGLAW"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SGLAW"."SYS_IMPORT_FULL_01": sglaw/******** DUMPFILE=DATA_PUMP:DP_SGLAW.DMP
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SGLAW" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
...
--数据初始化结束,使用goldengate用户登录目标端数据库查看数据是否已经正确初始化
--目标端配置
[oracle@cepri3 oggdata]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 13 10:13:25 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--创建数据库用户,'/oracle/oradata/goldengate/goldengate02.dbf'为表空间存放路径
SQL> create tablespace goldengate datafile '/oracle/oradata/goldengate/goldengate02.dbf' size 10240M;
Tablespace created.
SQL> create user goldengate identified by "goldengate" default tablespace goldengate;
User created.
SQL> grant resource, connect, dba to goldengate;
Grant succeeded.
SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--如果目标端goldengate还没有安装,请回本文档开头重新来过
[oracle@cepri3 ~]$ cd /home/oracle/goldengate/
[oracle@cepri3 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
--GLOBALS配置
GGSCI (cepri3) 1>GGSCI (cepri3) 1> edit params ./GLOBALS
--全文如下:
CHECKPOINTTABLE goldengate.ggchkptable
--退出ggsci并重新登录ggsci
GGSCI (cepri3) 2> quit
[oracle@cepri3 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
--登录数据库
GGSCI (cepri3) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (cepri3) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (goldengate.ggchkptable)...
Successfully created checkpoint table GOLDENGATE.GGCHKPTABLE.
--上传mgr.prm、repk1.prm至/home/oracle/goldengate/dirprm目录下
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 2> edit params mgr
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 3> edit params extk1
--添加复制进程组
GGSCI (cepri3) 5> add replicat repk1, exttrail ./dirdat/k1
REPLICAT added.
--启动管理进程和复制进程
GGSCI (cepri3) 6> start mgr
Manager started.
GGSCI (cepri3) 7> start replicat repk1, aftercsn 576717
Sending START request to MANAGER ...
REPLICAT REPK1 starting
GGSCI (cepri3) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPK1 00:00:00 00:00:01
--测试
--打开源数据端plsql,用goldengate用户登录ogg
select * from ogg_test;--记住结果集
--打开目标端plsql,用goldengate用户登录
select * from ogg_test;--记住结果集
--切换至源数据端plsq,插入测试数据
insert into ogg_test (OGG_ID, NAME) values (4, 'test4');
--切换至目标端plsql,
select * from ogg_test;--目标端也自动插入了测试数据
--切换至源数据端plsq,修改测试数据
update ogg_test set name='test' where ogg_id=4;
--切换至目标端plsql,
select * from ogg_test;--目标端也自动修改了测试数据
--切换至源数据端plsq,删除测试数据
delete from ogg_test where ogg_id=4;
--切换至目标端plsql,
select * from ogg_test;--目标端也自动删除了测试数据
--测试通过!thanks hanqingwang!
--使用xshell登录远端服务器(源数据端和目标数据端数据库服务器)
--使用oracle用户登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ mkdir goldengate
[oracle@sgdb1 ~]$ ll
总计 24
drwxr-xr-x 2 oracle oinstall 4096 01-12 20:25 goldengate
[oracle@sgdb1 ~]$ vi .bash_profile
--按i键进入编辑状态。新增如下内容,无需考虑是否重复
GG_HOME=/home/oracle/goldengate
LD_LIBRARY_PATH=/home/oracle/goldengate:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export GG_HOME LD_LIBRARY_PATH
--按esc键,按shift+: 组合键,输入wq并回车
--上传安装介质:fbo_ggs_Linux_x64_ora10g_64bit.tar至/home/oracle/goldengate文件夹下
[oracle@sgdb1 ~]$ cd goldengate/
[oracle@sgdb1 goldengate]$ ll
总计 267768
-rw-r--r-- 1 oracle oinstall 273920000 01-12 20:33 fbo_ggs_Linux_x64_ora10g_64bit.tar
[oracle@sgdb1 goldengate]$ tar xvf fbo_ggs_Linux_x64_ora10g_64bit.tar
[oracle@sgdb1 goldengate]$ ls
bcpfmt.tpl ddl_filter.sql ddl_session1.sql demo_ora_create.sql jagent.sh pw_agent_util.sh
bcrypt.txt ddlgen ddl_session.sql demo_ora_insert.sql keygen remove_seq.sql
cfg ddl_informix.tpl ddl_setup.sql demo_ora_lob_create.sql libicudata.so.38 replicat
chkpt_ora_create.sql ddl_mss.tpl ddl_sqlmx.tpl demo_ora_misc.sql libicui18n.so.38 retrace
cobgen ddl_mysql.tpl ddl_status.sql demo_ora_pk_befores_create.sql libicuuc.so.38 reverse
convchk ddl_nopurgeRecyclebin.sql ddl_staymetadata_off.sql demo_ora_pk_befores_insert.sql libxerces-c.so.28 role_setup.sql
db2cntl.tpl ddl_nssql.tpl ddl_staymetadata_on.sql demo_ora_pk_befores_updates.sql libxml2.txt sequence.sql
ddl_access.tpl ddl_ora10.sql ddl_sybase.tpl dirjar logdump server
ddl_cleartrace.sql ddl_ora10upCommon.sql ddl_tandem.tpl emsclnt marker_remove.sql sqlldr.tpl
ddlcob ddl_ora11.sql ddl_tracelevel.sql extract marker_setup.sql tcperrs
ddl_db2_os390.tpl ddl_ora9.sql ddl_trace_off.sql fbo_ggs_Linux_x64_ora10g_64bit.tar marker_status.sql UserExitExamples
ddl_db2.tpl ddl_oracle.tpl ddl_trace_on.sql freeBSD.txt mgr usrdecs.h
ddl_ddl2file.sql ddl_pin.sql defgen ggMessage.dat notices.txt zlib.txt
ddl_disable.sql ddl_purgeRecyclebin.sql demo_more_ora_create.sql ggsci params.sql
ddl_enable.sql ddl_remove.sql demo_more_ora_insert.sql help.txt prvtclkm.plb
[oracle@sgdb1 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (sgdb1) 1> create subdirs
Creating subdirectories under current directory /home/oracle/goldengate
Parameter files /home/oracle/goldengate/dirprm: created
Report files /home/oracle/goldengate/dirrpt: created
Checkpoint files /home/oracle/goldengate/dirchk: created
Process status files /home/oracle/goldengate/dirpcs: created
SQL script files /home/oracle/goldengate/dirsql: created
Database definitions files /home/oracle/goldengate/dirdef: created
Extract data files /home/oracle/goldengate/dirdat: created
Temporary files /home/oracle/goldengate/dirtmp: created
Veridata files /home/oracle/goldengate/dirver: created
Veridata Lock files /home/oracle/goldengate/dirver/lock: created
Veridata Out-Of-Sync files /home/oracle/goldengate/dirver/oos: created
Veridata Out-Of-Sync XML files /home/oracle/goldengate/dirver/oosxml: created
Veridata Parameter files /home/oracle/goldengate/dirver/params: created
Veridata Report files /home/oracle/goldengate/dirver/report: created
Veridata Status files /home/oracle/goldengate/dirver/status: created
Veridata Trace files /home/oracle/goldengate/dirver/trace: created
Stdout files /home/oracle/goldengate/dirout: created
GGSCI (sgdb1) 2>
--以上步骤在源端和目标端分别执行一次
--源数据端配置
--新建linux窗口,使用oracle用户再次登录稽查监控数据库主机操作系统
--ogg为oracle实例名,需修改
[oracle@sgdb1 ~]$ export ORACLE_SID=ogg
[oracle@sgdb1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jan 12 20:42:29 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--确认数据库是否处于归档模式,如已开启请跳过开启步骤,如不确定请按如下步骤依次执行命令
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 419430920 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14680064 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
--至此归档模式已开启
--开启数据库的附加日志,如已开启请跳过开启步骤,如不确定请按如下步骤依次执行命令
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
--至此数据库的附加日志已开启
--创建数据库用户,'/data/suypower/goldengate/goldengate02.dbf'为表空间存放路径
SQL> create tablespace goldengate datafile '/data/suypower/goldengate/goldengate02.dbf' size 10240M;
Tablespace created.
SQL> create user goldengate identified by "goldengate" default tablespace goldengate;
User created.
SQL> grant resource, connect, dba to goldengate;
Grant succeeded.
--打开plsql,使用goldengate用户登录ogg(ogg为goldengate用户所在实例)
--创建测试表,各网省实施时此时应完成准备区建表操作
create table ogg_test
( ogg_id int primary key,
name char(30)
);
--插入测试数据,各网省实施时此时应完成准备区数据初始化工作
insert into goldengate.ogg_test values(1,'test1');
insert into goldengate.ogg_test values(2,'test2');
insert into goldengate.ogg_test values(3,'test3');
--切换linux窗口至ggsci下
--上传mgr.prm、extk1.prm、dpk1.prm至/home/oracle/goldengate/dirprm目录下
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 2> edit params mgr
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 3> edit params extk1
--连接到数据库
GGSCI (sgdb1) 6> dblogin userid goldengate,password goldengate
Successfully logged into database.
--指定表
GGSCI (sgdb1) 7> add trandata GOLDENGATE.OGG_TEST
***** added.
--增加抽取进程组
GGSCI (sgdb1) 8> add extract extk1,tranlog,begin now
EXTRACT added.
--建立抽取进程和队列文件关联关系
GGSCI (sgdb1) 9> add exttrail ./dirdat/k1, extract extk1, megabytes 200
EXTTRAIL added.
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 4> edit params dpk1
--增加传输进程组
GGSCI (sgdb1) 10> add extract dpk1, exttrailsource ./dirdat/k1
EXTRACT added.
--建立传输进程和目标端队列文件关联关系
GGSCI (sgdb1) 11> ADD RMTTRAIL ./dirdat/k1, EXTRACT dpk1, MEGABYTES 200
RMTTRAIL added.
--启动管理进程、抽取进程和传输进程
GGSCI (sgdb1) 14> start mgr
Manager started.
GGSCI (sgdb1) 15> start extract extk1
Sending START request to MANAGER ...
EXTRACT EXTK1 starting
GGSCI (sgdb1) 16> start extract dpk1
Sending START request to MANAGER ...
EXTRACT DPK1 starting
GGSCI (sgdb1) 25> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPK1 00:00:00 00:15:56
EXTRACT RUNNING EXTK1 00:00:00 00:00:01
--数据初始化,该步骤与goldengate无关
--新建linux窗口,使用oracle用户再次登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ pwd
/home/oracle
[oracle@sgdb1 ~]$ mkdir oggdata
[oracle@sgdb1 ~]$ ll
总计 24
drwxr-xr-x 16 oracle oinstall 4096 01-13 09:18 goldengate
drwxr-xr-x 2 oracle oinstall 4096 01-13 09:50 oggdata
--切换至刚才打开的plsql,使用goldengate用户登录ogg(ogg为goldengate用户所在实例)
--执行以下语句,建立ggs和sglaw用户
create user ggs identified by "ggs" default tablespace goldengate;
grant resource, connect, dba to ggs;
create user sglaw identified by "sglaw" default tablespace goldengate;
grant resource, connect, dba to sglaw;
--创建directory用于执行数据泵操作
CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/home/oracle/oggdata';
grant read ,write on DIRECTORY DATA_PUMP to ggs;
grant read ,write on DIRECTORY DATA_PUMP to sglaw;
--源端获取数据库当前的SCN,并记下SCN号
select dbms_flashback.get_system_change_number from dual; --576717
--切换至刚才新建的linux窗口,使用oracle用户登录稽查监控数据库主机操作系统
[oracle@sgdb1 ~]$ cd oggdata/
[oracle@sgdb1 oggdata]$ pwd
/home/oracle/oggdata
-- sglaw/sglaw@ogg 不解释可能需要修改,576717为刚才获取的SCN号
[oracle@sgdb1 oggdata]$ expdp sglaw/sglaw@ogg directory=DATA_PUMP dumpfile=DP_SGLAW.DMP flashback_scn=576717
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 13 January, 2012 10:06:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SGLAW"."SYS_EXPORT_SCHEMA_01": sglaw/********@ogg directory=DATA_PUMP dumpfile=DP_SGLAW.DMP flashback_scn=576717
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Master table "SGLAW"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SGLAW.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/oggdata/DP_SGLAW.DMP
Job "SGLAW"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:06:50
--以下操作在目标端执行
--新建linux窗口,使用oracle用户登录目标端稽查监控数据库主机操作系统
[oracle@cepri3 ~]$ mkdir oggdata
[oracle@cepri3 ~]$ cd oggdata/
[oracle@cepri3 oggdata]$ pwd
/home/oracle/oggdata
--将源数据端生成的DP_SGLAW.DMP上传到目标端/home/oracle/oggdata
--登录数据库
[oracle@cepri3 oggdata]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 13 10:13:25 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--执行以下语句,建立ggs和sglaw用户
SQL> create user ggs identified by "ggs";
User created.
SQL> grant resource, connect, dba to ggs;
Grant succeeded.
SQL> create user sglaw identified by "sglaw";
User created.
SQL> grant resource, connect, dba to sglaw;
Grant succeeded.
SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@cepri3 oggdata]$ impdp sglaw/sglaw DUMPFILE=DATA_PUMP:DP_SGLAW.DMP
Import: Release 10.2.0.5.0 - 64bit Production on Friday, 13 January, 2012 10:36:54
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SGLAW"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SGLAW"."SYS_IMPORT_FULL_01": sglaw/******** DUMPFILE=DATA_PUMP:DP_SGLAW.DMP
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SGLAW" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
...
--数据初始化结束,使用goldengate用户登录目标端数据库查看数据是否已经正确初始化
--目标端配置
[oracle@cepri3 oggdata]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 13 10:13:25 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
Connected.
--创建数据库用户,'/oracle/oradata/goldengate/goldengate02.dbf'为表空间存放路径
SQL> create tablespace goldengate datafile '/oracle/oradata/goldengate/goldengate02.dbf' size 10240M;
Tablespace created.
SQL> create user goldengate identified by "goldengate" default tablespace goldengate;
User created.
SQL> grant resource, connect, dba to goldengate;
Grant succeeded.
SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--如果目标端goldengate还没有安装,请回本文档开头重新来过
[oracle@cepri3 ~]$ cd /home/oracle/goldengate/
[oracle@cepri3 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
--GLOBALS配置
GGSCI (cepri3) 1>GGSCI (cepri3) 1> edit params ./GLOBALS
--全文如下:
CHECKPOINTTABLE goldengate.ggchkptable
--退出ggsci并重新登录ggsci
GGSCI (cepri3) 2> quit
[oracle@cepri3 goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
--登录数据库
GGSCI (cepri3) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (cepri3) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (goldengate.ggchkptable)...
Successfully created checkpoint table GOLDENGATE.GGCHKPTABLE.
--上传mgr.prm、repk1.prm至/home/oracle/goldengate/dirprm目录下
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 2> edit params mgr
--按照《Goldengate参数规范.doc》修改如下参数, edit params 类似于 vi 命令,vi如何使用请参考第6行
GGSCI (sgdb1) 3> edit params extk1
--添加复制进程组
GGSCI (cepri3) 5> add replicat repk1, exttrail ./dirdat/k1
REPLICAT added.
--启动管理进程和复制进程
GGSCI (cepri3) 6> start mgr
Manager started.
GGSCI (cepri3) 7> start replicat repk1, aftercsn 576717
Sending START request to MANAGER ...
REPLICAT REPK1 starting
GGSCI (cepri3) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPK1 00:00:00 00:00:01
--测试
--打开源数据端plsql,用goldengate用户登录ogg
select * from ogg_test;--记住结果集
--打开目标端plsql,用goldengate用户登录
select * from ogg_test;--记住结果集
--切换至源数据端plsq,插入测试数据
insert into ogg_test (OGG_ID, NAME) values (4, 'test4');
--切换至目标端plsql,
select * from ogg_test;--目标端也自动插入了测试数据
--切换至源数据端plsq,修改测试数据
update ogg_test set name='test' where ogg_id=4;
--切换至目标端plsql,
select * from ogg_test;--目标端也自动修改了测试数据
--切换至源数据端plsq,删除测试数据
delete from ogg_test where ogg_id=4;
--切换至目标端plsql,
select * from ogg_test;--目标端也自动删除了测试数据
--测试通过!thanks hanqingwang!