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.
Resolved
None found
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.
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.
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.
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.
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.
My Oracle Support search for ORA-01994: GRANT failed: password file missing or disabled revealed the following hits:
Raised SR 3-6633000681