ORA-12154 TNS:could not resolve the connect identifier specified while installing APEX

Question

I have RHEL 6.8 Oracle version on Azure on which I installed database following Microsoft's tutorial Create an Oracle Database in an Azure VMe. Everything went fine with the tutorial. Now I try to install APEX with this one Oracle Application Express (APEX) 5.x Installation and I am stuck at line CONN sys@pdb1 AS SYSDBA.

After entering a password (doesn't matter if it is correct) I get every time ERROR: ORA-12154: TNS:could not resolve the connect identifier specified.

I have already tried:

• Creating variable TNS_ADMIN
• Adding ORACLE_HOME/network/admin to PATH
• Creating and editing files:

listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OracleDbVm)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

tnsnames.ora:

WINDOWNETSERVICE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleDbVm)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb1)
    )
  )

Output of lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-JUL-2017 09:02:06

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleDbVm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                25-JUL-2017 23:19:33
Uptime                    0 days 9 hr. 42 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/OracleDbVm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OracleDbVm.{some_values}.ax.internal.cloudapp.net)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=OracleDbVm.{some_values}.ax.internal.cloudapp.net)(PORT=5502))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

Output of my env:

[oracle@OracleDbVm admin]$ env
HOSTNAME=OracleDbVm
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
OLDPWD=/home/oracle
USER=oracle
LS_COLORS={colors_long_values}
ORACLE_SID=cdb1
TNS_ADMIN=/u01/app/oracle/product/12.1.0/dbhome_1/network/admin
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin:/u01/app/oracle/product/12.1.0/dbhome_1/bin:/home/oracle/bin
PWD=/u01/app/oracle/product/12.1.0/dbhome_1/network/admin
LANG=en_US.UTF-8
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
HISTCONTROL=ignoredups
SHLVL=1
HOME=/home/oracle
LOGNAME=oracle
LESSOPEN=||/usr/bin/lesspipe.sh %s
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
G_BROKEN_FILENAMES=1
_=/bin/env

What can I do more to fix that problem?

Answer 1

Your CONN sys@pdb1 AS SYSDBA is trying to connect to an instance with a SID (or tnsnames entry) of pdb1, but in a multitenant environment all your databases share the same SID (cdb1 in your case), and you don't have a pdb1 entry in your tnsnames. When connecting to a PDB, you need to use the service name in the connect string.

You can either update your tnsnames like this:

WINDOWNETSERVICE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleDbVm)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cdb1)
    )
  )

pdb1=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=OracleDbVm)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=pdb1)
    )
  )

And then try CONN sys@pdb1 AS SYSDBA again...

Or you can try using a full connect string with the service name, like:

conn sys@OracleDbVm:1521/pdb1 as sysdba

I think you could also connect to the CDB, switch to pdb1, and then start the script. Let me know if you want more details on that option.