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/，如需轉載，請註明出處，否則將追究法律責任。