goldengate11g测试案例

2015-07-15 16:55:59 暂无评论雪泥企业级Java 926

1 安装gg
在源端和目标端分别以oracle用户执行如下操作：
[oracle@bogon ~]$ mkdir /u01/app/ggate
[oracle@bogon ~]$ cd /u01/app
[oracle@bogon app]$ ls
ggate oracle oraInventory
复制到镜像端一份同时解压到本地安装目录
[oracle@bogon app]$ cp ~/ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar /archive2
[oracle@bogon app]$ tar xvf ~/*.tar -C /u01/app/ggate

2添加环境变量，和oracle同一个账户：
[oracle@bogon ggate]$ vim ~/.bash_profile
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11g/db_1
ORACLE_SID=orcl
NLS_LANG=AMERICAN_AMERICA.UTF8

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/u01/app/ggate

export LD_LIBRARY_PATH=/u01/app/ggate:$ORACLE_HOME/lib
export GGATE=/u01/app/ggate

export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG PATH

环境变量里面不能有不存在的路径！

使环境变量生效：

[oracle@bogon ggate]$ source ~/.bash_profile

3 使用ggsci工具，创建必要的目录：
[oracle@bogon ggate]$ ggsci

Oracle goldengate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42

GGSCI (bogon) 1> create subdirs
将创建如下目录：
Parameter files /u01/app/ggate/dirprm: created
Report files /u01/app/ggate/dirrpt: created
Checkpoint files /u01/app/ggate/dirchk: created
Process status files /u01/app/ggate/dirpcs: created
SQL script files /u01/app/ggate/dirsql: created
Database definitions files /u01/app/ggate/dirdef: created
Extract data files /u01/app/ggate/dirdat: created
Temporary files /u01/app/ggate/dirtmp: created
Veridata files /u01/app/ggate/dirver: created
Veridata Lock files /u01/app/ggate/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/ggate/dirver/oosxml: created
Veridata Parameter files /u01/app/ggate/dirver/params: created
Veridata Report files /u01/app/ggate/dirver/report: created
Veridata Status files /u01/app/ggate/dirver/status: created
Veridata Trace files /u01/app/ggate/dirver/trace: created
Stdout files /u01/app/ggate/dirout: created

以上就是gg的安装，在镜像端同样操作！

4 配置source database源端；
Goldengate 通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。

查看源端是否开启归档模式、附加日志、强制日志：
源端必须打开归档模式，开启附加日志和强制日志；

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEMENTAL_LOG_DATA_MI FORCE_LOG
———————————— ———————— ———
NOARCHIVELOG NO NO

SQL>

修改为归档模式：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 469766096 bytes
Database Buffers 150994944 bytes
Redo Buffers 7524352 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

打开force logging模式：
SQL> alter database force logging;

Database altered.

打开supplemental log附加日志：
SQL> alter database add supplemental log data;

Database altered.

启用gg对DDL支持：
。。。
禁用Recycle Bin回收站：
SQL> alter system set recyclebin=off scope=spfile;

System altered.

如果数据库是10g，需要关闭recyclebin并重启；或者手工purge recyclebin。

创建存放DDL信息的user并赋权：
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

退出所有使用oracle的session，然后使用Sysdba权限的用户执行如下脚本：
进入gg目录，然后调用脚本：
[oracle@bogon ~]$ su – oracle
口令：
[oracle@bogon ~]$ echo $GGATE
/u01/app/ggate
[oracle@bogon ~]$ cd `echo $GGATE`
[oracle@bogon ggate]$

[oracle@bogon ggate]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 10 16:18:46 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql;

Marker setup 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:ggate

Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

MARKER TABLE
——————————————————————————–
OK

MARKER SEQUENCE
——————————————————————————–
OK

Script complete.

执行脚本2：
SQL> @ddl_setup.sql;

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication…
Checking user sessions…

Check complete.

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
在oracle10g里，必须关闭recycle bin，在11g以后的版本，可以不关闭；
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

提示输入gg的用户：
Enter GoldenGate schema name:ggate

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
选择安装模式：install和reinstall选择INITIALSETUP
Enter mode of installation:INITIALSETUP

Working, please wait …
Spooling to file ddl_setup_spool.txt

Using GGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait …

DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

CLEAR_TRACE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

CREATE_TRACE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

TRACE_PUT_LINE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

INITIAL_SETUP STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

DDL HISTORY TABLE
——————————————————————————–
OK

DDL HISTORY TABLE(1)
——————————————————————————–
OK

DDL DUMP TABLES
——————————————————————————–
OK

DDL DUMP COLUMNS
——————————————————————————–
OK

DDL DUMP LOG GROUPS
——————————————————————————–
OK

DDL DUMP PARTITIONS
——————————————————————————–
OK

DDL DUMP PRIMARY KEYS
——————————————————————————–
OK

DDL SEQUENCE
——————————————————————————–
OK

GGS_TEMP_COLS
——————————————————————————–
OK

GGS_TEMP_UK
——————————————————————————–
OK

DDL TRIGGER CODE STATUS:

Line/pos
——————————————————————————–
Error
—————————————————————–
No errors
No errors

DDL TRIGGER INSTALL STATUS
——————————————————————————–
OK

DDL TRIGGER RUNNING STATUS
——————————————————————————–
ENABLED

STAYMETADATA IN TRIGGER
——————————————————————————–
OFF

DDL TRIGGER SQL TRACING
——————————————————————————–
0

DDL TRIGGER TRACE LEVEL
——————————————————————————–
0

LOCATION OF DDL TRACE FILE
——————————————————————————–
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION
——————————————————————————–
SUCCESSFUL installation of DDL Replication software components

Script complete.

执行脚本3：
SQL> @role_setup.sql;

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.)

输入gg用户名：
Enter GoldenGate schema name:ggate
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.

脚本4：赋权
SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

脚本5：
SQL> @ddl_enable.sql;

Trigger altered.

这里脚本创建的table都是使用默认的名称，当然可以可以修改这些table的默认名。以上操作中镜像端也执行；

5 测试gg

目标端的用户名和对象名称可以与源端不同，关机在于配置文件的能够匹配；
配置源和目标两端tnsnames，保持互联互通：
执行tnsping正常：
[oracle@bogon ggate]$ tnsping 192.168.128.130

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 10-FEB-2012 16:43:52

Used parameter files:
/u01/app/oracle/product/11g/db_1/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.128.130)(PORT=1521)))
OK (310 msec)

在源端库创建用户并赋权：
SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to sender;

Grant succeeded.

在镜像库创建用户并赋权：
SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to receiver;

Grant succeeded.

在源端和镜像端上配置Manager：

[oracle@bogon ~]$ su – oracle
口令：
[oracle@bogon ~]$ cd `echo $GGATE`
[oracle@bogon ggate]$ info all
[oracle@bogon ggate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42

GGSCI (bogon) 1> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

GGSCI (bogon) 2> edit params mgr

在此处制定端口：PORT 7809

GGSCI (bogon) 3>

启动：
GGSCI (bogon) 3> start manager

Manager started.

在源端source 连接数据库：

GGSCI (bogon) 4> dblogin userid ggate@orcl, password ggate
Successfully logged into database.

添加一个抽取：

GGSCI (bogon) 5> add extract ext1,tranlog, begin now
EXTRACT added.

GGSCI (bogon) 6> add exttrail /u01/app/ggate/dirdate/lt, extract ext1
EXTTRAIL added.

修改抽取进程ext1参数：

GGSCI (bogon) 7> edit params ext1
extract ext1
userid ggate@orcl, password ggate
rmthost orcl, mgrport 7809
rmttrail /u01/app/ggate/dirdate/lt
ddl include mapped objname sender.*;
table sender.*;

查看添加的抽取进程：

GGSCI (bogon) 8> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:29:53

配置TargetDB镜像度同步队列

添加镜像端checkpoint表：
GGSCI (imagehost) 4> edit params ./GLOBAL
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
~
在镜像度添加ggate用户：

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

配置镜像端同步队列：

GGSCI (imagehost) 2> dblogin userid ggate@orcl,password ggate
Successfully logged into database.

GGSCI (imagehost) 3> add replicat rep1,exttrail /u01/app/gate/dirdat/lt, checkpointtable ggate.checkpoint
REPLICAT added.

GGSCI (imagehost) 4> edit params rep1

添加如下内容：

replicat rep1
ASSUMETARGETDEFS
userid ggate@orcl,password ggate
discardfile /u01/app/ggate/dirdat/rep1_discard.txt,append,megabytes 10
DDL
map sender.*,target receiver.*;

在源端启动抽取进程：

GGSCI (bogon) 9> start extract ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

声明: 除非转自他站（如有侵权，请联系处理）外，本文采用 BY-NC-SA 协议进行授权 | 智乐兔
转载请注明：转自《goldengate11g测试案例》
本文地址：https://www.zhiletu.com/archives-439.html
关注公众号：