Friday, January 20, 2012

ORA-01994: GRANT failed: password file missing or disabled.

You try to grant sysdba privilege to a user. You can see below error
ORA-01994: GRANT failed: password file missing or disabled.

User doesn’t have sysdba privilege
Solutions
1.      Log  on to the server

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=10

2.      Grant Sysdba privilege to the user 
      grant sysdba to <user>

3.      Check v$pwfile_users to verify the prvilege

4.       Check owner,group  of orapw$ORACLE_SID

5.      If it is not   to oracle and  dba , then change the ownership
chown oracle:dba orapw$ORACLE_SID

Friday, January 13, 2012

ORA-27303: additional information: requested interface eth1 mixed MTU configured 9000 1500

When you try install RAC. You can see error when ASM instance started

ASM1> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 24 21:57:34 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:if MTU failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcini2
ORA-27303: additional information: requested interface eth1 mixed MTU configured 9000 1500
. Check output from ifconfig command
SQL>


It looks like 2 network devices (eth1 & eth2) are configured as cluster interconnect.

>/home/oracle/oracle/product/11.1.0/crs/bin> ./oifcfg getif
eth0 10.*.*.* global public
eth2 10.*.*.* global cluster_interconnect
eth1 10.*.*.* global cluster_interconnect

2. eth1 and eth2 are configured with different MTU sizes.
eth1 MTU=1500
eth2 MTU=9000

ACTION PLAN
===========
1. If your goal is to use both eth1 and eth2 for cluster interconnect, please set them
up with identical MTU sizes.

2. If only 1 interface is needed for cluster interconnect, please delete one of them.
$ cd <crs home>/bin
$ ./oifcfg delif -global <interface to be deleted>

Verify changes:
$ ./oifcfg getif



Sunday, January 8, 2012

Connection To RMAN Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

Symptoms
Connecting to an auxiliary instance via RMAN fails with ORA-01031 errors. The same is seen when properly testing a connection via SQL*Plus to the auxiliary instance.

RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-01031: INSUFFICIENT PRIVILEGES
Changes
You may have tried to add the connecting user to the password file but that has failed:
SQL> select * from v$pwfile_users;
No rows
SQL>  grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01109: database not open


Because the auxiliary is nomounted, the addition of a user to the password file for the auxiliary is not allowed, nor does it resolve this problem.

You have verified the same problem happens in SQL*Plus as does RMAN.  For example, if in RMAN you are issuing:
 
RMAN>  connect auxiliary <user/password>@<auxiliary_db>


then for testing SQL*Plus you'd have to issue this from the operating system prompt:
 
% sqlplus "<user/password>@<auxiliary_db> as sysdba"


also shows the ORA-1031 error.

(connecting in two steps to the auxiliary is not a valid test).
Cause
There may be a mismatch in the "case" of characters between these items on the host where the auxiliary database resides:

1)  The entry in the listener.ora file for service_name for the auxiliary instance.
2)  The entry for db_name in the init<sid>.ora file for the auxiliary instance.
3)  The naming of the password file.
4)  The $ORACLE_SID for the auxiliary instance.

If all four items have matching case characters, you may find the errors connecting remotely via the password file are resolved.
Solution
Set the reference to service_name in the listener.ora, db_name in the init<sid>.ora, the password file name itself, and the $ORACLE_SID to all uppercase.

Listener example:

   (DESCRIPTION=
   (ADDRESS=...)
   (ADDRESS=...)
   (CONNECT_DATA=
   (SERVICE_NAME=SALES.US.ACME.COM)))

The init<sid>.ora file:

  db_name=SALES

The password file:

  orapwSALES.ora  or orapwSALES

The $ORACLE_SID:

  $ ORACLE_SID=SALES; export ORACLE_SID