ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 配置支持DML和DDL操作同步的GoldenGate

配置支持DML和DDL操作同步的GoldenGate

原创 Linux操作系统 作者:lirenquan 时间:2011-03-09 09:33:42 0 删除 编辑

测试环境描述
SOURCE:
主机:vmware workstation 7.0
操作系统:CENTOS 5.5 32bit
数据库版本: ORACLE 10.2.0.4
IP:192.168.0.88
SID:orcl
TARGET:
主机:vmware workstation 7.0
操作系统:CENTOS 5.5 32bit
数据库版本: ORACLE 10.2.0.4
IP:192.168.0.88
SID:ggtarge

以下操作需要操作如无特殊说明,均为在源数据库和目标数据库上操作
1、安装GoldGate
1)将Goldgate的压缩包传送到相对应的目录,并解压
[ora10@dev160 gg10]$ ls -l
total 401000
-rwxr-xr-x 1 ora10 dba    500964 Aug  6  2010 OGG_WinUnix_Rel_Notes_11.1.1.0.0_078.pdf
-rwxrwxr-x 1 ora10 dba     26726 Aug  3  2010 README.txt
drwxr-x--- 7 ora10 dba      4096 Jul 29  2010 UserExitExamples
-r--r--r-- 1 ora10 dba       426 Mar 12  2007 bcpfmt.tpl
-r--r--r-- 1 ora10 dba      1725 Sep 11  2002 bcrypt.txt
-r--r--r-- 1 ora10 dba       739 Mar 13  2010 chkpt_ora_create.sql
-rwxr-xr-x 1 ora10 dba   7960573 Jul 29  2010 cobgen
-rwxr-xr-x 1 ora10 dba   7890642 Jul 29  2010 convchk
-r--r--r-- 1 ora10 dba       159 Mar 12  2007 db2cntl.tpl
-r--r--r-- 1 ora10 dba      3334 Mar 12  2007 ddl_access.tpl
-r--r--r-- 1 ora10 dba      1059 Mar 13  2010 ddl_cleartrace.sql
-r--r--r-- 1 ora10 dba      3017 Mar 12  2007 ddl_db2.tpl
-r--r--r-- 1 ora10 dba      3502 Mar 12  2007 ddl_db2_os390.tpl
-r--r--r-- 1 ora10 dba      4189 Mar 13  2010 ddl_ddl2file.sql
-r--r--r-- 1 ora10 dba       746 Mar 13  2010 ddl_disable.sql
-r--r--r-- 1 ora10 dba       692 Mar 13  2010 ddl_enable.sql
-r--r--r-- 1 ora10 dba      2984 Mar 12  2007 ddl_informix.tpl
-r--r--r-- 1 ora10 dba      3414 Mar 23  2010 ddl_mss.tpl
-r--r--r-- 1 ora10 dba      3481 Jun  2  2007 ddl_mysql.tpl
-r--r--r-- 1 ora10 dba       388 Mar 13  2010 ddl_nopurgeRecyclebin.sql
-r--r--r-- 1 ora10 dba      2653 Mar 18  2007 ddl_nssql.tpl
-r--r--r-- 1 ora10 dba     12424 May 22  2010 ddl_ora10.sql
-r--r--r-- 1 ora10 dba      3863 May 22  2010 ddl_ora10upCommon.sql
-r--r--r-- 1 ora10 dba     11064 Mar 13  2010 ddl_ora11.sql
-r--r--r-- 1 ora10 dba     12365 Mar 13  2010 ddl_ora9.sql
-r--r--r-- 1 ora10 dba      3458 Mar 12  2007 ddl_oracle.tpl
-r--r--r-- 1 ora10 dba      1026 Mar 13  2010 ddl_pin.sql
-r--r--r-- 1 ora10 dba      1227 Mar 13  2010 ddl_purgeRecyclebin.sql
-r--r--r-- 1 ora10 dba      3686 May 14  2010 ddl_remove.sql
-r--r--r-- 1 ora10 dba      1053 Jun 24  2009 ddl_session.sql
-r--r--r-- 1 ora10 dba       425 Jun 30  2009 ddl_session1.sql
-r-xr-xr-x 1 ora10 dba    228606 Jun 30  2010 ddl_setup.sql
-r--r--r-- 1 ora10 dba      2652 Jun  7  2007 ddl_sqlmx.tpl
-r--r--r-- 1 ora10 dba      8872 Mar 13  2010 ddl_status.sql
-r--r--r-- 1 ora10 dba      2506 Mar 13  2010 ddl_staymetadata_off.sql
-r--r--r-- 1 ora10 dba      2501 Mar 13  2010 ddl_staymetadata_on.sql
-r--r--r-- 1 ora10 dba      3523 Mar 12  2007 ddl_sybase.tpl
-r--r--r-- 1 ora10 dba      2653 Mar 12  2007 ddl_tandem.tpl
-r--r--r-- 1 ora10 dba      2543 Mar 13  2010 ddl_trace_off.sql
-r--r--r-- 1 ora10 dba      2862 Mar 13  2010 ddl_trace_on.sql
-r--r--r-- 1 ora10 dba      2955 Mar 13  2010 ddl_tracelevel.sql
-rwxr-xr-x 1 ora10 dba   8039613 Jul 29  2010 ddlcob
-rwxr-xr-x 1 ora10 dba   9415801 Jul 29  2010 ddlgen
-rwxr-xr-x 1 ora10 dba   9289214 Jul 29  2010 defgen
-r--r--r-- 1 ora10 dba      1217 Mar 13  2010 demo_more_ora_create.sql
-r--r--r-- 1 ora10 dba       967 Mar 13  2010 demo_more_ora_insert.sql
-r--r--r-- 1 ora10 dba       883 Mar 13  2010 demo_ora_create.sql
-r--r--r-- 1 ora10 dba       821 Mar 13  2010 demo_ora_insert.sql
-r--r--r-- 1 ora10 dba      4015 Mar 13  2010 demo_ora_lob_create.sql
-r--r--r-- 1 ora10 dba      2275 Mar 13  2010 demo_ora_misc.sql
-r--r--r-- 1 ora10 dba      1269 Mar 13  2010 demo_ora_pk_befores_create.sql
-r--r--r-- 1 ora10 dba      1227 Mar 13  2010 demo_ora_pk_befores_insert.sql
-r--r--r-- 1 ora10 dba      2520 Mar 13  2010 demo_ora_pk_befores_updates.sql
-rwxr-xr-x 1 ora10 dba   8432527 Jul 29  2010 emsclnt
-rwxr-xr-x 1 ora10 dba  39121727 Jul 29  2010 extract
-r--r--r-- 1 ora10 dba      1968 May 14  2009 freeBSD.txt
-r--r--r-- 1 ora10 dba    448512 Jul  7  2010 ggMessage.dat
-rw-rw---- 1 ora10 dba 204902400 Jul 29  2010 ggs_Linux_x64_ora10g_64bit_v11_1_1_0_0_078.tar
-rwxr-xr-x 1 ora10 dba  16248101 Jul 29  2010 ggsci
-r--r--r-- 1 ora10 dba    133635 Jul 17  2010 help.txt
-rwxr-xr-x 1 ora10 dba    105182 Jul 29  2010 keygen
-r--r--r-- 1 ora10 dba  11360748 Mar 20  2008 libicudata.so.38
-r--r--r-- 1 ora10 dba   5359083 Mar 20  2008 libicui18n.so.38
-r--r--r-- 1 ora10 dba   4034679 Mar 20  2008 libicuuc.so.38
-r-xr-xr-x 1 ora10 dba   5323945 Feb  2  2010 libxerces-c.so.28
-r--r--r-- 1 ora10 dba      1668 Mar  9  2005 libxml2.txt
-rwxr-xr-x 1 ora10 dba   1350044 Jul 29  2010 logdump
-r--r--r-- 1 ora10 dba      2162 Mar 13  2010 marker_remove.sql
-r--r--r-- 1 ora10 dba      3702 Mar 13  2010 marker_setup.sql
-r--r--r-- 1 ora10 dba      1715 Mar 13  2010 marker_status.sql
-rwxr-xr-x 1 ora10 dba  21980806 Jul 29  2010 mgr
-r--r--r-- 1 ora10 dba    206097 Jan 16  2010 notices.txt
-r--r--r-- 1 ora10 dba      4539 May 21  2010 params.sql
-rwxr-xr-x 1 ora10 dba  33431908 Jul 29  2010 replicat
-rwxr-xr-x 1 ora10 dba    401915 Jul 29  2010 reverse
-r--r--r-- 1 ora10 dba      4042 Mar 13  2010 role_setup.sql
-rwxr-xr-x 1 ora10 dba  13569641 Jul 29  2010 server
-r--r--r-- 1 ora10 dba       248 Mar 12  2007 sqlldr.tpl
-r--r--r-- 1 ora10 dba       759 Mar 12  2007 tcperrs
-r--r--r-- 1 ora10 dba     21197 Apr  6  2010 usrdecs.h
-r--r--r-- 1 ora10 dba      1476 Mar  9  2005 zlib.txt
2)设置环境变量
[ora10@dev160 gg10]$ 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 LANG=en_US:zh_CN.UTF-8
export LC_ALL=C
#export DISPLAY=192.168.161.200:0
export ORACLE_BASE=/ora10
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#export ORACLE_SID=ora10
export ORACLE_SID=crm20
export LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/jre/lib/i386/server:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:/home/ora10/gg10:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:/home/ora10/gg10:$PATH
export NLS_LANG=American_America.ZHS16GBK
3)初始化
GGSCI (ora10g) 4> create subdirs

Creating subdirectories under current directory /u01/ora10g/gg10

Parameter files                /u01/ora10g/gg10/dirprm: created
Report files                   /u01/ora10g/gg10/dirrpt: created
Checkpoint files               /u01/ora10g/gg10/dirchk: created
Process status files           /u01/ora10g/gg10/dirpcs: created
SQL script. files               /u01/ora10g/gg10/dirsql: created
Database definitions files     /u01/ora10g/gg10/dirdef: created
Extract data files             /u01/ora10g/gg10/dirdat: created
Temporary files                /u01/ora10g/gg10/dirtmp: created
Veridata files                 /u01/ora10g/gg10/dirver: created
Veridata Lock files            /u01/ora10g/gg10/dirver/lock: created
Veridata Out-Of-Sync files     /u01/ora10g/gg10/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/ora10g/gg10/dirver/oosxml: created
Veridata Parameter files       /u01/ora10g/gg10/dirver/params: created
Veridata Report files          /u01/ora10g/gg10/dirver/report: created
Veridata Status files          /u01/ora10g/gg10/dirver/status: created
Veridata Trace files           /u01/ora10g/gg10/dirver/trace: created
Stdout files                   /u01/ora10g/gg10/dirout: created

3)测试
[ora10@dev160 gg10]$ ggsci

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

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

 

GGSCI (dev160) 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, SEND, START, STATS, STATUS, STOP
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
CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO

Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD
(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 (dev160) 2> exit
2、创建管理用户及赋权:
create user GGUSER
IDENTIFIED by GGUSER
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on USERS;
-- Grant/Revoke role privileges
grant connect to GGUSER;
grant resource to GGUSER;
-- Grant/Revoke system privileges
grant unlimited tablespace to GGUSER;
3、由于GG需要关闭10G的回收站机制,因此进行如下操作:
SQL> alter system set recyclebin=off;
System altered.
4、由于GG需要开启数据库的归档,因此需要进行如下操作:
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Current log sequence           42
SQL> alter database close
  2  ;

Database altered.

SQL> alter database archive;
alter database archive
                     *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> alter database archivelog;

Database altered.

SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  222298112 bytes
Fixed Size                  1266680 bytes
Variable Size             134220808 bytes
Database Buffers           79691776 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

5、安装同步DLL语句要用到的GoldenGate脚本
1)执行marker_setup脚本,该脚本用以建立一个DDL标记表
SQL> @/ora10/gg10/marker_setup(此处文件位置以具体各主机地址为准)
[ora10@dic5f88 gg10]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 7 08:35:10 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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

SQL> @/ora10/gg10/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:gguser


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

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script. complete.

注:由于在一台机器上进行,因此在完成一个实例的某一步聚的安装后,需要退出sqlplus,以防sqlplus将访问过的文件锁住。
2)以INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件
SQL> @/u01/ora10g/gg10/ddl_setup;
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.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:gguser

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

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

Analyzing installation status...


STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script. complete.
3)role_setup脚本用以建立GGS_GGSUSER_ROLE角色
SQL> @/u01/ora10g/gg10/role_setup

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:gguser
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)按上一步的提示,将权限赋给在 Extract, GGSCI, and Manager processes中指定的用户gguser
SQL> grant GGS_GGSUSER_ROLE gguser;
grant GGS_GGSUSER_ROLE gguser
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant GGS_GGSUSER_ROLE to gguser;

Grant succeeded.
5)ddl_enable.sql将正式enable ddl捕获触发器,即:ALTER TRIGGER sys .&ddl_trigger_name ENABLE
SQL>  @/u01/ora10g/gg10/ddl_enable;

Trigger altered.
6)执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包,之后需要用到

SQL> @?/rdbms/admin/dbmspool  

Package created.


Grant succeeded.


View created.


Package body created.
7)ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中,以保证这些对象不要reload,提升性能
SQL> @/u01/ora10g/gg10/ddl_pin
Enter value for 1: gguser

PL/SQL procedure successfully completed.

Enter value for 1: gguser

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

以上DDL支持包对象都已经安装好,接下来就是配置manager,replicat和extract
6、manager的配置
GGSCI (dic5f88) 6> view param mgr
port 7908
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 60
DYNAMICPORTLIST 7940-8100,7930
SOURCEDB gzcrm20,USERID gguser,PASSWD gguser(这一行参数有问题,其它没问题)
PURGEOLDEXTRACTS /ora10/ggtrails/local/*,USECHECKPOINTS,MINKEEPDAYS 2
此处sourcedb只有在异构数据库的情况下才能使用,对于同构ORACLE数据库,不需要使用

GGSCI (ora10g) 4> view params mgr 

port 7908
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 5,RESETMINUTES 60
DYNAMICPORTLIST 7940-8100,7930
USERID system@localhost:1521/ggtarge PASSWORD oracle
PURGEOLDEXTRACTS /u01/ora10g/ggtrail/local/*,USECHECKPOINTS,MINKEEPDAYS 2
PURGEOLDEXTRACTS /u01/ora10g/ggtrail/remote/*,USECHECKPOINTS,MINKEEPDAYS 2


GGSCI (ora10g) 2> start manager

Manager started.


GGSCI (ora10g) 3> status manager

Manager is running (IP port ora10g.7908).


以下配置,是针对source数据库的
7、配置extract
1)在数据库级打开 追加日志 功能
SQL>  alter database add supplemental log data;

Database altered.

SQL> alter system switch log;
alter system switch log
                    *
ERROR at line 1:
ORA-01900: LOGFILE keyword expected


SQL> alter system switch logfile;

System altered.
2)增加extract组
GGSCI (ora10g) 5> ADD EXTRACT emp_ext1, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (ora10g) 6> status extract;
ERROR: Invalid command.

GGSCI (ora10g) 7> status extract
ERROR: You must specify a group name.

GGSCI (ora10g) 8> status extract emp_ext1
EXTRACT EMP_EXT1: STOPPED
3)接下来,我们要添加一个 RMTTRAIL 或 EXTTRAIL。前者用于远程计算机,如果您在同一台本地计算机上建立复制,则建议使用后者。我们将使用 EXTTRAIL,通过以下命令来创建它
GGSCI (ora10g) 9> add exttrail /u01/ora10g/ggtrail/local/ex,megabytes 10,extract emp_ext1
EXTTRAIL added.

4)为 Extract 组 emp_ext1创建参数文件
extract emp_ext1
USERID system@192.168.0.88:1521/orcl, PASSWORD tqtwfgwg
EXTTRAIL /u01/ora10g/ggtrail/local/ex
DDL INCLUDE MAPPED
Table test.*;

5)启动extract
GGSCI (ora10g) 35> start extract emp_ext1

Sending START request to MANAGER ...
EXTRACT EMP_EXT1 starting


GGSCI (ora10g) 36> info extract emp_ext1 detail

EXTRACT    EMP_EXT1  Initialized   2010-12-07 18:39   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:37:08 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2010-12-07 18:39:44  Seqno 0, RBA 0

  Target Extract Trails:

  Remote Trail Name                                Seqno        RBA     Max MB

  /u01/ora10g/ggtrail/local/ex                         0          0         10

  Extract Source                          Begin             End            

  Not Available                           * Initialized *   2010-12-07 18:39


Current directory    /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_EXT1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_ext1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_EXT1.cpe
Process file         /u01/ora10g/gg10/dirpcs/EMP_EXT1.pce
Stdout file          /u01/ora10g/gg10/dirout/EMP_EXT1.out
Error log            /u01/ora10g/gg10/ggserr.log


GGSCI (ora10g) 37> status extract exp_ext1
ERROR: EXTRACT does not exist.


GGSCI (ora10g) 38> status extract emp_ext1
EXTRACT EMP_EXT1: STOPPED

6)查看日志,诊断错误
tail -100f EMP_EXT1.rpt
...
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.

2010-12-07 19:16:41  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2010-12-07 19:16:41  INFO    OGG-00733  Marker table SYSTEM.GGS_MARKER not found.

Source Context :
  SourceModule            : [er.redo.ora]
  SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34090]/perforce/src/app/er/redo/oracle/redoora.c]
  SourceFunction          : [REDO_validate_config]
  SourceLine              : [3711]
  ThreadBacktrace         : [8] elements
                          : [/u01/ora10g/gg10/extract(CMessageContext::AddThreadContext()+0x26) [0x840b3f6]]
                          : [/u01/ora10g/gg10/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x817) [0x8401b57]]
                          : [/u01/ora10g/gg10/extract(_MSG_ERR_DDL_SUPPORT_TABLE_MISSING(CSourceContext*, DBString<777> const&, CMessageFactory::MessageDisposition)+0x81) [0x83b1e11]]
                          : [/u01/ora10g/gg10/extract(REDO_validate_config(int, unsigned int*, int*)+0x81b) [0x82fbf9b]]
                          : [/u01/ora10g/gg10/extract [0x81775da]]
                          : [/u01/ora10g/gg10/extract(main+0x1648) [0x819e2e8]]
                          : [/lib/libc.so.6(__libc_start_main+0xdc) [0x676ce9c]]
                          : [/u01/ora10g/gg10/extract(__gxx_personality_v0+0x1c5) [0x8114ac1]]

2010-12-07 19:16:41  ERROR   OGG-00529  DDL Replication is enabled but table SYSTEM.GGS_DDL_HIST is not found. Please check DDL installation in the database.

2010-12-07 19:16:41  ERROR   OGG-01668  PROCESS ABENDING.

从结果来看,是这几个对象不存在所到,是不是真不存在呢?我们到dba_objects里去查看一下:
-bash-3.2$ echo $ORACLE_SID
orcl
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 2 19:25:49 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col object_name for a40   
SQL> col owner for a20
SQL> select owner,object_name,object_type from dba_objects where object_name in('GGS_MARKER','GGS_DDL_HIST');

OWNER                OBJECT_NAME
-------------------- ----------------------------------------
OBJECT_TYPE
--------------------------------------
GGUSER               GGS_MARKER
TABLE

GGUSER               GGS_DDL_HIST
TABLE

结果已经很明确显示,对象是存在的,只是用户不是system下,所以,刚刚在文件中配置的userid是有问题的。
另外,它诊断数据库的归档目录没有指定,这个最好还是指定一下
因此,可做如下处理:
A、指定归档目录:
SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
orcl

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  222298112 bytes
Fixed Size                  1266680 bytes
Variable Size             134220808 bytes
Database Buffers           79691776 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/ora10g/oradata/archive' scope=both;    

System altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/ora10g/oradata/archive
Oldest online log sequence     42
Next log sequence to archive   44
Current log sequence           44
SQL>
B)、调整emp_ext1参数
GGSCI (ora10g) 39> edit params emp_ext1


extract emp_ext1
USERID gguser@192.168.0.88:1521/orcl, PASSWORD gguser
EXTTRAIL /u01/ora10g/ggtrail/local/ex
DDL INCLUDE MAPPED
Table test.*;
C)、重新再调起extract进程

GGSCI (ora10g) 42> info extract emp_ext1 detail

EXTRACT    EMP_EXT1  Last Started 2010-12-07 19:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2010-12-07 19:41:54  Seqno 44, RBA 2578944

  Target Extract Trails:

  Remote Trail Name                                Seqno        RBA     Max MB

  /u01/ora10g/ggtrail/local/ex                         0        922         10

  Extract Source                          Begin             End            

  /u01/ora10g/oradata/orcl/redo01.log     2010-12-07 18:39  2010-12-07 19:41
  Not Available                           * Initialized *   2010-12-07 18:39


Current directory    /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_EXT1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_ext1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_EXT1.cpe
Process file         /u01/ora10g/gg10/dirpcs/EMP_EXT1.pce
Stdout file          /u01/ora10g/gg10/dirout/EMP_EXT1.out
Error log            /u01/ora10g/gg10/ggserr.log


GGSCI (ora10g) 43> status extract emp_ext1
EXTRACT EMP_EXT1: RUNNING

OK,总算把extract进程搞定

以下配置,是针对target数据库的
8、配置replicat组的参数信息
1)在目标库上创建checkpointtable
知识补充:此表中保存的检查点指示 Replicat 进程的当前读写位置。这用于在进程需要重启时或者在服务器出现任何故障或网络停顿时防止数据丢失。如果没有这一机制,将导致数据丢失。另一个好处是,通过使用检查点,多个 Extract 或 Replicat 进程可通过相同的一组线索进行读取。
对于以批处理模式运行的 Extract 和 Replicat 进程,检查点不是必需的,因为这样的进程总是可以重启。然而,在 Extract 和 Replicat 进程连续工作的情况下,检查点是必需的。检查点通常保存在 dirchk 子目录的文件中,但是对于 Replicat,可以选择在数据库的检查点表中保存检查点。
GGSCI (ora10g) 27> DBLOGIN USERID system@192.168.0.88/ggtarge PASSWORD oracle
Successfully logged into database.

GGSCI (ora10g) 28> add checkpoint table gguser.rep_checkpoint
ERROR: Invalid command.

GGSCI (ora10g) 29> add checkpointtable gguser.rep_checkpoint

Successfully created checkpoint table GGUSER.REP_CHECKPOINT.
2)添加 Replicat 组
GGSCI (ora10g) 44> ADD REPLICAT emp_rep1, EXTTRAIL /u01/ora10g/ggtrail/local/ex, CHECKPOINTTABLE gguser.rep_checkpoint,begin now
REPLICAT added.
3)配置Replicat 组的参数文件
GGSCI (ora10g) 45> edit params emp_rep1
REPLICAT emp_rep
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP test.*, TARGET test.*;

4)启动Replicat 组

GGSCI (ora10g) 47> start replicat emp_rep1

Sending START request to MANAGER ...
REPLICAT EMP_REP1 starting


GGSCI (ora10g) 48> status emp_rep1
REPLICAT EMP_REP1: STOPPED


GGSCI (ora10g) 49> info extract emp_rep1 detail
ERROR: EXTRACT does not exist.


GGSCI (ora10g) 50> info replicat emp_rep1 detail

REPLICAT   EMP_REP1  Initialized   2010-12-07 19:56   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:07:45 ago)
Log Read Checkpoint  File /u01/ora10g/ggtrail/local/ex000000
                     2010-12-07 19:56:10.000000

  Extract Source                          Begin             End            

  /u01/ora10g/ggtrail/local/ex000000      * Initialized *   2010-12-07 19:56


Current directory    /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_rep1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_REP1.cpr
Checkpoint table     gguser.rep_checkpoint
Process file         /u01/ora10g/gg10/dirpcs/EMP_REP1.pcr
Stdout file          /u01/ora10g/gg10/dirout/EMP_REP1.out
Error log            /u01/ora10g/gg10/ggserr.log

再次经历失败,悲剧

5)查看日志,定位问题
-bash-3.2$ tail -100f /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
...
2010-12-07 20:02:58  ERROR   OGG-00446  Could not open checkpoint file /u01/ora10g/gg10/dirchk/EMP_REP.cpr, mode 1 (error 2, No such file or directory).

2010-12-07 20:02:58  ERROR   OGG-01668  PROCESS ABENDING.

错误报得很奇怪,说是一个checkpoint file找不到,我不是建的表吗?怎么成了文件了。
最关键的是,我的进程不是emp_rep1吗,怎么是emp_rep.cpr了
进目录查看:
-bash-3.2$ cd /u01/ora10g/gg10/dirchk/
-bash-3.2$ ls -l
total 8
-rw-rw-r-- 1 oracle oinstall 4096 Dec  7 20:08 EMP_EXT1.cpe
-rw-rw-r-- 1 oracle oinstall 2048 Dec  7 19:56 EMP_REP1.cpr

果然发现不存在EMP_REP.cpr文件,但存在EMP_REP1.cpr文件

查看之前的配置文件,果然发现第一行有问题:
REPLICAT emp_rep
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP test.*, TARGET test.*;
该死的粗心
修改如下:
REPLICAT emp_rep1
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD ---reperror错误,指定在出现该错误时,复制进行的处理方式(默认为abended,可有ignore,exception,discard等其它操作)
REPERROR DEFAULT,DISCARD
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS   ----这个参数指定在进行DML操作时,两边的对象结果是完全一致的,一般在DML操作中省略两边表结果的检查
HANDLECOLLISIONS
MAP test.*, TARGET test.*;

重启服务:
GGSCI (ora10g) 2> start replicat emp_rep1

Sending START request to MANAGER ...
REPLICAT EMP_REP1 starting


GGSCI (ora10g) 3> status emp_rep1
REPLICAT EMP_REP1: RUNNING


GGSCI (ora10g) 4> info replication emp_rep1 detail
ERROR: Invalid command.

GGSCI (ora10g) 5> info emp_rep1 detail

REPLICAT   EMP_REP1  Last Started 2010-12-07 20:18   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  File /u01/ora10g/ggtrail/local/ex000000
                     2010-12-07 19:56:10.000000  RBA 922

  Extract Source                          Begin             End            

  /u01/ora10g/ggtrail/local/ex000000      2010-12-07 19:56  2010-12-07 19:56
  /u01/ora10g/ggtrail/local/ex000000      * Initialized *   2010-12-07 19:56


Current directory    /u01/ora10g/gg10

Report file          /u01/ora10g/gg10/dirrpt/EMP_REP1.rpt
Parameter file       /u01/ora10g/gg10/dirprm/emp_rep1.prm
Checkpoint file      /u01/ora10g/gg10/dirchk/EMP_REP1.cpr
Checkpoint table     GGUSER.REP_CHECKPOINT
Process file         /u01/ora10g/gg10/dirpcs/EMP_REP1.pcr
Stdout file          /u01/ora10g/gg10/dirout/EMP_REP1.out
Error log            /u01/ora10g/gg10/ggserr.log

成功启动,接下来的内容就是同步测试了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12350275/viewspace-688781/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    108846