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