asked on

How Can I get Logged Into Oracle DB as SYSDBA via SQLPLUS?

I have a "local" Oracle DB installed on my machine. Version: 11.2.0.1

I use the DB for some software I use. However, I have forgotten what the password is for the DB user I connect with (JDE).

So I need a way to login as an admin to be able to access my DB and reset my DB user password.

I have try to login as SYSDBA but I"m not sure if I"m doing something wrong on the commands. I'm NOT an Oracle DB and only a novice really so I'm sure i'm prob just entering the command wrong.

My failed attempts are below. Please help!! Thanks!

D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 06:22:05 2013

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

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
D:\app\oracle\product\11.2.0\client_1\BIN>set ORACLE_SID E1LOCAL
Environment variable ORACLE_SID E1LOCAL not defined

D:\app\oracle\product\11.2.0\client_1\BIN>set ORACLE_SID E1Local
Environment variable ORACLE_SID E1Local not defined

D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 06:25:43 2013

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

Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 06:26:12 2013

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

Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 06:26:51 2013

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

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: sysdba
D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 06:27:11 2013

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

ERROR:
ORA-12560: TNS:protocol adapter error

slightwv (䄆 Netminder)

You are missing an '=' sign:
set ORACLE_SID=E1LOCAL

Then retry / as sysdba.

You can also verify the listener knows about the instance: lsnrctl status

matrix0511

ASKER

@slightwv, I was able to get the oracle sid set since it didnt give an error this time. However, the sqlplus command still doesn't work. results below.

Enter user-name:
D:\app\oracle\product\11.2.0\client_1\BIN>set ORACLE_SID=E1LOCAL

D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 07:05:40 2013

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

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:

matrix0511

ASKER

Here are the results of the "lsnrctl status" command:

D:\app\oracle\product\11.2.0\client_1\BIN> lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-FEB-2013 07:07
:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tk030adp01.WTSCUSTOMER.W
TSERVICES.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 12-OCT-2012 15:40:47
Uptime 122 days 16 hr. 27 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File c:\Oracle\E1Local\network\admin\listener.ora
Listener Log File c:\oracle\diag\tnslsnr\TK030ADP01\listener\alert\log.x
ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TK030ADP01.WTSCUSTOMER.WTSERVICES.co
m)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "E1LocalXDB" has 1 instance(s).
Instance "e1local", status READY, has 1 handler(s) for this service...
Service "e1local" has 1 instance(s).
Instance "e1local", status READY, has 1 handler(s) for this service...
The command completed successfully

D:\app\oracle\product\11.2.0\client_1\BIN>

slightwv (䄆 Netminder)

Make sure the OracleServiceE1LOCAL service is started.

matrix0511

ASKER

Yeah, its been running already. still have the issue.

Kent Olsen

Hi all,

I need to use quotes on the command line to connect as the super user.

sqlplus '/ as sysdba'

Otherwise, I get the same error as does matrix....

Kent

matrix0511

ASKER

I tried that but when I do it prompts me for a password. Does anyone know what the default password is for sysdba user?? I need to get on ASAP.

D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 08:52:13 2013

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

Enter password:

Kent Olsen

Ah, but that's progress. :) You're no longer getting the protocol error as sqlplus tries to handle parameters that it doesn't understand.

I don't know that there is a default password. That would seem incredibly dangerous.

If you'll sign on as a local administrator, you should be able to connect as sysdba without having to enter a password.

Kent

slightwv (䄆 Netminder)

>>client_1\BIN

Do you have a client and server install on the same machine?

Set your ORACLE_HOME to the database home and make sure you are running slqplus from that bin folder. Let us know if that solves the issue.

>>Does anyone know what the default password is for sysdba user??

The default used to be change_on_install for sys and manager for system.

Unfortunately this won't help you here. To add to what Kdo stated: As soon as Oracle sees 'as sysdba', it ignores the username and password you provided (most of the time).

matrix0511

ASKER

@slightwv, I have both client and server installs. I just can't recall which one is which. So far I have been using the path below for sqlplus:

D:\app\oracle\product\11.2.0\client_1\BIN

But I also have an intall in the path below. This could be the actual Oracle DB:

C:\Oracle\E1Local\BIN

I just tried the ORACLE_HOME but it still fails. I will try the other path (D:\XXX) and see if it works.

C:\Oracle\E1Local\BIN>set ORACLE_HOME=C:\Oracle\E1Local\BIN

C:\Oracle\E1Local\BIN>sqlplus '/ as sysdba'
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

C:\Oracle\E1Local\BIN>

matrix0511

ASKER

Nope. the other path fails as well. I don't know what else to try. Very frustrating.

D:\app\oracle\product\11.2.0\client_1\BIN>set ORACLE_HOME=D:\app\oracle\product\
11.2.0\client_1\BIN

D:\app\oracle\product\11.2.0\client_1\BIN>sqlplus '/ as sysdba'
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

D:\app\oracle\product\11.2.0\client_1\BIN>

slightwv (䄆 Netminder)

>>sqlplus '/ as sysdba'

Stop trying it with the quotes.

>>set ORACLE_HOME=C:\Oracle\E1Local\BIN
ORACLE_HOME does not have the BIN directory.

Try:
set ORACLE_HOME=C:\Oracle\E1Local

For the second attempt, HOME should not be the client install when working on the server.

Any resaon you installed a separate client?

Anthony Perkins

Topics: Oracle Database ,Microsoft Operating Systems ,MS SQL Server
Can someone kindly remove the MS SQL Server Topic Area?

matrix0511

ASKER

Ok. I thnk I found the path to the DB. (C:\Oracle\E1Local\database)
See below. But I already tried using that path and it still fails.
2-12-2013-1-51-23-PM.jpg

matrix0511

ASKER

This is what I get now:

C:\Oracle\E1Local\BIN>set ORACLE_HOME=C:\Oracle\E1Local

C:\Oracle\E1Local\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 12 11:17:15 2013

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

Kent Olsen

Hi matrix,

Log in as a local admin or have your user's permissions elevated to be a local admin.

You might create a new (local) user that's not part of the Active Directory that has administrator privileges.

Kent

arnold

what user are you logged into the system?
Linux/Unix you have to run as the user oracle
try
after setting all the prior parameters
sqlplus
connect / as sysdba

ASKER CERTIFIED SOLUTION

matrix0511

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

the os auth is off by default
i guess you created your database with dbca ?
dbca asks what password you want for the sys/system account.

if you have the sys password you don't need os auth.

for your problem with the client concerning this:
SP2-0667: Message file sp1<lang>.msb not found
this is two fold: you have the path to 1 oracle client (bin directory) in your global environment variable and your using a different client manually without setting oracle_home
or it's simply missing those files in directory

i found installing the client with custom option sometimes has those files missing.
installing the runtime with the language you use solves this problem
you reinstall the oracle client over the existing one, it will just add missing items
this is in severl mesg directories like in $ORACLE_HOME\network\mesg

slightwv (䄆 Netminder)

>>Guys I finally figured it out.

Feel free to accept your own post as the solution. If you feel that Experts helped you with some of the earlier problems please award points to the posts that assisted.

matrix0511

ASKER

I was able to resolve this on my own