OGG DDL 双向同步
来源:互联网 发布:三国战纪神剑进阶数据 编辑:程序博客网 时间:2024/05/23 01:57
1. 环境说明。
续单向DDL同步。 再把目标端的DDL也给开启来。
2. 确认源库处于归档模式。
SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/archivelogOldest online log sequence 3Next log sequence to archive 5Current log sequence 5
3. 确认源库为FORCE_LOGGIN。
SQL> alter database force logging;Database altered.SQL> select force_logging from v$database;FOR---YES
4. 确认源库打开辅助日志。
SQL> select supplemental_log_data_min from v$database;SUPPLEME--------YES
5. 源库关闭回收站。
SQL> show parameter recyclebinNAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string onSQL> alter system set recyclebin=off;System altered.SQL> show parameter recyclebinNAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string OFF
6. 保证字符集一致。
7. UNDO设置
SQL> alter system set undo_retention=86400;System altered.
8. 安装DDL_OBJETS
SQL> @marker_setupMarker setup scriptYou 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:goldengateMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.执行DDL_STEPSQL> @ddl_setupOracle GoldenGate DDL Replication setup scriptVerifying 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:goldengateWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking for sessions that are holding locks on Oracle Golden Gate metadata tables ...Check complete. Using GOLDENGATE 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:yesDDL replication setup script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATECLEAR_TRACE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsCREATE_TRACE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsTRACE_PUT_LINE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsINITIAL_SETUP STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDLREPLICATION PACKAGE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDL IGNORE TABLE-----------------------------------OKDDL IGNORE LOG TABLE-----------------------------------OKDDLAUX PACKAGE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDLAUX PACKAGE BODY STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsSYS.DDLCTXINFO PACKAGE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsSYS.DDLCTXINFO PACKAGE BODY STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDL HISTORY TABLE-----------------------------------OKDDL HISTORY TABLE(1)-----------------------------------OKDDL DUMP TABLES-----------------------------------OKDDL DUMP COLUMNS-----------------------------------OKDDL DUMP LOG GROUPS-----------------------------------OKDDL DUMP PARTITIONS-----------------------------------OKDDL DUMP PRIMARY KEYS-----------------------------------OKDDL SEQUENCE-----------------------------------OKGGS_TEMP_COLS-----------------------------------OKGGS_TEMP_UK-----------------------------------OKDDL TRIGGER CODE STATUS:Line/pos Error---------------------------------------- -----------------------------------------------------------------No errors No errorsDDL TRIGGER INSTALL STATUS-----------------------------------OKDDL TRIGGER RUNNING STATUS------------------------------------------------------------------------------------------------------------------------ENABLEDSTAYMETADATA IN TRIGGER------------------------------------------------------------------------------------------------------------------------OFFDDL TRIGGER SQL TRACING------------------------------------------------------------------------------------------------------------------------0DDL TRIGGER TRACE LEVEL------------------------------------------------------------------------------------------------------------------------0LOCATION OF DDL TRACE FILE------------------------------------------------------------------------------------------------------------------------/home/oracle/admin/gg1/udump/ggs_ddl_trace.logAnalyzing installation status...STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software componentsScript complete.执行ROLE_SETUPSQL> @role_setupGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo 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:goldengateWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant 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.将此角色给GOLDENGATE用户。SQL> grant ggs_ggsuser_role to goldengate;Grant succeeded.开启DDLSQL> @ddl_enableTrigger altered.安装性能优化包。SQL> @?/rdbms/admin/dbmspoolPackage created.Grant succeeded.View created.Package body created.ddl_pin将触发器用到的plsql包放进内存中SQL> @ddl_pin goldengatePL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
9. MGR配置
源端:
增加一行内容:
PURGEOLDEXTRACTS /home/oracle/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
目标端:
增加一行内容:
PURGEOLDEXTRACTS /home/oracle/gg/trails/w2*, USECHECKPOINTS, MINKEEPFILES 10
10. ./GLOBALS配置(目标端)
GGSCI (GOLDENGATE1) 2> edit params ./GLOBALSGGSCHEMA goldengateCHECKPOINTTABLE goldengate.chkpointGGSCI (GOLDENGATE1) 3> dblogin userid goldengate, password goldengateSuccessfully logged into database.GGSCI (GOLDENGATE1) 5> add checkpointtable goldengate.chkpointSuccessfully created checkpoint table goldengate.chkpoint.
11. 增加EXTRACT
add extract w2ext, tranlog, begin now
修改配置:
GGSCI (GOLDENGATE2) 4> edit params w2extEXTRACT w2extUSERID goldengate, PASSWORD goldengateTRANLOGOPTIONS EXCLUDEUSER goldengateIGNOREREPLICATESEXTTRAIL /home/oracle/gg/trails/w2DISCARDFILE w1extdsc,APPEND,MEGABYTES 5TRANLOGOPTIONS ALTARCHIVELOGDEST /home/oracle/archivelogTRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbfDDL INCLUDE MAPPEDDDLOPTIONS ADDTRANDATAFETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORTSTATOPTIONS REPORTFETCHWARNLONGTRANS 1H, CHECKINTERVAL 5MTABLE hr.*;
添加该extract对应的trail文件,用来存储抽取的数据。单个文件大小设置为100MB
GGSCI (GOLDENGATE2) 5> add exttrail /home/oracle/gg/trails/w2,extract w2ext, megabytes 100EXTTRAIL added.
12. 修改原源库EXTRAIL参数,增加以下两行:
TRANLOGOPTIONS EXCLUDEUSER goldengateIGNOREREPLICATES
13. 增加PUMP
add extract w2extdp, exttrailsource /home/oracle/gg/trails/w2, begin now
修改配置:
GGSCI (GOLDENGATE2) 7> edit params w2extdpEXTRACT w2extdpUSERID goldengate, PASSWORD goldengateRMTHOST 192.168.102.156, MGRPORT 5898RMTTRAIL /home/oracle/gg/trails/w2DISCARDFILE w1extdpdsc,APPEND,MEGABYTES 5TABLE hr.*;
14. 添加远程TRAIL文件:
GGSCI (GOLDENGATE2) 8> add rmttrail /home/oracle/gg/trails/w2, extract w2extdp, megabytes 100RMTTRAIL added.
15. 增加目标端REP进程
GGSCI (GOLDENGATE1) 11> add replicat w2rep, exttrail /home/oracle/gg/trails/w2, checkpointtable goldengate.chkpointREPLICAT added.
修改配置参数:
GGSCI (GOLDENGATE1) 12> edit params w2repREPLICAT w2repASSUMETARGETDEFSUSERID goldengate, PASSWORD goldengateDISCARDFILE w2repdsc,APPEND,MEGABYTES 5DDL INCLUDE MAPPEDDDLOPTIONS REPORTBATCHSQLDBOPTIONS DEFERREFCONSTDBOPTIONS LOBWRITESIZE 102400DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20MAP hr.* , TARGET hr.* ;
16. 在源端增加传输表。
GGSCI (GOLDENGATE2) 12> dblogin userid goldengate, password goldengateSuccessfully logged into database.GGSCI (GOLDENGATE2) 13> add trandata hr.*Logging of supplemental redo data enabled for table HR.COUNTRIES.Logging of supplemental redo data enabled for table HR.DEPARTMENTS.Logging of supplemental redo data enabled for table HR.EMPLOYEES.Logging of supplemental redo data enabled for table HR.JOBS.Logging of supplemental redo data enabled for table HR.JOB_HISTORY.Logging of supplemental redo data enabled for table HR.LOCATIONS.Logging of supplemental redo data enabled for table HR.REGIONS.2012-09-21 02:55:40 WARNING OGG-00869 No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table HR.T.2012-09-21 02:55:41 WARNING OGG-00869 No unique key is defined for table 'TT'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table HR.TT.
17. 查源库SCN。
SQL> select current_scn from v$database;CURRENT_SCN----------- 525516
18. 启动GG各进程。
注意启动REP进程时:
GGSCI (GOLDENGATE1) 19> start w2rep, aftercsn 525516Sending START request to MANAGER ...REPLICAT W2REP startingGGSCI (GOLDENGATE1) 20> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING W1EXT 00:00:00 00:00:08 EXTRACT RUNNING W1EXTDP 00:00:00 00:00:01 REPLICAT RUNNING W2REP 00:00:00 00:00:00
19, 测试。
源到目标:
SQL> create table t1(id number(9));Table created.SQL> insert into t1(id) values(88);1 row created.SQL> commit;Commit complete.SQL> select * from t1; ID---------- 88
目标到源;
SQL> create table t2(id number(9));Table created.SQL> insert into t2(id) values(88);1 row created.SQL> commit;Commit complete.SQL> select * from t2; ID---------- 88
- OGG DDL 双向同步
- OGG oracle DDL双向复制
- ogg 双向复制和ddl复制
- ogg双向复制,支持DML、DDL
- RAC-RAC双向DDL同步
- 两台ORACLE之间配置OGG-未配置同步DDL
- ogg单向DDL复制
- OGG DDL 操作详解
- sqlserver ogg 手动ddl
- ogg之ddl配置
- OGG Configuring DDL Support
- OGG单向复制(支持DDL)
- 【OGG】OGG简单配置双向复制(三)
- GoldenGate DDL双向复制
- OGG同步序列配置
- ogg同步表
- OGG同步数据方法
- DG备库-OGG 同步
- hdu 1272 小希的迷宫
- 使用FOR XML AUTO控制XML输出
- poj 2398 Toy Storage(计算几何,点跟直线的位置关系)
- V$SQL视图中SQL_TEXT的SQL语句是绑定变量,常量在v$sql_bind_capture视图的VLUE_STRING字段中可以查到
- JVM调优(九)-新一代的垃圾回收算法
- OGG DDL 双向同步
- boost------asio库的使用2(Boost程序库完全开发指南)读书笔记
- 在Django中使用最新版tinymce
- IOSOpenDev~抛弃数据线,用无线路IFILE上传文件
- 解析Excel,通过Excel驱动测试数据参数化
- JVM调优(十)-调优方法
- myEclipse 安装ibatis插件
- ARM CPSR+SPSR
- 区分Activity的四种加载模式