0

I am seeking for help for a problem I try to resolve for a whole week. As the title say, I try to connect to a pdb or cdb and specifically using the @ statement.

I created my instance named 'test', my cdb named 'testcdb' and my pdb named 'testpdb' on a Windows server 2012.

I have no problem connecting using the connect /as sysdba:

C:\Users\Administrateur>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Lun. Mai 4 15:12:41 2020

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

SQL> connect /as sysdba
ConnectÚ. 

But I can’t connect with the @ :

SQL> connect test/pwd@testpdb as sysdba
ERROR:
ORA-12518: TNS : le processus d'Úcoute n'a pas pu transmettre la connexion
client

I ran some query to try to understand my problem, firstly the status of the listener :

C:\Users\Administrateur>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 05-MAI  -2020 12:
28:28

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

Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.13)(PORT=1521)))

STATUT du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Date de dÚpart                       04-MAI  -2020 15:24:33
DurÚe d'activitÚ                    0 jours 21 heures 3 min. 59 sec
Niveau de trace           off
SÚcuritÚ                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramÞtres du processus d'Úcoute     E:\app\oracle_user\product\12.1.
0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'Úcoute             E:\app\oracle_user\diag\tnslsn
r\srvdo-geofit\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.13)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce se
rvice...
Le service "testcdb" comporte 2 instance(s).
  L'instance "test", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.
..
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

Le service "testcdbXDB" comporte 1 instance(s).
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

Le service "testpdb" comporte 2 instance(s).
  L'instance "test", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.
..
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

La commande a rÚussi

The ping :

C:\Users\Administrateur>tnsping testpdb

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 04-MAI
-2020 15:40:44

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

Fichiers de paramÞtres utilisÚs :
E:\app\oracle_user\product\12.1.0\dbhome_1\network\admin\sqlnet.ora


Adaptateur TNSNAMES utilisÚ pour la rÚsolution de l'alias
Tentative de contact de (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.1
3) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=testpdb)))
OK (0 msec)

My listener.ora :

    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = E:\app\oracle_user\product\12.1.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:E:\app\oracle_user\product\12.1.0\dbhome_1\bin\oraclr12.dll")
        )
        (SID_DESC =
          (GLOBAL_DBNAME =testcdb)
          (ORACLE_HOME = E:\app\oracle_user\product\12.1.0\dbhome_1)
          (SID_NAME =test)
       )
         (SID_DESC =
          (GLOBAL_DBNAME =testpdb)
          (ORACLE_HOME = E:\app\oracle_user\product\12.1.0\dbhome_1)
          (SID_NAME =test)
       )
     )

LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.13)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
DIRECT_HANDOFF_TTC_LISTENER=OFF

My sqlnet.ora :

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

My tnsnames.ora :

TESTCDB= 
      (DESCRIPTION=
          (ADDRESS= 
        (PROTOCOL=tcp)
        (HOST=192.168.2.13)
        (PORT=1521)
          )
        (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=testcdb)
          ) 
      )


TESTPDB= 
      (DESCRIPTION=
          (ADDRESS= 
        (PROTOCOL=tcp)
        (HOST=192.168.2.13)
        (PORT=1521)
          )
        (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=testpdb)
          ) 
      )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

The error in the log.xml file :

TNS-12518: TNS : le processus d'écoute n'a pas pu transmettre la connexion client
 TNS-12560: TNS : erreur d'adaptateur de protocole
  TNS-00534: L'attribution du droit d'accès à la connexion au fils a échoué
   64-bit Windows Error: 10022: Unknown error

I can add i run the two services (listener and test) with the same user, oracle_user (the main user created at the beginning).

My main hint is this TNS-00534. I guess i should maybe grant a role or something to oracle user in windows but i have no idea how to do it.

I started two month ago with Oracle so it may be a really simple problem. But if until now i always managed to resolved every problem i got, i can't figure out where i am wrong this time... I think I need an exterior sight.

Do not hesitate to ask questions if it is not enough!

Thanks to you to have read until now, even if you can't help me!

After advice given, i tested :

SQL> alter pluggable database all open;

Base de donnÚes pluggable modifiÚe.

SQL> select pdb_name, status from dba_pdbs order by pdb_id;

PDB_NAME
--------------------------------------------------------------------------------

STATUS
---------
PDB$SEED
NORMAL

TESTPDB
NORMAL

And got the result :

C:\Users\Administrateur>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 06-MAI  -2020 11:
41:02

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

Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.13)(PORT=1521)))

STATUT du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Date de dÚpart                       04-MAI  -2020 15:24:33
DurÚe d'activitÚ                    1 jours 20 heures 16 min. 33 sec
Niveau de trace           off
SÚcuritÚ                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramÞtres du processus d'Úcoute     E:\app\oracle_user\product\12.1.
0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'Úcoute             E:\app\oracle_user\diag\tnslsn
r\srvdo-geofit\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.13)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce se
rvice...
Le service "testcdb" comporte 2 instance(s).
  L'instance "test", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.
..
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

Le service "testcdbXDB" comporte 1 instance(s).
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

Le service "testpdb" comporte 2 instance(s).
  L'instance "test", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service.
..
  L'instance "test", statut READY, comporte 1 gestionnaire(s) pour ce service...

La commande a rÚussi

But same error message when i connect with @.

0

It doesn't look like the pluggable database has been started. Starting a container database doesn't start the pluggable databases automatically. Try the following:

C:\Users\Administrateur>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Lun. Mai 4 15:12:41 2020

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

SQL> connect /as sysdba
SQL> alter pluggable database open all;
SQL> select pdb_name, status from dba_pdbs order by pdb_id;

Then check "lsnrctl status" and see if testpdb appears in the list of services.

Note: tnsping doesn't actually tell you if a specific service is available, only if a listener is available on specified host and port. You can literally use any made up service name and tnsping will respond with a successful result as long as the listener is up and running.

| improve this answer | |
  • Hi! Thanks for the help. I did it, testpdb does appear but it already did. And when i try to connect i get the same message. Ok for tnsping, it is more clear now. – SimonO May 6 at 11:25
  • Try removing the "SQLNET.AUTHENTICATION_SERVICES= (NTS)" line from your sqlnet.ora. – pmdba May 9 at 19:33

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.