搭建Oracle GoldenGat （個人操作實驗文件，有興趣的可以照著實現下）

GM_DBA發表於2013-12-19
OracleGo

1 GoldenGate安裝準備
在Oracle官網上下載GoldenGate介質，GoldenGate是包含在Fusion Middleware類目下面，並上傳到源資料庫和目標資料庫主機；
2 GoldenGate安裝過程
注意：此步驟需要在兩臺主機上操作
2.1 建立GoldenGate作業系統使用者
這裡直接使用Oracle使用者安裝GoldenGate，而不建立新的使用者。
2.2 建立GoldenGate安裝路徑
[root@GMDBA ~]# mkdir -p /u01/ggs/11.2.0
[root@GMDBA ~]# mv /u01/software/ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/ggs/11.2.0/
[root@GMDBA ~]# chown -R oracle:oinstall /u01/ggs/
[root@GMDBA ~]# su - oracle
2.3 編輯使用者環境變數
[oracle@GMDBA ogg]$ vi ~/.bash_profile 
# .bash_profile


# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi


# User specific environment and startup programs


PATH=$PATH:$HOME/bin


export PATH


export ORACLE_SID=GMDBA
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:/u01/ggs/11.2.0:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ggs/11.2.0:/lib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
2.4 安裝GoldenGate檔案
[oracle@GMDBA ~]$ cd /u01/ggs/11.2.0/
[oracle@GMDBA 11.2.0]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip 
Archive:  ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
inflating: fbo_ggs_Linux_x86_ora11g_32bit.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@GMDBA 11.2.0]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@GMDBA 11.2.0]$ cd
[oracle@GMDBA ~]$ cd /u01/ggs/11.2.0
[oracle@GMDBA 11.2.0]$ 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 11g on Apr 23 2012 08:09:25


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






GGSCI (GMDBA) 1> create subdirs


Creating subdirectories under current directory /u01/ggs/11.2.0


Parameter files                /u01/ggs/11.2.0/dirprm: already exists
Report files                   /u01/ggs/11.2.0/dirrpt: created
Checkpoint files               /u01/ggs/11.2.0/dirchk: created
Process status files           /u01/ggs/11.2.0/dirpcs: created
SQL script files               /u01/ggs/11.2.0/dirsql: created
Database definitions files     /u01/ggs/11.2.0/dirdef: created
Extract data files             /u01/ggs/11.2.0/dirdat: created
Temporary files                /u01/ggs/11.2.0/dirtmp: created
Stdout files                   /u01/ggs/11.2.0/dirout: created




GGSCI (GMDBA) 2>


注意：
需要進入ogg的安裝目錄在執行ggsci
[oracle@GMDBA ~]$ cd /u01/ogg/11.2.0/
[oracle@GMDBA 11.2.0]$ 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 11g on Apr 23 2012 08:09:25


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






GGSCI (GMDBA) 1> help




GGSCI Command Summary: 


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


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






For help on a specific command, type HELP .


Example: HELP ADD REPLICAT


GGSCI (GMDBA) 2>
2.5 配置源資料庫
2.5.1 開啟歸檔
[oracle@GMDBA ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 15 20:16:57 2013


Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to an idle instance.


SQL> startup
ORACLE instance started.


Total System Global Area  422670336 bytes
Fixed Size                  1345380 bytes
Variable Size             327157916 bytes
Database Buffers           88080384 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.
SQL> show parameter log_archive_dest


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
SQL> show parameter db_recovery


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size           big integer 4977M
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  422670336 bytes
Fixed Size                  1345380 bytes
Variable Size             327157916 bytes
Database Buffers           88080384 bytes
Redo Buffers                6086656 bytes
Database mounted.
SQL> alter database archivelog;


Database altered.


SQL> alter database open;


Database altered.
2.5.2 開啟補充日誌
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
2.5.3 建立GoldenGate管理使用者
SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp;


User created.


SQL> grant connect,resource to ggs;


Grant succeeded.


SQL> grant execute on utl_file to ggs;


Grant succeeded.


SQL> grant select any dictionary,select any table to ggs;


Grant succeeded.


SQL> grant alter any table to ggs;


Grant succeeded.


SQL> grant flashback any table to ggs;


Grant succeeded.


SQL> grant execute on DBMS_FLASHBACK to ggs;


Grant succeeded.


SQL>
2.5.4 新增表級trandata
對hr使用者下的所有表進行同步
[oracle@GMDBA ggs]$ cd 11.2.0/
[oracle@GMDBA 11.2.0]$ ./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 11g on Apr 23 2012 08:09:25


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






GGSCI (GMDBA) 1> dblogin userid ggs password ggs
Successfully logged into database.


GGSCI (GMDBA) 2> add trandata hr.* 


Logging of supplemental redo data enabled for table HR.COUNTRIES.


Logging of supplemental redo data enabled for table HR.DEPARTMENTS.


Logging of supplemental redo data enabled for table HR.EMPLOYEES.


Logging of supplemental redo data enabled for table HR.JOBS.


Logging of supplemental redo data enabled for table HR.JOB_HISTORY.


Logging of supplemental redo data enabled for table HR.LOCATIONS.


Logging of supplemental redo data enabled for table HR.REGIONS.


GGSCI (GMDBA) 3>
2.5.5 配置源端mgr管理程式組
GGSCI (GMDBA) 1> edit params mgr
port 7500
dynamicportlist 7501-7505
autorestart extract *,waitminutes 2,retries 5


GGSCI (GMDBA) 2> view params mgr


port 7500
dynamicportlist 7501-7505
autorestart extract *,waitminutes 2,retries 5


GGSCI (GMDBA) 3> start mgr


Manager started.


GGSCI (GMDBA) 4> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                   
2.5.6 配置Extract抽取程式組
GGSCI (GMDBA) 6> edit params ext1
extract ext1
dynamicresolution
userid ggs,password ggs
setenv(ORACLE_SID=GMDBA)
exttrail /u01/ggs/11.2.0/dirdat/et
table hr.*;


GGSCI (GMDBA) 7> view params ext1


extract ext1
dynamicresolution
userid ggs,password ggs
setenv(ORACLE_SID=GMDBA)
exttrail /u01/ggs/11.2.0/dirdat/et
table hr.*;
建立extract程式
GGSCI (GMDBA) 8> add extract ext1,tranlog,begin now
EXTRACT added.


GGSCI (GMDBA) 9> add exttrail /u01/ggs/11.2.0/dirdat/et,extract EXT1
EXTTRAIL added.


GGSCI (GMDBA) 10> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT1        00:00:00      00:00:41    




GGSCI (GMDBA) 11> start ext1


Sending START request to MANAGER ...
EXTRACT EXT1 starting




GGSCI (GMDBA) 12> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT1        00:12:29      00:00:07    
2.5.7 配置pump投遞程式組
GGSCI (GMDBA) 13> edit params pump1
extract pump1  
dynamicresolution
userid ggs,password ggs
rmthost 192.168.80.30,mgrport 7809,compress
rmttrail /u01/ggs/11.2.0/dirdat/pt
table hr.*;


GGSCI (GMDBA) 14> add extract pump1,exttrailsource /u01/ggs/11.2.0/dirdat/et
EXTRACT added.




GGSCI (GMDBA) 15>ADD RMTTRAIL /u01/ggs/11.2.0/dirdat/pt, EXTRACT PUMP1
RMTTRAIL added.
GGSCI (GMDBA) 16> start pump1


Sending START request to MANAGER ...
EXTRACT PUMP1 starting




GGSCI (GMDBA) 17> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT1        00:00:00      00:00:00    
EXTRACT     RUNNING     PUMP1       00:00:00      00:04:07    
2.6 目標端操作
2.6.1 目標端使用者建立
SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp;


User created.


SQL>grant connect,resource to ggs;


Grant succeeded.


SQL> grant execute on utl_file to ggs;


Grant succeeded.


SQL> grant select any table to ggs;


Grant succeeded.


SQL> grant insert any table to ggs;


Grant succeeded.


SQL> grant delete any table to ggs;


Grant succeeded.


SQL> grant update any table to ggs;


Grant succeeded.


SQL> GRANT ALTER SESSION TO ggs;


Grant succeeded.


SQL> GRANT SELECT ANY DICTIONARY to ggs;


Grant succeeded.
2.6.2 新增checkpoint表
[oracle@GMDBAGC ogg]$ cd 11.2.0/
[oracle@GMDBAGC 11.2.0]$ ./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 11g on Apr 23 2012 08:09:25


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






GGSCI (GMDBAGC) 1> edit params ./GLOBALS
checkpointtable ggs.checkpoint


GGSCI (GMDBAGC) 1> dblogin userid ggs password ggs
ERROR: Unable to connect to database using user ggs. Please check privileges.
ORA-00942: table or view does not exist.


GGSCI (GMDBAGC) 2> dblogin userid ggs password ggs
Successfully logged into database.


GGSCI (GMDBAGC) 3> add checkpointtable ggs.checkpoint


Successfully created checkpoint table ggs.checkpoint.


GGSCI (GMDBAGC) 4>


2.6.3 配置mgr
GGSCI (GMDBAGC) 1> edit params mgr
port 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts /u01/ggs/11.2.0/dirdat/rt*,usecheckpoints,minkeepdays 3


GGSCI (GMDBAGC) 2> start mgr


Manager started.




GGSCI (GMDBAGC) 3> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING 


2.6.4 配置replicat
GGSCI (GMDBAGC) 4> edit params repl 
replicat repl
userid ggs,password ggs
assumetargetdefs
reperror default,discard
discardfile /u01/ggs/11.2.0/dirrpt/repl.dsc,append,megabytes 50
dynamicresolution
map hr.*,target hr.*;


GGSCI (GMDBAGC) 5> add replicat repl,exttrail /u01/ggs/11.2.0/dirdat/pt
REPLICAT added.




GGSCI (GMDBAGC) 4> start repl


Sending START request to MANAGER ...
REPLICAT REPL starting




GGSCI (GMDBAGC) 5> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPL        00:00:00      00:00:01  
3 測試
源庫
SQL> create table test (a int,b int);


Table created.


SQL> insert into test values(1,1);


1 row created.


SQL> commit;


Commit complete.


SQL>
目標庫
SQL> create table test (a int,b int);


Table created.


SQL>
SQL> select * from test;


no rows selected


SQL> select * from test;


         A          B
---------- ----------
         1          1


SQL>


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

相關文章