TOAD ORACLE ORA-01031: insufficient privileges

TOAD issue

Question:  I can connect to Oracle just fine via SQL*Plus, but I get an ORA-01031 error when I try to connect through TOAD:

ORA 01031: Insufficient privileges.

How can I connect to Oracle with TOAD?

SQL> select * from v$pwfile_users;

no rows selected

SQL> show parameter remote_login_passwordfile;

NAME TYPE VALUE
———————————— ———– ——————————
remote_login_passwordfile string NONE

SQL> show parameter remote;

NAME TYPE VALUE
———————————— ———– ——————————
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string NONE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE

oraprod@newapp:/oratech/proddb/9.2.0/dbs>ls -la
total 328
drwxrwxr-x 3 oraprod dba 4096 Apr 14 16:32 .
drwxr-xr-x 63 oraprod dba 4096 Apr 14 17:24 ..
-rw-r–r– 1 oraprod dba 0 Apr 12 21:53 PROD_newapp_ifile.ora
-rw-r–r– 1 oraprod dba 0 Apr 12 21:26 PROD_oradb_ifile.ora
drwxr-xr-x 3 oraprod dba 256 May 11 2010 backup
-rw-r–r– 1 oraprod dba 8385 May 06 2010 init.ora
-rw-r–r– 1 oraprod dba 19710 Jul 01 2005 initPROD.bak
-rw-r–r– 1 oraprod dba 19796 Apr 14 16:27 initPROD.ora
-rw-r–r– 1 oraprod dba 22429 Nov 08 2010 initPROD.ora.bk.orig
-rw-r–r– 1 oraprod dba 19692 Apr 14 16:32 initPROD.ora.oac.orig
-rw-r–r– 1 oraprod dba 19671 May 11 2010 initPROD.ora_old
-rw-r–r– 1 oraprod dba 19738 Apr 12 21:53 initPROD_noaq.ora
-rw-r–r– 1 oraprod dba 12920 Mar 10 2002 initdw.ora
-rw-rw—- 1 oraprod dba 24 Apr 14 16:33 lkPROD

$ORACLE_HOME/dbs doesn’t have the password file.

—–

Answer:  

Create Oracle password files for Each databases like below and GRANT SYSDBA privilege to SYS:

Follow these steps to connect to Oracle from TOAD:

STEP 1: Create a password file:

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd.ora password=oracle entries=5

#### Go to the path: $ORACLE_HOME/dbs
______________________________________

$ orapwd FILE=orapwPROD IGNORECASE=y FORCE=y NOSYSDBA=N
[ Be very particular about the instance name after ‘orapw<Instance_name>’ ]

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5
Force=y

Enter password for SYS: sys

$ export ORACLE_SID=ECDRGJHR

$ sqlplus ‘/ as sysdba’

SQL> grant sysdba to sys;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS                            TRUE  TRUE  FALSE

Execute the command: orapwd file=/oratech/proddb/9.2.0/dbs/orapwPROD password=sys entries=5
oraprod@newapp:/oratech/proddb/9.2.0/dbs>ls -ltra
total 336
-rw-r–r– 1 oraprod dba 12920 Mar 10 2002 initdw.ora
-rw-r–r– 1 oraprod dba 19710 Jul 01 2005 initPROD.bak
-rw-r–r– 1 oraprod dba 8385 May 06 2010 init.ora
-rw-r–r– 1 oraprod dba 19671 May 11 2010 initPROD.ora_old
drwxr-xr-x 3 oraprod dba 256 May 11 2010 backup
-rw-r–r– 1 oraprod dba 22429 Nov 08 2010 initPROD.ora.bk.orig
-rw-r–r– 1 oraprod dba 0 Apr 12 21:26 PROD_oradb_ifile.ora
-rw-r–r– 1 oraprod dba 19738 Apr 12 21:53 initPROD_noaq.ora
-rw-r–r– 1 oraprod dba 0 Apr 12 21:53 PROD_newapp_ifile.ora
-rw-r–r– 1 oraprod dba 19796 Apr 14 16:27 initPROD.ora
-rw-r–r– 1 oraprod dba 19692 Apr 14 16:32 initPROD.ora.oac.orig
-rw-rw—- 1 oraprod dba 24 Apr 14 16:33 lkPROD
drwxr-xr-x 63 oraprod dba 4096 Apr 14 17:24 ..
-rwSr—– 1 oraprod dba 2048 Apr 15 13:40 orapwPROD
drwxrwxr-x 3 oraprod dba 4096 Apr 15 13:40 .

STEP 2: Set “REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE” in your init.ora or spfile

STEP 3: Bounce the database (shutdown and re-start)

This should allow you to connect via TOAD