ITPub博客

首页 > 数据库 > Oracle > 搭建Oracle GoldenGat (个人操作实验文档,有兴趣的可以照着实现下)

搭建Oracle GoldenGat (个人操作实验文档,有兴趣的可以照着实现下)

原创 Oracle 作者:GM_DBA 时间:2013-12-19 22:48:16 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 闪回表
下一篇: OCP 053 102-106
请登录后发表评论 登录
全部评论

注册时间:2013-11-20

  • 博文量
    33
  • 访问量
    192141