Wednesday, August 28, 2013

ORA-01114: IO error writing block to file 8 (block # 3307) ORA-29701: unable to connect to Cluster Manager

Was getting that error for any ddl operation.

crsctl check crs throws this error:

>crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon

vxfs: msgcnt 2849 mesg 001: V-2-1: vx_nospace - /dev/vx/dsk/local_ora/oracle_lv                                                           file system full (1 block extent)Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manager

That tells me that ORA-1114 is nothing to worry as this is just file system is full and oracle was not able to write any traces.

Clear up the mount point and you should be good.

Thursday, August 22, 2013

Statistics reply buffer exceeded. Results truncated...

In GoldenGate when using "stats extract group_name" you may get this error when the ouptut is too big to fit in the buffer:

"Statistics reply buffer exceeded.  Results truncated..."

You can write the statistics to a report file like below for the entire group stats:

GGSCI (ndhdbp3) 18> send epcs07, report

Sending REPORT request to EXTRACT EPCS07 ...
Request processed.


Or use stats extract group_name, table owner.table_name(or *) to get stats on individual table or schema.

Tuesday, August 20, 2013

Failed to get next ts for EXTRACT PPCS07 (error 109, Invalid pointer retrieving GGS logtrail timestamp in )

GGSCI (dbsrv1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EPCS07      00:48:12      00:00:08
Failed to get next ts for EXTRACT  PPCS07     (error 109, Invalid pointer retrieving GGS logtrail timestamp in )


GGSCI (ndhdbp4) 3> info *

EXTRACT    EPCS07    Last Started 2013-08-20 14:05   Status RUNNING
Checkpoint Lag       00:57:05 (updated 00:00:10 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-08-20 14:19:58  Thread 1, Seqno 34114, RBA 1065005856
                     SCN 69.2532298994 (298885042418)
Log Read Checkpoint  Oracle Redo Logs
                     2013-08-20 14:20:00  Thread 2, Seqno 33665, RBA 268387208
                     SCN 69.2532299819 (298885043243)

EXTRACT    PPCS07    Last Started 2013-08-20 15:02   Status RUNNING
ERROR: Error retrieving current checkpoint timestamp.

That message keeps coming for every Extract process we run so thought of nailing it down to see whats causing this message to popup when the Extract and Pump process is running fine.

Well, the reason was simple:
When creating Pump process, we used "Threads 2" (just like for Extract process) and that "Threads 2" option is not valid with Pump process. Thats why we were getting this message.

As you see "info *" also errors out when it tries to show info for thread 2.

Recreate the pump process without this parameter and you wont see this coming up again...

Golden Gate Initial Load Methods...

GoldenGate (Rel.11) provides multiple choices on performing Initial Load for any Migration or DR setup apart from other Legacy methods such as RMAN backup/restore, cloning etc.,

I am working on migrating 10g database from HP unix to 11gR2 Linux. Pretty simple migration one can think...
The catch is we are dealing with databases ranging from 2TB to 40TB.
So, XTTS (Cross Platform Transportable Tablespaces) is also not an option with 40TB database.

So, went with GoldenGate and trying to chose the best Initial Load methodology and thought of sharing that info here.

Below are the Initial Load GoldenGate methods:

Opt#OptionConceptProsConsMet#
1Loading Data from File to ReplicatInitial load extract writes the records to an external file which Replicat uses to apply the changes to the target site.Quite a slow initial-load and cannot be used due to limitations such as column size/data-type limitations.1441172.1
1.aLoading Data from Trail to ReplicatInitial load extract writes the records to a series of external files which Replicat uses to apply the changes to the target site.Faster than #1 because replication can begin while extraction is already started. It can also process a near unlimited amount of tables. 1195705.1
2Loading Data from File to Database UtilityInitial load extract writes the records to an external ASCII files. These files are uses as datafiles at target by a data bulk load utility native to the database such as SQLLoader1457989.1
3Loading Data with an Oracle GoldenGate Direct LoadInitial load extract extracts the records and sends them directly to a Replicat initial-load task.FasterNo support for LOBs, LONGs and UTD's and any other data-type larger than 4K.1457164.1
4Loading Data with a Direct Bulk Load to SQL*LoaderInitial load extract extracts the source records and sends them directly to an initial-load Replicat task, which is dynamically started by manager. The initial load Replicat task interfaces with the SQL*Loader API to load the data as a direct-bulk loadNo support for LOBs, LONGs.
Only works with Oracle's SQLLOADER.
146185.1

As you see none of the above, method 1, 1a, 3 and 4 are not going to work for my situation as we have plenty of CLOB's, LONG's and UTD's (User Defined Datatype).
So only option left is #2 and I really did not want to use that method as our databases are not in GB range but TB's and trying to extract the data into ASCII file and storing them on source and transferring to target over network and load them... no. not really an option to chose from.

So, what's my option for Initial load then?

I propose pure legacy method using EXP(dp)/IMP(dp)  as this will be lot faster than option#2 and merely there is no other option left.

Check the note below:
999EXP/IMPBig UNDO as this will be with SCN1276058.1

Also planned on moving more than 50% of 40TB data to Target prior to starting Extract process to reduce the overall migration time.
Reach me out for more details on this monster migration.