第一次安裝，不管是源端還是目標端程式都正常，但是就是不同步，後來把ogg使用者刪掉，把ogg安裝目錄都刪掉，並且把目標端的附加日誌給刪除掉（第一次安裝把目標端也新增了附加日誌，不知道是不是因為這裡導致失敗的）重新按著下面的過程安裝了一次成功了

在開啟ogg的時候要保證源端和目標端的資料一致，也就是初始化資料庫，oracle-oracle可以用資料泵完成，oracle-mysql則要透過ogg特有的配置完成，具體步驟參考筆記：網上down的ogg文件

配置DDL的時候，也是按著步驟完成後，一切都正常，錯誤日誌也沒有報錯，就是不同步，後來網上有人說目標端不需要配置ddl include all ,於是我在目標端去掉replicate的ddl include all，然後我給了源端ogg 使用者dba許可權，並且把程式都重啟了一下，就正常了，可是我不知道是哪個原因的過，控制變數法得到的結論是這些修改不是他後來成功的原因，到現在我都不知道，錯誤原因在哪裡。

一：首先兩端都要安裝好oracle資料庫軟體，並且建立好庫，這個過程省略。。。

二：配置oracle環境變數，源端和目標端一樣，

[oracle@master2 ~]$ cat .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 ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=wenhe2
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export GGATE=/u01/OGG_linux/ggs
alias sqlplus='rlwrap sqlplus'

注意：

1,可以透過這個語句來檢視NLS_LANG

SQL> select userenv('language') nls_lang from dual;

NLS_LANG

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2， GGATE=/u01/OGG_linux/ggs 這個路徑是你將要安裝ogg軟體的路徑。

3，設定LD_LIBRARY_PATH，為了安裝OGG所用的動態連結庫。如果沒有配置這個路徑的話，在安裝OGG的過程
中會報找不到動態連結庫的錯誤，

二安裝ogg軟體，具體操作如下，源端和目標端都要安裝。

源端：

[oracle@master2 ggs]$ cd /u01/OGG_linux/ggs

[oracle@master2 ggs]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@master2 ggs]$ ll
總計 310956
-rw-rw-r-- 1 oracle oinstall 228556800 2012-04-23 fbo_ggs_Linux_x64_ora11g_64bit.tar
-rw-r--r-- 1 oracle oinstall  89186858 01-15 20:31 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
-rwxrwxrwx 1 oracle oinstall    220546 2012-05-02 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx 1 oracle oinstall     93696 2012-05-02 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx 1 oracle oinstall     24390 2012-05-02 Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@master2 ggs]$ tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

測試安裝成功，只需進入命令列，就表明安裝是成功的。

[oracle@gg1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02

GGSCI (gg1) 1>

GGSCI (gg1) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

注意：

表示安裝成功。

目標端和源端一樣，省略。

三：建立ogg專用的schema，表空間，並且授權,

SQL>create tablespace ogg_data datafile '/u01/app/oracle/oradata/ogg_data.dbf' size 1000M autoextend on;

SQL> create user ogg identified by ogg default tablespace ogg_data temporary tablespace TEMP ;

grant connect,resource to ogg;

grant create session,alter session to ogg;

grant select any dictionary,select any table to ogg;

grant alter any table to ogg;

grant flashback any table to ogg;

grant execute on dbms_flashback to ogg;

四：修改源端資料庫配置

源端：

新增附加日誌來唯一標識一行記錄，要在資料庫級別開啟最小開關。為了減少整個資料庫新增附加日誌，以及減少歸檔量，

goldengate建議複製哪些物件，就新增哪些表的附加日誌（我們到時候是拿scott使用者下的表來實驗）。

檢查：

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

開啟：

SQL> alter database add supplemental log data;

Database altered.

再次檢視：

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

切換日誌組，使附加日誌開關生效。

SQL> alter system switch logfile;

System altered.

解釋：
備註：在oracle中可以透過rowid來定位某條記錄，但是目標端的資料庫和源端資料庫的資料庫可能完全不一樣，所以無法透過rowid來確定源端資料庫的邏輯變化，這時附加日誌supplemental log便登上了表演的舞臺。資料庫在開啟附加日誌功能後，對於源端的修改操作，oracle會同時追加能夠唯一標示記錄的列到redo log。這樣目標端資料庫就可以知道源端發生了哪些具體的變化。

四：配置ogg

源端：

[oracle@master2 ggs]$ cd /u01/OGG_linux/ggs

[oracle@master2 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

1，GGSCI (master2) 1> create subdirs

Creating subdirectories under current directory /u01/OGG_linux/ggs

Parameter files /u01/OGG_linux/ggs/dirprm: already exists

Report files /u01/OGG_linux/ggs/dirrpt: created

Checkpoint files /u01/OGG_linux/ggs/dirchk: created

Process status files /u01/OGG_linux/ggs/dirpcs: created

SQL script files /u01/OGG_linux/ggs/dirsql: created

Database definitions files /u01/OGG_linux/ggs/dirdef: created

Extract data files /u01/OGG_linux/ggs/dirdat: created

Temporary files /u01/OGG_linux/ggs/dirtmp: created

Stdout files /u01/OGG_linux/ggs/dirout: created,

2，登入資料庫

GGSCI (master2) 3> dblogin userid ogg,password ogg

Successfully logged into database.

3，新增需要同步的使用者到ogg

開啟scott使用者下所有表的附加日誌

GGSCI (WebServer) 3> add trandata scott.*

2013-03-08 11:02:33 WARNING OGG-00869 No unique key is defined for table 'BONUS'.
All viable columns will be used to represent the key, but may not guarantee
uniqueness. KEYCOLS may be used to
define the key.

Logging of supplemental redo data enabled
for table SCOTT.BONUS.

Logging of supplemental redo data enabled
for table SCOTT.DEPT.
Logging of supplemental redo data enabled
for table SCOTT.EMP.

2013-03-08 11:02:34 WARNING OGG-00869 No unique key is defined for table
'SALGRADE'. All viable columns will be used to represent the key, but may not
guarantee uniqueness. KEYCOLS may be
used to define the key.

Logging of supplemental redo data enabled
for table SCOTT.SALGRADE.

4，GGSCI (WebServer) 4> info trandata scott.*

Logging of supplemental redo log data is
enabled for table SCOTT.BONUS.

Columns supplementally logged for table
SCOTT.BONUS: ENAME, JOB, SAL, COMM.

Logging of supplemental redo log data is
enabled for table SCOTT.DEPT.
Columns supplementally logged for table
SCOTT.DEPT: DEPTNO.

Logging of supplemental redo log data is
enabled for table SCOTT.EMP.

Columns supplementally logged for table
SCOTT.EMP: EMPNO.
Logging of supplemental redo log data is
enabled for table SCOTT.SALGRADE.

Columns supplementally logged for table
SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

3，GGSCI (master2) 3>edit params mgr

PORT 7839

4，GGSCI (master2) 3> start mgr

5，GGSCI (master2) 3>info mgr

Manager is running (IP port
WebServer.7839).

6，新增抓取程式起名字為ext_liu

GGSCI (master2) 3>add extract ext_liu, tranlog, begin now

EXTRACT added.

引數分析：
tranlog：表示資料抓取的來源是資料庫的redo資料。
begin now：表示我們在啟動這個抓取程式的就去抓取資料。
threads 1：表示我們資料庫有多少個redo threads，單例項基本上是1或者不設，rac就自己設定了，
配置過RAC的基本都瞭解。

7，編輯剛才新增的抓取程式

GGSCI (master2) 3>edit params ext_liu

EXTRACT ext_liu

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

userid ogg,password ogg

exttrail ./dirdat/h1,megabytes 100

TABLE scott.*;

分析：

setenv (ORACLE_SID=ogg)：指定oracle的sid，單機環境下可以不設或者bashprofile檔案中設定了預設的

也可以不設，但是叢集環境就要指定了，這裡我們強烈建議指定。

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)：源端資料庫的字符集，一定要跟資料庫中匹配。

userid ogg,password ogg：告訴抓取程式是透過這個使用者登陸資料庫的。

exttrail ./dirdat/h1,megabytes 100：與我們上面配置抓取程式的設定要一致，路徑和大小嘛。

TABLE SCOTT.*：最重要的，告訴抓取程式抓取的是哪些使用者哪些表的資料。

8,新增源端的佇列檔案。
GGSCI (gg1) 15> add EXTTRAIL ./dirdat/h1, extract ext_liu, megabytes 100
EXTTRAIL added.

分析：
./dirdat：表示trail檔案的目錄
h1：trail檔案的字首
extract ext_demo：值指定給那個程式用的（ext_demo）。
megabytes 100：檔案大小是100m

9，配置datapump程式，將抓取資料傳到目標主機。負責TCPIP通訊。
GGSCI (gg1) 31> add extract dpe_liu, exttrailsource ./dirdat/h1 ##h1是源端設定的trails 檔案的格式
EXTRACT added.

10

,配置datapump程式引數：

GGSCI (gg1) 34> edit param dpe_liu

extract dpe_liu

dynamicresolution

passthru

rmthost 192.168.9.25, mgrport 7839, compress

rmttrail ./dirdat/h1

numfiles 5000

TABLE scott.*;

輸出：目標主機怎麼寫，也是定義datapumo程式的輸出。
GGSCI (gg1) 33> add rmttrail ./dirdat/h1, EXTRACT dpe_liu,MEGABYTES 100
RMTTRAIL added.

分析：
exttrailsource：抓取程式的來源，因為這個程式不否則到資料庫中取抓取，所以抓取的來源
是透過抓取程式已經生成好的trail檔案中的內容。

10，開啟新增的程式

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPED DPE_LIU 00:00:00 00:00:02

EXTRACT STOPED EXT_LIU 00:00:00 00:00:04

GGSCI (master2) 4> start DPE_LIU

GGSCI (master2) 4> start EXT_LIU

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT_LIU 00:00:00 00:00:02

EXTRACT RUNNING DPE_LIU 00:00:00 00:00:04

目標端：

建立ogg的使用者和表空間，並授權，注意授權和源端有區別，

SQL>create tablespace ogg_data datafile '/u01/app/oracle/oradata/ogg_data .dbf' size 1000M autoextend on;

SQL> create user ogg identified by ogg default tablespace ogg_data temporary tablespace TEMP ;

grant connect to ogg ;

grant alter any table to ogg ;

grant alter session to ogg ;

grant create session to ogg ;

grant flashback any table to ogg ;

grant select any dictionary to ogg ;

grant select any table to ogg ;

grant resource to ogg ;

grant insert any table to ogg ;

grant update any table to ogg ;

grant delete any table to ogg ;

grant create any index to ogg ;

grant select any transaction to ogg ;

GGSCI (master2) 4> create subdirs

GGSCI (master2) 4> edit params mgr

port 7839

啟動配置的管理程式

GGSCI (master2) 4> start mgr

Manager started.

檢視是否正在執行的管理程式，如果不是running請檢視自己的原因。

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

檢視剛剛設定的埠號有沒有問題

GGSCI (master1) 4> info mgr

Manager is running (IP port master2.7839).

為replicat程式建立checkpoint表：

GGSCI (master1) 4> dblogin userid ogg ,password ogg

Successfully logged into database.

目標端新增檢查點表，操作完成後，在oracle資料庫 ogg使用者下生成了一個名字叫rep_liu_ckpt的表。

GGSCI (master2) 1> edit params ./GLOBALS

GGSCI (master2) 2> view params ./GLOBALS

checkpointtable ogg.rep_liu_ckpt

GGSCI (master1) 4> add checkpointtable ogg.rep_liu_ckpt

Successfully created checkpoint table ogg.rep_demo_ckpt.

配置目標端replicate程式：

GGSCI (master2) 4> add replicat rep_liu ,exttrail ./dirdat/h1,checkpointtable ogg.rep_liu_ckpt

REPLICAT added.

刪除：GGSCI (master1) 23> delete replicat REP_LIU

Deleted REPLICAT REP_DEMO.

分析：

exttrail：表示這個程式獲取資料的來源是什麼地方，是我們源端rmttrail所設定過的。

配置目標端replicate引數：

GGSCI (gg2) 5> edit param rep_liu

REPLICAT rep_liu

SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8 )

USERID ogg,PASSWORD ogg

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

numfiles 5000

assumetargetdefs

DISCARDFILE ./dirrpt/rep_liu.dsc, APPEND, MEGABYTES 1000 ##注意此處和replicate 的名字相對應

ALLOWNOOPUPDATES

MAP scott.*, TARGET scott.*;

啟動並檢視replicate程式是否執行：

GGSCI (master2) 4> start rep_liu

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP_LIU 00:00:00 01:22:45

至此配置算是完成了。。。。

ogg的日常操作

1、啟動manager：start manager

2、啟動extract：start extract extn

3、啟動replicat：start replicat repn

4、停止manager：stop manager

5、停止 extract：stop extract extn

6、停止replicat：stop replicat repn

7、刪除extract：delete extract extn

8、刪除replicat：delete extract repn

9、檢視ogg狀態：info all

10、修改manager引數：edit params mgr

11、修改extract引數：edit params extn

12、修改replicat引數：edit params repn

13、修改全域性引數：edit params ./GLOBAL

以下繼續配置支援DDL操作的流程：

在source端操作:

1：賦予ogg使用者相應的許可權，修改全域性配置檔案新增ggschema引數

SQL> conn /as sysdba

Connected.


	     SQL> grant execute on utl_file to ogg;  



	  Grant succeeded.  



	      



	    [oracle@master2 ~]$ cd $GGATE   



	     [oracle@master2 ogg]$ ggsci  



	     GGSCI (master2 ) 1> edit param ./GLOBALS  



	     



	    GGSCI (master2) 2> view param ./GLOBALS  



	     ggschema ogg

2：執行相關的sql指令碼

[oracle@master2 ~]$ cd $GGATE

[oracle@master2 ~]$ sqlplus / as sysdba

Connected.

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

13. Marker setup table script complete, running verification script...

14. Please enter the name of a schema for the GoldenGate database objects:

15. Setting schema name to OGG

16.

17. MARKER TABLE

18. -------------------------------

19. OK

20.

21. MARKER SEQUENCE

22. -------------------------------

23. OK

24.

25. Script complete.

26.

27. SQL> show parameter recyclebin;

28.

29. NAME TYPE VALUE

30. ------------------------------------ ----------- ------------------------------

31. recyclebin string on

32.

關閉回收站：

SQL> alter system set recyclebin=off;

System altered.

SQL> show parameter recyclebin;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string OFF

42. SQL> @ddl_setup.sql

43. Oracle GoldenGate DDL Replication setup script

44. Verifying that current user has privileges to install DDL Replication...

45. You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

46. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

47. NOTE: The schema must be created prior to running this script.

48. NOTE: Stop all DDL replication before starting this installation.

49. Enter Oracle GoldenGate schema name:ogg

50.

51. Working, please wait ...

52. Spooling to file ddl_setup_spool.txt

53.

54. Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

55. Check complete.

56. Using OGG as a Oracle GoldenGate schema name.

57. Working, please wait ...

58. RECYCLEBIN must be empty.

59. This installation will purge RECYCLEBIN for all users.

60. To proceed, enter yes. To stop installation, enter no.

61.

62. Enter yes or no:yes

63. ————————其他輸出省略————————

64.

65. STATUS OF DDL REPLICATION

66. ---------------------------------------------------------------------------------------

67. SUCCESSFUL installation of DDL Replication software components

68.

69. Script complete.

70.

71. SQL> @role_setup.sql

72. GGS Role setup script

73. This script will drop and recreate the role GGS_GGSUSER_ROLE

74. To use a different role name, quit this script and then edit the params.sql script to change th e gg_role parameter to the preferred name. (Do not run the script.)

75.

76. You will be prompted for the name of a schema for the GoldenGate database objects.

77. NOTE: The schema must be created prior to running this script.

78. NOTE: Stop all DDL replication before starting this installation.

79.

80. Enter GoldenGate schema name:ogg

81. Wrote file role_setup_set.txt

82. PL/SQL procedure successfully completed.

83.

84. Role setup script complete

85.

86. Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

87. GRANT GGS_GGSUSER_ROLE TO <loggedUser>

88. where <loggedUser> is the user assigned to the GoldenGate processes.

89.

90. SQL> grant ggs_ggsuser_role to ogg;

91. Grant succeeded.

92.

93. SQL> @ddl_enable.sql

94. Trigger altered.

95.

96. SQL> @?/rdbms/admin/dbmspool.sql #?就是 $ORACLE_HOME

97. Package created.

98. Grant succeeded.

99. View created.

100. Package body created.

101.

102. SQL> @ddl_pin.sql ogg ###要加上空格 ogg,否則需要自己輸入ogg.


	



	    103. PL/SQL procedure successfully completed.  



	    104. PL/SQL procedure successfully completed.  



	    105. PL/SQL procedure successfully completed.

3：source端修改extract程式的params檔案，新增"ddl include all"引數，重啟extract程式


	    1. GGSCI (master2) 1> view params ext_liu  



	   EXTRACT ext_liu



	setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)



	ddl include all 



	userid ogg,password ogg



	exttrail ./dirdat/h1,megabytes 100



	TABLE scott.*;



	    



	    9.GGSCI (master2) 2> stop extract ext_liu  



	    10. Sending STOP request to EXTRACT ext_liu  ...  



	    11. Request processed.  



	    12.  



	    13. GGSCI (master2) 3> start extract ext_liu  



	    14. Sending START request to MANAGER ...  



	    15. EXTRACT ext_liu  starting  



	    16.  



	    17. GGSCI (master2) 4> info extract ext_liu  



	    18. EXTRACT    ext_liu  Last Started 2012-06-20 15:42   Status RUNNING  



	    19. Checkpoint Lag       00:00:00 (updated 00:00:10 ago)  



	    20. Log Read Checkpoint  Oracle Redo Logs  



	    21.                      2012-06-20 15:42:58  Seqno 3, RBA 50044416  



	    22.                      SCN 0.567478 (567478)

在target端操作：

target端修改replicat程式的params檔案，新增ddlerror default ignore retryop maxretries 3 retrydelay 5" 引數，重啟replicat程式注意：說明目標端不是必須要配置DDL include all語句，可有可無


	    1. [root@master1  ~]# su - oracle  



	    2. [oracle@master1 ~]$ cd $GGATE   



	    3. [oracle@master1 ogg]$ ggsci  



	    4. GGSCI (master1)  1> edit params rep_liu



	    5. GGSCI (master1)  2> view params rep_liu



	    REPLICAT rep_liu



	SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8)



	ddlerror default ignore retryop maxretries 3 retrydelay 5



	

 DDL include all                              ##可有可無

	USERID ogg,PASSWORD ogg



	handlecollisions



	REPORTCOUNT EVERY 30 MINUTES, RATE



	REPERROR DEFAULT, ABEND



	numfiles 5000



	assumetargetdefs



	DISCARDFILE ./dirrpt/rep_liu.dsc, APPEND, MEGABYTES 1000



	ALLOWNOOPUPDATES



	MAP scott.*, TARGET scott.*;



	    



	     



	   



	    



	   



	     GGSCI (master1)  3> stop replicat rep_liu



	     Sending STOP request to REPLICAT rep_liu...  



	     Request processed.  



	      



	     GGSCI (master1)  4> start replicat rep_liu



	     Sending START request to MANAGER ...  



	     REPLICAT rep_liu starting  



	     



	  GGSCI (master1)  5> info replicat rep_liu



	     REPLICAT   rep_liu Last Started 2012-06-20 15:50   Status RUNNING  



	     Checkpoint Lag       00:00:00 (updated 00:00:00 ago)  



	     Log Read Checkpoint  File ./dirdat/pa000000  



	                         First Record  RBA 4780973

至此，oracle-oracle的ogg搭建配置已完成，透過測試可以知道，在oracle源端scott使用者下建立表，可以在一定的延遲（3秒以內）同步到目標端。

oracle---oracle的單向ogg搭建流程（DML+DDL）

相關文章