Database Administration
oracle connectivity
Updated Fri, 24 Jun 2022 01:02:32 GMT

ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host


I'm a software developer and am working on getting an Oracle VM Appliance running for testing purposes (the Database App Development VM with Oracle 12c - http://www.oracle.com/technetwork/community/developer-vm/index.html#dbapp). The host OS is Win 8.1 x64. I'm having trouble logging into the example schemas (HR, SH etc) from the host machine, with error ORA-01017. I've set up a tnsnames.ora file on the host and can connect as both SYS (as SYSDBA) and SYSTEM from there, so my basic setup seems fine (e.g. no firewall issues). The service alias is set to ORCL12C (the same as the SID on the VM).

the current situation is as follows:

  1. Port-forwarding and Host-only network both set up in the VM.
  2. From the Win8.1 host I can logon and run queries using both SQL*Plus and SQL Developer as SYS as SYSDBA/SYSTEM using either 0.0.0.0 (port-forwarding) or 192.168.56.101 (host-only) in tnsnames.
  3. When I attempt a login to HR from the host using SQL Developer I get the ORA-01017 error (using "Basic" connection type with default role). This is the case for both SID and Service Name options.
  4. A login attempt to HR from the host using SQL*Plus to HR@orcl12c also gives an ORA-01017 error.
  5. Within the VM I can connect to the HR schema using SQL Developer only if I select the Local/Bequeath connection type, but it fails with the ORA-01017 error if I use the Basic connection type. I can connect to SYSTEM or SYS/SYSDBA using the Basic connection type, however.
  6. Within the VM I can connect to HR, SH etc using SQL*Plus with no problems.
  7. I'm using the instant client on the host.

So there appears to be something I'm missing when using the Basic connection type with standard users in SQL Developer, both within the VM and from the host (possibly related to the SQL*Plus failure from the host?). I've done extensive web searching but can find nothing to solve this. Thanks.




Solution

Problem solved - turns out that Oracle 12c has a new pluggable database architecture, and that's where the example schemas reside. So the root container is identified by the "orcl12c" SID, but the pluggable DB is "orcl". The solution was to add a new entry to the tnsnames file as follows:

ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (SID = orcl12c) ) )

In the host PC SQL Developer logon screen choose the "TNS" connection type and ORCL from the dropdown. You'll then be able to connect to the example schemas from the host PC.







External Links

External links referenced by this document: