What are the required Database Patches for Oracle GoldenGate Integrated Capture and Delivery?6/23/2017 Please refer to the Oracle Support Note: Doc ID 1557031.1 Oracle GoldenGate -- Oracle RDBMS Server Recommended Patches.
0 Comments
In Oracle MAA (Maximum Availability Architecture), Oracle Database is protected by Oracle Active Data Guard (ADG) to move users from the primary database to the standby database as planned (switchover) or unplanned (failover) [1]. The process is also called the Data Guard Role Transition. During the role transition, Oracle GoldenGate needs to seamlessly follow the transition to maintain non-stop services. In this blog, l will briefly summarize some key concepts of Oracle GoldenGate failover configuration.
First, the automatic failover configuration requires the following:
Second, the most critical parts of this configuration include:
Third, during the role transition, the Oracle GoldenGate extract will abend after the old primary is no longer available. However, after the new primary becomes available, the extract restarted by Oracle GoldenGate Manager will resume the replication automatically. Don't worry about the Oracle GoldenGate extract doesn't replicate at the beginning. The Oracle GoldenGate extract will wait until the new standby database instance is up before it starts to replicate the transactions.
Can I use ACFS instead of DBFS to store Oracle GoldenGate for failover support?
Yes, you can, but it's not recommended. This is because ACSF is not integrated with role transitions or CRS and there are a lot of manual processes and limitations with ACFS.[2]
Resources
If you only have terminal access (such as an Amazon AWS EC2 environment) you need to install Oracle GoldenGate for Oracle in OUI silent mode. The following are the steps.
Step 1: Creating the response file You can update the response file available at the $DOWNLOAD_HOME/Disk1/response folder. The following is an example. #################################################################### ## Copyright(c) Oracle Corporation 2014. All rights reserved. ## ## ## ## Specify values for the variables listed below to customize ## ## your installation. ## ## ## ## Each variable is associated with a comment. The comment ## ## can help to populate the variables with the appropriate ## ## values. ## ## ## ## IMPORTANT NOTE: This file should be secured to have read ## ## permission only by the oracle user or an administrator who ## ## own this installation to protect any sensitive input values. ## ## ## #################################################################### #------------------------------------------------------------------------------- # Do not change the following system generated value. #------------------------------------------------------------------------------- oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_sc hema_v12_1_2 ################################################################################ ## ## ## Oracle GoldenGate installation option and details ## ## ## ################################################################################ #------------------------------------------------------------------------------- # Specify the installation option. # Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and # ORA11g for installing Oracle GoldenGate for Oracle Database 11g #------------------------------------------------------------------------------- INSTALL_OPTION=ORA11g #------------------------------------------------------------------------------- # Specify a location to install Oracle GoldenGate #------------------------------------------------------------------------------- SOFTWARE_LOCATION=/home/ec2-user/gg #------------------------------------------------------------------------------- # Specify true to start the manager after installation. #------------------------------------------------------------------------------- START_MANAGER= #------------------------------------------------------------------------------- # Specify a free port within the valid range for the manager process. # Required only if START_MANAGER is true. #------------------------------------------------------------------------------- MANAGER_PORT= #------------------------------------------------------------------------------- # Specify the location of the Oracle Database. # Required only if START_MANAGER is true. #------------------------------------------------------------------------------- DATABASE_LOCATION= ################################################################################ ## ## ## Specify details to Create inventory for Oracle installs ## ## Required only for the first Oracle product install on a system. ## ## ## ################################################################################ #------------------------------------------------------------------------------- # Specify the location which holds the install inventory files. # This is an optional parameter if installing on # Windows based Operating System. #------------------------------------------------------------------------------- INVENTORY_LOCATION=/home/ec2-user/gg/inventory #------------------------------------------------------------------------------- # Unix group to be set for the inventory directory. # This parameter is not applicable if installing on # Windows based Operating System. #------------------------------------------------------------------------------- UNIX_GROUP_NAME=ec2-user
Step 2: Run OUI with Silent Mode
Make sure you add swap space for the installation(How to Add SWAP Space for GoldenGate Installation?) and run the following command:
./runInstaller -silent -nowait -responseFile /home/ec2-user/gg/fbo_ggs_Linux_x64_shiphome/Disk1/response/ggorcl.rsp
After the installation, run the following command as the root user:
[ec2-user@ip-10-184-238-104 Disk1]$ sudo /home/ec2-user/gg/inventory/orainstRoot.sh Changing permissions of /home/ec2-user/gg/inventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /home/ec2-user/gg/inventory to ec2-user. The execution of the script is complete.
Created 1/11/2016, Last Updated 6/8/2017
When Integrated Replicat (IR) has performance issues, you will see long lag time in GoldenGate replication. In this blog, let me discuss some considerations when tuning the performance of integrated replicat.
More Info
You can learn more about the basics of Integrated Replicat in the Integrated Replicat blog.
Handling Large Transactions
In Integrated Replicat, operations in one transaction are applied by a single applier. Therefore, there is no parallelism within a transaction. In other words, multiple smaller transactions work better that a single large transaction in Integrated Replicat. To optimize the parallel processing, you then would need to avoid large transactions in the first place. However, if you can't break up the large transactions, you can use the Eager Apply feature in Oracle GoldenGate. In the DBOPTIONS INTEGRATEDPARAMS, you can configure EAGER_SIZE (in the number of LCRs) [5]. This is the threshold when Oracle GoldenGate inbound server starts to apply transactions before the commit record is received. The default EAGER_SIZE threshold is 9500. With the EAGER_SIZE configured, you technically break up large transactions into smaller ones, which are also called eager transactions. Integrated Replicat can create additional apply servers to handle outstanding eager transactions automatically and thus improves the performance. Please also make sure you the STREAM_POOL_SIZE is properly configured on the target database to avoid waiting for memory issues when having large transactions.
Does Oracle GoldenGate Integrated Replicat breakup the transactions?
Answer: No, Oracle GoldenGate Integrated Replicat keeps the transaction boundaries. However, the transaction committed orders are not maintained. Integrated Replicat applies transactions asynchronously. Transactions that do not have inter-dependencies can be safely executed and committed out of order to achieve fast throughput. Transactions with dependencies are guaranteed to be applied in the same order as on the source.[1]
You can also consider use BATCHSQL to optimize the batch processing within the transaction.
More Info
You can learn more about BATCHSQL in the How to Use BatchSQL.
Handling the Direct Apply
For Oracle GoldenGate Integrated Replicat, the following activities lead to direct apply [1]:
When this happens, you will see the delivery process slows down. To improve the performance, you first would prevent the barrier operations from happening at the beginning. Please evaluate the solution with your application developers and find out if those operations are really necessary. Second, to minimize the impacts, use smaller transactions. Third, you can create a new replicat to apply the barrier transactions separately. In summary, a large number of barrier transactions in an integrated replicat process will cause performance issues because of the serializing the apply process. Try to avoid it, put it into small transactions or a separate replicat. All of these will help in this case.
Why my replicat has long lag after a large transaction is committed?
Answer: Yes, this is common. You can try EAGER_APPLY and BATCHSQL to tune the performance. Try to avoid direct apply operations in large transactions.
In summary, Integrated Replicat is optimized for large number of small transactions, to tune its performance you can:
Let me know if you have anything to add.
Resources
I got a question today on if migrating off Oracle Streams need to be considered. The answer is yes. Oracle Stream has been deprecated since Oracle Database 12.1 release. You can now use Oracle GoldenGate to replace all replication features of Oracle Streams.
Oracle provides a tool to simplify the migration. Please check the Oracle Streams to GoldenGate Migration Utility (Doc ID 1912338.1) for the detailed information. You can also refer to Oracle GoldenGate Best Practices: Oracle Streams to Oracle GoldenGate Conversion (Doc ID 1383303.1) for the best practices. Thomson Reuters is one of the customers who had evaluated and completed the migration of its Active-Active database replication solution from Oracle Streams to Oracle GoldenGate. If you'd like to learn more about the differences between Oracle Streams and Oracle GoldenGate, you can also read the following Oracle Notes: Comparison Between Oracle Streams and GoldenGate (Doc ID 1437736.1). The followings are some case studies:
Yes, but this is only limited to the Oracle GoldenGate classic capture. Oracle GoldenGate integrated capture can only capture from a primary database that is open for read/write. Resources
Oracle GoldenGate uses LogMiner [1] for both Integrated Capture and Delivery. Let's talk about how you optimize the logminer for Oracle GoldenGate replications.
STREAM_POOL_SIZE STREAM_POOL_SIZE [4] is a database initialization parameter and is part of Oracle Database SGA (System Global Area). It is the memory used by logminer server. Therefore, you need to allocate enough memory size Oracle GoldenGate Replication. By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB. As an Oracle GoldenGate best practice, it is recommended to keep an additional 25% of memory for other processing.[3][5] Therefore, if you can calculate the STREAM_POOL_SIZE as follows:
Integrated Extract/Replicat- MAX_SGA_SIZE and PARALLELISM
The following are two extract parameters that can be used to optimize the logminer processing:
The following is an example, we have two extracts configure each has parallelism as 2 and the MAX_SGA_SIZE to be 1 GB:
Extract: TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2) Replicat: DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)
The STREAM_POOL_SIZE is:
You can change the STREAM_POOL_SIZE as follows:
SQL> alter system set STREAMS_POOL_SIZE=5000M scope=both;
Resources
LOB objects, either BLOBs or CLOBs, have impacts on the replication performance. Let's explain what you needs to be considered when tuning the GoldenGate replication performance.
Note:The discussion is only on the integrated capture.
In Line or Out of Line LOB
In Oracle Database, LOB can be stored in line or out of line. If the LOB data is less than 4000 bytes, then it can be stored in line or on the same database page as the rest of the row data. This means Oracle GoldenGate can capture from the redo log. If LOB data is larger than 4000 bytes, LOB data is stored out of line. Oracle GoldenGate will fetch the data from the database. Fetching operation is in general slower than capturing from the redo log.
Secure File LOB
Oracle GoldenGate in general captures Secure File LOB from redo log . However, if the SECUREFILE LOBs are de-duplicated in 11g, modified using DBMS_LOB.FRAGMENT_* procedures or NOLOGGING LOBs is used, Oracle GoldenGate then needs to fetch the data from the source table. Again, fetching operation is in general slower than capturing from the redo log. ch the data from the source table.[2]
Basic File LOB
Oracle GoldenGate captures from redo log.
In summary, you need to reduce the fetching operation in Oracle GoldenGate capture to improve the performance.
Resources
You need to configure Oracle Databases to enable Oracle GoldenGate replications. In general, the steps includes:
ENABLE_GOLDENGATE_REPLICATION
To enable Oracle GoldenGate replication, you need to set ENABLE_GOLDENGATE_REPLICATION to be true for on both source and target database. ENABLE_GOLDENGATE_REPLICATION controls many services that are needed by Oracle GoldenGate replication but most important services are:
Oracle GoldenGate provides Direct Load to perform initial load (also called instantiation). The traditional the direct load with RMTTASK doesn't have a checkpoint, nor it has the trail files to keep the extracted data. It's not resilient to failures or recoverable from failures. The best practice is to create a recoverable initial load using initial load extract and a regular replicat in the replication. Let me explain how you can configure this through an example.
When to use RMTTASK and when to use RMTFILE in the intial load?
Answer: If your network is stable and secure, you can use RMTTASK. The reason is that RMTTASK doesn't stage captured data into files nor it has checkpoints. In the case of any failure, you have to start from scratch. The pro side is that RMMTTASK approach can give you better performance by using bulk loading APIs like SQL*Loader. However, it doesn't offer encryption and has limitations on datatypes such as lobs and UDTs. For other cases, you would use RMTFILES.
1. Create an Initial Load Extract
You can start with creating an initial load extract. The following is an example parameter file and the commands to create the extract. When using RMTFILE to creating trail files from initial load extract, there is a restriction on file has a 2GB size limit. For large initial load tasks, you need to use the MAXFILES and MEGABYTES options of the RMTFILE to create multiple trail files. When creating the extract, you need to use SOURCEISTABLE to define it as an initial load extract. extract extinitd useridalias awsuser RMTHOST 129.144.0.33, MGRPORT 7865,socksproxy 127.0.0.1:9000 ENCRYPTTRAIL AES192 RMTFILE ./dirdat/ld, purge, maxfiles 100, megabytes 2048, format release 12.2 TABLE awsuser.member; GGCSI> add extract extinitd, sourceistable
After you create the extract, you can start it. The process runs and completes automatically after the data are captured from the source table.
GGCSI> start extract extinitd GGCSI> view report extinitd
An example result is shown as follows:
*********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2016-12-16 20:55:37 (activity since 2016-12-16 20:55:32) Output to ./dirdat/ld: From Table AWSUSER.MEMBER: # inserts: 10 # updates: 0 # deletes: 0 # discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 1365
2. Create a Regular Replicat
You then can create a regular replicat shown as follows: replicat repinitd useridalias ggadmin_tgt discardfile ./dirrpt/repinitd.dsc,append megabytes 50 map awsuser.member, TARGET pdb1.pmdemo.member; add replicat repinitd exttrail ./dirdat/ld, checkpointtable PDB1.pmdemo.chkpttbl
After run the replicat, you can see the result of the replication by running the stats command in GGSCI:
GGSCI> stats repinitd Sending STATS request to REPLICAT REPINITD ... Start of Statistics at 2016-12-16 21:10:13. Replicating from AWSUSER.MEMBER to PDB1.PMDEMO.MEMBER: *** Total statistics since 2016-12-16 21:10:10 *** Total inserts 10.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 10.00 *** Daily statistics since 2016-12-16 21:10:10 *** Total inserts 10.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 10.00 *** Hourly statistics since 2016-12-16 21:10:10 *** Total inserts 10.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 10.00 *** Latest statistics since 2016-12-16 21:10:10 *** Total inserts 10.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 10.00 End of Statistics.
Please make sure you stop the replicat after it's task is completed.
|
GoldenGate for Oracle DBInformation, Tip and Trick to use Oracle GoldenGate for Oracle Database. Links
- Installing and Configuring Oracle GoldenGate for Oracle Database (12.2) - Oracle Support Policy Archives
March 2018
Categories
All
|
Jinyu's Blog |
|