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 <loggedUser>
where <loggedUser> 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單機安裝與配置,並實驗單向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 <loggedUser>
where <loggedUser> 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複製!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2146613/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OGG雙向條件複製的部署與測試
- poi操作excel，複製sheet，複製行，複製單元格，複製styleExcel
- ogg 併發複製程式自阻塞
- Oracle 19C OGG基礎運維-05DDL操作同步Oracle運維
- OGG classic模式maxtransops引數提升複製效率模式
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- PostgreSQL雙向複製教程SQL
- ogg複製程式報ORA-01438錯誤處理
- MySQL DDL操作表MySql
- OGG DDL觸發器引發的故障系列（一）觸發器
- OGG複製程式延遲高，優化方法一(使用索引)優化索引
- OGG同步複製時與相容觸發器解決方法觸發器
- SharePlex qview工具 vs OGG logdump工具探究兩個複製工具事務開始 or 事務提交複製？View
- 如何在不相容的DDL命令後修復MySQL複製MySql
- OGG複製同步,提示欄位長度不夠ORA-01704
- Oracle 19C OGG基礎運維-06增加複製表Oracle運維
- python 複製以及更改列表操作Python
- 前端er怎樣操作剪下複製以及禁止複製+破解等前端
- MySQL 傳統複製與 GTID 複製原理及操作詳解MySql
- 使用事件溯源、Kafka和OGG從Oracle內部複製資料事件KafkaOracle
- Oracle 19C OGG基礎運維-07減少複製表Oracle運維
- 整明白 Golang slice 宣告方式、淺複製現象、深複製、append操作GolangAPP
- ORA-12801 AND ORA-01031 ONLY WHEN OGG DDL TRIGGER IS ENABLED [ID 1280235.1]
- 04 MySQL 表的基本操作-DDLMySql
- DDL、DML、DCL、DQL相關操作
- 資料庫操作語言DDL資料庫
- OGG 簡單DML同步
- [20181213]ogg大量讀取操作.txt
- 淺複製和深複製的概念與值複製和指標複製（引用複製）有關 淺複製 “指標複製 深複製 值複製指標
- 剪貼簿複製貼上操作彙總
- 前端模擬使用者的複製操作前端
- 簡單搭建MySQL主從複製MySql
- MySQL5.7 InnoDB線上DDL操作MySql
- MySQL的DDL和DML操作語法MySql
- 記錄一次 Online DDL 操作
- Java引用複製、淺複製、深複製Java
- JS物件複製：深複製和淺複製JS物件
- 複製和引用複製