Monday, October 31, 2011

Upgrade Oracle Database using Oracle GoldenGate

I have used many upgrade options in the past to Upgrade an Oracle database. Be it an inplace upgrade, out of place upgrade, upgrade on logical standby, using materialized view logs and views or using GOLDENGATE. Turns out Oracle GoldenGate is the best amongst all of them.

We get the most out of this replication in terms of reliability, speed, error-free, ease of use, utmost least downtime. Here are a few more advantages compared to others:

Advantages:
1. Least amount of work upfront.
2. Least amount of errors.
3. Low overhead of troubleshooting if something fails.
4. Least amount of programming effort.
5. Least amount of DOWNTIME.
6. On going progress can be maintained. You can test your upgraded version while sync'ing your production.You can also compare data between your old and new production all the time.

The only disadvantage is that there is a price TAG associated with OGG as with any other product. Contact us and we maybe we able to help you and help you succeed with your upgrade plans.

Monday, October 24, 2011

Replicat Abended Due To Oracle Error ORA-12899

PROBLEM STATEMENT:

The report file contains:
2011-10-24 05:48:25 WARNING OGG-00869 Aborting BATCHSQL transaction. Database error 12899 (ORA-12899: value too large for column "SECS5"."ARDELETEDACCTHOLDERAUTH"."HI
NTQ" (actual: 137, maximum: 128)).
2011-10-24 05:48:25 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.
2011-10-24 05:48:25 WARNING OGG-01003 Repositioning to rba 388304405 in seqno 362.
2011-10-24 05:48:26 WARNING OGG-00869 OCI Error ORA-12899: value too large for column "SECS5"."ARDELETEDACCTHOLDERAUTH"."HINTQ" (actual: 137, maximum: 128) (status =
12899), SQL ARDELETEDACCTHOLDERAUTH" ("BANKID","RANGEID","ACTIVE","CARDNUMBER","CARDHOLDERNAME","DATEEXPIRED","SHOPPE
RID","SECRETCODE","CHIPSECRET","HINTQ","HINTA>.


PROBLEM AREAS:

1. You have a different NLS_LANG on the source and target system.
or
2. You are using replicat parameter "REPLACEBADCHAR SPACE"


SOLUTION:

Change/add the NLS_LANG param on the replicat to match what is specified on the extract. Make sure to add NLS_LANG parameter is added before the USERID and PASSWORD fields to connect to the database.

In the extract parameter file the NLS_LANG parameter has been set as follows

setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

Example

setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

userid xxxx password yyyy


2. If you are using replicat parameter "REPLACEBADCHAR SPACE"

Another cause for ora-12899 is use of replicat parameter "REPLACEBADCHAR SPACE" when processing multiple byte (multibyte)data. the parameter should not be used for multi-byte data.

For the second cause, do not use "REPLACEBADCHAR SPACE" when processing multi-byte data.


Sunday, October 23, 2011

Database Replication: Streams vs. GoldenGate

Replication in any database provides functionality to facilitate replication (one way or multi), upgrades, reporting, data warehousing, query offloading, and many others without having to put pressure on production. Actually the main benefit of replicating data in a database is to reduce and if possible, eliminate, the load on the source system. It also allows you to offload some percentage of data thus move load to another server. Replication of part of the source may be more efficient than cloning it completely by the use of a standby system, when remote sites do not need to be exact copies of the source databases.

From the point of Replication, Streams and GoldenGate might seem similar but are very different. Streams for starters sits inside the database which in experience, makes it slower. GoldenGate on the other hand sits outside and uses the memory from the OS and NOT from the database. It's basic design is to read redologs/archive logs like a journal and record all new transactions that it encounters (of course based on your parameter settings).

Streams architecture on the other hand uses AQ's within the database which has proven to be very inefficient. More over streams for heterogeneous environments is very convoluted. Personally I have yet to see a successful efficient Heterogeneous environment that people would prefer to use Streams over OGG.

Some of the places where OGG is a hit:
1. One way replication - Reporting
2. One way replication - DR (esp when the network pipe is small)
3. One way replication - DW/ODS/BI
4. Query off-Loading
5. Heterogeneous Replication (From Oracle -> MS SQL,etc)
6. Multi master between two database (Homogenous or Heterogeneous).






Wednesday, October 19, 2011

Data Type Support for GoldenGate 11.1.1.1

This blog post shows what is supported / Unsupported in Goldengate 11.1.1 Replication

Below are the supported and unsupported data types in Goldengate 11.1.1 for Oracle databases. These are the basic limitations that must be taken into consideration when configuring replication using Goldengate.

DATA TYPES
Supported GoldenGate Oracle Data Types

NUMBER
BINARY FLOAT
BINARY DOUBLE
CHAR
VARCHAR2
LONG
NCHAR
NVARCHAR2
RAW
LONG RAW
CLOB
NCLOB
BLOB
XML data types
User defined types (UDT)
DATE
TIMESTAMP
TIMESTAMP with TIMEZONE


Non-Supported Goldengate Oracle Data Types
ORDDICOM
ANYDATA
ANYDATASET
ANYTYPE
BFILE
MLSLABEL
TIMEZONE_ABBR
TIMEZONE_REGION
URITYPE
UROWID
DDL REPLICATION SUPPORT

Support for DDL on objects:
TABLES
INDEX
TRIGGERS
SEQUENCES
MATERIALIZED VIEWS
VIEWS
FUNCTION
PACKAGES
PROCEDURE
SYNONYMS
PUBLIC SYNONYMS

DDL On ALL Oracle Reserved Schemas is unsupported.
These include:
ANONYMOUS
AURORA
$JIS
$UTILITY
$AURORA
$ORB
$UNAUTHENTICATED
CTXSYS
DBSNMP
DMSYS
DSSYS
EXFSYS
MDSYS
ODM
ODM_MTR
OLAPSYS
ORDPLUGINS
ORDSYS
OSE$HTTP$ADMIN
OUTLN
PERFSTAT
PUBLIC
REPADMIN
SYS
SYSMAN
SYSTEM
TRACESVR
WKPROXY
WKSYS
WMSYS
XDB

ADDITIONAL UNSUPPORTED OBJECTS TO WATCH OUT FOR:

External tables are not supported
Compressed tables are not supported
TDE at column and tablespace level is supported. Check out Release guide for more information.


Restrictions on using DDL Replication

OGG: Restrictions on using DDL replication

Oracle GoldenGate supports DDL synchronization only in a like-to-like configuration.

Oracle GoldenGate DDL replication requires the following:
Source and target object definitions must be identical.

The ASSUMETARGETDEFS parameter must be used in the Replicat parameter file. Delivery process (Replicat) will fail if objects are configured for DDL support and the SOURCEDEFS parameter is being used.

DDL Constraints

Oracle GoldenGate supports DDL synchronization only in a like-to-like configuration.

Oracle GoldenGate DDL requires the following:
Source and target object definitions must be identical.

The ASSUMETARGETDEFS parameter must be used in the Replicat parameter file. Delivery process (Replicat) will fail if objects are configured for DDL support and the SOURCEDEFS parameter is being used.