If your administration is as good as anybodies, you are bound to loose the not-so-frequently used password for the SYS and SYSTEM users of oracle. Here are a few ways I found to re-set those passwords:
Method 1: SQLPLUS (Tested on AIX Oracle 9.2.0.1.0)
Log into the database server as a user belonging to ‘dba’ [unix ] or ‘ora_dba’ [windows ] group , typically ‘oracle’, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:
$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:32:09 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> show user USER is "SYS" SQL> passw system Changing password for system New password: Retype new password: Password changed SQL> quit
Next, we need to change the password of SYS:
$ sqlplus "/ as system" SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:36:45 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where <logon> ::= <username>[/<password>][@<connect_string>] | / Enter user-name: system Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> passw sys Changing password for sys New password: Retype new password: Password changed SQL> quit
You should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.
Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)
- Stop the Oracle service of the instance you want to change the passwords of.
- Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
- rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
- Create a new pwd file by issuing the command:
orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts. - Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.
Stop the Oracle service of the instance you want to change the passwords of.
Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
Create a new pwd file by issuing the command:
orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.
My question is where to execute this
Create a new pwd file by issuing the command:
orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
“orapwd” is a utility which is part of your oracle installation. There is probably a path set to it, which means you can execute it anywhere you like, as long as it points to the correct file with the “file=…” parameter, as described.
And here’s some info on resetting passwords for Windows users (in this case, the postgres user): http://www.guusbosman.nl/node/2531
I tried method 2 on Oracle 9.2 on windows 2000. It seems to work fine but I used TOAD to reconnect to the database as sysdba (with user SYS). Then I changed the System password. what is the exact script to reconnect as sysdba using SQLPLUS.
Thanks in advance for your help.
Pierre
To connect as sys/sysdba, you can use the first blue command in method 1 (sqlplus “/ as sysdba”) on a command prompt.
In 10g, the first method works, but you have to enter normally:
sqlplus system/@
because “/ as system” doesn’t work.
Can I translate this article and publish it on my blog with link to this page?
Sure, feel free! 🙂
[…] https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/ Posted by NT Filed in Uncategorized Leave a Comment » […]
very much helpful.. [:-)]
I’m having trouble with oracle 11gr2, and no matter what I do, I just get insufficient privileges…
I am simply trying to install a single instance of oracle 11gr2 on linux.. and I seem to be missing something…
[oracle@appserver3 bin]$ srvctl
****Unable to retrieve Oracle Clusterware home.
Start Oracle Clusterware stack and try again.
Sounds like a missing environment variable, but I’m no Oracle expert so you might want to check out http://stackoverflow.com/ or http://superuser.com/ for help/answers.
I just used method #1 on an 11g system on Windows XP.
Thank you for sharing your knowledge!
SHELL Script File to Confirm and check the status and availability of ORACLE Instance & Schema Name
echo “ENTER INSTANCE NAME”
read instanceName
# Change the Path Accordingly
ORACLE_HOME=/data1/oracle/product/10.2.0/db_1
# Change the Path Accordingly
export PATH=/data1/oracle/product/10.2.0/db_1/bin:$PATH
export ORACLE_HOME
export ORACLE_SID=$instanceName
schemaName()
{
echo “ENTER SCHEMA NAME”
read schemaName
sName=” ‘$schemaName’ ”
ora_schemaName=`echo ‘set heading off
select username from all_users where username = ‘$sName’;
exit’ | sqlplus -s sys/enter as sysdba`
# userName=`echo $ora_schemaName|cut -d” ” -f2`
for schemaName in $ora_schemaName
do
if [ $ora_schemaName = “$schemaName” ]
then
echo “Schema $schemaName Exists in the Instance:$instanceName”
exit;
fi
done
echo “Schema do not Exists in the Instance:$instanceName”
exit;
}
ora_status=`echo ‘set heading off
select status from v$instance;
exit’ | sqlplus -s sys/enter as sysdba`
if [ $ora_status = “OPEN” ];
then
echo “Instance $instanceName is OPEN and in running state”
schemaName
else
if [ $ora_status = “MOUNTED” ];
then
echo “Instance $instanceName is in MOUNT state”
else
if [ $ora_status = “STARTED” ];
then
echo “Instance $instanceName is in NOMOUNT state”
else
echo “Unable to reach Instance: $instanceName”
exit;
fi
fi
fi
Thanks a lot for a quick solution…I was able to recover my 10g Express Edition password…
Thanks once again
Your #1 solution worked for database 10.1.0.2.0 running on Windows Server 2003 (SP1). This post definitely “gave back” to one who had never encountered this situation before now. I’m glad you felt the need to share.
I use the information on the web a lot. This is my way to give back and keep the system working. I’m glad to see that this worked for you. Thanks!
Change password.
$ sqlplus “/ as sysdba”
SQL> ALTER USER sys IDENTIFIED BY ;
[…] 13. Import successful but unable to see it in the system.. shows up under COGS_ADMIN as a table… huh??? ->>speak to RM about this.. Useful links: http://www.orafaq.com/node/67 http://www.orafaq.com/wiki/Datapump http://eqbalz.wordpress.com/2009/10/14/exportimport-datapump-submitting-datapump-job-via-oem-user-does-not-exist/ https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/ […]
[…] Links: https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/ […]
[…] 3-Lost Oracle SYS and SYSTEM password? Method 1: SQLPLUS (Tested on AIX Oracle 9.2.0.1.0) […]
Thank you!
I had this problem under RH linux using Oracle 10.2.0.1.0. When I tried:
sqlplus / as sysdba
I got insifficient privileges while logged on as root. I then did:
su oracle
and I was able to follow method 1 from there.
Wonderful. Got the system password changed to something more memorable nice and easy. Had to add myself into the Windows ORA_DBA group and also ensure that the following was in sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
Logged off and then back on again and all worked a treat. I removed the entry in sqlnet.ora afterwards too.
Cheers chaps, thought I was lost for a moment there with no way of sorting my database out!!!
Glad to hear that this helped. Cheers! 🙂
i am trying to change SYSTEM user password by using second method but getting error as Unable to open password file,Please help,Thanks in advance.
Hi William, I’m afraid there’s too little information in your question to provide you with a good answer, but did you check the posts from other users, above? Some people seem to have to log in as a different user, or change some settings.
I hope the comments will help.
THANKS
i have changed password but now i am facing another problem as “ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)”
Please suggest and also i am new to oracle
thanks in adavance
Hi William, TNS Listener is the process that manages network connections to your database. Either you are connecting with a wrong SID (database id) or your TNSListener does not know of it’s existence. Try to use tnsping to test this, and use “the google” and Oracle’s manual to figure this out. Lots of examples out there.
thank you vary much its working bue only problem is i am not able to connect from OEM
Thanks for the help.
Thanks!
System Admin, Group Policy, Active Directory…
[…]Lost Oracle SYS and SYSTEM password? « Rolfje’s blog[…]…
Thanks for the post, it pointed me in the right direction. In my case, we are running oracle X10 10g on Linux and somehow both SYS and SYSTEM passwords became corrupted. What worked for me:
Become oracle user from root:
su – oracle
Then make sure ORACLE_HOME and ORACLE_SID are set and exported:
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
ORACLE_SID=XE
export ORACLE_HOME ORACLE_SID
Only then, I was able to successfully follow your prescriptions starting from:
sqlplus “/ as sysdba”
Thanks.
Hi dude,
while installing oracle 9i , i kept username and password , so i forgot username and password ,now i am unable to connect to the system/manager .
How to check the username and password of administrator …… Pls suggest anything.
You can not read existing passwords. Any software that allows you to do so has a big security flaw.
SYSTEM and SYS passwords can be reset as described. If you have lost the password of your Windows Administrator or Linux root accounts, please google a bit, there are ways to reset those, too, but generally you need physical access to the machine to do that.
thank you so much…it works
Hi,
I am able to reset the password of user “system” but when i am trying to reset the password of user “sys” it’s asking old password but i forgot the old one. I am using Oracle 10.2.
By using second method new file has been generated but when i am trying to login then again showing incorrect user name and password.
Please suggest
Are you sure you are logging out when switching from SYSTEM to the “/ as sysdba” user? This is a common mistake to make.
i am trying to install primavera p6r8 but when i put the password for the oracle xe system it gives me the following message:
user authentication failed please re-enter password
can you tell me what is happening thank you
Hi,
while changine the paswrd iam geting error…
missing or invalid password…
please suggest
Sorry but I can not help you if you are not specific and precise.
[…] [https://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/] […]
connect to oracle database on unix by typing:
$ sqlplus sys/sysPass1@orcl as sysdba
Is there any mistakes in the above sentence ?
Sorry, but I’m not your syntax checker. Read the documentation for connecting to your database, or be a bit more elaborate on what you want to do, what you tried and why you think it fails.
thanq, i have got lot of struggle to know the forgotten passwords
Sir,
i’m having a bit different type of problem
i changed my sys user password by alter user command
it got changed sucessfully, i also created the pwd file as per shown by you..
but my problem is that i want to login in oracle as sysdba only by my newly created password not by my previously created one
e.g previously i have been typing sqlplus sys/sys as sysdba
after changing password i typed it as
sqlplus sys/xyz as sysdba
i loged in sucessfully but also when i wrote
sqlplus sys/sys as sysdba
it logged me in as sysdba which is wrong because after changing my password it should have shown me login denied….
could you please help me in resolving this problem or making me undestand where m wrong..
would be very thankful to you….
waiting for ur reply
Hello Sumit,
The sys password is stored in a strange way. Please check this answer on Ask Tom to see how that works.
I hope this helps.
thanx
Thanks it worked from XE
thanks man.
its working for me
thanks a lot man
[…] Lost Oracle SYS and SYSTEM password? | Rolfje’s blog – Jan 16, 2007 · 53 Responses to Lost Oracle SYS and SYSTEM password? … my 10g Express Edition password … to reset the password of user “system” but when i … […]
THANK YOU – It’s works
thanks man.
its working for me
thanks a lot man