I'm trying to connect sql developer to a fresh installation of oracle:
I can connect to sys user via sqlplus I can connect to sys user via sql developer when I select Connection Type "Local\Bequeath". But I guess it didn't been connected to the PDB, just to the container.
I've created user called demo with following command:
alter session set container=pdborcl;
create user demo identified by password QUOTA unlimited on users account unlock;
I'm trying to connect to the DB with username and password like I did in oracle 11g.
To user: sys as sysdba, And to the new created user demo.
I don't care about pdb, but as I understood there is no other option in oracle 12c...
I've tried to set entry called PDBORCL in the tnsnames.ora, I've also have tried to use service name PDBORCL. as I saw in some manual on the internet, but it didn't worked for me.
I'm getting the following errors:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Again, I used a default installation of oracle 12c, except setting passwords and select server type, I didn't changed much.
What I'm looking is a simple "todo list" to configure connection in the sql developer, to connect, even from a remote machine, to oracle 12c DB.
BTW: (if its matter)
I have 2 versions of sql developer: 4.1.2.20, and the original from instalation: 3.2.20.10.
I'm currently working on windows server 2008 OS.
Thanks,
EDIT:
Query:
SQL> select name, open_mode from v$pdbs where name='PDBORCL';
Result:
NAME OPEN_MODE
-------- ----------
PDBORCL READ WRITE
Command line:
lsnrctl service
Result:
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 30-MAY-2016 13:26:20
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=PROTOCOL=TCP)(HOST=192.168.19.58)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
I've tried to use service name CLRExtProc. Now I have the following error:
Status: Failure -Test failed: The Network Adapter could not established the connection
listener.ora file:
# listener.ora Network Configuration File: C:\app\Administrator\product\12.1.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\Administrator\product\12.1.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\12.1.0\dbhome_1\bin\oraclr12.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.58)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
tnsnames.ora
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.58)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#this is my addition I guess it not written properly
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.58)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
EDIT:
command: lsnrctl STATUS LISTENER
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 30-MAY-2016 14:22
:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.19.58)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date 29-MAY-2016 17:33:36
Uptime 0 days 20 hr. 48 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\Administrator\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\Administrator\diag\tnslsnr\DevOraRX\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.19.58)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
EDIT: Full Solution
This is how it works:
- Install a fresh copy of oracle 12c (i did a desktop installation).
- Changes in the
listener.ora:- in the SID_LIST_LISTENER I added the following section:
(SID_DESC =(SID_NAME = pdborcl)(ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)) - in the LISTENER instead of
HOST=127.0.0.1, I set it toHOST=0.0.0.0
- in the SID_LIST_LISTENER I added the following section:
- restart oracle listener service.
now in the sql developer:
login as sys user:
Username: sys
Password: ****
Connection type: Basic, Role: SYSDBA
Hostname: localhost
Port: 1521
SID: orcl
Now check if pdb is up:
SQL> select name, open_mode from v$pdbs where name='PDBORCL';
If it is in mounted mode then,
SQL> alter pluggable database pdborcl open;
Create the user + grant permissions:
alter session set container=pdborcl;
create user demo identified by password QUOTA unlimited on users account unlock;
grant create session to demo;
grant create table to demo;
Connect as demo user in sql developer:
Username: demo
Password: ****
Connection type: Basic, Role: default
Hostname: localhost
Port: 1521
Service name: pdborcl
And, again, Thanks to JSapkota.
Now I'm trying to figure the sqlplus and instant client connection strings to pdbs...
