oracle-databaseora2pg

ora2pg fails to connect but sqlplus works

I am using the setup from https://oracle-base.com/articles/19c/minimum-viable-oracle-database-19c-installation-on-ol8#download-software that does this:

sqlplus / as sysdba <<EOF
alter session set container=ORCLPDB1;

create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

exit;
EOF

Login to the DB is successful with:

sqlplus testuser1/testuser1@//localhost:1521/ORCLPDB1

But with an ora2pg.conf file set up thusly:

ORACLE_DSN      dbi:Oracle:host=localhost;service_name=ORCLCDB;port=1521
ORACLE_USER     testuser1
ORACLE_PWD      testuser1

...I fail to login. I have tried

  1. Changing the DSN and it properly says it cannot find the database at all; good
  2. Changing the service_name and it says no such service exists; good
  3. Trying both service_name=ORCLCDB (as it appears in tnsnames.ora) and service_name=ORCLCDB1 in the ORACLE_DSN. ORCLCDB waits for a bit then fails; ORCLCDB1 fails fast with:
FATAL: 12514 ... ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (DBD ERROR: OCIServerAttach)

The ora2pg.conf is a copy from the dist version with just DNS, USER, and PWD changed.

Any ideas?

Solution

  • Service name of your pluggable database is ORCLPDB1, so use it instead of CDB service name(ORCLCDB):

    ORACLE_DSN      dbi:Oracle:host=localhost;service_name=ORCLPDB1;port=1521
ORACLE_USER     testuser1
ORACLE_PWD      testuser1