How to reset SYS, SYSTEM or User password or grant SYSDBA, SYSOPER privilege

This article helps to reset SYS, SYSTEM or any other user password and also grant admin privileages. There are different methods to rest the password in Oracle.

As SYSDBA or DBA privilege - As long as you have SYSDBA or DBA privilege you should be able to reset any user password.

$ sqlplus / as sysdba
SQL> alter user <username> identified by <new password>;
For ex:- alter user sys identified by xxxaaxxx;
For ex: - alter user EMP identified by xxssxxxx;

Orapwd Utility – This is very useful utility to rest the password. This utility helps in granting SYSOPER, SYSDBA privileges. Creating orapwd enables users to connect with admin privileges. From Oracle 11g the passwords are case sensitive.

orapwd file=orapwd<SID> password=password entries=max_users

In order Oracle to check for password file the REMOTE_LOGIN_PASSWORDFILE must be set to either SHARED or EXCLUSIVE

SHARED - More than one database can use a password file. But only SYS user recognized by the password file
EXCLUSIVE - The password file can be used by only one database and the password file can contain names other than SYS.

SQL> alter system set remote_login_passwordfile =’EXCLUSIVE’;
SQL> show parameter password
NAME TYPE VALUE
----------------------------- ----------- ----------
remote_login_passwordfile string EXCLUSIVE

You can also grant SYSDBA, SYSOPER privileges to user by logging as SYSDBA. Once the privilege is granted, the users will be added to password file

SQL> grant sysdba to AdminUser;
SQL> grant sysoper to DBAUser;

To see who are all the users has sysdba, sysoper privilege run below command. You can also see the users listed in password file

SQL> select * from v$pwfile_users;

Oracle has listed several articles how to recover LOST password and resolve issues connecting as YSSDBA.

 
How to Set up the Oracle Password File MOSC Note: 1029539.6
Checklist for Resolving Connect AS SYSDBA Issues MOSC Note: 69642.1
How To Recover From Lost SYS Password MOSC Note: 805084.1

Thanks & Regards,

http://oracleracexpert.com