1.配置环境变量 export ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1 export GGATE=/oracle/ogg export PATH=$ORACLE_HOME/bin:$PATH:$GGATE export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ogg 2.修改数据位FORCELOGGING模式并启用追加日志 Enabling FORCELOGGING SELECT force_logging FROM v$database; Enable forced logging. ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SWITCH LOGFILE; Enabling schema-level supplemental logging alter database add supplemental log data; 3.创建OGG表空间及用户 create tablepace ogg datafile '/oracle/oradata/erp/ogg01.dbf' size 1000m; create user ogg identified by ogg default tablespace ogg; grant connect,resource,dba to ogg; 注意：turn off Recycebin 关闭回收站特性，10g必须，11g可选 4.创建OGG目录（OGG 12c在安装时已创建相关目录）并配置MGR GGSCI> CREATE SUBDIRS 配置MGR edit param mgr PORT 7809 AUTOSTART ER t* AUTORESTART ER t*, RETRIES 4, WAITMINUTES 4 STARTUPVALIDATIONDELAY 5 PURGEOLDEXTRACTS /u01/ogg/dirdat/lt*, USECHECKPOINTS, MINKEEPHOURS 2 5.为同步的表添加trandata日志 dblogin userid ogg,password ogg add trandata DDD.* 6.安装DDL sqlplus / as sysdba @marker_setup.sql @ddl_setup.sql @role_setup.sql grant GGS_GGSUSER_ROLE to ogg; @ddl_enable.sql 8.配置extract抽取组 add extract ext1, tranlog, begin now add exttrail /u01/ogg/trail/lt,extract ext1,megabytes 200(trail目录要手动创建) add extract dp,exttrailsource /u01/ogg/trail/lt add rmttrail /u01/ogg/trail/rt ,extract dp,megabytes 200 edit param ext1 extract ext1 dynamicresolution userid ogg,password ogg exttrail /u01/ogg/trail/lt WARNLONGTRANS 2h,CHECKINTERVAL 3m ddl include mapped DDLOPTIONS ADDTRANDATA table test.*; sequence test.*; ------------------- edit param dp extract dp dynamicresolution PASSTHRU rmthost 192.168.114.144, mgrport 7809,compress rmttrail /u01/ogg/trail/rt table test.*; sequence test.*; ~ ------------------- 目标端 1.创建OGG表空间及用户 create tablepace ogg datafile '/oracle/oradata/erp/ogg01.dbf' size 2000m; create user ogg identified by ogg default tablepace ogg; grant connect,resource,dba to ogg; 2.创建checkpoint表 dblogin userid ogg password ogg add checkpointtable ogg.ckpt 3.创建replicat组 add replicat rep1,exttrail /u01/ogg/trail/rt, checkpointtable ogg.ckpt GGSCI>edit param rep1 replicat rep1 userid ogg,password ogg assumetargetdefs reperror default,discard discardfile /u01/ogg/dirtmp/rep2.dsc,append, megabytes 50,purge dynamicresolution map test.*, target test.*; ----------------------- sequence 同步 配置 resource&target端执行 SQL>@sequence.sql （输入ogg schema） resource端 GRANT EXECUTE on ogg.updateSequence TO ogg; edit param ext1 添加SEQUENCE test.*; edit param dp 添加SEQUENCE test.*; target端 GRANT EXECUTE on ogg.replicateSequence TO ogg; ======================================================== The Integrated Capture GoldenGate Mode in Goldengate 11gr2 is one of the more interesting and useful feature released with this version. This capture process is the component responsible for extracting the DML transactional data and DDL’s from the source database redo log files. This data is then written to local trail files which eventually is move to the destination database to be applied there. Here are the list of topics to be covered in this article. ? What is the GoldenGate Integrated Capture Mode? ? Integrated Capture vs Classic Capture ? On-Source Capture ? Downstream Capture ? Prerequisites ? Configuration ? Monitoring/Views What is the GoldenGate Integrated Capture Mode? Integrated Capture Mode (IC) is a new form of the Extract process, were in this process is moved closer, inside the source database. In the traditional Classic extract process, the extract works on the redo logs outside the domain of the actual database. In this new integrated capture mode, a server Log Miner process is started which extracts all the DML data and DDLS statements creating Logical Change Records (LCR’s). These are then handed to the Goldengate memory processes which writes these LCR’s to the local trail files. This Log Miner server process is not the Log Miner utility we are used to in the database but is a similar mechanism which has been tuned and enhanced for specific use by the Goldengate processes. The purpose of moving this inside the database is to be able to make use of the already existing internal procedures in the database, making it easier to provide support for the newer features of Oracle faster than was previously possible. Due to this change, Oracle is now able to provide the following. ? Full Support of Basic, OLTP and EHCC compressed data. ? No need to fetch LOB’s from tables. ? Full Secure File support for Secure file lobs. ? Full XML support. ? Automatically handles addition of nodes and threads in RAC environment. ? Senses node up down in RAC and handles it in its processes transparently. Integrated Capture vs Classic Capture The Integrated Capture mode offers the following. ? Integrated with Database features ? Allows to mine earlier versions of integrated capture on secondary ? More efficient. It does not have to fetch data because of the datatype, etc.. ? No longer necessary to set this: Threads, ASMUSER, ASMBUF, DBLOGREADER, DECRYPASSWORD ? For RAC no additional manual steps required. Transparent with RAC. Integrated Capture Modes Integration capture supports two types of deployment configurations. They are: ? On-Source Capture ? Downstream Capture On-Source Capture When the integrated capture process is configured using the on-source capture mode, the capture process is started on the actual source database server itself. Changes as they happen on source database will be captured locally, routed, transformed and applied on target database in near real-time. This may seem convenient but consideration needs to be given to the additional workload that will be placed by this process on the database server. However if real-time replication is required this is the best option. Note: All features are supported in both On-Source or Downstream Deployment Downstream Capture In the downstream mode, the capture process is configured to run on a remote database host. All the database redo logs from the source database are shipped to this remote server using Dataguard technology and then mined there by the capture process. In this mode there is an inherent latency introduced due to the fact that the redo log on the source needs to switch first before the log can be shipped downstream. So there will be some delay in the replication of data to a target database as the extraction will be delayed due to the log switch. The main benefit of this setup however is the offset of the resource usage on the source server. In this mode, to overcome the log switch latency, Oracle has provide a near Real time capture using Standby redo logs for extraction. In this configuration the redo log from the source continuously writes into the standby redo logs of the downstream database. The capture process directly capture the data from here. It is important to keep in mind when deciding whether to use the Integrated capture or the classic capture mechanism that both configuration will remain available in future releases. However Oracle recommends to use the new Integrated capture mechanism as Oracle will not be adding new features to classic capture in the future and it will only be there for legacy support purposes. Prerequisites The database where integrated capture runs: ? Must be at least 11.2.0..3 ? Database patch 1411356.1 must be installed. In Downstream Configuration: ? DBID’s of ALL source databases must be unique. ? Downstream capture must have same OS/platform as source. ? Standby redo logs must be as large as the largest source redo logs. ? Only one database can have real time mining Configuration Add Extract On the source database the capture is created by first adding the capture parameter file xint1.prm in the dirprm directory. ladb01>cat xint1.prm EXTRACT XINT1 USERID gguser, PASSWORD gguser EXTTRAIL ./dirdat/xi TRANLOGOPTIONS EXCLUDEUSER gguser TABLE EQ2.*; Next add the capture as an integrated capture specifying the “INTEGRATED TRANLOG” option. ladb01> ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258 ... GGSCI (xgoldengate01) 2> ADD EXTRACT XINT1, INTEGRATED TRANLOG, BEGIN NOW EXTRACT added. GGSCI (xgoldengate01) 3> ADD EXTTRAIL ./dirdat/xn EXTRACT XINT1, megabytes 100 EXTTRAIL added. GGSCI (xgoldengate01) 4> start extract XINT1 Sending START request to MANAGER ... Here is the entries in the ggserror.log when the extract was created. 2013-04-25 17:18:54 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTRACT XINT1 INTEGRATED TRANLOG, BEGIN NOW. 2013-04-25 17:19:22 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): ADD EXTTRAIL ./dirdat/xn EXTRACT XINT1 megabytes 100. 2013-04-25 17:19:39 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract XINT1. 2013-04-25 17:19:39 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host ladb01.vst.com:35534 (START EXTRACT XINT1 ). 2013-04-25 17:19:40 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xint1.prm: EXTRACT XINT1 starting. 2013-04-25 17:19:40 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, xint1.prm: Operating system character set identified as US-ASCII. Locale: en_US_POSIX, LC_ALL: C. 2013-04-25 17:19:40 INFO OGG-03500 Oracle GoldenGate Capture for Oracle, xint1.prm: WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character set value of AL32UTF8. 2013-04-25 17:19:40 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, xint1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint. 2013-04-25 17:19:40 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, xint1.prm: Virtual Memory Facilities for: BR anon alloc: mmap(MAP_ANON) anon free: munmap file alloc: mmap(MAP_SHARED) file free: munmap target directories: /u01/app/ggs/BR/XINT1. 2013-04-25 17:19:41 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, xint1.prm: Virtual Memory Facilities for: COM anon alloc: mmap(MAP_ANON) anon free: munmap file alloc: mmap(MAP_SHARED) file free: munmap target directories: /u01/app/ggs/dirtmp. 2013-04-25 17:19:43 WARNING OGG-01842 Oracle GoldenGate Capture for Oracle, xint1.prm: CACHESIZE PER DYNAMIC DETERMINATION (2G) LESS THAN RECOMMENDED: 64G (64bit system) vm found: 3.82G Check swap space. Recommended swap/extract: 128G (64bit system). Monitoring There are several components of the Integrated Capture Mode all of which need to be monitored for the effective tuning and troubleshooting of the Replication. Since the extract process mostly resides in the database, the Goldengate, Capture and the Logminer statistics integrated capture monitoring views can be used to view the progress of the extract process. The main components to keep an eye on are below. ? Capture Processes configured in the database. ? Dynamic stats of the Goldengate capture process ? Logminer performance ? Outbound progress table DBA_CAPTURE col CAPTURE_NAME for a20; col QUEUE_NAME for a15; col START_SCN for 9999999999; col STATUS for a10; col CAPTURED_SCN for 9999999999; col APPLIED_SCN for 9999999999; col SOURCE_DATABASE for a10; col LOGMINER_ID for 9999999; col REQUIRED_CHECKPOINTSCN for a30; col STATUS_CHANGE_TIME for a15; col ERROR_NUMBER for a15; col ERROR_MESSAGE for a10; col START_TIME for a30 col CAPTURE_TYPE for a10; SELECT CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS, CAPTURED_SCN, APPLIED_SCN, SOURCE_DATABASE, LOGMINER_ID, REQUIRED_CHECKPOINT_SCN, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE, CAPTURE_TYPE, START_TIME FROM DBA_CAPTURE; GOLDENGATE CAPTURE/Trans col state for a30; SELECT sid, serial#, capture#, CAPTURE_NAME, STARTUP_TIME, CAPTURE_TIME, state, SGA_USED, BYTES_OF_REDO_MINED, to_char(STATE_CHANGED_TIME, 'mm-dd-yy hh24:mi') STATE_CHANGED_TIME FROM V$GOLDENGATE_CAPTURE; col capture_message_create_time for a30; col enqueue_message_create_time for a27; col available_message_create_time for a30; SELECT capture_name, to_char(capture_time, 'mm-dd-yy hh24:mi') capture_time, capture_message_number, to_char(capture_message_create_time ,'mm-dd-yy hh24:mi') capture_message_create_time, to_char(enqueue_time,'mm-dd-yy hh24:mi') enqueue_time, enqueue_message_number, to_char(enqueue_message_create_time, 'mm-dd-yy hh24:mi') enqueue_message_create_time, available_message_number, to_char(available_message_create_time,'mm-dd-yy hh24:mi') available_message_create_time FROM GV$GOLDENGATE_CAPTURE; SELECT component_name capture_name, count(*) open_transactions, sum(cumulative_message_count) LCRs FROM GV$GOLDENGATE_TRANSACTION WHERE component_type='CAPTURE' group by component_name; LOGMINER SESSIONS/STATS col db_name for a15; select INST_ID, SESSION_ID,SESSION_NAME,SESSION_STATE, DB_NAME, NUM_PROCESS,START_SCN,END_SCN,SPILL_SCN, PROCESSED_SCN, PREPARED_SCN, READ_SCN MAX_MEMORY_SIZE,USED_MEMORY_SIZE PINNED_TXN, PINNED_COMMITTED_TXN from GV$LOGMNR_SESSION; SELECT SESSION_ID, NAME, VALUE FROM V$LOGMNR_STATS; OUTBOUND PROGRESS TABLE SELECT inst_id, sid, serial#, spid,server_name, startup_time, state, tztal_messages_sent, committed_data_only, last_sent_message_number, send_time, elapsed_send_time,bytes_sent, to_char(last_sent_message_create_time,'mm-dd-yy hh24:mi') last_sent_message_create_time, FROM GV$XSTREAM_OUTBOUND_SERVER; ================================================================================= --9.extract停掉后，再次启动从checkpoint table处取得变化的起点，如果起点已在归档日志中，extract能否自动寻找？如何寻找？找不到咋办？ 10.源端打应用补丁和应用升级对容灾端的影响。 11.OGG datapump启用compress和加密后对系统的影响？ 12.当遇到长事物时，OGG的extract抓取的redo中变化如何缓存？ 13.表没有主键有什么影响？ 15.针对各个进程的参数，Oracle推荐使用的有哪些？ 16.CSN的解释 17.sequence同步如何配置？ 18.源端停机维护的注意事项 19.Director使用 Management Pack(重点功能介绍) --20.表结构变化的注意事项？是否需要重建trandata？ 21.collector注意事项？ 22.troubleshooting的步骤:logdump, Director,/var/log/messahes,stats/info/status /send,... 23.tunning的步骤: os,network performance,process group,filter,.... 24.OGG全库同步，可否不指定用户？ 25.目标端是否需要禁用触发器和外键？?? 26.lob字段???OGG不支持的数据库对象。 27.源端数据库 追加日志参数 有何影响？ 28.如何实现开机自动启动OGG 29.EBS在应用端跨平台的同步,也就是应用的跨平台 30.两端的trail不是一一对应的。同名的trial文件在源和目的不一样？ 31.如果容灾端数据发生变化，是否影响同步？？？ 32， extract&replicate进程异常中断后的恢复原理，从哪读取恢复的断点信息 33， 源端抽取进程trail文件损坏后，如何处理 34，rollover的应用场景 beau.zhang@oracle.com 1.系统负载影响很低 。 内存消耗大 2.Management Pack 图形界面管理工具 包含Director 3. filter include filename APPS.TA;filter rectype delete;filter match all; filter include filename APPS.TA;filter string 'ogg';filter match all; /root/.gconf/apps/puplet/ Jun 9 12:34:29 HdcProd gconfd (root-15733): Failed to write "/root/.gconf/apps/puplet/%gconf.xml": Failed to move temporary file "/root/.gconf/apps/puplet/%gconf.xml.new" to final location "/root/.gconf/apps/puplet/%gconf.xml": No su ch file or directory Jun 9 12:34:29 HdcProd gconfd (root-14905): Failed to write "/root/.gconf/apps/puplet/%gconf.xml": Failed to move temporary file "/root/.gconf/apps/puplet/%gconf.xml.new" to final location "/root/.gconf/apps/puplet/%gconf.xml": No su ch file or directory Jun 9 12:34:29 HdcProd gconfd (root-14905): Failed to sync one or more sources: Failed to write some configuration d ata to disk Jun 9 12:34:29 HdcProd gconfd (root-15733): Failed to sync one or more sources: Failed to write some configuration data to disk select count(*) from hdcoracle.delayed_jobs;