nzmike
nzmike

Reputation: 578

Oracle 18c XE Windows 10 - SQLPlus returns TNS-12560 but everything else works

First, I understand there are lots of similar posts but I have googled a lot and haven't found the answer to this particular issue.

Second, I'm very new to Oracle (I'm a SQL Server guy) so please be patient.

Ok, so a few months I had a client who gave me an Oracle database backup to import into Oracle 18c XE on Windows 10 to then extract data out of it for them via a .Net program using ODP.Net 11.2. I installed 18c XE and ODP.Net, created a user/schema and imported the backup using DBCA all using tutorials etc. I could connect to the user/schema in SQL Developer and do queries. I ran my .Net program and all was well.

At that point I could connect to SQL plus using 'sys as sysdba' to execute commands like creating the user, granting privileges etc. But now when I log in a few months later to do the same exercise on a new backup I can no longer connect to SQLPlus, always getting "ORA-12560: TNS:protocol adapter error" for any user. Everything else still works but I need SQLPlus to drop the user/schema so I can import the new backup. The client's IT company say they haven't touched Oracle as this is set up just in my user account which no else would have logged in to except me.

So here are the checks I done so far from googling and reading posts on SO. (I've changed the master user and passwords to mike below but these have been tried with the real user and password)

enter image description here

My listener and tnsnames.ora files look like this:

enter image description here

enter image description here

Finally I Looked in the listener log file in C:\app\mike\product\18.0.0\diag\tnslsnr\APP01\listener\alert and got the following which I also assume looks correct:

<msg time='2020-05-29T12:38:48.315+10:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='APP01'
 host_addr='fe80::1dfb:94cd:7fac:9551%5' pid='25368'>
 <txt>29-MAY-2020 12:38:48 * service_update * xe * 0
 </txt>
</msg>

But still I can't connect to SQLPLus using sys - can anyone suggest what might be the issue and how I can fix it? I just need to be able to log in to drop the user I created so I can import the new backup so this is incredibly frustrating!

TIA for any help rendered.

**** EDIT ****

SQLNet.ORA file: enter image description here

ORACLE_SID / ORACLE_HOME (even though I listed these above) enter image description here

Upvotes: 0

Views: 2159

Answers (0)

Related Questions