Oracle_11G搭建单实例GoldenGate步骤

Oracle 11G 搭建单实例GoldenGate步骤

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