How to reset the password for the administrative login 'AS SYSDBA' if it is lost?

How to reset the password for the administrative login 'AS SYSDBA' if it is lost? [ID 431374.1]
Modified 09-MAR-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1.0.6
Information in this document applies to any platform.
Checked for relevance on 09-Mar-2010
Goal

How to reset the password for the administrative login 'AS SYSDBA' if it is lost?

If for some reason you forgot the remote login password or the passwordfile was corrupted, this document describes how you can recreate the passwordfile. You can only do this if you have the proper privileges to access the passwordfile on the Operating System filesystem. The alternative to this is to use OS authentication to connect to the database as SYSDBA and then change the password with "alter user sys identified by newpassword".
Solution

1. Check v$pwfile_users to list the users having been granted SYSDBA or SYSOPER currently.


SQL> connect system/manager
Connected.
SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
EXTJOB_USER TRUE FALSE
SYSTEM TRUE FALSE

If the database cannot be accessed anymore, use strings on the passwordfile (Unix only):

$ strings $ORACLE_HOME/dbs/orapw$ORACLE_SID

ORACLE Remote Password file
INTERNAL
EXTJOB_USER
SYSTEM

This will give you the usernames that have been granted SYSDBA or SYSOPER.


2. Check that 'init.ora' parameter REMOTE_LOGIN_PASSWORDFILE was set to to EXCLUSIVE:


SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

3. Shutdown the instance for which this passwordfile can provide authentication, use OS authentication to login, if this was disabled, terminate the instance by killing the smon background process.

4. Just in case backup your current passwordfile.

5. Create a new password file using the following command syntax:

orapwd file= password= entries= force= nosysdba=

where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA,
force - whether to overwrite existing file (opt),
nosysdba - whether to shut out the SYSDBA logon (opt for Database Vault only).
There are no spaces around the equal-to (=) character.

On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw$ORACLE_SID
On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA

6. You can now restart the database.

7. Now you can try to connect using the new passwordfile, note that for local connections you can likely use OS authentication, so for a true test, make a remote connection as follows:

$ sqlplus sys/manager@v1020 as sysdba

8. Grant all the users (except internal) you listed in step 1 the SYSDBA or SYSOPER privilege again:

SQL> grant sysdba to SYSTEM;