GOLDENGATE之初体验

一、MANAGER的相关管理

1、  安装和基本环境变量

作为LINUX和UNIX系统LD_LIBRY_PATH需要特别的注意

 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg

PATH=$ORACLE_HOME%/bin:$GGHOME$PATH

2、登陆及检查进程运行情况

登陆GG

oracle@gctest3 ggs]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

 

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

 

检查GG的相关配置和运行情况

 

GGSCI (gctest3) 1> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     STOPPED                                          

 

在这里大家需要注意的是WINDOWS的环境需要要添加WINDOWS的服务，具体命令如下：

 

Install addservice

 

GGSCI (gctest3) 11> create subdirs

 

Creating subdirectories under current directory /oradata/ggs

 

Parameter files                /oradata/ggs/dirprm: already exists

Report files                   /oradata/ggs/dirrpt: already exists

Checkpoint files               /oradata/ggs/dirchk: already exists

Process status files           /oradata/ggs/dirpcs: already exists

SQL script. files               /oradata/ggs/dirsql: already exists

Database definitions files     /oradata/ggs/dirdef: already exists

Extract data files             /oradata/ggs/dirdat: already exists

Temporary files                /oradata/ggs/dirtmp: already exists

Veridata files                 /oradata/ggs/dirver: already exists

Veridata Lock files            /oradata/ggs/dirver/lock: already exists

Veridata Out-Of-Sync files     /oradata/ggs/dirver/oos: already exists

Veridata Out-Of-Sync XML files /oradata/ggs/dirver/oosxml: already exists

Veridata Parameter files       /oradata/ggs/dirver/params: already exists

Veridata Report files          /oradata/ggs/dirver/report: already exists

Veridata Status files          /oradata/ggs/dirver/status: already exists

Veridata Trace files           /oradata/ggs/dirver/trace: already exists

Stdout files                   /oradata/ggs/dirout: already exists

 

这里因为之前我已经执行过此命令，创建了相关的文件。

 

3、启动MANAGER

 

GGSCI (gctest3) 4> edit params mgr

port 10002  --添加服务端口

 

GGSCI (gctest3) 2> start manager

 

Manager started.

 

GGSCI (gctest3) 3> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     RUNNING                                          

 

4、修改数据库的相关配置

 

A、确认数据库是否为归档模式

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oradata/cmtest/archive

Oldest online log sequence     66

Next log sequence to archive   68

Current log sequence           68

 

B、supplemental log是否已经打开，并启用

 

select supplemental_log_data_min from v$database;

 

alter database add supplemental log data;

 

ALTER DATABASE FORCE LOGGING;

 

C、RECYCLEBIN是否关闭 ---10G以上版本为了支持DDL复制建议关闭RECYCLEBIN

 

alter system set recyclebin=off;

 

D、创建SUPPORT的DDL复制管理用户

SQL> create user ggate identified by ggate ;

 

User created.

 

SQL> grant connect,resource,unlimited tablespace to ggate;

 

Grant succeeded.

 

SQL> grant execute on utl_file to ggate;

 

Grant succeeded.

 

SQL>

 

E、运行所有支持DDL复制的脚本

 

这里需要注意的一个问题是需要到GGHOME下去执行以下脚本，不然会出现执行过程中无法通过。

 

SQL> @marker_setup

 

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.

 

SQL> @ddl_setup

 

GoldenGate DDL Replication setup script

 

Verifying that current user has privileges to install DDL Replication...

 

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: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

 

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

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

BEGIN

*

ERROR at line 1:

ORA-20783:

GoldenGate DDL Replication setup:

*** RECYCLEBIN must be turned off.

*** For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set

_RECYCLEBIN in parameter file to FALSE. Then restart database and installation.

ORA-06512: at line 19

这里由于没有关闭RECYCLEBIN

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@gctest3 ggs]$

SQL> @ddl_setup

 

GoldenGate DDL Replication setup script

 

Verifying that current user has privileges to install DDL Replication...

 

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: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.

 

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

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

 

RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.

 

Enter yes or no:YES

 

 

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/admin/cmtest/udump/ggs_ddl_trace.log

 

Analyzing installation status...

 

 

STATUS OF DDL REPLICATION

--------------------------------------------------------------------------------

SUCCESSFUL installation of DDL Replication software components

 

Script. complete.

SQL> @ddl_enable

 

Trigger altered.

 

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

 

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

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.

 

SQL> grant GGS_GGSUSER_ROLE to ggate; 

 

Grant succeeded.

 

创建相关的业务用户，这里我已经创建好了，用户为TEST，由于比较简单。创建过程就不在讲述，未完待续..............