Recently added to the oracledi project over at java.net is an adaptor enabling Oracle GoldenGate (OGG) to send data to Elasticsearch. This adds a powerful alternative to [micro-]batch extract via JDBC from Oracle to Elasticsearch, which I wrote about recently over at the Elastic blog.

Elasticsearch is a ‘document store’ widely used for both search and analytics. It’s something I’ve written a lot about (here and here for archives), as well as spoken about – preaching the good word, as it were, since the Elastic stack as a whole is very very good at what it does and a pleasure to work with. So, being able to combine that with my “day job” focus of Oracle is fun. Let’s get started!

From the adaptor page, download the zip to your machine. I’m using Oracle’s BigDataLite VM which already has GoldenGate installed and configured, and which I’ve also got Elasticsearch already on following on from this earlier post. If you’ve not got Elasticsearch already, head over to elastic.co to download it. I’m using version 2.3.1, installed in /opt/elasticsearch-2.3.1.
Ready …

Once you’ve got the OGG adaptor zip, you’ll want to unzip it — a word of advice here, specify the destination folder as there’s no containing root within the archive so you’ll end up with a mess of folder and files in amongst your download folder otherwise:

For gg.handler.elasticsearch.host and gg.handler.elasticsearch.port I left the defaults (localhost / 9300) unchanged – update these for your Elasticsearch instance as required. Note that Elasticsearch listens on two ports, with 9200 by default for HTTP traffic, and 9300 for Java clients which is what we’re using here.

Steady …

Run ggsci to add and start the replicat using the provided res configuration (res = Replicat, ElasticSearch, I’m guessing) and sample trail file (i.e. we don’t need a live extract running to try this thing out):

If you’re using Elasticsearch, you may well be doing so for the whole Elastic experience, using Kibana to view the data:

ogges02

and even start doing quick profiling:

ogges03

One issue with the data that’s come through in this example is that it is all string – even the dates and numerics (AMOUNT, PRICE), which makes instant-analysis in Kibana less possible.
Streaming data from Oracle to Elasticsearch

Now that we’ve tested and proven the replicat load into Elasticsearch, let’s do the full end-to-end. I’m going to use the same Extract as the BigDataLite Oracle by Example (you can see my notes on it here if you’re interested).

You’ll note that the primary key (MOVIE_ID) has been correctly identied as the unique document _id field. The _id is now where things begin to get interesting, because this field enables the new OGG-Elasticsearch adaptor to apparently perform “UPSERT” on documents that already exist.

To doublecheck this apparent method of handling of the data, I first wanted to validate what was coming through from OGG in terms of the data flowing through from the extract. To do this I hooked up a second replicat, to Kafka and on to Logstash into Elasticseach (using this method), and then compared the doc count in the two relevant indices (or strictly speaking, the mapping types, corresponding to each index).

The index loaded from the OGG-Elasticsearch Adaptor has only two documents still, whilst the other route has three. If we look at what’s in the first of these (movie, loaded by OGG-Elasticsearch) for movie_id=1:

You can see it’s the latest version of the row (TITLE=Foobar). In the second index, loaded from the change record sent to Kafka and then on through Logstash, there are both the before and after record for this key:

My document counts reflect what I’d expect — the OGG-Elasticsearch adaptor deleted the record from Elasticsearch, whilst the Kafka route just recorded another change record, of op_type=’D’ this time.

“key” : “logs”,
“doc_count” : 4
}, {
“key” : “movie”,
“doc_count” : 1

Summary

This adaptor is a pretty smart way of mirroring a table’s contents from one of the many RDBMS that GoldenGate supports as an extract source, into Elasticsearch.

If you want to retain history of changed records, then using OGG->Kafka->Logstash->Elasticsearch is an option.

And, if you don’t have the spare cash for OGG, you can use Logstash’s JDBC input mechanism to pull data periodically from your RDBMS. This has the additional benefit of being able to specify custom SQL queries with joins etc – useful when pulling in denormalised datasets into Elasticsearch for analytics.

In Oracle database 12c, DDL replication does not require any setup of triggers as it is natively supported at the database level.

So none of the marker, ddl_setup or any of the other scripts need to be run. All that is required is including the “DDL INCLUDE MAPPED” parameter in the Extract parameter file as shown in the last step.

Run the role_setup.sql script. Provide OGG_USER schema name, when prompted.

SQL> @role_setup.sql

Then grant the ggs_ggsuser_role to the OGG_USER.

SQL> grant ggs_ggsuser_role to OGG_USER;

Run the ddl_enable.sql script as shown in below command:

SQL> @ddl_enable;

Run the ddl_pin.sql script as shown below.

SQL> @ddl_pin OGG_USER;

Configure Extract Process with DDL Replication

The following extract ESRC01 was configured previously. Adding “DDL INCLUDE MAPPED” enables extracting the DDL which ran in the database. Here the “MAPPED .. TABLE” are all tables specified in [schema_name].*.

On restart of the ESRC01 process all DDL on the speicfied tables will be picked up and placed in the trail file for applying to the destination database.

Monitoring lag in GoldenGate has always been an important part of monitoring GoldenGate. Lags are reported in several ways. When using the ggsci command lag only the latest lag is reported with a 1 second resolution providing the last reported lag. This isn’t accurate and does not provide a history for the Oracle GoldenGate 12.2 Heartbeat. In the past, GoldenGate implementers have created heartbeat tables manually.

In Oracle GoldenGate 12.2 a built-in heartbeat table feature has been added. This heartbeat table allows for more accurate heartbeats and heartbeat history. It works by creating an artificial transaction every minute that contains timing information that is used for heartbeats. The heartbeat tables are accessed via views that provide accurate lag data and lag history.

Recently I setup a pair of Oracle databases with Oracle GoldenGate 12.2. I setup these systems in hope of testing the new Heartbeattable feature that was introduced in GoldenGate version 12.2. The internal heartbeat mechanism is a great improvement in that it provides automatic and accurate lag times and includes a lag history.

I implemented GoldenGate 12.2 between two Oracle 11.2.0.4 databases using standard parameter files that I typically use as a starting point for GoldenGate projects or testing. In addition, database connectivity was set up using the GoldenGate credentialstore. In this case, for testing I have set up GoldenGate to replicate the HR example tables. Unfortunately the heartbeat mechanism failed to work. This blog entry describes the issue that I had and potential solutions.

Configuring GoldenGate

In order to configure replication I used the following GLOBALS and extract parameter files:

At first look I thought that this should work. The GLOBALS parameter GGSCHEMA ggadmin is required for both DDL replication and the heartbeattable. Unfortunately this turned out to be part of the problem.

On the target GoldenGate 12.2 was setup using the following GLOBALS and replciat parameter files:

GLOBALS
GGSCHEMA ggadmin
REP1HR

————————————
— Replicat for HR schema
————————————
replicat REP1HR
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERIDALIAS ggadm
BATCHSQL
HandleCollisions
— Only one of these is used at a time
AssumeTargetDefs
ReportCount Every 30 Minutes, Rate
Report at 01:00
ReportRollover at 01:15

Once the heartbeat table was created it should have been an easy matter to go to the target system and query the ggadmin.gg_heartbeat and ggadmin.gg_heartbeat_history tables to see the automated heartbeats. Unfortunately at this point there were no rows in these tables. It would take a little bit of investigation in order to determine what the issue was.

Debugging the Issue

Since there are a number of parts to the heartbeat table including the extract, pump and replicat I had to decide where to start. I knew that the heartbeat mechanism took advantage of the replication that was currently configured in order to move its heartbeat information from the source to the target. I also believed that it was using GoldenGate replication.

In order to see if anything was moving in the trail file I used the GoldenGate stats command against the extract. I only saw the transactions that I had run for my own testing of the replication. This led me to believe that it was a problem at the source side. I also ran logdump and looked at the trail files and I saw no “heartbeat” records in the trail.

I then looked in the database at the database schedule to see if the GG_UPDATE_HEARTBEATS job was running. It was and it was updating the GG_HEARTBEAT_SEED HEARTBEAT_TIMESTAMP column. So, the scheduler job was running and the column was being updated so there was nothing in the trail file, so it was most likely an issue with replication.

Looking back at the extract parameter file it became apparent that this might be related to the parameter TRANLOGOPTIONS EXCLUDEUSER ggadmin parameter. So, I commented out that parameter and suddenly the GG_HEARTBEAT and GG_HEARTBEAT_HISTORY table began populating on the target side. In addition, after a while a stats command against the extract showed updates to the GG_HEARTBEAT_SEED table.

*** Daily statistics since 2016-02-18 00:00:00 ***

Total inserts 0.00

Total updates 603.00

Total deletes 0.00

Total discards 0.00

Total operations 603.00

In addition the gg_lag_history view showed the data that I was looking for:

Because I was setting up for bi-directional replication and using the ggadmin user for both source and target I had configured the excludeuser parameter. This keeps GoldenGate from re-replicating replicated transactions by ignoring transactions submitted by the ggadmin user. This was fine for normal transactions but I didn’t expect that the heartbeat transactions would be excluded as well.

The excludeuser ggadmin caused updates to the GG_HEARTBEAT_SEED table to not be replicated. You cannot exclude the ggadmin user in the extract. The heartbeat schema is defined by the GGSCHEMA parameter in the GLOBALS file. In addition, the GGSCHEMA parameter defines the schema for DDL replication. This causes a bit of a problem for bi-directional replication when you want to use the ggadmin user for both extract and replicat.

Solutions

I thought about a number of different solutions to this problem and consulted some of my colleagues. We decided that the best approach to this problem was to simply use a different Oracle database user for extract and replicat. This would allow us to still maintain the same GGSCHEMA user for heartbeat and DDL replication. The new user account used for the replicat would be excluded in the TRANLOGOPTIONS EXCLUDEUSER parameter and everything should work well.

This would allow the ggadmin user at the source to submit both DDL and heartbeats, since you can only have one setting for GGSCHEMA which both must use. The different user that is used for replicating back to the source will be excluded via TRANLOGOPTIONS EXCLUDEUSER .

I really would like to see the GoldenGate developers take a look at this and internally allow replication of the GoldenGate lag tables to be excluded from the excludeuser option.

Managing Heartbeat Data

As seen above the heartbeat table is created via the ADD HEARTBEATTABLE command within ggsci. By default a heartbeat is generated every minute, retained for 30 days then purged. The frequency of the heartbeat, the history retention and how often the purge process runs is configurable. This is done via the ALTER HEARTBEATTABLE command.

Viewing Heartbeat Data

Viewing the heartbeat table is done via the two heartbeat views; GG_LAG and GG_LAG history. These views provide information on lags for each set of ext -> pump -> replicat that is configured. This information as well as the history is valuable for monitoring the performance of the GoldenGate configuration.

I have implemented viewing these tables via the following scripts. The scripts and output are shown here:

December 2015 brought us a new version of GoldenGate, and a new version for Big Data adapters for the GoldenGate. Let’s have a look at what we have now and how it works. I am going to start from the HDFS adapter.

As a first step, we need to prepare our source database for replication. It becomes easier with every new GoldenGate version. We will need to perform several steps:
a) Enable archive logging on our database. This particular step requires downtime.
orcl> alter database mount;

And we are almost done. Now we can create a schema for a GoldenGate administrator, and provide required privileges. I’ve just granted DBA role to the user to simplify process. In any case you will need it in case of integrated capture. For a production installation I advise you to have a look at the documentation to verify necessary privileges and roles.

Let’s create a test schema to be replicated. We will call it schema on the source as ggtest and I will name the destination schema as bdtest. It will allow us also to check how the mapping works in our replication.

Everything is ready on our source database for the replication.
Now we are installing Oracle GoledenGate for Oracle to our database server. We can get the software from the Oracle site on the download page in the Middleware section, GoldenGate, Oracle GoldenGate for Oracle databases. We are going to use 12.2.0.1.1 version of the software. The installation is easy – you need to unzip the software and run Installer which will guide you through couple of simple steps. The installer will unpack the software to the destination location, create subdirectories, and register GoldenGate in the Oracle global registry.

We continue by setting up parameters for Oracle GoldenGate (OGG) manager and starting it up. You can see that I’ve used a default blowfish encryption for the password. In a production environment you may consider another encryption like AES256. I’ve also used a non-default port for the manager since I have more than one GoldenGate installation on my test sandbox.

Let’s prepare everything for initial load, and later online replication.
I’ve decided to use GoldenGate initial load extract as the way for initial load for the sake of consistency for the resulted dataset on Hadoop.
I prepared the parameter file to replicate my ggtest schema and upload all data to the trail file on remote site. I’ve used a minimum number of options for all my processes, providing only handful of parameters required for replication. Extract options is a subject deserving a dedicated blog post. Here is my simple initial extract:

We can also prepare our extract on the source site as well. I haven’t used datapump in my configuration limiting the topology only by simplest and strait-forward extract to replicat configuration. Of course, in any production configuration I would advise using datapump on source for staging our data.
Here are my extract parameters, and how I added it. I am not starting it yet because I must have an Oracle GoldenGate Manager running on the target, and the directory for the trail file should be created. You may have guessed that the Big Data GoldenGate will be located in /u01/oggbd directory.

Let’s leave our source site for a while and switch to the target . Our target is going to be a box where we have hadoop client and all requirement java classes.
I used the same box just to save resources on my sandbox environment. You may run different GoldeGate versions on the same box considering, that Manager ports for each of them will be different.
Essentially we need a Hadoop client on the box, which can connect to HDFS and write data there. Installation of Hadoop client is out of the scope for this article, but you can easily get all necessary information from the Hadoop home page .

Having all required Hadoop classes we continue by installing Oracle GoldenGate for Big Data, configuring and starting it up. In the past I received several questions from people struggling to find the exact place where all adapters could be uploaded. The Adapters were well “hidden” on “Oracle edelivery”, but now it is way simpler. You are going to GoldenGate download page on Oracle site and find the section “Oracle GoldenGate for Big Data 12.2.0.1.0” where you can choose the OGG for Linux x86_64, Windows or Solaris. You will need an Oracle account to get it. We upload the file to our linux box, unzip and unpack the tar archive. I created a directory /u01/oggbd as our GoldenGate home and unpacked the tar archive there.
The next step is to create all necessary directories. We start command line utility and create all subdirectories.

We are changing port for our manager process from default and starting it up. I’ve already mentioned that the port was changed due to the existence off several GoldenGate managers running from different directories.

GGSCI (sandbox.localdomain) 2> edit params mgr
PORT 7839
…..

GGSCI (sandbox.localdomain) 3> start manager
Manager started.

Now we have to prepare parameter files for our replicat processes. Let’s assume the environment variable OGGHOME represents the GoldenGate home and in our case it is going to be /u01/oggbd.
Examples for the parameter files can be taken from $OGGHOME/AdapterExamples/big-data directories. There you will find examples for flume, kafka, hdfs, hbase and for metadata providers. Today we are going to work with HDFS adapter.
I copied files to my parameter files directory ($OGGHOME/dirprm) and modified them accordingly:
1
2

You can find information about all those parameters in oracle documentation here, but there are parameters you will most likely need to change from default:

gg.handler.hdfs.rootFilePath – it will tell where the directories and files have to be written on HDFS.
gg.handler.hdfs.format – you can setup one of the four formats supported by adapter.
goldengate.userexit.timestamp – it will depend from your preferences for transactions timestamps written to your hdfs files.
gg.classpath – it will depend from location for your hadoop jar classes and native libraries.

You can see I’ve mentioned the gg.handler.hdfs.format.includeColumnNames parameter. It is supposed to put column name before each value in the output file on HDFS. It may be helpful in some cases, but at the same time it makes the file bigger. If you are planning to create an external Hive table, you may consider commenting on it as I have.
The next parameter file is for our data initialization replicat file. You may consider using a Sqoop or another way to make the initial load for your tables, but I think it makes sense to use the GG replicat if the table size is relatively small. It makes the resulting file-set more consistent with the following replication, since it will be using the same engine and format. So, here is my replicat for initial load:

You can see the Hadoop directories and files created by the initial load.
As soon as the initial load has run we can start our extract and replicat to keep the destination side updated.
We are moving to source and starting our extract prepared earlier.
1
2
3
4

GGSCI (sandbox.localdomain as ogg@orcl) 6>start extract ggext

Sending START request to MANAGER …
EXTRACT GGEXT starting

So, moving back to target and preparing our replicat. I used the replicat with the following parameters:
1
2
3
4
5
6
7
8
9
10
11
12

You can see the table definition is a bit different from what we have on the source site and you will see why.
We got additional columns on the destination side. Interesting that while some of them have a pretty clear purpose, the other columns are not totally clear and have null values.
The first column is a flag for operation, and it shows what kind of operation we have gotten in this row. It can be “I” for insert, “D” for delete and “U” for an update. The second column is table name. The third one is a timestamp in UTC showing when the transaction occurred. The next one is another time in local timezone informing the time of commit, and the next column has a commit sequence number. Those columns can help you to construct the proper data set for any given time.
Let’s insert and update some row(s) on source and see how it will show up on the target:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Let’s check how it is reflected on the destination site. We see two new rows, where each row represents a DML operation. One was for the insert and the second one was for the update.
1
2
3
4
5
6
7
8
9
10
11

It work for deletes too, only flag will be “D” instead of “I” for insert and “U” for updates.

What about DDL support? Let’s truncate the table.
1
2
3
4
5

orcl> truncate table ggtest.test_tab_1;

Table truncated.

orcl>

And here, there is nothing in our hdfs files. Maybe I’ve missed something, but it looks like the truncate operation is not creating any record. I need to dig a bit more. I will try to make a separate blog post about DDL support for the Big Data Adapters.
It works pretty well when we create a new table and insert new rows.

It also works if we change one of the existing tables, adding or dropping a column. Let’s try to create a new table here:
1
2
3
4
5
6
7
8
9
10
11
12
13

You can see that it has created a new directory and file for the new table. Additionally, if you add a column the new file will be used for all DML operations for the updated table. It will help to separate tables with different structure.
1
2
3
4
5
6
7
8
9
10
11

Not sure if it is an expected behavior or bug, but apparently it is not working. Our replicat is broken and complains that “DDL Replication is not supported for this database”.
1
2
3
4
5
6
7
8
9
10
11
12
13

What can we say in summary? The replication works, and supports all DML, and some DDL commands. You will need to prepare to get consistent datasets for any given time using flag for operation, and time for the transaction. In my next few posts, I will cover other Big Data adapters for GoldenGate.

System resource usage of GoldenGate/XStream processes aggregated by Session Type and Session Module
Data is ordered by CPU Time in descending order, followed by Session Type and Session Module in ascending order

SGA usage of Streams Pool for GoldenGate/XStream processes ordered by SGA Used %Diff, SGA Allocated %Diff, Component in descending order
For each Capture (Object Name), reporting memory used by Capture and Logminer processes at the Begin and End snapshots
For each Apply (Object Name), reporting memory used by each Apply process at the Begin and End snapshots
% SGA Util refers to the percentage of allocated memory used at the End snapshot
Memory usage is displayed in Megabytes

SQL Tuning Advisor in Oracle SQL Developer 3.0

<Do not delete this text because it is a placeholder for the generated list of “main” topics when run in a browser>

Purpose

This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.0.

Time to Complete

Approximately 20 minutes.

Overview

The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.

In this tutorial, you learn how to run and review the recommendations of the SQL Tuning Advisor.

Note: Tuning Advisor is part of the Tuning Pack, one of the Oracle management packs and is available for purchase with Enterprise Edition. For more information see The Oracle Technology Network or the online documentation.

Software and Hardware Requirements

The following is a list of software requirements:

Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed.

If you have installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 from your desktop.

Open the directory where the SQL Developer 3.0 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

2.

On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

Note: To rename it, select the icon and then press F2 and enter a new name.

3.

Your Oracle SQL Developer opens up.

4.

In the Connections navigator, right-click Connections and select New Connection.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save and then click Connect.

10 .

The connection is saved and you can view the two newly created connections in the Connections list.

Providing Privileges and Removing the existing Statistics on the Scott User

A user requires certain privileges to run the SQL Tuning Advisor. Also, in order to collect and manage statistics on the SCOTT schema, the existing statistics have to be cleared. Below are the steps to grant SQL Tuning Advisor privileges and remove the existing statistics on the scott user.

1.

Click SQL Worksheet and select system user.

2.

To grant privileges to the scott user to run the SQL Tuning Advisor, enter the following lines of code. Click Run Statement .

grant advisor to scott;

grant administer sql tuning set to scott;

3.

The output for the statements is displayed.

4.

The Oracle database allows you to collect statistics of many different kinds in order to improve performance. To illustrate some of the features the SQL Tuning Advisor offers, clear the existing statistics from the SCOTT schema.

To delete the schema statistics, enter the following line of code.

exec DBMS_STATS.DELETE_SCHEMA_STATS (‘scott’);

Select the statement and click Run Statement .

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.The DELETE_SCHEMA_STATS procedure deletes statistics for an entire schema.

5.

The output for the statement appears.

Running the SQL Tuning Advisor on a SQL statement

In this topic, you run the SQL Tuning Advisor on a SQL statement. Four types of analysis are performed by the SQL Tuning Advisor on the SQL statement.

All the recommendations are displayed in the Overview. You can also view each recommendation individually.

1.

Open the SQL Worksheet for the scott user by clicking SQL Worksheet .

2.

Enter the following SQL statement in the worksheet.

select sum(e.sal), avg(e.sal), count(1), e.deptno from dept d, emp e group by e.deptno order by e.deptno;

Note that the above SQL statement has an unused reference to the “dept” table.

3.

Select the SQL statement and click SQL Tuning Advisor .

4.

The SQL Tuning Advisor output appears.

5.

In the left navigator, click Statistics. In this analysis, objects with stale or missing statistics are identified and appropriate recommendations are made to remedy the problem.

6.

In the left navigator, click SQL Profile. Here, the SQL Tuning Advisor recommends to improve the execution plan by the generation of a SQL Profile.

7.

Click the Detail tabbed page to view the SQL Profile Finding.

8.

In the left navigator, click Indexes. This recommends whether the SQL statement might benefit from an index. If necessary, new indexes that can significantly enhance query performances are identified and recommended.

9.

Click the Overview tabbed page. In this case, there are no index recommendations.

10.

In the left navigator, click Restructure SQL. In this analysis, relevant suggestions are made to restructure selected SQL statements for improved performance.

Implementing SQL Tuning Advisor recommendations

You can implement the SQL Tuning Advisor recommendation feature. This will enable you to update the statistics in scott schema. Perform the following steps to implement the SQL Tuning Advisor recommendations:

Much awaited Oracle GoldenGate for Big Data 12.2 is released today and it is available for download at OTN.

Let me give you a quick recap on Oracle GoldenGate for Big Data. Oracle GoldenGate for Big Data streams transactional data into big data systems in real-time, raising the quality and timeliness of business insights. Oracle GoldenGate for Big Data offers also provides a flexible and extensible solution to support all major big data systems.

Oracle GoldenGate for Big Data

Same trusted Oracle GoldenGate architecture used by 1000’s of customers

Data delivery to Big Data targets including NoSQL databases

Support for Polyglot, Lambda and Kappa architectures for streaming data

Key Benefits

Less invasive on source databases when compared to batch processing such as Sqoop or ETL processes

You no longer require to define SOURCEDEFS. DDL changes are automatically replicated to target. For example, if a new column named “mycolumn“ is added on the source database, it will be automatically replicated to the target without stopping and reconfiguring Oracle GoldenGate.

Pluggable Formatters

Oracle GoldenGate for Big Data can write into any Big Data targets in various data formats such as delimited text or XML or JSON or Avro or custom format. This can save users cost and time for staging data in ETL operations.

Oracle GoldenGate for Big Data is able to provide mapping functionally between source table to target table and source field to target field for HDFS/Hive, HBase, Flume and Kafka. The metadata is also validated at Hive or using an Avro schema to ensure data correctness.

Oracle GoldenGate for Big Data can write Logical change records data to a Kafka topic. Operations such as Insert, Update, Delete and Primary Key Update can be handled. It can handles native compression such as GZIP and Snappy in Kafka.

Configuring Classic Capture in Oracle Active Data Guard Only Mode
You can configure classic Extract to access both redo data and metadata in real-time to successfully replicate source database activities using Oracle Active Data Guard. This is known as Active Data Guard (ADG) mode. ADG mode enables Extract to use production logs that are shipped to a standby database as the data source. The online logs are not used as all. Oracle GoldenGate connects to the standby database to get metadata and other required data as needed.
This mode is useful in load sensitive environments where ADG is already in place or can be implemented. It can also be used as cost effective method to implement high availability using the ADG Broker role planned (switchover) and failover (unplanned) changes. In an ADG configuration, switchover and failover are considered roles. When either of the operations occur, it is considered a role change. For more information, see Oracle Data Guard Concepts and Administration and Oracle Data Guard Broker.

Limitations and Requirements for Using ADG Mode
Observe the following limitations and requirements when using Extract in ADG mode.

Extract in ADG mode will only apply redo data that has been applied to the standby database by the apply process. If Extract runs ahead of the standby database, it will wait for the standby database to catch up.

You must explicitly specify ADG mode in your classic Extract parameter file to run extract on the standby database.

You must specify the database user and password to connect to the ADG system because fetch and other metadata resolution occurs in the database.

The number of redo threads in the standby logs in the standby database must match the number of nodes from the primary database.

No new RAC instance can be added to the primary database after classic Extract has been created on the standby database. If you do add new instances, the redo data from the new thread will not be captured by classic Extract.

Archived logs and standby redo logs accessed from the standby database will be an exact duplicate of the primary database. The size and the contents will match, including redo data, transactional data, and supplemental data. This is guaranteed by a properly configured ADG deployment.

ADG role changes are infrequent and require user intervention in both cases.

With a switchover, there will be an indicator in the redo log file header (end of the redo log or EOR marker) to indicate end of log stream so that classic Extract on the standby can complete the RAC coordination successfully and ship all of the committed transactions to the trail file.

With a failover, a new incarnation is created on both the primary and the standby databases with a new incarnation ID, RESETLOG sequence number, and SCN value.

You must connect to the primary database from GGSCI to add TRANDATA or SCHEMATRANDATA because this is done on the primary database.

DDL triggers cannot be used on the standby database, in order to support DDL replication (except ADDTRANDATA). You must install the Oracle GoldenGate DDL package on the primary database.

DDL ADDTRANDATA is not supported in ADG mode; you must use ADDSCHEMATRANDATA for DDL replication.

When adding extract on the standby database, you must specify the starting position using a specific SCN value, timestamp, or log position. Relative timestamp values, such as NOW, become ambiguous and may lead to data inconsistency.

When adding extract on the standby database, you must specify the number of threads that will include all of the relevant threads from the primary database.

During or after failover or switchover, no thread can be added or dropped from either primary or standby databases.

Classic Extract will only use one intervening RESETLOG operation.

If you do not want to relocate your Oracle GoldenGate installation, then you must position it in a shared space where the Oracle GoldenGate installation directory can be accessed from both the primary and standby databases.

If you are moving capture off of an ADG standby database to a primary database, then you must point your net alias to the primary database and you must remove the TRANLOG options.

Only Oracle Database releases that are running with compatibility setting of 10.2 or higher (10g Release 2) are supported.

Configuring Extract for ADG Mode
To configure Extract for ADG mode, follow these steps as part of the overall process for configuring Oracle GoldenGate, as documented in Chapter 8, “Configuring Capture in Classic Mode.”

Enable supplemental logging at the table level and the database level for the tables in the primary database using the ADD SCHEMATRANDATA parameter. If necessary, create a DDL capture. (See Section 3.2, “Configuring Logging Properties”.)

When Oracle GoldenGate is running on a different server from the source database, make certain that SQL*Net is configured properly to connect to a remote server, such as providing the correct entries in a TNSNAMES file. Extract must have permission to maintain a SQL*Net connection to the source database.

On the standby database, use the Extract parameter TRANLOGOPTIONS with the MINEFROMACTIVEDG option. This option forces Extract to operate in ADG mode against a standby database, as determined by a value of PRIMARY or LOGICAL STANDBY in the db_role column of the v$database view.
Other TRANLOGOPTIONS options might be required for your environment. For example, depending on the copy program that you use, you might need to use the COMPLETEARCHIVEDLOGONLY option to prevent Extract errors.

On the standby database, add the Extract group by issuing the ADD EXTRACT command specifying the number of threads active on the primary database at the given SCN. The timing of the switch depends on the size of the redo logs and the volume of database activity, so there might be a limited lag between when you start Extract and when data starts being captured. This can happen in both regular and RAC database configurations.

Migrating Classic Extract To and From an ADG Database
You must have your parameter files, checkpoint files, bounded recovery files, and trail files are stored in shared storage or copied to the ADG database before attempting to migrate a classic Extract to or from an ADG database. Additionally, you must ensure that there has not been any intervening role change or Extract will be mining the same branch of redo.

Handling Role Changes In an ADG Configuration
In a role change involving a standby database, all sessions in the primary and the standby database are first disconnected including the connections used by Extract. Then both databases are shut down, then the original primary is mounted as a standby database, and the original standby is opened as the primary database.
The procedure for a role change is determined by the initial deployment of Classic Extract and the deployment relation that you want, database or role. The following table outlines the four possible role changes and is predicated on an ADG configuration comprised of two databases, prisys and stansys. The prisys system contains the primary database and the stansys system contains the standby database; prisys has two redo threads active, whereas stansys has four redo threads active.

Initial Deployment Primary (prisys)

Initial Deployment ADG (stansys)

Role Related:

TRANLOGOPTIONS MINEFROMACTIVEDG

Database Related:

After Role Transition: Classic Extract to classic Extract

After Role Transition: ADG to ADG

1.Edit ext1.prm to change the database system to the standby system:

1.Edit ext1.prm to change the database system to the primary system:

DBLOGIN USERID userid@stansys, PASSWORD password

DBLOGIN USERID userid@prisys, PASSWORD password

2.If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

2.If a failover, add TRANLOGOPTIONS USEPREVRESETLOGSID.

3.Start Extract:

3.Start Extract:

START EXTRACT ext1

START EXTRACT ext1

Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint

Extract will abend once it reaches the role transition point, then it does an internal BR_RESET and moves both the I/O checkpoint and current checkpoint to SCN s.

Enter the appropriate GoldenGate directory where you want to install binaries. Provide the Oracle home location so that GoldenGate can use the shared library libnnz12.so. Modify the port if a different one is required. The Manager process can also be selected to start up after the installation is completed.

Review the information provide before starting the install process.

Installation in progress.

Installation completed.

Once the installation is complete you can confirm by checking the contents of Inventory.

Copiar o GLOBALS do home antigo para o novo home 12C do Goldengate

Copiar os arquivos de parâmetro .prm do home antigo para o novo home do Goldengate

Atualizar as variáveis de ambiente para refletir no o novo Home 12C “GG_HOME”

New Features

Here are a few highlights of the new features available with GoldenGate 12c Software.1. Replication: Coordinated Mode – For environments where there is a large number of transactions against a table or a group of tables, the RANGE function has traditionally been used to improve performance of the replicats. Management of this requires quite a bit of effort, especially when this is done on a group of tables which have relationships with each other. The need for this in most situations is eliminated by the introduction of the Coordinated Replicat in GoldenGate 12c. This allows the replicat to process transactions in parallel similar to a multithreaded process. The coordinated replicat feature is able to handle referential integrity, applying the records to the table in the correct order.
To enable Coordinated mode Replication the COORDINATED parameter needs to be specified in the parameter file. The THREADRANGE parameter is used in conjunction with COORDINATED parameter to specify the thread number to be assigned to the process. The SYNCHRONIZE REPLICAT can be used to synchronize all these threads to the same position in the trail file.2. Replicat: Integrated Mode – With Oracle Goldengate 11g, the Integrated Extract mode on the source database was made available. In GoldenGate 12c, the Integrated Replicat Mode has also been introduced for use on the target database. With Integrated Replicat the Logical Change Records (LCR’s) are created and transferred to the inbound server which applies them to the database.
The DBOPTIONS parameter with the INTEGRATEDPARAMS(parallelism n) option needs to be used to create the Replicat in Integrated mode. Here the parallelism is specified where ‘n’ is the number for parallel processes to be used.3. Multitenant Support: 12c Database – To support the specification of objects within a given PDB, Oracle GoldenGate now supports three-part object name specifications. Extraction and Replication is supported using the TABLE and MAP statements with other parameters and commands which accept the object names as input. The format for these parameters is container.schema.object.
Three-part names are required to capture from a source Oracle container database because one Extract group can capture from more than one container. Thus, the name of the container, as well as the schema, must be specified for each object or objects in an Extract TABLE statement.
Specify a three-part Oracle CDB name as follows:
Syntax: container.schema.object
Example: PDB1.HR.EMP
Oracle GoldenGate supports three-part names for the following databases:
• Oracle container databases (CDB)
• Informix Dynamix Server
• NonStop SQL/MX
Alternatively to be able to be backup compatible the two naming scheme SCHEMA.OBJECT is still supported with the use of the SOURCECATALOG database name. Below is a sample of the entry required in the parameter file.
SOURCECATALOG plugdb1
MAP schema*.tab*, TARGET *1.*;4. Security: Credential store – The username and encrypted password login credentials are no longer required to be encrypted and can securely be stored in a database wallet. Reference to this login information is made via an alias.5. Security: Wallet and Master Key – Data in trail files and the network are encrypted using the master-key wallet mechanism. With the creation of each trail file, a new encryption key is generated. This is used to encrypt the data while the encryption key is encrypted by the master key. Secure network transfer is done by creating a session key using the master key and a crystallographic function.6. DDL Replication: Native Capture – For capturing DDL operations, the DDL trigger mechanism has been replaced by a new triggerless capture method. This allows support of additional DDL statements which was not previously possible.7. Installation: Using Oracle Universal Installer (OUI). The installation mechanism no longer uses the untarring of the binaries, rather it uses the OUI, much like most Oracle products.

8. Enhanced character set conversion: The conversion of the source character set to an Oracle target character set is now performed by the Replicat instead of the OCI. The name of the source character set is included in the trail and Replicat uses that character set for its session. This enhancement eliminates the requirement to set NLS_LANG on the target to support conversion. See the list of supported Oracle character sets in the Oracle GoldenGate For Windows and UNIX Administrator’s Guide. See SOURCECHARSET in Parameter Changes and Additions for additional information.9. Remote task data type support: Remote task now supports all Oracle data types, including BLOB, CLOB, NCLOB, LONG, UDT, and XML.
A remote task is a special type of initial-load process in which Extract communicates directly with Replicat over TCP/IP. Neither a Collector process nor temporary disk storage in a trail or file is used. The task is defined in the Extract parameter file with the RMTTASK parameter.
This method supports standard character, numeric, and datetime data types, as well as CLOB, NCLOB, BLOB, LONG, XML, and user-defined datatypes (UDT) embedded with the following attributes: CHAR, NCHAR, VARCHAR, NVARCHAR, RAW, NUMBER, DATE, FLOAT, TIMESTAMP, CLOB, BLOB, XML, and UDT. Character sets are converted between source and target where applicable.10. Enhanced timezone conversion: Extract now writes the source time zone to the trail. Replicat sets its session to this time zone. This eliminates the need to use SQLEXEC to alter the Replicat session time zone to apply the source database time zone to the target. See Parameter Changes and Additions for parameter changes related to this enhancement.11. CSN-based transaction filtering: You can now start Extract at a specific CSN in the transaction log or trail.
Syntax: START EXTRACT group_name [ATCSN csn | AFTERCSN csn] START EXTRACT extfin ATCSN 725473
START EXTRACT extfin AFTERCSN 72547312. Automatic discard file creation: By default, Oracle GoldenGate processes now generate a discard file with default values whenever a process is started with the START command through GGSCI. However, if you currently specify the DISCARDFILE parameter in your parameter files, those specifications remain valid. If you did not specify DISCARDROLLOVER along with DISCARDFILE, however, your discard file will roll over automatically every time the process starts. This automatic rollover behavior contradicts the DISCARDFILE [APPEND/PURGE] option because the new default is to rollover.
• The default discard file has the following properties:
• The file is named after the process that creates it, with a default extension of .dsc.
Example: extfin.dsc.
• The file is created in the dirrpt sub-directory in the GoldenGate home.
• The maximum file size is 50 megabytes.
• At startup, if a discard file exists, it is purged before new data is written.
You can change these properties by using the DISCARDFILE parameter. You can disable the use of a discard file by using the NODISCARDFILE parameter.
If a process is started from the command line of the operating system, it does not generate a discard file by default. You can use the DISCARDFILE parameter to specify the use of a discard file and its properties.External Links
• Oracle GoldenGate 12c Documentation.

GOLDENDATE HA MAA RAC ACFS XAG

Purpose is to demonstrate how to create HA for Bi-Directional Replication Goldengate installed on ACFS with RAC cluster using XAG.

XAG simplifies the process since there are no requirements to create action scripts.

Please review REFERENCE section for versions used in test case and versions requirements.

Goldengate is installed on ACFS for simplicity; otherwise, at a minimum the following directories br, dirchk, dirdat, dirtmp will need to be on shared storage with symbolic links if installed on local storage. Keyword is minimum until you find out more directories are required.

Role separation was a huge PITA and do not attempt to perform chmod -R 775 /u01 as it will break since the setuid get unset.
Even with chmod 6751 oracle may prove to be ineffective and relink was done.

Over the last few months, several people had approached me on the process, as well as improved on it. One such improvement is accredited to Bobby Curtis (@dbasolved) who taught me how to buffer in Perl. Bobby also has a neat collection of monitoring scripts for GoldenGate, you can find them here.

The latest implementation, is a collaboration led by my tenacious and talented colleague Tucker Thompson (LinkedIn). He is responsible to maintain and manage the Enterprise Manager environment from an operational perspective for (among many other) a rather large retail corporation – let’s call them Furry Feet (FF). Their environment contains multiple Exadata machines including several dozen non-Exadata environments.

A few weeks ago he approached me with a question on GoldenGate monitoring with Enterprise Manager that does not involve the GoldenGate Plugin. In his own words, Tucker described the problem below:

“The client was previously using [custom built] crontab scripts to monitor multiple items (including GoldenGate) in their large Exadata environment, despite having an OEM 12c implementation. Our desire was to move all of their crontab elements into a centralized strategy utilizing OEM 12c.

The current GoldenGate plugin for OEM 12c was tested, but seemed very buggy and the client was not ready to use it yet.

The client has AGCTL configured to assist in running multiple highly available GoldenGate instances in the same Exadata DBM.”

As per Oracle’s documentation; Agent Control (AGCTL) is the agent command line utility to manage bundled agents (XAG) for application High Availability (HA) using Oracle Grid Infrastructure.

Tucker explains why this solution wasn’t always reliable by stating:

“The crontab scripts operated per compute node to check the logs for errors, send a lag status to the elements, and check the AGCTL status to determine where the instance was running. However, we found that any alert in the alert.log triggered a critical alert through their ticketing system, as it gripped for any ORA-XXXXX error.

For instance, if there were any long running queries, we would get a ticket created. Another major issue was that we would encounter multiple issues where the status of GoldenGate in AGCTL did not accurately reflect is actual status. For example, an instance could be showing as down through AGCTL, but through GGSCI, the status was RUNNING.”

We seem to find a pattern with issues in monitoring of GoldenGate, don’t we? This doesn’t necessarily mean that the tool itself is at fault but rather the available options. Since I had already come up with an adequate way to monitor GoldenGate using Metric Extensions in EM12c, but it was designed to run against a host target specifically where GoldenGate runs in a stand alone mode. In FF’s environment, there were several GoldenGate instances running across the various Exadata compute nodes that were configured to fail-over and restart GoldenGate seamlessly. This made for an interesting problem to resolve because my initial script assumes a static GoldenGate home.

As an example, three Nodes in a cluster each with a different GoldenGate instance that is managed by XAG.

Tucker’s innovative solution was to retrieve the information from clusterware via AGCTL to run the GoldenGate check against the nodes where the instance is currently running.

“What this script does is execute against the Exadata Database Machine as a target. This means that it will first find a compute node available, run AGCTL to determine the names of the GoldenGate instances, and respective nodes they are configured to run on. This information is always available from any node, and the script does not take into account where AGCTL thinks GoldenGate is actually currently running.

Next, with that information registered, the script runs olsnodes to grab the host names of all compute nodes registered in the DBM. It then uses information pulled from the AGCTL configuration per GoldenGate instance to ssh to each compute node and grep for the manager process for that specific GG instance. With the manager found running on a certain node, the script then runs ggsci from that node against that GG instance, and parses the results to tell us if the different components are running, stopped, or abended. It will also take the lag into consideration and set warning thresholds, rather than a critical alert for any amount of lag. The script even goes as far to add the agctl status as an informational column, so it can be seen if agctl is showing as down, but GGSCI shows all processes running fine.

If the manager is not found anywhere in the Exadata environment, it extracts the first node that the instance is configured to run on from the AGCTL configuration, and runs ggsci from that node. This allows the script to still show all of the components as stopped or abended, and their respective lags.”

Another thing to note is that if a GoldenGate instance relocates to a different node for some reason, instead of just getting an alert that the instance went down we would get that alert, followed by a clear alert once the GoldenGate objects (manager, extract, replicat, pump) are back up and running on a different node.

ggate_test|MANAGER|MANAGER|STOPPED|0|0|1|exababy01db01|Goldengate instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test

ggate_test|TEST_E01|EXTRACT|ABENDED|6|7|2|exababy01db01|Goldengate instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test

ggate_test|TEST_R01|REPLICAT|ABENDED|0|0|2|exababy01db01|Goldengate instance 'ggate_test' is not running|/u01/app/oracle/product/11.2.1.0.6/gghome_1/gg_test

The perl script itself is fairly simple, and can be plugged into the Metric Extension example from earlier. The only caveat is, that your GoldenGate environment must be an XAG resource.

Once the Metric Extension is deployed, its information is accessible in the “All Metrics” section for the Exadata Database Machine.

The point of this exercise was to solve a particular use-case where GoldenGate instances are configured as clusterware resources which can be restarted on different nodes each time. What I would like to see is, an implementation of this GoldenGate monitoring script in an implementation that doesn’t necessarily use XAG in a clustered environment.

Thanks again to Tucker for coming up with the idea to retrieve the information and it was fun to incorporate my original script into his version.

ALWAYS review My Oracle Support note 1070954.1 and download the latest scripting for this
HealthCheck before executing any of the scripting.

Current HealthCheck Version
—————————
As of 05/16/2011, the current HealthCheck version is 1.2.2, reflected in the filename ‘HealthCheck_1_2_2_tar_gz’.
Version numbers are also contained in the headers of the uncompressed files.

Target Oracle Database Machine Impact
————————————-
The Oracle Database Machine HealthCheck consists of read only commands. Other than the writing of the output files
and an empty locking file to help guard against more than one HealthCheck executing at a time, the impact to the
target machine is minimal.

The operating system, hardware, and firmware checks running all options take about 4 minutes on and HP quarter rack
and about 3 minutes 30 seconds.

Note Well:
==========
Execute only one HealthCheck at a time in a database machine.

For example, if you have a full rack configured with one cluster, then run one HealthCheck
on the first database server in the cluster.

For another example, if you have a full rack divided into two clusters, run one HealthCheck on the first database
server in the first cluster, wait for it to complete, then run one HealthCheck on the first database server
in the second cluster.

Environment and Configuration Settings
————————————–
This HealthCheck assumes a deployment according to standard Oracle Database Machine naming and location conventions.
This section details some of those conventions, and other information regarding the command syntax and structures
in this HealthCheck.

DCLI Group Files
—————-
This HealthCheck requires the “root” userid to have the following dcli group files present in its home directory:

dbs_group, contains the Ethernet host names of the Oracle Database servers.
cell_group, contains the Ethernet host names of the Exadata Cells.
all_group, contains the Ethernet host names of both the Exadata Cells and the Oracle Database servers.
all_ib_group, contains the private InfiniBand host names of both the Exadata Cells and the Oracle Database servers.
cell_ib_group, contains the private InfiniBand host names of the Exadata Cells.

Linux Convention
—————-
The Linux ~/ convention is used to indicate the home directory of the current user.

Parameters
———-
This HealthCheck uses the following input parameters to simplify some of the command structure:

Note: for 11.2.x deployments, enter the grid home location for both parameters -b and -c.

Note: the -e parameter takes no arguments. When -e is added to the parameters, the scripting
adds -s -q to each dcli command to attempt to suppress SSH login banners.

Note: the -f parameter takes no arguments.

HealthCheck by default on HP hardware does not stop the MS Server on the storage cells in order
to run CheckHWnFWProfile or execute hpaducli commands for
“Determining SAS Backplane Version on storage cells:” and “Verifying disk health on storage cells:”.

If you wish to execute those HealthCheck sections and CheckHWnFWProfile on the storage cells,
it is recommended that you:

1) Schedule an outage.
2) Shutdown the entire Oracle stack running in the cluster.
3) Re-execute the HealthCheck with the “-f” input parameter.
4) Restart the entire Oracle stack running in the cluster.

Note: the -g parameter takes no arguments.

HealthCheck by default does not execute either CheckHWnFWProfile or CheckSWProfile.sh on the
database servers. They should only be executed immediately after the first build of the database
machine or a fresh image. Specify the “-g” parameter to execute CheckHWnFWProfile and
CheckSWProfile.sh on the database servers.

Path
—-
This HealthCheck requires the root user and the oracle user to include
/usr/local/bin:/usr/bin:/usr/sbin:/bin in the $PATH environment variable.

Command Execution Location
————————–
The Automatic Storage Management HealthCheck script is recommended to be executed on the database server
where the +ASM1 instance exists, typically the first node in the target cluster. Unless stated otherwise,
the HealthCheck assumes all commands are executed on the database node where the +ASM1 instance exists.

Command Line Prompts
——————–
A command run by the “root” userid is indicated by a “#” prompt, or may be explicitly stated in the directions.

A command run by the “oracle” userid is indicated by a “$” prompt, or may be explicitly stated in the directions.

Note: When constructing commands, do not copy the “#” or “$” used in these examples.

Secure Shell Equivalence
————————
This HealthCheck requires that there is Secure Shell (SSH) equivalence configured for the “root” userid
between the first database server and all other database servers, and between the first database server
and the storage servers. The scripts will not work without the required SSH equivalence.

Pre-execution Steps
——————-
1) Verify ssh equivalence for the root user:
For a standard Oracle Database Machine, the required equivalence was created during the onsite deployment
and may have been left in place, if requested. If you are uncertain of your configuration, you can execute
the following two commands:
# dcli -g ~/all_group -l root hostname
# dcli -g ~/all_ib_group -l root hostname

If you are challenged to authenticate, ctrl-c out of the commands and establish the required SSH connectivity
as follows:

Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
09:79:e9:78:14:bf:29:40:66:ec:94:25:a9:7d:93:3e

The passphrase has been left empty so that when an SSH connection is established, a passphrase is not required.

2) Download and uncompress the “HealthCheck_bundle.zip” file from MOS note 1070954.1 to your desktop or
laptop computer. This is because there are example files and a spreadsheet that can be viewed there.

2.1) Transfer the HealthCheck_1_2_2_tar_gz file to the /home/oracle directory on the first database server
in the cluster where this HealthCheck is to be executed.

Note: Do not decompress the files onto a Windows environment, read the files in an editor, and then transfer
the decompressed files to your Linux environment. This activity may insert stray characters into the scripts.
It is strongly recommended to decompress the tar file only in your Linux environment and read the files in vi,
if so desired.

Note: When you uncompress the files to your desktop / laptop, check to see if the file receives an extra “.gz”
file extension (e.g: HealthCheck_1_2_2_tar_gz.gz). If it did, then rename the file to remove the extra “.gz”
file extension.

2.2) Extract the files using the following command:

Note: These instructions assume the HealthCheck is being installed for the first time. If you have been running
HealthCheck on your system, it is recommended that you save both the prior scripting and the output files before
you install a newer version of HealthCheck. If you do not, the older files will be overwritten. For example,
assuming that you wish to retain the prior HealthCheck installation online for reference, one method to preserve
the prior installation is to use the mv command in the /home/oracle directory to rename the existing installation.
Eg: mv HealthCheck HealthCheck_03182010.

The output should look similar to this (date and timestamp will vary):

drwxr-xr-x 3 oracle oinstall 4096 Mar 11 12:05 HealthCheck

HealthCheck is the base directory that contains the command files and the output_files subdirectory.

Note: The operating system and Automatic Storage Management scripts, as well as the Voltaire commands screen
capture write their output to the /home/oracle/HealthCheck/output_files directory with a date and timestamp
embedded in the file names so that an output history can be easily maintained.

Note: Files written to the /home/oracle/HealthCheck/output_files directory by the root user are owned by the root user. If file cleanup is desired, the root user will have to perform the actions.

Operating System Healthcheck
—————————-
Execute the following command as the root user from the /home/oracle/HealthCheck directory on the first database
server in the cluster from which this HealthCheck is being driven:

Note: The following command is a sample, and you must substitute the correct parameter values as discussed earlier
in the “Parameters” section. If you try this verbatim on your system, it may not work!

The output will scroll by on your screen as the scripting executes, and an output file will be written to the /home/oracle/HealthCheck/output_files directory.

Automatic Storage Management Healthcheck
—————————————-
Execute the following command as the oracle user from the /home/oracle/HealthCheck directory on the driving node
in the cluster from which this HealthCheck is being driven:

3) When you have processed all of the managed switches, stop logging the terminal output and close
the output file using this command:

# exit

Output File Analysis
——————–
In the output files, after the output from each individual command, there will be one of two types
of expected result provided:

Direct text
A link to another My Oracle Support note

The direct text is used when the expected output is fixed or simple, and the link is used if the
expected output interpretation is complex or varies over time (e.g. firmware versions for
different Exadata Storage Cell Software versions).

If you discover variances between the current values reported for your Oracle Database Machine and
the expected values detailed in the output or referenced files, and are uncertain of how to proceed,
contact Oracle Support for assistance.

The file “sample_output_files.zip” contains a sampling of outupt files for the operating
system scripts and the Automatic Storage Management scripts.

The file “HealthCheck_command_table.xls” is a spreadsheet listing the included checks.

The number of cylinders for this disk is set to 109053.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

*******************************************************************************
Add a new file system using the free space in the extended volum
*******************************************************************************

The following procedure describes how to take a snapshot-based backup. The values shown in the procedure are examples.

1.Prepare a destination to hold the backup, as follows. The destination can be a large, writable NFS location. The NFS location should be large enough to hold the backup tar files. For uncustomized partitions, 145 GB should be adequate.

In the preceding command, nfs_mount_points are the NFS mount points. Excluding the mount points prevents the generation of large files and long backup times.

i.Check the /tmp/backup_tar.stderr file for any significant errors. Errors about failing to tar open sockets, and other similar errors, can be ignored.

3.Unmount the snapshots and remove the snapshots for the root and /01 directories using the following commands:

cd /

umount /root/mnt/u01

umount /root/mnt

/bin/rm -rf /root/mnt

lvremove /dev/VGExaDb/u01_snap

lvremove /dev/VGExaDb/root_snap

4.Unmount the NFS share using the following command:

umount /root/tar

Refer to the maintenance chapter in the owners guide for information about back up and restore. Note that the backup created by this procedure facilitates best in both rolling back software changes and recovering from an unbootable system.

## COMMON
alter system set log_buffer=134217728 sid=’*’ scope=spfile;
alter system set db_ultra_safe=’DATA_ONLY’ sid=’*’ scope=spfile;
alter system set fast_start_mttr_target=600 sid=’*’ scope=spfile;
alter system set parallel_adaptive_multi_user=FALSE sid=’*’ scope=spfile;
alter system set parallel_threads_per_cpu=1 sid=’*’ scope=spfile;
alter system set open_cursors=1000 sid=’*’ scope=spfile;
alter system set use_large_pages=’ONLY’ sid=’*’ scope=spfile;
alter system set “_enable_NUMA_support”=FALSE sid=’*’ scope=spfile;
alter system set sql92_security=TRUE sid=’*’scope=spfile;
alter system set “_file_size_increase_increment” = 2044M sid=’*’ scope=spfile;
alter system set global_names=TRUE sid=’*’ scope=spfile;
alter system set db_create_online_log_dest_1=’+DATA_MZFL’ sid=’*’ scope=spfile;
alter system set os_authent_prefix=” sid=’*’ scope=spfile;
alter system set shared_servers=0 sid=’*’ scope=both;
alter system set DB_LOST_WRITE_PROTECT = ‘TYPICAL’ sid=’*’;

The following procedure describes how to take a snapshot-based backup. The values shown in the procedure are examples.

1.Prepare a destination to hold the backup, as follows. The destination can be a large, writable NFS location. The NFS location should be large enough to hold the backup tar files. For uncustomized partitions, 145 GB should be adequate.

Main Article
A Macro Library is a collection of OGG Macros used to externalize OGG parameters shared across multiple Groups. The library can be a single file containing multiple Macro definitions, or multiple files. Best practice is to create a directory “dirmac” as part of the OGG installation environment to hold the library files. Another best practice is to use the suffix “.mac” on all library files. This way Macro Library files can be recognized without having to open and read one.
Using these best practice tips, the following Macro Library would be stored inside of the file “$OGG_BASE/dirmac/macrolib.mac”.
MACRO #dbconnect BEGIN userid gguser, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default END;
MACRO #bpsettings BEGIN -- The following are "best practice" runtime options which may be -- used for workload accounting and load balancing purposes. -- STATOPTIONS RESETREPORTSTATS ensures that process -- statistics counters will be reset whenever a new report file is -- created. STATOPTIONS RESETREPORTSTATS
-- Generate a report every day at 1 minute after midnight. -- This report will contain the number of operations, by operation -- type, performed on each table. REPORT AT 00:01
-- Close the current report file and create a new one daily at 1 -- minute after midnight. Eleven report files are maintained on disk -- in the dirrpt directory/folder for each GoldenGate group. The -- current report file names are <group name>.rpt. The older reports -- are <group name>0.rpt through <group name>9.rpt, with the -- older report files having larger numbers. REPORTROLLOVER AT 00:01
-- REPORTCOUNT denotes that every 60 seconds the Replicat report file -- in the dirrpt directory/folder will have a line added to it that reports the -- total number of records processed since startup, along with the rated -- number of records processed per second since startup, and the change -- in rate, or "delta" since the last report. -- In a production environment, this setting would typically be 1 hour. REPORTCOUNT EVERY 60 SECONDS, RATE
-- End of "best practices" section END;
MACRO #funcsmap PARAMS (#src_table, #target_table) BEGIN -- Map the source table provided in the variable #src_table to the target -- table listed in the variable #target_table. There are extra columns in the -- target we need to populate, so get the data from either the environment -- variable, or the user token data sent over from Extract MAP #src_table, TARGET #target_table, colmap (usedefaults, orders_trans_ts = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), trans_rec_loc8tr = @STRCAT (@GETENV ("RECORD", "FILESEQNO"), @GETENV ("RECORD", "FILERBA")), extract_lag_ms = @TOKEN ("TKN-EXTLAG-MSEC"), replicat_lag_ms = @GETENV ("LAG", "MSEC"), src_db_name = @TOKEN ("TKN-SRC-DBNAME"), src_db_version = @TOKEN ("TKN-SRC-DBVERSION"), src_txn_csn = @TOKEN ("TKN-TXN-CSN") ); END;
Macros are identified by the keyword “Macro” followed by the macro name “#<name>”. The Macro body is contained within the “BEGIN” and “END” statements. The three Macros provided in this example library file are #dbconnect, #bpsettings, and #funcsmap.
The #dbconnect Macro provides a centralized location for storing database connection information. The database login password is Blowfish encrypted using the “DEFAULT” GoldenGate encryption key. #bpsettings activates best practices settings for generating hourly and daily activity counts. #funcsmap accepts input parameters and uses them to build a Replicat map statement.
Using the Macro Library
Consider the following Replicat parameter file:
nolist include ./dirmac/macrolib.mac list
replicat rfuncb #dbconnect () #bpsettings () sourcedefs ./dirdef/mydefs.defs discardfile ./dirrpt/rfuncb.dsc, purge #funcsmap (amer.orders, euro.funcs_test)
IMPORTANTE---> If list is not included in the parameter file after using nolist, no information will be written to the Group report file.

ibqueryerrors.pl -s RcvSwRelayErrors,RcvRemotePhysErrors,XmtDiscards,XmtConstraintErrors,RcvConstraintErrors, ExcBufOverrunErrors,VL15Dropped — A single invocation of this command will report on all switch ports on all switches. Run this check from a database server or a switch.

Ipmitool Oracle Exadata

IPMI — Intelligent Platform Management Interface, an interface standard that allows remote management, of a server from another, using standardized interface and to check status of components. # ipmitool # ipmitool -h — Help # ipmitool -help # ipmitool -H cel01-ilom -U root chassis power on — To power on a cell or database server, issue this from another server

cellcli commands in Oracle Exadata

CellCLI — Cell Command Line Interface/Interpreter (in Oracle Exadata)

CellCLI manages Exadata Storage Servers (Cells). The scope of the CellCLI command is the cell where it is run, not in other cells. To invoke the CellCLI, login to the Exadata cell as cellmonitor, celladmin, or root, and type “cellcli”.

Oracle GoldenGate
Best Practice – Oracle GoldenGate Conflict Management
Version 1
Date: November 11, 2011
Ananth R. Tiru
Senior Solutions Architect
Center of Excellence
Table of Contents
Introduction: ………………………………………………………………………………………………………………………………. 3
Requirements: …………………………………………………………………………………………………………………………. 3
Methodology: ………………………………………………………………………………………………………………………….. 4
Approach: ……………………………………………………………………………………………………………………………….. 4
Implementation: ……………………………………………………………………………………………………………………… 5
Create Users: ……………………………………………………………………………………………………………………….. 5
Create Tables ………………………………………………………………………………………………………………………. 5
Add Supplemental Logging ……………………………………………………………………………………………………. 6
Configure Extract, Replicat and Stored Procedure ……………………………………………………………………. 6
Creating and starting extract on the source …………………………………………………………………………….. 6
Creating and starting replicat on target …………………………………………………………………………………… 7
ETAB.PRM – Implementation highlights …………………………………………………………………………………… 7
RTAB.prm – Implementation highlights……………………………………………………………………………………. 7
cm_procedure.sql – Implementation highlights………………………………………………………………………… 9
Testing: …………………………………………………………………………………………………………………………………. 11
Update Tests: …………………………………………………………………………………………………………………….. 11
Insert Tests: ……………………………………………………………………………………………………………………….. 11
Delete Tests: ……………………………………………………………………………………………………………………… 12
Recommendations: ………………………………………………………………………………………………………………… 12
Conclusion: ……………………………………………………………………………………………………………………………. 12
Key Reviewers ……………………………………………………………………………………………………………………….. 12
Appendix: ……………………………………………………………………………………………………………………………… 13
Extract Param file – Etab.prm ……………………………………………………………………………………………… 13
Replicat Param file – Rtab.prm …………………………………………………………………………………………….. 14
Conflict Handling Stored Procedure – cm_procedure ……………………………………………………………… 15
Introduction:
In a multi-master database environment where the occurrence of conflicts is possible during replication, it is highly desirable to have a conflict management scheme which is enforced as an exception rather than as a norm. Typically, the chances of conflicts occurring during replication are low and hence, checking for conflicts every time before a record is applied on the target is not efficient. However, at the same time it is highly important to ensure that when a conflicting scenario is encountered it is handled in the right manner.
The goal of this document is to illustrate a generic approach which would handle conflicts as an exception. The approach eliminates the need to check for conflict every time before a record is applied to the target table. It will check and handle conflicts only if an exception occurs when attempting to apply a record on the target table.
The approach described is the key focus of this document. It illustrates the configuration and the procedure for handling conflicts as an exception using OGG. The sample implementation is provided to illustrate the approach and is specific to the requirements listed in this document and Oracle database. However, depending on the actual requirements and use cases different implementation should be pursued. It is therefore, highly recommended to evaluate the implementation provided in this document from a functional, performance, and maintenance perspectives before adopting it in an actual use case.
Requirements:
It is highly desired to have a robust conflict management mechanism which addresses the following requirements for a two node multi-master database environment.
 Identify and handle conflicts due to inserts
o Potentially the same record could be inserted on source and target. During replication it is required to keep the record with the latest timestamp. The rejected record should be moved to an exception table for auditing purposes.
 Identify and handle conflicts due to updates
o Potentially the same record could be modified on the source and target. During replication it is required to keep the record with the latest timestamp. The rejected record should be moved to an exception table for auditing purpose.
o Potentially the record modified on the source could have been deleted on the target. During replication the record should be moved to an exception table.
o PK value of the record could be modified. If PK is modified, the rules listed above apply.
 Identify and handle conflicts due to deletes
o Potentially when propagating deletes from source to target the record on the target could have already been deleted. During replication it is required to move the record to an exception table for auditing purposes.
Methodology:
Given the possibility of occurrences of conflict during various database operations and the desired resolution when these conflicts arise, the approach illustrated below provides a way to deal with conflicts as an exception which will address all the aspects of the requirements listed above.
OGG will always assume that there is no conflict when applying a record on the target and will rely on the DB to throw an exception when there is a conflict. The exception will then be handled based on the above requirement.
Approach:
 Use KEYCOLS in both the extract and replicat. Include the table primary key(s) and the timestamp columns to the keycols.
o By doing this OGG will automatically include before image of the record which will be used by the replicat to identify the record on the target table. If the before image matches the current image on the target table replicat will apply the record. However, if the target record has changed, which is typically indicated by the timestamp column, replicat will not be able to find the record and an exception will be thrown.
o Note, supplemental logging must be turned on to include the timestamp in addition to the primary keys.
 Use multiple maps for the a given source and target table. Typically, a table will have three map statements. The first map statement will attempt to apply the record, the second map statement will handle the DB exception that may be generated by the first map statement and invokes the conflict management logic. Finally, the third map statement will handle the exception, if any, from the second map statement and will insert the incoming record into the exception table.
 The map dealing with handling the DB exception can either invoke an external stored procedure or implement the logic in a SQLEXEC (within the replicat param file) to detect and resolve conflict. The complexity of the stored procedure or the logic implemented in a SQLEXEC will depend on the requirements and use case.
Implementation:
The implementation illustrated below attempts to address the requirements using the approach described in a 2 node multi-master database environment.
Create Users:
The implementation was tested on a 2 node multi-master database with source schema named – atiru_east1 and target schema named – atiru_west1 with password for both the schema set to ‘oracle’. Two OGG users named atiru_gguser1 and atiru_gguser2 was created on source and target with password for both the users set to ‘oracle’
Create Tables
The following table, MMS_TEST, will be used as the sample table and is deployed in a 2 node multi-master database environment for which conflict management is desired. The table MMS_TEST_EXCEPTION is created to store records resulting from the conflict management strategy that is adopted based upon the requirements and use case.
Create table MMS_TEST
(CITY VARCHAR2(64),
CODE VARCHAR2(36),
INFORMATION VARCHAR2(500),
PAYLOAD BLOB,
create_timestamp number,
modify_date timestamp);
alter table MMS_TEST add constraint MD_CITY_PK primary key (CITY, CODE);
–Table to store the exception records
Create table MMS_TEST_EXCEPTION
(CITY VARCHAR2(64),
CODE VARCHAR2(36),
INFORMATION VARCHAR2(500),
PAYLOAD BLOB,
create_timestamp number,
modify_date timestamp);
Add Supplemental Logging
After successfully creating the table, turn on supplemental logging on both the source and target for the table MMS_TEST. The following box illustrates adding supplemental logging for the source table.
Configure Extract, Replicat and Stored Procedure
The following artifacts which provide the implementation as described in the approach discussed above accompany this document. Also, the appendix of this document lists the code present in each of the following files.
o Etab.prm – Extract param file for the source.
o Rtab.prm – Replicat param file for the target.
o Cm_procedure.sql – Stored Procedure to resolve conflicts, invoked by replication when an exception occurs when applying the record on the target.
Modify the param files and cm_procedure to properly reflect the schema names in the actual environment. Import the cm_procedure to the target schema. Create and start the extract and replicat on the source and target OGG environment respectively.
Using the extract and replicat param files provided, create another set of extract and replicat param file for replicating from target to source. Import the cm_procedure onto the source schema. Register the extract and replicat and start them as described above. Note, performing this step is optional when the focus is getting an conceptual understanding of the approach and to execute the test cases listed below, but it is mandatory in an actual production multi-master database scenario.
Creating and starting extract on the source
— Log on to your source database.
GGSCI>dblogin userid atiru_east1 password oracle
— This will add supplemental log data for columns – city, code and modify_date on the source
GGSCI>add trandata atiru_east1.mms_test, cols(modify_date)
– Log into source DB. Ensure the OGG user atiru_gguser2 is created on the source DB.
GGSCI> DBLOGIN USERID atiru_gguser1 PASSWORD oracle
–Configure an extract to read from the transaction logs.
GGSCI> ADD EXTRACT ETAB, TRANLOG, BEGIN NOW
–Configure a remote trail to which the extract will write.
GGSCI > ADD RMTTRAIL ./dirdat/ta, EXTRACT ETAB, MEGABYTES 5
–Start the extract.
GGSCI> start etab
Creating and starting replicat on target
ETAB.PRM – Implementation highlights
Some of the important points to be noted in the extract parameter file on the source. See Appendix for full listing.
Note: Do not just specify the extra columns, because when a table has a primary key or unique index, the KEYCOLS specification will override them. Using KEYCOLS in this way ensures that before images are available for updates to the key or index columns.
RTAB.prm – Implementation highlights
Some of the important points to be noted in the replicat parameter file on the target. See Appendix for full listing.
Typically for a given table that requires conflict management there are three map statements. The first map statement assumes no conflicts and attempts to apply the DML on the target table. Using the
– Log into target DB. Ensure the OGG user atiru_gguser2 is created on the target DB.
GGSCI> DBLOGIN USERID atiru_gguser2 PASSWORD oracle
— Add checkpoint table, use the GLOBAL file to specify the name of the checkpoint table
GGSCI> add checkpointtable
–Add replicat on the target
GGSCI> ADD REPLICAT RTAB, EXTTRAIL ./dirdat/ta
–Start Replicat
GGSCI>start rtab
–In a bi-direction replication configuration the following parameter will ensure that the updates performed by replicat logged as ‘atiru_gguser1’ on the source DB will not be picked up the extract.
TRANLOGOPTIONS EXCLUDEUSER atiru_gguser1
–Specify KEYCOLS and include the primary key and the timestamp column. Using KEYCOLS will facilitate the conflict detection when applying the record on the target.
TABLE atiru_east1.mms_test, keycols (city, code, modify_date);
keycols specified, for an update operation, the replicat will use the before image of the keycols and attempt to find the record on the target, if it is successful in finding the record it will update record. However, if it cannot find the record either because the record was updated on the target, in which case the before image of the incoming record will not match the record, or the record was deleted, it will get an exception.
The second map statement will handle the exception thrown by the first map statement and will invoke the stored procedure. Based on the requirements the store procedure will implement an appropriate conflict management logic and return a value indicating whether the replicat should apply the record on the target or discard the record and throw an exception.
Notice the above map statement uses a filter clause which evaluates the return value from the stored procedure and determines whether or not to apply the record. The filter will apply the incoming record to the target if the stored procedure returns a value equal to ‘1’ otherwise it will raise an exception.
The third map statement will handle the exception thrown by the second map statement and insert the incoming record into an exception table.
–First map statement for handling DML for the given table.
–Assume no conflicts and apply record to the target.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
keycols (city, code, modify_date),
COLMAP ( USEDEFAULTS );
–Second map statement for handling DB exceptions, e.g. ORA-1403, thrown by the first map statement for the given table.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
EXCEPTIONSONLY,
SQLEXEC (SPNAME atiru_west1.cm_procedure, ID detect,
PARAMS (i_city=city, i_code=code, i_modify_date=modify_date, ib_city=before.city, ib_code=before.code, ib_modify_date=before.modify_date, i_opcode=@getenv (“GGHEADER”, “OPTYPE”)),
EXEC SOURCEROW,
BEFOREFILTER),
FILTER ((@getval (detect.o_result) = 1),
RAISEERROR 9999),
keycols (city, code),
COLMAP (USEDEFAULTS);
cm_procedure.sql – Implementation highlights
The implementation of the stored procedure invoked by the map statement to handle conflict is highly dependent on the requirements. Also, depending on the requirement implementing the logic in a SQLEXEC statement within the replicat param file can be considered. Based on the requirement listed above, it was deemed that using a stored procedure to implement the conflict management logic was appropriate.
The procedure takes the current and before values of the primary key(s) and timestamp, the database operation type and returns either a ‘0’ or ‘1’ to indicate to replicat whether to reject or apply the record respectively.
First the procedure checks for the existence of the record in the target. If the record is found it continues with the processing. However, if the record is not found it handles the exception thrown by the database and depending upon the DML it returns an appropriate value . Example, if the database operation is ‘update’ and the record is not found then it returns ‘0’.
–Third map statement for handling exception thrown by the second map statement as a result of the logic implemented for conflict management.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test_exception,
INSERTALLRECORDS,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS);
CREATE OR REPLACE PROCEDURE “ATIRU_WEST1″.”CM_PROCEDURE” (
o_result OUT NUMBER,
i_opcode IN VARCHAR2,
i_city IN VARCHAR2,
i_code IN VARCHAR2,
i_modify_date IN TIMESTAMP,
ib_city IN VARCHAR2 =null,
ib_code IN VARCHAR2 =null,
ib_modify_date IN TIMESTAMP =null,
)
Then the procedure checks for conflict based on timestamp column and based on the requirement which is to retain the latest timestamp sets the return value to either ‘0’ or ‘1’
IF i_opcode=’UPDATE’ OR i_opcode = ‘INSERT’ THEN
SELECT * into mms_record
FROM mms_test
WHERE city=i_city
AND code=i_code for update;
END IF;
IF i_opcode=’PK UPDATE’ THEN
SELECT * INTO mms_record
FROM mms_test
WHERE city=ib_city
AND code=ib_code for UPDATE;
END IF;
t_last_change_ts := mms_record.modify_date;
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ OR i_opcode=’INSERT’ THEN
IF t_last_change_ts IS NULL THEN
o_result := 1;
ELSE
IF sys_extract_utc(i_modify_date) >= sys_extract_utc(t_last_change_ts) THEN
o_result := 1;
dbms_output.put_line(‘record will be processed by replicat’);
ELSE
o_result := 0;
dbms_output.put_line(‘record will not processed by replicat’);
END IF;
END IF;
END IF;
EXCEPTION
when TOO_MANY_ROWS then
o_result :=0; return;
when NO_DATA_FOUND then
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ THEN
o_result := 0;
ELSE –insert
o_result := 1;
END IF;
Finally, it performs logging into the exception table of the record that will be replaced and returns.
Testing:
All the tests for inserts, updates and deletes can be simulated and verified on the replication flow from source to target. It is not required to have the replication from target to source to conduct the test. When conducting the tests it is assumed that the modify_date column is updated every time the record is modified.
Update Tests:
o Update a record on source and validate it gets replicated.
o Update a record on the target, update the same record on the source. Validate the source record is replicated to the target and the replaced record on the target is moved to the exception table.
o Stop the replicat ‘RTAB’. Update the record on the source, update the same record on the target. Start the replicat ‘RTAB’. Validate the target record is retained and the record from the source is moved to the exception table.
o Delete a record on the target. Update the same record on the source. Validate the record from the source is moved to the exception table.
o Perform all the above tests with PK update.
Insert Tests:
o Insert a record on source and validate it gets replicated.
o Insert a record on the target. Insert a same record (having same keys) on the source. Validate the source record is replicated to the target and the replaced record on the target is moved to the exception table.
IF o_result = 1 THEN
IF i_opcode=’UPDATE’ or i_opcode=’PK UPDATE’ THEN
INSERT INTO mms_test_exception
values mms_record;
END IF;
IF i_opcode=’INSERT’ THEN
DELETE FROM mms_test WHERE city=i_city AND code=i_code;
END IF;
END IF;
Delete Tests:
o Delete a record that exists on both the target and source on the source. Validate the record is deleted on the target.
o Delete a record on the target. Delete the same record on the source. Validate the record from the source is moved to the exception table.
Recommendations:
The exception table can be enhanced and additional code can be added in the replicat param file to capture additional information about the records that rejected because of conflict management rules. This information can be used to identify the causality of the conflicts and determine the actions that can be taken to minimize the conflicts.
Conclusion:
The key goal of this best practice document was to illustrate an approach to deal with conflict management during replication in an efficient manner. Using the approach an implementation was presented to address a set of requirements to handle conflicts. It should be noted while the scope of the implementation will depend upon the requirements; the approach presented provides an alternate and an efficient method to build the implementations.
When constructing your own conflict management solution it is important to consider the consequences of “choosing” one DML operation over another based solely on primary key and timestamp. It is possible that different columns are updated for the same PK on both the source and target system. When this occurs, choosing one update over the other could result in some information loss occurring. In our example that risk is not addressed as the stated requirement was to use all the values for the DML operation with the most current timestamp.
Key Reviewers
G. Allen Pearson
Director, CoE
Steve George
CMTS
Appendix:
Extract Param file – Etab.prm
EXTRACT ETAB
USERID atiru_gguser1, PASSWORD oracle
–Exclude records updated by replicat
TRANLOGOPTIONS EXCLUDEUSER atiru_gguser1
–Comment out the following if not using ASM
TRANLOGOPTIONS ASMUSER sys@asm1, ASMPASSWORD coe123
— Turn Bounded Recovery off, if required turn it on.
BR BROFF
RMTHOST localhost, MGRPORT 16000
RMTTRAIL ./dirdat/ta
TABLE atiru_east1.mms_test, keycols (city, code, modify_date);
Replicat Param file – Rtab.prm
REPLICAT RTAB
USERID atiru_gguser2, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RTAB.DSC, PURGE
ALLOWDUPTARGETMAP
REPERROR (7777, DISCARD)
REPERROR (9999, EXCEPTION)
REPERROR (DEFAULT, EXCEPTION)
— Assume no conflicts and map DML to the target
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
keycols (city, code, modify_date),
COLMAP ( USEDEFAULTS );
— Handle DB exceptions. E.g. ORA-1403, thrown by by the above map. Perform conflict management.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
EXCEPTIONSONLY,
SQLEXEC (SPNAME atiru_west1.cm_procedure, ID detect,
PARAMS (i_city=city, i_code=code, i_modify_date=modify_date, ib_city=before.city, ib_code=before.code, ib_modify_date=before.modify_date, i_opcode=@getenv (“GGHEADER”, “OPTYPE”)),
EXEC SOURCEROW,
BEFOREFILTER),
FILTER ((@getval (detect.o_result) = 1),
RAISEERROR 9999),
keycols (city, code),
COLMAP (USEDEFAULTS);
— Handle exception as a result of conflict management logic.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test_exception,
INSERTALLRECORDS,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS);
Conflict Handling Stored Procedure – cm_procedure
——————————————————–
— DDL for Procedure CM_PROCEDURE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “ATIRU_WEST1″.”CM_PROCEDURE” (
o_result OUT number,
i_opcode IN VARCHAR2,
i_city IN VARCHAR2,
i_code IN VARCHAR2,
i_modify_date IN TIMESTAMP,
ib_city IN VARCHAR2 := NULL,
ib_code IN VARCHAR2 := NULL ,
ib_modify_date IN TIMESTAMP :=NULL
)
IS
t_last_change_ts TIMESTAMP;
mms_record mms_test%rowtype;
BEGIN
t_last_change_ts := NULL;
— If DML is ‘delete’ this means record is already deleted
— on the target, hence return 0
— so replicat will put this record into the exception table.
IF i_opcode =’DELETE’ THEN
o_result :=0;
RETURN;
END IF;
— Check if the record on the target exists.
— If record is not found or more than one record is found handle
— it as an exception.
— When more than one record is found then
— return 0 so replicat will put the record into the exception
— table.
— If record is not found then Return 0, if opcode is update or pk
— update so replicat will put the record in the exception table.
— Return 1, If opcode is insert, so replicat will process the
— record appropriately.
— If record found then continue processing and resolve the
— conflict.
— Return 0 if the incoming record timestamp is lesser than target
— timestamp, otherwise return 1.
— When performing conflict detection and resolution on a record,
— lock the record until the resolution is completed.
IF i_opcode=’UPDATE’ OR i_opcode = ‘INSERT’ THEN
SELECT * into mms_record
FROM mms_test
WHERE city=i_city
AND code=i_code for update;
END IF;
IF i_opcode=’PK UPDATE’ THEN
SELECT * INTO mms_record
FROM mms_test
WHERE city=ib_city
AND code=ib_code for UPDATE;
END IF;
t_last_change_ts := mms_record.modify_date;
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ OR i_opcode=’INSERT’
THEN
IF t_last_change_ts IS NULL THEN
o_result := 1;
ELSE
IF sys_extract_utc(i_modify_date) >=
sys_extract_utc(t_last_change_ts)
THEN
o_result := 1;
dbms_output.put_line(‘record will be processed by replicat’);
ELSE
o_result := 0;
dbms_output.put_line(‘record will not processed by replicat’);
END IF;
END IF;
END IF;
— Perform logging of the target record.
— In case of update, log the rejected target record into an
— exception table.
— In the case of insert, delete the target record.
IF o_result = 1 THEN
IF i_opcode=’UPDATE’ or i_opcode=’PK UPDATE’ THEN
INSERT INTO mms_test_exception
values mms_record;
END IF;
IF i_opcode=’INSERT’ THEN
DELETE FROM mms_test WHERE city=i_city AND code=i_code;
END IF;
END IF;
–Handle the exception when more than one record is found or no
–record is found.
EXCEPTION
when TOO_MANY_ROWS then
o_result :=0; return;
when NO_DATA_FOUND then
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ THEN
dbms_output.put_line(‘exception’);
o_result := 0;
ELSE –insert
o_result := 1;
END IF;
RETURN;
END cm_procedure;
/

Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based on balance and timestamp in a bidirectional active-active configuration

Using Oracle GoldenGate (OGG) 11gR2 for Conflict Detection and Resolution (CDR) based on balance and timestamp in a bidirectional active-active configuration

In the article you will have a look at an example for CDR implementation based on a balance and timestamp column in a bidirectional active-active OGG setup. I will build an active-active bidirectional OGG replication between two sites (RACD, RACDB) each having identical tables (test5.account, test5.seat_assignment). I will emphasize on the requirements for CDR implementation and will outline CDR concepts and illustrate a step-by step CDR implementation, testing and troubleshooting. I will cover two cases

Use delta method for account balance CDR – An initial balance of 1000 will be simultaneously credited 200 on site B and debited 100 on site A. The result will be 1100 on both site A and site B.

Use USEMIN timestamp method for seat booking CDR – a seat ’2A’ will be booked 1st by John Smith on site A and at about the same time will be booked by Pier Cardin on site B. The result will be the first user on both site A and site B.

Starting with OGG 11gR2 there are build in options in the MAP replicat parameter, such as COMPARECOLS, RESOLVECONFLICT, and in the TABLE extract parameter, such as GETBEFORECOLS, allowing easy, automatic and OGG driven CDR compared to the methods involving SQL or PLSQL code invoked from SQLEXEC used for CDR in versions of OGG prior to 11gR2.

In active-active bidirectional configuration we have

OGG configured for replication from site A to site B and from site B to site A

Application that can access both site A and site B

In the article Site A is RACD ad site b is RACDB.

Due to the asynchronous nature of OGG conflicts can occur if both sites update the same record at or near the same time. For CDR there are different methods in use such as

Latest time stamp – a timestamp column is added to the table and in case of two contending operations against the same record issued each on a different site, the record corresponding to an operation with the latest timestamp win the contest and persist in the database

Earliest timestamp – a timestamp column is added to the table and in case of two contending operations against the same record issued each on a different site, the record corresponding to an operation with the earliest timestamp win the contest and persist in the database.

Balance – in case of two contending operations against the same record issued each on a different site, the record that persist in the database is a summation from the difference(before-after) from the source + current column value from a record on the target for the column where balance is used. That is, adds the difference between the before and after values in the trail record from site A to the current value of the column in the target database on site B.

Site priority

Etc…

Any combination of the above methods.

OGG 11gR2 introduced facilities for automatic handling of

Latest timestamp – using USEMAX option in RESOLVECONFLICT

Earliest timestamp – using USEMIN option in RESOLVECONFLICT

Balance – using BALANCE option in RESOLVECONFLICT

In OGG versions prior to 11gR2 a custom code using SQLEXEC was required in order to implement the CDR functionality.

For CDR to operate the before image of the changed record is required in addition to the before image of the table key. Force Oracle to log in the tranlog the before image of a changed non key column by issuing ADD TRANDATA <tablename>, COLS(<changed columns>)

The extract capture for CDR should include the following

Force extract to capture the before image using GETBEFORECOLS in the TABLE parameter.

Use NOCOMPRESSDELETES and NOCOMPRESSUPDATES in the extract parameter file so that to have extract write a full record in a trail instead of the changed columns only.

For CDR replicat will be configured with mapping for:

A base table part of the replication configuration

An exception table corresponding to each replicated table to store the records details only in case of a conflict resolution or an error ( Similar to the way OGG REPERROR maps errors using exception map statement into an exception table)

The CDR is handled first and the REPERROR is handled second. Thus, the exception table is populated in case of a CDR.

A replicat configured for CDR performs the following tasks in addition to the usual tasks of applying the records from the trail.

Compare the before values of the record from the trail using the MAP option COMPARECOLS with the before values on the target for each update or delete or both update and delete

Use the before images from the trail to calculate a value on the target in case of conflict if DELTA is used

Use the after images from the trail to calculate a value on the target in case of conflict if DELTA, USEMIN or USEMAX is used

In case of conflict populates the table specified in the exception map for future reference (optional if you use an exception mapping)

The column test5.seat_assignment.latest_timestamp is instrumental for the USEMIN mode CDR. If there are no conflicts the data from the trail is applied as usual. In case of a conflict the after image from the trail file for this column is compared to the current value on the target. The earliest timestamp wins and the change record with the earliest after image of test5.seat_assignment.latest_timestam is written to the target. Note that as DEFAULT is used the resolution applies to all columns.

The column test5.account.balance is instrumental for the DELTA mode CDR. If there are no conflicts the data from the trail is applied as usual. In case of a conflict, the difference between the before and after values of test5.account.balance column from the trail record is added to the current value of the test5.account.balance column on the target database. The remaining columns are overwritten.

Implementing bidirectional active-active OGG replication with CDR includes the following steps performed in order.

Prepare the Oracle to log into the redo log before and after images for all columns, not only keys and changed data, for tables in the replication (test5.seat_assignment,test5.account)

Oracle 10g introduced the recycle bin. You can recover a table that you have dropped from the Oracle recycle bin by using the flashback table command as seen here:

SQL> DROP TABLE books;

SQL> FLASHBACK TABLE books TO BEFORE DROP;

The recycle bin uses the flashback table command.

However, the more time that has passed since the table was dropped, the less likely it will be in the Oracle recycle bin (The Oracle recycle bin is purged periodically based on a number of different criteria).

The contents of the Oracle recycle bin can be viewed from SQL*Plus by using the show recyclebin command:

Managing the Recycle Bin

The recycle binis a new feature in Oracle 10g which keeps dropped objects. When you drop an object, no space is released and the object is moved to the logical container called the recycle bin. In case you want to get the table back, issue the flashback drop command as it was explained in the previous scenarios. Each user has a view called recycle_bin which he can use to get the list of dropped objects.

You can query the dropped object by not restoring it from the recycle bin. This is done by using the special name that was given to the dropped object by Oracle, i.e. the object name starting with bin$. To get the name of all dropped objects, use the show recycle_bin command. More detailed information can be found by querying the user_recyclebin view. To understand the concept, see the following example:

Note: When running queries for used space and free space in a tablespace, segments that have moved to the recyclebin will not be listed as normal table/index segments consuming used space, but will reduce the free space. So be aware of the size of the recyclebin when generating space usage reports for the database.

Recycle bin objects, i.e. dropped objects, will not be included during the Oracle export. Only the real objects can be exported. So after importing, there is no need to panic when you find that the total number of objects count is different from source to target.

Purging Objects From the Recycle Bin

To remote the tables and indexes from the recycle binand free the space that they consume, use the PURGE clause.

There is a recyclebin parametern the parameter file whose default is ON.

SQL>
show
parameter recyclebin;

NAME TYPE VALUE
————————– ———– —————————–
recyclebin string ON
SQL>

To disable it to function, use:

alter system set recyclebin=off;

To disable it in the session level, use:

alter session set recyclebin=off;

To delete the table without putting it in the recycle bin, use the purge command at the end of the DROP TABLE clauseas follows:

SQL>
create
table tbl_rc (id number);
Table created.

SQL>
drop
table tbl_rc purge;
Table dropped.

SQL>
show
recyclebin;
SQL>

Although this is enabled by default, if the parameter recyclebin is set to OFF at the instance level, dropped tables are not retained in the recycle bin. Similarly, if the tablespace has low free space, older dropped tables are silently purged from the recycle bin. So it is advisable to query the recycle bin immediately after the problem is identified. Take care to ensure that a recycle bin is available before running your tests for flashback query on a dropped table or flashback table to before drop.

Purging Objects in the Oracle Recycle Bin

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

Using Oracle’s recycle bin

One of the many new features that Oracle 10g introduced is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go “into” the recyclebin, and can be restored from the recyclebin. OraFAQ has already published an article covering the basics; in this article, I’ll cover some of the more subtle aspects of the recyclebin.

THE BASICS

First, a quick review of the basics. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

Since the table data is still there, it’s very easy to “undrop” the table. This operation is known as a “flashback drop”. The command is FLASHBACK TABLE… TO BEFORE DROP, and it simply renames the BIN$… table to its original name:

It’s important to know that after you’ve dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER .

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

DROPPED TABLE VERSIONS

Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin. For example, if we create and drop the TST table twice, we’ll have two versions in the recyclebin:

Oracle always restores the most recent version of the dropped object. To restore the earlier version of the table, instead of the later one, we can either keep flashing back until we hit the version we want, or we can simply refer to the correct version of the table by using its new BIN$… name. For example, dropping TST once more gives us two versions in the recyclebin again:

DEPENDENT OBJECTS

In a modern relational database, few tables stand alone. Most will have indexes, constraints, and/or triggers. Dropping a table also drops these dependent objects. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, though, are invalidated.) For example:

The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TST_COL is 233434. Note the value of the BASE_OBJECT column for IND_TST_COL: 233031, the object number of the associated version of the TST table.

If we FLASHBACK DROP the TST table, its index will be restored – but Oracle will not rename it to its original name. It will retain its BIN$.. name:

I’m not sure why Oracle bothers storing the index’s original name, since it doesn’t seem to be used for anything. If we now drop this copy of the TST table, Oracle doesn’t “remember” that the original name of the index “BIN$HGnc55/+rRPgQPeM/qQoRw==$0″was IND_TST_COL – the ORIGINAL_NAME column in RECYCLEBIN holds the ugly string “BIN$HGnc55/+rRPgQPeM/qQoRw==$0” :

Note the values in the CAN_UNDROP and CAN_PURGE columns for the index (displayed as “UND” and “PUR” above). An index cannot be undropped without the table – so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:

If you drop a table with associated LOB segments, they are handled in a similar way, except that they cannot be independently purged: CAN_UNDROP and CAN_PURGE are set to NO, and they are purged if you purge the table from the recyclebin, restored with the table if you restore it.

LIMITATIONS

A few types of dependent objects are not handled like the simple index above.

Bitmap join indexes are not put in the recyclebin when their base table is DROPped, and not retrieved when the table is restored with FLASHBACK DROP.

The same goes for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.

Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.

DISABLING THE RECYCLEBIN

In Windows, you can choose to permanently delete a file instead of sending it to the recycle bin. Similarly, you can choose to drop a table permanently, bypassing the Oracle recyclebin, by using the PURGE clause in your DROP TABLE statement.

If you disable the recyclebin at the session level, with ALTER SESSION SET RECYCLEBIN=OFF, it has the same effect as putting PURGE at the end of all your drop statements. Note, however, that you can still use FLASHBACK DROP to restore objects that were put in the recyclebin before you set RECYCLEBIN=OFF. For example:

CONCLUSION

This article has explored some of the subtler ramifications of the recyclebin. To sum up:

-The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version’s BIN$… name directly.
– Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them.
– Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.