How to Resolve Oracle Error ORA-00904 Raised During a Delphix SnapSync (KBA1531)

Applicable Delphix Versions

Issue

During a SnapSync operation or while attempting to provision or refresh an Oracle virtual database (VDB), an Oracle error ORA-00904 may be raised similar to the following:

Failed to complete query as user "delphix" in database "products" due to an unexpected Oracle Error: 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, dh.error, 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# 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 .

Troubleshooting

This error is typically raised because the Delphix database user has not been setup correctly. It has also been raised after upgrading from Oracle 11g to 12c (when the user had been setup correctly prior to the upgrade).

Resolution

1. Download and run the createDelphixDBUser.sh script from https://cd.delphix.com/docs/latest/upgrading-dsources-after-an-oracle-upgrade
2. Refresh the environment
3. Attempt a fresh SnapSync by clicking the camera icon

Additional Information

How to Create and Maintain Permissions for Delphix OS and Database Users on Host Systems