ODI 10gR1: Connection to Repository Failed after Database Descriptor Change

After migrating database to another host, port or SID, the error below started to happen when running a scenario.
“Of course” all mapped connection was right setted on Topology… But environment is complex, it’s possible something is missing…

java.sql.SQLException: Io Exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:361)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:151)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:595)
at com.sunopsis.sql.SnpsConnection.u(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.h.run(h.java)

Ok, so something really is missing… But where?
Try to check this:

select * from ODIM_PROD.SNP_MTXT_PART where txt like '%MYOLDDB%';
TXT                                      TXT_ORD       I_TXT
---------------------------------------  ------------  ---------
jdbc:oracle:thin:@dbsrvr:1521:myoldb_1   0             2913000
jdbc:oracle:thin:@dbsrvr:1521:myoldb_1   0             2000
jdbc:oracle:thin:@dbsrvr:1521:myoldb_1   0             1000
jdbc:oracle:thin:@dbsrvr:1521:myoldb_1   0             92999
jdbc:oracle:thin:@dbsrvr:1521:myoldb_1   0             2923000

To fix:

update snp_mtxt_part set TXT='jdbc:oracle:thin:@dbsrvrnew:1521:mynewdb_1' where i_txt in (...);
commit;

So, to check detailed Topology Connection information, as posted here, you chan check this:

SELECT DISTINCT
SNP_TECHNO.TECHNO_NAME    AS TECHNOLOGY,
SNP_CONNECT.CON_NAME      AS DATA_SERVER,
SNP_PSCHEMA.EXT_NAME      AS PHYSICAL_SCHEMA,
SNP_PSCHEMA.SCHEMA_NAME   AS SCHEMA_NAME,
SNP_PSCHEMA.WSCHEMA_NAME  AS WORK_SCHEMA,
SNP_CONTEXT.CONTEXT_NAME  AS CONTEXT_NAME,
SNP_LSCHEMA.LSCHEMA_NAME  AS LOGICAL_SCHEMA,
SNP_CONNECT.JAVA_DRIVER   AS DRIVER_INFO,
SNP_MTXT_PART.TXT         AS URL
FROM SNP_TECHNO
LEFT OUTER JOIN SNP_CONNECT ON SNP_CONNECT.I_TECHNO=SNP_TECHNO.I_TECHNO
LEFT OUTER JOIN SNP_PSCHEMA ON SNP_PSCHEMA.I_CONNECT=SNP_CONNECT.I_CONNECT
LEFT OUTER JOIN SNP_PSCHEMA_CONT ON SNP_PSCHEMA_CONT.I_PSCHEMA=SNP_PSCHEMA.I_PSCHEMA
LEFT OUTER JOIN SNP_LSCHEMA ON SNP_LSCHEMA.I_LSCHEMA=SNP_PSCHEMA_CONT.I_LSCHEMA
LEFT OUTER JOIN SNP_CONTEXT ON SNP_CONTEXT.I_CONTEXT=SNP_PSCHEMA_CONT.I_CONTEXT
LEFT OUTER JOIN SNP_MTXT_PART ON SNP_MTXT_PART.I_TXT=SNP_CONNECT.I_TXT_JAVA_URL
WHERE SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY SNP_TECHNO.TECHNO_NAME;

Cool, isn’t it?

Have a nice day!
Matheus.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s