oracle golden gate 安裝配置

oracle goldengate 在oracle 11gR2上的安裝過程：

一、golden gate 軟體安裝，很簡單。建立目錄後解壓即可。

注意：建議用oracle 使用者安裝，設定oralce使用者的環境變數：

LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib:$ORACLE_GOLDENGATE:$ORACLE_HOME/lib
LD_LIBRARY_PATH 中必須有 $ORACLE_HOME/lib 才能執行./ggsci ，否則回報缺少動態庫檔案的錯誤：
但是這種情況下oracle 資料庫執行沒有問題。
[oracle@haozg goldengate]$ ./ggsci
./ggsci: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file:
No such file or directory

原因是：goldengate 在執行時需要oralce 的動態庫檔案。所以需要把oracle的動態庫檔案放到共享庫中，
賦值給環境變數LD_LIBRARY_PATH。

Make certain that the database libraries are added to the shared-library environment variables of the system。

Installing Oracle GoldenGate on Linux and UNIX
Follow these steps to install Oracle GoldenGate for Oracle on a Linux or UNIX system 。
Installing the Oracle GoldenGate files
1. Extract the Oracle GoldenGate mediapack.zip file to the system and directory where you
   want Oracle GoldenGate to be installed.
2. Run the command shell.
   ./ggsci
3、In GGSCI, issue the following command to create the Oracle GoldenGate working directories.
   CREATE SUBDIRS
4、Issue the following command to exit GGSCI.
   EXIT

二、goldendate configure

1、為goldengate軟體建立資料庫使用者，為了不影響生產環境，此使用者用來安裝一些複製軟體自身用到的procedure、table等，
   也就是搭建ddl複製環境用到的一些表，過程等。---ddl objects 都在這個使用者下。

   Oracle GoldenGate schema

SQL> create user ogg identified by ogg; 

User created.

2、對其授權

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> GRANT EXECUTE ON utl_file TO ogg;

Grant succeeded.

SQL> !pwd   
/oracle/goldengate

上面的兩步操作在源端和目標端都需要操作。
下面的操作在源端執行。

3、源端資料庫需要開啟的操作，在sys 使用者操作

1) 檢查附加日誌情況
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
2) 增加資料庫附加日誌及回退
alter database add supplemental log data;
alter database add supplemental log data (primary key, unique,foreign key) columns;
---rollback
alter database drop supplemental log data (primary key, unique,foreign key) columns;
alter database drop supplemental log data;
4.3.5. 開啟資料庫強制日誌模式
alter database force logging;

 

4、執行marker_setup.sql 指令碼。This script. installs support for the Oracle GoldenGate DDL marker system
SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

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

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

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

Script. complete.
SQL>

5、執行@ddl_setup.sql
SQL> @ddl_setup.sql

Oracle 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 Oracle 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 Oracle GoldenGate schema name:ogg

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 OGG as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

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 IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO  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
------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/ora11/ora11/trace/ggs_ddl_trace.log

Analyzing installation status...

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

Script. complete.
SQL>

6、執行role_setup.sql。
The script. drops and creates the role that is needed for DDL synchronization, and it grants DML permissions on
the Oracle GoldenGate DDL objects.
SQL> @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:ogg
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>

7、Grant the role that was created (default name is GGS_GGSUSER_ROLE  to all Oracle GoldenGate Extract users.

SQL> Grant GGS_GGSUSER_ROLE to ogg;

Grant succeeded.

SQL>

8、執行@ddl_enable.sql，Run the  ddl_enable.sql  script. to enable the DDL trigger.
ddl triger 判斷在有ddl發生時是否把ddl 傳送給extract 程式。
SQL> @ddl_enable.sql

Trigger altered.

9、執行優化指令碼。

To improve the performance of the DDL trigger, make the ddl_pin script. part of the database startup。
SQL> @ddl_pin ogg

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

-----另外： 安裝sequence support

---1、  在源端和目標端都執行sequence.sql
SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
ogg
Setting schema name to OGG

UPDATE_SEQUENCE STATUS:

Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors

GETSEQFLUSH

Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors

SEQTRACE

Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors

REPLICATE_SEQUENCE STATUS:

Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors

STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
SQL>

---2、在源端執行下面的命令

GRANT EXECUTE on OGG.updateSequence TO OGG;

---3、在目標端執行
GRANT EXECUTE on ogg.replicateSequence TO ogg;

---4、在源端執行下面的命令

alter table sys.seq$ add supplemental log data (primary key) columns;

--------goldengate 源端配置

1、配置globals 引數

GGSCI (haozg) 4> edit params ./globals
GGSCHEMA ogg

2、在源端配置管理程式

配置mgr
GGSCI (haozg) 1> edit params mgr

PORT 7839
DYNAMICPORTLIST  7840-7849
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
--DDL
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7

 

3、配置 extract 程式

EXTRACT ext_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
DBOPTIONS   ALLOWUNUSEDCOLUMN
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default
--GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
NUMFILES 5000
DISCARDFILE ./dirrpt/ext_test.dsc, APPEND, MEGABYTES 100
DISCARDROLLOVER AT 2:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m
EXTTRAIL ./dirdat/st, MEGABYTES 200
DYNAMICRESOLUTION
TRANLOGOPTIONS CONVERTUCS2CLOBS
TRANLOGOPTIONS RAWDEVICEOFFSET 0
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
INCLUDE MAPPED OBJTYPE 'SEQUENCE' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS  NOCROSSRENAME  REPORT
TABLE     HAOZG.*;
SEQUENCE  HAOZG.*;

4、配置投遞程式

EXTRACT dp_test
RMTHOST 192.168.1.101, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/st
DYNAMICRESOLUTION
TABLE      HAOZG.*;
SEQUENCE   HAOZG.*;

5、新增抽取/投遞程式與佇列檔案

GGSCI>add ext ext_test,tranlog ,begin now

GGSCI>add exttrail ./dirdat/st,ext ext_test,megabytes 200  -----備註：新增抽取程式，每個隊檔案大小為200m

GGSCI>add ext dp_test,exttrailsource ./dirdat/st           -----備註：新增投遞程式，從某一個佇列開始投

GGSCI>add rmttrail ./dirdat/st,ext dp_test,megabytes 200   -----備註：投遞程式，每個隊檔案大小為200m

-----------------------如果新增錯誤，刪除程式 和佇列檔案的命令，重新新增---------
delete ext_test
delete exttrail ./dirdat/st,ext ext_test,megabytes 200

delete dp_test
delete rmttrail ./dirdat/st,ext dp_test,megabytes 200

 

---------------goldengate災備端的配置-----------------------------------------------

1、配置GLOBALS
GGSCI (rac2) 1> edit params ./globals
GGSCHEMA ogg
CHECKPOINTTABLE ogg.oggchkpt

2、新增檢查表

GGSCI (rac2) 2> DBLOGIN USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB , ENCRYPTKEY default
Successfully logged into database.

GGSCI (rac2) 3> ADD CHECKPOINTTABLE ogg.oggchkpt

Successfully created checkpoint table ogg.oggchkpt.

GGSCI (rac2) 4>

3、配置mgr

PORT 7839
DYNAMICPORTLIST 7840-7849
--AUTOSTART REPLICAT *
--AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 1
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

 

4、配置repylqa

REPLICAT rep_test
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB ,ENCRYPTKEY default
REPORT AT 00:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
NUMFILES 5000
GROUPTRANSOPS 10000
--HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repylqa.dsc, APPEND, MEGABYTES 200
DISCARDROLLOVER AT 02:00
GETTRUNCATES
ALLOWNOOPUPDATES
DDL include mapped
DDLOPTIONS report
MAP HAOZG.*,TARGET HAOZG.*;

5、新增rep_test

GGSCI (rac2) 1> ADD REPLICAT rep_test, EXTTRAIL ./dirdat/st , EXTSEQNO 0, EXTRBA 0 , CHECKPOINTTABLE ogg.oggchkpt
REPLICAT added.

-----------如果新增錯誤，那麼刪除rep_test 程式
但是：必須先連線到資料庫
DBLOGIN USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB , ENCRYPTKEY default

delete REPLICAT rep_test, EXTTRAIL ./dirdat/st , EXTSEQNO 0, EXTRBA 0 , CHECKPOINTTABLE ogg.oggchkpt

 

 

到此完成安裝與配置