Database Replication with GoldenGate software

For a number of reasons - people replicate data to remote destinations. We do have our own needs. We have been running SharePlex for data replication purpose for the last 3 years. By this time, our system usages increased more than twice in just last year. Initially SharePlex was good for us but now a days we need more flexible configurations and different replication architecture for the system.

We are looking for an alternative database replication mechanism that would suit best for us. We are exploring new technologies available to serve replication purpose.

Now, we found two alternatives to be considered as we need more control for an "Active-Active" mode -

1. Oracle Streams
2. GoldenGate Software

I did several extensive experiments with Oracle Streams for about 1.5 months. You can guess, there are lots of outcomes. This post is for telling a bit about my GoldenGate experience!

GoldenGate Software (GGS) is a popular one used for data replication and keep several database in sync. They are supporting a number of database including Oracle, Teradata, DB2, Sybase, SQL Server. It seems that GGS are in market for long time, probably before Streams introduced by Oracle, now they are running version 9.5.

The basic concepts of GGS are - GGS has EXTRACT process which captures changes in source database. The changes are kept in files called TRAIL. The changes can be directly sent to the target/destination database's REPLICAT process to apply the changes in destination database. GGS EXTRACT reads data from oracle online redo log file - so it's independent of oracle process. I forgot to mention, GGS has several processes, MANAGER is one of the important process which managers all other stuffs.

My first "one way" replication test configuration (obvious these are not all for production db) with GGS were as follows -


Source Neptune: Create EXTRACT which will capture changes and send the changes to remote host's TRAIL files

EDIT PARAMS extprod

EXTRACT extprod
USERID ggs, PASSWORD ggs
RMTHOST saturn, MGRPORT 7820
RMTTRAIL /export/home/oracle/ggs/dirdat/rt
TABLE DEV.LOGIN;

**

ADD EXTRACT extprod, TRANLOG, BEGIN NOW

**
ADD RMTTRAIL /export/home/oracle/ggs/dirdat/rt, EXTRACT extprod, MEGABYTES 50

Destination Saturn: Create REPLICAT which will read changes from TRAIL files and apply the changes in current database

EDIT PARAMS repprod

REPLICAT repprod
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/REPSOE_DSC.rpt, PURGE
USERID ggs, PASSWORD ggs
MAP DEV.*, TARGET DEV.*;

**
ADD REPLICAT repprod, EXTTRAIL /export/home/oracle/ggs/dirdat/rt, NODBCHECKPOINT

So, these were the basic stuffs that I started with GoldenGate Software (GGS). They have thousand page documents and there are a lot more to test. Earlier, we created spreadsheet putting about 30 evaluation criteria against which we compared SharePlex and Oracle Streams. After experimenting with GGS, I will put the outcomes for GGS and would take the ultimate product decision.

Expecting comments if you have any good/bad experience with the product!

Oracle: Index auto dropped while importing. Where my index gone?

One day, someone was telling me that he had been lost in blue, one index was vanished!

We have multiple production servers. So, taking a dump from one server and import it to another is quite common for initial synchronization. Like this, one day, one of my colleague found that schema on p01 database had one less index than s01 database. One index was not created in p01 while imported table "TITLE". I investigated and found that it happened for an interesting feature of oracle!

The "TITLE" table has primary key ID. You know, when primary key is created, one system generated index is created with the primary key column to enforce the constraint.

During the release 7.2, we created a composite index named "TITLE_ID_TITLE" on table "TITLE" in database server s01 using two columns including the primary key column "ID" and another column, "TITLE" (TITLE table has a column named TITLE).

Now, when oracle was importing indexes for table TITLE on p01, it found that the composite index "TITLE_ID_TITLE" is enough to enforce the primary key - so it automatically discarded the system generated index which was created with the primary key.

That's the ghost story behind this!

SpringSource launches new Application Platform

SpringSource, the company behind Spring framework, launches new Application Platform without Java EE standerds! The Application Platform has been built on Spring, OSGi, and Apache Tomcat. The new appserver departs from the Java2 EE standards, exposing the Spring programming model natively, along with a new deployment and packaging system.

I was reading the overview of it and was amazed by reading the following development and production benefits. It is our dream to modularize the big applications that we are developing and do the updates in production server in real time without shutting down the system for long time.

I am just pasting the benefits SpringSource are talking about -

Production Benefits

• Real time application and server updates
• Better resource utilization
• Side by side resource versioning
• Longer server uptime
• Run new and old web application

Development Benefits

• Faster iterative development
• Small server footprint
• More manageable applications
• Greater code reuse
• Resolve transitive dependencies

Nice blogs have been written by the development team about the architecture.

http://blog.springsource.com/main/2008/04/30/introducing-the-springsource-application-platform/

The overall introduction is given on the official site of SpringSource -

http://www.springsource.com/web/guest/products/suite/applicationplatform

I have the strong desire to explore more on this!

Handling BIG Database Table "UserActivity"

Let's start with the stats -

We have over 70 million records in user_activity table now. As the name implies, user_activity keeps record of users' actions in applications.

The growths for year 2007 were like (in million) -

1st quarter: 6
2nd quarter: 8.5
3rd quarter: 10.5
4th quarter: 12

These huge number of records were giving a lot more pain than expected. SharePlex tool can not sync this big table between databases. This table is occupying 1/3 size of the whole database. Handling dump requires extra time and space. We could go for oracle partitioning but it will not serve our purpose here.

I thought to handle it by ourselves. I decided to to split user_activity table into a number of tables according to date range. For example, all 2004 data would reside in one table, 2005 data would go to another table, the same for 2006 data as well. I am keeping these yearly data together as we had less amount of data during those years. Then I split data quarterly from 2007 and onwards. Again, this is not fixed, in 2009 when we would have 100K system users, we might need to split monthly :)

Now, user_activity table is divided into a number of tables like -

UA_200401_200412
UA_200501_200512
UA_200601_200612
UA_200701_200703
UA_200704_200706
UA_200707_200709
UA_200710_200712
USER_ACTIVITY

After each quarter of 2008, at some time, I will run the dynamic partitioning script which will split user_activity again and will create a new table. For example, the first quarter table will be UA_200801_200803.

We have programming challenges here. What if when user select a date range where we need to fetch data from multiple tables and how do we formulate a clean logic for that?

Okay, we need some generic mechanism so that the code remains clean and scalable. I created a table USER_ACTIVITY_SPLIT_INFO which provides the necessary table(s) information from where user activity data will be looked up after breaking the original table. This table contains information like follows -

TABLE_NAME	BEGIN_DATE	END_DATE
UA_200401_200412	25-OCT-03 02.23.22.000000 PM	31-DEC-04 03.50.56.000000 PM
UA_200501_200512	01-JAN-05 05.09.38.000000 AM	31-DEC-05 11.58.52.000000 PM
UA_200601_200612	01-JAN-06 12.01.23.000000 AM	31-DEC-06 11.59.55.956000 PM
UA_200701_200703	01-JAN-07 12.00.27.530000 AM	31-MAR-07 11.59.59.344000 PM
UA_200704_200706	01-APR-07 12.00.00.786000 AM	30-JUN-07 11.59.58.917000 PM
UA_200707_200709	01-JUL-07 12.00.00.055000 AM	30-SEP-07 11.59.57.929000 PM
UA_200710_200712	01-OCT-07 12.00.00.811000 AM	31-DEC-07 11.59.57.893000 PM
USER_ACTIVITY	01-JAN-08 12.00.02.140000 AM

Now, for example, user is seeking data for a date range 11-01-2007 to 04-01-2008. The following query will return the name of table(s) from where data need to be fetched.

SELECT x.table_name
FROM user_activity_split_info x
WHERE x.begin_date BETWEEN TO_DATE('11-01-2007','mm-dd-yyyy') AND TO_DATE('04-01-2008','mm-dd-yyyy')
OR x.end_date BETWEEN TO_DATE('11-01-2007','mm-dd-yyyy') AND TO_DATE('04-01-2008','mm-dd-yyyy');

Output:

TABLE_NAME

UA_200710_200712

USER_ACTIVITY

So it just returned two table names where the data would be found.

In our application code, a method will return the name of table(s) based on the user's date input and we would fetch data directly from those tables and add with combine results together. The programmer does not have to know how many tables to deal with!

One more thing, we don't have to worry about the sequence number which would cross limit after certain years. Now we can reset user_activity sequence at any point just with a single command.

I also came up with another approach which would provided more simpler programming - almost noting to change in code but that approach requires user_activity data duplication by overlapping each three months data! This is not feasible for a table like user_activity - but it was an option.

Let's see how things work!

Data transfers through SQL*Net

We need to check how much data is going back and forth from database servers to take some system engineering decisions. In order to know what is the flow rate for current sessions, I issued the following SQL -

/** Current Sessions **/

SELECT n.name,ss.username, ROUND((SUM(st.value))/1024/1024) MB
FROM v$sesstat st,v$session ss ,v$statname n
WHERE ss.SID=st.SID
AND n.statistic# = st.statistic#
AND n.name like '%SQL*Net%'
HAVING ROUND((SUM(st.value))/1024/1024) > 0
GROUP BY N.NAME,ss.username
ORDER BY ss.username, n.name;

NAME	USERNAME	MB
---------------------------	--------------	-----------
SQL*Net roundtrips to/from client	DBSNM	1
bytes received via SQL*Net from client	DBSNM	182
bytes sent via SQL*Net to client	DBSNM	154
bytes received via SQL*Net from client	PROD	68
bytes sent via SQL*Net to client	PROD	141

Well, how do I know the historical data for the instance? The answer could be like follows -

/** Since Instance Startup **/

SELECT NAME,ROUND(VALUE/1024/1024) MB
FROM v$sysstat
WHERE NAME like '%SQL*Net%'
AND ROUND(VALUE/1024/1024) > 0
ORDER BY name;

NAME	MB
-------------------------------------	-------------------
SQL*Net roundtrips to/from client	380
bytes received via SQL*Net from client	46331
bytes sent via SQL*Net to client	92414

There are other ways to do that from the trace files. If I enable trace level by adding the following lines in my listener.ora and reload it, then I would find my desired stuffs in the trace file.

-- listener.ora

TRACE_FILE_LISTENER = netstat-info-20080409.trc
TRACE_DIRECTORY_LISTENER = /export/home/oracle
TRACE_LEVEL_LISTENER =SUPPORT

From command line as oracle user -
# lsnrctl reload

Now it's time to check the logs after certain period -

# trcasst -s /export/home/oracle/netstat-info-20080409.trc

The trcasst just came up with the nice formatted output - cool !


*************************************************************************
* Trace Assistant *
*************************************************************************

----------------------
Trace File Statistics:
----------------------
Start Timestamp : 09-APR-2008 13:03:10:531
End Timestamp : 09-APR-2008 19:54:36:028
Total number of Sessions: 1172

DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES


ORACLE NET SERVICES:
Total Calls : 1593 sent, 1427 received, 0 oci
Total Bytes : 292573 sent, 255785 received
Average Bytes: 183 sent per packet, 179 received per packet
Maximum Bytes: 2011 sent, 2034 received

Grand Total Packets: 1593 sent, 1427 received


*************************************************************************
* Trace Assistant has completed *
*************************************************************************

Stop user from accidental database damage!

The guys who play with database can not help themselves doing crazy stuffs always. Sometimes we logged in multiple production servers and also test servers simultaneously. These sessions are opened in multiple console tabs. It might be happened, we want to run some operations on test servers but accidentally run those on production boxes. Just imagine, I want to run TRUNCATE TABLE operation on test box but I did it on prod boxes actually - opss! what a disaster!

To avoid this types of blundering actions, I have introduces the following trigger to prevent any kind of unconscious DDLs in production boxes. The trigger will be stored on proper schema always and if any DDL issueed in the presence of the trigger, it will simply slap us :P

CREATE OR REPLACE TRIGGER ddl_restrict_trigger
BEFORE ALTER OR CREATE OR DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20001,' You are not authorized to perform DDL. Please contact DBA team.');
END;
/

Read-only database user

Sometimes the application developers need to debug data from the production database. It demands to give access to the live schema. How can I trust a developer who accidentally won't modify stuffs in database and blander everything!

I need a read-only user on which developers only would be able to issue "select" statements. For now, it will serve my purpose.

What I did, I created a user, created role with select any table privileges and assigned that user with the role. Now the developers are restricted to issue other than select commands - cool!

There are two ways of doing this. Here are the scripts for that -

** option-1 **

> sqlplus system/*******
> create user devels identified by xyz;

> create role SELECT_ANY_TABLE;
> grant select any table to SELECT_ANY_TABLE;

> grant connect to devels;
> grant SELECT_ANY_TABLE to devels;

** option-2 **

> sqlplus system/******
> create user devels identified by xyz;
> sqlplus prod/***********

Now have to run the following piece on PL/SQL

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT x.table_name FROM user_tables x;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

The second option has a drawback - each time we add new tables in schema, we have to run it.

Latch Contention in Oracle

What is Latch?

Well, the hardware definition of latch is - a window or door lock. The electronic definition of latch is - an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration - might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Unfortunately we are experiencing Cache Buffer Latch contention in database now a days.

Identifying HOT BLocks

Possible hot blocks in the buffer cache normally can be identified by a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch.
This latch is acquired when searching for data blocks cached in the buffer cache. Since the Buffer cache is implemented as a sum of chains of blocks, each of those chains is protected by a child of this latch when needs to be scanned. Contention in this latch can be caused by very heavy access to a single block. This can require the application to be reviewed.

By examining the waits on this latch, information about the segment and the specific block can be obtained using the following queries.

First determine which latch id (ADDR) are interesting by examining the number of sleeps for this latch. The higher the sleep count, the more interesting the latch id (ADDR) is:

select * from (
select CHILD# "cCHILD", ADDR "sADDR",
GETS "sGETS" , MISSES "sMISSES",
SLEEPS "sSLEEPS"
from v$latch_children

where name = 'cache buffers chains'

order by 5 desc, 4, 1, 2, 3
) where rownum <=20;

Have to run the above query a few times to to establish the id (ADDR) that has the most consistent amount of sleeps. Once the id (ADDR) with the highest sleep count is found then this latch address can be used to get more details about the blocks currently in the buffer cache protected by this latch. The query below should be run just after determining the ADDR with the highest sleep count.

column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = 'ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;

Depending on the TCH column (The number of times the block is hit by a SQL statement), you can identify a hotblock. The higher the value of the TCH column, the more frequent the block is accessed by SQL statements.

The solution of the problem will vary depending upon the problem and application architecture.
Some generic approach might be -

1. Creation of Reverse Index of those hot blocks
2. Distribute hot blocks table on different size cache segment
3. Ensure the usages of index to reduce full scan