在上一篇文件中的OGG单向复制配置不支持DLL的同步,只支持DML,因而本文在之前的基础上增加对DDL语句的复制,下面是简要配置过程记录!
一:验证之前的配置不支持DDL复制,这里在source端,新建一张表,发现无法复制到target端!target端也新建相同的表后,DML操作可以成功复制
- [root@db1 ~]# su - oracle
- [oracle@db1 ~]$ sqlplus hr/hr
- SQL> create table t2 (id number primary key,name varchar2(20));
- Table created.
- SQL> conn hr/hr@db2
- Connected.
- SQL> select tname from tab where tname='T2';
- no rows selected
- SQL> create table t2 (id number primary key,name varchar2(20));
- Table created.
- SQL> conn hr/hr
- Connected.
- SQL> insert into t2 values (1,'one');
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> conn hr/hr@db2
- Connected.
- SQL> select * from t2;
- ID NAME
- ---------- --------------------
- 1 one
二:开始配置OGG支持DDL复制(在source端操作)
1:赋予ogg用户相应的权限,修改全局配置文件添加ggschema参数
- SQL> conn /as sysdba
- Connected.
- SQL> grant execute on utl_file to ogg;
- Grant succeeded.
- [oracle@db1 ~]$ cd $GGATE
- [oracle@db1 ogg]$ ggsci
- GGSCI (db1) 1> edit param ./GLOBALS
- GGSCI (db1) 2> view param ./GLOBALS
- ggschema ogg
2:运行相关的sql脚本
- [oracle@db1 ~]$ cd $GGATE
- [oracle@db1 ogg]$ sqlplus /nolog
- SQL> conn /as sysdba
- Connected.
- SQL> @marker_setup.sql
- Marker setup script
- You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
- NOTE: The schema must be created prior to running this script.
- NOTE: Stop all DDL replication before starting this installation.
- Enter Oracle GoldenGate schema name:ogg
- Marker setup table script complete, running verification script...
- Please enter the name of a schema for the GoldenGate database objects:
- Setting schema name to OGG
- MARKER TABLE
- -------------------------------
- OK
- MARKER SEQUENCE
- -------------------------------
- OK
- Script complete.
- SQL> show parameter recyclebin;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- recyclebin string on
- SQL> alter system set recyclebin=off;
- System altered.
- SQL> show parameter recyclebin;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- recyclebin string OFF
- SQL> @ddl_setup.sql
- Oracle 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 Oracle 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 Oracle GoldenGate schema name:ogg
- Working, please wait ...
- Spooling to file ddl_setup_spool.txt
- Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
- Check complete.
- Using OGG as a Oracle GoldenGate schema name.
- 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
- ————————其他输出省略————————
- STATUS OF DDL REPLICATION
- ---------------------------------------------------------------------------------------
- SUCCESSFUL installation of DDL Replication software components
- Script complete.
- 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:ogg
- 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 <loggedUser>
- where <loggedUser> is the user assigned to the GoldenGate processes.
- SQL> grant ggs_ggsuser_role to ogg;
- Grant succeeded.
- SQL> @ddl_enable.sql
- Trigger altered.
- SQL> @?/rdbms/admin/dbmspool.sql
- Package created.
- Grant succeeded.
- View created.
- Package body created.
- SQL> @ddl_pin.sql ogg
- PL/SQL procedure successfully completed.
- PL/SQL procedure successfully completed.
- PL/SQL procedure successfully completed.
3:source端修改extract进程的params文件,添加"ddl include all"参数,重启extract进程
- GGSCI (db1) 1> view params eora_t1
- extract eora_t1
- setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
- ddl include all
- userid ogg,password ogg
- exttrail ./dirdat/aa
- table hr.*;
- GGSCI (db1) 2> stop extract eora_t1
- Sending STOP request to EXTRACT EORA_T1 ...
- Request processed.
- GGSCI (db1) 3> start extract eora_t1
- Sending START request to MANAGER ...
- EXTRACT EORA_T1 starting
- GGSCI (db1) 4> info extract eora_t1
- EXTRACT EORA_T1 Last Started 2012-06-20 15:42 Status RUNNING
- Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
- Log Read Checkpoint Oracle Redo Logs
- 2012-06-20 15:42:58 Seqno 3, RBA 50044416
- SCN 0.567478 (567478)
4:target端修改replicat进程的params文件,添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数,重启replicat进程
- [root@db2 ~]# su - oracle
- [oracle@db2 ~]$ cd $GGATE
- [oracle@db2 ogg]$ ggsci
- GGSCI (db2) 1> edit params rora_t1
- GGSCI (db2) 2> view params rora_t1
- replicat rora_t1
- setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
- ddl include all
- ddlerror default ignore retryop maxretries 3 retrydelay 5
- userid ogg,password ogg
- handlecollisions
- assumetargetdefs
- discardfile ./dirrpt/rora_t1.dsc,purge
- map hr.* ,target hr.*;
- GGSCI (db2) 3> stop replicat rora_t1
- Sending STOP request to REPLICAT RORA_T1 ...
- Request processed.
- GGSCI (db2) 4> start replicat rora_t1
- Sending START request to MANAGER ...
- REPLICAT RORA_T1 starting
- GGSCI (db2) 5> info replicat rora_t1
- REPLICAT RORA_T1 Last Started 2012-06-20 15:50 Status RUNNING
- Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
- Log Read Checkpoint File ./dirdat/pa000000
- First Record RBA 4780973
三:测试
- [oracle@db1 ogg]$ sqlplus hr/hr
- SQL> alter table t2 add location varchar2(200);
- Table altered.
- SQL> conn hr/hr@db2
- Connected.
- SQL> desc t2
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- ID NOT NULL NUMBER
- NAME VARCHAR2(20)
- LOCATION VARCHAR2(200)
- SQL> conn hr/hr
- Connected.
- SQL> create table t3 as select object_id,object_name from dba_objects;
- Table created.
- SQL> conn hr/hr@db2
- Connected.
- SQL> select tname from tab where tname='T3';
- TNAME
- ------------------------------
- T3
- SQL> desc t3;
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- OBJECT_ID NUMBER
- OBJECT_NAME VARCHAR2(128)
本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/904373如需转载请自行联系原作者
ylw6006