oracle goldengate 雙向複製配置
oracle goldengate bi-directional active-active configure
一、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
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，ogg 的官方文件是這樣描述：
賦值給環境變數LD_LIBRARY_PATH，ogg 的官方文件是這樣描述：
Make certain that the database libraries are added to the shared-library environment variables of the system。
--ogg官方文件上的安裝步驟
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
二、建立goldengate 使用者（在源端和目標端都執行）
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
二、建立goldengate 使用者（在源端和目標端都執行）
說明：為goldengate軟體建立資料庫使用者，為了不影響生產環境，此使用者用來安裝存放一些複製軟體自身用到的procedure、table等，
也就是搭建ddl複製環境用到的一些表，過程等。該使用者需要有一定的許可權。---ddl objects 都在這個使用者下。
Oracle GoldenGate schema --ogg 官網上的稱呼
注意：該使用者在官網上也稱之外複製使用者： replicate user
也就是搭建ddl複製環境用到的一些表，過程等。該使用者需要有一定的許可權。---ddl objects 都在這個使用者下。
Oracle GoldenGate schema --ogg 官網上的稱呼
注意：該使用者在官網上也稱之外複製使用者： replicate user
1、建立 ogg schema
SQL> create user ogg identified by ogg;
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
/oracle/goldengate
===========配置思路是：先配置主端到災備端的單向複製，然後再配置從災備端到主端的單向複製，從而實現雙向複製============
三 先執行從主端到災備端的單向複製配置
------------------------------主端的配置---------------------------------------------------
---------------------------資料庫層面的配置--------------------------------------------------
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;
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
正確狀態如下：
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES NO NO NO NO
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES NO NO NO NO
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;
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;
3、開啟資料庫強制日誌模式
alter database force logging;
alter database force logging;
------------------------------安裝ddl 複製支援-----------------------------
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.
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
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
-------------------------------
OK
Script. complete.
SQL>
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.
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
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
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
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/ora11/ora11/trace/ggs_ddl_trace.log
------------------------------------------------------------------------------------------------------------------------
/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>
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.
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.)
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.
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
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>
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 複製支援-------------------------------------------
10、在主端和備端都執行sequence.sql
SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
ogg
Setting schema name to OGG
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
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
SQL>
11、在主端執行下面的命令
GRANT EXECUTE on OGG.updateSequence TO OGG;
12、在備端端執行
GRANT EXECUTE on ogg.replicateSequence TO ogg;
13、在主端執行下面的命令
alter table sys.seq$ add supplemental log data (primary key) columns;
----------------------goldengate 引數配置----------------------------------------------
14、在主端配置globals 引數
GGSCI (haozg) 4> edit params ./globals
GGSCHEMA ogg
GGSCHEMA ogg
15、在主端配置管理程式 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
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
-------------------引數說明----
DYNAMICPORTLIST： Specifies the ports that Collector can dynamically allocate.
PORT： Establishes the TCP/IP port number on which Manager listens for requests.
PURGEDDLHISTORY Purges rows from the Oracle DDL history table when they are no longer needed.
PORT： Establishes the TCP/IP port number on which Manager listens for requests.
PURGEDDLHISTORY Purges rows from the Oracle DDL history table when they are no longer needed.
16、在主端配置 extract 程式
GGSCI (haozg) 1> edit params ext_test
EXTRACT ext_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default
TRANLOGOPTIONS EXCLUDEUSER ogg
--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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS NOCROSSRENAME REPORT
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
EXTRACT ext_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default
TRANLOGOPTIONS EXCLUDEUSER ogg
--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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS NOCROSSRENAME REPORT
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
17、在主端配置投遞程式
GGSCI (haozg) 2> edit params dp_test
EXTRACT dp_test
RMTHOST 192.168.1.101, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/st
DYNAMICRESOLUTION
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
EXTRACT dp_test
RMTHOST 192.168.1.101, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/st
DYNAMICRESOLUTION
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
18、在主端新增抽取/投遞程式與佇列檔案
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
------------------------------------備端的配置--------------------------------------
19、 在備端配置GLOBALS
GGSCI (rac2) 1> edit params ./globals
GGSCHEMA ogg
CHECKPOINTTABLE ogg.oggchkpt
GGSCHEMA ogg
CHECKPOINTTABLE ogg.oggchkpt
20、在備端新增檢查表
GGSCI (rac2) 2> DBLOGIN USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB , ENCRYPTKEY default
Successfully logged into database.
Successfully logged into database.
GGSCI (rac2) 3> ADD CHECKPOINTTABLE ogg.oggchkpt
Successfully created checkpoint table ogg.oggchkpt.
GGSCI (rac2) 4>
21、備端配置mgr 管理程式
GGSCI (rac2) 1> edit params 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
PORT 7839
DYNAMICPORTLIST 7840-7849
--AUTOSTART REPLICAT *
--AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 1
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
22、備端配置複製程式
GGSCI (rac2) 2> edit params rep_test
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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS report
MAPEXCLUDE HAOZG.TEST15
MAP HAOZG.*,TARGET HAOZG.*;
23、新增複製程式rep_test
GGSCI (rac2) 1> ADD REPLICAT rep_test, EXTTRAIL ./dirdat/st , EXTSEQNO 0, EXTRBA 0 , CHECKPOINTTABLE ogg.oggchkpt
REPLICAT added.
REPLICAT added.
-------到此為止 完成了從主端到災備端的單向複製配置-------------------
-----下面是從備端到主端的單向複製配置，現在要保持思路清晰，主端成為備端，備端成為主端。
--------在主端做的配置，即原來的備端
---------------------------資料庫層面的配置--------------------------------------------------
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;
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
正確狀態如下：
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES NO NO NO NO
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES NO NO NO NO
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;
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;
3、開啟資料庫強制日誌模式
alter database force logging;
alter database force logging;
------------------------------安裝ddl 複製支援-----------------------------
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.
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
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
-------------------------------
OK
Script. complete.
SQL>
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.
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
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
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
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/ora11/ora11/trace/ggs_ddl_trace.log
------------------------------------------------------------------------------------------------------------------------
/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>
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.
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.)
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.
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
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>
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 複製支援-------------------------------------------
10、在主端執行下面的命令
GRANT EXECUTE on OGG.updateSequence TO OGG;
11、在備端端執行
GRANT EXECUTE on ogg.replicateSequence TO ogg;
12、在主端執行下面的命令
alter table sys.seq$ add supplemental log data (primary key) columns;
----------------------goldengate 引數配置-------------------------------------
---主端的goldengate配置
13、在主端配置 extract 程式
GGSCI (rac2) 1> edit params ext_test
EXTRACT ext_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default
TRANLOGOPTIONS EXCLUDEUSER ogg
--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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS NOCROSSRENAME REPORT
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
EXTRACT ext_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, ENCRYPTKEY default
TRANLOGOPTIONS EXCLUDEUSER ogg
--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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS NOCROSSRENAME REPORT
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
14、在主端配置投遞程式
GGSCI (rac2) 2> edit params dp_test
EXTRACT dp_test
RMTHOST 192.168.1.101, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/st
DYNAMICRESOLUTION
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
EXTRACT dp_test
RMTHOST 192.168.1.101, MGRPORT 7839, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/st
DYNAMICRESOLUTION
TABLE HAOZG.*;
SEQUENCE HAOZG.*;
15、在主端新增抽取/投遞程式與佇列檔案
GGSCI>add ext ext_test,tranlog ,begin now
GGSCI>add exttrail ./dirdat/tt,ext ext_test,megabytes 200 -----備註：新增抽取程式，每個隊檔案大小為200m
GGSCI>add ext dp_test,exttrailsource ./dirdat/tt -----備註：新增投遞程式，從某一個佇列開始投
GGSCI>add rmttrail ./dirdat/tt,ext dp_test,megabytes 200 -----備註：投遞程式，每個隊檔案大小為200m
------------------------------災備端的goldengate 配置
16、在備端新增檢查表
GGSCI (haozg) 2> DBLOGIN USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB , ENCRYPTKEY default
Successfully logged into database.
Successfully logged into database.
GGSCI (haozg) 3> ADD CHECKPOINTTABLE ogg.oggchkpt
Successfully created checkpoint table ogg.oggchkpt.
GGSCI (haozg) 4>
17、備端配置複製程式
GGSCI (haozg) 3> edit params rep_test
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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS report
MAPEXCLUDE HAZG.TEST15
MAP HAOZG.*,TARGET HAOZG.*;
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 &
EXCLUDE OBJNAME "HAOZG.TEST15"
DDLOPTIONS report
MAPEXCLUDE HAZG.TEST15
MAP HAOZG.*,TARGET HAOZG.*;
18、備端新增複製程式rep_test
GGSCI (haozg) 1> ADD REPLICAT rep_test, EXTTRAIL ./dirdat/tt , EXTSEQNO 0, EXTRBA 0 , CHECKPOINTTABLE ogg.oggchkpt
到此為止 完成了從備端到主端的單向複製配置，也就完成了整個雙活配置。
24、啟動兩邊的程式檢視程式的狀態。
start ext_test
start dp_test
start rep_test
如果啟動失敗，用 view report ext_test/dp_test/rep_test 檢視失敗的原因。
GGSCI (haozg) 10> info all
start dp_test
start rep_test
如果啟動失敗，用 view report ext_test/dp_test/rep_test 檢視失敗的原因。
GGSCI (haozg) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP_TEST 00:00:00 00:00:02
EXTRACT RUNNING EXT_TEST 00:00:00 00:00:13
REPLICAT RUNNING REP_TEST 00:00:00 00:00:01
EXTRACT RUNNING DP_TEST 00:00:00 00:00:02
EXTRACT RUNNING EXT_TEST 00:00:00 00:00:13
REPLICAT RUNNING REP_TEST 00:00:00 00:00:01
程式全都起來。
25、引數說明，見後續部落格。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23062014/viewspace-741698/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- GoldenGate配置（二）之雙向複製配置Go
- goldengate 單向複製配置Go
- GoldenGate單向複製配置(支援DDL複製)Go
- goldengate單向複製的配置Go
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- Oracle 11g GoldenGate單向複製配置（DML）OracleGo
- GoldenGate配置（一）之單向複製配置Go
- goldengate不使用資料泵完成Oracle-Oracle的雙向複製GoOracle
- 配置Oracle GoldenGate for DB2(雙向)OracleGoDB2
- oracle goldengate 雙活複製避免迴圈複製引數OracleGo
- GoldenGate雙向同步配置Go
- 通過goldengate從ORACLE向mysql的單向複製GoOracleMySql
- goldengate配置DDL複製Go
- ogg12 oracle to oracle 雙向DDL複製Oracle
- PostgreSQL雙向複製教程SQL
- Oracle goldengate 11g （二）【DML and DDL單向複製】OracleGo
- GoldenGate學習筆記(5)_配置例程之單向複製Go筆記
- GoldenGate配置（三）之DDL複製配置Go
- OGG 的配置:不使用資料泵完成Oracle-Oracle的雙向複製。Oracle
- 用python生成oracle goldengate複製配置檔案PythonOracleGo
- 搭建一個Oracle到Oracle的GoldenGate單向複製測試環境OracleGo
- 使用goldengate的資料泵進行Oracle-Oracle的單向複製GoOracle
- OGG雙向DML複製操作
- MySQL主從雙向同步複製MySql
- 實戰goldengate:安裝配置+資料初始化+單向DML複製Go
- mysql主主複製（雙主複製）配置步驟MySql
- 實戰goldengate之ora-To-ora單向複製Go
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- Oracle GoldenGate系統之----雙向同步資料表OracleGo
- GoldenGate實現oracle和sqlserver雙向資料同步GoOracleSQLServer
- MySQL主主複製（雙主複製）配置過程介紹MySql
- 為Oracle配置DDL複製Oracle
- Step by Step TimesTen --- DataStore的雙向複製( 一)AST
- Step by Step TimesTen --- DataStore的雙向複製( 二)AST
- GoldenGate的複製原理Go
- 資料複製_GoldenGateGo
- 安裝配置Oracle GoldenGate for DB2(單向)OracleGoDB2