测试环境file to replicat 方式初始化实践操作





初始化之前注销mgr进程自动启动参数,停止qrep进程，源端开启抽取进程，导出数据的操作在extract ,data pump 开启之后，replicat 进程开启之前执行

1.查询库删除三个复制用户
2.查询库创建三个用户
-- 说明：
-- 用DBA权限的用户登陆；
-- 数据文件名中的路径部分根据实际环境进行修改；

spool 01_创建表空间和用户脚本.log

-- 定义数据文件路径信息，执行前必须修改为实际路径
define datafile_directory=/oracle/app/oradata/orcl

-- 开始创建表空间 ---------
-- 创建 ECIQ_OPERATION 用户表空间，初始空间2G，数据文件最大可扩展空间10G；

source and dest all done


source:

CREATE TABLESPACE TS_TAB_OPERATION
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_SUPPORT 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_SUPPORT
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_LOB 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_LOB
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TS_TEMP_01
TEMPFILE '&datafile_directory/tempfile01.dbf'
SIZE 500M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
TABLESPACE GROUP GROUP1;

-- 开始创建用户 ---------
-- 创建 ECIQ_OPERATION 用户
CREATE USER ECIQ_OPERATION IDENTIFIED BY ECIQ_OPERATION
DEFAULT TABLESPACE TS_TAB_OPERATION
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_OPERATION;
GRANT RESOURCE TO ECIQ_OPERATION;
GRANT EXP_FULL_DATABASE TO ECIQ_OPERATION;
GRANT IMP_FULL_DATABASE TO ECIQ_OPERATION;
-- 创建 ECIQ_SUPPORT 用户
CREATE USER ECIQ_SUPPORT IDENTIFIED BY ECIQ_SUPPORT
DEFAULT TABLESPACE TS_TAB_SUPPORT
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_SUPPORT;
GRANT RESOURCE TO ECIQ_SUPPORT;
GRANT EXP_FULL_DATABASE TO ECIQ_SUPPORT;
GRANT IMP_FULL_DATABASE TO ECIQ_SUPPORT;
-- 创建 ECIQ_LOB 用户
CREATE USER ECIQ_LOB IDENTIFIED BY ECIQ_LOB
DEFAULT TABLESPACE TS_TAB_LOB
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_LOB;
GRANT RESOURCE TO ECIQ_LOB;
GRANT EXP_FULL_DATABASE TO ECIQ_LOB;
GRANT IMP_FULL_DATABASE TO ECIQ_LOB;

dest:

CREATE TABLESPACE TS_TAB_OPERATION
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/operation.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_SUPPORT 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_SUPPORT
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/support.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_LOB 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_LOB
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/lob.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TS_TEMP_01
TEMPFILE '&datafile_directory/tempfile01.dbf'
SIZE 500M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
TABLESPACE GROUP GROUP1;





-- 开始创建用户 ---------
-- 创建 ECIQ_OPERATION 用户
CREATE USER ECIQ_OPERATION IDENTIFIED BY ECIQ_OPERATION
DEFAULT TABLESPACE TS_TAB_OPERATION
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_OPERATION;
GRANT RESOURCE TO ECIQ_OPERATION;
GRANT EXP_FULL_DATABASE TO ECIQ_OPERATION;
GRANT IMP_FULL_DATABASE TO ECIQ_OPERATION;
-- 创建 ECIQ_SUPPORT 用户
CREATE USER ECIQ_SUPPORT IDENTIFIED BY ECIQ_SUPPORT
DEFAULT TABLESPACE TS_TAB_SUPPORT
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_SUPPORT;
GRANT RESOURCE TO ECIQ_SUPPORT;
GRANT EXP_FULL_DATABASE TO ECIQ_SUPPORT;
GRANT IMP_FULL_DATABASE TO ECIQ_SUPPORT;
-- 创建 ECIQ_LOB 用户
CREATE USER ECIQ_LOB IDENTIFIED BY ECIQ_LOB
DEFAULT TABLESPACE TS_TAB_LOB
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_LOB;
GRANT RESOURCE TO ECIQ_LOB;
GRANT EXP_FULL_DATABASE TO ECIQ_LOB;
GRANT IMP_FULL_DATABASE TO ECIQ_LOB;


前期导出文件：




导出表定义：
expdp system/oracle dumpfile=tab_define_%U.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n exclude=table_data
########expdp system/oracle dumpfile=tab_define_%U.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n content=metadata_only

tab_define_01.dmp


导出数据十分之一
expdp system/oracle dumpfile=tab.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB   cluster=n sample=10

tab.dmp






源端导入采样数据:


dmp 文件拷贝之相应的目录，并调整好权限

impdp  system/oracle dumpfile=tab.dmp directory=DATA_PUMP_DIR SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n

DATA_PUMP_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/
目标端导入表定义数据：

dmp 文件拷贝之相应的目录，并调整好权限




impdp  system/oracle dumpfile=tab_define_01.dmp directory=DATA_PUMP_DIR SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n

DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/




Note that I’ll leave the constraints disabled to avoid any issues when GoldenGate populates the tables later.
As in the last post we need to execute the ADD TRANDATA command:

GGSCI  7> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI  8> add trandata eciq_support.*


GGSCI  9>
In real life environments there will probably be ongoing transactions while the initial load of the target database happens. To be able to re-synchronize these changes we need to configure change data capture.
On the source system, as usual, we’ll need an extract:

GGSCI  3> edit params extrcdc1
The parameters are注释掉的两项是开启DDL的选项）

EXTRACT extrcdc1
USERID ogg@orcc,password oracle
EXTTRAIL ./dirdat/gg
--DDL INCLUDE MAPPED
--DDLOPTIONS ADDTRANDATA
LOGALLSUPCOLS
UPDATERECORDFORMAT compact
TABLE ECIQ_SUPPORT.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;
The remaining steps on the source database are the same as in the last post:

GGSCI  4> dblogin userid ogg,password oracle

GGSCI (node1 as ogg@orcc1) 11> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (node1 as ogg@orcc1) 12> register extract extrcdc1 database
Extract EXTRCDC1 successfully registered with database at SCN 3207847.


GGSCI  6> add extract extrcdc1, integrated tranlog, begin now
EXTRACT added.

GGSCI  7> add exttrail ./dirdat/gg, extract extrcdc1, megabytes 5
EXTTRAIL added.



Configure the datapump:

GGSCI  10> edit params dppump1
The parameters are:

EXTRACT dppump1
PASSTHRU
RMTHOST 192.168.1.7, MGRPORT 7809
RMTTRAIL ./dirdat/jj
TABLE ECIQ_SUPPORT.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;

Start the datapump:

GGSCI  12> add extract dppump1, exttrailsource ./dirdat/gg
EXTRACT added.

GGSCI  13> add rmttrail ./dirdat/jj, extract dppump1, megabytes 5
RMTTRAIL added.

GGSCI  23> start extract *

Sending START request to MANAGER ...
EXTRACT DPPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXTRCDC1 starting

GGSCI  24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     DPPUMP1     00:00:00      00:00:02   
EXTRACT     RUNNING     EXTRCDC1    00:00:07      00:00:07   
The next step if to configure the change delivery on the target system:






目标端：

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 26> edit params rplcdd
The parameters are:

REPLICAT replcdd
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/replccd.dsc, purge
USERID ogg@orcl, password oracle
MAP eciq_support.*, TARGET eciq_support.*;


Configure the replicat:

GGSCI (oelgg2.it.dbi-services.com) 1> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 2> add replicat replcdd, integrated, exttrail ./dirdat/jj
REPLICAT (Integrated) added.
We will not start the replicat right now as we wan to do the initial load before.
Now it is time to get the current scn of the source database:


源端：

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3285413

The next step is to create the extract process parameter file:

GGSCI  10> edit params INITLOAD
The parameters for the file are:

EXTRACT initload
SOURCEISTABLE
USERID ogg@orcc,password oracle
RMTHOST 192.168.1.7, MGRPORT 7809
RMTFILE ./dirdat/initld, MEGABYTES 2, PURGE
TABLE ECIQ_SUPPORT.*, SQLPREDICATE 'AS OF SCN 3285413';
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;
Lets create the extract group:

GGSCI  2> add extract initload, sourceistable
EXTRACT added.
The sourceistable parameter tells GoldenGate to extract the data directly from the tables for the initial load.
Now we can start the extract process:

GGSCI  30> start extract initload

Sending START request to MANAGER ...
EXTRACT INITLOAD starting
This will start the extract and stop automatically once completed. You can get a report on what happened with:

GGSCI  36> view report initload
On the target system add the replicat:
GGSCI (node1 as ogg@orcl) 24> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (node1 as ogg@orcl) 25> add replicat rload1, specialrun
REPLICAT added.

REPLICAT rload1      
USERID ogg@orcl,password oracle         
SPECIALRUN      
END RUNTIME     
ASSUMETARGETDEFS         
EXTFILE ./dirdat/initld     
MAP eciq_support.*, TARGET eciq_support.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;

The “SPECIALRUN” and “END RUNTIME” tell GoldenGate that this is a one time batch task.
The “TABLEEXCLUDE” parameter excludes the View “HR.EMP_DETAILS_VIEW” as we do not want to get the view populated.
Lets see if we can load the data on the target system:






禁用约束及触发器：
在原库生成禁用外键的sql并执行

set pagesize 20000
set linesize 200
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||' cascade;' from dba_constraints where owner in ('ECIQ_SUPPORT') and constraint_type='R';
select 'alter table '||owner||'.'||table_name||' disable all triggers;' from dba_tables where owner in ('ECIQ_SUPPORT');


GGSCI (oelgg2.it.dbi-services.com) 3> start replicat rload1



报错重新执行，注意截断所有表！！！！！！！！！！！！
It is advisable to tail the GoldenGate log in a separate session while the load is running. If everything is fine it should look similar to this:

...
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.JOB_HISTORY: EMPLOYEE_ID, START_DATE.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."LOCATIONS", TARGET hr."LOCATIONS".
2015-09-03 11:14:17  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID.
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.LOCATIONS: LOCATION_ID.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."REGIONS", TARGET hr."REGIONS".
2015-09-03 11:14:18  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: REGION_ID, REGION_NAME.
2015-09-03 11:14:18  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.REGIONS: REGION_ID.
2015-09-03 11:14:18  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, rload1.prm:  REPLICAT RLOAD1 stopped normally.
Lets check if the data is really there:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107
Looks fine. The data is now available up to SCN 1909670. Now we need to make sure that the data after this SCN will be synchronized.
Before starting the synchronization lets update some data in the source database:

SQL> update hr.countries set COUNTRY_NAME = 'Zimbabwe2' where COUNTRY_NAME = 'Zimbabwe';

1 row updated.

SQL> commit;

Commit complete.
Time to start the replicat on the target database:

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 18> start replicat replcdd, aftercsn 3285413

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

GGSCI (oelgg2.it.dbi-services.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REPLCDD     00:00:00      00:00:06   
If everything works as expected we should see the row we updated just before:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select * from hr.countries where COUNTRY_NAME like '%Zimbabwe%';

CO COUNTRY_NAME                  REGION_ID
-- ---------------------------------------- ----------
ZW Zimbabwe2                         4
Works like a charm. In the next post I’ll look into how to do the same but populating the target database with expdp/impdp instead of the GoldenGate file to replicat method.

测试环境file to replicat 方式初始化实践操作





初始化之前注销mgr进程自动启动参数,停止qrep进程，源端开启抽取进程，导出数据的操作在extract ,data pump 开启之后，replicat 进程开启之前执行

1.查询库删除三个复制用户
2.查询库创建三个用户
-- 说明：
-- 用DBA权限的用户登陆；
-- 数据文件名中的路径部分根据实际环境进行修改；

spool 01_创建表空间和用户脚本.log

-- 定义数据文件路径信息，执行前必须修改为实际路径
define datafile_directory=/oracle/app/oradata/orcl

-- 开始创建表空间 ---------
-- 创建 ECIQ_OPERATION 用户表空间，初始空间2G，数据文件最大可扩展空间10G；

source and dest all done


source:

CREATE TABLESPACE TS_TAB_OPERATION
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_SUPPORT 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_SUPPORT
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_LOB 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_LOB
LOGGING
DATAFILE  '+DATA'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TS_TEMP_01
TEMPFILE '&datafile_directory/tempfile01.dbf'
SIZE 500M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
TABLESPACE GROUP GROUP1;

-- 开始创建用户 ---------
-- 创建 ECIQ_OPERATION 用户
CREATE USER ECIQ_OPERATION IDENTIFIED BY ECIQ_OPERATION
DEFAULT TABLESPACE TS_TAB_OPERATION
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_OPERATION;
GRANT RESOURCE TO ECIQ_OPERATION;
GRANT EXP_FULL_DATABASE TO ECIQ_OPERATION;
GRANT IMP_FULL_DATABASE TO ECIQ_OPERATION;
-- 创建 ECIQ_SUPPORT 用户
CREATE USER ECIQ_SUPPORT IDENTIFIED BY ECIQ_SUPPORT
DEFAULT TABLESPACE TS_TAB_SUPPORT
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_SUPPORT;
GRANT RESOURCE TO ECIQ_SUPPORT;
GRANT EXP_FULL_DATABASE TO ECIQ_SUPPORT;
GRANT IMP_FULL_DATABASE TO ECIQ_SUPPORT;
-- 创建 ECIQ_LOB 用户
CREATE USER ECIQ_LOB IDENTIFIED BY ECIQ_LOB
DEFAULT TABLESPACE TS_TAB_LOB
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_LOB;
GRANT RESOURCE TO ECIQ_LOB;
GRANT EXP_FULL_DATABASE TO ECIQ_LOB;
GRANT IMP_FULL_DATABASE TO ECIQ_LOB;

dest:

CREATE TABLESPACE TS_TAB_OPERATION
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/operation.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_SUPPORT 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_SUPPORT
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/support.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建 ECIQ_LOB 用户表空间，初始空间2G，数据文件最大可扩展空间10G；
CREATE TABLESPACE TS_TAB_LOB
LOGGING
DATAFILE  '/u01/app/oracle/oradata/orcl/lob.dbf'
SIZE 1024M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 10000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TS_TEMP_01
TEMPFILE '&datafile_directory/tempfile01.dbf'
SIZE 500M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
TABLESPACE GROUP GROUP1;





-- 开始创建用户 ---------
-- 创建 ECIQ_OPERATION 用户
CREATE USER ECIQ_OPERATION IDENTIFIED BY ECIQ_OPERATION
DEFAULT TABLESPACE TS_TAB_OPERATION
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_OPERATION;
GRANT RESOURCE TO ECIQ_OPERATION;
GRANT EXP_FULL_DATABASE TO ECIQ_OPERATION;
GRANT IMP_FULL_DATABASE TO ECIQ_OPERATION;
-- 创建 ECIQ_SUPPORT 用户
CREATE USER ECIQ_SUPPORT IDENTIFIED BY ECIQ_SUPPORT
DEFAULT TABLESPACE TS_TAB_SUPPORT
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_SUPPORT;
GRANT RESOURCE TO ECIQ_SUPPORT;
GRANT EXP_FULL_DATABASE TO ECIQ_SUPPORT;
GRANT IMP_FULL_DATABASE TO ECIQ_SUPPORT;
-- 创建 ECIQ_LOB 用户
CREATE USER ECIQ_LOB IDENTIFIED BY ECIQ_LOB
DEFAULT TABLESPACE TS_TAB_LOB
PROFILE DEFAULT;
-- 授权
GRANT CONNECT TO ECIQ_LOB;
GRANT RESOURCE TO ECIQ_LOB;
GRANT EXP_FULL_DATABASE TO ECIQ_LOB;
GRANT IMP_FULL_DATABASE TO ECIQ_LOB;


前期导出文件：




导出表定义：
expdp system/oracle dumpfile=tab_define_%U.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n exclude=table_data
########expdp system/oracle dumpfile=tab_define_%U.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n content=metadata_only

tab_define_01.dmp


导出数据十分之一
expdp system/oracle dumpfile=tab.dmp directory=expdp_dir SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB   cluster=n sample=10

tab.dmp






源端导入采样数据:


dmp 文件拷贝之相应的目录，并调整好权限

impdp  system/oracle dumpfile=tab.dmp directory=DATA_PUMP_DIR SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n

DATA_PUMP_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/
目标端导入表定义数据：

dmp 文件拷贝之相应的目录，并调整好权限




impdp  system/oracle dumpfile=tab_define_01.dmp directory=DATA_PUMP_DIR SCHEMAS=ECIQ_OPERATION,ECIQ_SUPPORT,ECIQ_LOB  parallel=8 cluster=n

DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/




Note that I’ll leave the constraints disabled to avoid any issues when GoldenGate populates the tables later.
As in the last post we need to execute the ADD TRANDATA command:

GGSCI  7> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI  8> add trandata eciq_support.*


GGSCI  9>
In real life environments there will probably be ongoing transactions while the initial load of the target database happens. To be able to re-synchronize these changes we need to configure change data capture.
On the source system, as usual, we’ll need an extract:

GGSCI  3> edit params extrcdc1
The parameters are注释掉的两项是开启DDL的选项）

EXTRACT extrcdc1
USERID ogg@orcc,password oracle
EXTTRAIL ./dirdat/gg
--DDL INCLUDE MAPPED
--DDLOPTIONS ADDTRANDATA
LOGALLSUPCOLS
UPDATERECORDFORMAT compact
TABLE ECIQ_SUPPORT.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;
The remaining steps on the source database are the same as in the last post:

GGSCI  4> dblogin userid ogg,password oracle

GGSCI (node1 as ogg@orcc1) 11> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (node1 as ogg@orcc1) 12> register extract extrcdc1 database
Extract EXTRCDC1 successfully registered with database at SCN 3207847.


GGSCI  6> add extract extrcdc1, integrated tranlog, begin now
EXTRACT added.

GGSCI  7> add exttrail ./dirdat/gg, extract extrcdc1, megabytes 5
EXTTRAIL added.



Configure the datapump:

GGSCI  10> edit params dppump1
The parameters are:

EXTRACT dppump1
PASSTHRU
RMTHOST 192.168.1.7, MGRPORT 7809
RMTTRAIL ./dirdat/jj
TABLE ECIQ_SUPPORT.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;

Start the datapump:

GGSCI  12> add extract dppump1, exttrailsource ./dirdat/gg
EXTRACT added.

GGSCI  13> add rmttrail ./dirdat/jj, extract dppump1, megabytes 5
RMTTRAIL added.

GGSCI  23> start extract *

Sending START request to MANAGER ...
EXTRACT DPPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXTRCDC1 starting

GGSCI  24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     DPPUMP1     00:00:00      00:00:02   
EXTRACT     RUNNING     EXTRCDC1    00:00:07      00:00:07   
The next step if to configure the change delivery on the target system:






目标端：

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 26> edit params rplcdd
The parameters are:

REPLICAT replcdd
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/replccd.dsc, purge
USERID ogg@orcl, password oracle
MAP eciq_support.*, TARGET eciq_support.*;


Configure the replicat:

GGSCI (oelgg2.it.dbi-services.com) 1> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 2> add replicat replcdd, integrated, exttrail ./dirdat/jj
REPLICAT (Integrated) added.
We will not start the replicat right now as we wan to do the initial load before.
Now it is time to get the current scn of the source database:


源端：

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3285413

The next step is to create the extract process parameter file:

GGSCI  10> edit params INITLOAD
The parameters for the file are:

EXTRACT initload
SOURCEISTABLE
USERID ogg@orcc,password oracle
RMTHOST 192.168.1.7, MGRPORT 7809
RMTFILE ./dirdat/initld, MEGABYTES 2, PURGE
TABLE ECIQ_SUPPORT.*, SQLPREDICATE 'AS OF SCN 3285413';
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;
Lets create the extract group:

GGSCI  2> add extract initload, sourceistable
EXTRACT added.
The sourceistable parameter tells GoldenGate to extract the data directly from the tables for the initial load.
Now we can start the extract process:

GGSCI  30> start extract initload

Sending START request to MANAGER ...
EXTRACT INITLOAD starting
This will start the extract and stop automatically once completed. You can get a report on what happened with:

GGSCI  36> view report initload
On the target system add the replicat:
GGSCI (node1 as ogg@orcl) 24> dblogin userid ogg,password oracle
Successfully logged into database.

GGSCI (node1 as ogg@orcl) 25> add replicat rload1, specialrun
REPLICAT added.

REPLICAT rload1      
USERID ogg@orcl,password oracle         
SPECIALRUN      
END RUNTIME     
ASSUMETARGETDEFS         
EXTFILE ./dirdat/initld     
MAP eciq_support.*, TARGET eciq_support.*;
TABLEEXCLUDE eciq_support.V_BASECODE_RELATION;
TABLEEXCLUDE eciq_support.V_TEST;

The “SPECIALRUN” and “END RUNTIME” tell GoldenGate that this is a one time batch task.
The “TABLEEXCLUDE” parameter excludes the View “HR.EMP_DETAILS_VIEW” as we do not want to get the view populated.
Lets see if we can load the data on the target system:






禁用约束及触发器：
在原库生成禁用外键的sql并执行

set pagesize 20000
set linesize 200
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||' cascade;' from dba_constraints where owner in ('ECIQ_SUPPORT') and constraint_type='R';
select 'alter table '||owner||'.'||table_name||' disable all triggers;' from dba_tables where owner in ('ECIQ_SUPPORT');


GGSCI (oelgg2.it.dbi-services.com) 3> start replicat rload1



报错重新执行，注意截断所有表！！！！！！！！！！！！
It is advisable to tail the GoldenGate log in a separate session while the load is running. If everything is fine it should look similar to this:

...
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.JOB_HISTORY: EMPLOYEE_ID, START_DATE.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."LOCATIONS", TARGET hr."LOCATIONS".
2015-09-03 11:14:17  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID.
2015-09-03 11:14:17  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.LOCATIONS: LOCATION_ID.
2015-09-03 11:14:17  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Wildcard MAP resolved (entry hr.*): MAP "HR"."REGIONS", TARGET hr."REGIONS".
2015-09-03 11:14:18  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using following columns in default map by name: REGION_ID, REGION_NAME.
2015-09-03 11:14:18  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, rload1.prm:  Using the following key columns for target table HR.REGIONS: REGION_ID.
2015-09-03 11:14:18  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, rload1.prm:  REPLICAT RLOAD1 stopped normally.
Lets check if the data is really there:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107
Looks fine. The data is now available up to SCN 1909670. Now we need to make sure that the data after this SCN will be synchronized.
Before starting the synchronization lets update some data in the source database:

SQL> update hr.countries set COUNTRY_NAME = 'Zimbabwe2' where COUNTRY_NAME = 'Zimbabwe';

1 row updated.

SQL> commit;

Commit complete.
Time to start the replicat on the target database:

GGSCI (oelgg2.it.dbi-services.com as ggadmin@DB2) 18> start replicat replcdd, aftercsn 3285413

Sending START request to MANAGER ...
REPLICAT REPLCDD starting

GGSCI (oelgg2.it.dbi-services.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REPLCDD     00:00:00      00:00:06   
If everything works as expected we should see the row we updated just before:

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB2

SQL> select * from hr.countries where COUNTRY_NAME like '%Zimbabwe%';

CO COUNTRY_NAME                  REGION_ID
-- ---------------------------------------- ----------
ZW Zimbabwe2                         4
Works like a charm. In the next post I’ll look into how to do the same but populating the target database with expdp/impdp instead of the GoldenGate file to replicat method.