ii. Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.
If orapwd has not yet been executed or password file is not available, attempting to grant SYSDBA or SYSOPER privileges will result in the following error:
SQL> grant sysdba to SYS;
grant sysdba to SYS
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file
1. Create the password file.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=30
2. Edit the init.ora parameter remote_login_passwordfile.
If you're unsure on which type of file is using by your database.
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
Append the entry into init<ORACLE_SID>.ora.
*.remote_login_passwordfile=EXCLUSIVE
SQL> show parameter password;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
If the database is using spfile, please execute the command below.
* SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=BOTH;
3. Grant SYSDBA or SYSOPER to user.
SQL> grant sysdba to SYS;
Grant succeeded.
4. Confirm the user is listed in the password file.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
---------------------------------------------------------
SYS TRUE TRUE
Removing a Password File
If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA or SYSOPER database administration operations.
