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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29319205/viewspace-1063843/