GoldenGate unsupported types

identity
When using GoldenGate there are few rules which need to be followed, and a very small number of unsupported datatypes. This is the excerpt from the GoldenGate 12.2 manual:

 

 

1.6.10 Non-Supported Oracle Data Types
Oracle GoldenGate does not support the following data types.

 ANYDATA fetch-based column support for data types with VARRAYS that do not include named collections and VARRAYS embedded within those data types
 ANYDATASET
 ANYTYPE
 BFILE
 MLSLABEL
 ORDDICOM
 REFs
 TIMEZONE_ABBR
 URITYPE
 UDT with containing an unsupported Oracle Data Type
 Oracle GoldenGate does not support replication of identity column data or Valid Time Temporal column data.

One of those may be more common that you think – the last bullet point is the Oracle 12C datatype IDENTITY. The following code snippet is not supported for replication by GoldenGate

create table with_identity
( COL_ID    number(38) generated always as identity minvalue 1 not null enable,
  text_info varchar2(10),
  constraint with_identity_pk  primary key (COL_ID) );

insert into with_identity (text_info) values ('Row 1');

commit;

desc with_identity
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 COL_ID                        NOT NULL NUMBER(38)
 TEXT_INFO                              VARCHAR2(10)

You can see that it is an identity column in user_tab_columns:

select TABLE_NAME,COLUMN_NAME,IDENTITY_COLUMN from user_tab_columns where table_name = 'WITH_IDENTITY';

TABLE_NAME                     COLUMN_NAME                    IDE
------------------------------ ------------------------------ ---
WITH_IDENTITY                  COL_ID                         YES
WITH_IDENTITY                  TEXT_INFO                      NO

So, how does GoldenGate alert you to the fact that it will not be replicating this table with an unsupported type? It doesn’t.

look in ggserr.log – nothing
look in the extract report file for wildcard matching the table – nothing

With a table containing an unsupported data type, the lack of replication is silent because GoldenGate is completely ignoring the object. Completely Silently.

So, how could I have picked this up before it became a problem?

Obviously I need to run the script in MOS article 1296168.1, which analyzes your schema for GoldenGate compatiblity. Unfortunately, Oracle have not updated this to account for IDENTITY columns, so there is no indication that it does not work!

The script to run is the GoldenGate Integrated Extract Healthcheck script, found in MOS article 1448324.1

Finally some vague and unclear indication that we may have a problem. Part way through the report we get this:

++ TABLES SUPPORT BY GOLDENGATE Integrated Capture ++
Lists tables that can not be supported by OGG (NONE)
Lists table that are supported via OGG FETCH (ID KEY)

Owner           OBJECT_NAME             SUPPOR  Container ID
C##GOLDENGATE 	AQ$_OGG$Q_TAB_E_HR_C 	NONE 	1
C##GOLDENGATE 	AQ$_OGG$Q_TAB_E_HR_D 	NONE 	1
C##GOLDENGATE 	OGG$Q_TAB_E_HR 	        NONE 	1
HR              WITH_IDENTITY         ID KEY     3
IX 	        AQ$_ORDERS_QUEUETABLE_G NONE 	3

Wow.
Always take care with GoldenGate – it’s not just a plug-in solution. You need to think about the implementaiton and work with the application to get the best outcomes.

Goldengate Log Rotation

Golden Gate 12 has some excellent commands to keep your log files in check, plus one glaring omission (scheduled for a future enhancement)

Each extract, datapump and replicat will be writing to report (.rpt) and discard (.dsc) files in the dirrpt directory (if you aren’t specifying a discard file, you should. They are very useful for troubleshooting)
If your system is up for a long time, these files are going to get large. Oracle has realised this and provides some lovely in-built log rotation commands. To keep my parameter (.prm) files nice and neat and consistent, I use include files, and this is a perfect case for a standard include.

report.prm:

-- Standard include commands for ALL extracts and replicats to ensure they are aligned
-- Write the days stats out to the file at the end of every day.
-- Roll the file over every week
-- Report just how much throughput we have every 15 minutes
-- History: 14.04.2015 N Chandler 
-- place the command include/dirprm/report.prm in your parameter files

STATOPTIONS REPORTDETAIL, RESETREPORTSTATS
REPORT AT 23:59
REPORTROLLOVER AT 00:01 ON MONDAY
REPORTCOUNT EVERY 15 MINUTES, RATE
-- or if you would rather by volume...
--REPORTCOUNT EVERY 10000000 RECORDS, RATE

I think it’s worth pointing out here what the 2 bracketed throughput numbers output by the REPORTCOUNT commands mean, as you’ll struggle to find it in the documentation

Rate  = number of records processed per second since startup divided by the total time since startup of the extract/replicat
Delta = number of records processed per second since last report divided by time since last report (in this case, 15 minutes)

 

 

There is 1 notable growing file which you cannot rotate using Goldengate commands: ggserr.log

This is a significant oversight by Oracle and will be rectified in a future release, but as of 12.1 you have to manually sort this out. You have 2 main options to do this:

1. Stop the manager, rename the file, restart the manager
2. Copy the file to a new file and then empty the in-place file by catting /dev/null into it. (I’m sure there’s a Windows equivalent of this, but I mainly work on Unix)
* DO NOT simply delete the file while the manager is running.
All future error output will drop into a “black hole” until the manager is restarted.   Option 2 tends to be preferably, so here’s part of a bash script I use to perform this action

#!/bin/bash
# rotate_ggserr_log.sh - copies the logfile to one side with a date suffix and blows away the current file
# but leaves it in place to we can continue to write to it with the manager.
# Neil Chandler 14.04.2015 created
#
today=`date +%Y%m%d`

-- Check to see if we have already rolled-over today
if [ -e /u99/gg/bin/ggserr.log.${today} ]
then
 echo "File /u99/gg/bin/ggserr.log.${today} exist already. Stopping."
else
 # copy the log file preserving attributes
 /bin/cp -pnv /u99/gg/bin/ggserr.log /u99/gg/bin/ggserr.log.${today}

 # See if there is a difference - did you copy it successfully?
 diff /u99/gg/bin/ggserr.log /u99/gg/bin/ggserr.log.${today}
 RC=$?

 # If there is no difference, wipe the ggserr.log file out
 # otherwise stop!
 if [ ${RC} -eq 0 ]
 then
  echo "clear the file /u99/gg/bin/ggserr.log"
  cat /dev/null > /u99/gg/bin/ggserr.log
  exit ${RC}
 else
  echo "Error - cannot clear file /u99/gg/bin/ggserr.log as it's not the same as the copied version. Stopping."
  exit ${RC}
 fi
fi
%d bloggers like this: