一、准备工作： 二、安装 三、配置 1.配置mgr： 2.配置采集进程demo. 3.配置投递进程dpdemo. 4.配置mgr： 5.创建checkpoint表 6.配置接受进程repdemo 四、增加DDL同步的配置
1. 对于源端数据库，要做以下准备。
a . 在数据库中创建GoldenGate数据库用户， 名称以 goldengate 为例，用户至少应该有 connect,resource,select any dictionary,select any table 的权限
SQL>create user goldengate identified by goldengate;
SQL>grant dba to goldengate;
b . 检查源端数据库是否为归档模式，若为非归档模式，建议将其改为归档模式，在非归档模式下也能运行
SQL> alter database archivelog;
SQL>archive log list;
c . 检查源端数据库附加日志是否打开
SQL>
将数据库附加日志打开
SQL>alter database add supplemental log data;
d. 将数据库置为force loogging状态
SQL> show user;
USER is "SYS"
SQL> alter database force loogging;
Database altered.
SQL> select force_loogging from v$database;
FOR
---
YES2 ． 在目标数据库上，也需要创建用户。
SQL>create user goldengate identified by goldengate;
SQL>grant dba to goldengate;
在源端和目标端执行：
将下载好的ogg 安装包解压到/u01/app/oracle/ogg/ 目录下，在oracle 用户下执行
[oracle@node1 ~]$ cd ogg
[oracle@node1 ogg]$ ./oggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 13:22:25
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
OGGSCI (node1) 1>create subdirs --GG12C 不需要
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm: created
Report files /u01/app/oracle/ogg/dirrpt: created
Checkpoint files /u01/app/oracle/ogg/dirchk: created
Process status files /u01/app/oracle/ogg/dirpcs: created
SQL script files /u01/app/oracle/ogg/dirsql: created
Database definitions files /u01/app/oracle/ogg/dirdef: created
Extract data files /u01/app/oracle/ogg/dirdat: created
Temporary files /u01/app/oracle/ogg/dirtmp: created
Veridata files /u01/app/oracle/ogg/dirver: created
Veridata Lock files /u01/app/oracle/ogg/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/oracle/ogg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/ogg/dirver/oosxml: created
Veridata Parameter files /u01/app/oracle/ogg/dirver/params: created
Veridata Report files /u01/app/oracle/ogg/dirver/report: created
Veridata Status files /u01/app/oracle/ogg/dirver/status: created
Veridata Trace files /u01/app/oracle/ogg/dirver/trace: created
Stdout files /u01/app/oracle/ogg/dirout: created 安装完成
源数据库中配置
oracle@node1 ogg]$ ./oggsci
oggsci>dblogin userid goldengate, password goldengate
Successfully loogged into database.
这里 username 是登录数据库的用户，它不一定是刚才我们为 goldengate 建立的数据库用户，但它必须有 dba 权限 ,password 是数据库登录的密码 , 登录后运行：
oggsci> <schema_name>.<table_name>
这里 schema_name 和 table_name 就不用解释了，它们是 goldengate 要同步的所有表，也就是说针对每一张表，都要做一遍，当然你可以采用 add trandata <schema_name>.* ，或者采用 sqlplus 中的 select from dba_tables 批量生成脚本。
这一步非常关键，如果漏掉，同步时 insert ， delete 可能会没问题，但 update 肯定报错。
配置 mgr ：
goldengate 安装完成后，首先需要在源和目标各 配置一个 manager 管理 进程 ，然后启动，首先在 oggsci 下运行
[oracle@node1 ogg]$ ./oggsci
OGGSCI (node1) 1>edit params mgr
加入如下内容
port 7809
purgeoldextracts /u01/app/oracle/ogg/dirdat/*
purgeoldextracts /u01/app/oracle/ogg/dirdat/* 是清除过期的 trail 文件
存盘退出，然后运行 start mgr ，并检查 manager 进程的状态
OGGSCI (node1) 2> info mgr
Manager is running (IP port node1.7809).
OGGSCI (node1) 3>
在源和目标上做同样的操作，务必保证源和目标的 manager 进程正常运行，否则后面的配置会出现错误。至此 goldengate manager 管理进程配置完毕
配置采集进程 ext_prod.
OGGSCI (node1) 1>add extract ext_prod,tranlog,begin now
OGGSCI (node1) 1>add exttrail /u01/app/oracle/ogg/dirdat/mm, extract ext_prod
OGGSCI (node1) 1>edit params ext_prod
然后添加以下语句：
extract ext_prod
setenv (NLS_LANG=american_america.ZHS16GBK)
setenv (ORACLE_SID=orcl)
userid goldengate,password oracle
gettruncates
warnlongtrans 2h,checkinterval 3m
tranlogoptions convertucs2clobs
fetchoptions nousesnapshot
exttrail /u01/app/oracle/ogg/dirdat/mm
discardfile /u01/app/oracle/ogg/dirrpt/mm.dsc,append,megabytes 100
dynamicresolution
table scott.*; 保存退出。
OGGSCI (node1) 1>start ext_prod
OGGSCI (node1) 2> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DEMO 00:00:00 00:00:04 至此，demo 进程配置完毕
配置投递进程 dpdemo.
OGGSCI (node1) 1>add extract dpdemo,exttrailsource /u01/app/oracle/ogg/dirdat/mm
OGGSCI (node1) 2>add rmttrail /u01/app/oracle/ogg/dirdat/mm,extract dpdemo
OGGSCI (node1) 3>edit params dpdemo 添加如下内容：
extract dpdemo
setenv (NLS_LANG=american_america.ZHS16GBK)
passthru
userid goldengate,password oracle
discardfile /u01/app/oracle/ogg/dirrpt/dpdemo.dsc,append,megabytes 100
rmthost 192.168.0.97,mgrport 7809,compress
rmttrail /u01/app/oracle/ogg/dirdat/mm
dynamicresolution
table scott.*;
保存退出
OGGSCI (node1) 1>start dpdemo
OGGSCI (node1) 2>info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DEMO 00:00:00 00:00:09
EXTRACT RUNNING DPDEMO 00:00:00 00:00:04
至此，dpdemo 进程配置完毕
目标端配置
配置 mgr ：
goldengate 安装完成后，首先需要在源和目标各 配置一个 manager 管理 进程 ，然后启动，首先在 oggsci 下运行
[oracle@node2 ogg]$ ./oggsci
OGGSCI (node2) 1>edit params mgr
加入如下内容
port 7809
purgeoldextracts /u01/app/oracle/ogg/dirdat/*
存盘退出，然后运行 start mgr ，并检查 manager 进程的状态：
OGGSCI (node2) 2> info mgr
Manager is running (IP port node1.7809).
OGGSCI (node2) 3>
至此 goldengate manager 管理进程配置完毕 .
创建 checkpoint 表
OGGSCI (node2) 1>dblogin userid goldengate,password oracle
OGGSCI (node2) 2>add checkpointtable scott.ct
配置接受进程 repdemo
add replicat repdemo,exttrail /u01/app/oracle/ogg/dirdat/mm,begin now,checkpointtable scott.ct
OGGSCI (node2) 2>edit params repdemo 添加如下内容：
replicat repdemo
setenv (NLS_LANG=american_america.ZHS16GBK)
setenv (ORACLE_SID=orcl)
userid goldengate,password oracle
sqlexec "alter session set constraints=deferred"
handlecollisions
reperror default,discard
discardfile /u01/app/oracle/ogg/dirrpt/repdemo.dsc,append,megabytes 100
gettruncates
assumetargetdefs
allownoopupdates
dynamicresolution
map scott.*, target scott.*;
保存退出。
OGGSCI (node2) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPDEMO 00:00:00 00:00:04
至此，repdemo 配置完毕。
1. 同步DDL 要求我们关闭10g 中的回收站特性
SQL> alter system set recyclebin=off;
System altered.2. 以SYSDBA 登陆数据库并执行当前OGG 安装目录下的marker_setup 脚本，该脚本用以建立一个DDL 标记表
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name: goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete. 3. 以INITIALSETUP 选项运行ddl_setup.sql 将在数据库中创建捕获DDL 语句的Triogger 等必要组件
SQL> @ddl_setup
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
Checking user sessions...
Check complete.
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name :goledengate
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation :INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using MACLEAN as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
CLEAR_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
OGGS_TEMP_COLS
-----------------------------------
OK
OGGS_TEMP_UK
-----------------------------------
OK
DDL TRIOGGER CODE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL TRIOGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIOGGER RUNNING STATUS
-----------------------------------
ENABLED
STAYMETADATA IN TRIOGGER
-----------------------------------
OFF
DDL TRIOGGER SQL TRACING
-----------------------------------
0
DDL TRIOGGER TRACE LEVEL
-----------------------------------
0
LOCATION OF DDL TRACE FILE
--------------------------------------------------------------------------------
/s01/10gdb/admin/clinica/udump/oggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.4.role_setup 脚本用以建立OGGS_OGGSUSER_ROLE 角色
SQL> @role_setup
OGGS Role setup script
This script will drop and recreate the role OGGS_OGGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to
change the ogg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name: goldengate
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, OGGSCI, and Manager processes, by using the following SQL command:
GRANT OGGS_OGGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.5. 我们需要将该OGGS_OGGSUSER_ROLE 授予给extract group 参数中定义的userid 用户
SQL> grant OGGS_OGGSUSER_ROLE to goldengate;
Grant succeeded.6.ddl_enable.sql 将正式enable ddl 捕获触发器，即:ALTER TRIOGGER sys .&ddl_triogger_name ENABLE
SQL> @ddl_enable
Triogger altered.7. 执行dbmspool 包将在数据库中创建DBMS_SHARED_POOL 包，之后需要用到
SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.8.ddl_pin.sql 通过dbms_shared_pool.keep 存储过程将DDLReplication 相关的对象keep 在共享池中， 以保证这些对象不要reload ，提升性能
SQL> @ddl_pin
Enter value for 1: maclean
PL/SQL procedure successfully completed.
Enter value for 1: maclean
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.9. 在源数据端配置
[oracle@node1 ogg]$ ./oggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 13:22:25
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
OGGSCI (node1) 1>edit params demo 添加以下语句:
DDL INCLUDE ALL
OGGSCI (node1) 2>stop demo
OGGSCI (node1) 3>start demo 启动成功，源端配置完成。10. 在目标端配置:
[oracle@node2 ogg]$ ./oggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 13:22:25
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
OGGSCI (node2) 1>edit params repdemo 添加以下语句:
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
OGGSCI (node2) 2>stop repdemo
OGGSCI (node2) 3>start repdemo 启动成功，目标端配置完成。
