Troubleshooting the 'ORA-01031: insufficient privileges' error when using EMCA to Create or Drop DBconsole

Troubleshooting Steps

1) Confirm that the database is up.  Connect to the database via sql*plus and do:-

select instance_name,status,version from v$instance;

2) Confirm whether the $ORACLE_HOME environmental variable is correct. This involves comparing the current ORACLE_HOME environmental setting, with the one that was set the last time the database was started.

a) To check the current ORACLE_HOME :-

echo $ORACLE_HOME (unix)
set ORACLE_HOME (windows)

b) To check the $ORACLE_HOME which was set at the time when the database was last started:-

connect to sqlplus as SYS as SYSDBA and issue the following:-

SQL> var OHM varchar2(100);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PL/SQL procedure successfully completed.

SQL> PRINT OHM

OHM
<Oracle_Home which was set at the time that the database was last started will be returned here>

Check whether the OHM which is returned here, matches what is set environmentally.

c) If the values returned by 2a) and 2b) above, do not match, find out the 'real' ORACLE_HOME, to do this check:-

- The path to the oracle home as specified in /etc/oratab.
- The ORACLE_HOME which is shown by calling up the installer
- The ORACLE_HOMES listed in /opt/oracle/oraInventory/ContentsXML/inventory.xml

The ORACLE_HOME should not end in a "/" slash character.

d) If the ORACLE_HOME was set wrongly at the time that the database was started (as determined by 2b), then the database will need to be shut down and re-started. To shut the database down, it will be necessary to set the 'wrong' ORACLE_HOME (the one which is returned in the OHM output). After the database has been successfully shut down, then the correct ORACLE_HOME should be set, and the database can be re-started. eg.

export ORACLE_HOME=/u01/testdb/  <--wrong home which ends with a trailing slash
sqlplus sys/oracle1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 13:14:51 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

After database has shut down:-

export ORACLE_HOME=/u02/testdb  <--correct oracle home with no trailing slash
sqlplus sys/oracle1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 13:14:51 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup

3) Confirm whether it's possible to connect to the database as the SYSDBA user remotely, using sqlplus. As part of the repository creation/drop process, the SYS user needs to be able to connect as SYSDBA. The ORA-01031: insufficient privileges error is returned when the SYS user is unable to connect as SYSDBA.  To test this outside of dbconsole, test a sqlplus remote connection (one which connects via a tnsnames.ora alias) as sysdba. eg:-

$ORACLE_HOME/bin>sqlplus sys/<password>@<alias> as sysdba
eg.
D:\10205\BIN>sqlplus sys/oracle1@testdb as sysdba

Tip* The alias should exist in the $ORACLE_HOME/network/admin/tnsnames.ora (or $TNS_ADMIN/tnsnames.ora if $TNS_ADMIN is set).  This test is done from the same machine and the same $ORACLE_HOME as the database being configured for dbconsole by EMCA.

If the above returns the ora-1031 error, check the following:-

a) Check whether the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set correctly in the database.  For the purposes of configuring dbconsole, it must be set to 'EXCLUSIVE'.  This value can be checked by logging into the database via sql*plus and typing "show parameter remote".

SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string EXCLUSIVE

If this parameter is not set to 'EXCLUSIVE', the following command can be run:-

alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

Note* that the database must be stopped and started for this parameter to take effect.

b) Check whether the password file exists.

The location of the password file is $ORACLE_HOME/dbs (on unix systems) or $ORACLE_HOME/database (on windows). If the password file does not exist, create one as follows:-

Unix:
$ orapwd file=$ORACLE_HOME/dbs/orapw<$ORACLE_SID> password=sys entries=5

eg.
orapwd file=/u01/orabase/11db/dbs/orapwtestdb password=sys entries=5

Windows:
C:\> orapwd file=%ORACLE_HOME%\database\pwd<%ORACLE_SID%>.ora password= sys entries=5

eg.
orapwd file=d:\10204\database\PWDtestdb.ora password=sys

Note* The password will be "sys" in the above examples

c) Confirm whether the SYS user has been granted the SYSDBA role.  To do this check the v$pwfile_users view as below:-

select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

If SYSDBA does not show as 'TRUE' , log in as SYS as SYSOPER and grant 'SYSDBA' to SYS.   If no users are returned, the password file will need to be created as per point 3b) above.
4) Check whether it's possible to make a bequeath connection without using the tnsalias - eg.

$ORACLE_HOME/bin>sqlplus sys/<password> as sysdba

If the message "ORA-12162: TNS:net service name is incorrectly specified" is returned, it suggests that the ORACLE_SID has not been set.  If an error such as ORA-12154 is returned, see note:756076.1 "EMCA Fails With The Error 'Failed to unlock all EM-related accounts' when Creating DBConsole" .  If the message  "Connected to an idle instance"  is returned after logging into sqlplus (but it is possible to login to the database using an alias), it suggests that the ORACLE_SID is set incorrectly.

Tip* To check what the ORACLE_SID is set to do:-
echo $ORACLE_SID (unix)
set ORACLE_SID (windows)

5) Unix only:-

a) Check permissions on the oracle executable, it should be as follows :

$ cd $ORACLE_HOME/bin
$ ls -l oracle

-rwsr-s--x 1 oracle oinstall 210824714 Sep 17 2010 oracle

If the permissions on the oracle executable are wrong, they can be changed using 'chmod 6751 oracle'

b) Check that the user running the emca is the same user as created the database. The user is 'oracle' in this example.
c) Check that this user belongs to the dba group - eg.

id oracle

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

d) When entering the name of the database in the emca dialogue, check that the correct case (upper or lower) has been used. To do this:-

ps -ef | grep pmon
(this should show the pmon process for all running databases on the machine). Identify this particular database.  For example if the output of the above grep shows:-
oracle 3451 1 0 Sep14 ? 00:04:40 ora_pmon_v11203
oracle 4157 4107 0 10:43 pts/2 00:00:00 grep pmon
oracle 14844 1 0 Sep13 ? 00:05:10 ora_pmon_cloudrep
oracle 29339 1 0 Oct10 ? 00:00:07 ora_pmon_test                <--> This is the database that is being configured by emca
The pmon process shows ora_pmon_test so the "Database SID" as entered in the EMCA dialogue should be "test" (lower case)
oracle@machine1 admin]$ emca -config dbcontrol db -repos create
STARTED EMCA at Oct 11, 2012 10:37:55 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: test      <----specify the same case here
Listener port number: 1541
Listener ORACLE_HOME [ /oracle/orabase/11203hometwo ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:

The case entered here, should also match the case of the instance used by the listener.  This can be seen in the output of status <listenerName> in lsnrctl.  For example:-
Ouput from a listener where the sid is statically specified
LSNRCTL> LSNRCTL> status listenertest
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=machine1.uk.oracle.com)(PORT=1541)))
STATUS of the LISTENER
------------------------
Alias listenertest
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 11-OCT-2012 10:30:09
Uptime 0 days 0 hr. 14 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/orabase/11203hometwo/network/admin/listener.ora
Listener Log File /oracle/orabase/diag/tnslsnr/rbridden-pc/listenertest/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine1.uk.oracle.com)(PORT=1541)))
Services Summary...
Service "test.machine1.uk.oracle.com" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...     <--  The SID specified in the EMCA should be the same case as this.
The command completed successfully
Output from a listener where the sid is dynamically registered
LSNRCTL> status listenerdyn
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=machine2.oracle.com)(PORT=1542)))
STATUS of the LISTENER
------------------------
Alias listenerdyn
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 11-OCT-2012 10:52:25
Uptime 0 days 0 hr. 2 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/orabase/11203/network/admin/listener.ora
Listener Log File /oracle/orabase/diag/tnslsnr/machine2/listenerdyn/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=machine2.oracle.com)(PORT=1542)))
Services Summary...
Service "prodXDB.machine2.uk.oracle.com" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "production.machine2.uk.oracle.com" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...    <--  The SID specified in the EMCA should be the same case as this.
The command completed successfully

e) If the $ORACLE_HOME is a symbolic link, then the symbolic link needs to be removed.
See note:550484.1  point 5.

6) Windows only:-

a) Check that the "Log on as" account, which is specified in the services, is the same for the database and listener. Usually this will be set to "Local System Account", but if it is different, the listener and the database (and the susbsequent dbconsole service) must all use the same account.

b) Check that the user who is creating the dbconsole, is a member of the oracle_dba group.

for example:-

control panel/administrative tools/computer management/local users and groups/groups/ora_dba (double click to see the members of that group)

c) For dbconsole 11.2.0.1 and 11.2.0.2 check whether the %ORACLE_HOME%/network/admin/sqlnet.ora (or %TNS_ADMIN%/sqlnet.ora)  contains the entry:-

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If not, add this entry.   See note:1332546.1  for more information on this.   This is currently logged as unpublished bug 11740099

7) Check that the %ORACLE_HOME%/bin/emca.bat (windows) or $ORACLE_HOME/bin/emca script contains the correct ORACLE_HOME.  If the home is not set correctly here, take a backup of the emca file, then amend so that the correct home is reflected.

eg. emca on unix will show something like:-

#!/bin/sh
ORACLE_HOME="/u01/112dboraclebase/112db"
PLATFORM="Linux"
export ORACLE_HOME

emca.bat on windows will show something like:-

@echo off

setlocal

set OH=D:\10204

8) If any of  the changes listed above have been made, repeat step 3 and check that it is now possible to make the remote SYSDBA connection from sql*plus:-

$ORACLE_HOME/bin>sqlplus sys/<password>@<alias> as sysdba

If this is now successful, drop the 'failed' dbconsole configuration using:

emca -deconfig dbcontrol db -repos drop

and create a fresh dbconsole using:-

emca -config dbcontrol db -repos create

 

Thanks 

Srini