GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script for Ogg v12.3.x and Above (Doc ID 2653026.1)

APPLIES TO:

Oracle GoldenGate - Version 12.3.0.1.0 and later
Information in this document applies to any platform.

PURPOSE

Explain on the script that quickly retrieves all the information related to the Oracle GoldenGate Integrated Capture and Integrated Replicat configuration in an Oracle database available in the OGG_HOME

SCOPE

This note is intended for anyone interested in installing/running the healthcheck scripts available in the OGG_HOME/healthcheck directory which could be used for retrieving information about the current status of Oracle GoldenGate Integrated Capture and Integrated Replicat configuration in an Oracle database.

The scripts available in the OGG_HOME/healthcheck directory could be used across different DB versions greater than 11.2.0.3.0

If you are on a ogg version 12.2.x or below, then you need to have the scripts executed from

GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)

DETAILS

From OGG 12.3 version, the Healthcheck script is present in the healthcheck directory of OGG_HOME.

For OGG MicroService Architecture (MA), the Healthcheck would be found in /lib/sql/healthcheck

The ogghc_install.sql needs to be run for setup(one time) and ogghc_run.sql needs to be executed each time to generate the Healthcheck report!!

• ogghc_install.sql

This is the installation script that installs the necessary tables and packages needed to generate the healthcheck report. It can be installed on sys or any other user having 'SELECT ANY DICTIONARY' privilege.However, when installing as non-sys users some of the stats may not be available as they select from x$ views and other sys-access-only tables.

• ogghc_run.sql

This script should be used to generate the report. It spools the output to gghc__YYYYMMDDHH24MI.html. The default output is javascript based html output.

GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)

APPLIES TO:

Oracle GoldenGate - Version 11.2.1.0.0 and later
Information in this document applies to any platform.
Oracle Database Enterprise Edition & Standard Edition for releases 11.2.0.3 onwards.

PURPOSE

To provide a script that quickly retrieves all the information related to the Oracle GoldenGate Integrated Capture and Integrated Replicat configuration in an Oracle database.

DISCLAIMER : This script is provided for diagnosis and troubleshooting purposes only. The script has been tested and appears to work as intended.
Any modification or customization made by customer without support's approval is NOT supported by Oracle Support.

SCOPE

This note is intended for anyone interested in retrieving information about the current status of Oracle GoldenGate Integrated Capture and Integrated Replicat configuration in an Oracle database.

This report is useful to confirm that the pre-requisites for Oracle GoldenGate Integrated Capture/Replicat are met as well as to identify the database objects of interest for Oracle GoldenGate Integrated Capture/Replicat.

These scripts do not modify the Integrated Capture/Replicat configuration. Each script reports important information on the setup and operation of Integrated Capture/Replicat. This will provide a consistent basis upon which to understand what is happening in the environment and aid to diagnose issues.

Integrated Capture feature is available on combination of Oracle GoldenGate 11.2.1.0.0 and Oracle Database 11.2.0.3 onwards.

Integrated Replicat feature is available on combination of Oracle GoldenGate 12.1.2.0.0 and Oracle Database 11.2.0.4 and 12.1.0.2 onwards.

DETAILS

Scripts available

It is important to use the IC/R HC script for your Oracle database release. You can download the scripts from the following links:

Integrated Capture and Integrated Replicat Health Check script for Oracle Database 12.1.0.2

Integrated Capture and Integrated Replicat Health Check script for Oracle Database 12.1.0.1

Integrated Capture and Integrated Replicat Health Check script for Oracle Database 11.2.0.4

Integrated Capture Health Check script for Oracle Database 11.2.0.3

NOTE : Integrated Capture and Replicat Health Check Script (IC/R HC) are updated and improved frequently. It is recommended to run the latest release of IC/R HC for your combination of OGG release and Oracle DB release.

How to run the script

You must execute IC/R HC in every database where integrated extract or integrated replicat are is configured.

Health check script must be run as sysdba to access some structures. Therefore, health check script includes a connection command as follows: connect / as sysdba
Please modify it as required.

The output of the IC/R HC script must be spooled to a file for analysis.

By default, the output generated by the script is html format, so please be sure that the extension of the file generated is .html to be able to view with a browser.
To convert output to a text file viewable with a text editor, change the HTML ON to HTML OFF in the set markup command

So, a sample to run the script is:

ICR HC executes SQL statements and PL/SQL code in your database to check your configuration.

Please note that some report section may be larger than others depending on your configuration, so allow the script to complete its output

Script output

Default script output is in html format so it can be reviewed with a browser. The generated output file will prefix SRDC_OGG_HC* and have extension .htm

Output format has changed from script available for 11.2.0.3 to 11.2.0.4 onwards.
From on release 11.2.0.4 the most relevant information of the report appears at top of the report.
The information on this report refers to the output format in release 11.2.0.4 onwards.

Initially, the output shows a header about the report itself including: health check script release and database, instance and timestamp of report generated.

After the header, the output shows links to five main sections as report is generated:

• Configuration, contains generic information for the database configuration affecting Integrated processes
• Extract, contains information relevant to Integrated Extract processes defined
• Replicat, contains information relevant to Integrated Replicat processes defined
• Analysis : ICR HC performs different checks to confirm sanity of your installation
• Statistics : reports statistics for those elements of Integrated Capture and Replicar that are enabled

Report generates navigation links through sections for quick access to different information in the report.

After links to five sections, we have a new section called Summary, that quickly will show what is our configuration and what are the potential problems that our installation may be suffering.
It is good practice to start checking this Summary section as it reports key messages concerning environmental problems.

Configuration

In Configuration section, you can review the configuration of different components involving integrated processes. The information returned on the configuration report is nearly static, so you will retrieve information for the defined components even if they are not up and running.

Different sections of configuration sections are:

Database, which provides generic information about the database where the script has been executed. It will generate information about minimal supplemental logging settings, archivelog, platforms, current scn, instance information, registry information and registry history. These help to identify upgrade paths, NLS settings and DB GoldenGate administrators.

Relevant parameters to review are :

• compatible, The minimum OGG extract release level requirement is 11.2.0.0.0, but a version level of at least 11.2.0.3 is recommended to provide full support on datatypes. If this is not set, you can see in your extract report file a warning like the following:

WARNING OGG-02064 Oracle compatibility version 11.2.0.0.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.

• streams_pool_size, Extract integrates with the database logmining server, which passes logical change records (LCR) directly to Extract. Extract does not read the redo log. LCRs are stored in streams pool area.

Queue

When working with integrated processes, a queue owned by the GoldenGate administrator user is created with an internal name called OGG$Q_.
In this section we can see queues and subscribers associated that potentially could be used by an integrated process.

Administrators, in this section the report will list the database users that have been granted privileges provided through dbms_goldengate_auth.grant_admin_privilege procedure, required for setting up Integrated processes.

Bundle, shows Bundle Patch installed in your Exadata Database installation

Extract

Configuration

When an extract is registered through the ggsci command REGISTER EXTRACT, a capture process is created in the database.
When registering with DATABASE option an Integrated Capture is created; in this case script will report the capture with extract mode "Integrated Capture". An integrated capture is started and mines redo log files.

In this section of the report, you can identify the extract processes registered with the database and it is possible to know if that OGG extract process is currently attached to the database or not. We have the following status:

• ATTACHED, Integrated Capture process is up and attached to GG extract process.
• DETACHED, Integrated Capture process is waiting for the GG extract process to be started.
• ABORTED,Integrated Capture process has aborted as the associated GG extract process has abended.

Capture

On Capture section we got details about the Capture process associated to extract process registered in the database regardless if it is an integrated capture or not.
Among other relevant information available, you can check different scns used by integrated capture, rulesets and rules associated to integrated capture process, attributes, parameters,supplemental logging information, etc..

The status (ENABLED, DISABLED, ABORTED) of the capture process is displayed along with the SCN. If the status of the Capture is ENABLED, it will be necessary to check the Capture state on Statistics section to know the real state of the Capture.

In some specific circumstances, it is possible to observed that the status of the extract process displayed on ggsci for an integrated capture is STOPPED despite it has been recently started and there being no errors on report file. However, the status of the process reported by the script describes the integrated capture process as ENABLED. This is because the integrated capture process is in a starting phase and still not serving LCRs to extract. Again to obtain details of the phase of integrated capture start, it will be necessary to check states in the Statistics section.

In the section 'Registered Log Files for Capture', we can confirm that there are no gaps in the sequence of logs that have been registered for integrated capture. Otherwise, the integrated capture will stop and wait for the right sequence to be registered. If the source database is a RAC, there should not be gaps in either thread. Also, based on the capture_scn value observed above in the report, we can determine how near we are to the latest registered archiver.

If the number of files reported on "Registered Log Files For Capture" becomes very large, please consider modifying checkpoint_retention_time capture setting according to Doc ID 418755.1 - Master Note for Streams Recommended Configuration.

Statistics

The statistics section only reports information for Integrated Capture processes that are enabled. The information refers to the specific moment when this information is obtained.
In this section it is possible to review different details and statistics related to integrated capture and associated logminer session.
To know what an enabled IC component is doing you have to check its state on statistics section, but remember that this state is specific for the moment running the query, therefore its state may change immediately.
Comparing different ICR HC outputs you can verify if your IC component is flowing properly or its stuck.

Replicat

Configuration

When an replicat is defined as INTEGRATED an apply process is created on the database. In this section we can link OGG replicat process with its associated apply process and observe the status of the link, which can be:

• ATTACHED, associated apply process is up and attached to OGG replicat process.
• DETACHED, associated apply process is waiting for the GG replicat process to be started.
• ABORTED, associated apply process has aborted as the associated GG replicat process has abended.

Replicat

In this section you can find static information about the apply process like parameters, handlers or conflict detection settings defined though replicat parameter file.
Also you can find information about transaction in error queue or instantiation scns defined.
In addition to this you can find information about scns that define the progress of the apply process.

Statistics

The statistics section only reports information for Integrated Replicat processes that are enabled. The information refers to the specific moment when this information is obtained.
Comparing different ICR HC outputs you can verify if your IR component is flowing properly or its stuck.

You can find detailed information for Apply Reader, Component and Server processes defined. You can check total transactions processes, What transactions are being processed and know what transaction is applying every server.

Analysis

In Analysis section, ICR HC reports the outputs of checks that are done to verify adequate operation of integrated processes.
Also it shows some information that helps to understand the behaviour of different components during last 30 minutes or last day.
So, the following subsections are available:

History, it reports information operation that has occurred within last day.The information returned is useful to study if the setting for memory, capture and apply are the right ones for the volume of data or may help to identify the peaks or valleys times across a day. The aparts reported are:

• Streams Buffered Queue History for last day
• Streams Buffered Subscriber History for last day
• Streams Capture History for last day
• Streams Apply History for last day

Notification, a message will be written if any Integrated Capture Replicat component is disabled or aborted avoiding the correct data flow. Additionally it checks for minimum setting of different initialization parameters and write a message if streams pool area is exhausted. A working environment should not report messages.

Objects, provides information about objects ids and object types related to Integrated processes.

Checks, IC/R HC will warn if there is any wrong configuration and perform some configuration suggestions that may improve performance. If no issues are detected no messages written. There are 3 types of messages to be written:

• ERROR, that will be written if existing configuration will prevent the integrated capture to work properly.
• WARN, written when a misconfiguration is detected, but do not prevent the system to flow. Usually correcting this issues improves performance.
• INFO, written when existing configuration can be modified to improve performance.

Performance checks, in this section IC/R HC checks if some predefined threshold for working Integrated Capture and Replicat components have been exceeded. If any message is written, it can be evidence of an existing problem.

Wait Analysis, IC/R HC will print per Integrated Capture Replicat component started, the percentage wait time for every wait event that an started component has been waiting on in last 30 minutes. To analyze a problem, only consider the waits for which the BUSY column is set to YES. If the column is set to NO, it refers to an idle wait event. When wait event is not set and busy is set to YES, the process on wait on CPU or waiting for CPU.

Topology, allows reader to quickly identify the elements involved in integrated processes.

NOTE: Performance checks and Wait Analysis report are only executed on processes ENABLED. No performance checks nor Wait Analysis report are executed for processes on status STOPPED or ABORTED.

Statistics

On statistics section we can verify buffered messages associated to integrated processes.

Also we have a link to statistics for Extract and Replicat reported below "Extract > Statistics" and "Extract > Replicat"

Finally we can see a list of failed transaction that integrated replicat has failed to apply and a summary of tables affected by error type.

Important Information : From OGG 12.3 version, the Healthcheck script is present in the healthcheck directory of OGG_Home. The ogghc_install.sql needs to be run for setup and ogghc_run.sql needs to be run to generate the Healthcheck report . Hence, we can use this script to generate the healthcheck for Oracle Database 12.2 and above.

​ For OGG MicroService Architecture, the Healthcheck would be found in /lib/sql/healthcheck

Still have questions ?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion via My Oracle Support GoldenGate, Streams and Distributed Database Community
Enjoy a short Video about Oracle´s Support Communities - to quickly understand it´s benefits for you right now (http://bcove.me/tlygjitz)

REFERENCES

Attachments IC HC for Oracle DB 11.2.0.3(127.4 KB)OGG_11204(209.25 KB)OGG_12101(211.32 KB)OGG_12102(211.21 KB)

巡检结果

内容比较详细：

我就不一一截图了。