0

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)

  1. I have 2 versions of sql developer: 4.1.2.20, and the original from instalation: 3.2.20.10.

  2. 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:

  1. Install a fresh copy of oracle 12c (i did a desktop installation).
  2. 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 to HOST=0.0.0.0
  3. 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...

  • I added detailed, step by step, solution. Look in the EDIT: Full Solution at the bottom. – SHR May 31 '16 at 13:11
2

First of all check whether your pluggable database is opened or not.

SQL> select name, open_mode from v$pdbs where name='PDBORCL';

If it is in mounted mode then,

SQL> alter pluggable database pdborcl open;

Now on SQL Developer Create new connection as,

Connection Name: demo-pdborcl

Username: demo

Password: *****

Connection Type: Basic(If you wish to use TNS connection type you have to create TNS Network Alias in client side.)

Hostname: ip/dns

Port: 1521

SID: (If you use SID like 'ORCL' you will be connected to root container, therefore use Service name instead of SID)

Service name: (You can get your pdb's service name by firing $ lsnrctl serivce, also you can use USE_SID_AS_SERVICE_listener parameter on $ORACLE_HOME/network/admin/listner.ora file as USE_SID_AS_SERVICE_listener=on and any given SID will be treated as service name.

Some useful links-

  • Creating a Database Connection Using SQL Developer
  • Connect HR/SYS user with SQL Developer in Oracle12c Using TNS Service
  • Connecting to a Container Database (CDB)
  • USE_SID_AS_SERVICE_listener_name

    • Thanks again, can you please see the Edit in the end of my question and tell me if you see a problem? – SHR May 30 '16 at 10:57
    • Have you set the local_listener parameter? I got that updated error because that's not the service which connects to you the pdb. – JSapkota May 30 '16 at 11:14
    • I guess not. How can I do it? – SHR May 30 '16 at 11:15
    • I get ORA-02097: parameter cannot be modified because because specified value is invalid. .... ORA-00132: syntax error or unresolved network name 'LISTENER' . – SHR May 30 '16 at 11:22
    • 1
      Reinstalled the server and change ip in the listener to any, and reopen pdborcl and now it works. – SHR May 31 '16 at 10:17

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.