ODS data failed to be transferred to datamart

[mrpeez]

Hi all,

Please help me out guys.

My past ODS data failed to be transferred to datamart.
The data is available but the transfer time is null.

The excerpt of the log as below :

11/02/17 16:52:41.647 MYT 87777280      ChunkWriter#01_for#289@24054760 WARNING Report View Access Object Pool: invalid : ReportViewAccessObject[connection: oracle.jdbc.driver.OracleConnection@11ff436, numberOfViewAccessed: 1, listOfViewAccessed: {289, }] was returned. Removing it
11/02/17 16:52:41.878 MYT 87777280      ChunkWriter#01_for#268@28336193 EXCEPT  ChunkWriter#01_for#268: unable to insert time dim row: TableRowObject[id: UKey[name: UKey#-1321214918'TIME_KEY':201101280900MYT], values: {'TIME_KEY':201101280900MYT, 'CHUNK_ID':1_1903011, 'BEGIN_TIME':2011-01-28 09:00:00.0, 'QUERY_DATE':20110128/0900, 'DATE_YYYYMMDD':20110128, 'DATE_YYMMDD':110128, 'DATE_MMDD':0128, 'TIME_HH24MI':0900, 'TIME_HHMM_AM_PM':0900, 'HOUR_HH24':09, 'END_TIME':2011-01-28 09:30:00.0, 'DAY_OF_WEEK_SHORT':Fri, 'DAY_OF_WEEK':Friday, 'DAY_N_IN_WEEK':6, 'DAY_N_IN_MONTH':28, 'DAY_N_IN_YEAR':28, 'WEEKEND':N, 'WEEK_N_IN_YEAR':05, 'MONTH_NAME_SHORT':Jan, 'MONTH_NAME':January, 'MONTH_N_IN_YEAR':01, 'QUARTER':Q1, 'CALENDAR_QUARTER':2011Q1, 'YEAR':2011, 'DISPLAY_DATE':28-Jan-2011 09:00 MYT, 'BEGIN_TIME_GMT':2011-01-28 01:00:00.0, 'END_TIME_GMT':2011-01-28 01:30:00.0, 'TZ':MYT, 'AGG_BY_HOUR':2011012809MYT, 'AGG_BY_DAY':20110128, 'AGG_BY_WEEK':201105, 'AGG_BY_MONTH':201101, 'AGG_BY_QUARTER':2011Q1, 'AGG_BY_YEAR':2011, 'CUSTOM_AGG_LEVEL':null, 'AGG_INTER_COUNT_':0, 'INTERVAL_LEN_SEC_':0, 'TIME_ZONE_ID':65}] into: Dist Time Dim Table for view#268[ExcptMsg: ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated ]
Add info:
        SQLState: 23000
        Message:  ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated

        Vendor:  1
        Stack Trace:
java.sql.SQLException: ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1839)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1764)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2354)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421)
        at com.genesyslab.dbaccess.TableAccessObject.executeUpdateStmt(TableAccessObject.java:881)
        at com.genesyslab.dbaccess.TableAccessObject.insert(TableAccessObject.java:758)
        at DistChunkWriterMonitor.insertTimeRow(DistChunkWriterMonitor.java:732)
        at DistChunkWriterMonitor.proceedChunk(DistChunkWriterMonitor.java:324)
        at DistChunkWriterMonitor.createNew(DistChunkWriterMonitor.java:242)
        at DistChunkWriterMonitor.executeCommand(DistChunkWriterMonitor.java:153)
        at Monitor.run(Monitor.java:287)
        at java.lang.Thread.run(Thread.java:534)
11/02/17 16:52:41.878 MYT 87777280      ChunkWriter#01_for#268@28336193 EXCEPT  ChunkWriter#01_for#268: exception happend when trying to WRITE chunk#1_1903011 skipping it ...[ExcptMsg: ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated ]
Add info:
        SQLState: 23000
        Message:  ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated

        Vendor:  1
        Stack Trace:
java.sql.SQLException: ORA-00001: unique constraint (IPCCREP.PK_T_268_TIME_DIM) violated

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1839)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1764)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2354)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421)
        at com.genesyslab.dbaccess.TableAccessObject.executeUpdateStmt(TableAccessObject.java:881)
        at com.genesyslab.dbaccess.TableAccessObject.insert(TableAccessObject.java:758)
        at DistChunkWriterMonitor.insertTimeRow(DistChunkWriterMonitor.java:732)
        at DistChunkWriterMonitor.proceedChunk(DistChunkWriterMonitor.java:324)
        at DistChunkWriterMonitor.createNew(DistChunkWriterMonitor.java:242)
        at DistChunkWriterMonitor.executeCommand(DistChunkWriterMonitor.java:153)
        at Monitor.run(Monitor.java:287)
        at java.lang.Thread.run(Thread.java:534)
11/02/17 16:52:41.895 MYT 87777280      ChunkWriter#01_for#268@28336193 WARNING Report View Access Object Pool: invalid : ReportViewAccessObject[connection: oracle.jdbc.driver.OracleConnection@16921fd, numberOfViewAccessed: 1, listOfViewAccessed: {268, }] was returned. Removing it




TransformException: Found statistic for an object in ol_data without corresponding record in ol_object. The likely cause for this is corruption of ODS. You may need to restore ODS integrity manually.
        at DistChunkWriterMonitor.updateNewObjectUsingGlobalCatalog(DistChunkWriterMonitor.java:552)
        at DistChunkWriterMonitor.checkObjectsInChunk(DistChunkWriterMonitor.java:477)
        at DistChunkWriterMonitor.checkObjectsAndStats(DistChunkWriterMonitor.java:439)
        at DistChunkWriterMonitor.proceedChunk(DistChunkWriterMonitor.java:314)
        at DistChunkWriterMonitor.createNew(DistChunkWriterMonitor.java:242)
        at DistChunkWriterMonitor.executeCommand(DistChunkWriterMonitor.java:153)
        at Monitor.run(Monitor.java:287)
        at java.lang.Thread.run(Thread.java:534)
11/02/17 16:56:52.773 MYT 98992128      ChunkWriter#01_for#289@24054760 WARNING Report View Access Object Pool: invalid : ReportViewAccessObject[connection: oracle.jdbc.driver.OracleConnection@11ff436, numberOfViewAccessed: 1, listOfViewAccessed: {289, }] was returned. Removing it

Thanks in advance!

[Timur Karimov]

To eliminate this errors try this , logged on oracle as the owner of ol_chunk_log table. and do next things
"
ORACLE:
-------------------------------------------------------------------
SQL> update ol_chunk_log set transferred_time=begin_time where
transferred_time is null and receive_time < sysdate - 0.5;
"

hope is helpfull, thaler

[mrpeez]

thanks for the help, but it didn't resolved the problem...
The transfer time is restored, but the data from ODS still didn't transferred...
Any other way?

[mrpeez]

now i'm focusing on this error :

11/02/23 11:22:12.451 MYT 99188736      ChunkWriter#01_for#289@24061351 EXCEPT  ChunkWriter#01_for#289: exception happend when trying to WRITE chunk#1_1903663 skipping it
...[ExcptMsg: Found statistic for an object in ol_data without corresponding record in ol_object. The likely cause for this is corruption of ODS. You may need to restore ODS integrity manually.]
        Stack Trace:
TransformException: Found statistic for an object in ol_data without corresponding record in ol_object. The likely cause for this is corruption of ODS. You may need to restore ODS integrity manually.
        at DistChunkWriterMonitor.updateNewObjectUsingGlobalCatalog(DistChunkWriterMonitor.java:552)
        at DistChunkWriterMonitor.checkObjectsInChunk(DistChunkWriterMonitor.java:477)
        at DistChunkWriterMonitor.checkObjectsAndStats(DistChunkWriterMonitor.java:439)
        at DistChunkWriterMonitor.proceedChunk(DistChunkWriterMonitor.java:314)
        at DistChunkWriterMonitor.createNew(DistChunkWriterMonitor.java:242)
        at DistChunkWriterMonitor.executeCommand(DistChunkWriterMonitor.java:153)
        at Monitor.run(Monitor.java:287)
        at java.lang.Thread.run(Thread.java:534)


Please guys... emergency here..
Thanks!

[cavagnaro]

We are a users forum, we have nothing to do with Genesys itself. If your problem is that big and urgent then why don't open a ticket with Genesys...

[borkokrz]

What version of DataSourcer you are using ? I've seen similar problem with some early 7.2.x, when it produced chunks for "ghost" objects, after they were deleted from CME.  In ol_dataXXX appeared statistics for objects with OBJECT_ID = 0 or a very high number like 1264556745. So to be sure check all distinct object_id's from single problematic chunk from ol_dataXXX table and compare them with distinct object_id's from ol_object table. If there will be a difference, you have to alter table ol_dataXXX and delete all rows for this "ghosts". And after successful repair - update DS to newest 7.2 version.

[Timur Karimov]

[quote author=borkokrz link=topic=6189.msg26993#msg26993 date=1298492860]
And after successful repair - update DS to newest 7.2 version.
[/quote]
np - but i recomended newest 7.6 version =)

WBR