ORA-12518 and then TNS-12560, TNS-00534 and 64-bit Windows Error: 10022: Unknow Error when trying to connect to a cdb or a pdb (Oracle 12cR1)

connectivityoracle-12ctnsnames

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&apos;écoute n&apos;a pas pu transmettre la connexion client
 TNS-12560: TNS : erreur d&apos;adaptateur de protocole
  TNS-00534: L&apos;attribution du droit d&apos;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 @.

Best Answer

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.

Related Solutions

Sql-server – Connection AlwaysOn AG Listener Problem

It can connect from SQL 1 server, which is the primary for AG.

By "connect", do you mean it can ping AG-LISTENER from SQL1?

It sounds like what your problem might be is with the port number you chose for your listener. By choosing 5525, you are selecting a non-default port (1433 would be the default).

So when you try to connect to the listener, what does your connection string look like? I'm guessing it looks something like this:

data source = ag-listener; initial catalog = ...

You have two options here. You can either be explicit with your listener's port number:

data source = ag-listener,5525; initial catalog = ...

Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener for the Server name text box, put in ag-listener,5525.

Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):

alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
    port = 1433
);

It is worth noting when you can use the default port (1433). Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

(portions omitted for brevity)

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening. You can use a network monitoring tool like netmon to accomplish this.

Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).

Oracle Connectivity – Fixing ORA-12560 Error When Connecting SQL*Plus Client to Oracle 11g Linux Server

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one. Also, system as sysdba generally isn't used, not even sure if that makes sense. SYS as sysdba and just plain system are used.

Enter user-name: system@ORCL
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

If you don't have tnsnames.ora set up on your Windows client, you can still connect via EZCONNECT string @"hayer:1522/orcl" instead of setting up tnsnames (yes, the quotes are needed).

Enter user-name: system@"hayer:1522/orcl"
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

Best Answer

Related Solutions

Sql-server – Connection AlwaysOn AG Listener Problem

Oracle Connectivity – Fixing ORA-12560 Error When Connecting SQL*Plus Client to Oracle 11g Linux Server

Related Question