SnapSync Fails With Fault exception.host.host.connection.reset (KBA1801)

Issue

After an upgrade of Delphix Engine to 5.2 or later, or upgrade of Oracle for a source Environment, a SnapSync fails with errors indicating host connection reset. 

The fault logged may present as:

event_params         | ["Connection to the remote host \"<HOSTNAME>\" is lost.","Check network connectivity.","DB_SYNC","pidb_group/pidb","null"]
event_title          | Error during job execution
event_description    | DB_SYNC job for "<DATASET GROUP>/<DSOURCE NAME>" failed due to an error during execution: Connection to the remote host "example.host.com" is lost.

Applicable Delphix Versions

This article applies to the following versions of the Delphix Engine:

Resolution

The missing Delphix DB user grants can be updated as needed manually, updating the necessary view and grants for x$kccfe:

create or replace view v_x$kccfe as select * from x$kccfe;
grant select on v_x$kccfe to $USERNAME;
create synonym $USERNAME.x$kccfe for v_x$kccfe;

Alternatively, the Delphix DB user can be deleted and recreated using the createDelphixDBUser.sh script, provided on our documentation site.

Troubleshooting

In this instance, all host operations completed as expected; the Environment can be refreshed without issue, and all network connectivity tests are successful.

The fault logged can be a bit misleading, as this condition can be caused by missing grants or views for the configured Delphix DB user. The SQL exception indicating this can be found in the SnapSync debug logs (debug.log), which are contained in the log/connector subdirectory.  Example below:

[2018-11-01 12:00:06,827][DEBUG][org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator][pool-7-thread-1][SQLErrorCodeSQLExceptionTranslator.logTranslation] Translating SQLException with SQL state '42000', error code '904', message [ORA-00904: "FE"."CON_ID": invalid identifier^M
]; SQL was [SELECT /*+ RULE */ nvl(dh.name, 'NONAME') AS file_name, dh.file# AS afn, dh.rfile# AS rfn, ts.name AS tablespace_name, dh.bytes, fe.febsz AS blocksize, dh.status, decode(fe.con_id, 2, 'READ ONLY', decode(bitand(fe.feflg, 4), 4, 'READ WRITE', decode(bitand(fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12,'READ WRITE', 'UNKNOWN'))) as read_mode, dh.creation_change#, dh.con_id as con_id, dh.error, feplus as plugin_change# FROM v$datafile_header dh, x$kccfe fe, v$tablespace ts WHERE dh.file# = fe.fenum AND dh.con_id = fe.con_id AND ts.ts# = dh.ts# AND ts.con_id = dh.con_id AND dh.con_id IN (?)] for task [PreparedStatementCallback]

[2018-11-01 12:00:06,828][DEBUG][com.delphix.ojdbc.OJDBCAccessorImpl][pool-7-thread-1][OJDBCAccessorImpl$DelphixOJDBC.retryOrFail] Retrying query (jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=tcp)(HOST=example.host.com)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=dsource))))), after PreparedStatementCallback; bad SQL grammar [SELECT /*+ RULE */ nvl(dh.name, 'NONAME') AS file_name, dh.file# AS afn, dh.rfile# AS rfn, ts.name AS tablespace_name, dh.bytes, fe.febsz AS blocksize, dh.status, decode(fe.con_id, 2, 'READ ONLY', decode(bitand(fe.feflg, 4), 4, 'READ WRITE', decode(bitand(fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12,'READ WRITE', 'UNKNOWN'))) as read_mode, dh.creation_change#, dh.con_id as con_id, dh.error, feplus as plugin_change# FROM v$datafile_header dh, x$kccfe fe, v$tablespace ts WHERE dh.file# = fe.fenum AND dh.con_id = fe.con_id AND ts.ts# = dh.ts# AND ts.con_id = dh.con_id AND dh.con_id IN (?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "FE"."CON_ID": invalid identifier
:
SELECT /*+ RULE */ nvl(dh.name, 'NONAME') AS file_name, dh.file# AS afn, dh.rfile# AS rfn, ts.name AS tablespace_name, dh.bytes, fe.febsz AS blocksize, dh.status, decode(fe.con_id, 2, 'READ ONLY', decode(bitand(fe.feflg, 4), 4, 'READ WRITE', decode(bitand(fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12,'READ WRITE', 'UNKNOWN'))) as read_mode, dh.creation_change#, dh.con_id as con_id, dh.error, feplus as plugin_change# FROM v$datafile_header dh, x$kccfe fe, v$tablespace ts WHERE dh.file# = fe.fenum AND dh.con_id = fe.con_id AND ts.ts# = dh.ts# AND ts.con_id = dh.con_id AND dh.con_id IN (:con_ids)

[2018-11-01 12:00:06,828][DEBUG][com.delphix.ojdbc.OJDBCAccessorImpl][pool-7-thread-1][OJDBCAccessorImpl$DelphixOJDBC.retryOrFail] Failed to perform query "SELECT /*+ RULE */ nvl(dh.name, 'NONAME') AS file_name, dh.file# AS afn, dh.rfile# AS rfn, ts.name AS tablespace_name, dh.bytes, fe.febsz AS blocksize, dh.status, decode(fe.con_id, 2, 'READ ONLY', decode(bitand(fe.feflg, 4), 4, 'READ WRITE', decode(bitand(fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12,'READ WRITE', 'UNKNOWN'))) as read_mode, dh.creation_change#, dh.con_id as con_id, dh.error, feplus as plugin_change# FROM v$datafile_header dh, x$kccfe fe, v$tablespace ts WHERE dh.file# = fe.fenum AND dh.con_id = fe.con_id AND ts.ts# = dh.ts# AND ts.con_id = dh.con_id AND dh.con_id IN (:con_ids)" on "jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=tcp)(HOST=example.host.com)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=example.host.com))))" due to: PreparedStatementCallback; bad SQL grammar [SELECT /*+ RULE */ nvl(dh.name, 'NONAME') AS file_name, dh.file# AS afn, dh.rfile# AS rfn, ts.name AS tablespace_name, dh.bytes, fe.febsz AS blocksize, dh.status, decode(fe.con_id, 2, 'READ ONLY', decode(bitand(fe.feflg, 4), 4, 'READ WRITE', decode(bitand(fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12,'READ WRITE', 'UNKNOWN'))) as read_mode, dh.creation_change#, dh.con_id as con_id, dh.error, feplus as plugin_change# FROM v$datafile_header dh, x$kccfe fe, v$tablespace ts WHERE dh.file# = fe.fenum AND dh.con_id = fe.con_id AND ts.ts# = dh.ts# AND ts.con_id = dh.con_id AND dh.con_id IN (?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "FE"."CON_ID": invalid identifier
, attempt # 1 (refreshing pool)