Reputation: 578
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)
Checked services: OracleOraDB18Home1TNSListener and OracleServiceXE are both running.
Ran "lsnrctl status" and got the following which I assume looks OK (no errors anyway):
My listener and tnsnames.ora files look like this:
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 ****
ORACLE_SID / ORACLE_HOME (even though I listed these above)
Upvotes: 0
Views: 2159