In this post I am sharing my experience on how I solved the below error.,
Suddenly one of my Integrated Replicat Porcess UODSREP got abended with the below error.,
REPLICAT RUNNING TRAREP 00:00:00 00:00:04
REPLICAT ABENDED UODSREP 20:58:15 00:46:20
The error in the View Report or in the GGSERR.log file was as below.,
2015-01-03 01:28:52 ERROR OGG-00662 OCI Error Flushing database inbound server, ‘OGG$UODSREP’ (status = 26804).
2015-01-03 01:37:55 ERROR OGG-00664 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: OCI Error Flushing database inbound server, ‘OGG$UODSREP’ (status = 26815-ORA-26815: Error from Apply Network Receiver for Apply “OGG$UODSREP” and Capture “replicat”.
ORA-01722: invalid number).
2015-01-03 01:37:56 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2015-01-03 01:37:58 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: PROCESS ABENDING.
I thought to check if any error in the Database Alertlog file. So I started the Replicat UODSREP and checked the Alertlog parallely but unable to find any error in the log 🙁
GGSCI (OGGSRV2) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TRAREP 00:00:00 00:00:04
REPLICAT RUNNING UODSREP 20:58:15 00:09:06
The Alertlog showed me the below.,
GoldenGate APPLY AP07 for OGG$UODSREP started with pid=120, OS id=12517398
APPLY OGG$UODSREP: Apply User: GGADMIN
APPLY OGG$UODSREP: Apply Tag: 00
APPLY OGG$UODSREP: Parameter Set by User: ALLOW_DUPLICATE_ROWS Value: Y
APPLY OGG$UODSREP: Parameter Set by User: CDGRANULARITY Value: ROW
APPLY OGG$UODSREP: Parameter Set by User: PARALLELISM Value: 6
APPLY OGG$UODSREP: Parameter Set by User: PRESERVE_ENCRYPTION Value: N
APPLY OGG$UODSREP: Parameter Set by User: SUPPRESSTRIGGERS Value: Y
Sat Jan 03 01:37:54 2015
GoldenGate Apply Reader for OGG$UODSREP started AS0I with pid=121 OS id=26804350
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS0E with pid=132 OS id=16253088
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS1P with pid=133 OS id=28836026
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS1H with pid=135 OS id=27787392
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS1G with pid=136 OS id=25624664
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS3E with pid=134 OS id=24117402
Sat Jan 03 01:37:54 2015
GoldenGate Apply Server for OGG$UODSREP started AS32 with pid=137 OS id=21495838
APPLY OGG$UODSREP: Source Database: NULL
APPLY OGG$UODSREP: Applied Message Number: 0
APPLY OGG$UODSREP: Message Create Time: 04:43, 2015/01/02
Sat Jan 03 01:37:54 2015
Propagation Receiver (CCA) for GoldenGate replicat and Apply OGG$UODSREP with pid=87, OS id=15597598, objnum=0 started.
APPLY OGG$UODSREP: Apply User: GGADMIN
APPLY OGG$UODSREP: Apply Tag: 00
APPLY OGG$UODSREP: Parameter Set by User: ALLOW_DUPLICATE_ROWS Value: Y
APPLY OGG$UODSREP: Parameter Set by User: CDGRANULARITY Value: ROW
APPLY OGG$UODSREP: Parameter Set by User: PARALLELISM Value: 6
APPLY OGG$UODSREP: Parameter Set by User: PRESERVE_ENCRYPTION Value: N
APPLY OGG$UODSREP: Parameter Set by User: SUPPRESSTRIGGERS Value: Y
APPLY OGG$UODSREP: Source Database: NULL
APPLY OGG$UODSREP: Applied Message Number: 0
APPLY OGG$UODSREP: Message Create Time: 04:43, 2015/01/02
APPLY OGG$UODSREP: Bytes Received: 0
APPLY OGG$UODSREP: SGA Used: 3862048
APPLY OGG$UODSREP: SGA Allocated: 3872144
GoldenGate Apply Server AS3E for OGG$UODSREP with pid=134 OS id=24117402 stopped
GoldenGate Apply Server AS0E for OGG$UODSREP with pid=132 OS id=16253088 stopped
GoldenGate Apply Server AS1H for OGG$UODSREP with pid=135 OS id=27787392 stopped
GoldenGate Apply Reader AS0I for OGG$UODSREP with pid=121 OS id=26804350 stopped
GoldenGate Apply Server AS32 for OGG$UODSREP with pid=137 OS id=21495838 stopped
GoldenGate Apply Server AS1P for OGG$UODSREP with pid=133 OS id=28836026 stopped
GoldenGate Apply Server AS1G for OGG$UODSREP with pid=136 OS id=25624664 stopped
GoldenGate APPLY AP07 for OGG$UODSREP with pid=120, OS id=12517398 stopped
The Replicat as usually ABENDED.,
REPLICAT RUNNING TRAREP 00:00:00 00:00:04
REPLICAT ABENDED UODSREP 20:58:15 00:46:20
So there is something unusual going on in the Target Database Replicat Process UODSREP
Since this is an Integrated Replicat, we have a query to check the error in the Apply process in the Database. I check and it showed me no rows selected. It means, there is no error.,
SQL> COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
SQL> COLUMN LOCAL_TRANSACTION_ID HEADING ‘Local|Transaction|ID’ FORMAT A11
SQL> COLUMN ERROR_NUMBER HEADING ‘Error Number’ FORMAT 99999999
SQL> COLUMN ERROR_MESSAGE HEADING ‘Error Message’ FORMAT A20
SQL> COLUMN MESSAGE_COUNT HEADING ‘Messages in|Error|Transaction’ FORMAT 99999999
SQL>
SQL> SELECT APPLY_NAME,
2 SOURCE_DATABASE,
3 LOCAL_TRANSACTION_ID,
4 ERROR_NUMBER,
5 ERROR_MESSAGE,
6 MESSAGE_COUNT
7 FROM DBA_APPLY_ERROR;no rows selected
At last I found a MOS Notes with Doc ID 1907713.1. This was a Bug in the Version OGG 12.1.2.0.0 and it got fixed in the Version OGG 12.1.2.0.1.
There were two solutions,
Remove the UPDATERECORDFORMAT COMPACT parameter from the Integrated Extract Parameter file.
The Other solution was to Downgrade the Replicat from Integrated Mode to Classic or Regular Mode.
I followed the second option and Downgraded the Replcat from Integrated Mode to Classic Mode.
Parameter of the Replicat File.,
REPLACEBADCHAR SPACE
USERID ggadmin, PASSWORD oracle
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
DISCARDFILE /ogg/dirrpt/UODSRE.DSC,Append, megabytes 2048
MAP ODSUPLOAD.*, TARGET ODSUPLOAD.*;
I Deleted the Integrated Replicat process and created it as a Classic Replicat.
Deleted REPLICAT UODSREP.
From the below output we can see UODSREP has been deleted.
REPLICAT RUNNING TRAREP 00:00:00 00:00:04
Now Edit the UODSREP Replicat Parameters and comment the line.
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
The parameter should be commented because, this parameter should be used in Integrated Replicat Feature.
REPLACEBADCHAR SPACE
USERID ggadmin, PASSWORD oracle
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
–DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
DISCARDFILE /ogg/dirrpt/UODSRE.DSC,Append, megabytes 2048
MAP ODSUPLOAD.*, TARGET ODSUPLOAD.*;
~
~
~
~
~
~
~
After commenting the parameter, save the file and check if the changes has been correctly done.,
REPLACEBADCHAR SPACE
USERID ggadmin, PASSWORD oracle
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
–DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
ASSUMETARGETDEFS
DISCARDFILE /ogg/dirrpt/UODSRE.DSC,Append, megabytes 2048
MAP ODSUPLOAD.*, TARGET ODSUPLOAD.*;
Now Add the Replicat as Classic and not as an Integrated with specifying the Checkpoint table.,
ADD REPLICAT UODSREP,EXTTRAIL /ogg/dirdat/uo checkpointtable ggadmin.uodsrep_chkpt
Since the Replicat is in NonIntegrated Mode that is the Replicat Process is in Classic Mode, a checkpoint table should be added to the Replicat.
Note: In Integrated Replicat Mode, there is no requirement of the Checkpoint table.
First Add the Checkpointtable.,
Now Add the Replicat specifying the Checkpointtable.,
REPLICAT added.
Check if the Replicat has been added to the GoldenGate.
REPLICAT RUNNING TRAREP 00:00:00 00:00:04
REPLICAT STOPPED UODSREP 00:00:00 00:00:02
Now Start the Replicat UODSREP.,
REPLICAT UODSREP starting
Check if the process is running.,
REPLICAT RUNNING TRAREP 00:00:00 00:00:01
REPLICAT RUNNING UODSREP 23:52:48 00:00:00
Checked the progress of the UODSREP Replicat Process in the GGSERR.log file.,
2015-01-03 02:53:00 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT UODSREP starting.
2015-01-03 02:53:00 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: REPLICAT UODSREP starting.
2015-01-03 02:53:00 INFO OGG-03059 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Operating system character set identified as ISO-8859-1.
2015-01-03 02:53:00 INFO OGG-02695 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: ANSI SQL parameter syntax is used for parameter parsing.
2015-01-03 02:53:01 INFO OGG-06451 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Triggers will be suppressed by default.
2015-01-03 02:53:01 INFO OGG-01815 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/ogg/dirtmp.
2015-01-03 02:53:01 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: REPLICAT UODSREP started.
2015-01-03 02:53:01 INFO OGG-03522 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Setting session time zone to source database time zone ‘-04:00’.
2015-01-03 02:53:01 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: The source database character set, as determined from the trail file, is CESU-8.
2015-01-03 02:53:01 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: DDL found, operation [TRUNCATE TABLE ODSUPLOAD.SIS_COMMITTED_PROJECT_SPONSOR (size 55)].
2015-01-03 02:53:01 INFO OGG-00540 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Metadata not cleared for [ODSUPLOAD.SIS_COMMITTED_PROJECT_SPONSOR] because of TRUNCATE.
2015-01-03 02:53:01 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: DDL is of mapped scope, after mapping new operation [TRUNCATE TABLE “ODSUPLOAD”.”SIS_COMMITTED_PROJECT_SPONSOR” (size 59)].
2015-01-03 02:53:01 INFO OGG-00487 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: DDL operation included [INCLUDE MAPPED], optype [TRUNCATE], objtype [TABLE], objowner [ODSUPLOAD], objname [SIS_COMMITTED_PROJECT_SPONSOR].
2015-01-03 02:53:01 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Setting current schema for DDL operation to [ODSUPLOAD].
2015-01-03 02:53:01 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Executing DDL operation.
2015-01-03 02:53:01 INFO OGG-00483 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: DDL operation successful.
2015-01-03 02:53:01 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: Restoring current schema for DDL operation to [ggadmin].
2015-01-03 02:53:01 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, uodsrep.prm: DDL found, operation [truncate table odsupload.SIS_COMMITTED_PROJECT_SPONSOR reuse storage (size 69)].
From the above logs we can clearly see that the Replicat Process started running. The Lag time is more because, the process was not running for a long time because of this error. Now the process is started and the changes are getting applied slowly. Once the Target is in sync, the Lag time will be reduced automatically.
In this post I did not
Reference Oracle Notes:
Doc ID 1907713.1
Cause:
If parameter ‘UPDATERECORDFORMAT COMPACT’ is used in source extract, this is bug 18406246. There was an issue while skipping LOB mapping for Unified trail.
Solution:
The bug is fixed in 12.1.2.0.1.
The workarounds are:
- Removed ‘UPDATERECORDFORMAT COMPACT’ from extract and restarted.
- Downgraded REPLICAT to Classic.
Loading ...
Total Users : 1822672