OracleGoldenGate系列五--GG使用配置说明
一. Preparing the database for Oracle GoldenGate
1.1 Preparing constraints for Oracle GoldenGate
The followingtable attributes must be addressed in an Oracle GoldenGate environment.
成都创新互联10多年企业网站设计服务;为您提供网站建设,网站制作,网页设计及高端网站定制服务,企业网站设计及推广,对广告推广等多个行业拥有多年的网站推广经验的网站建设公司。
1.1.1 Disabling triggers and cascade constraints
You will need to make some changes to the triggers, cascade update, and cascade delete constraintson the target tables.oracle GoldenGate replicates DML that results from a triggeror a cascade constraint. If the same trigger or constraint gets activated onthe target table, it becomes redundant because of the replicated version, andthe database returns an error.
--在target table上，需要禁用triggers或者cascadeconstraints，因为source 上会将这些改变同步过去，如果在target 端在做一次就会报错，下面的示例使整个流程：
Consider the following example, where the source tables are “emp_src” and “salary_src” and the target tables are “emp_targ” and “salary_targ.”
1. A delete is issued for emp_src.
2. It cascades a delete to salary_src.
3. Oracle GoldenGate sends both deletes tothe target.
4. The parent delete arrives first and isapplied to emp_targ.
5. The parent delete cascades a delete tosalary_targ.
6. The cascaded delete from salary_src isapplied to salary_targ.
7. The row cannot be located because it wasalready deleted in step 5.
Oracle GoldenGate provides some options to handle triggers or cascade constraints automatically,depending on the Oracle version:
--GG 提供了选选项自动处理triggers 或者 cascade constraints，不同的版本，方法也不一样。
（1） For Oracle10.2.0.5 and later patches to 10.2.0.5, and for Oracle 11.2.0.2 and later 11gR2 versions, you can use the Replicat parameter DBOPTIONS with the SUPPRESSTRIGGERS option to cause Replicat to disable the triggers during its session.
（2） For Oracle9.2.0.7 and later, you can use the Replicat parameter DBOPTIONS with the DEFERREFCONSToption to delay the checking and enforcement of cascade update and cascadedelete constraints until the Replicat transaction commits.
（3） For earlier Oracle versions, you must disable triggers and integrity constraints or alter themmanually to ignore the Replicat database user.
1.1.2 Deferring constraint checking
If constraintsare DEFERRABLE on the source, the constraints on the target must also be DEFERRABLE.You can use one of the following parameter statements to defer constraint checkinguntil a Replicat transaction commits:
--如果source 端的约束是deferrable（延时）的，那么target端也必须是deferrable的。
（1） Use SQLEXEC at the root level ofthe Replicat parameter file to defer the constraints for an entire Replicatsession.
--使用SQLEXEC设置这个Replicat session的defer 属性
SQLEXEC (“altersession set constraint deferred”)
（2） For Oracle 9.2.0.7 and later, youcan use the Replicat parameter DBOPTIONS with the DEFERREFCONST option to delayconstraint checking for each Replicat transaction.
--9.2.0.7 之后可以通过DBOPTIONS参数的DEFERREFCONST选项来设置delay constraint checking。
Replicat mightneed to set constraints to DEFERRED if it is possible that an update transactioncould affect the primary keys of multiple rows. Called a transient primary key updatein Oracle GoldenGate terminology, this kind of operation typically uses an x+n formulaor other form of manipulation that shifts the values and causes a new value tobe the same as an old one.
The followingillustrates a sequence of value changes that can cause this condition if constraintsare not deferred. The example assumes the primary key column is “CODE” and thecurrent key values (before the updates) are 1, 2, and 3.
update item set code = 2 where code = 1;
update item set code = 3 where code = 2;
update item set code = 4 where code = 3;
In this example,when Replicat applies the first update to the target, there is an error becausethe key value of 2 already exists in the table. The Replicat transactionreturns constraint violation errors. By default,Replicat does not handle these violations and abends.
--上面的示例演示了deferred 的影响，如果不设置，Replicat 事务会报错，而且在默认情况下Replicat 不会处理这种异常。
To enable Replicat tomanage these updates:
--启用Replicat管理以上的update 情况：
（1） Use the Replicat parameter HANDLETPKUPDATE to enable Replicat to handle the transient primary key updates.
（2） Create the constraints as DEFERRABLE INITIALLY IMMEDIATE on the target tables. The constraints arechecked when Replicat commits the transaction. You can:
1） Use SQLEXEC at the root level of the Replicat parameter file todefer the constraints for an entire Replicat session.
SQLEXEC (“alter session set constraintdeferred”)
2） For Oracle 9.2.0.7 and later, you can use the Replicat parameterDBOPTIONS with the DEFERREFCONST option to delay constraint checking for eachReplicat transaction. If constraints are not DEFERRABLE, Replicat handles theerrors according to rules that are specified with the HANDLECOLLISIONS andREPERROR parameters, if they exist, or else it abends.
1.1.3 Assigning row identifiers
Oracle GoldenGate requires some form of unique row identifier on the source and targettables to locate the correct target rows for replicated updates and deletes.
1.1.3.1 How Oracle GoldenGate determines the kind of row identifier to use
Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate automatically selectsa row identifier to use in the following order of priority:
1. Primary key
2. First uniquekey alphanumerically with no virtual columns, no UDTs, no functionbased columns,and no nullable columns
3. First uniquekey alphanumerically with no virtual columns, no UDTs, and no functionbased columns,but can include nullable columns
4. If none of the preceding key types exist (even though there might be other types of keys definedon the table) Oracle GoldenGate constructs a pseudo key of all columns that thedatabase allows to be used in a unique key, excluding virtual columns, UDTs, function-basedcolumns, and any columns that are explicitly excluded from the Oracle GoldenGateconfiguration.
Depending on whether schema-level or table-level logging was activated, there might be just one key or multiple keys logged to the redo log.
NOTE:
If there are other, non-usable keys on a table or if there are no keys at all on the table,Oracle GoldenGate logs an appropriate message to the report file. Constructinga key from all of the columns impedes the performance of Oracle GoldenGate onthe source system. On the target, this key causes Replicat to use a larger,less efficient WHERE clause.
1.1.3.2 Using KEYCOLS tospecify a custom key
If a table does not have an appropriate key, or if you prefer the existing key(s) not to be used,you can define a substitute key if the table has columns that always containunique values. You define this substitute key by including a KEYCOLS clausewithin the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that OracleGoldenGate finds.
1.1.4 Configuring the database to log key values
GGSCI provides commands to configure the source database to log the appropriate key values whenever it logs a row change, so that they are available to Oracle GoldenGate in the redo record. By default, the database only logs column values that arechanged. The appropriate command must be issued before you start OracleGoldenGate processing.
--GGSCI 可以配置source database 上写入log的内容，在默认情况下只log 变化的列值，在启动GG 进程之前需要发布合适的命令来控制log内容。
1.1.4.1 ADD TRANDATA
The ADD TRANDATA command enables table-level logging and is generally appropriate if you willnot be using the Oracle GoldenGate DDL replication feature, or if you want to use that feature and your data meets certain requirements documented for this command in the Windows and UNIX Reference Guide.
1.1.4.2 ADD SCHEMATRANDATA
The ADD SCHEMATRANDATA command enables schema-level logging. It logs more key values to the redo log than ADD TRANDATA does, and it affects all of the current and future tables of a given schema. Because ADD SCHEMATRANDATA logs key values atomically when each DDL operation occurs, it is the preferred logging method to use if you will be using the Oracle GoldenGate DDL replication feature. (If thedatabase system can tolerate the extra redo data, you can also use ADDSCHEMATRANDATA without using the DDL replication feature.)
To initiate the logging of key values
1. On the source system, run GGSCI from theOracle GoldenGate directory.
2. In GGSCI, issue the following command tolog on to the database.
DBLOGIN USERID
Where:
3. Issue the ADD TRANDATA or ADD SCHEMATRANDATA command.
4. Log in to SQL*Plus as a user with ALTERSYSTEM privilege, and then issue the following command to enable minimalsupplemental logging at the database level. This logging is required to processupdates to primary keys and chained rows.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
5. To start the supplemental logging,switch the log files.
ALTER SYSTEM SWITCH LOGFILE;
6. Verify that supplemental logging isenabled at the database level with this command:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
（1） For Oracle 9i, the output must beYES.
（2） For Oracle 10g, the output must beYES or IMPLICIT.
7. If using ADD TRANDATA with the COLSoption, create a unique index for those columns on the target to optimize rowretrieval. If you are logging those columns as a substitute key for a KEYCOLSclause, make a note to add the KEYCOLS clause to the TABLE and MAP statementswhen you configure the Oracle GoldenGate processes.
1.1.5 Limiting row changes in tables that do not have a key
If a target table does not have a primary key or a unique key, duplicate rows can exist. In this case, Oracle GoldenGate could update or delete too many target rows,causing the source and target data to Go out of synchronization without error messages to alert you.
To limit the number of rows that are updated, use the DBOPTIONS parameter with the LIMITROWS option in the Replicat parameter file. LIMITROWS can increase the performance of Oracle GoldenGate on the target system because only one row is processed.
1.2 Configuring character sets
To ensure accurate character representation from one database to another, the following mustbe true:
（1） The character set of the targetdatabase must be a superset of the character set of the source database.
（2） If your client applications usedifferent character sets, the database character set must be a superset of thecharacter sets of the client applications. In this configuration, every characteris represented when converting from a client character set to the database characterset.
1.2.1 To view globalization settings
To determine the globalization settings of the database and whether it is using byte or character semantics, use the following commands in SQL*Plus:
SHOW PARAMETER NLS_LANGUAGE
SHOW PARAMETER NLS_TERRITORY
SELECT name,value$ from SYS.PROPS$ WHERE name = 'NLS_CHARACTERSET';
SHOW PARAMETERNLS_LENGTH_SEMANTICS
1.2.2 To view globalization settings from GGSCI
The VIEW REPORT
1.2.3 To set NLS_LANG
1. Set the NLS_LANG parameter according to the documentation for your database version and operating system. On UNIX systems, you can set NLS_LANG through the operating system or by using a SETENV parameter in the Extract and Replicat parameter files. For best results, set NLS_LANG from the parameter file, where it is less likely to be changed than at the system level.
NLS_LANG must beset in the format of:
This is an example in UNIX, using the SETENV parameter in the Oracle GoldenGate parameterfile:
SETENV (NLS_LANG= “AMERICAN_AMERICA.AL32UTF8”)
2. Stop and thenstart the Oracle GoldenGate Manager process so that the processes recognize thenew variable.
NOTE:
OracleGoldenGate reports Oracle error messages in U.S. English (AMERICAN_AMERICA),regardless of the actual character set of the reporting database. OracleGoldenGate performs any necessary language conversion internally withoutchanging the language configuration of the database.
1.3 Adjusting cursors
The Extract process maintains cursors for queries that fetch data and also for SQLEXEC operations.Without enough cursors, Extract must age more statements. Extract maintains as many cursors as permitted by the Extract MAXFETCHSTATEMENTS parameter. You can increase the value of this parameter as needed. Make an appropriate adjustment to the maximum number of open cursors that are permitted by the database.
1.4 Setting fetch options
To process certain update records from the redo log, Oracle GoldenGate fetches additional rowdata from the source database. Oracle GoldenGate fetches data for the following:
（1） Operations that contain LOBs.(Fetching of LOBs does not apply to Oracle 10g and later databases, becauseLOBs are captured from the redo log of those versions.)
（2） User-defined types
（3） Nested tables
（4） XMLType objects
By default,Oracle GoldenGate uses Flashback Query to fetch the values from the undo (rollback) tablespaces. That way, Oracle GoldenGate can reconstruct a read-consistent row imageas of a specific time or SCN to match the redo record.
--默认情况下，GG 使用Flashback query 从undo 里fetch values。
To configure the database for best fetch results
For best fetch results, configure thesource database as follows:
--为了更好的进行fetch，在source database 进行如下配置：
1. Set a sufficient amount of redoretention by setting the Oracle initialization parameters UNDO_MANAGEMENT andUNDO_RETENTION as follows (in seconds).
UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=86400
UNDO_RETENTION can be adjusted upward in high-volume environments.
2. Calculate the space that is required inthe undo tablespace by using the following formula.
Where:
（1）
（2）
（3）
（4）
Use the system view V$UNDOSTAT to estimate
3. For tables that contain LOBs, do one of the following:
（1） Set the LOB storage clause toRETENTION. This is the default for tables that are created when UNDO_MANAGEMENTis set to AUTO.
--lob 存储设置为retention，那么UNDO_MANAGEMENT 设置为auto。
（2） If usingPCTVERSION instead of RETENTION, set PCTVERSION to an initial value of 25. You can adjust it based on the fetch statistics that are reportedwith the STATS EXTRACT command (see Table 10). If the value of theSTAT_OPER_ROWFETCH CURRENTBYROWID or STAT_OPER_ROWFETCH_CURRENTBYKEY field inthese statistics is high, increase PCTVERSION in increments of 10 until thestatistics show low values.
--如果使用PCTVERSION，那么该值设为25. LOB中PCTVERSION 和RETENTION的区别，参考我的blog：
Oracle LOB 详解
http://blog.csdn.net/tianlesoftware/article/details/6905406
4. Grant the following privileges to theOracle GoldenGate Extract user:
GRANT FLASHBACKANY TABLE TO
Or ...
GRANT FLASHBACKON
Oracle GoldenGate provides the following parameters to manage fetching.
1.5 Replicating TDE-encrypted data
Oracle GoldenGate supports the Transparent Data Encryption (TDE) at the column and tablespace level.
（1） Column-level encryption issupported for all versions of 10.2.0.5, 11.1, and 11.2.
（2） Tablespace-level encryption issupported for all versions of 10.2.0.5 and 11.1.0.2.
1.5.1 Required database patches
To support TDE,one of the following Oracle patches must be applied to the database,dependingon the version.
(1) Patch 10628966 for10.2.0.5.2PSU
(2) Patch 10628963 for11.1.0.7.6PSU
(3) Patch 10628961 for 11.2.0.2
1.5.2 Overview of TDE support
TDE support involves two kinds of keys:
（1） The encrypted key can be a tablekey (column-level encryption), an encrypted redo log key (tablespace-levelencryption), or both. A key is shared between the Oracle server and Extract.
（2） The decryption key is a passwordknown as the shared secret that is stored securely in both domains. Only aparty that has possession of the shared secret can decrypt the table and redolog keys.
The encrypted keys are delivered to the Extract process by means of built-in PL/SQL code. Extract uses the shared secret to decrypt the data. Extract never handles the walletMaster Key itself, nor is it aware of the Master Key password. Those remainwithin the Oracle server security framework.
Extract neverwrites the decrypted data to any file other than a trail file, not even adiscard file (specified with the DISCARDFILE parameter). The word “ENCRYPTED”will be written to any discard file that is in use.
The impact of this feature on Oracle GoldenGate performance should mirror that of the impactof decryption on database performance. Other than a slight increase in Extract startuptime, there should be a minimal affect on performance from replicating TDEdata.
1.5.3 Requirements for replicating TDE
（1） If DDL will ever be performed onan encrypted table, or if table keys will ever be rekeyed, you must eitherquiesce the table while the DDL is performed or enable Oracle GoldenGate DDLsupport. It is more practical to have the DDL environment active so that it isready, because a re-key usually is a response to a security violation and must beperformed immediately. To install the Oracle GoldenGate DDL environment, see theinstructions in this guide.
（2） To maintain high securitystandards, the Oracle GoldenGate Extract process should run as part of theOracle User (the user that runs the Oracle Server). That way, the keys areprotected in memory by the same privileges as the Oracle User.
（3） The Extract process must run onthe same machine as the database installation.
1.5.4 Recommendations for replicating TDE
Extract decryptsthe TDE data and writes it to the trail as clear text. To maintain data securitythroughout the path to the target tables, it is recommended that you alsodeploy Oracle GoldenGate security features to:
(1) encrypt the data in the trails
(2) encrypt the data in transitacross TCP/IP
1.5.5 Configuring TDE support
The followingoutlines the steps that the Oracle Security Officer and the Oracle GoldenGateAdministrator take to establish communication between the Oracle server and theExtract process.
1.5.5.1 Oracle SecurityOfficer and Oracle GoldenGate Administrator
Agree on ashared secret (password) that meets or exceeds Oracle password standards. Thispassword must not be known by anyone else.
1.5.5.2 Oracle Security Officer
1. Issue thefollowing MKSTORE command to create an “ORACLEGG” entry in the Oracle wallet.ORACLEGG must be the name of the key. Do not supply the shared secret on the commandline; instead, supply it when prompted.
Mkstore -wrl ./ -createEntryORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Enter
2. (Oracle RAC10gR2 and 11gR1) Copy the wallet that contains the shared secret to each node,and then reopen the wallets.
NOTE
With Oracle11gR2, there is one wallet in a shared location, with synchronized access among all nodes.
1.5.5.3 Oracle GoldenGate Administrator
1. Compile thedbms_internal_cklm.plb package that is installed in the Oracle GoldenGate installationdirectory.
2. Grant EXECprivilege on the dbms_internal_cklm.get_key procedure to the Extract database user.This procedure facilitates sharing of the encrypted keys between the Oracle Serverand Extract.
3. Run GGSCI andissue the ENCRYPT PASSWORD command to encrypt the shared secret so that it isobfuscated within the Extract parameter file. This is a security requirement.
ENCRYPT PASSWORDtakes the clear-text string as input and provides options for encrypting itwith an Oracle GoldenGate-generated default key or a user-defined key that isstored in a secure local ENCKEYS file. For instructions, see the securitychapter of the Oracle GoldenGate Windows and UNIX Administrator’s Guide.
4. In theExtract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option.As input, supply the encrypted shared secret and the Oracle GoldenGategeneratedor user-defined decryption key. For syntax options, see the Oracle GoldenGateWindows and UNIX Reference Guide.
5. Close, andthen open, the Oracle wallet before you start Extract. This step works aroundissues with caching that can cause an ORA-28360 (security module) error.
NOTE :
Close and thenopen the wallet whenever a shared secret is created or changed.
1.5.6 Changing the Oracle shared secret
Use this procedure to change the shared secret that supports Oracle Transparent Data Encryption.You can change the shared secret when needed, but “ORACLEGG” must remain thename of the key.
1. Stop the Extract process.
STOP EXTRACT
2. Issue the following MKSTORE command tomodify the "ORACLEGG" entry in the Oracle wallet. Do not supply thenew shared secret on the command line; instead, supply it when prompted.
Mkstore -wrl ./ -modifyEntryORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Enter
NOTE:
To change the shared secret, the parameter 'modifyEntry' is used instead of “createEntry,”because you are modifying an existing wallet entry.
3. Use the ENCRYPT PASSWORD command inGGSCI to encrypt the new shared secret. For instructions, see the securitychapter of the Oracle GoldenGate Windows and UNIX Administrator’s Guide.
4. Replace the old encrypted shared secretand decryption key with the new ones in the Extract parameter file by modifyingthe DECRYPTPASSWORD option of DBOPTIONS. For syntax options, see the OracleGoldenGate Windows and UNIX Reference Guide.
5. Close, and then open, the Oracle walletbefore you start Extract. This process works around issues with caching thatcan cause an ORA-28360 error.
6. Start Extract.
START EXTRACT
1.6 Ensuring correct handling of Oracle Spatial objects
To replicate georaster tables (tables that contain one or more columns of SDO_GEORASTER objecttype), follow these instructions to configure Oracle GoldenGate to process themcorrectly.
1.6.1 Mapping the georaster tables
You must createa TABLE statement and a MAP statement for the georaster tables and also for therelated raster data tables.
1.6.2 Sizing the XML memory buffer
Evaluate yourspatial data before starting Oracle GoldenGate processes. If the METADATA attributeof the SDO_GEORASTER data type in any of the values exceeds 1 MB, you must increasethe size of the memory buffer that stores the embedded SYS.XMLTYPE attribute ofthe SDO_GEORASTER data type. If the data exceeds the buffer size, Extractabends. The size of the XML buffer is controlled by the DBOPTIONS parameterwith the XMLBUFSIZE option.
1.6.3 Handling triggers on the georaster tables
Every georastertable has a trigger that affects the raster data table. To ensure the integrityof the target georaster tables, do the following:
（1） Keep the trigger enabled on bothsource and target to ensure consistency of the spatial data.
（2） Use the REPERROR option of the MAPparameter to handle “ORA-01403 No data found” errors.
The error iscaused by redundant deletes on the target. When a row in the source georaster tableis deleted, the trigger cascades the delete to the raster data table. Bothdeletes are replicated. The replicated parent delete triggers the cascaded(child) delete on the target.
When thereplicated child delete arrives, it is redundant and generates the error.
To handle redundant deletes with REPERROR
1. Use a REPERROR statement in each MAPstatement that contains a raster data table.
2. Use Oracle error 1403 as the SQL error.
3. Use any of the response options as theerror handling.
See the following examples for ways you canconfigure the error handling.
Example:
A sufficient way to handle the errors is simply to use REPERROR with DISCARD to discard the cascadeddelete that triggers them. The trigger on the target georaster table performsthe delete to the raster data table, so the replicated one is not needed.
MAP geo.st_rdt,TARGET geo.st_rdt, REPERROR (-1403, DISCARD) ;
Example:
If you need tokeep an audit trail of the error handling, use REPERROR with EXCEPTION to invokeexceptions handling. For this, you create an exceptions table and map thesource raster data table twice:
(1) once to the actual targetraster data table (with REPERROR handling the 1403 errors).
(2) again to the exceptions table,which captures the 1403 error and other relevant information by means of aCOLMAP clause.
When usingexceptions handling like this, you must use the ALLOWDUPTARGETMAP parameter tokeep Replicat from abending on the dual source mapping.
This example provides a Replicat parameter file that contains the required parameters, andit provides a sample script that creates an exceptions table. Note that a macrois used in the parameter file to populate the TARGET and COLMAP portions of theexceptions MAP statements. The required INSERTALLRECORDS and EXCEPTIONSONLYparameters are also included in the macro. The macro eliminates the need totype the same information over again for each of the MAP statements.
Replicat parameter file
REPLICATrgeoras
SETENV(ORACLE_SID=tgt111)
USERIDgeo, PASSWORD xxxxx, ENCRYPTKEY DEFAULT
ASSUMETARGETDEFS
DISCARDFILE./dirrpt/rgeoras.dsc, purge
ALLOWDUPTARGETMAP
-- Thisstarts the macro
MACRO#exception_handling
BEGIN
, TARGETgeo.exceptions
, COLMAP( rep_id = "1"
,table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno= @GETENV ("LASTERR", "DBERRNUM")
,dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype= @GETENV ("LASTERR", "OPTYPE")
,errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba= @GETENV ("GGHEADER", "LOGRBA")
,logposition = @GETENV ("GGHEADER", "LOGPOSITION")
,committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")
)
,INSERTALLRECORDS
,EXCEPTIONSONLY ;
END;
-- Thisends the macro
EXTTRAIL./dirdat/eg
--Mapping of regular and georaster tables. Requires no exception handling.
--Replicat abends on errors, which is its default error handling.
MAPgeo.blob_table, TARGET geo.blob_table ;
MAPgeo.georaster_table, TARGET geo.georaster_table ;
MAPgeo.georaster_table2, TARGET geo.georaster_table2 ;
MAPgeo.georaster_tab1, TARGET geo.georaster_tab1 ;
MAPgeo.georaster_tab2, TARGET geo.georaster_tab2 ;
MAPgeo.mv_georaster_table1, TARGET geo.mv_georaster_table1 ;
-- Mapping of rasterdata tables. Requires exception handling for 1403 errors.
MAPgeo.st_rdt_3_table, TARGET geo.st_rdt_3_table, REPERROR (-1403, EXCEPTION)
;
MAPgeo.st_rdt_3_table #exception_handling()
MAP geo.rdt_1_table,TARGET geo.rdt_1_table, REPERROR (-1403, EXCEPTION) ;
MAP geo.rdt_1_table#exception_handling()
MAP geo.rdt_2_table,TARGET geo.rdt_2_table, REPERROR (-1403, EXCEPTION) ;
MAP geo.rdt_2_table#exception_handling()
MAPgeo.mv_rdt_1_table, TARGET geo.mv_rdt_1_table, REPERROR (-1403, EXCEPTION)
;
MAPgeo.mv_rdt_1_table #exception_handling()
Sample script that creates an exceptions table
drop tableexceptions
/
create tableexceptions
( rep_id number
, table_namevarchar2(61)
, errno number
, dberrmsgvarchar2(4000)
, optypevarchar2(20)
, errtypevarchar2(20)
, logrba number
, logposition number
, committimestamptimestamp
)
/
NOTE:
When using anexceptions table for numerous tables, someone should monitor its growth.
1.7 Replicating TIMESTAMP with TIME ZONE
Oracle GoldenGate supports the capture and replication of TIMESTAMP WITH TIME ZONE asa UTC offset (TIMESTAMP '2011-01-01 8:00:00 -8:00') but abends on TIMESTAMPWITH TIME ZONE as TZR (TIMESTAMP '2011-01-01 8:00:00 US/Pacific') by default.
To support TIMESTAMP WITH TIME ZONE as TZR, use the Extract parameter TRANLOGOPTIONS with oneof the following:
(1) INCLUDEREGIONID to replicateTIMESTAMP WITH TIME ZONE as TZR from an Oracle source to anOracle target of thesame version or later.
(2) INCLUDEREGIONIDWITHOFFSET toreplicate TIMESTAMP WITH TIMEZONE as TZR from an Oraclesource that is at leastv10g to an earlier Oracle target, or to a non-Oracle target.
These options allow replication to Oracle versions that do not support TIMESTAMP WITH TIME ZONEas TZR and to database systems that only support time zone as a UTC offset.
NOTE:
Oracle GoldenGate does not support TIMESTAMP WITH TIME ZONE as TZR for initial loads,situations where the column must be fetched from the database, or for theSQLEXEC feature. In these cases, the region ID is converted to a time offset bythe Oracle database when the column is selected. Replicat replicates the columndata as date and time data with a time offset value.
1.8 Controlling Replicat COMMIT options on an Oracle target
When an onlineReplicat group is configured to use a checkpoint table (recommended), it takes advantageof the asynchronous COMMIT feature that was introduced in Oracle 10gR2.
When applying atransaction to the Oracle target, Replicat includes the NOWAIT option in theCOMMIT statement. This improves performance by allowing Replicat to continue processingimmediately after applying the COMMIT, while the database engine logs the transactionin the background.
The checkpointtable supports data consistency with asynchronous COMMITs because it makes theReplicat checkpoint part of the Replicat transaction itself. The checkpointeither succeeds with, or fails with, that transaction. Asynchronous COMMIT isalso the default for initial loads and batch processing.
You can disablethe default asynchronous COMMIT behavior by using the DBOPTIONS parameter with theDISABLECOMMITNOWAIT option in the Replicat parameter file. If a checkpoint table is not used for a Replicat group, the checkpoints are maintained in a fileon disk, and Replicat uses the synchronous COMMIT option by default (COMMIT with WAIT), which forces Replicat to wait until the transaction is loggedbefore it can continue processing. This prevents inconsistencies that canresult after a database failure, where the state of the transaction that is recorded in the checkpoint file might be different than its state after therecovery.
1.9 Supporting delivery to Oracle Exadata with EHCC compressed data
OracleGoldenGate supports delivery to Oracle Exadata with Hybrid Columnar Compression(EHCC) enabled for insert operations. To ensure that this data is applied
correctly, use the INSERTAPPEND parameterin the Replicat parameter file. INSERTAPPEND causes Replicat to use an APPENDhint for inserts. Without this hint, the record will be inserted uncompressed.
NOTE：
Capture from Exadata is not supported at this time.
1.10 Managing LOB caching on a target Oracle database
Replicat writesLOB data to the target database in fragments. To minimize the effect of thisI/O on the system, Replicat enables Oracle’s LOB caching mechanism, caches the fragmentsin a buffer, and performs a write only when the buffer is full. For example, ifthe buffer is 25,000 bytes in size, Replicat only performs I/O four times givena LOB of 100,000 bytes.
(1) To optimize the buffer size tothe size of your LOB data, use the DBOPTIONS parameter with the LOBWRITESIZE
(2) To disable Oracle’s LOBcaching, use the DBOPTIONS parameter with the DISABLELOBCACHING option. WhenLOB caching is disabled, whatever is sent by Replicat to Oracle in one I/O callis written directly to the database media.
1.11 Additional requirements for Oracle RAC
This topiccovers additional configuration requirements that apply when Oracle GoldenGatewill be operating in an Oracle Real Application Clusters (RAC) environment.
1.11.1 General requirements
（1） All nodes in the RAC cluster musthave synchronized system clocks. The clocks must be synchronized with the clockon the system where Extract is executed. Oracle GoldenGate compares the time ofthe local system to the commit timestamps to make critical decisions. Forinformation about synchronizing system clocks, consult www.ntp.org or yoursystems administrator.
（2） All nodes in the cluster must havethe same COMPATIBLE parameter setting.
The following table shows some OracleGoldenGate parameters that are of specific benefitin Oracle RAC.
1.11.2 Special procedures on RAC
（1） If the primary database instanceagainst which Oracle GoldenGate is running stops or fails for any reason,Extract abends. To resume processing, you can restart the instance or mount theOracle GoldenGate binaries to another node where the database is running andthen restart the Oracle GoldenGate processes. Stop the Manager process on theoriginal node before starting Oracle GoldenGate processes from another node.
（2） Whenever the number of redothreads changes, the Extract group must be dropped and re-created.
（3） To write SQL operations to thetrail, Extract must verify that there are no other operations from other RACnodes that precede the ones in the redo log that it is reading. For example, ifa log contains operations that were performed from 1:00 a.m. to 2:00 a.m., andthe log from Node 2 contains operations that were performed from 1:30 a.m. to2:30 a.m., then only the operations up to, and including, the 2:00 a.m. one canbe moved to the server where the main Extract is coordinating the redo data. Extractmust ensure that there are no more operations between 2:00 a.m. and 2:30a.m.that need to be captured.
（4） In active-passive environments,the preceding requirement means that you might need to perform some operationsand archive log switching on the passive node to ensure that operations fromthe active node are passed to the passive node. This eliminates any issues thatcould arise from a slow archiver process, failed network links, and other latencyissues caused by moving archive logs from the Oracle nodes to the server where themain Extract is coordinating the redo data.
（5） To process the last transaction ina RAC cluster before shutting down Extract, insert a dummy record into a sourcetable that Oracle GoldenGate is replicating, and then switch log files on allnodes. This updates the Extract checkpoint and confirms that all availablearchive logs can be read. It also confirms that all transactions in those archivelogs are captured and written to the trail in the correct order.
1.12 Additional requirements for ASM
This topiccovers additional configuration requirements that apply when Oracle GoldenGateoperates in an Oracle Automatic Storage Management (ASM) instance.
1.12.1 Ensuring ASM connectivity
To ensure thatthe Oracle GoldenGate Extract process can connect to an ASM instance, do thefollowing.
（1） List the ASM instance in thetnsnames.ora file. The recommended method for connecting to an ASM instancewhen Oracle GoldenGate is running on the database host machine is to use abequeath (BEQ) protocol.
NOTE：
A BEQ connectiondoes not work when using a remote Extract configuration. In that case,configure TNSNAMES with the TCP/IP protocol.
（2） If using the TCP/IP protocol,verify that the Oracle listener is listening for new connections to the ASMinstance. The listener.ora file must contain an entry similar to the following.
SID_LIST_LISTENER_ASM=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= ASM)
(ORACLE_HOME= /u01/app/grid)
(SID_NAME= +ASM1)
)
)
NOTE：
TheBEQ protocol does not require a listener.
1.12.2 Optimizing the ASM connection
Use theTRANLOGOPTIONS parameter with the DBLOGREADER option in the Extract parameter fileif the ASM instance is one of the following versions:
(1) Oracle 10.2.0.5 or later 10g R2versions
(2) Oracle 11.2.0.2 or later 11g R2versions
A newer ASM APIis available in those releases (but not in Oracle 11g R1 versions) that usesthe database server to access the redo and archive logs. When used, this APIenables Extract to use a read buffer size of up to 4 MB in size. A largerbuffer may improve the performance of Extract when redo rate is high. You canuse the DBLOGREADERBUFSIZE option of TRANLOGOPTIONS to specify a buffer size.
二.Preparing DBFS for active-activepropagation with Oracle GoldenGate
2.1 Supported operations and prerequisites
Oracle GoldenGate for DBFS supports the following:
（1） Supported DDL (like TRUNCATE or ALTER) on DBFS objects except for CREATE statements on the DBFS objects. CREATE onDBFS must be excluded from the configuration, as must any schemas that willhold the created DBFS objects. The reason to exclude CREATES is that themetadata for DBFS must be properly populated in the SYS dictionary tables (whichitself is excluded from Oracle GoldenGate capture by default).
（2） Capture and replication of DML onthe tables that underlie the DBFS filesystem.
The procedures that follow assume that Oracle GoldenGate is configured properly to support active-activeconfiguration. This means that it must be:
（1） Installed according to theinstructions in this guide.
（2） Configured according to theinstructions in the Oracle GoldenGate Windows and UNIX Administrator’s Guide.
2.2 Applying the required patch
Apply the OracleDBFS patch for bug-9651229 on both databases. To determine if the patch isinstalled, run the following query:
connect / as sysdba
select procedure_name from dba_procedures
where object_name = 'DBMS_DBFS_SFS_ADMIN'
and procedure_name = 'PARTITION_SEQUENCE';
The query should return a single row. Anything else indicates that the proper patched version ofDBFS is not available on your database.
2.3 Examples used in these procedures
The followingprocedures assume two systems and configure the environment so that DBFS userson both systems see the same DBFS files, directories, and contents that are keptin synchronization with Oracle GoldenGate. It is possible to extend theseconcepts to support three or more peer systems.
2.4 Partitioning the DBFS sequence numbers
DBFS uses aninternal sequence-number generator to construct unique names and unique IDs.These steps partition the sequences into distinct ranges to ensure that thereare no conflicts across the databases. After this is done, further DBFSoperations (both creation of new fileystems and subsequent filesystemoperations) can be performed without conflicts of names, primary keys, or IDsduring DML propagation.
1. Connect to each database as sysdba.
2. Issue the following query on eachdatabase.
select last_number
from dba_sequences
where sequence_owner = 'SYS'
and sequence_name = 'DBFS_SFS_$FSSEQ'
3. From this query, choose the maximumvalue of LAST_NUMBER across both systems, or pick a high value that issignificantly larger than the current value of the sequence on either system.
4. Substitute this value (“maxval” is usedhere as a placeholder) in both of the following procedures. These procedureslogically index each system as myid=0 and myid=1.
Node1
declare
begin
dbms_dbfs_sfs_admin.partition_sequence(nodes=> 2, myid => 0,
newstart=> :maxval);
commit;
end;
/
Node 2
declare
begin
dbms_dbfs_sfs_admin.partition_sequence(nodes => 2, myid => 1,
newstart=> :maxval);
commit;
end;
/
NOTE：
Notice thedifference in the value specified for the myid parameter. These are the different index values.
For a multi-way configuration among threeor more databases, you could make the following alterations:
（1） Adjust the maximum value that isset for “maxval” upward appropriately, and use that value on all nodes.
（2） Vary the value of “myid” in theprocedure from 0 for the first node, 1 for the second node, 2 for the thirdone, and so on.
5. (Recommended) After (and only after) theDBFS sequence generator is partitioned, create a new DBFS filesystem on eachsystem, and use only these filesystems for DML propagation with OracleGoldenGate. See “Configuring the DBFS filesystem”。
NOTE:
DBFS filesystemsthat were created before the patch for bug-9651229 was applied or before theDBFS sequence number was adjusted can be configured for propagation, but thatrequires additional steps not described in this document. If you must retainold filesystems, open a service request with Oracle Support.
2.5 Configuring the DBFS filesystem
To replicate DBFS filesystem operations, use a configuration that is similar to the standardbi-directional configuration for DML.
（1） Use matched pairs of identicallystructured tables.
（2） Allow each database to have writeprivileges to opposite tables in a set, and set the other one in the set toread-only. For example:
1）Node1 writes to local table "t1" and these changesare replicated to t1 on Node2.
2）Node2 writes to local table “t2” and these changes arereplicated to t2 on Node1.
3）On Node1, t2 is read-only. On Node2, t1 is read-only.
DBFS filesystems make this kind of table pairing simple because:
（1） The tables that underlie the DBFSfilesystems have the same structure.
（2） These tables are modified bysimple, conventional DML during higher-level filesystem operations.
（3） The DBFS Content API provides away of unifying the namespace of the individual DBFS stores by means of mountpoints that can be qualified as read-write or read-only.
The following steps create two DBFS filesystems (in this case named FS1 and FS2) and set the mto be read-write or read, as appropriate.
1. Run the following procedure to createthe two filesystems. (Substitute your store names for “FS1” and “FS2.”)
Example declare
dbms_dbfs_sfs.createFilesystem('FS1');
dbms_dbfs_sfs.createFilesystem('FS2');
dbms_dbfs_content.registerStore('FS1',
'posix', 'DBMS_DBFS_SFS');
dbms_dbfs_content.registerStore('FS2',
'posix', 'DBMS_DBFS_SFS');
commit;
end;
/
2. Run the following procedure to give eachfilesystem the appropriate access rights.
(Substitute your store names for “FS1” and“FS2.”)
Example Node 1
declare
dbms_dbfs_content.mountStore('FS1', 'local');
dbms_dbfs_content.mountStore('FS2', 'remote',read_only => true);
commit;
end;
/
Example Node 2
declare
dbms_dbfs_content.mountStore('FS1', 'remote',read_only => true);
dbms_dbfs_content.mountStore('FS2', 'local');
commit;
end;
/
In this example,note that on Node 1, store "FS1" is read-write and store"FS2" is read-only, while on Node 2 the converse is true: store"FS1" is read-only and store "FS2" is read-write.
Note also thatthe read-write store is mounted as "local" and the read-only store ismounted as "remote". This provides users on each system with anidentical namespace and identical semantics for read and write operations.Local path names can be modified, but remote path names cannot.
2.6 Mapping local and remote peers correctly
The names of thetables that underlie the DBFS filesystems are generated internally and dynamically.Continuing with the preceding example, there are:
(1) Two nodes (Node 1 and Node 2 inthe example).
(2) Four stores: two on each node(FS1 and FS2 in the example).
(3) Eight underlying tables: twofor each store (a table and a ptable). These tables must be identified,specified in Extract TABLE statements, and mapped in Replicat MAP statements.
1. To identify the table namesthat back each filesystem, issue the following query.(Substitute your storenames for “FS1” and “FS2.”)
Example select fs.store_name, tb.table_name,tb.ptable_name
fromtable(dbms_dbfs_sfs.listTables) tb,
table(dbms_dbfs_sfs.listFilesystems)fs
wherefs.schema_name = tb.schema_name
andfs.table_name = tb.table_name
andfs.store_name in ('FS1', 'FS2')
;
The output looks like the followingexamples.
2. Identify the tables that are locallyread-write to Extract by creating the following TABLE statements in the Extractparameter files. (Substitute your owner and table names.)
Example: Node 1
TABLE owner.SFS$_FST_100;
TABLE owner.SFS$_FSTP_100;
Example: Node 2
TABLE owner. SFS$_FST_119;
TABLE owner.SFS$_FSTP_119;
3. Link changes on each remotefilesystem to the corresponding local filesystem by creating the following MAP statementsin the Replicat parameter files. (Substitute your owner and table names.)
Example: Node 1
MAP owner.SFS$_FST_119,
TARGET owner.SFS$_FST_118;
MAP owner.SFS$_FSTP_119,
TARGET owner.SFS$_FSTP_118;
Example: Node 2
MAP owner.SFS$_FST_100,
TARGET owner.SFS$_FST_101;
MAP owner.SFS$_FSTP_100,
TARGET owner.SFS$_FSTP_101;
This mapping captures and replicates local read-write “source” tables to remote readonly peertables:
（1） Filesystem changes made to FS1 onNode 1 propagate to FS1 on Node 2.
（2） Filesystem changes made to FS2 onNode 2 propagate to FS2 on Node1.
Changes to thefilesystems can be made through the DBFS ContentAPI (package DBMS_DBFS_CONTENT)of the database or through dbfs_client mounts and conventional filesystemstools.
All changes are propagated in both directions.
（1） A user at the virtual root of theDBFS namespace on each system sees identical content.
（2） For mutable operations, users usethe "/local" sub-directory on each system.
（3） For read operations, users can useeither of the "/local" or "/remote" sub-directories, dependingon whether they want to see local or remote content.
三.Configuring the Oracle redo logs
3.1 Ensuring data availability
When operatingin its normal mode, Oracle GoldenGate reads the online logs by default, butwill read the archived logs if an online log is not available. Therefore, forbest results, enable archive logging. The archives provide a secondary datasource should the online logs recycle before Extract is finished with them. Thearchive logs for open transactions must be retained on the system in caseExtract needs to recapture data from them to perform a recovery.
默认情况下，GG 读取onlinelog 来获取capture 数据，如果online redo 不可用的时候就会去读归档日志。 所以尽可能的启用归档，并将归档日志保留一定的时间，以防Extract 进程从归档日志recapture data。
If you cannotenable archive logging, configure the online logs according to the following guidelinesto retain enough data for Extract to capture what it needs before the logs recycle.Allow for Extract backlogs caused by network outages and other externalfactors, as well as long-running transactions.
如果没有启用归档，那么就需要按照以下方法来配置onlinelog，以保证在log recycle 之前，Extrace 能从onlinelog里capture 足够的数据。
In a RACconfiguration, Extract must have access to the online and archived logs for allnodes in the cluster, including the one where Oracle GoldenGate is installed.
--RAC 环境下，Extract 必须能够访问所有节点的online 和archivedlog。 即RAC 环境下GG需要安装在共享文件系统上，这点在安装GG那部分有说明。
3.1.1 Log retention requirements per Extract recovery mode
The followingsummarizes the different recovery modes that Extract might use and their log-retentionrequirements:
（1） By default, the Bounded Recoverymode is in effect, and Extract requires access to the logs only as far back astwice the Bounded Recovery interval that is set with the BR parameter. Thisinterval is an integral multiple of the standard Extract checkpoint interval,as controlled by the CHECKPOINTSECS parameter. These two parameters control theOracle GoldenGate Bounded Recovery feature, which ensures that Extract can recoverin-memory captured data after a failure, no matter how old the oldest open transactionwas at the time of failure.
（2） In the unlikely event that theBounded Recovery mechanism fails when Extract attempts a recovery, Extractreverts to normal recovery mode and must have access to the archived log thatcontains the beginning of the oldest open transaction in memory at the time offailure and all logs thereafter.
3.1.2 Log retention options
Depending on theversion of Oracle, there are different options for ensuring that the requiredlogs are retained on the system.
（1）Oracle Enterprise Edition 10.2 and later
For these versions, Extract works with Oracle Recovery Manager (RMAN) to retain the logsthat Extract needs for recovery. This feature is enabled by default when youadd or register an Extract group in GGSCI with ADD EXTRACT (TRANLOG option) or REGISTEREXTRACT.
By default,Extract retains enough logs to perform a Bounded Recovery, but you can configureExtract to retain enough logs through RMAN for a normal recovery by using the TRANLOGOPTIONSparameter with the LOGRETENTION option set to SR. There also is an option to disablethe use of RMAN log retention. Review the options of LOGRETENTION in the OracleGoldenGate Windows and UNIX Reference Guide before you configure Extract. Ifyou set LOGRETENTION to DISABLED, see “Determining how much data to retain”.
NOTE:
To support RMANlog retention on Oracle RAC, you must download and install the database patchthat is provided in BUGFIX 11879974 before you add the Extract
groups.
The RMAN logretention feature creates an underlying (but non-functioning) Oracle StreamsCapture process for each Extract group. The name of the Capture is based on thename of the associated Extract group. The log retention feature can operateconcurrently with other local Oracle Streams installations. When you create anExtract group, the logs are retained from the current database SCN.
NOTE:
If the OracleFlashback storage area is full, RMAN purges the archive logs even when neededby Extract. This limitation exists so that the requirements of Extract (andother Oracle replication components) do not interfere with the availability of redoto the database.
（2）All other Oracle versions
For versions ofOracle other than Enterprise Edition 10.2 and later, you must manage the logretention process yourself with your preferred administrative tools. Follow thedirections in “Determining how much data to retain”.
3.1.3 Determining how much data to retain
When managinglog retention, try to ensure rapid access to the logs that Extract would requireto perform a normal recovery (not a Bounded Recovery). See “Log retention requirementsper Extract recovery mode”. If you must move the archives off the database system,the TRANLOGOPTIONS parameter provides a way to specify an alternate location.See “Specifying the archive location”.
The recommendedretention period is at least 24 hours worth of transaction data, including bothonline and archived logs. To determine the oldest log that Extract might needat any given point, issue the SEND EXTRACT command with the SHOWTRANS option.You might need to do some testing to determine the best retention time givenyour data volume and business requirements.
If data that Extract needs during processing was not retained, either in online or archived logs,one of the following corrective actions might be required:
（1） Alter Extract to capture from alater point in time for which log data is available (and accept possible dataloss on the target).
（2） Resynchronize the source and targetdata, and then start the Oracle GoldenGate environment over again.
3.1.4 Purging log archives
Make certain notto use backup or archive options that cause old archive files to be overwrittenby new backups. Ideally, new backups should be separate files with different namesfrom older ones. This ensures that if Extract looks for a particular log, itwill still exist, and it also ensures that the data is available in case it isneeded for a support case.
3.2 Specifying the archive location
If the archivedlogs reside somewhere other than the Oracle default directory, specify that directorywith the ALTARCHIVELOGDEST option of the TRANLOGOPTIONS parameter in theExtract parameter file.
You might alsoneed to use the ALTARCHIVEDLOGFORMAT option of TRANLOGOPTIONS if the format thatis specified with the Oracle parameter LOG_ARCHIVE_FORMAT containssub-directories.
ALTARCHIVEDLOGFORMATspecifies an alternate format that removes the sub-directory from the path. Forexample, %T/log_%t_%s_%r.arc would be changed to log_%t_%s_%r.arc. As an alternativeto using ALTARCHIVEDLOGFORMAT, you can create the sub-directory manually, and thenmove the log files to it.
