[Solved] Issue: Bug no ODI 12.2.1 ao tentar excluir cenário

Administration / 15/07/2022

O Oracle Data Integrator, conhecido pelo acrônimo ODI, é uma poderosa ferramenta de ETL da Oracle, muito utilizada em ambientes de BI e também Analytics. Ela tem capacidade de se conectar em múltiplas origens (seja banco de dados, planilhas excel, arquivos, e outras fontes de dados) e realizar sua devida transformação e carga de acordo com as necessidades do negócio. Tudo isso com uma ferramenta gráfica que auxilia muito bem o profissional a desenvolver desde processos simples aos mais complexos.

Neste artigo, vou explorar um comportamento muito pontual (caracterizado por um bug) do ODI, que eu nunca identifiquei nas versões 11, apenas na 12.

Versão 12.2.1.0:

Característica do problema: dentro do ODI, temos um objeto chamado cenário, que encapsula as regras de ETL e possibilita que as cargas sejam feitas. Toda vez que um cenário é executado, ele salva várias informações no seu respectivo banco de metadados (onde o ODI foi instalado). Quando um cenário é executado com uma periodicidade constante (por exemplo, a cada 10 minutos de forma cíclica), obviamente muitos logs serão gerados.

Nesta versão que usamos como exemplo, o problema ocorre quando, no momento de Deploy, você tenta excluir o cenário atual (com a intenção de posteriormente importar o novo). Nesta etapa, o ODI realiza um purge interno nos logs associados com o cenário em questão, e acaba explodindo um erro. Vamos simular este caso:

Como este artigo foi originado de uma situação real, apagarei quaisquer informações de nome de objetos e detalhes, para manter o sigilo do ambiente.

Erro em modo texto:

java.lang.RuntimeException: com.sunopsis.dwg.SQLWorkReposException: ORA-02292: restrição de integridade (XXXXXX.FK_SCEN_REPORT) violada - registro filho localizado
 
    at com.sunopsis.graphical.core.DwgObjectDeleterService$2.doInTransaction(DwgObjectDeleterService.java:649)
    at oracle.odi.core.persistence.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:100)
    at com.sunopsis.graphical.core.DwgObjectDeleterService.deleteObject(DwgObjectDeleterService.java:632)
    at com.sunopsis.graphical.core.DwgObjectDeleterService.access$200(DwgObjectDeleterService.java:120)
    at com.sunopsis.graphical.core.DwgObjectDeleterService$1.doInBackground(DwgObjectDeleterService.java:329)
    at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
    at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:961)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.sunopsis.dwg.SQLWorkReposException: ORA-02292: restrição de integridade (XXXXXXXXX.FK_SCEN_REPORT) violada - registro filho localizado
 
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:407)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1113)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:546)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:603)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:234)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:55)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1006)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1316)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:5010)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:5136)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1519)
    at sun.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:353)
    at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:178)
    at com.sun.proxy.$Proxy32.executeUpdate(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at oracle.odi.core.datasource.support.RuntimeClassLoaderDataSourceCreator$StatementInvocationHandler.invoke(RuntimeClassLoaderDataSourceCreator.java:173)
    at com.sun.proxy.$Proxy33.executeUpdate(Unknown Source)
    at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:689)
    at com.sunopsis.dwg.DwgObject.deleteAction(DwgObject.java:1387)
    at com.sunopsis.dwg.DwgObject.deleteGlobal(DwgObject.java:1548)
    at com.sunopsis.dwg.DwgObject.deleteGlobal(DwgObject.java:1555)
    at com.sunopsis.dwg.DwgObject.delete(DwgObject.java:1102)
    at com.sunopsis.dwg.DwgObject.delete(DwgObject.java:1081)
    at com.sunopsis.graphical.core.DwgObjectDeleterService$2.doInTransaction(DwgObjectDeleterService.java:643)
    ... 7 more

Em contato com o fornecedor, nos foi recomendado, devido as circunstâncias, aplicar o procedimento seguinte:

1-Executar um backup do owner XXXX (owner de metadados que abriga as informações internas do ODI);
2-Parar todos os processos de carga do ODI caso estejam em execução;
3-Executar o comando abaixo no banco de metadados em questão:
ALTER TABLE XXXXXX.SNP_SCEN_REPORT DISABLE CONSTRAINT FK_SCEN_REPORT;
4-Tentar excluir novamente o cenário;
5-Executar o comando abaixo no banco de metadados em questão:
ALTER TABLE XXXXXXX.SNP_SCEN_REPORT ENABLE CONSTRAINT FK_SCEN_REPORT;

Até para fundamentar a decisão de executar esse Workaround, uma situação similar já está documentada no Oracle Note ““ORA-02292: integrity constraint (xxxx.FK_SNP_SESSION_SB) violated – child record found” when Running ODIPurgeLog in ODI 12c (Doc ID 2049524.1)“.

Realizando o export do owner de repositório:

[user@server]$ cat expdp.log
;;;
Export: Release 12.1.0.2.0 - Production on Fri Feb 14 15:01:52 2020
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 21.27 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "XXXXXXX"."SNP_STEP_LOG"           488.1 MB 2048203 rows
. . exported "XXXXXXX"."SNP_STEP_REPORT"        485.0 MB 1777493 rows
. . exported "XXXXXXX"."SNP_SESS_TASK_LOG"      80.94 MB  527825 rows
. . exported "XXXXXXX"."SNP_PARAM_SESS"         74.67 MB 2694134 rows
. . exported "XXXXXXX"."SNP_SESSION"            38.34 MB  717394 rows
. . exported "XXXXXXX"."SNP_SCEN_REPORT"        19.58 MB  692287 rows
. . exported "XXXXXXX"."SNP_SESS_TASK_LS"       1.557 MB   69315 rows
. . exported "XXXXXXX"."SNP_SESS_STEP_LV"       882.2 KB   39242 rows
. . exported "XXXXXXX"."SNP_SB_TASK"            280.0 KB    2286 rows
. . exported "XXXXXXX"."SNP_SCEN_TASK"          227.7 KB    1844 rows
. . exported "XXXXXXX"."SNP_CONV_DT"            855.1 KB   38206 rows
. . exported "XXXXXXX"."SNP_SB_STEP"            52.44 KB     601 rows
. . exported "XXXXXXX"."SNP_MTXT"               153.9 KB    3949 rows
. . exported "XXXXXXX"."SNP_CONNECT"            8.976 KB      11 rows
. . exported "XXXXXXX"."SNP_LPI_EC_VAR_LOG"         0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_VAR_LOG"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_SCEN_STEP"          42.40 KB     406 rows
. . exported "XXXXXXX"."SNP_SESS_TASK"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_VAR"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_AGENT"                  0 KB       0 rows
. . exported "XXXXXXX"."SNP_DEPLOYMENT_LOG"         0 KB       0 rows
. . exported "XXXXXXX"."SNP_EXP_TXT_HEADER"     8.687 KB      86 rows
. . exported "XXXXXXX"."SNP_FF_VALUEE"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_FF_VALUEM"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_FLEX_FIELD"         8.429 KB      40 rows
. . exported "XXXXXXX"."SNP_LPI_EXC_LOG"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_RUN"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_STEP"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_STEP_LOG"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_LPI_STEP_VAR"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_LP_STEP"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_LP_STEP_VAR"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_LP_VAR"                 0 KB       0 rows
. . exported "XXXXXXX"."SNP_PARAM_LPI_RUN"          0 KB       0 rows
. . exported "XXXXXXX"."SNP_PROF_METH"          57.82 KB    2494 rows
. . exported "XXXXXXX"."SNP_PSCHEMA"            9.203 KB      11 rows
. . exported "XXXXXXX"."SNP_SB"                 9.421 KB      73 rows
. . exported "XXXXXXX"."SNP_SEQ_SB"             6.773 KB      24 rows
. . exported "XXXXXXX"."SNP_SEQ_SCEN"           6.601 KB      19 rows
. . exported "XXXXXXX"."SNP_SESS_STEP"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_VAR_DATA"           6.070 KB       5 rows
. . exported "XXXXXXX"."SNP_VAR_PLAN_AGENT"         0 KB       0 rows
. . exported "XXXXXXX"."SNP_VAR_SB"             7.984 KB      75 rows
. . exported "XXXXXXX"."SNP_VAR_SCEN"           7.523 KB      49 rows
. . exported "XXXXXXX"."SNP_VAR_SESS"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_LE_TECHNO"          71.10 KB    2341 rows
. . exported "XXXXXXX"."SNP_DT"                 40.00 KB     896 rows
. . exported "XXXXXXX"."SNP_FIELD"              59.42 KB    1290 rows
. . exported "XXXXXXX"."SNP_LANG_ELT"           53.18 KB    1308 rows
. . exported "XXXXXXX"."SNP_METHOD"             36.07 KB     863 rows
. . exported "XXXXXXX"."OGG_METADATA"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_ACTION"             7.851 KB      58 rows
. . exported "XXXXXXX"."SNP_AGENT_PROP"             0 KB       0 rows
. . exported "XXXXXXX"."SNP_AGENT_PROP_LST"     5.484 KB       6 rows
. . exported "XXXXXXX"."SNP_ALLOC_AGENT"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_CEC_LP"                 0 KB       0 rows
. . exported "XXXXXXX"."SNP_CEC_SCEN"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_CONNECT_PROP"       5.882 KB      20 rows
. . exported "XXXXXXX"."SNP_CONTEXT"            5.781 KB       1 rows
. . exported "XXXXXXX"."SNP_DATA"                   0 KB       0 rows
. . exported "XXXXXXX"."SNP_DATASOURCE"             0 KB       0 rows
. . exported "XXXXXXX"."SNP_DIM_PATTERN"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_ENTITY"             11.91 KB     103 rows
. . exported "XXXXXXX"."SNP_ENT_ID"             5.484 KB      35 rows
. . exported "XXXXXXX"."SNP_ENT_PRINCIPAL"          0 KB       0 rows
. . exported "XXXXXXX"."SNP_ESS"                    0 KB       0 rows
. . exported "XXXXXXX"."SNP_FIELD_LOOKUP"       21.89 KB     568 rows
. . exported "XXXXXXX"."SNP_GRP_ACTION"          6.25 KB      10 rows
. . exported "XXXXXXX"."SNP_HIST_VERSION"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_HOST"               5.625 KB       8 rows
. . exported "XXXXXXX"."SNP_HOST_MOD"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_ID"                 5.148 KB       5 rows
. . exported "XXXXXXX"."SNP_IMPORT_REP"             0 KB       0 rows
. . exported "XXXXXXX"."SNP_INDEX_TYPE"          8.25 KB      66 rows
. . exported "XXXXXXX"."SNP_INST_OBJ"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_LAGENT"                 0 KB       0 rows
. . exported "XXXXXXX"."SNP_LANG"               6.296 KB       8 rows
. . exported "XXXXXXX"."SNP_LANG_TECHNO"        7.195 KB      54 rows
. . exported "XXXXXXX"."SNP_LB_AGENT"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_LINE_ACTION"        8.679 KB      78 rows
. . exported "XXXXXXX"."SNP_LINK"               12.53 KB     220 rows
. . exported "XXXXXXX"."SNP_LOAD_PLAN"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_LOCK"                   0 KB       0 rows
. . exported "XXXXXXX"."SNP_LOCKED_OBJECT"          0 KB       0 rows
. . exported "XXXXXXX"."SNP_LOC_REP"            7.046 KB       1 rows
. . exported "XXXXXXX"."SNP_LOC_REPW"           6.429 KB       1 rows
. . exported "XXXXXXX"."SNP_LOOKUP"                 0 KB       0 rows
. . exported "XXXXXXX"."SNP_LOOKUP_VALUE"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_LP_INST"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_LSCHEMA"            6.445 KB      12 rows
. . exported "XXXXXXX"."SNP_MERGE"                  0 KB       0 rows
. . exported "XXXXXXX"."SNP_MERGE_OBJECT"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_MODULE"             5.531 KB       5 rows
. . exported "XXXXXXX"."SNP_M_IMPORT_REP"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_OBJECT"             10.64 KB     115 rows
. . exported "XXXXXXX"."SNP_OPEN_TOOL"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_PLAN_AGENT"             0 KB       0 rows
. . exported "XXXXXXX"."SNP_PROFILE"            6.195 KB      15 rows
. . exported "XXXXXXX"."SNP_PSCHEMA_CONT"       5.992 KB      10 rows
. . exported "XXXXXXX"."SNP_PWD_POLICY"          5.25 KB       1 rows
. . exported "XXXXXXX"."SNP_PWD_RULE"           5.476 KB       4 rows
. . exported "XXXXXXX"."SNP_REM_REP"            5.687 KB       1 rows
. . exported "XXXXXXX"."SNP_ROLE_ENT_PRP"           0 KB       0 rows
. . exported "XXXXXXX"."SNP_RT_BRP"                 0 KB       0 rows
. . exported "XXXXXXX"."SNP_SCEN"               9.796 KB      60 rows
. . exported "XXXXXXX"."SNP_SCEN_BRP"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_SCEN_FOLDER"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_SCHEDULE_EXEC"          0 KB       0 rows
. . exported "XXXXXXX"."SNP_SEQ_DATA"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_SESSION_DBG"        5.789 KB      10 rows
. . exported "XXXXXXX"."SNP_SESS_FOLDER"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_SOLUTION"               0 KB       0 rows
. . exported "XXXXXXX"."SNP_SOL_ELT"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_SUB_LANG"           7.539 KB      51 rows
. . exported "XXXXXXX"."SNP_TECHNO"             17.35 KB      62 rows
. . exported "XXXXXXX"."SNP_USER"               6.492 KB       4 rows
. . exported "XXXXXXX"."SNP_USER_CONNECTED"     17.36 KB     373 rows
. . exported "XXXXXXX"."SNP_USER_METH"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_USER_OBJ_METH"          0 KB       0 rows
. . exported "XXXXXXX"."SNP_USER_PREF"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_USER_PROF"          5.531 KB       3 rows
. . exported "XXXXXXX"."SNP_USR_PREFERENCE"     5.164 KB       2 rows
. . exported "XXXXXXX"."SNP_U_O_M_REP"              0 KB       0 rows
. . exported "XXXXXXX"."SNP_VCS_VERSION"            0 KB       0 rows
. . exported "XXXXXXX"."SNP_VERSION"                0 KB       0 rows
. . exported "XXXXXXX"."SNP_WS_POLICY"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /xxxx/BKP.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Feb 14 16:12:41 2020 elapsed 0 00:30:44

Rodar comando abaixo no banco de Metadados:

ALTER TABLE XXXXXXX.SNP_SCEN_REPORT DISABLE CONSTRAINT FK_SCEN_REPORT;
 
Table XXXXXXX.SNP_SCEN_REPORT altered.

Tentando deletar o cenário novamente:

Cenário excluído com sucesso.

Depois disso, e necessário rodar o comando abaixo no banco de metadados:

ALTER TABLE XXXXXXX.SNP_SCEN_REPORT ENABLE CONSTRAINT FK_SCEN_REPORT;
 
Table XXXXXXX.SNP_SCEN_REPORT altered.

Obviamente, o procedimento acima é uma solução paliativa do bug documentado, de modo que para saná-lo de forma definitiva, é recomendado aplicar o Patch abaixo, ou até subir o ambiente para a versão mais recente, que contempla o ajuste: (ODI 12.2.1.x: Delete Mapping Error “ORA-02292: integrity constraint (WORKREPO.FK_MAP_CONN_MAP) violated – child record found” (Doc ID 2329587.1)).

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish