资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

OGG单向DDL复制操作

实验目的：在两台虚机模拟实现简单的单向的DDL复制.

说明：因《OGG单机安装与配置,并实验单向DML复制操作》中已配置过单向的dml操作，所以本次配置内容在前一篇的基础之上。

环境解释：在《OGG单机安装与配置,并实验单向DML复制操作》中hostname：slient,db_name:test作为源库，而hostname:one,db_name:onemore作为目标库，本次只需要配置一次反向的操作即可：即slient为源端，  onemo为目标端.

实验步骤：

1.源端关闭回收站
注明：在oracle11g 中， recyclebin参数的 System Modifiable为DEFERRED，意思是要修改系统级的话，就要加deferred参数，对当前已经连接的sesion没有影响，但新连接的session将受到影响。(可以查询视图selectname,isses_modifiable,issys_modifiable from v$parameter wherename='recyclebin';)

SQL> set lines 200
SQL> col name for a30
SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin';

NAME                           ISSES ISSYS_MOD
------------------------------ ----- ---------
recyclebin                     TRUE  DEFERRED

SQL> alter system set recyclebin=off DEFERRED;   

System altered.

SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin';

NAME                           ISSES ISSYS_MOD
------------------------------ ----- ---------
recyclebin                     TRUE  DEFERRED

2.源端配置./GLOBALS（修改全局配置文件添加ggschema参数）
GGSCI (slient as ogg@test) 14> edit params ./GLOBALS  
ggschema ogg
~
"./GLOBALS" [New] 1L, 13C written

GGSCI (slient as ogg@test) 15>

GGSCI (slient as ogg@test) 16> view param ./GLOBALS  

ggschema ogg

GGSCI (slient as ogg@test) 17>

3.源库运行相关的sql脚本
[oracle@slient ogg_home]$ pwd
/opt/ogg/ogg_home
[oracle@slient ogg_home]$
[oracle@slient ogg_home]$ ls mark*
marker_remove.sql  marker_setup.sql  marker_status.sql
[oracle@slient ogg_home]$
[oracle@slient ogg_home]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 30 21:52:30 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

--执行marker_setup.sql脚本：
SQL> @/opt/ogg/ogg_home/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>

--执行ddl_setup.sql脚本
SQL> !ls ddl_setup*
ddl_setup.sql

SQL>@/opt/ogg/ogg_home/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 ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

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 IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO  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
----------------------------------------------------------------------
NONE

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

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

Script complete.
SQL>

--执行role_setup.sql脚本
SQL> !pwd
/opt/ogg/ogg_home

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
SP2-0606: Cannot create SPOOL file "role_setup_spool.txt"
SP2-0606: Cannot create STORE 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>

--根据上述提示执行授权：
SQL> GRANT GGS_GGSUSER_ROLE TO ogg;

Grant succeeded.

SQL>

--执行ddl_enable.sql 脚本：
SQL>  @ddl_enable.sql  

Trigger altered.

SQL>

--执行dbmspool.sql  
SQL> @?/rdbms/admin/dbmspool.sql  

Package created.


Grant succeeded.

--执行ddl_pin.sql
SQL> @ddl_pin.sql            
Enter value for 1: ogg

PL/SQL procedure successfully completed.

Enter value for 1: ogg

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>


4.源库修改extract进程的params文件，添加"ddl include all"参数，重启extract进程
GGSCI (slient as ogg@test) 19> edit param exta
EXTRACT exta
setenv (ORACLE_SID=test)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
ddl include all
exttrail /opt/ogg/ogg_home/dirdat/r1
dynamicresolution
TABLE scott.*;
~
"dirprm/exta.prm" 8L, 203C written


GGSCI (slient as ogg@test) 20>

--重启extract进程：
GGSCI (slient as ogg@test) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEA        00:00:00      00:00:05    
EXTRACT     RUNNING     EXTA        00:00:00      00:00:10    
REPLICAT    STOPPED     REP_REV     00:00:00      01:16:00    


GGSCI (slient as ogg@test) 21>

GGSCI (slient as ogg@test) 21>

GGSCI (slient as ogg@test) 21> stop exta

Sending STOP request to EXTRACT EXTA ...
Request processed.


GGSCI (slient as ogg@test) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEA        00:00:00      00:00:07    
EXTRACT     STOPPED    EXTA        00:00:00      00:00:20    
REPLICAT    STOPPED     REP_REV     00:00:00      01:16:32    

GGSCI (slient as ogg@test) 23> start EXTA

Sending START request to MANAGER ...
EXTRACT EXTA starting


GGSCI (slient as ogg@test) 24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEA        00:00:00      00:00:07    
EXTRACT     RUNNING    EXTA        00:00:00      00:00:03    
REPLICAT    STOPPED     REP_REV     00:00:00      01:16:42    


GGSCI (slient as ogg@test) 25> info extract EXTA

EXTRACT    EXTA      Last Started 2017-10-30 22:30   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Process ID           21542
Log Read Checkpoint  Oracle Redo Logs
                     2017-10-30 22:30:38  Thread 1, Seqno 336, RBA 7193088
                     SCN 0.21418576 (21418576)


GGSCI (slient as ogg@test) 26>  


5.目标库修改replicat进程的params文件，添加"ddl include all"和"ddlerrordefault ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程
GGSCI (one as ogg@onemo) 67>   edit param rep_demo
replicat rep_demo
setenv (oracle_sid=onemo)
setenv (nls_lang ="american_america.zhs16gbk")
userid ogg,password ogg
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
--report at 01:59
--reportrollover at 02:00
reperror default,abend
discardfile /u01/app/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
assumetargetdefs
--allownoopupdates
dynamicresolution
--insertallrecords
map scott.*,target scott.*;
~
~
"dirprm/rep_demo.prm" 15L, 431C written


GGSCI (one as ogg@onemo) 68>

GGSCI (one as ogg@onemo) 68> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DPE_REV     00:00:00      01:24:21    
EXTRACT     STOPPED     EXT_REV     00:00:00      01:24:27    
REPLICAT    STOPPED    REP_DEMO    00:00:00      00:04:09    

--重启replicat进程
GGSCI (one as ogg@onemo) 69> start REP_DEMO

Sending START request to MANAGER ...
REPLICAT REP_DEMO starting


GGSCI (one as ogg@onemo) 70> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DPE_REV     00:00:00      01:24:28    
EXTRACT     STOPPED     EXT_REV     00:00:00      01:24:34    
REPLICAT    RUNNING    REP_DEMO    00:00:00      00:00:01    


GGSCI (one as ogg@onemo) 71>
GGSCI (one as ogg@onemo) 71> info REPLICAT REP_DEMO

REPLICAT   REP_DEMO  Last Started 2017-10-27 08:45   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           11591
Log Read Checkpoint  File /u01/app/oracle/ogg/dirdat/ra000000000
                     2017-10-30 21:11:03.153747  RBA 5075


GGSCI (one as ogg@onemo) 72>


6.测试源端和目标端的数据
--先检查源库和目标库：
源库：
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TB_PT                          TABLE
TREE_VIEW                      VIEW

6 rows selected.

目标库：
SQL> conn scott/tiger;
Connected.
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL>

--开始测试：
源库：
SQL> create table t5(a int);

Table created.

SQL> insert into t5 values(111);  

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
T5                             TABLE
TB_PT                          TABLE
TEST                           TABLE
TREE_VIEW                      VIEW

8 rows selected.

SQL>

检查目标库：
SQL>  select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
T5                             TABLE
TEST                           TABLE

6 rows selected.

SQL> select * from t5;

         A
----------
       111

SQL>   

测试数据同步成功，实现了单向DDL复制!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


名称栏目：OGG单向DDL复制操作
分享网址：http://cdkjz.cn/article/ijhjej.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
买链接香港服务器空间成都微商城响应式网站设计方案广安网站建设大足网站建设企业邮箱wap网站建设成都柴油发电机组成都网站建设

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图