【OGG】OGG的单向复制配置-支持DDL(二)
【OGG】OGG的單向復制配置-支持DDL(二)
一.1 ?BLOG文檔結構圖
?
?
?
一.2 ?前言部分
?
一.2.1 ?導讀
各位技術愛好者，看完本文后，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① OGG的單向DDL實時復制功能
?
?
注意：本篇BLOG中代碼部分需要特別關注的地方我都用黃色背景和紅色字體來表示，比如下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方。
? List of Archived Logs in backup set 11
? Thrd Seq???? Low SCN??? Low Time??????????? Next SCN?? Next Time
? ---- ------- ---------- ------------------- ---------- ---------
? 1??? 32????? 1621589??? 2015-05-29 11:09:52 1625242??? 2015-05-29 11:15:48
? 1??? 33????? 1625242??? 2015-05-29 11:15:48 1625293??? 2015-05-29 11:15:58
? 2??? 42????? 1613951??? 2015-05-29 10:41:18 1625245??? 2015-05-29 11:15:49
? 2??? 43????? 1625245??? 2015-05-29 11:15:49 1625253??? 2015-05-29 11:15:53
?
?
?
?
?
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
一.2.2 ?實驗環境介紹
?
| 項目 | source db | target? db |
| db 類型 | 單實例 | 單實例 |
| db version | 11.2.0.3 | 11.2.0.3 |
| db 存儲 | FS type | FS type |
| ORACLE_SID | ogg1 | ogg2 |
| db_name | ogg1 | ogg2 |
| 主機IP地址： | 192.168.59.129 | 192.168.59.130 |
| OS版本及kernel版本 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
| OGG版本 | 11.2.1.0.1 64位 | 11.2.1.0.1 64位 |
| OS hostname | orcltest | rhel6_lhr |
?
?
?
一.2.3 ?相關參考文章鏈接
?
【OGG】OGG的下載和安裝篇：http://blog.itpub.net/26736162/viewspace-1693241/
【OGG】OGG的單向DML復制配置(一):http://blog.itpub.net/26736162/viewspace-1696020/
?
?
?
一.2.4 ?本文簡介
?
?
本文基于OGG的DML復制功能，加上DDL復制功能，后續會推出系列的OGG配置，包括雙向復制，還有rac到單實例的復制等等，主要參考網址為：http://ylw6006.blog.51cto.com/all/470441/16 ，非常感謝斬月大師。
?
?
?
?
?
一.3 ?實驗部分
?
一.3.1 ?實驗目標
?
配置2臺服務器，搭建OGG,實現hr用戶下的數據ddl和DML復制功能。
?
?
?
一.3.2 ?先驗證之前的配置不支持DDL復制
?
驗證之前的配置不支持DDL復制，這里在source端，新建一張表，發現無法復制到target端！target端也新建相同的表后，DML操作可以成功復制。
?
[oracle@orcltest ~]$ sqlplus hr/hr@ogg1
?
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:18:03 2015
?
Copyright (c) 1982, 2011, Oracle.? All rights reserved.
?
?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
?
SQL> create table t2 (id number primary key,name varchar2(20));?
?
Table created.
?
SQL> conn hr/hr@ogg2
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@ogg1
Connected.
SQL>? insert into t2 values (1,'one');?
?
1 row created.
?
SQL> commit;
?
Commit complete.
?
SQL> conn hr/hr@ogg2
Connected.
SQL> select * from t2;
?
??????? ID NAME
---------- --------------------
???????? 1 one
?
SQL>
?
此時source庫：
GGSCI (orcltest) 26> dblogin userid ggusr@ogg1,password lhr
Successfully logged into database.
?
GGSCI (orcltest) 29> info trandata hr.t2
?
Logging of supplemental redo log data is disabled for table HR.T2.
?
GGSCI (orcltest) 30>
?
?
?
一.3.3 ?開始配置OGG支持DDL復制(在source端操作)
?
?
?
一.3.3.1 ?賦予ggusr用戶相應的權限，修改全局配置文件添加ggschema參數
[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as? sysdba
?
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:27:05 2015
?
Copyright (c) 1982, 2011, Oracle.? All rights reserved.
?
?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
?
SQL>? grant execute on utl_file to ggusr;
?
Grant succeeded.
?
SQL>
?
?
[oracle@orcltest gg11]$ ggsci
?
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
?
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
?
?
?
GGSCI (orcltest) 1> edit param ./GLOBALS?
GGSCHEMA ggusr
~
~
~
~
。。。。。。。。。。。。。。。
~
~
~
~
"./GLOBALS" 1L, 15C written
?
?
GGSCI (orcltest) 2> view param ./GLOBALS
?
GGSCHEMA ggusr
?
?
GGSCI (orcltest) 3>
?
一.3.3.2 ?運行相關的sql腳本
如果想使用DDL功能，需要在之前運行支持DDL的相關腳本。
1.@marker_setup.sql
2.@ddl_setup.sql
3.@role_setup.sql
4.GRANT GGS_GGSUSER_ROLE TO gguser
5.@ddl_enable.sql
6.@?/rdbms/admin/dbmspool.sql?
7.@ddl_pin.sql ggusr
?
?
[oracle@orcltest gg11]$ sqlplus sys/lhr@ogg1 as? sysdba
?
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:30:45 2015
?
Copyright (c) 1982, 2011, Oracle.? All rights reserved.
?
?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
?
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:ggusr
?
?
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSR
?
MARKER TABLE
-------------------------------
OK
?
MARKER SEQUENCE
-------------------------------
OK
?
Script complete.
?
?
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:ggusr
?
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 GGUSR 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 GGUSR
?
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
----------------------------------------------------------------------
0
?
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/ogg1/ogg1/trace/ggs_ddl_trace.log
?
Analyzing installation status...
?
?
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:ggusr
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 ggusr;
?
Grant succeeded.
?
SQL> @ddl_enable.sql
?
Trigger altered.
?
SQL> @?/rdbms/admin/dbmspool.sql?
?
Package created.
?
?
Grant succeeded.
?
SQL>? @ddl_pin.sql ggusr
?
PL/SQL procedure successfully completed.
?
?
PL/SQL procedure successfully completed.
?
?
PL/SQL procedure successfully completed.
?
SQL>
?
/u02/app/oracle/diag/rdbms/ogg1/ogg1/trace/ggs_ddl_trace.log 為DDL語句的日志。
?
一.3.4 ?source端修改extract進程的params文件，添加"ddl include all"參數，重啟extract進程
?
GGSCI (orcltest) 1> info all
?
Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?
MANAGER???? RUNNING??????????????????????????????????????????
EXTRACT???? RUNNING???? EORA_HR???? 00:00:27????? 00:00:08???
EXTRACT???? RUNNING???? PORA_HR???? 00:00:00????? 00:00:02???
?
?
?
?
GGSCI (orcltest) 4> edit params eora_hr
extract eora_hr
setenv (ORACLE_SID=ogg1)
setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
ddl include all
userid ggusr,password lhr
exttrail ./dirdat/hr
table hr.*;
~
~
~
~
~
。。。。。。。。。。。。
~
~
~
~
~
~
~
~
"dirprm/eora_hr.prm" 8L, 229C written
?
?
GGSCI (orcltest) 5> view params eora_hr
?
extract eora_hr?
setenv (ORACLE_SID=ogg1)
setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)?
ddl include all
userid ggusr,password lhr
exttrail ./dirdat/hr?
table hr.*;
?
?
GGSCI (orcltest) 6> stop eora_hr
?
Sending STOP request to EXTRACT EORA_HR ...
Request processed.
?
?
GGSCI (orcltest) 7> start eora_hr
?
Sending START request to MANAGER ...
EXTRACT EORA_HR starting
?
?
GGSCI (orcltest) 8> info all
?
Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?
MANAGER???? RUNNING??????????????????????????????????????????
EXTRACT???? RUNNING???? EORA_HR???? 00:00:00????? 00:00:07???
EXTRACT???? RUNNING???? PORA_HR???? 00:00:00????? 00:00:09???
?
?
GGSCI (orcltest) 9>
GGSCI (orcltest) 9> view report EORA_HR
?
?
***********************************************************************
???????????????? Oracle GoldenGate Capture for Oracle
??? Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
?? Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16
?
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
?
?
??????????????????? Starting at 2015-06-10 16:41:04
***********************************************************************
?
Operating System Version:
Linux
Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64
Node: orcltest
Machine: x86_64
???????????????????????? soft limit?? hard limit
Address Space Size?? :??? unlimited??? unlimited
Heap Size??????????? :??? unlimited??? unlimited
File Size??????????? :??? unlimited??? unlimited
CPU Time???????????? :??? unlimited??? unlimited
?
Process id: 22734
?
Description:
?
***********************************************************************
**??????????? Running with the following parameters????????????????? **
***********************************************************************
?
2015-06-10 16:41:04? INFO??? OGG-03035? Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
extract eora_hr
setenv (ORACLE_SID=ogg1)
Set environment variable (ORACLE_SID=ogg1)
setenv (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)
Set environment variable (ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
ddl include all
userid ggusr,password ***
exttrail ./dirdat/hr
table hr.*;
?
2015-06-10 16:41:04? INFO??? OGG-01815? Virtual Memory Facilities for: BR
??? anon alloc: mmap(MAP_ANON)? anon free: munmap
??? file alloc: mmap(MAP_SHARED)? file free: munmap
??? target directories:
??? /u01/gg11/BR/EORA_HR.
?
Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR????? = /u01/gg11
?
2015-06-10 16:41:04? INFO??? OGG-01815? Virtual Memory Facilities for: COM
??? anon alloc: mmap(MAP_ANON)? anon free: munmap
??? file alloc: mmap(MAP_SHARED)? file free: munmap
??? target directories:
??? /u01/gg11/dirtmp.
?
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:?????????????????????????????? 64G
CACHEPAGEOUTSIZE (normal):??????????????? 8M
PROCESS VM AVAIL FROM OS (min):???????? 128G
CACHESIZEMAX (strict force to disk):???? 96G
?
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE??? 11.2.0.3.0????? Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
?
Database Language and Character Set:
NLS_LANG???????? = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE???? = "AMERICAN"
NLS_TERRITORY??? = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
?
2015-06-10 16:41:04? WARNING OGG-01423? No valid default archive log destination directory found for thread 1.
?
2015-06-10 16:41:04? INFO??? OGG-01513? Positioning to Sequence 8, RBA 4283920, SCN 0.1107430.
?
2015-06-10 16:41:04? INFO??? OGG-01516? Positioned to Sequence 8, RBA 4283920, SCN 0.1107430, Jun 10, 2015 4:40:34 PM.
?
2015-06-10 16:41:04? INFO??? OGG-01055? Recovery initialization completed for target file ./dirdat/hr000001, at RBA 1076.
?
2015-06-10 16:41:04? INFO??? OGG-01478? Output file ./dirdat/hr is using format RELEASE 11.2.
?
2015-06-10 16:41:04? INFO??? OGG-01026? Rolling over remote file ./dirdat/hr000001.
?
2015-06-10 16:41:04? INFO??? OGG-01053? Recovery completed for target file ./dirdat/hr000002, at RBA 1076.
?
2015-06-10 16:41:04? INFO??? OGG-01057? Recovery completed for all targets.
?
***********************************************************************
**???????????????????? Run Time Messages???????????????????????????? **
***********************************************************************
?
?
2015-06-10 16:41:04? INFO??? OGG-01517? Position of first record processed Sequence 8, RBA 4283920, SCN 0.1107430, Jun 10, 2015 4:40:34 PM.
?
?
GGSCI (orcltest) 10>
GGSCI (orcltest) 10> info extract eora_hr
?
EXTRACT??? EORA_HR?? Last Started 2015-06-10 16:41?? Status RUNNING
Checkpoint Lag?????? 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint? Oracle Redo Logs
???????????????????? 2015-06-10 16:42:16? Seqno 8, RBA 4302848
???????????????????? SCN 0.1107495 (1107495)
?
?
GGSCI (orcltest) 11>
?
?
?
一.3.5 ?target端修改replicat進程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 參數，重啟replicat進程
[oracle@rhel6_lhr gg11]$ ggsci
?
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
?
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
?
?
?
GGSCI (rhel6_lhr) 1> info all
?
Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?
MANAGER???? RUNNING??????????????????????????????????????????
REPLICAT??? RUNNING???? RORA_HR???? 00:00:00????? 00:00:07???
?
?
GGSCI (rhel6_lhr) 2> edit params rora_hr
?
?
?
GGSCI (rhel6_lhr) 3> view params rora_hr
?
replicat rora_hr
setenv (ORACLE_SID=ogg2)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
ddl include all?
ddlerror default ignore retryop maxretries 3 retrydelay 5
userid ggusr,password lhr?
handlecollisions?
assumetargetdefs?
discardfile ./dirrpt/rora_hr.dsc,purge?
map hr.* ,target hr.*;
?
?
GGSCI (rhel6_lhr) 4> stop rora_hr
?
Sending STOP request to REPLICAT RORA_HR ...
Request processed.
?
?
GGSCI (rhel6_lhr) 5> start rora_hr
?
Sending START request to MANAGER ...
REPLICAT RORA_HR starting
?
?
GGSCI (rhel6_lhr) 6> info all
?
Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?
MANAGER???? RUNNING??????????????????????????????????????????
REPLICAT??? RUNNING???? RORA_HR???? 00:00:00????? 00:00:06???
?
?
GGSCI (rhel6_lhr) 7>
?
GGSCI (rhel6_lhr) 7> info replicat rora_hr
?
REPLICAT?? RORA_HR?? Last Started 2015-06-10 02:39?? Status RUNNING
Checkpoint Lag?????? 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint? File ./dirdat/pa000000
???????????????????? First Record? RBA 7200161
?
?
GGSCI (rhel6_lhr) 8>
?
?
一.3.6 ?測試
?
[oracle@orcltest ~]$ sqlplus hr/hr@ogg1
?
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:47:12 2015
?
Copyright (c) 1982, 2011, Oracle.? All rights reserved.
?
?
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
?
SQL> alter table t2 add location varchar2(200);?
?
Table altered.
?
SQL> conn hr/hr@ogg2
Connected.
SQL> desc t2?
Name????????????????????????????????????? Null???? Type
----------------------------------------- -------- ----------------------------
ID??????????????????????????????????????? NOT NULL NUMBER
NAME?????????????????????????????????????????????? VARCHAR2(20)
LOCATION?????????????????????????????????????????? VARCHAR2(200)
?
SQL> conn hr/hr@ogg1
Connected.
SQL> create table t3 as select object_id,object_name from dba_objects;?
?
Table created.
?
SQL> select count(1) from t3;
?
? COUNT(1)
----------
???? 75262
?
?
SQL> conn hr/hr@ogg2
Connected.
SQL> select tname from tab where tname='T3';?
?
TNAME
------------------------------
T3
?
SQL> select count(1) from t3;
?
? COUNT(1)
----------
???? 75217
?
?
?
?
可以看到DDL語句已經執行了，我們看看DDL語句的日志：
SESS 380011-2015-06-10 16:47:19 : DDL : ************************* Start of log for DDL sequence [14], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T2]
SESS 380011-2015-06-10 16:47:19 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T2]
SESS 380011-2015-06-10 16:47:19 : DDL : DDL operation [alter table t2 add location varchar2(200) ], sequence [14], DDL type [ALTER] TABLE, real object type [TABLE], validity [VALID], object ID [76836], object [HR.T2], real object [HR.T2], base object schema [], base object name [], logged as [HR]
SESS 380011-2015-06-10 16:47:19 : DDL : Start SCN found [1107605]
SESS 380011-2015-06-10 16:47:20 : DDL : ------------------------- End of log for DDL sequence [14]
?
SESS 390002-2015-06-10 16:48:14 : DDL : ************************* Start of log for DDL sequence [501], v[ $Id: ddl_setup.sql /st_oggcore_11.2.1/8 2012/04/02 11:11:33 smijatov Exp $ ] trace level [0], owner schema of DDL package [GGUSR], objtype [TABLE] name [HR.T3]
SESS 390002-2015-06-10 16:48:14 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [TABLE] name [HR.T3]
SESS 390002-2015-06-10 16:48:14 : DDL : DDL operation [create table t3 as select object_id,object_name from dba_objects ], sequence [501], DDL type [CREATE] TABLE, real object type [TABLE], validity [], object ID [], object [HR.T3], real object [HR.T3], base object schema [], base object name [], logged as [HR]
SESS 390002-2015-06-10 16:48:14 : DDL : Start SCN found [1107994]
SESS 390002-2015-06-10 16:48:14 : DDL : ------------------------- End of log for DDL sequence [501]
?
?
?
一.3.7 ?實驗總結
?
DDL復制，我們可以從日志看出其執行的SQL語句，若DML配置熟悉的話，DDL配置就很簡單了。
?
?
一.4 ?About Me
?
...........................................................................................................................................................................................
本文作者：小麥苗，只專注于數據庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1696031/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w? 提取碼：af2d
QQ：642808185 若加QQ請注明你所正在讀的文章標題
創作時間地點：2015-06-10 09:00~ 2015-06-10 19:00 于外匯交易中心
<版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
?
?
轉載于:https://www.cnblogs.com/lhrbest/p/4569864.html
總結
以上是生活随笔為你收集整理的【OGG】OGG的单向复制配置-支持DDL(二)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： css Gradients（渐变）
- 下一篇： 【HDU】5256 系列转换（上涨时间最