Oracle GoldenGate安裝初體驗
實驗環境為兩臺OracleLinux5.7作業系統，分別DBCA安裝好了oracle11g 11.2.0.3資料庫，兩邊的安裝路徑完全相同。ogg的版本為112101 linux86位。
主機 IP oracle_sid
oggsource 192.168.1.20 oggsource
oggtarget 192.168.1.21 oggtarget
很多操作都需要在兩臺主機分別執行，以示區別，我用藍色的字型標示target主機。
下面開始OGG初體驗之旅
OGG安裝目錄為$ORACLE_BASE/gg
SourceDB
[oracle@oggsource ~]$ cd /u01/app/oracle
[oracle@oggsource oracle]$ ll
total 32
drwxr-x--- 3 oracle oinstall 4096 Aug 5 08:46 admin
drwxr-x--- 5 oracle oinstall 4096 Aug 5 08:47 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 13:19 checkpoints
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database
drwxrwxr-x 11 oracle oinstall 4096 Jul 30 13:18 diag
drwxr-x--- 4 oracle oinstall 4096 Aug 5 08:47 fast_recovery_area
drwxr-x--- 3 oracle oinstall 4096 Aug 5 08:46 oradata
drwxr-xr-x 3 oracle oinstall 4096 Jul 30 13:16 product
[oracle@oggsource oracle]$ mkdir gg
[oracle@oggsource oracle]$ cd gg
[oracle@oggsource gg]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[[oracle@oggsource gg]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
OGG安裝目錄為$ORACLE_BASE/gg
TargetDB
[oracle@oggtarget ~]$ cd /u01/app/oracle
[oracle@oggtarget oracle]$ mkdir gg
[oracle@oggtarget oracle]$ cd gg
[oracle@oggtarget gg]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@oggtarget gg]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
Oracle使用者下的bash_profile檔案中配置關於OGG的資訊
SourceDB
vi /home/oracle/.bash_profile
export ORACLE_SID=oggsource
export ORACLE_BASE=/u01/app/oracle
export OGG_HOME=$ORACLE_BASE/gg <==增加一個OGG_HOME
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME <==增加OGG_HOME到PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
umask 022
alias s="sqlplus / as sysdba"
stty erase ^h
[oracle@oggsource ~]$ . .bash_profile
Oracle使用者下的bash_profile檔案中配置關於OGG的資訊
TargetDB
vi /home/oracle/.bash_profile
export ORACLE_SID=oggtarget
export ORACLE_BASE=/u01/app/oracle
export OGG_HOME=$ORACLE_BASE/gg
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
umask 022
alias s="sqlplus / as sysdba"
stty erase ^h
[oracle@oggtarget ~]$ . .bash_profile
安裝OGG，操作很簡單
SourceDB
[oracle@oggsource ~]$ cd $OGG_HOME
[oracle@oggsource gg]$ 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.
GGSCI (oggsource) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg
Parameter files /u01/app/oracle/gg/dirprm: already exists
Report files /u01/app/oracle/gg/dirrpt: created
Checkpoint files /u01/app/oracle/gg/dirchk: created
Process status files /u01/app/oracle/gg/dirpcs: created
SQL script files /u01/app/oracle/gg/dirsql: created
Database definitions files /u01/app/oracle/gg/dirdef: created
Extract data files /u01/app/oracle/gg/dirdat: created
Temporary files /u01/app/oracle/gg/dirtmp: created
Stdout files /u01/app/oracle/gg/dirout: created
安裝OGG，操作很簡單
TargetDB
[oracle@oggtarget gg]$ 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.
GGSCI (oggtarget) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg
Parameter files /u01/app/oracle/gg/dirprm: already exists
Report files /u01/app/oracle/gg/dirrpt: created
Checkpoint files /u01/app/oracle/gg/dirchk: created
Process status files /u01/app/oracle/gg/dirpcs: created
SQL script files /u01/app/oracle/gg/dirsql: created
Database definitions files /u01/app/oracle/gg/dirdef: created
Extract data files /u01/app/oracle/gg/dirdat: created
Temporary files /u01/app/oracle/gg/dirtmp: created
Stdout files /u01/app/oracle/gg/dirout: created
到此OGG的安裝結束。除了加壓tar包，建立一些目錄就沒什麼操作了。
在配置OGG前，先要確保資料庫處與歸檔模式下
SourceDB
SYS@oggsource >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
將資料庫force logging和supplemental_log_data全部開啟，確保DML操作產生日誌，並且在傳輸日誌變化的時候有足夠的資訊來標示當前資料，不僅僅依靠rowid。
SYS@oggsource >alter database force logging;
SYS@oggsource >alter database add supplemental log data;
SYS@oggsource >col open_mode for a10
SYS@oggsource >select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME OPEN_MODE FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI
--------------------------- ---------- --------- ------------------------
OGGSOURC READ WRITE YES YES
SYS@oggsource >alter system switch logfile;
建立一個測試使用者snow，用來產生資料庫動作
SYS@oggsource >create user snow identified by snow default tablespace users;
為了操作不受到許可權限制，此處給予DBA許可權。生產環境中不建議這樣做。
SYS@oggsource >grant dba to snow;
用測試使用者snow建立測試表T1
SYS@oggsource >conn snow/snow
SNOW@oggsource >create table t1 (id int primary key, name varchar2(20));
SNOW@oggsource >conn / as sysdba
建立一個表空間oggts
SYS@oggsource >create tablespace oggts datafile '/u01/app/oracle/oradata/oggsource/oggts01.dbf' size 100m autoextend on;
SYS@oggsource >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oggsource/system01.dbf
/u01/app/oracle/oradata/oggsource/sysaux01.dbf
/u01/app/oracle/oradata/oggsource/undotbs01.dbf
/u01/app/oracle/oradata/oggsource/users01.dbf
/u01/app/oracle/oradata/oggsource/oggts01.dbf
建立使用者oggadmin用來管理ogg相關的工作，預設表空間為剛剛建立的oggts，避免佔用system表空間。
SYS@oggsource >create user oggadmin identified by oggadmin default tablespace oggts;
SYS@oggsource >grant dba to oggadmin;
執行OGG安裝包中的sql語句，來產生角色。該sql檔案在$OGG_HOME下
SYS@oggsource >@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:oggadmin <==輸入剛剛建立的使用者oggadmin
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.
SYS@oggsource >GRANT GGS_GGSUSER_ROLE TO oggadmin; <==按照提示授權給oggadmin使用者GGS_GGSUSER_ROLE許可權
Grant succeeded.
在Target端執行同樣的操作
TargetDB
SYS@oggtarget >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SYS@oggtarget >create user snow identified by snow default tablespace users;
SYS@oggtarget >grant dba to snow;
SYS@oggtarget >conn snow/snow
SNOW@oggtarget >create table t1 (id int primary key, name varchar2(20));
SNOW@oggtarget >conn / as sysdba
SYS@oggtarget >alter database force logging;
SYS@oggtarget >alter database add supplemental log data;
SYS@oggtarget >col open_mode for a10
SYS@oggtarget >select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME OPEN_MODE FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI
--------------------------- ---------- --------- ------------------------
OGGTARGE READ WRITE YES YES
SYS@oggtarget >alter system switch logfile;
SYS@oggtarget >create tablespace oggts datafile '/u01/app/oracle/oradata/oggtarget/oggts01.dbf' size 100m autoextend on;
SYS@oggtarget >create user oggadmin identified by oggadmin default tablespace oggts;
SYS@oggtarget >grant dba to oggadmin;
SYS@oggtarget >@role_setup
Enter GoldenGate schema name:oggadmin <==輸入oggadmin
SYS@oggtarget >GRANT GGS_GGSUSER_ROLE TO oggadmin;
配置OGG各項引數
SourceDB
[oracle@oggsource ~]$ cd $OGG_HOME
[oracle@oggsource gg]$ 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.
GGSCI (oggsource) 1> edit params ./GLOBALS
GGSCHEMA oggadmin
GGSCI (oggsource) 2> edit params mgr
PORT 7809 <==OGG管理程式的通訊埠，注意source與target要保持一致
GGSCI (oggsource) 3> edit params ex1
EXTRACT ex1
USERID oggadmin, PASSWORD oggadmin <==抽取程式透過使用者oggadmin訪問資料庫
EXTTRAIL /u01/app/oracle/gg/dirdat/ex <==產生trail檔案後儲存的路徑
TABLE snow.*; <==抽取物件為使用者snow下所有表
GGSCI (oggsource) 4> edit params dp1
EXTRACT dp1
USERID oggadmin, PASSWORD oggadmin
RMTHOST 192.168.1.21, MGRPORT 7809 <==推送目的地為target資料庫。ip地址為192.168.1.21，埠7809
RMTTRAIL /u01/app/oracle/gg/dirdat/rt <==接收端trail檔案目錄
TABLE snow.*;
TargetDB
[oracle@oggtarget gg]$ ggsci
GGSCI (oggtarget) 1> edit params ./GLOBALS
GGSCHEMA oggadmin
GGSCI (oggtarget) 2> edit params mgr
PORT 7809
GGSCI (oggtarget) 3> edit params rt1 <==target端獨有的配置
REPLICAT rt1
USERID oggadmin, PASSWORD oggadmin
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/discards.dsc, PURGE
MAP snow.*, TARGET snow.*; <==匹配source端與target端的名稱
GGSCI (oggtarget) 5> dblogin userid oggadmin,password oggadmin
Successfully logged into database.
GGSCI (oggtarget) 6> ADD CHECKPOINTTABLE oggadmin.checkpointtable <==target端獨有的配置，記錄著已經提交的事務
Successfully created checkpoint table oggadmin.checkpointtable.
SYS@oggtarget >conn oggadmin/oggadmin
OGGADMIN@oggtarget >col object_name for a20
OGGADMIN@oggtarget >col object_type for a20
OGGADMIN@oggtarget >select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SYS_C0011004 INDEX
CHECKPOINTTABLE_LOX TABLE
SYS_C0010998 INDEX
CHECKPOINTTABLE TABLE
在全域性變數中加入檢查點資訊
GGSCI (oggtarget) 8> edit params ./GLOBALS
GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpointtable
將使用者snow下的表T1加入到佇列中
SourceDB
GGSCI (oggsource) 6> dblogin userid oggadmin,password oggadmin
Successfully logged into database.
GGSCI (oggsource) 7> ADD TRANDATA snow.t1
Logging of supplemental redo data enabled for table SNOW.T1.
設定抽取程式
GGSCI (oggsource) 9> ADD EXTRACT ex1,TRANLOG,BEGIN NOW
EXTRACT added.
GGSCI (oggsource) 10> ADD EXTTRAIL /u01/app/oracle/gg/dirdat/ex, EXTRACT ex1
EXTTRAIL added.
設定推送程式
GGSCI (oggsource) 11> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/gg/dirdat/ex
EXTRACT added.
GGSCI (oggsource) 12> ADD RMTTRAIL /u01/app/oracle/gg/dirdat/rt, EXTRACT dp1
RMTTRAIL added.
啟動管理程式
GGSCI (oggsource) 15> start mgr
啟動抽取程式和推送程式
GGSCI (oggsource) 18> start *
GGSCI (oggsource) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:05:06
EXTRACT RUNNING EX1 00:08:11 00:00:02
trail檔案已經產生
GGSCI (oggsource) 21> sh ls -ltr /u01/app/oracle/gg/dirdat
-rw-rw-rw- 1 oracle oinstall 1048 Aug 5 11:02 ex000000
TargetDB
GGSCI (oggtarget) 9> ADD REPLICAT rt1, EXTTRAIL /u01/app/oracle/gg/dirdat/rt, checkpointtable oggadmin.checkpointtable
GGSCI (oggtarget) 11> start mgr
GGSCI (oggtarget) 12> start *
GGSCI (oggtarget) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RT1 00:00:00 00:00:03
target端的管理程式、抽取整合、推送程式已經全部啟動。
source端的管理整合、複製程式全部啟動。
現在開始進行測試，在source端插入資料，檢視target端是否可以同步。
SYS@oggsource >conn snow/snow
SNOW@oggsource >insert into t1 values(0,'snow');
SNOW@oggsource >commit;
SNOW@oggsource >insert into t1 values(1,'snow');
SNOW@oggsource >commit;
SNOW@oggsource >insert into t1 values(3,'snow');
SNOW@oggsource >commit;
SYS@oggtarget >conn snow/snow
Connected.
SNOW@oggtarget >select * from t1;
ID NAME
---------- ------------------------------------------------------------
0 snow
1 snow
3 snow
本次初體驗順利結束
主機 IP oracle_sid
oggsource 192.168.1.20 oggsource
oggtarget 192.168.1.21 oggtarget
很多操作都需要在兩臺主機分別執行，以示區別，我用藍色的字型標示target主機。
下面開始OGG初體驗之旅
OGG安裝目錄為$ORACLE_BASE/gg
SourceDB
[oracle@oggsource ~]$ cd /u01/app/oracle
[oracle@oggsource oracle]$ ll
total 32
drwxr-x--- 3 oracle oinstall 4096 Aug 5 08:46 admin
drwxr-x--- 5 oracle oinstall 4096 Aug 5 08:47 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 4096 Jul 30 13:19 checkpoints
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database
drwxrwxr-x 11 oracle oinstall 4096 Jul 30 13:18 diag
drwxr-x--- 4 oracle oinstall 4096 Aug 5 08:47 fast_recovery_area
drwxr-x--- 3 oracle oinstall 4096 Aug 5 08:46 oradata
drwxr-xr-x 3 oracle oinstall 4096 Jul 30 13:16 product
[oracle@oggsource oracle]$ mkdir gg
[oracle@oggsource oracle]$ cd gg
[oracle@oggsource gg]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[[oracle@oggsource gg]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
OGG安裝目錄為$ORACLE_BASE/gg
TargetDB
[oracle@oggtarget ~]$ cd /u01/app/oracle
[oracle@oggtarget oracle]$ mkdir gg
[oracle@oggtarget oracle]$ cd gg
[oracle@oggtarget gg]$ unzip /tmp/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@oggtarget gg]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
Oracle使用者下的bash_profile檔案中配置關於OGG的資訊
SourceDB
vi /home/oracle/.bash_profile
export ORACLE_SID=oggsource
export ORACLE_BASE=/u01/app/oracle
export OGG_HOME=$ORACLE_BASE/gg <==增加一個OGG_HOME
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME <==增加OGG_HOME到PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
umask 022
alias s="sqlplus / as sysdba"
stty erase ^h
[oracle@oggsource ~]$ . .bash_profile
Oracle使用者下的bash_profile檔案中配置關於OGG的資訊
TargetDB
vi /home/oracle/.bash_profile
export ORACLE_SID=oggtarget
export ORACLE_BASE=/u01/app/oracle
export OGG_HOME=$ORACLE_BASE/gg
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export LANG=en_US
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
umask 022
alias s="sqlplus / as sysdba"
stty erase ^h
[oracle@oggtarget ~]$ . .bash_profile
安裝OGG，操作很簡單
SourceDB
[oracle@oggsource ~]$ cd $OGG_HOME
[oracle@oggsource gg]$ 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.
GGSCI (oggsource) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg
Parameter files /u01/app/oracle/gg/dirprm: already exists
Report files /u01/app/oracle/gg/dirrpt: created
Checkpoint files /u01/app/oracle/gg/dirchk: created
Process status files /u01/app/oracle/gg/dirpcs: created
SQL script files /u01/app/oracle/gg/dirsql: created
Database definitions files /u01/app/oracle/gg/dirdef: created
Extract data files /u01/app/oracle/gg/dirdat: created
Temporary files /u01/app/oracle/gg/dirtmp: created
Stdout files /u01/app/oracle/gg/dirout: created
安裝OGG，操作很簡單
TargetDB
[oracle@oggtarget gg]$ 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.
GGSCI (oggtarget) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg
Parameter files /u01/app/oracle/gg/dirprm: already exists
Report files /u01/app/oracle/gg/dirrpt: created
Checkpoint files /u01/app/oracle/gg/dirchk: created
Process status files /u01/app/oracle/gg/dirpcs: created
SQL script files /u01/app/oracle/gg/dirsql: created
Database definitions files /u01/app/oracle/gg/dirdef: created
Extract data files /u01/app/oracle/gg/dirdat: created
Temporary files /u01/app/oracle/gg/dirtmp: created
Stdout files /u01/app/oracle/gg/dirout: created
到此OGG的安裝結束。除了加壓tar包，建立一些目錄就沒什麼操作了。
在配置OGG前，先要確保資料庫處與歸檔模式下
SourceDB
SYS@oggsource >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
將資料庫force logging和supplemental_log_data全部開啟，確保DML操作產生日誌，並且在傳輸日誌變化的時候有足夠的資訊來標示當前資料，不僅僅依靠rowid。
SYS@oggsource >alter database force logging;
SYS@oggsource >alter database add supplemental log data;
SYS@oggsource >col open_mode for a10
SYS@oggsource >select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME OPEN_MODE FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI
--------------------------- ---------- --------- ------------------------
OGGSOURC READ WRITE YES YES
SYS@oggsource >alter system switch logfile;
建立一個測試使用者snow，用來產生資料庫動作
SYS@oggsource >create user snow identified by snow default tablespace users;
為了操作不受到許可權限制，此處給予DBA許可權。生產環境中不建議這樣做。
SYS@oggsource >grant dba to snow;
用測試使用者snow建立測試表T1
SYS@oggsource >conn snow/snow
SNOW@oggsource >create table t1 (id int primary key, name varchar2(20));
SNOW@oggsource >conn / as sysdba
建立一個表空間oggts
SYS@oggsource >create tablespace oggts datafile '/u01/app/oracle/oradata/oggsource/oggts01.dbf' size 100m autoextend on;
SYS@oggsource >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oggsource/system01.dbf
/u01/app/oracle/oradata/oggsource/sysaux01.dbf
/u01/app/oracle/oradata/oggsource/undotbs01.dbf
/u01/app/oracle/oradata/oggsource/users01.dbf
/u01/app/oracle/oradata/oggsource/oggts01.dbf
建立使用者oggadmin用來管理ogg相關的工作，預設表空間為剛剛建立的oggts，避免佔用system表空間。
SYS@oggsource >create user oggadmin identified by oggadmin default tablespace oggts;
SYS@oggsource >grant dba to oggadmin;
執行OGG安裝包中的sql語句，來產生角色。該sql檔案在$OGG_HOME下
SYS@oggsource >@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:oggadmin <==輸入剛剛建立的使用者oggadmin
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
SYS@oggsource >GRANT GGS_GGSUSER_ROLE TO oggadmin; <==按照提示授權給oggadmin使用者GGS_GGSUSER_ROLE許可權
Grant succeeded.
在Target端執行同樣的操作
TargetDB
SYS@oggtarget >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SYS@oggtarget >create user snow identified by snow default tablespace users;
SYS@oggtarget >grant dba to snow;
SYS@oggtarget >conn snow/snow
SNOW@oggtarget >create table t1 (id int primary key, name varchar2(20));
SNOW@oggtarget >conn / as sysdba
SYS@oggtarget >alter database force logging;
SYS@oggtarget >alter database add supplemental log data;
SYS@oggtarget >col open_mode for a10
SYS@oggtarget >select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME OPEN_MODE FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI
--------------------------- ---------- --------- ------------------------
OGGTARGE READ WRITE YES YES
SYS@oggtarget >alter system switch logfile;
SYS@oggtarget >create tablespace oggts datafile '/u01/app/oracle/oradata/oggtarget/oggts01.dbf' size 100m autoextend on;
SYS@oggtarget >create user oggadmin identified by oggadmin default tablespace oggts;
SYS@oggtarget >grant dba to oggadmin;
SYS@oggtarget >@role_setup
Enter GoldenGate schema name:oggadmin <==輸入oggadmin
SYS@oggtarget >GRANT GGS_GGSUSER_ROLE TO oggadmin;
配置OGG各項引數
SourceDB
[oracle@oggsource ~]$ cd $OGG_HOME
[oracle@oggsource gg]$ 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.
GGSCI (oggsource) 1> edit params ./GLOBALS
GGSCHEMA oggadmin
GGSCI (oggsource) 2> edit params mgr
PORT 7809 <==OGG管理程式的通訊埠，注意source與target要保持一致
GGSCI (oggsource) 3> edit params ex1
EXTRACT ex1
USERID oggadmin, PASSWORD oggadmin <==抽取程式透過使用者oggadmin訪問資料庫
EXTTRAIL /u01/app/oracle/gg/dirdat/ex <==產生trail檔案後儲存的路徑
TABLE snow.*; <==抽取物件為使用者snow下所有表
GGSCI (oggsource) 4> edit params dp1
EXTRACT dp1
USERID oggadmin, PASSWORD oggadmin
RMTHOST 192.168.1.21, MGRPORT 7809 <==推送目的地為target資料庫。ip地址為192.168.1.21，埠7809
RMTTRAIL /u01/app/oracle/gg/dirdat/rt <==接收端trail檔案目錄
TABLE snow.*;
TargetDB
[oracle@oggtarget gg]$ ggsci
GGSCI (oggtarget) 1> edit params ./GLOBALS
GGSCHEMA oggadmin
GGSCI (oggtarget) 2> edit params mgr
PORT 7809
GGSCI (oggtarget) 3> edit params rt1 <==target端獨有的配置
REPLICAT rt1
USERID oggadmin, PASSWORD oggadmin
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/discards.dsc, PURGE
MAP snow.*, TARGET snow.*; <==匹配source端與target端的名稱
GGSCI (oggtarget) 5> dblogin userid oggadmin,password oggadmin
Successfully logged into database.
GGSCI (oggtarget) 6> ADD CHECKPOINTTABLE oggadmin.checkpointtable <==target端獨有的配置，記錄著已經提交的事務
Successfully created checkpoint table oggadmin.checkpointtable.
SYS@oggtarget >conn oggadmin/oggadmin
OGGADMIN@oggtarget >col object_name for a20
OGGADMIN@oggtarget >col object_type for a20
OGGADMIN@oggtarget >select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
SYS_C0011004 INDEX
CHECKPOINTTABLE_LOX TABLE
SYS_C0010998 INDEX
CHECKPOINTTABLE TABLE
在全域性變數中加入檢查點資訊
GGSCI (oggtarget) 8> edit params ./GLOBALS
GGSCHEMA oggadmin
CHECKPOINTTABLE oggadmin.checkpointtable
將使用者snow下的表T1加入到佇列中
SourceDB
GGSCI (oggsource) 6> dblogin userid oggadmin,password oggadmin
Successfully logged into database.
GGSCI (oggsource) 7> ADD TRANDATA snow.t1
Logging of supplemental redo data enabled for table SNOW.T1.
設定抽取程式
GGSCI (oggsource) 9> ADD EXTRACT ex1,TRANLOG,BEGIN NOW
EXTRACT added.
GGSCI (oggsource) 10> ADD EXTTRAIL /u01/app/oracle/gg/dirdat/ex, EXTRACT ex1
EXTTRAIL added.
設定推送程式
GGSCI (oggsource) 11> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/gg/dirdat/ex
EXTRACT added.
GGSCI (oggsource) 12> ADD RMTTRAIL /u01/app/oracle/gg/dirdat/rt, EXTRACT dp1
RMTTRAIL added.
啟動管理程式
GGSCI (oggsource) 15> start mgr
啟動抽取程式和推送程式
GGSCI (oggsource) 18> start *
GGSCI (oggsource) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:05:06
EXTRACT RUNNING EX1 00:08:11 00:00:02
trail檔案已經產生
GGSCI (oggsource) 21> sh ls -ltr /u01/app/oracle/gg/dirdat
-rw-rw-rw- 1 oracle oinstall 1048 Aug 5 11:02 ex000000
TargetDB
GGSCI (oggtarget) 9> ADD REPLICAT rt1, EXTTRAIL /u01/app/oracle/gg/dirdat/rt, checkpointtable oggadmin.checkpointtable
GGSCI (oggtarget) 11> start mgr
GGSCI (oggtarget) 12> start *
GGSCI (oggtarget) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RT1 00:00:00 00:00:03
target端的管理程式、抽取整合、推送程式已經全部啟動。
source端的管理整合、複製程式全部啟動。
現在開始進行測試，在source端插入資料，檢視target端是否可以同步。
SYS@oggsource >conn snow/snow
SNOW@oggsource >insert into t1 values(0,'snow');
SNOW@oggsource >commit;
SNOW@oggsource >insert into t1 values(1,'snow');
SNOW@oggsource >commit;
SNOW@oggsource >insert into t1 values(3,'snow');
SNOW@oggsource >commit;
SYS@oggtarget >conn snow/snow
Connected.
SNOW@oggtarget >select * from t1;
ID NAME
---------- ------------------------------------------------------------
0 snow
1 snow
3 snow
本次初體驗順利結束
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1245563/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 18c安裝初體驗Oracle
- Oracle GoldenGate安裝（一）OracleGo
- Oracle GoldenGate安裝（二）OracleGo
- Oracle GoldenGate安裝（三）OracleGo
- Go初體驗｜Mac上安裝GoGoMac
- Flutter安裝、配置、初體驗 windows 版FlutterWindows
- Oracle GoldenGate Veridata 12.2.1.4安裝配置使用全手冊OracleGo
- Windows下安裝PostgreSQL初體驗(使用Installer)WindowsSQL
- oracle 19c 初體驗Oracle
- flutter安裝詳解--初體驗--問題解決Flutter
- Nacos 學習筆記：安裝執行初體驗筆記
- GBase 8s資料庫初體驗-01安裝資料庫
- ORACLE GoldenGate Initial LoadOracleGo
- About the Oracle GoldenGate TrailOracleGoAI
- 靜默方式安裝、升級oracle(一): 安裝oracle軟體Oracle
- 11. Oracle for Linux安裝和配置—11.3. Oracle安裝和配置—11.3.1. Oracle軟體安裝OracleLinux
- 2.2. 安裝Oracle軟體Oracle
- 安裝和體驗hiveHive
- Oracle GoldenGate 18.1釋出OracleGo
- GoldenGate抽取Informix資料庫安裝及配置GoORM資料庫
- 初學 Python 需要安裝哪些軟體？Python
- 直播預告 | CloudQuery初體驗——安裝及多資料來源連線Cloud
- 2024 安裝體驗 React NativeReact Native
- ReactNative初體驗React
- OpenCV 初體驗OpenCV
- http初體驗HTTP
- Flutter初體驗Flutter
- Nuxt 初體驗UX
- jQuery初體驗jQuery
- indexedDB 初體驗Index
- ollama 初體驗
- AQS初體驗AQS
- Compose初體驗
- krpano初體驗
- Angular 初體驗Angular
- outline初體驗
- Selenium 初體驗
- Prettier初體驗
- wepy初體驗