Oracle Goldengate General recommendations
In this blog, We are going to show some general recommendation for Goldengate.
DBLOGREADER
A new DBLOGREADER option was added to TRANLOGOPTIONS to enable Extract to use a newer API for capturing from an Oracle instance using ASM storage. This feature is available as of Oracle 10.2.0.5. The API enables extract to use a read buffer size of up to 4 MB in size and uses the database server to access the redo and archive logs, so that the extract doesn’t have to access the logs via ASM. A larger buffer may improve the performance of extract when redo rate is high. You can use the new DBLOGREADERBUFSIZE option of TRANLOGOPTIONS to specify a buffer size.
Supplemental Logging
To extract the committed transactions from the source Oracle database's online redo logs, as a minimum the database must be configured for supplemental logging on Primary Key columns. Enabling Supplemental logging at the source database side at the table level is required to ensure that the changes which are happened to the columns which are supplemental logging enabled will be applied successfully at the target database. With the help of these additional columns, oracle decides the rows which need to be updated on the destination side.
If the table has primary key or unique key column defined, only the column which are involved in primary key or unique key column will be registered in the redo logs along with the actual column that has changed. If the table does not have any primary key or unique key defined, oracle will write all columns of the changed row data into the redo log file.
To check supplemental logging is enabled or not, run the below sql:
SQL> SELECT supplemental_log_data_min FROM v$database;
To enable minimum supplemental logging at database level, run the below sql:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Below are the sqls to add supplemental logging at all levels, run the below sqls:
SQL> alter database add supplemental log data (all) columns;
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (unique) columns;
SQL> alter database add supplemental log data (foreign key) columns;
SQL> alter system switch logfile;
Note: Supplemental logging at all levels increases the amount of redo log generation for very large databases having multiple schemas, the archive log destination disk space may have to be increased. To minimize the amount of redo log generation for large databases, it will be sufficient to add supplemental logging for specific schema level only.
Supplemental logging needs to be added at schema level for all replicated tables using golden gate command interface,
GGSCI>ADD TRANDATA SCHEMAOWNER.*
To check whether supplemental logging enabled at the schema level, run the below command:
GGSCI>INFO TRANDATA SCHEMAOWNER.*
Trail File Size Recommendation
Default trail file size is 10MB which is very small for high volume busy systems. During high throughput processing times, the Golden Gate extract processes will be generating multiple trail files within few minutes, causing extra file management overhead, increasing CPU utilization, decreasing throughput, and increasing latency. The general recommendation is to set the size of the trail file between 100MB to 250MB so that it switches between 5 to 15 minutes during “average” loads and doesn’t switch more frequently than every minute during peak loads.
Trail File Storage Recommendation
The general recommendation is to extract to local trails and pump the trails to the target server. The Golden Gate manager process can be configured to purge the trails after they’ve been processed. The cummins dba team can determine the trail retention policy that you want the manager to utilize. Keeping trail that has already been processed can provide more options in recovery scenarios in the event of server failures or database issues. Typically customers will maintain between 12 hours to 2 days of trail files on the source (locally) and on the target (remotely). Key to be aware is that a trail file will not become a candidate to be purged unless the processes writing to or reading from it have been completed processing it.
Maintaining local trails instead of just remote trails protects the extract process from interruption. In Golden Gate configuration with only a trail on the target system, there is nowhere on the source system to store data that the Extract processes. If the network or the target system becomes unavailable, the primary Extract would lose its connection with the target and would ABEND. However, with a trail and data pump on the source system, captured data can be moved to disk, preventing the ABEND and allowing the extract to stay current with the redo log of the source database instance. When connectivity is restored, the data pump extracts the data from the source trail and sends it to the target system(s). If the RAC node, the extract is running on fails, extract can run from other node and store data locally on a shared disk subsystem between all nodes. Trail data will still be sent to the remote target destination.
Archive log Retention Storage Recommendation
To estimate the storage requirements for archive log files on the database server the general rule of thumb will be to keep a minimum of 3days of archived redo log files to cover weekend maintenance window. Run the below query to determine the maximum size of archive log files generated per day, and to estimate the disk space required for Archivelog destination based on the output.
Provides a summary of log generated per day for each instance
SELECT INST_ID, TO_CHAR(FIRST_TIME, 'MM-DD') MM_DD, SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024) LOG_GB FROM GV$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE – 15 GROUP BY INST_ID, TO_CHAR(FIRST_TIME, 'MM-DD') ORDER BY 2,1;
Run the below query to determine the periods when maximum archive log files generated and estimate the disk space required for Archivelog destination based on the output.
Provides a summary of log generated per hour
SELECT TO_CHAR(FIRST_TIME, 'MM-DD') MM_DD, TO_CHAR(FIRST_TIME, 'HH24') HR_24, SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024) LOG_GB FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 15 GROUP BY TO_CHAR(FIRST_TIME, 'MM-DD'), TO_CHAR(FIRST_TIME, 'HH24') ORDER BY 1, 2;
Remote Trail Processing Options
When the pump replicates the trail files from the source server to the target server, there are a few options:
• Compression – trails can be compressed as they are sent across the network in order to save some bandwidth, however, this will increase the CPU required by the pump process and potentially increase the latency. While it may generally be a practice to compress logs to the standby with Oracle Data Guard, this is generally not recommended for Golden Gate. Golden Gate’s trails are already in a compressed binary format. Plus extract has already scraped the log record down to just the essential data required to do table based transactional replication. Typically, when processing 100% of all application table activity, our trail files will be 30-50% of the database log file. So, as in our Phoenix configuration, where we’re only processing a subset of tables, we’re only capturing a subset of the log records anyway.
• Encryption – trails can be encrypted before transmission across the network and decrypted on the target side. As with compression, this will add CPU overhead and increase latency so the general recommendation is to not encrypt the trails unless it’s required for sensitive data or you’re processing in an unsecured network and sent outside the firewall protected datacenter environment. As indicated before the trail is already in a compressed binary format so it’s not easily viewable.
• TCP Buffer Size – the default buffer size used by the pump process is 28 KB. This can be increased to 2 MB. The way the trail data is “pumped” across the network is to use an acknowledgement protocol so the transmission is “throttle” so that a single pump process could never consume much of a healthy network bandwidth. In most cases, the default 28 KB TCP buffer size is sufficient so that you do not encounter. latency in the pump process (i.e. 1 to 2 seconds max). If you find that your pump process is encountering more latency that desired, you can increase the TCP buffer size. Values that have been used in a couple cases that I’ve been involved with have been 128 KB and 256 KB. If you find you want to increase the Golden Gate TCP buffer size, you also need to assure that the servers TCP layer has their TCP window sizes increased to handle what you set the Golden Gate TCP buffer size to. If the server’s TCP buffer size is smaller than what Golden Gate delivers to it to be sent across the network, then the server will just break up the Golden Gate buffer into multiple packets to send across the network and you won’t get full advantage of increasing the Golden Gate buffer size. The parameter that controls the Golden Gate buffer size is TCPBUFSIZE of the RMTHOST parm.
Backup of Oracle GoldenGate subdirectories
The binaries and parameter files for Golden gate is required to restore the golden gate configuration quickly. To ensure quick recovery incase of a filesystem failure, Golden gate home directory needs to be backed up atleast once every day, along with its subdirectories. This section describes the subdirectories of the Oracle GoldenGate installation directory that needs to be backed up .
|
Directory |
Description |
|
dirprm |
The default location for Oracle GoldenGate
parameter files created by Oracle GoldenGate users to store run-time
parameters for Oracle GoldenGate process groups or utilities. Written in
external ASCII format. File name format is .prm or mgr.prm. |
|
dirdat |
The default location for Oracle GoldenGate trail
files and extract files that are created by Extract processes to store
extracted data for further processing by the Replicat process or another
application or utility. Written in internal Oracle GoldenGate format. File
name format is a user-defined two-character prefix followed by either a
sixdigit sequence number (trail files) or the user-defined name of the
associated Extract process group (extract files). Do not edit these files. |
|
dirchk |
Contains the checkpoint files created by Extract
and Replicat processes, which store current read and write positions to
support data accuracy and fault tolerance. Written in internal Oracle
GoldenGate format. File name format is . where is a sequential number
appended to aged files and is either cpe for Extract checkpoint files or cpr
for Replicat checkpoint files. Do not edit these files. |
|
Dirrpt |
The default location for process report files
created by Extract, Replicat, and Manager processes to report statistical
information relating to a processing run. Written in external ASCII format.
File name format is .rpt where is a sequential number appended to aged files. |
(NOTE: Do not define “NOVALIDATE” for a table because even though it may have a PK or UI defined on it, Golden Gate will not use those columns since the database is not assuring uniqueness.). If there isn’t a PK or UI defined, Golden Gate will use every column of the table to ensure uniqueness. What that implies is that it will capture in the trail record the full before image of all columns of the table and the full after image of all columns of the table. On the target the WHERE clause values will comprise of the before values and the SET clause values will comprise of the after values. This is Golden Gate’s default behavior for any table that doesn’t have a PK or UI. If the table doesn’t encounter many UPDATEs or DELETEs, then this will likely not be a significant performance issue. However, if the table does have many UPDATEs and DELETEs processed on it (i.e. it’s a very volatile table), then this could cause a throughput challenge during replication. Golden Gate has an option that allows you to configure in the extract and replicat a set of columns for it to use to ensure uniqueness instead of using all columns. The parameter is called KEYCOLS. If this is used, please ensure that the supplemental logging for that table matches the columns specified in KEYCOLS. When KEYCOLS is used, the extract and replicat will only capture the before images of those columns and after images of any of the columns that changed. This can significantly reduce the amount of data that needs to be captured and processed. KEYCOLS can be thought of as a “pseudo PK” for Golden Gate’s purpose and doesn’t require you to change the definition of the table (i.e. force you to add a PK or UI).
Refrential integrity constraints
Oracle Golden Gate only captures and moves committed database transactions and carefully ensures the integrity of data as it is moved from the source database and applied to target databases. The Delivery process applies each database change in the same order as it was committed in the source database to provide data and referential integrity. In addition, it applies changes within the same transaction context as they were on the source system for consistency on the target. The current version of Golden gate 11.1.1.1 has an option to handle integrity constraints on the target:
The Replicat parameter DBOPTIONS with the DEFERREFCONST option to delay checking and enforcement of integrity constraints until the Replicat transaction commits.
For earlier Oracle versions10g or heavy transactional database, you must disable triggers and integrity constraints or alter them manually to ignore the Replicat database user.Disabling of referential integrity constraints on the target database to all allow child records to be populated before their parents, thus increasing throughput of the delivery process.
Golden gate will always commit transactions in the same order as the source, so data integrity will be preserved. For High volume busy transactional systems, disabling the Foreign key constraints on the target side significantly improves the performance of the replicats.
In an active/passive configuration Triggers and ON DELETE CASCADE constraints generate DML operations that can be replicated by Oracle Golden Gate. To prevent the local DML from conflicting with the replicated DML from these operations, replicat DBOPTIONS parameter with the SUPPRESSTRIGGERS option are used to disable the triggers for the Replicat session and the triggers on the target side are not disabled. Cascade delete constraints for an active/active configuration must be rewrote to be only a FK constraint and the delete logic can be modified as a delete trigger and to exclude golden gate XXOGG schema. In this way the FK integrity can be maintained without the errors of the cascade delete constraints. For earlier versions of Goldengate 10.2.0.5 and below you can modify the triggers to ignore DML operations that are applied by Replicat or you may disable ON DELETE CASCADE constraints and use a trigger on the parent table to perform the required delete (s) to the child tables. Create it as a BEFORE trigger so that the child tables are deleted before the delete operation is performed on the parent table. This reverses the logical order of a cascaded delete but is necessary so that the operations are replicated in the correct order to prevent “table not found” errors on the target.
You can learn more about oracle database administration:
Pro Oracle GoldenGate for the DBA
Oracle Security Tools Installation and Configuration
==========================================================
Please check our other blogs for GoldenGate.
Comments
Post a Comment
Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !