Hello,
Let me start by providing the details and what I have done so far and what I'm attempting to do.
I have a Suse Linux 13.1 desktop
I have managed to install Oracle 12c database on the machine. ( While installing I selected the option to install as container db)
I have set my .bashrc file to set oracle variables on startup.
ORACLE_HOSTNAME=shona.teamshona.dom; export ORACLE_HOSTNAME
ORACLE_BASE=/home/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
I understand that with oracle 12c we have the Container DB (CDB) and Pluggable DB (PDB)
I have modified My tnsnames.ora file as below-
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = shona.teamshona.dom)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shona.teamshona.dom)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.teamshona.dom)
)
)
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shona.teamshona.dom)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl.teamshona.dom)
)
)
I have my listener.ora file as
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = shona.teamshona.dom)(PORT = 1521))
)
)
#pdborcl.teamshona.dom
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl.teamshona.dom)
(SID_NAME=orcl))
(SID_DESC=
(GLOBAL_DBNAME=pdborcl.teamshona.dom)
(SID_NAME=pdborcl))
)
My Listener status -
lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-FEB-2014 21:26:29
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 06-FEB-2014 21:25:31
Uptime 0 days 0 hr. 0 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/linux-gan9/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-gan9.nycap.rr.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=linux-gan9.nycap.rr.com)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.teamshona.dom" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.teamshona.dom" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.teamshona.dom" has 2 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Instance "pdborcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
I can login to sqlplus as sys and have issued the following commands-
Connect to sqlplus -> sys as sysdba
> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2291472 bytes
Variable Size 671090928 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20017152 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME OPEN_MODE RES OPEN_TIME
PDB$SEED READ ONLY NO 06-FEB-14 08.33.36.043 PM
PDBORCL READ WRITE NO 06-FEB-14 08.38.27.992 PM
SQL> ALTER SESSION SET container = pdborcl;
Session altered.
SQL>alter user hr identified by *** account unlock;
-user altered-
Now I exit out and try
$ sqlplus
Enter user-name: hr@pdborcl
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: hr@pdborcl.teamshona.dom
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
I have read a lot about connecting to PDB
Numerous blogs about how oracle 12c uses service name and not SID to connect to PDB etc etc.
But ultimately I'm unable to connect to my pdb using my hr user.
Appreciate the help in advance and looking forward for a solution.
Will provide any additional details as requested