0

I have Oracle 19c installed on my windows 10 machine.

When I try to log in as sysdba I am getting an ORA-12560: TNS:protocol adapter error error:

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 25 16:47:45 2021
Version 19.3.0.0.0

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

My database is called aws and I've verified that my listener (called LISTENER1) is running:

lsnrctl status LISTENER1

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 25-SEP-2021 16:50:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-0T5DL1G)(PORT=1555)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                25-SEP-2021 16:29:29
Uptime                    0 days 0 hr. 20 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\Oracle_Home\instantclient\network\admin\listener.ora
Listener Log File         D:\Oracle_Home\app09\oracle\base\diag\tnslsnr\DESKTOP-0T5DL1G\listener1\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-0T5DL1G)(PORT=1555)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1555ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-0T5DL1G)(PORT=5500))(Security=(my_wallet_directory=D:\ORACLE_HOME\APP09\ORACLE\BASE\admin\aws\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "38b9417aa9264443afd1d72225643c01.jokefire.com" has 1 instance(s).
  Instance "aws", status READY, has 2 handler(s) for this service...
Service "52448234712340b69f274bcc790ecfe0.jokefire.com" has 1 instance(s).
  Instance "aws", status READY, has 2 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "aws.jokefire.com" has 1 instance(s).
  Instance "aws", status READY, has 2 handler(s) for this service...
Service "pdb1.jokefire.com" has 1 instance(s).
  Instance "aws", status READY, has 2 handler(s) for this service...
The command completed successfully

I've set my oracle_sid to the same name as my hostname:

set oracle_sid=DESKTOP-0T5DL1G

I'm not seeing any errors in the alert logs, but I do see this warning from around the time I tried logging in:

<msg time='2021-09-25T16:41:13.815-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='DESKTOP-0T5DL1G'
 host_addr='fe80::4872:2113:3a49:48e3%7' pid='25252'>
 <txt>WARNING: Subscription for node down event still pending
 </txt>
</msg>

I am new to Oracle. Why am I getting this error and how do I solve this?

3
  • Just as an aside, you are attempting what is known as a BEQueath connection - that is an inter-process call, as opposed to a network connection. That said, the listener is not at all involved.
    – EdStevens
    Sep 27 at 21:11
  • cool, thank you for pointing that out!
    – Tim Dunphy
    Oct 2 at 12:54
  • Start by checking the oracle logs. Find the Oracle install folder and then in there, find th \diag\rdbms\aws\aws\trace\alert_aws.log file. See if you can find any error messages in there that point to a crash condition. If that fails to lead to anything... It isn't common, but I've seen Oracle flake out and get lost, especially if the system doesn't have enough RAM or other processes are taking too much memory from what Oracle wants... there may be a less brute-force fix, but in my case, stopping/restarting Oracle instance and listener services was about it.
    – CaM
    Nov 22 at 15:47
1

The oracle_sid parameter should be the SID of your instance. According to your listener status output your SID is AWS. So try:

set oracle_sid=AWS
sqlplus / as sysdba

Remember, if you've set this up as a multitenant DB (which is the recommended option and the only one supported in newer versions) this will connect you to the CDB$ROOT container, you can use

alter session set container=<pdb name>;

ie

alter session set container=pdb1;

to switch to your PDB.

5
  • Hi! Thanks for answering. I tried setting the sid to aws but I am still not able to log into the database. Here is what I get: PS D:\Oracle_Home> set oracle_sid=AWS PS D:\Oracle_Home> sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 25 21:56:28 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-12560: TNS:protocol adapter error Enter user-name:
    – Tim Dunphy
    Sep 26 at 1:59
  • Note that ORACLE_SID is case sensitive; both the name and the value. oracle_sid is not valid; it should be capitalized, and the SID appears to be aws, not AWS.
    – pmdba
    Sep 26 at 2:41
  • Fire up powershell, what’s the output of (get-service oracleservice*).name? Try lowercase aws. You don’t need to worry about the case of oracle_sid in Windows Sep 26 at 9:46
  • In Windows, oracle_sid and the SID are not case sensitive. I can confirm that on Oracle versions 7, 8, 9, 10, 11, 12, 18, and 19 (gawd I'm getting old). That may be not be true in linux?
    – CaM
    Nov 22 at 15:43
  • 1
    @CaM agreed, not sure why I entertained the idea :) in Linux pretty much everything is case sensitive, Windows is much friendlier ;) I’ve spotted the issue in the questioners comment though - set oracle_sid Was run from powershell not cmd, so the syntax should be $env:oracle_sid='Aws' Nov 23 at 8:24

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.