Link to home
Start Free TrialLog in
Avatar of System Admin
System Admin

asked on

Reset Oracle 9 Server username/password

I'm running Oracle 9i and on Windows 2003 Server.  This is a legacy application that was setup by an old administrator and I need to access to recover the data to move.  I found the command for sqlplus and to use /nolog but SQLplus requires an admin and password.  If I have physical access to the server how to I reset the Oracle Admin login?  Or which login do i need to try to reset to be able to access features like the Database, exporting, backup and so on.  It seems they locked every place to access, Oracle enterprise console, SQLPlus, and the Database itself.  I assume once I get into enterprise console I can just reset the DB password myself if we even need to.  I'm just trying to get if off this server before it crashes.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

On the database server try:
sqlplus / as sysdba

That will connect you as the SYS user which owns the data dictionary.  That is root in the Oracle world.

From there you can access everything.
I should also add that the original DBA account is system.  Once you have connected as sys, you can change the system password:  alter user system identified by new_password;

Then I would do everything like exports/backups/??? as system.
Avatar of System Admin

ASKER

What if the sqlplus tool has a username/password combo?  Where does that login come from or if I don't have that?
check the local windows group ora_dba via compmgmt.msc
add the account you are logged in with to that group

that's one of the steps to avoid having to enter a password for / as sysdba
Yes, I forgot the ORA_DBA group.


>>What if the sqlplus tool has a username/password combo?

The '/' is part of the username/password combo.  It is called OS authentication and the 'as sysdba' is a special addition that tells the tool it is a 'special' connection.  Most of the time when Oracle sees 'as sysdba' it ignores the username and password.

You can likely connect using:  sqlplus fred/flintstone as sysdba

You will still connect as the 'SYS' user.

Just try it?
I doubt that this will work:
sqlplus fred/flintstone as sysdba

This has a better chance of working:
sqlplus scott/tiger

But, even if that works, that account has very few privileges

This may work:
sqlplus system/manager

If that works, that will give you quite a bit of power in Oracle.

Be aware that "sys" and "system" are two different users in Oracle. "Sys" is the more powerful of the two.  

If your Windows user is a member of the ORA_DBA group, (or if you add your Windows user to the ORA_DBA group) on the database server, you should be able to run this at a command (DOS) prompt:
sqlplus / as sysdba

That should connect you to the database as "sys" (the superuser in Oracle).  Be careful with this!  As "sys" you can change or delete anything in that database.
>>I doubt that this will work:
C:\>sqlplus fred/flintstone as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 15 10:12:24 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
USER is "SYS"
SQL>

Open in new window

That only works in your system because someone created a user named FRED with a password of: FLINTSTONE and also granted FRED the privilege to "connect as sysdba".  Oracle does not include a default user named FRED. Oracle does include users named:
SYS
SYSTEM
SCOTT
(plus some others)
Nope:

SQL> select username from dba_users where lower(username)='fred';

no rows selected

Open in new window


I'm guessing you cannot reproduce it on your local systems?  Remember I said "almost".  Depending on some of the security setup it doesn't work 100% of the time.  Out of the box, almost 100% of the time.
Wow thank you for the responses!  I think someone did this on purpose.

1.  My admin account is in the group of ORA_DBA group - and it still prompts for login.
2.  I tried sqlplus system/manager and said bad login.
3.  The command sqlplus / as sysdba  -  this brings up a list of commands - Usuage, options like -H,-L-S and so on and put me back at C: prompt

Any more things to try?  Sorry about other steps not working, I think the previous sys admin did this on purpose...
>>My admin account is in the group of ORA_DBA group - and it still prompts for login.

try sqlplus <enter>

When prompted for username use:  / as sysdba
I'm also working with my DBA and we tried sqlplus "/ as sysdba"

It still says ERROR:  ORA-01031:  insufficient privileges
>>It still says ERROR:  ORA-01031:  insufficient privileges

Try my previous suggestion of entering / as sysdba when prompted for the username.

If that still gives you the  ORA-01031, is your logged in user a local or domain account?  If a Domain account. I've had problems using Domain  accounts.  Can you try a local account that is in the ORA_DBA group.

If that fails, we might try recreating the Oracle password file:
http://docs.oracle.com/cd/B10501_01/server.920/a96521/dba.htm#1229

Make sure you save off the one that exists before trying to create a new one.
SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I will try to password reset file command today.  To answer a few questions we are using the local admin account with is part of the group.  This server is not hooked up to a network at all.  A standalone server that users walk up to and use.  The other command still says wrong user name or password.  Trust me I know you say this is a standard way of working but as I stated either the vendor or the system admin did all of this on purpose so we couldn't get back in.  So most defaults logins will not work.  Let me try the password link and test that.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, with all four of those pieces in place (that Geert just listed) SQL*Plus should allow you to log in, *AND* you will then actually be connected as "SYS" - the superuser in Oracle.
@slightvw it seems it has already been set to none, which I guess is strange?

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
did you try it with NTS ?

you can just edit that file, don't worry about the disclaimer, it's just a text file
@Geert - so there also seems to be a backup file:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


So if I follow your advice:

the requirements for / (or with any password and user):

    login from a command box on the database host *** I have physical access to this Windows 2003 server and logged in under ora_dbagroup
    your osuser is part of local ora_dba group (or ora_[sid]_dba group)
    the sqlnet.ora parameter SQLNET.AUTHENTICATION_SERVICES=(NTS) (sorry for contradicting you on this slight ..., but i tested this too)
    the init parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive (or shared) in that database  ***

How do I set the  init parameter REMOTE_LOGIN_PASSWORDFILE is set to exclusive (or shared) in that database?
If I cannot access the database.  While we wait I'm going to change to NTS and just test the letmein/now!

Where does that come from is that just a known oracle default login?
@Geert I did edit the file and then tried to login with letmein/now!  
Just to be clear can I try any service to login.  Like use SQLPLUS or where should I be logging in?
Also do I need to restart the service or the server after a change in the txt file?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
to access the parameters of a database look in
[oracle_home]\database\init[oracle_sid].ora

[oracle_home] is where oracle software is installed
[oracle_sid] is the name of the database
uhm ... side note.
i have no clue if that script works on win2K3

it's a somewhat ancient windows version now
@ Geert

First setting the .ora file to NTS did work to login as  sqlplus "/ as sysdba"
But just for notes you have to add the quotes.
Also your script is cool and did also work by dropping into the SQLPLUS prompt.
Don't know if that is because I also changed or not.

Now once in no commands are working.  Are we still doing something wrong, we just want to dump the tables and get rid of this server/program.  I've added screenshot to help.
oracle.JPG
"How do I set the init parameter REMOTE_LOGIN_PASSWORDFILE ... to exclusive (or shared) in that database, if I cannot access the database.?"

That is not a problem, since the init parameters for Oracle are stored outside of the database.  These used to be in a plain text file in the $ORACLE_HOME\dba directory.  Look there for a file named: "init[SID].ora" (where [SID] is the name of your database.  This may be "ORCL" or whatever else someone set up in your system) and open that with a text editor, like Notepad.  Save a copy of this here with a different name, or somewhere else where you can find it back if you need it, before you change anything in it. Be careful if you use WordPad or a word processor on this file, since they can introduce non-displayed and non-printable characters that will cause problems.  Or, they may add a different file extension, so Oracle will ignore the file after that.

In Oracle9 (or maybe Oracle10?) Oracle introduced the concept of an "spfile" in addition to the older "pfile" that Oracle databases used to use for their parameters.  Look for a file named "spfile [SID].ora" in this same directory.  If you have one of those, it becomes slightly more complex, because you cannot edit the spfile directly.

If you have just an "init[SID]ora file there, you can simply add the line: "REMOTE_LOGIN_PASSWORDFILE = exclusive" to it, then restart the database.
Just wondering what is the difference between none and NTS?  Either way the script does also work on a Win2k3.  Thanks for sharing you will get the points as well as others when I close.  Still testing other commands right now.
In your oracle.JPGm file, this line indicates success:
"Connected to:"

The three lines of text following that, plus the "SQL>" prompt on the next line confirm that you are in the database at that point.

Those "show  ..." commands are not valid SQL commands

But, you can use this command yourself (from a DOS prompt) to get into the database:
sqlplus "/ as sysdba"
>>Now once in no commands are working

SQL Server and/or MySQL commands will not work in Oracle.

What exactly are you wanting to do now that you can connect?

Exporting is done with command line utilities:  exp/imp.  Backups are normally done using RMAN.  There are also other options.

None of these use sqlplus.

What you can do now is change the passwords for SYS and SYSTEM so once you do figure out what tools to use, you know the passwords.
you'r in !
congratulations !
you can do everything now ... just need to figure out how.
now, you can start with the serious questions :)

poinx ?
nah, only gave that script so we would see a smile and a relieving sigh :)
on second thoughts, if i can steal some points from slightwv ... give me some poinx too
you could read the administrators guid for oracle 9.
http://docs.oracle.com/cd/B10501_01/win.920/a95491/toc.htm

but since it's prod, which doesn't sound like a test system ... you can always ask questions to get you in the good direction
Yes both get points!  I understand that the next part is different.  So thank you again for this hard one for me.

In short in someone reads this bottom comment

1.  the sqlnet.ora parameter SQLNET.AUTHENTICATION_SERVICES=(NTS)
2.   sqlplus "/ as sysdba"

Bam your in, Thanks team!