Got ORA-01031 when logging on remotely using SYS AS SYSDBA

Overview

After I Upgraded Oracle Personal Edition from 11.2.0.1.0 to 11.2.0.3.0 on Windows XP Pro , I got the ORA-01031 : insufficient privileges when I tried to connect using SYS@PRODSRVR AS SYSDBA .

Status

Resolved

Workaround

None found

Resolution

The problem was caused by I/O errors on the disk drive where the Oracle Home was installed.

The solution is to get a new disk drive.

Diagnosis

Summary

I found that the SYS user lost the SYSDBA privilege among others.

When I try to grant SYSDBA to SYS , I get the ORA-01994 : GRANT failed: password file missing or disabled message.

Initial Search of My Oracle Support

There did not appear to be any hits at My Oracle Support , but I was pointed to the troubleshooting guide, Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [ID 233223.1] .

I found ORA-1031 When Connecting Remotely AS SYSDBA [ID 470832.1] from using the keyword search:

"connect as sysdba" ora-01031

When I run the following command:

select * from v$pwfile_users;

I get no rows selected.

The system parameter, remote_login_passwordfile , is set to EXCLUSIVE .

The symptoms did not match 470832.1 exactly.

Recreating the Password File

However, when I looked at the database subdirectory of the new ORACLE_HOME, I find the following files:

 Directory of I:\app\Douglas\product\11.2.0\dbhome_1\database

04/01/2013  09:51 PM    <DIR>          .
04/01/2013  09:51 PM    <DIR>          ..
02/01/2013  05:01 PM    <DIR>          archive
04/01/2013  08:39 PM             2,048 hc_prodsrvr.dat
04/01/2013  08:38 PM               889 initPRODSRVR.ora
22/12/2005  04:07 AM            31,744 oradba.exe
04/01/2013  08:38 PM             1,536 PWDPRODSRVR.ora
04/01/2013  09:51 PM            12,288 SPFILEPRODSRVR.ORA
               5 File(s)         48,505 bytes

I ran the following command to recreate the Oracle password file:

orapwd file=PWDPRODSRVR.ora entries=20 force=y nosysdba=n

I was prompted for the SYS password as follows:

Enter password for SYS:

This did not resolve the problem.

Grant Priviliges Explicitly

Tried Using SYSTEM

I used the SYSTEM user to run the following commands:

GRANT SYSOPER TO "SYS" ;

But, I got he following errors:

Error starting at line 2 in command:
GRANT SYSDBA TO "SYS" 
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

GRANT Using BEQUEATH Interface

I:\app\Douglas\product\11.2.0\dbhome_1\database>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 5 21:44:53 2013

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


Connected to:
Personal Oracle Database 11g Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 87) The parameter is incorrect.
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 87) The parameter is incorrect.

Copied the old password file from the old Oracle Home. Still got the same result.

Investigate ORA-01994

My Oracle Support search for ORA-01994 : GRANT failed: password file missing or disabled revealed the following hits:

Raised SR

Raised SR 3-6633000681