GoldenGate單向複製配置(支援DDL複製)
在上一篇檔案中的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 session set recyclebin=off;
System altered.
SQL> alter system set recyclebin=off deferred;
System altered.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
1.回收站啟動和關閉
回收站預設是開啟的.不過我們可以透過引數recyclebin來關閉和開啟.
如果只是針對每個session可以透過alter session set recyclebin=off; 和 alter session set recyclebin=on;來關閉和開啟
如果是針對所有session可以透過修改系統引數recyclebin來實現,不過這裡要注意10g和11g有點不一樣,它們的引數型別不同
10g可以用:alter system set recyclebin=off; 和alter system set recyclebin=on;來關閉和開啟
11g可以用:alter system set recyclebin=off deferred; 和alter system set recyclebin=on deferred;來關閉和開啟
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
where 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斬月部落格“進行整理。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28389881/viewspace-1702283/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- goldengate配置DDL複製Go
- goldengate 單向複製配置Go
- goldengate單向複製的配置Go
- 1.--Goldgate單向複製(支援DDL)Go
- GoldenGate配置(一)之單向複製配置Go
- GoldenGate配置(三)之DDL複製配置Go
- Oracle goldengate 11g (二)【DML and DDL單向複製】OracleGo
- OGG單向DDL複製操作
- oracle goldengate 雙向複製配置OracleGo
- GoldenGate配置(二)之雙向複製配置Go
- Oracle 11g GoldenGate單向複製配置(DML)OracleGo
- 為Oracle配置DDL複製Oracle
- GoldenGate學習筆記(5)_配置例程之單向複製Go筆記
- 通過goldengate從ORACLE向mysql的單向複製GoOracleMySql
- 實戰goldengate之ora-To-ora單向複製Go
- Goldengate複製程式錯誤Fatal error executing DDLGoError
- 實戰goldengate:安裝配置+資料初始化+單向DML複製Go
- ogg12 oracle to oracle 雙向DDL複製Oracle
- mysql複製--主從複製配置MySql
- poi操作excel,複製sheet,複製行,複製單元格,複製styleExcel
- GoldenGate的複製原理Go
- 資料複製_GoldenGateGo
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- GoldenGate簡單複製環境的搭建Go
- oracle goldengate ddl 操作導致複製程式abended處理案例OracleGo
- oracle goldengate 雙活複製避免迴圈複製引數OracleGo
- MySQL 8 複製(五)——配置GTID複製MySql
- goldengate基於表複製Go
- GoldenGate多對一複製Go
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- Oracle9i高階複製(單向複製部分)實施完畢Oracle
- GoldenGate複製的幾個簡單測試Go
- goldengate 單向DDLGo
- postgresql 9.4 流複製簡單配置SQL
- MySQL 8 複製(九)——組複製聯機配置MySql
- 搭建一個Oracle到Oracle的GoldenGate單向複製測試環境OracleGo
- 使用goldengate的資料泵進行Oracle-Oracle的單向複製GoOracle
- 一次通過stream複製解決資料單向複製的案例