环境
操作系统:Redhat 5.4 x86
数据库版本:Oracle 10.2.0.1
Golden Gate : V11.1.1.1.2 for Oracle 10g on Linux x86
源端: hostname:ylptnode1
目标端:hostname:ylptnode2
一准备工作
1 创建用户,目录,组
mkdir /u01/ggate
useradd -g oinstall -G dba oraogg
chown -R oraogg:oinstall /u01/ggate
passwd oraogg
2设置环境变量
vi ~/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export OGG_HOME=/u01/ggate
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ggate
export PATH=$ORACLE_HOME/bin:/u01/ggate:$PATH
export ORACLE_SID=prod1
umask 022
二安装
1解压
[oraogg@ylptnode1 ggate]$ unzip V28942-01.zip
Archive: V28942-01.zip
inflating: fbo_ggs_Linux_x86_ora10g_32bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.2.pdf
inflating: Oracle_GoldenGate_11.1.1.1_README.txt
[oraogg@ylptnode1 ggate]$ tar xvf fbo_ggs_Linux_x86_ora10g_32bit.tar
2 创建子目录
./ggsci
create subdirs
[oraogg@ylptnode1 ggate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 10g on Oct 4 2011 23:54:04
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (ylptnode1) 1> create subdirs
Creating subdirectories under current directory /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
二.源数据库配置
配置源数据库归档
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO NO
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data (primary key,unique,foreign key) columns;
Database altered.
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG IMPLICIT YES
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG IMPLICIT YES
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database;
SUPPLEME SUP SUP
-------- --- ---
IMPLICIT YES YES
SQL>
创建测试用户
--source
create user test identified by oracle default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to test;
--target
create user test identified by oracle default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to test;
三 配置对DDl的支持
alter system set recyclebin=off scope=spfile;
重启数据库
创建goldengate管理用户
create user ggate identified by ggate default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to ggate;
grant execute on utl_file to ggate;
--Target 端也要执行
cd /u01/ggate/
@/u01/ggate/marker_setup.sql;
@/u01/ggate/ddl_setup.sql;
@/u01/ggate/role_setup.sql;
grant GGS_GGSUSER_ROLE to ggate;
@/u01/ggate/ddl_enable.sql;
执行日志:
[oraogg@ylptnode1 ggate]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 25 16:07:49 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @/u01/ggate/marker_setup.sql;
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:ggate
输入管理用户
Marker setup table script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script. complete.
SQL> @/u01/ggate/ddl_setup.sql;
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
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:ggate
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
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using GGATE 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 GGATE
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
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/prod1/udump/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script. complete.
SQL> @/u01/ggate/role_setup.sql;
GGS Role setup script
This script. will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script. and then edit the params.sql script. to change the gg_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:ggate
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, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ggate;
Grant succeeded.
SQL> @/u01/ggate/ddl_enable.sql;
Trigger altered.
四 goldengate配置
1 启动管理进程(在source 端和 target 端)
[oraogg@localhost gg]$ ./ggsci
GGSCI (localhost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (localhost) 2> edit params mgr
PORT 7809
ggate (localhost) 3> start manager
Manager started.
2配置extract
--source 端
add extract ext1,tranlog, begin now
add exttrail /u01/ggate/dirdat/lt, extract ext1
配置Extract 参数如下:
edit params ext1
输入如下内容
extract ext1
userid ggate, password ggate
ddl include mapped objname test.*;
table test.*;
2创建Data Pump Group
--在Source 端
add extract dpump,exttrailsource /u01/ggate/dirdat/lt
add rmttrail /u01/ggate/dirdat/lt, extract dpump
edit params dpump 输入如下内容
extract dpump
userid ggate@prod1, password ggate
rmthost ylptnode2, mgrport 7809
rmttrail /u01/ggate/dirdat/lt
passthru
table test.*;
3配置Replicat 进程
--在Target 端
./ggsci
EDIT PARAMS ./GLOBALS
输入如下内容:
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
--执行如下命令
dblogin userid ggate,password ggate
add checkpointtable ggate.checkpoint
--创建replicat group
add replicat rep1,exttrail /u01/ggate/dirdat/lt, checkpointtable ggate.checkpoint
edit params rep1 输入如下内容
replicat rep1
ASSUMETARGETDEFS
userid ggate,password ggate
discardfile /u01/ggate/dirdat/rep1_discard.txt, append, megabytes 10
--HANDLECOLLISIONS
DDL
map test.*, target test.*;
--五启动测试
start extract ext1
start extract dpump
--source
start replicat rep1
[oraogg@ylptnode1 ggate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 10g on Oct 4 2011 23:54:04
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (ylptnode1) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:06
EXTRACT RUNNING EXT1 00:00:00 00:00:00
[oraogg@ylptnode2 ggate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 10g on Oct 4 2011 23:54:04
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (ylptnode2) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15747463/viewspace-775262/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15747463/viewspace-775262/