Oracle Golden Gate Examples

Oracle Golden Gate 系列六 — 11gR2 Ora2Ora 单向复制 GG 示例

http://www.2cto.com/database/201111/111285.html

在前面几篇了看了GG 的安装配置的一些理论东西,在这篇来搭建一个Oracle 到Oracle 的一个Golden Gate 测试环境。

测试环境:
OS: Redhat 5.4 64bit
DB: Oracle 11.2.0.3 64bit

相关的理论知识参考:
Oracle Golden Gate 系列四 —GG 安装 与 卸载 理论知识:http://www.2cto.com/database/201111/111283.html

示例采用GoldenGate典型的配置:
在Source端,配置一个管理进程, 添加一个Extract进程,添加一个本地队列路径,定义一个远端的接收队列路径。
在Target端,配置一个管理进程和添加一个Replicat进程,指定一个应用队列,即抽取进程定义的远端队列。

一.安装GG 软件
1.1 OS 和 DB 版本
[root@gg2 ~]# uname -a
Linux gg2 2.6.18-164.el5xen #1 SMP Tue Aug18 15:59:52 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@gg2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4(Tikanga)

SQL> select * from v$version whererownum=1;
BANNER


Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

1.2 安装GG
在source database 和 target database 都执行如下操作:
[root@gg2 ~]# su - oracle
gg2:/home/oracle> mkdir /u01/ggate
gg2:/home/oracle> cd /u01
gg2:/u01> ls
app ggate
fbo_ggs_Linux_x64_ora11g_64bit.tar OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
fbo_ggs_Linux_x64_ora11g_64bit.zip README.txt
gg2:/u01> tar xvffbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/ggate

1.3 添加环境变量
在/home/oracle/.bash_profile文件里添加如下内容:
export PATH=/u01/ggate:$PATH
exportLD_LIBRARY_PATH=/u01/ggate:$LD_LIBRARY_PATH
export GGATE=/u01/ggate

注意我这里的GG 和Oracle 使用的是相同的用户,所以把GG 的变量加上就可以了。加载刚刚设置的环境变量:
gg2:/home/oracle> source/home/oracle/.bash_profile

1.4 使用ggsci工具,创建必要的目录
gg1:/u01/ggate> ggsci
—调用ggsci 工具
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14

Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.

GGSCI (gg1) 1> create subdirs
—使用ggsci 工具创建目录
Creating subdirectories under currentdirectory /u01/ggate

Parameter files /u01/ggate/dirprm: created
Report files /u01/ggate/dirrpt: created
Checkpoint files /u01/ggate/dirchk: created
Process status files /u01/ggate/dirpcs: created
SQL script files /u01/ggate/dirsql: created
Database definitions files /u01/ggate/dirdef: created
Extract data files /u01/ggate/dirdat: created
Temporary files /u01/ggate/dirtmp: created
Veridata files /u01/ggate/dirver: created
Veridata Lock files /u01/ggate/dirver/lock: created
Veridata Out-Of-Sync files /u01/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files/u01/ggate/dirver/oosxml: created
Veridata Parameter files /u01/ggate/dirver/params: created
Veridata Report files /u01/ggate/dirver/report: created
Veridata Status files /u01/ggate/dirver/status: created
Veridata Trace files /u01/ggate/dirver/trace: created
Stdout files /u01/ggate/dirout: created

GGSCI (gg1) 2>

以上就是GG 的安装,在source 和target database 都执行。

二.配置Source database
GoldenGate通过抓取源端数据库重做日志进行分析,将获取的数据应用到目标端,实现数据同步。因此,源数据库需要必须处于归档模式,并启用附加日志和强制日志。

2.1 归档模式、附加日志、强制日志
查看
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
-- -- -
ARCHIVELOG NO NO

—修改
(1)archivelog
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
(2) force logging
SQL>alterdatabase force logging;
(3)supplemental log data
SQL>alterdatabase add supplemental log data;

2.2 Oracle 9i的_LOG_PARALLELISM参数
如果是Oracle 9i的数据库,还需要将_LOG_PARALLELISM 参数设置为1. 因为GG 不支持该值超过1.
If using OracleGoldenGate for an Oracle 9i source database, set the _LOG_PARALLELISMparameter to 1. Oracle GoldenGate does not support values higher than 1.

2.3 启用DDL 支持
GG虽然支持DDL,但是也是有限制的,具体参考:
Oracle Gloden Gate 系列三 —GG 支持与不支持的对象类型与操作 说明:http://www.2cto.com/database/201111/111282.html

GG 支持DDL 也是通过创建一些table 来保存这些DDL 的信息,关于这些table 的具体说明,在如下链接的第二小节:启用GG 对DDL 操作的支持有详细说明:
Oracle Golden Gate 系列四 —GG 安装 与 卸载 理论知识:http://www.2cto.com/database/201111/111283.html

对于这些存放DDL 信息表的管理的理论支持,参考如下链接的第四小结:Managing theOracle DDL replication environment。

Oracle Golden Gate 系列五 —GG 使用配置 说明:http://www.2cto.com/database/201111/111284.html

这块的测试内容会另篇Blog进行测试。

2.3.1 禁用Recycle Bin
如果启用DDL 支持,必须关闭recycle bin。官网的解释如下:
If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.

Oracle 11g:
SQL> alter system set recyclebin=offscope=spfile;
System altered.

如果数据库是10g,需要关闭recyclebin并重启;或者手工purge recyclebin。

2.3.2 创建存放DDL 信息的user并赋权
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
Grant succeeded.

退出所有使用Oracle 的session,然后使用SYSDBA权限的用户执行如下脚本:

gg1:/u01/ggate> echo $GGATE
/u01/ggate
—进入GG的目录,然后调用脚本:
gg1:/home/oracle> cd $GGATE
gg1:/u01/ggate> sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
—脚本1:
SQL> @marker_setup.sql;

Marker setup script

You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
—输入我们之前创建的用户名:
Enter GoldenGate schema name:ggate

Marker setup table script complete, runningverification script…
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to GGATE

MARKER TABLE


OK
MARKER SEQUENCE


OK
Script complete.

—脚本2:
SQL> @ddl_setup.sql;

GoldenGate DDL Replication setup script

Verifying that current user has privilegesto install DDL Replication…

You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
—注意这里提示我们在10g里,必须关闭recycle bin,在11g以后的版本,可以不用关闭。
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.

—提示输入GG的用户:
Enter GoldenGate schema name:ggate

You will be prompted for the mode ofinstallation.
To install or reinstall DDL replication,enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
—这里让我们选择安装模式: install 和 reinstall 选择INITIALSETUP
Enter mode of installation:INITIALSETUP

Working, please wait …
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables …

Check complete.

Using GGATE as a GoldenGate schema name,INITIALSETUP as a mode of installation.

Working, please wait …

DDL replication setup script complete,running verification script…
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to GGATE

DDLORA_GETTABLESPACESIZE STATUS:
……

STATUS OF DDL REPLICATION


SUCCESSFUL installation of DDL Replicationsoftware components

Script complete.

—脚本3:
SQL> @role_setup.sql;

GGS Role setup script

This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)

You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
—同样输入GG用户名:
Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
—这里提示我们赋权给相关的用户:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.

—脚本4:赋权
SQL> grant GGS_GGSUSER_ROLE to ggate;
Grant succeeded.

—脚本5:
SQL> @ddl_enable.sql;
Trigger altered.

注意这里脚本创建的table都是使用默认的名称,当然也可以修改这些table的默认名,具体这块参考之前的文档中的说明。

三.测试GG
经过第一和第二节的配置,GG 的配置基本完成,这里我们开始测试GG。
注意:
(1) 目标库的用户名和对象名称可以与源端不同,关键在于配置文件中要能够正确匹配。
(2) 配置源和目标两端tnsnames,保持互联互通。

3.1 在Source 和 Target database上创建测试用户
—source database
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba tosender;
Grant succeeded.

—target database
SQL> create user receiver identified byoracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba toreceiver;
Grant succeeded.

3.2 在Source 和Target 上配置Manager
gg1:/home/oracle> cd $GGATE
gg1:/u01/ggate> ggsci
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14

Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.

GGSCI (gg1) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (gg1) 2> edit params mgr
PORT 7809
—这里我们指定了端口,然后:wq 保存退出。
GGSCI (gg1) 3> start manager
Manager started.

以上是在Source 库上执行的,在Target 库上执行同样的操作。

3.3 配置SourceDB 的复制队列

3.3.1 先连接到数据库,测试连接:
GGSCI (gg1) 10> dblogin useridggate@gg1, password ggate
Successfully logged into database.

3.3.2 增加一个抽取:
GGSCI (gg1) 11> add extract ext1,tranlog, begin now
2011-11-08 20:36:47 INFO OGG-01749 Successfully registeredEXTRACT EXT1 to start managing log retention at SCN 1121060.
EXTRACT added.

GGSCI (gg1) 12> add exttrail /u01/ggate/dirdat/lt, extract ext1
EXTTRAIL added

修改抽取进程ext1参数:
GGSCI (gg1) 13> edit params ext1
extract ext1
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
ddl include mapped objname sender.*;
table sender.*;

GGSCI (gg1) 14> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:03:26

3.4 配置TargetDB 同步队列
3.4.1 在Target 端添加checkpoint表:
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
添加如上2条记录。

GGSCI (gg2) 12> dblogin userid ggate@gg2,password ggate
Successfully logged into database.
—说明,这个用户是在Source 库启用DDL 创建的,我在Target 库也创建了这个用户。
GGSCI (gg2) 13> add checkpointtable ggate.checkpoint
Successfully created checkpoint tableGGATE.CHECKPOINT.

3.4.2 创建同步队列
GGSCI (gg2) 14> add replicat rep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.

GGSCI (gg2) 15> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile /u01/ggate/dirdat/rep1_discard.txt,append, megabytes 10
DDL
map sender.*, target receiver.*;
添加如上内容。

3.5开启同步
3.5.1 Source DB:
GGSCI (gg1) 15> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (gg1) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:14:16

GGSCI (gg1) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:14:26 00:00:02

3.5.2 Target DB
GGSCI (gg2) 16> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (gg2) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:04

3.6 测试Data 复制
我们在Source DB上的sender 用户下创建一张表,然后看这张表是否同步到了Target DB的receiver用户下。

—Source DB:
SQL> conn sender/oracle;
Connected.

SQL> create table dave as select * fromsys.all_users;
Table created.

—Target DB:
SQL> conn receiver/oracle;
Connected.
SQL> select count(*) from dave;

COUNT(*)


32

数据同步过来了,因为我们启用了DDL的支持,所以这里把表给复制过来了。

现在我们在Source DB上在插入一些记录,在验证下GG的同步情况:
SQL> insert into dave select * fromsys.all_users;
32 rows created.
SQL> commit;
Commit complete.

在Target DB 验证:
SQL> select count(*) from dave;
COUNT(*)


32

SQL> /

COUNT(*)


64

同步正常,以上就是Oracle to Oracle 下的一个GG 单向复制示例。有关GG的更多内容会继续测试。

Oracle Golden Gate 系列七 — 配置 GG Manager process

2011-11-16 15:34:23 我来说两句 收藏 我要投稿 [字体:小 大]

在上篇GG 单向复制的示例中,有配置Manager process,这篇具体来看一下Manager Process 的配置。
Oracle Golden Gate 系列六 11gR2 Ora2Ora 单向复制GG 示例:http://www.2cto.com/database/201111/111285.html
一.Overview of the Manager process
To configure andrun Oracle GoldenGate, a Manager process must be running on the source andtarget systems. The Manager process performs the following functions:
要配置GG,必须先要在Source和 Target System上部署Manager Process。 该进程执行一下4个函数:
(1) Start Oracle GoldenGateprocesses
启动GG 进程
(2) Start dynamic processes
启动动态进程
(3) Perform trail management
执行trail 管理
(4) Create event, error, andthreshold reports
—创建event,error和threshold报告

There is oneManager for each Oracle GoldenGate installation. One Manager can support multipleOracle GoldenGate extraction and replication processes.
—每个GG 都必须要有一个Manager Process,一个MP可以支持多个GGExtraction 和 Replication 进程。

二.Configuring Manager
To configureManager, create a parameter file by following these steps. If you installed OracleGoldenGate on a UNIX cluster, configure the Oracle GoldenGate Manager process withinthe cluster application as directed by the vendor’s documentation, so thatOracle GoldenGate will fail over properly with the other applications.
—配置Manager,只需要按照以下步骤创建一个参数文件。

To configure Manager
1. From the Oracle GoldenGatedirectory, run the ggsci program to open the Oracle GoldenGate Software CommandInterface, commonly known as GGSCI.
—在GG的根目录运行ggsci命令,因为这个命令会调用其他的命令,如果不是在根目录下执行,虽然在通过PATH变量也可以找到ggsci命令,但是会找不到其他的文件而报错。

2. In GGSCI, issue the following command toedit the Manager parameter file.
EDIT PARAMS MGR
—在ggsci里执行上述命令,编辑Manager 进程参数
3. Add the following parameter to specifythe Manager port number.
PORT<port_number>

PORT defines theport number on which Manager runs on the local system.
—这个端口是本地OS 的端口

Observe these guidelines:
(1) The default port number is7809.
默认端口是7809
(2) You must specify either thedefault port number or a different one.
必须指定这个端口为默认端口或者其他端口
(3) Each Manager instance on asystem must use a different port number.
每个Manager instance必须使用不同的端口
(4) The port must be unreserved andunrestricted. GGSCI uses this port to request Manager to start processes. TheExtract process uses this port to request Manager to start a remote Collectorprocess or an initial-load Replicat process.
这个端口不能受限
(5) PORT is the only requiredManager parameter.
端口是Manager 唯一必须指定的参数
NOTE:
The port numberalso must be specified with the MGRPORT argument of the Extract parameter RMTHOST.
Extrace 的RMTHOST 也必须指定MGRPORT 端口

三.Recommended parameters
The followingparameters are optional, but recommended for the Manager process.
—第二节讲了Manager 必须指定的参数Port,这里讲了一些可选的参数。

(1) USERID: Required if usingOracle GoldenGate DDL support, specify the Manager user and password with the .
如果使用DDL,需要创建一个用户来保存相关的table,这里指定该用户的用户名和密码
(2) DYNAMICPORTLIST: Use to specifyup to 256 unreserved, unrestricted ports for dynamic TCP/IP communicationsbetween the source and target systems. The Collector,Replicat, and GGSCIprocesses will use these ports. In the absence of DYNAMICPORTLIST, Managertries to start Collector on port 7840. If 7840 is not available, Manager incrementsby one until it finds an available port.
动态的指定端口在source 和 targetsystems 进行交流。 默认情况下从7840 端口开始,如果该端口不可用,则端口数加1,知道找到一个可用的。
(3) DYNAMICPORTREASSIGNDELAY:Controls how long Manager waits to assign a port that was assigned before.
控制Manager 等待分配端口的时间。
(4) AUTOSTART: Starts Extract andReplicat processes when Manager starts. This can be useful, for example, if youwant Oracle GoldenGate activities to begin immediately when you start thesystem, assuming Manager is part of the startup routine. You can use multiple AUTOSTARTstatements in the same parameter file.
在Manager 启动时,自动启动Extract和replicat进程。 这是一个有用的命令,可以将Manager 添加到stratup routine里,这样Manager启动之后,Extrace 和Replicat 也就可以自动启动了。
(5) AUTORESTART: Starts Extract andReplicat processes again after abnormal termination.
当Extract 和Replicat 进程异常中断后自动启动。
(6) PURGEOLDEXTRACTS: Purges trailfiles when Oracle GoldenGate has finished processing them. Without using PURGEOLDEXTRACTS,no purging is performed, and trail files can consume significant diskspace.Using PURGEOLDEXTRACTS as a Manager parameter is preferred over using theExtract or Replicat version of PURGEOLDEXTRACTS.
当trail file 处理完后purge它们,如果没有指定该参数,trail file 会占用大量的磁盘空间。 Manager 的PURGEOLDEXTRACTS参数优先级比Extrace 和Replicat 的PURGEOLDEXTRACTS高。 即会先使用Manager的该参数。

NOTE:
When using PURGEOLDEXTRACTS,do not permit trail files to be deleted by any user or program other than OracleGoldenGate. It will cause PURGEOLDEXTRACTS to function improperly.
注意,如果使用PURGEOLDEXTRACTS,那么不允许出GG之外的用户和程序删除trail files 文件。 否则会导致PURGEOLDEXTRACTS异常。

四.Starting Manager
Manager must berunning before you start other Oracle GoldenGate processes. You can startManager from:
—Manager 必须先与GG 进程启动
(1) The command line of any supportedoperating system
(2) GGSCI
(3) The Services applet on aWindows system if Manager is installed as a service. See the Windowsdocumentation or your system administrator.
(4) The Cluster Administrator toolif the system is part of a Windows cluster. This is the recommended way tobring the Manager resource online. See the cluster documentation or your systemadministrator.
(5) The cluster software of a UNIXor Linux cluster. Refer to the documentation provided by the cluster vendor todetermine whether to start Manager from the cluster or by using GGSCI or thecommand line of the operating system.

4.1 To start Manager from the command line
To run Managerfrom the command shell of the operating system, use the following syntax.
mgr paramfile<param file> [reportfile <report file>]

The reportfile argumentis optional and can be used to store the Manager process report in a locationother than the default of the dirrpt directory in the Oracle GoldenGateinstallation location.

4.2 To start Manager from GGSCI
1. From the Oracle GoldenGate directory,run GGSCI.
2. In GGSCI, issue the following command.
START MANAGER

On Windows systems, you can use the BOOTDELAYMINUTES parameter to specify how long aftersystem boot time Manager delays until it starts its processing activities.

NOTE:
When startingManager from the command line or GGSCI on Windows Server 2008 with User Account Control enabled, you will receive a UAC prompt requesting you to allow or deny the program to run.

五.Stopping Manager
Manager runsindefinitely or until stopped by a user. In general, Manager should remain runningwhen there are synchronization activities being performed. Manager performs importantmonitoring and maintenance functions, and processes cannot be started unless Manageris running.
—在停Manager 之前,需要先停GG 进程,因为Manager负责监控和维护其他的进程。

To stop Manager
(1) On UNIX, Linux, and z/OS usingUSS, Manager must be stopped by using the STOP MANAGER command in GGSCI.
STOP MANAGER [!]
Where: ! stops Manager without userconfirmation.
—这里可选的!表示不经过用户确认直接stop
(2) On Windows, if Manager isinstalled as a service, you can stop it from the Services applet. See theWindows documentation or your system administrator.
(3) In a Windows cluster, Managermust only be stopped by taking the Manager resource offline by using theCluster Administrator. If you attempt to stop the Manager service from theGGSCI interface, the cluster monitor interprets it as a resource failure and willattempt to bring the resource online again. If a stop request is repeatedly submittedfrom the GGSCI interface, and the restart threshold of the Manager cluster resourceis exceeded, the cluster monitor marks the Manager resource as failed.
(4) In a UNIX or Linux cluster,refer to the documentation provided by the cluster vendor to determine whetherManager should be stopped from the cluster or by using GGSCI.

Oracle Golden Gate 系列八 — GG 参数文件 说明

2011-11-16 15:35:48 我来说两句 收藏 我要投稿 [字体:小 大]

一.官网有关GG 参数文件的说明
Most OracleGoldenGate functionality is controlled by means of parameters specified in parameterfiles. A parameter file is an ASCII file that is read by an associated process.
大多数GG 功能都是受指定的参数控制,这个参数文件是一个ASCII 文件。
OracleGoldenGate uses two types of parameter files: a GLOBALS file and runtimeparameter files.
GG 使用两种类型的参数文件: GLOBALS 和 runtime parameter。

1.1 Overview of the GLOBALS file
The GLOBALS filestores parameters that relate to the Oracle GoldenGate instance as a whole.This is in contrast to runtime parameters, which are coupled with a specificprocess such as Extract. The parameters in the GLOBALS file apply to allprocesses in the Oracle GoldenGate instance, but can be overridden by specificprocess parameters. Once set, GLOBALS parameters are rarely changed, and thereare far fewer of them than runtime parameters.
—GLOBALS 文件里保存的参数争对整个GG instance,该参数可以被指定的进程参数覆盖,一般来说,GLOBALS参数一旦指定,很少修改。
A GLOBALSparameter file is required only in certain circumstances and, when used, must becreated from the command shell before starting any Oracle GoldenGate processes,including GGSCI. The GGSCI program reads the GLOBALS file and passes theparameters to processes that need them.
GLOBALS 参数文件仅当某些情况下才会使用,在使用时,必须在启动GG进程之前创建该参数文件。

To create a GLOBALS file:
1. From the Oracle GoldenGate installationlocation, run GGSCI and enter the following command, or open a file in an ASCIItext editor.
EDIT PARAMS./GLOBALS
NOTE:
The ./ portionof this command must be used, because the GLOBALS file must
reside at the root of the Oracle GoldenGateinstallation file.
—在GG的根目录运行edit 命令创建参数文件,前面必须加./,因为GLOBALS文件必须放在GG 安装的根目录下面。

2. In the file, enter the GLOBALSparameters, one per line.
3. Save the file. If you used a texteditor, save the file as GLOBALS (uppercase, without a file extension) at theroot of the Oracle GoldenGate installation directory. If you created the filecorrectly in GGSCI, the file is saved that way automatically. Do not move thisfile.
4. Exit GGSCI. You must start from a newGGSCI session before issuing commands or starting processes that reference theGLOBALS file.

1.2 Overview of runtime parameters
Runtimeparameters give you control over the various aspects of Oracle GoldenGate synchronization,such as:
(1) Data selection, mapping,transformation, and replication
(2) DDL and sequence selection,mapping, and replication (where supported)
(3) Error resolution
(4) Logging
(5) Status and error reporting
(6) System resource usage
(7) Startup and runtime behavior

There can be only one active parameter file for the Manager process or an Extract or Replicatgroup; however, you can use parameters in other files by using the OBEY parameter.
—对每个Manager Process 或者Extract 和Replicat group 只能有一个active 的Parameter file. 但是可以通过OBEY参数使用其他的参数文件。

There are twotypes of parameters: global (not to be confused with GLOBALS parameters) and object-specific:
— runtime parameters 也有两种:blobal 和object-specific.
(1) Global parameters apply to alldatabase objects that are specified in a parameter file. Some global parametersaffect process behavior, while others affect such things as memory utilizationand so forth.
global 参数影响所有参数文件中指定的databaseobject。 一些blobal 参数影响进程的行为,另一些影响内存使用等。
USERID in Figure 4 and Figure 5 is an example of a global parameter.In most cases, a global parameter can appear anywhere in the file before theparameters that specify database objects, such as the TABLE and MAP statementsin Figure 4 and Figure 5.
A global parameter should be listed only once in the file. Whenlisted more than once, only the last instance is active, and all other instancesare ignored.
(2) Object-specific parametersenable you to apply different processing rules for different sets of databaseobjects.
object-specific 参数允许对不同的databaseobject 设置不同的process rule。
GETINSERTS and IGNOREINSERTS in Figure 5 are examples ofobjectspecific parameters. Each precedes a MAP statement that specifies theobjects to be affected. Object-specific parameters take effect in the orderthat each one is listed in the file.

The following are examples of basicparameter files for Extract and Replicat.

Figure 4 Sample Extractparameter file
EXTRACT capt
USERID ggs, PASSWORD *
DISCARDFILE /ggs/capt.dsc, PURGE
RMTHOST sysb, MGRPORT 7809
RMTTRAIL /ggs/dirdat/aa
TABLE fin.*;
TABLE sales.*;

Figure 5 Sample Replicatparameter file
REPLICAT deliv
USERID ggs, PASSWORD
SOURCEDEFS /ggs/dirdef/defs
DISCARDFILE /ggs/deliv.dsc, PURGE
GETINSERTS
MAP fin.account, TARGET fin.acctab,
COLMAP (account = acct,
balance = bal,
branch = branch);
MAP fin.teller, TARGET fin.telltab,
WHERE (branch = “NY”);
IGNOREINSERTS
MAP fin.teller, TARGET fin.telltab,
WHERE (branch = “LA”);

1.3 Creating a parameter file
To create aparameter file, use the EDIT PARAMS command within the GGSCI user interface (recommended)or use a text editor directly. When you use GGSCI, you are using a standardtext editor, but your parameter file is saved automatically with the correctfile name and in the correct directory.
—可以使用ggsci 或者直接使用text editor创建parameterfile。
The EDIT PARAMScommand launches the following text editors within the GGSCI interface:
(1) Notepad on Microsoft Windowssystems
(2) The vi editor on UNIX and Linuxsystems

NOTE:
You can changethe default editor through the GGSCI interface by using the SET EDITOR command.
—可以使用set editor 命令修改ggsci 默认的editor。

To create a parameter file in GGSCI
1. From the directory where OracleGoldenGate is installed, run GGSCI.
2. In GGSCI, issue the following command toopen the default text editor.
EDIT PARAMS<group name>

Where: <groupname> is either mgr (for the Manager process) or the name of the Extract orReplicat group for which the file is being created. The name of an Extract or Replicatparameter file must match that of the process group.
—这里的group name 必须是我们我们创建的process groupname。
The followingcreates or edits the parameter file for an Extract group named extora.
EDIT PARAMS extora
The following creates or edits theparameter file for the Manager process.
EDIT PARAMS MGR

NOTE:
On Linux, thegroup and parameter file names must be all uppercase or all lowercase. Usage ofmixed case file names result in errors when starting the process.
—在Linux 下,group 和parameterfile name 必须是全部的大写或者全部的小谢,不能混合使用,否则会报错。

3. Using the editing functions of theeditor, enter as many comment lines as you want to describe this file, makingcertain that each comment line is preceded with two hyphens (—).
—注释使用- -表示。
4. On non-commented lines, enter the OracleGoldenGate parameters, starting a new line for each parameter statement.

Oracle GoldenGate parameters have thefollowing syntax:
<PARAMETER> <argument> [,<option>] [&]
Where:
. <PARAMETER> is the name of theparameter.
. <argument> is a required argumentfor the parameter. Some parameters take arguments, but others do not. Separateall arguments with commas, as in the following example:
USERID ggs, PASSWORDAACAAAAAAAAAAAIALCKDZIRHOJBHOJUH, &ENCRYPTKEY superx128
RMTHOST sysb, MGRPORT 8040
RMTTRAIL /home/ggs/dirdat/c1, PURGE
. [, <option>] is an optionalargument.
. [&] is required at the end of eachline in a multi-line parameter statement, as in the
USERID parameter statement in the previousexample.
5. Save and close the file.

1.4 Storing parameter files
When you createa parameter file with EDIT PARAMS in GGSCI, it is saved to the dirprmsubdirectory of the Oracle GoldenGate directory. You can create a parameterfile in a directory other than dirprm by specifying the full path name, but youalso must specify the full path name with the PARAMS option of the ADD EXTRACTor ADD REPLICAT command when you create the process group.
当创建了parameter 文件之后,该文件保存在GG 根目录的dirprm子目录,可以在创建文件时指定参数文件的全路径,从而指定在其他位置。
Once paired withan Extract or Replicat group, a parameter file must remain in its original locationfor Oracle GoldenGate to operate properly once processing has started.
在相关的进程启动之后,参数文件就不能改变位置。

1.5 Verifying a parameter file
You can checkthe syntax of parameters in an Extract or Replicat parameter file for accuracy.This feature is not available for other Oracle GoldenGate processes.
可以通过参数验证Extrace 和Replicat 参数事都正确,该特性只对以上2组进程有效。

To verify parameter syntax:
1. Include the CHECKPARAMSparameter in the parameter file.
—在参数文件里添加CHECKPARAMS 参数
2. Start the associated process by issuingthe START EXTRACT or START REPLICAT command in GGSCI.
START {EXTRACT |REPLICAT} <group name>

Oracle GoldenGate audits the syntax and writes the results to the report file or tothe screen. Then the process stops.
—然后启动进程,如果有错误,会将结果输出到screen,然后停止进程。
3. Do either of the following:
(1) If the syntax is correct, removethe CHECKPARAMS parameter before starting the process to process data.
—如果正确,从参数文件中移除该参数。
(2) If the syntax is wrong, correct itbased on the findings in the report. You can run another test to verify thechanges, if desired. Remove CHECKPARAMS before starting the process to processdata.
—如果报错可以根据提示进行处理。

1.6 Viewing a parameter file
You can view aparameter file directly from the command shell of the operating system, or youcan view it from the GGSCI user interface. To view the file from GGSCI, use theVIEW PARAMS command.
VIEW PARAMS<group name>

Where: <groupname> is either mgr (for Manager) or the name of the Extract or Replicat groupthat is associated with the parameter file.
—可以通过view 命令查看进程的参数。
If the parameterfile was created in a location other than the dirprm sub-directory of the OracleGoldenGate directory, specify the full path name as shown in the followingexample.
VIEW PARAMSc:\lpparms\replp.prm
—如果文件没有放在默认的目录,则指定文件的全路径。

1.7 Changing a parameter file
An OracleGoldenGate process must be stopped before editing the parameter file, and then startedagain after saving the parameter file. Changing parameter settings while aprocess is running can have unpredictable and adverse consequences, especiallyif you are adding tables or changing mapping or filtering rules.
—必须停止进程之后才能修改参数文件。 如果在进程运行时修改参数文件可能导致不可预知的错误。

To change parameters
1. Stop the process by using the followingcommand in GGSCI, unless you want to stop Manager in a Windows cluster; in thatcase, Manager must be stopped by using the Cluster Administrator.
STOP {EXTRACT |REPLICAT | MANAGER} <group name>
2. Open the parameter file by using a texteditor or the EDIT PARAMS command in GGSCI.
EDIT PARAMS mgr
3. Make the edits, and then save the file.
4. Start the process (use the ClusterAdministrator if starting Manager in a Windows
cluster).
START {EXTRACT |REPLICAT | MANAGER} <group name>

1.8 Simplifying the creation of parameter files
OracleGoldenGate provides tools that reduce the number of times that a parameter mustbe specified.
—GG 提供工具来减少指定参数的次数。
(1) Wildcards
(2) The OBEY parameter
(3) Macros
(4) Parameter substitution

Using wildcards
For parametersthat accept object names, you can use an asterisk (*) wildcard to match any numberof characters. Owner names, if used, cannot be specified with wildcards. Theuse of wildcards reduces the work of specifying numerous object names or allobjects within a given schema.
—可以使用*通配符来匹配对象。

Using OBEY
You can create alibrary of text files that contain frequently used parameter settings, and thenyou can call any of those files from the active parameter file by means of theOBEY parameter. The syntax for OBEY is:
OBEY <filename>
Where: <filename> is the relative or full path name of the file.
—可以创建一个文件包含常用的参数设置,可以在active parameter 的情况下通过OBEY来调用其他的参数。
Uponencountering an OBEY parameter in the active parameter file, Oracle GoldenGate processesthe parameters from the referenced file and then returns to the active file to processany remaining parameters.

Using macros
You can use macros to automate multipleuses of a parameter statement.

Using parameter substitution
You can useparameter substitution to assign values to Oracle GoldenGate parameters automaticallyat run time, instead of assigning static values when you create the parameterfile. That way, if values change from run to run, you can avoid having to edit theparameter file or maintain multiple files with different settings. You cansimply export the required value at runtime. Parameter substitution can be usedfor any Oracle GoldenGate process.
—使用动态参数代替静态参数

To use parameter substitution
1. For each parameter for whichsubstitution is to occur, declare a runtime parameter instead of a value,preceding the runtime parameter name with a question mark (?) as shown in thefollowing example.
在原参数文件中, 在动态参数之前加上?
SOURCEISFILE
EXTFILE ?EXTFILE
MAP ? TABNAME,TARGET account_targ;
2. Before starting the OracleGoldenGate process, use the shell of the operating system to pass the runtimevalues by means of an environment variable, as shown in Figure 6 and Figure 7.
- 在运行进程之前,先指定动态参数值,在运行,示例如下:

Figure 6 Parameter substitution on Windows
C:\GGS> set EXTFILE=C:\ggs\extfile
C:\GGS> set TABNAME=prod.accounts
C:\GGS> replicat paramfilec:\ggs\dirprm\parmfl

Figure 7 Parameter substitution on UNIX(Korn shell)
$ EXTFILE=/ggs/extfile
$ export EXTFILE
$ TABNAME=prod.accounts
$ export TABNAME
$ replicat paramfile c:\ggs\dirprm\parmfl
UNIX is case-sensitive, so the parameterdeclaration in the parameter file must be the
same case as the shell variableassignments.

二.具体GG 参数分类说明
GG的参数分为如下几类:
(1)GLOBALS parameters
(2)Manager parameters
(3)Extract parameters
(4)Replicat parameters
(5)DEFGEN parameters

2.1 GLOBALS parameters
The GLOBALS filestores parameters that relate to the Oracle GoldenGate instance as a whole, asopposed to runtime parameters for a specific process.

2.2 Manager parameters summary
Manager is theparent process of Oracle GoldenGate and is responsible for the management ofits processes, resources, user interface, and the reporting of thresholds and errors.In most cases default settings for Manager suffice.
—大多数情况下Manager 的默认参数是足够的。

2.3 Extract parameters summary
The Extractprocess captures either full data records or transactional data changes, dependingon configuration parameters, and then sends the data to a target system to be appliedto target tables or processed further by another process, such as a loadutility.

2.4 Replicat parameters summary
The Replicatprocess reads data extracted by the Extract process and applies it to target tablesor prepares it for use by another application, such as a load utility.

2.5 DEFGEN parameters summary
DEFGEN creates afile with data definitions for source or target tables. Data definitions are neededwhen the source and target tables have different definitions or the databasesare of different types.

2.6 DDL parameters summary
These parameterscontrol Oracle GoldenGate DDL support. Other parameters may be required withDDL support, but the ones here deal specifically with the DDL feature.

三.示例
3.1 Source System

ADD EXTRACT <ext>, TRANLOG, BEGIN<time>, [, THREADS]
ADD EXTTRAIL <local_trail>, EXTRACT<ext>

EDIT PARAMS <ext>
— Identify the Extract group:
EXTRACT <ext>
— Specify database login information asneeded for the database:
[SOURCEDB <dsn_1>,][USERID<user>[, PASSWORD <pw>]]
— Specify the local trail that thisExtract writes to:
EXTTRAIL <local_trail>
— Specify tables to be captured:
TABLE <owner>.<table>;

ADD EXTRACT <pump_1>, EXTTRAILSOURCE<local_trail>, BEGIN <time>
ADD RMTTRAIL <remote_trail_1>,EXTRACT <pump_1>

EDIT PARAMS <pump_1>
— Identify the data pump group:
EXTRACT <pump_1>
— Specify database login information asneeded for the database:
[SOURCEDB <dsn_1>,][USERID<user>[, PASSWORD <pw>]]
— Specify the name or IP address of thefirst target system:
RMTHOST <target_1>, MGRPORT<portnumber>
— Specify the remote trail on the firsttarget system:
RMTTRAIL <remote_trail_1>
— Allow mapping, filtering, conversion orpass data through as-is:
[PASSTHRU | NOPASSTHRU]
— Specify tables to be captured:
TABLE <owner>.<table>;

示例:
add extract extl,tranlog,begin now
ADD EXTTRAIL /u01/ogg/dirdat/rl, EXTRACTEXTL
ADD TRANDATA dave.objce_t

edit params extl
extract extl
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
userid ogg,password ogg
exttrail /u01/ogg/dirdat/rl
dynamicresolution
gettruncates
table dave.table;

ADD EXTRACT pump1, EXTTRAILSOURCE/u01/ogg/dirdat/rl, BEGIN now
add rmttrail /u01/ogg/dirdat/rl extractpump1

edit params pump1
extract pump1
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
userid ogg, password ogg
rmthost 192.168.1.111, mgrport 7809
rmttrail /u01/ogg/dirdat/rl
PASSTHRU
gettruncates
table dave.table;

3.2 Target System
edit params ./GLOBALS
CHECKPOINTTABLE<owner>.<tablename>

ADD REPLICAT <rep_1>, EXTTRAIL<remote_trail_1>, BEGIN <time> checkpointtable<owner>.<tablename>

edit params <rep_1>
— Identify the Replicat group:
REPLICAT <rep_1>
— State whether or not source and targetdefinitions are identical:
SOURCEDEFS <full_pathname> |ASSUMETARGETDEFS
— Specify database login information asneeded for the database:
[TARGETDB <dsn_3>,] [USERID <userid>[, PASSWORD <pw>]]
— Specify error handling rules:
REPERROR (<error>, <response>)
— Specify tables for delivery:
MAP <owner>.<table>, TARGET<owner>.<table>[, DEF <template name>];

示例:
edit params ./GLOBALS
CHECKPOINTTABLE ogg.chkpoint

ADD CHECKPOINTTABLE ogg.chkpoint

add replicat repl exttrail/u01/ogg/dirdat/rl,begin now,checkpointtable ogg.chkpoint

edit repl
replicat repl
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
userid ogg,password ogg
assumetargetdefs
reperror default,discard
discardfile /tmp/repsz.dsc,append,megabytes100
gettruncates
map dave.table, target dave.table;

Oracle Golden Gate 系列九 — GG 数据初始化装载 说明 与 示例

2011-11-16 15:36:37 我来说两句 收藏 我要投稿 [字体:小 大]

在前面的示例:
Oracle Golden Gate 系列六 —11gR2 Ora2Ora 单向复制GG 示例:http://www.2cto.com/database/201111/111285.html

中简单的描述了一下GG 的同步,就是先配置好GG,然后进行同步,但实际情况下,是Source 端的数据已经存在,这种情况下就要先进行initial data load,使Source 和 Target 的同步对象上Data 一致,然后在进行GG的online change synchronization。

在GG的官方文档上对initial data load 有一章节进行说明,鉴于我之前的几篇Blog都是基于理论的多,所以以后的blog会偏重于理论加示例的方式进行。理论的知识还是要先看,没有理论,其他都是空谈。

一.官网有关数据装载的说明
详细内容可以参考Administrator'sGuide 中的Running aninitial data load 小节。

You can use Oracle GoldenGate to:
(1) Perform a standalone batch loadto populate database tables for migration or other purposes.
(2) Load data into database tablesas part of an initial synchronization run in preparation for changesynchronization with Oracle GoldenGate.

The initial loadcan be performed from an active source database. Users and applications canaccess and update data while the load is running. You can perform initial loadfrom a quiesced source database if you delay access to the source tables untilthe target load is completed.

1.1 Using parallel processing in an initial load
For all initialload methods except those performed with a database utility, you can load largedatabases more quickly by using parallel Oracle GoldenGate processes.

To use parallel processing
1. Follow the directions in this chapterfor creating an initial-load Extract and an initialload Replicat for each setof parallel processes that you want to use.
2. With the TABLE and MAP parameters,specify a different set of tables for each pair of Extract-Replicat processes,or you can use the SQLPREDICATE option of TABLE to partition the rows of largetables among the different Extract processes.

1.2 Prerequisites for initial load
1.2.1 Disable DDL processing
Before executingan initial load, disable DDL extraction and replication. DDL processing iscontrolled by the DDL parameter in the Extract and Replicat parameter files.
之前整理的Blog中的第四节有说明:
Oracle Golden Gate 系列五 —GG 使用配置 说明
http://blog.csdn.net/tianlesoftware/article/details/6947973

SQL> @ddl_disable.sql; —> disable the DDL trigger.
SQL> @ddl_enable.sql; —> enable the DDL trigger.

1.2.2 Prepare the target tables
The followingare suggestions that can make the load go faster and help you to avoid errors.
以下建议可以增加load 速度和避免错误。
(1)Data: Make certain that the targettables are empty. Otherwise, there may be duplicate-row errors or conflictsbetween existing rows and rows that are being loaded.
—确保target 表是空的,否则可能会出现重复行或者行冲突。
(2)Constraints: Disable foreign-keyconstraints and check constraints. Foreign-key constraints can cause errors,and check constraints can slow down the loading process. Constraints can bereactivated after the load concludes successfully.
—禁用外键约束和check 约束,外键约束可能导致错误,而check 约束会到时load速度变慢。 约束可以待我们数据load 完成之后在激活。
(3) Indexes: Remove indexes from thetarget tables. Indexes are not necessary for inserts. They will slow down theloading process significantly. For each row that is inserted into a table, thedatabase will update every index on that table. You can add back the indexesafter the load is finished.
—移除target 表上的索引。 Target 表上的索引会增加load的时间。 如果有索引,在维护表的同时还需要维护索引的信息。 待数据load 完毕之后在重建索引,重建的速度要比维护快很多。
NOTE:
A primary indexis required for all applications that access DB2 for z/OS target tables. Youcan delete all other indexes from the target tables, except for the primaryindex.
DB2上的primary 索引是必须的。
(4)Keys: To use the HANDLECOLLISIONSfunction to reconcile incremental data changes with the load, each target tablemust have a primary or unique key. If you cannot create a key through yourapplication, use the KEYCOLS option of the TABLE and MAP parameters to specifycolumns as a substitute key for Oracle GoldenGate’s purposes. A key helps identifywhich row to process. If you cannot create keys, the source database must be quiescedfor the load.
—使用HANDLECOLLISIONS 参数可以保证load的一致性,但是该函数要求target table 必须有primary 或者unique key。 如果没有,可以使用KEYCOLS 选项来指定. 有关该参数的具体作用,在后面讲direct load 时在说明。

1.2.3 Configure theManager process
On the sourceand target systems, configure and start a Manager process. One Manager can beused for the initial-load processes and the change-synchronization processes.
Oracle Golden Gate 系列七 — 配置 GGManager process
http://blog.csdn.net/tianlesoftware/article/details/6953745

1.2.4 Create adata-definitions file
Adata-definitions file is required if the source and target databases havedissimilar definitions. Oracle GoldenGate uses this file to convert the data tothe format required by the target database.
—当source 和 target database 同步的表结构不一样时,可使用配置data-definitions file,GG 使用该文件转换data到target database 需要的格式。

1.2.5 Create change-synchronizationgroups
NOTE:
If the load isperformed from a quiet source database and will not be followed by continuouschange synchronization, you can omit these groups.
—如果使用quiet load,则可以忽略一下内容。但是对于direct load,还是需要仔细的看一下。
To prepare forthe capture and replication of transactional changes during the initial load, createonline Extract and Replicat groups. You will start these groups during the loadprocedure. See the instructions in this documentation that are appropriate forthe type of replication configuration that you will be using.
Do not start theExtract or Replicat groups until instructed to do so in the initial-load instructions.Change synchronization keeps track of transactional changes while the load isbeing applied, and then the target tables are reconciled with those changes.
—在我们load 之前,先创建Extract 和Replicat 进程,当我们进行load 时,也需要运行这2个进程。
Extrace 进程会在我们load 期间抓取变化的内容,等我们load 结束,在进行同步。
NOTE:
The first timethat Extract starts in a new Oracle GoldenGate configuration, any opentransactions will be skipped. Only transactions that begin after Extract startsare captured.

If the sourcedatabase will remain active during the initial load, include the HANDLECOLLISIONSparameter in the Replicat parameter file; otherwise do not use it.
—如果我们Source database 在load 期间还是激活的,那么必须在Replicat 参数里添加HANDLECOLLISIONS 参数。 否则不要使用。

HANDLECOLLISIONSaccounts for collisions that occur during the overlap of time between the initialload and the ongoing change replication. It reconciles insert operations forwhich the row already exists, and it reconciles update and delete operationsfor which the row does not exist.
—HANDLECOLLISIONS 控制initial load 和 changereplication 之间的一致性,如insert 时,记录已经存在,update 和delete 时 记录已经不存在。

It can be used in these ways:
(1) globally for all tables in aparameter file
(2) as an on/off toggle for groupsof tables
(3) within MAP statements to enableor disable the error handling for specific table pairs.

1.2.6 Sharing parameters between process groups
Some of theparameters that you use in a change-synchronization parameter file also are requiredin an initial-load Extract and initial-load Replicat parameter file. You cancopy those parameters from one parameter file to another, or you can store themin a central file and use the OBEY parameter in each parameter file to retrievethem. Alternatively, you can create an Oracle GoldenGate macro for the sharedparameters and then call the macro from each parameter file with the MACROparameter.

二.Loading data with an Oracle GoldenGate direct load
官网提供的Load 方法有如下几种:
(1)Loading data with a databaseutility
(2)Loading data from file to Replicat
(3)Loading data from file to databaseutility
(4)Loading data with an OracleGoldenGate direct load
(5)Loading data with a direct bulkload to SQL*Loader
(6)Loading data with Teradata loadutilities

这里只看direct load,其他的参考官方文档。

To use an OracleGoldenGate direct load, you run an Oracle GoldenGate initial-load Extract toextract the source records and send them directly to an initial-load Replicattask.
—为了使用direct load,必须先运行initial-load Extract进程,将source 段的记录抽取出来,然后将记录发送到target 端的initial-load Replicattask.

A task isstarted dynamically by the Manager process and does not require the use of a Collectorprocess or file. The initial-load Replicat task delivers the load in largeblocks to the target database. Transformation and mapping can be done byExtract, Replicat, or both. During the load, the change-synchronization groupsextract and replicate incremental changes, which are then reconciled with theresults of the load.
—这里要注意的事,initial-load Replicat task 是由ManagerProcess 控制动态启动的。 不需要人工的启动这个进程。 控制参数在Source 端的Extract 参数里配置,参数是:rmttask。

NOTE:
This method doesnot support extraction of LOB or LONG data. As an alternative, see “Loadingdata from file to Replicat” or “Loading data from file to database utility”.
—Direct Load 不支持LOB和LONG data,如果有这两种类型,可以考虑使用file toReplicat 或者 File to database utility。

You can controlwhich port is used by Replicat by specifying the DYNAMICPORTLIST parameter inthe Manager parameter file. When starting a process such as Replicat, Managerfirst looks for a port defined with DYNAMICPORTLIST. If no ports are listed,Manager chooses a port number by incrementing from its own port number until aport is available.
可以通过DYNAMICPORTLIST参数控制Replicat 使用的端口。如:
DYNAMICPORTLIST 7802-7820
注意这里如果分配的端口端少于extract-replicat进程对的话,会导致部分进程因通讯失败而出错。

OracleGoldenGate direct load does not support tables that have columns that contain LOBs,LONGs, user-defined types (UDT), or any other large data type that is greaterthan 4 KB in size.

To load data with anOracle GoldenGate direct load:
1. Make certain to satisfy “Prerequisitesfor initial load” 。
2. On the source and target systems, runGGSCI and start Manager.
START MANAGER
NOTE:
In a Windows cluster, start the Manager resource from the Cluster Administrator.

3. On the source, issue the followingcommand to create the initial-load Extract.
ADD EXTRACT<initial-load Extract name>, SOURCEISTABLE
Where:
(1) <initial-load Extract name> is the name of the initial-loadExtract, up to eight characters. –Extract 名字最多8个字符。
(2) SOURCEISTABLE designates Extract as an initial-load process thatreads complete records directly from the source tables. Do not use any of theother ADD EXTRACT service options or datasource arguments.
—标记该Extract进程是一个initial-load进程,不要和其他的参数一起使用。

4. On the source system, issue thefollowing command to create an initial-load Extract parameter file.
EDIT PARAMS<initial-load Extract name>

5. Enter the parameters listed in Table 33in the order shown, starting a new line for each parameter statement.

Table 33 Initial-load Extract parametersfor Oracle GoldenGate direct load

6. Enter any appropriate optional Extractparameters listed in the Oracle GoldenGate Windows and UNIX Reference Guide.
7. Save and close the file.

—-Target System
8. On the target system, issue thefollowing command to create the initial-load Replicat task.
ADD REPLICAT<initial-load Replicat name>, SPECIALRUN

Where:
(1) <initial-load Replicat name>is the name of the initial-load Replicat task.
(2) SPECIALRUN identifies theinitial-load Replicat as a one-time run, not a continuous process.
–注意这个参数作用,表示一次性加载完成。

9. On the target system, issue thefollowing command to create an initial-load Replicat parameter file.
EDIT PARAMS<initial-load Replicat name>

10. Enter the parameters listed in Table 34in the order shown, starting a new line for each parameter statement.

Table 34 Initial-load Replicat parametersfor Oracle GoldenGate direct load

11. Enter any appropriate optional Replicatparameters listed in the Oracle GoldenGate Windows and UNIX Reference Guide.
12. Save and close the parameter file.

13. On the source system, start changeextraction.
START EXTRACT<Extract group name>

14. (Oracle, if replicating sequences)Issue the DBLOGIN command as the user who has EXECUTE privilege onupdate.Sequence.
GGSCI>DBLOGIN USERID DBLOGINuser, PASSWORD password

15. (Oracle, if replicating sequences)Issue the following command to update each source sequence and generate redo.From the redo, Replicat performs initial synchronization of the sequences onthe target. You can use an asterisk wildcard for any or all characters in thename of a sequence (but not the owner).
FLUSH SEQUENCE<owner.sequence>

16. On the source system, start theinitial-load Extract.
START EXTRACT<initial-load Extract name>
NOTE:
Do not start theinitial-load Replicat. The Manager process starts it automatically
and terminates it when the load is finished.
—注意这里,不要在Target 端启动initial-load replicat 进程,它是自动启动,并在load 完成时自动中断。

17. On the target system, issue thefollowing command to find out if the load is finished. Wait until the load isfinished before going to the next step.
VIEW REPORT<initial-load Extract name>
—这里要注意,等load 完成之后才可以进行其他的操作。

18. On the target system, start changereplication.
START REPLICAT<Replicat group name>

19. On the target system, issue thefollowing command to verify the status of change
replication.
INFO REPLICAT<Replicat group name>

20. Continue to issue the INFO REPLICATcommand until you have verified that Replicat posted all of the change datathat was generated during the initial load. For example, if the initial-loadExtract stopped at 12:05, make sure Replicat posted data up to that point.

21. On the target system, issue thefollowing command to turn off the HANDLECOLLISIONS parameter and disable theinitial-load error handling.
SEND REPLICAT<Replicat group name>, NOHANDLECOLLISIONS
—关闭该参数,该参数的作用,前面已经说过。

22. On the target system, edit the Replicatparameter file to remove the HANDLECOLLISIONS parameter. This preventsHANDLECOLLISIONS from being enabled again the next time Replicat starts.
EDIT PARAMS<Replicat group name>

23. Save and close the parameter file. Fromthis point forward, Oracle GoldenGate continues to synchronize data changes.

三.Load 说明
在第二节中看了direct load,因为相对与其他的方法而言,这是比较直观的一种方法,其他几种官方介绍的初始化方式要么需要借助其他数据库工具(如extract->SQL*Loader),要么中间走了完全没必要的步骤导致性能很差(如extract->file->replicat方式),都不算纯正的GoldenGate方式。

Direct Load架构:

上图中,显示了初始化加载启用了两条同步路线:
(1)上面一条是真正的initial load,负责将源数据端的数据一次性发送到目标数据库;
(2)下面一条,就是普通的GoldenGate同步进程,负责抓取初始化加载时源端数据库进行的在线数据变化。

在实际应用中,往往需要在生产库(源数据库)不停机的状态下,将数据加载到备用数据库(目标数据库)中并应用实时同步,在数据初始化的过程中,生产库将继续进行正常的事务操作,所以此时需要有抓取进程在初始化时开始将这些变化捕获,以免数据丢失。

实际部署时需要注意正确的执行顺序,大致可以分为以下几步:
(1) 源端和目标端创建配置各个同步进程。
(2) 开启源端同步抓取进程(图上的Change Extract),开始捕获变化。
(3) 开启初始化进程(图上的Initial-Load Extract),开始数据初始化加载。
(4) 等初始化加载结束,开启目标端复制应用进程(图上的ChangeReplicat),开始实时同步应用。

在目标端复制应用进程(图上的Change Replicat)中,需要在参数文件中配置HANDLECOLLISIONS参数,以避免重复应用第2和第3步之间的数据变化,因为这部分数据已经包含在初始化加载中传到目标数据库中了。
该参数在前面的准备工作中有说明:
To use theHANDLECOLLISIONS function to reconcile incremental data changes with the load,each target table must have a primary or unique key.

注意:
GoldenGate的初始化同步不会也不需要去初始化目标端的SCN号。GoldenGate的同步与Streams不同,它不需要依赖两端数据库保持一致的SCN来应用同步,实际上它只在抓取时可能会与数据库的SCN有关联(抓取时可以指定源数据库的特定SCN号开始解析日志),在trail传输以及目标端应用时,都和源端数据库的SCN毫无关系。它的实质是通过自己的一套队列文件检查点机制来实现队列数据的管理,在目标端则通过数据的唯一键来定位数据行,trail文件最终解析成SQL语句在目标端数据库执行而实现数据的应用。
所以这里的初始化加载,完全可以使用其他数据库工具来实现,比如说exp/imp、SQL*Loader、RMAN复制数据库等。

也正式因为如此,所以在实际使用中,尽量使用其他高效的数据库传输工具来完成初始化加载,而不要用GoldenGate提高的初始化功能。

网上朋友的一个测试:对600万的表进行初始化,使用direct load 需要30分钟,而impdp 仅1分30秒。

exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
http://blog.csdn.net/tianlesoftware/article/details/6093973

Oracle expdp/impdp 使用示例
http://blog.csdn.net/tianlesoftware/article/details/6260138

Oracle DB Link
http://blog.csdn.net/tianlesoftware/article/details/4698642

四.DirectLoad 示例
4.1 准备工作
4.1.1 测试数据是一张270万的分区表,信息如下:
SQL> select count(1) from pdba;
COUNT(1)


2713235

SQL> desc pdba
Name Null? Type
---------— ------
ID NOT NULL NUMBER
TIME NOT NULLDATE

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME PARTITION_NAME
------ ------
PDBA P1
PDBA P2
PDBA P3
PDBA P4

4.1.2 禁用DDL,因为我之前的测试环境已经启用了DDL
gg1:/home/oracle> cd /u01/ggate/
—一定要进入GG的根目录
gg1:/u01/ggate> sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production onTue Nov 15 19:59:41 2011
SQL> @ddl_disable.sql
Trigger altered.

4.1.3 在Target端创建目标表
这里我直接使用Toad 获取表的SQL 之后,在target database上执行了一下。

其他的准备工作在上次实验中已经完成。

4.2 使用GG Direct Load 方式初始化
这里的一些准备工作就跳过,如果归档设置等,不做说明,可以参考:
Oracle Golden Gate 系列六 —11gR2 Ora2Ora 单向复制GG 示例
http://blog.csdn.net/tianlesoftware/article/details/6950018

4.2.1 Source 端操作:
(1) 添加 initial-load Extract 提取进程
GGSCI (gg1) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
EXTRACT ABENDED EXT1 00:00:00 143:13:06

GGSCI (gg1) 6> start manager
Manager started.

GGSCI (gg1) 2> add extract ext2,sourceistable
EXTRACT added.
—没有tranlog,意味着不是通过日志方式;没有begin XXX,表示还未启动;使用sourceistable参数不会使用检查点机制

GGSCI (gg1) 3> info all
Program Status Group Lag Time Since Chkpt

MANAGER STOPPED
EXTRACT ABENDED EXT1 00:00:00 143:10:18

GGSCI (gg1) 11> info extractext2

EXTRACT EXT2 Initialized 2011-11-15 20:09 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE

修改ext2的参数如下:
GGSCI (gg1) 12> view params ext2
extract ext2
userid ggate@gg1, password ggate
rmthost 192.168.3.200, mgrport 7809
rmttask replicat, group rept2
—注意是rmttask,指定目标复制进程名,直接管理target 端的进程自动启动。
table dave.pdba;

(2)增加一个抽取online log的Extract
这里我直接使用之前的ext1,相关的创建命令如下:
GGSCI (gg1) 11> add extractext1,tranlog, begin now
GGSCI (gg1) 12> add exttrail/u01/ggate/dirdat/lt, extract ext1

GGSCI (gg1) 14> view params ext1

extract ext1
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
table dave.pdba;

4.2.2 Target 端操作:
(1)添加direct load 对应的Replicat 进程
GGSCI (gg2) 4> add replicat rept2,specialrun
REPLICAT added.
— specialrun 参数表示一次性加载。

修改rept2参数如下:
GGSCI (gg2) 10> view params rept2
replicat rept2
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
reperror default, discard
discardfile/u01/ggate/dirdat/rep2_discard.txt, append, megabytes 100
BATCHSQL
INSERTAPPEND
MAP dave.pdba, TARGET dave.pdba;

注意:
这里的extract和replicat进程添加完后在info all中看不到这个进程,但是view report和 info extract name/info replicat name可以跟踪到。

(2)添加online change的Replicat 进程及checkpoint table
这里还是使用之前的rep1 进程,相关命令如下:

—在Target 端添加checkpoint表:
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint

GGSCI (gg2) 12> dblogin useridggate@gg2,password ggate
Successfully logged into database.
GGSCI (gg2) 13> add checkpointtableggate.checkpoint
Successfully created checkpointtableGGATE.CHECKPOINT.

—创建同步队列
GGSCI (gg2) 14> add replicatrep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.

修改相关的参数:
GGSCI (gg2) 11> view params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
HANDLECOLLISIONS —当目标端已有数据时,略过重复数据错误
map dave.pdba, target dave.pdba;

4.2.3 开始初始化
(1)On the source system, start changeextraction.
GGSCI (gg1) 17> START EXTRACT ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting

(2)On the source system, start theinitial-load Extract.
GGSCI (gg1) 21> START EXTRACText2
Sending START request to MANAGER …
EXTRACT EXT2 starting

(3)On the target system, issue thefollowing command to find out if the load is finished. Waituntil the load is finished before going to the next step.
VIEW REPORT<initial-load Extract name>

GGSCI (gg1) 34> info extract ext2

EXTRACT EXT2 LastStarted 2011-11-16 11:10 StatusRUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table DAVE.PDBA
2011-11-1611:14:18 Record 1610801
Task SOURCEISTABLE

GGSCI (gg1) 36> info extract ext2

EXTRACT EXT2 Last Started 2011-11-1611:10 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table DAVE.PDBA
2011-11-16 11:15:18 Record 2035567
Task SOURCEISTABLE

通过以上2个对比,1分钟近40w条数据。

GGSCI (gg1) 37> view report ext2

2011-11-16 11:09:43 INFO OGG-01017 Wildcard resolution setto IMMEDIATE because
SOURCEISTABLE is used.

***
Oracle GoldenGate Capture forOracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 30 2011 18:52:51

Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.

Starting at 2011-11-1611:09:43
***

Operating System Version:
Linux
Version #1 SMP Tue Aug 18 15:59:52 EDT2009, Release 2.6.18-164.el5xen
Node: gg1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 4804

Description:

*
Running with the followingparameters
*
extract ext2
userid ggate@gg1, password *
rmthost 192.168.3.200, mgrport 7809
rmttask replicat, group rept2
table dave.pdba;

2011-11-16 11:09:55 WARNING OGG-00869 No unique key is defined for table PDBA. All
viable columns will be used to represent thekey, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.

Using the following key columns for sourcetable DAVE.PDBA: ID, TIME.

CACHEMGR virtual memory values (may havebeen adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G

Database Version:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 -Production
NLSRTL Version 11.2.0.3.0 - Production

Database Language and Character Set:
NLS_LANG ="AMERICAN_AMERICA.zhs16gbk"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"

Processing table DAVE.PDBA

确认操作结束:
GGSCI (gg2) 25> info replicat rept2

REPLICAT REPT2 Initialized 2011-11-15 20:26 Status STOPPED
Checkpoint Lag 00:00:00 (updated 14:50:41 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

GGSCI (gg1) 39> view report ext2

2011-11-16 11:09:43 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because
SOURCEISTABLE is used.

***
Oracle GoldenGate Capture forOracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 30 2011 18:52:51

Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.

Starting at 2011-11-1611:09:43
***

Operating System Version:
Linux
Version #1 SMP Tue Aug 18 15:59:52 EDT2009, Release 2.6.18-164.el5xen
Node: gg1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 4804

Description:

*
Running with the followingparameters
*
extract ext2
userid ggate@gg1, password *
rmthost 192.168.3.200, mgrport 7809
rmttask replicat, group rept2
table dave.pdba;

2011-11-16 11:09:55 WARNING OGG-00869 No unique key is defined for table PDBA. All
viable columns will be used to represent thekey, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.

Using the following key columns for sourcetable DAVE.PDBA: ID, TIME.

CACHEMGR virtual memory values (may havebeen adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G

Database Version:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 -Production
NLSRTL Version 11.2.0.3.0 - Production

Database Language and Character Set:
NLS_LANG ="AMERICAN_AMERICA.zhs16gbk"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"

Processing table DAVE.PDBA

***

  • ** Run Time Statistics** *

***

Report at 2011-11-16 11:17:01 (activitysince 2011-11-16 11:09:55)

Output to rept2:

From Table DAVE.PDBA:
# inserts: 2713235
# updates: 0
# deletes: 0
# discards: 0

REDO Log Statistics
Bytes parsed 0
Bytes output 279463205

初始化的工作已经完成,几分钟就完成了同步工作,比之前网友测试的数据快,不过我这里的测试数据比较特殊,分区表数据虽多,但是只有2列,这个也是影响数据的一个重要因素。

(4) On the target system, start changereplication.
GGSCI (gg2) 26> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting

(5)On the target system, issue thefollowing command to verify the status of change replication.
GGSCI(gg2) 28> info replicat rep1

REPLICAT REP1 Last Started 2011-11-16 11:23 Status RUNNING
Checkpoint Lag 00:00:00 (updated 158:24:45 ago)
Log Read Checkpoint File /u01/ggate/dirdat/lt000001
2011-11-0910:39:45.497561 RBA 1009

(6). On the target system, issue thefollowing command to turn off the HANDLECOLLISIONS parameter and disable theinitial-load error handling.
取消HANDLECOLLISIONS参数
GGSCI (gg2) 30> send replicatrep1,NOHANDLECOLLISIONS

Sending NOHANDLECOLLISIONS requestto REPLICAT REP1 …
REP1 No tables found matchingGGATE.* to set NOHANDLECOLLISIONS.

(7) On the target system, edit theReplicat parameter file to remove the HANDLECOLLISIONS parameter. This preventsHANDLECOLLISIONS from being enabled again the next time Replicat starts.

GGSCI (gg2) 32> view params rep1

replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
—HANDLECOLLISIONS
map dave.pdba, target dave.pdba;

至此,使用DirectLoad 方式初始的测试完成。

4.3 使用expdp/impdp 进行初始化
在前面演示了使用Direct Load 的方式进行初始化,当我们的表很大时,这种方法可能会需要较长的时间,这种情况下,我们可以考虑使用dblink 直接从Source db 将数据同步到target db。

这个步骤大致如下:
(1) 配置同步的Change Extract 和 Change Replicat 进程
(2) 启动Change Extract 进程,捕捉改变的数据。
(3) 使用expdp/impdp 迁移数据
(4) 完成迁移后启动Change replicat,完成数据同步。

4.3.1 准备工作
—先启用我们的DDL支持
SQL> @ddl_enable.sql
Trigger altered.

—truncate target db 上的pdba 表
SQL> truncate table pdba;
Table truncated.

4.3.2 添加change Extract 和 change Replicat 进程
—change Extract 进程
GGSCI (gg1) 11> add extractext1,tranlog, begin now
GGSCI (gg1) 12> add exttrail/u01/ggate/dirdat/lt, extract ext1

GGSCI (gg1) 14> view params ext1
extract ext1
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
table dave.pdba;

Change Replicat 进程
在Target 端添加checkpoint表:
GGSCI (gg2) 6> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint

GGSCI (gg2) 12> dblogin useridggate@gg2,password ggate
Successfully logged into database.
GGSCI (gg2) 13> add checkpointtableggate.checkpoint
Successfully created checkpointtableGGATE.CHECKPOINT.

—创建同步队列
GGSCI (gg2) 14> add replicatrep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.

修改相关的参数:
GGSCI (gg2) 11> view params rep1
replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile/u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
HANDLECOLLISIONS —当目标端已有数据时,略过重复数据错误
map dave.pdba, target dave.pdba;

4.3.3 在Source 端启动changeExtract进程:ext1
启动Extract 进程之后就会捕捉Source 端的变化。
GGSCI (gg1) 43> view params ext1
extract ext1
userid ggate@gg1, password ggate
rmthost gg2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
table dave.pdba;

GGSCI (gg1) 44> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:02

4.3.4 使用expdp 导出导入数据
Oracle expdp/impdp 使用示例
http://blog.csdn.net/tianlesoftware/article/details/6260138

gg1:/home/oracle> expdp dave/dave directory=backup dumpfile=pdba.dmp logfile=table.log tables=pdba;

Export: Release 11.2.0.3.0 - Production onWed Nov 16 12:21:32 2011

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
Starting"DAVE"."SYS_EXPORT_TABLE_02": dave/ directory=backupdumpfile=pdba.dmp logfile=table.log tables=pdba
Estimate in progress using BLOCKS method…
Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 60 MB
Processing object typeTABLE_EXPORT/TABLE/TABLE
. . exported"DAVE"."PDBA" 49.14 MB 2713235rows
Master table"DAVE"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
**
Dump file set for DAVE.SYS_EXPORT_TABLE_02is:
/u01/backup/pdba.dmp
Job"DAVE"."SYS_EXPORT_TABLE_02" successfully completed at12:22:16

在Source DB上删除一部分数据,已体现变化。
SQL> select count(1) from pdba;
COUNT(1)


2713235
SQL> select count(1) from pdba wheretrunc(time)=to_date('2010/1/11','yyyy/mm/dd');

COUNT(1)


6613

删除这6613条记录:
SQL> Delete from pdba wheretrunc(time)=to_date('2010/1/11','yyyy/mm/dd');
6613 rows deleted.
SQL> commit;
Commit complete.

SQL> select count(1) from pdba;

COUNT(1)


2706622

将dump 传到备库,然后impdp进去:
gg1:/u01/backup> scp pdba.dmp 192.168.3.200:/u01/backup
The authenticity of host '192.168.3.200(192.168.3.200)' can't be established.
RSA key fingerprint is04:39:b6:d7:61:44:18:42:80:4b:37:1a:31:5d:a7:55.
Are you sure you want to continueconnecting (yes/no)? yes
Warning: Permanently added '192.168.3.200'(RSA) to the list of known hosts.
002.3.861.291|elcaro#002.3.861.291|elcaro's password:
pdba.dmp 100% 49MB 3.5MB/s 00:14

gg2:/u01/backup> impdp dave/davedirectory=backup dumpfile=pdba.dmp logfile=table.log tables=pdba table_exists_action=replace;

Import: Release 11.2.0.3.0 - Production onWed Nov 16 12:39:40 2011

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Master table "DAVE"."SYS_IMPORT_TABLE_01"successfully loaded/unloaded
Starting"DAVE"."SYS_IMPORT_TABLE_01": dave/ directory=backupdumpfile=pdba.dmp logfile=table.log tables=pdba table_exists_action=replace
Processing object typeTABLE_EXPORT/TABLE/TABLE
Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA
. . imported"DAVE"."PDBA" 49.14 MB 2713235rows
Job"DAVE"."SYS_IMPORT_TABLE_01" successfully completed at12:40:16

4.3.5 启动Target 的Replicat 进程

SQL> conn dave/dave;
Connected.
SQL> select count(1) from pdba;

COUNT(1)


2713235

GGSCI (gg2) 37> start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (gg2) 38> info all
Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:28:17

SQL> select count(1) from pdba;

COUNT(1)


2706622

在次查看,我们之前delete的数据,在Target 端也被delete 了。

清除Replicat 进程上的HANDLECOLLISIONS参数:
GGSCI (gg2) 40> send replicatrep1,NOHANDLECOLLISIONS

Sending NOHANDLECOLLISIONS request toREPLICAT REP1 …
REP1 No tables found matching GGATE.* toset NOHANDLECOLLISIONS.

该参数是修改当前进程,使其生效,这样就不用重启replicat 进程。

修改参数,下次重启时生效:
GGSCI (gg2) 41> edit params rep1
GGSCI (gg2) 42> view params rep1

replicat rep1
ASSUMETARGETDEFS
userid ggate@gg2,password ggate
discardfile /u01/ggate/dirdat/rep1_discard.txt,append, megabytes 10
—HANDLECOLLISIONS
map dave.pdba, target dave.pdba;

至此,有关GG 数据初始化的内容就全部结束。