we are replicating DDL from one database to another. We have a lot of PL SQL code in source DB.
For simple DDL operations works fine, but today we faced following error while changing one procedure:
OGG-00470 Oracle GoldenGate Capture for Oracle, src_ddl1.prm: Wrong format of ddl property string (missing end delimiter).
Error logs says:
-- call \'vallidate_voucher\' via SOAP
validate_voucher_soap\(i_vouchercode \=> i_voucherid\,
I checked the source code and this is the corresponding point:
-- call 'vallidate_voucher' via SOAP
validate_voucher_soap (i_vouchercode => i_voucherid,
i_saleschannel => 'SOMETEXT',
i_vonumber => i_vonumber,
vreturnarray => aryerror,
benefitsarray => aryout);
So it seems the problem is with multiline command?
Any suggestion how to solve this?
I am not 100% sure that that is the line causing the error. For sure that is the package, but it seems that the error log is someway truncated.
is there a way to check which is the line causing the problem?
I checked the report file and found something strage:
ERROR: DDL sequence , marker sequence , DDL trace log file [somepathhere], error code -1653 error message ORA-01653: unable to extend table GGS_ADMIN
.GGS_MARKER by 1024 in tablespace GGS_DATA, error stack: ORA-06512: at "GGS_ADMIN.DDLREPLICATION", line 1236
ORA-06512: at line 694
The tablespace has plenty of free space, just added one 4GB datafile.
Table has abot 31K records.
Any solution/suggestion for this?
We are doing a POC on Golden gate but if it is not able to replicate our DDL that is pretty much of a KO criterium for us, as the tool becomes more or less useless for our use case.
so it seem we have someway solved this issue.
First we have seen that in the extract report there were some errors like this:
ERROR: DDL sequence , marker sequence , DDL trace log file [pkg/vddmb/logs/oradmb/TSTSRCCT/udump/ggs_ddl_trace.log], error code -1653 error message ORA-01653: unable to extend table GGS_ADMIN.GGS_MARKER by 1024 in tablespace GGS_DATA, error stack: ORA-06512: at "GGS_ADMIN.DDLREPLICATION", line 1236
ORA-06512: at line 694
At the first we checked the tablespaces but all seemed fine. We increased them and still seing a plenty of free space. Error was still there.
The we thought maybe there are some issues with that tablespace. So we just created a band new tablespace and moved the GGS_MARKER to the new tablespace (tablespace name GG_MARK).
Now what happened at this point was quite strange, the error was stil the same, i.e. it was still complaining about tablespace GGS_DATA. But we had just moved GGS_MARKER to a new tablespace.
Next we tryed the approach in Metalink ID 1379867.1. basically purged the recycle bin and disabled them in the complete DB. Still the same error with GGS_DATA.
This made us think that naybe GGS_DATA was hardcoded somewhere. That is where we looked at the GGS_MARKER table and found out that in that table (which apparently stored all the DDL) there were some records which contained as MARKER_TEXT not some valid SQL, but exaclty the above message.
So GG was basically trying to interpret the text in there as some SQL commands, but indeed the text is NO Sql, it is an error message.
so what we did is we found out about 10 rows with the same error text and added for each of them one skip row in the extract params file:
DDLERROR SKIPDDL 13887 (get the number from GGSMARKER)
(there is a blank before _)
With this we basically skipped those records (there were anyway not much to be replicated there) but replication started working again.
After that we had to recompile DDLREPLICATION package, and two indexes on GGS_MARKER but now seems to work fine.
The Question remains anyway why that Error message was written to the ggs_marker table?? In our opinion that should not be the case.