ogg单向x86ora10g到x64ora11g支持ddl同步实验

***********************ogg实验环境说明***********************

源端：            OS_NAME            db_version        OS_IP                字符集                                
oracle10gr2        ora10gr2        10.2.0.1.0        192.168.137.212        AMERICAN_AMERICA.WE8ISO8859P1

目标端：
oracle11gr2        OELx64            11.2.0.1.0        192.168.137.101        AMERICAN_AMERICA.WE8MSWIN1252

***********************ogg实验环境说明***********************
***********************ogg实验环境步骤***********************

******源端所有节点和目标端均执行以下步骤******
--建目录，解压文件
--源端2个节点创建相同结构目录
[oracle@OELx64 u01]$ cd app
[oracle@OELx64 app]$ ls
oracle  oraInventory
[oracle@OELx64 app]$ mkdir -p ogg/11.1
[oracle@OELx64 11.1]$ tar -xvf /tmp/ggs_Linux_x64_ora11g_64bit_v11_1_1_0_0_078.tar

--修改ogg用户的环境变量（此处的ogg用户为oracle）
--在.bash_profile中添加以下条目
#ogg set
OGG_BASE=/u01/app/ogg; export OGG_BASE
OGG_HOME=$OGG_BASE/11.1; export OGG_HOME
PATH=$OGG_HOME:$PATH; export PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:$LD_LIBRARY_PATH

--创建ogg工作目录
[oracle@ora10gr2 11.1]$ pwd
/u01/app/ogg/11.1
[oracle@ora10gr2 11.1]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 10g on Oct  4 2011 23:54:04

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



GGSCI (ora10gr2) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg/11.1

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


--创建数据库用户

SQL> select file_name from dba_data_files where rownum<10;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora10gr2/users01.dbf
/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
/u01/app/oracle/oradata/ora10gr2/system01.dbf
/u01/app/oracle/oradata/ora10gr2/test01.dbf

SQL> create tablespace ogg datafile '/u01/app/oracle/oradata/ora10gr2/ogg01.dbf' size 50m autoextend on;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace ogg quota unlimited on ogg temporary tablespace temp;

User created.

SQL> grant dba to ogg;

Grant succeeded.


******源端所有节点和目标端均执行以上步骤******


--在源端设置数据库日志补充模式
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.


--在源端设置归档模式
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
SQL> alter system set log_archive_dest='/u01/app/oracle/oradata/arch' scope=spfile;

System altered.

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

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             188747540 bytes
Database Buffers          415236096 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

--登陆源库
[oracle@ora10gr2 11.1]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x86, 32bit (optimized), Oracle 10g on Oct  4 2011 23:54:04

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



GGSCI (ora10gr2) 1> DBLOGIN USERID ogg,PASSWORD ogg;
ERROR: Failed to open data source for user OGG.

GGSCI (ora10gr2) 2> DBLOGIN USERID ogg
Password:
Successfully logged into database.


--在目标端添加checkpoint列表
[oracle@OELx64 11.1]$ 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

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



GGSCI (OELx64) 1> edit params ./GLOBALS


CHECKPOINTTABLE ogg.checkpoint
~
GGSCI (OELx64) 2> dblogin userid ogg
Password:
Successfully logged into database.

GGSCI (OELx64) 3> add checkpointtable ogg.checkpoint

Successfully created checkpoint table OGG.CHECKPOINT.
--2端配置mgr进程

GGSCI (ora10gr2) 3> delete params ../mgr
ERROR: Invalid command.

GGSCI (ora10gr2) 4> edit params mgr


port 7840
~
GGSCI (ora10gr2) 5> start mgr

Manager started.


GGSCI (ora10gr2) 6> info mgr

Manager is running (IP port ora10gr2.7840).

--源端配置extract进程
GGSCI (ora10gr2) 2> view params eora

extract eora
dynamicresolution
userid ogg,password ogg
exttrail /u01/app/ogg/11.1/dirdat/et
table test.*;

GGSCI (ora10gr2) 4> add extract eora,tranlog,begin now
EXTRACT added.


GGSCI (ora10gr2) 5> add exttrail /u01/app/ogg/11.1/dirdat/et,extract eora
EXTTRAIL added.

GGSCI (ora10gr2) 6> start extract eora

Sending START request to MANAGER ...
EXTRACT EORA starting


GGSCI (ora10gr2) 7> info extract eora

EXTRACT    EORA      Last Started 2013-01-01 22:02   Status RUNNING
Checkpoint Lag       00:01:18 (updated 00:00:07 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-01-01 22:00:47  Seqno 7, RBA 12490256
                    

--源端配置pump进程
GGSCI (ora10gr2) 9> view params pump_so

extract pump_so
dynamicresolution
passthru
rmthost 192.168.137.101,mgrport 7840,compress
rmttrail /u01/app/ogg/11.1/dirdat/pt
table test.*;

GGSCI (ora10gr2) 10> add extract pump_so,exttrailsource /u01/app/ogg/11.1/dirdat/et
EXTRACT added.


GGSCI (ora10gr2) 11> add rmttrail /u01/app/ogg/11.1/dirdat/pt,extract pump_so
RMTTRAIL added.

--目标端配置replicat进程
GGSCI (OELx64) 1> view params REP1

replicat rep1
userid ogg,password ogg   
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/rep1.dsc,append,megabytes 50
dynamicresolution
map test.test, target test.test;

GGSCI (OELx64) 4> add replicat rep1,exttrail /u01/app/ogg/11.1/dirdat/pt
REPLICAT added.


--源端信息
GGSCI (ora10gr2) 34> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EORA        00:00:00      00:00:02   
EXTRACT     RUNNING     PUMP_SO     00:00:00      00:00:23   

--目的端信息
GGSCI (OELx64) 8> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:03   



--支持DDL
1、在第两个节点执行执行DDL同步脚本命令：
先进入goldengate软件安装目录，以SYSDBA身份登录oracle执行以下脚本，执行脚本过程中，需要输入的用户全部是ogg，安装模式为INITIALSETUP，如果数据字典或者某些内部的包有错误，则需要运行catalog.sql和catproc.sql脚本。
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to ogg;
SQL>@ddl_enable
如果某项脚本执行错误，需要重新执行时，先要执行清除的脚本：ddl_remove.sql和marker_remove.sql

2、配置源端节点的extract，在配置文件中添加以下一行：
DDL INCLUDE OBJNAME "scott.*"
注意：先关闭eora_1进程，再添加，然后重新启动。
       
3、配置目标端REPLICAT进程参数文件，添加以下几行到配置文件中：
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
注意：先关闭rora_1进程，再添加，然后重新启动。

--测试
--测试DML
--源端（测试insert）
SQL> ho uname -a
Linux ora10gr2 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux

SQL> insert into test values(1,'yallonking');

1 row created.

SQL> commit;

Commit complete.
--目标端
SQL> ho uname -a
Linux OELx64 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

SQL> select * from test;

        ID NAME
---------- --------------------
         2 oraking
         2 oraking
         1 yallonking

3 rows selected.
--源端（测试update）
SQL> update test set id=id+1;

1 row updated.

SQL> commit;

Commit complete.

SQL> ho uname -a
Linux ora10gr2 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux
--目标端
SQL> select * from test;

        ID NAME
---------- --------------------
         2 oraking
         2 oraking
         2 yallonking

3 rows selected.

SQL> ho name -a
/bin/bash: name: command not found

SQL> ho uname -a
Linux OELx64 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
--源端（测试delete）
SQL> delete test where id=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> ho uname -a
Linux ora10gr2 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux
--目标端
SQL> ho uname -a
Linux OELx64 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

SQL> select * from test;

        ID NAME
---------- --------------------
         2 oraking
         2 oraking

2 rows selected.

--测试DDL
--源端建表
SQL> drop table test1;

Table dropped.

SQL> create table test1 as select * from test where 1=2;

Table created.

SQL> ho uname -a
Linux ora10gr2 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux
--目标端
SQL> drop table test1;

Table dropped.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST1
TEST

2 rows selected.

--至此完成。

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

转载于:http://blog.itpub.net/26143577/viewspace-752005/