配置支援DML和DDL操作同步的GoldenGate

測試環境描述
SOURCE：
主機：vmware workstation 7.0
作業系統：CENTOS 5.5 32bit
資料庫版本： ORACLE 10.2.0.4
IP：192.168.0.88
SID:orcl
TARGET:
主機：vmware workstation 7.0
作業系統：CENTOS 5.5 32bit
資料庫版本： ORACLE 10.2.0.4
IP：192.168.0.88
SID:ggtarge

以下操作需要操作如無特殊說明，均為在源資料庫和目標資料庫上操作
1、安裝GoldGate
1)將Goldgate的壓縮包傳送到相對應的目錄，並解壓
[ora10@dev160 gg10]$ ls -l
total 401000
-rwxr-xr-x 1 ora10 dba    500964 Aug 6 2010 OGG_WinUnix_Rel_Notes_11.1.1.0.0_078.pdf
-rwxrwxr-x 1 ora10 dba     26726 Aug 3 2010 README.txt
drwxr-x--- 7 ora10 dba      4096 Jul 29 2010 UserExitExamples
-r--r--r-- 1 ora10 dba       426 Mar 12 2007 bcpfmt.tpl
-r--r--r-- 1 ora10 dba      1725 Sep 11 2002 bcrypt.txt
-r--r--r-- 1 ora10 dba       739 Mar 13 2010 chkpt_ora_create.sql
-rwxr-xr-x 1 ora10 dba   7960573 Jul 29 2010 cobgen
-rwxr-xr-x 1 ora10 dba   7890642 Jul 29 2010 convchk
-r--r--r-- 1 ora10 dba       159 Mar 12 2007 db2cntl.tpl
-r--r--r-- 1 ora10 dba      3334 Mar 12 2007 ddl_access.tpl
-r--r--r-- 1 ora10 dba      1059 Mar 13 2010 ddl_cleartrace.sql
-r--r--r-- 1 ora10 dba      3017 Mar 12 2007 ddl_db2.tpl
-r--r--r-- 1 ora10 dba      3502 Mar 12 2007 ddl_db2_os390.tpl
-r--r--r-- 1 ora10 dba      4189 Mar 13 2010 ddl_ddl2file.sql
-r--r--r-- 1 ora10 dba       746 Mar 13 2010 ddl_disable.sql
-r--r--r-- 1 ora10 dba       692 Mar 13 2010 ddl_enable.sql
-r--r--r-- 1 ora10 dba      2984 Mar 12 2007 ddl_informix.tpl
-r--r--r-- 1 ora10 dba      3414 Mar 23 2010 ddl_mss.tpl
-r--r--r-- 1 ora10 dba      3481 Jun 2 2007 ddl_mysql.tpl
-r--r--r-- 1 ora10 dba       388 Mar 13 2010 ddl_nopurgeRecyclebin.sql
-r--r--r-- 1 ora10 dba      2653 Mar 18 2007 ddl_nssql.tpl
-r--r--r-- 1 ora10 dba     12424 May 22 2010 ddl_ora10.sql
-r--r--r-- 1 ora10 dba      3863 May 22 2010 ddl_ora10upCommon.sql
-r--r--r-- 1 ora10 dba     11064 Mar 13 2010 ddl_ora11.sql
-r--r--r-- 1 ora10 dba     12365 Mar 13 2010 ddl_ora9.sql
-r--r--r-- 1 ora10 dba      3458 Mar 12 2007 ddl_oracle.tpl
-r--r--r-- 1 ora10 dba      1026 Mar 13 2010 ddl_pin.sql
-r--r--r-- 1 ora10 dba      1227 Mar 13 2010 ddl_purgeRecyclebin.sql
-r--r--r-- 1 ora10 dba      3686 May 14 2010 ddl_remove.sql
-r--r--r-- 1 ora10 dba      1053 Jun 24 2009 ddl_session.sql
-r--r--r-- 1 ora10 dba       425 Jun 30 2009 ddl_session1.sql
-r-xr-xr-x 1 ora10 dba    228606 Jun 30 2010 ddl_setup.sql
-r--r--r-- 1 ora10 dba      2652 Jun 7 2007 ddl_sqlmx.tpl
-r--r--r-- 1 ora10 dba      8872 Mar 13 2010 ddl_status.sql
-r--r--r-- 1 ora10 dba      2506 Mar 13 2010 ddl_staymetadata_off.sql
-r--r--r-- 1 ora10 dba      2501 Mar 13 2010 ddl_staymetadata_on.sql
-r--r--r-- 1 ora10 dba      3523 Mar 12 2007 ddl_sybase.tpl
-r--r--r-- 1 ora10 dba      2653 Mar 12 2007 ddl_tandem.tpl
-r--r--r-- 1 ora10 dba      2543 Mar 13 2010 ddl_trace_off.sql
-r--r--r-- 1 ora10 dba      2862 Mar 13 2010 ddl_trace_on.sql
-r--r--r-- 1 ora10 dba      2955 Mar 13 2010 ddl_tracelevel.sql
-rwxr-xr-x 1 ora10 dba   8039613 Jul 29 2010 ddlcob
-rwxr-xr-x 1 ora10 dba   9415801 Jul 29 2010 ddlgen
-rwxr-xr-x 1 ora10 dba   9289214 Jul 29 2010 defgen
-r--r--r-- 1 ora10 dba      1217 Mar 13 2010 demo_more_ora_create.sql
-r--r--r-- 1 ora10 dba       967 Mar 13 2010 demo_more_ora_insert.sql
-r--r--r-- 1 ora10 dba       883 Mar 13 2010 demo_ora_create.sql
-r--r--r-- 1 ora10 dba       821 Mar 13 2010 demo_ora_insert.sql
-r--r--r-- 1 ora10 dba      4015 Mar 13 2010 demo_ora_lob_create.sql
-r--r--r-- 1 ora10 dba      2275 Mar 13 2010 demo_ora_misc.sql
-r--r--r-- 1 ora10 dba      1269 Mar 13 2010 demo_ora_pk_befores_create.sql
-r--r--r-- 1 ora10 dba      1227 Mar 13 2010 demo_ora_pk_befores_insert.sql
-r--r--r-- 1 ora10 dba      2520 Mar 13 2010 demo_ora_pk_befores_updates.sql
-rwxr-xr-x 1 ora10 dba   8432527 Jul 29 2010 emsclnt
-rwxr-xr-x 1 ora10 dba 39121727 Jul 29 2010 extract
-r--r--r-- 1 ora10 dba      1968 May 14 2009 freeBSD.txt
-r--r--r-- 1 ora10 dba    448512 Jul 7 2010 ggMessage.dat
-rw-rw---- 1 ora10 dba 204902400 Jul 29 2010 ggs_Linux_x64_ora10g_64bit_v11_1_1_0_0_078.tar
-rwxr-xr-x 1 ora10 dba 16248101 Jul 29 2010 ggsci
-r--r--r-- 1 ora10 dba    133635 Jul 17 2010 help.txt
-rwxr-xr-x 1 ora10 dba    105182 Jul 29 2010 keygen
-r--r--r-- 1 ora10 dba 11360748 Mar 20 2008 libicudata.so.38
-r--r--r-- 1 ora10 dba   5359083 Mar 20 2008 libicui18n.so.38
-r--r--r-- 1 ora10 dba   4034679 Mar 20 2008 libicuuc.so.38
-r-xr-xr-x 1 ora10 dba   5323945 Feb 2 2010 libxerces-c.so.28
-r--r--r-- 1 ora10 dba      1668 Mar 9 2005 libxml2.txt
-rwxr-xr-x 1 ora10 dba   1350044 Jul 29 2010 logdump
-r--r--r-- 1 ora10 dba      2162 Mar 13 2010 marker_remove.sql
-r--r--r-- 1 ora10 dba      3702 Mar 13 2010 marker_setup.sql
-r--r--r-- 1 ora10 dba      1715 Mar 13 2010 marker_status.sql
-rwxr-xr-x 1 ora10 dba 21980806 Jul 29 2010 mgr
-r--r--r-- 1 ora10 dba    206097 Jan 16 2010 notices.txt
-r--r--r-- 1 ora10 dba      4539 May 21 2010 params.sql
-rwxr-xr-x 1 ora10 dba 33431908 Jul 29 2010 replicat
-rwxr-xr-x 1 ora10 dba    401915 Jul 29 2010 reverse
-r--r--r-- 1 ora10 dba      4042 Mar 13 2010 role_setup.sql
-rwxr-xr-x 1 ora10 dba 13569641 Jul 29 2010 server
-r--r--r-- 1 ora10 dba       248 Mar 12 2007 sqlldr.tpl
-r--r--r-- 1 ora10 dba       759 Mar 12 2007 tcperrs
-r--r--r-- 1 ora10 dba     21197 Apr 6 2010 usrdecs.h
-r--r--r-- 1 ora10 dba      1476 Mar 9 2005 zlib.txt
2)設定環境變數
[ora10@dev160 gg10]$ vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export LANG=en_US:zh_CN.UTF-8
export LC_ALL=C
#export DISPLAY=192.168.161.200:0
export ORACLE_BASE=/ora10
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#export ORACLE_SID=ora10
export ORACLE_SID=crm20
export LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/jre/lib/i386/server:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:/home/ora10/gg10:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:/home/ora10/gg10:$PATH
export NLS_LANG=American_America.ZHS16GBK
3)初始化
GGSCI (ora10g) 4> create subdirs

Creating subdirectories under current directory /u01/ora10g/gg10

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

3)測試
[ora10@dev160 gg10]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

GGSCI (dev160) 1> help

GGSCI Command Summary:

Object:          Command:
SUBDIRS          CREATE
ER               INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                 LAG, SEND, START, STATS, STATUS, STOP
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
MANAGER          INFO, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
                 START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO

Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD
(DDL)            DUMPDDL
(Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,
                 SHOW, VERSIONS, ! (note: you must type the word
                 COMMAND after the ! to display the ! help topic.)
                 i.e.: GGSCI (sys1)> help ! command

For help on a specific command, type HELP

Example: HELP ADD REPLICAT

GGSCI (dev160) 2> exit
2、建立管理使用者及賦權：
create user GGUSER
IDENTIFIED by GGUSER
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
quota unlimited on USERS;
-- Grant/Revoke role privileges
grant connect to GGUSER;
grant resource to GGUSER;
-- Grant/Revoke system privileges
grant unlimited tablespace to GGUSER;
3、由於GG需要關閉10G的回收站機制，因此進行如下操作:
SQL> alter system set recyclebin=off;
System altered.
4、由於GG需要開啟資料庫的歸檔，因此需要進行如下操作：
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Current log sequence           42
SQL> alter database close
2 ;

Database altered.

SQL> alter database archive;
alter database archive
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database archivelog;

Database altered.

SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 222298112 bytes
Fixed Size                  1266680 bytes
Variable Size             134220808 bytes
Database Buffers           79691776 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

5、安裝同步DLL語句要用到的GoldenGate指令碼
1）執行marker_setup指令碼，該指令碼用以建立一個DDL標記表
SQL> @/ora10/gg10/marker_setup（此處檔案位置以具體各主機地址為準）
[ora10@dic5f88 gg10]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 7 08:35:10 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/ora10/gg10/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:gguser

Marker setup table script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSER

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script. complete.

注：由於在一臺機器上進行，因此在完成一個例項的某一步聚的安裝後，需要退出sqlplus，以防sqlplus將訪問過的檔案鎖住。
2）以INITIALSETUP選項執行ddl_setup.sql 將在資料庫中建立捕獲DDL語句的Trigger等必要元件
SQL> @/u01/ora10g/gg10/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:gguser

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 GGUSER 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 GGUSER

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/ora10g/admin/ggtarge/udump/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script. complete.
3）role_setup指令碼用以建立GGS_GGSUSER_ROLE角色
SQL> @/u01/ora10g/gg10/role_setup

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:gguser
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.
4）按上一步的提示，將許可權賦給在 Extract, GGSCI, and Manager processes中指定的使用者gguser
SQL> grant GGS_GGSUSER_ROLE gguser;
grant GGS_GGSUSER_ROLE gguser
*
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant GGS_GGSUSER_ROLE to gguser;

Grant succeeded.
5)ddl_enable.sql將正式enable ddl捕獲觸發器，即:ALTER TRIGGER sys .&ddl_trigger_name ENABLE
SQL> @/u01/ora10g/gg10/ddl_enable;

Trigger altered.
6)執行dbmspool包將在資料庫中建立DBMS_SHARED_POOL包，之後需要用到

SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.
7)ddl_pin.sql通過dbms_shared_pool.keep儲存過程將DDLReplication相關的物件keep在共享池中，以保證這些物件不要reload，提升效能
SQL> @/u01/ora10g/gg10/ddl_pin
Enter value for 1: gguser

PL/SQL procedure successfully completed.

Enter value for 1: gguser

PL/SQL procedure successfully completed.

以上DDL支援包物件都已經安裝好，接下來就是配置manager,replicat和extract
6、manager的配置
GGSCI (dic5f88) 6> view param mgr
port 7908
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 60
DYNAMICPORTLIST 7940-8100,7930
SOURCEDB gzcrm20,USERID gguser,PASSWD gguser(這一行引數有問題，其它沒問題)
PURGEOLDEXTRACTS /ora10/ggtrails/local/*,USECHECKPOINTS,MINKEEPDAYS 2
此處sourcedb只有在異構資料庫的情況下才能使用，對於同構ORACLE資料庫，不需要使用

GGSCI (ora10g) 4> view params mgr

port 7908
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 60
DYNAMICPORTLIST 7940-8100,7930
USERID system@localhost:1521/ggtarge PASSWORD oracle
PURGEOLDEXTRACTS /u01/ora10g/ggtrail/local/*,USECHECKPOINTS,MINKEEPDAYS 2
PURGEOLDEXTRACTS /u01/ora10g/ggtrail/remote/*,USECHECKPOINTS,MINKEEPDAYS 2

GGSCI (ora10g) 2> start manager

Manager started.

GGSCI (ora10g) 3> status manager

Manager is running (IP port ora10g.7908).

以下配置，是針對source資料庫的
7、配置extract
1)在資料庫級開啟追加日誌功能
SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch log;
alter system switch log
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected

SQL> alter system switch logfile;

System altered.
2)增加extract組
GGSCI (ora10g) 5> ADD EXTRACT emp_ext1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (ora10g) 6> status extract;
ERROR: Invalid command.

GGSCI (ora10g) 7> status extract
ERROR: You must specify a group name.

GGSCI (ora10g) 8> status extract emp_ext1
EXTRACT EMP_EXT1: STOPPED
3)接下來，我們要新增一個 RMTTRAIL 或 EXTTRAIL。前者用於遠端計算機，如果您在同一臺本地計算機上建立複製，則建議使用後者。我們將使用 EXTTRAIL，通過以下命令來建立它
GGSCI (ora10g) 9> add exttrail /u01/ora10g/ggtrail/local/ex,megabytes 10,extract emp_ext1
EXTTRAIL added.

4)為 Extract 組 emp_ext1建立引數檔案
extract emp_ext1
USERID system@192.168.0.88:1521/orcl, PASSWORD tqtwfgwg
EXTTRAIL /u01/ora10g/ggtrail/local/ex
DDL INCLUDE MAPPED
Table test.*;

5)啟動extract
GGSCI (ora10g) 35> start extract emp_ext1

Sending START request to MANAGER ...
EXTRACT EMP_EXT1 starting

GGSCI (ora10g) 36> info extract emp_ext1 detail

EXTRACT    EMP_EXT1 Initialized   2010-12-07 18:39   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:37:08 ago)
Log Read Checkpoint Oracle Redo Logs
                     2010-12-07 18:39:44 Seqno 0, RBA 0

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/u01/ora10g/ggtrail/local/ex 0 0 10

Extract Source Begin End

Not Available * Initialized * 2010-12-07 18:39

Current directory /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_EXT1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_ext1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_EXT1.cpe
Process file         /u01/ora10g/gg10/dirpcs/EMP_EXT1.pce
Stdout file          /u01/ora10g/gg10/dirout/EMP_EXT1.out
Error log            /u01/ora10g/gg10/ggserr.log

GGSCI (ora10g) 37> status extract exp_ext1
ERROR: EXTRACT does not exist.

GGSCI (ora10g) 38> status extract emp_ext1
EXTRACT EMP_EXT1: STOPPED

6）檢視日誌，診斷錯誤
tail -100f EMP_EXT1.rpt
...
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.

2010-12-07 19:16:41 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.

2010-12-07 19:16:41 INFO OGG-00733 Marker table SYSTEM.GGS_MARKER not found.

Source Context :
SourceModule            : [er.redo.ora]
SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34090]/perforce/src/app/er/redo/oracle/redoora.c]
SourceFunction          : [REDO_validate_config]
SourceLine              : [3711]
ThreadBacktrace         : [8] elements
                          : [/u01/ora10g/gg10/extract(CMessageContext::AddThreadContext()+0x26) [0x840b3f6]]
                          : [/u01/ora10g/gg10/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x817) [0x8401b57]]
                          : [/u01/ora10g/gg10/extract(_MSG_ERR_DDL_SUPPORT_TABLE_MISSING(CSourceContext*, DBString<777> const&, CMessageFactory::MessageDisposition)+0x81) [0x83b1e11]]
                          : [/u01/ora10g/gg10/extract(REDO_validate_config(int, unsigned int*, int*)+0x81b) [0x82fbf9b]]
                          : [/u01/ora10g/gg10/extract [0x81775da]]
                          : [/u01/ora10g/gg10/extract(main+0x1648) [0x819e2e8]]
                          : [/lib/libc.so.6(__libc_start_main+0xdc) [0x676ce9c]]
                          : [/u01/ora10g/gg10/extract(__gxx_personality_v0+0x1c5) [0x8114ac1]]

2010-12-07 19:16:41 ERROR OGG-00529 DDL Replication is enabled but table SYSTEM.GGS_DDL_HIST is not found. Please check DDL installation in the database.

2010-12-07 19:16:41 ERROR OGG-01668 PROCESS ABENDING.

從結果來看，是這幾個物件不存在所到，是不是真不存在呢？我們到dba_objects裡去檢視一下：
-bash-3.2$ echo $ORACLE_SID
orcl
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 2 19:25:49 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col object_name for a40
SQL> col owner for a20
SQL> select owner,object_name,object_type from dba_objects where object_name in('GGS_MARKER','GGS_DDL_HIST');

OWNER OBJECT_NAME
-------------------- ----------------------------------------
OBJECT_TYPE
--------------------------------------
GGUSER GGS_MARKER
TABLE

GGUSER GGS_DDL_HIST
TABLE

結果已經很明確顯示，物件是存在的，只是使用者不是system下，所以，剛剛在檔案中配置的userid是有問題的。
另外，它診斷資料庫的歸檔目錄沒有指定，這個最好還是指定一下
因此，可做如下處理：
A、指定歸檔目錄:
SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
orcl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 222298112 bytes
Fixed Size                  1266680 bytes
Variable Size             134220808 bytes
Database Buffers           79691776 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/ora10g/oradata/archive' scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/ora10g/oradata/archive
Oldest online log sequence     42
Next log sequence to archive   44
Current log sequence           44
SQL>
B）、調整emp_ext1引數
GGSCI (ora10g) 39> edit params emp_ext1

extract emp_ext1
USERID gguser@192.168.0.88:1521/orcl, PASSWORD gguser
EXTTRAIL /u01/ora10g/ggtrail/local/ex
DDL INCLUDE MAPPED
Table test.*;
C)、重新再調起extract程式

GGSCI (ora10g) 42> info extract emp_ext1 detail

EXTRACT    EMP_EXT1 Last Started 2010-12-07 19:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
                     2010-12-07 19:41:54 Seqno 44, RBA 2578944

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/u01/ora10g/ggtrail/local/ex 0 922 10

Extract Source Begin End

/u01/ora10g/oradata/orcl/redo01.log 2010-12-07 18:39 2010-12-07 19:41
Not Available * Initialized * 2010-12-07 18:39

Current directory /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_EXT1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_ext1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_EXT1.cpe
Process file         /u01/ora10g/gg10/dirpcs/EMP_EXT1.pce
Stdout file          /u01/ora10g/gg10/dirout/EMP_EXT1.out
Error log            /u01/ora10g/gg10/ggserr.log

GGSCI (ora10g) 43> status extract emp_ext1
EXTRACT EMP_EXT1: RUNNING

OK，總算把extract程式搞定

以下配置，是針對target資料庫的
8、配置replicat組的引數資訊
1)在目標庫上建立checkpointtable
知識補充：此表中儲存的檢查點指示 Replicat 程式的當前讀寫位置。這用於在程式需要重啟時或者在伺服器出現任何故障或網路停頓時防止資料丟失。如果沒有這一機制，將導致資料丟失。另一個好處是，通過使用檢查點，多個 Extract 或 Replicat 程式可通過相同的一組線索進行讀取。
對於以批處理模式執行的 Extract 和 Replicat 程式，檢查點不是必需的，因為這樣的程式總是可以重啟。然而，在 Extract 和 Replicat 程式連續工作的情況下，檢查點是必需的。檢查點通常儲存在 dirchk 子目錄的檔案中，但是對於 Replicat，可以選擇在資料庫的檢查點表中儲存檢查點。
GGSCI (ora10g) 27> DBLOGIN USERID system@192.168.0.88/ggtarge PASSWORD oracle
Successfully logged into database.

GGSCI (ora10g) 28> add checkpoint table gguser.rep_checkpoint
ERROR: Invalid command.

GGSCI (ora10g) 29> add checkpointtable gguser.rep_checkpoint

Successfully created checkpoint table GGUSER.REP_CHECKPOINT.
2)新增 Replicat 組
GGSCI (ora10g) 44> ADD REPLICAT emp_rep1, EXTTRAIL /u01/ora10g/ggtrail/local/ex, CHECKPOINTTABLE gguser.rep_checkpoint,begin now
REPLICAT added.
3)配置Replicat 組的引數檔案
GGSCI (ora10g) 45> edit params emp_rep1
REPLICAT emp_rep
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP test.*, TARGET test.*;

4)啟動Replicat 組

GGSCI (ora10g) 47> start replicat emp_rep1

Sending START request to MANAGER ...
REPLICAT EMP_REP1 starting

GGSCI (ora10g) 48> status emp_rep1
REPLICAT EMP_REP1: STOPPED

GGSCI (ora10g) 49> info extract emp_rep1 detail
ERROR: EXTRACT does not exist.

GGSCI (ora10g) 50> info replicat emp_rep1 detail

REPLICAT   EMP_REP1 Initialized   2010-12-07 19:56   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:07:45 ago)
Log Read Checkpoint File /u01/ora10g/ggtrail/local/ex000000
                     2010-12-07 19:56:10.000000

Extract Source Begin End

/u01/ora10g/ggtrail/local/ex000000 * Initialized * 2010-12-07 19:56

Current directory /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_rep1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_REP1.cpr
Checkpoint table     gguser.rep_checkpoint
Process file         /u01/ora10g/gg10/dirpcs/EMP_REP1.pcr
Stdout file          /u01/ora10g/gg10/dirout/EMP_REP1.out
Error log            /u01/ora10g/gg10/ggserr.log

再次經歷失敗，悲劇

5）檢視日誌，定位問題
-bash-3.2$ tail -100f /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
...
2010-12-07 20:02:58 ERROR OGG-00446 Could not open checkpoint file /u01/ora10g/gg10/dirchk/EMP_REP.cpr, mode 1 (error 2, No such file or directory).

2010-12-07 20:02:58 ERROR OGG-01668 PROCESS ABENDING.

錯誤報得很奇怪，說是一個checkpoint file找不到，我不是建的表嗎？怎麼成了檔案了。
最關鍵的是，我的程式不是emp_rep1嗎，怎麼是emp_rep.cpr了
進目錄檢視:
-bash-3.2$ cd /u01/ora10g/gg10/dirchk/
-bash-3.2$ ls -l
total 8
-rw-rw-r-- 1 oracle oinstall 4096 Dec 7 20:08 EMP_EXT1.cpe
-rw-rw-r-- 1 oracle oinstall 2048 Dec 7 19:56 EMP_REP1.cpr

果然發現不存在EMP_REP.cpr檔案，但存在EMP_REP1.cpr檔案

檢視之前的配置檔案，果然發現第一行有問題:
REPLICAT emp_rep
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP test.*, TARGET test.*;
該死的粗心
修改如下：
REPLICAT emp_rep1
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD ---reperror錯誤，指定在出現該錯誤時，複製進行的處理方式（預設為abended，可有ignore,exception,discard等其它操作)
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS ----這個引數指定在進行DML操作時，兩邊的物件結果是完全一致的，一般在DML操作中省略兩邊表結果的檢查
HANDLECOLLISIONS
MAP test.*, TARGET test.*;

重啟服務：
GGSCI (ora10g) 2> start replicat emp_rep1

Sending START request to MANAGER ...
REPLICAT EMP_REP1 starting

GGSCI (ora10g) 3> status emp_rep1
REPLICAT EMP_REP1: RUNNING

GGSCI (ora10g) 4> info replication emp_rep1 detail
ERROR: Invalid command.

GGSCI (ora10g) 5> info emp_rep1 detail

REPLICAT   EMP_REP1 Last Started 2010-12-07 20:18   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File /u01/ora10g/ggtrail/local/ex000000
                     2010-12-07 19:56:10.000000 RBA 922

Extract Source Begin End

/u01/ora10g/ggtrail/local/ex000000 2010-12-07 19:56 2010-12-07 19:56
/u01/ora10g/ggtrail/local/ex000000 * Initialized * 2010-12-07 19:56

Current directory /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_rep1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_REP1.cpr
Checkpoint table     GGUSER.REP_CHECKPOINT
Process file         /u01/ora10g/gg10/dirpcs/EMP_REP1.pcr
Stdout file          /u01/ora10g/gg10/dirout/EMP_REP1.out
Error log            /u01/ora10g/gg10/ggserr.log

成功啟動，接下來的內容就是同步測試了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12350275/viewspace-688781/，如需轉載，請註明出處，否則將追究法律責任。

配置支援DML和DDL操作同步的GoldenGate

相關文章