Windows – Oracle 11g: LRM-00109 followed by ORA-01078 when using PFILE created by SPFILE

I know this is long, but please bear with me – I think the background info may be important.

OS: Windows Server 2008 R2 64-bit

DB: Oracle 11g 64-bit Enterprise Edition

Yesterday evening we had to restart the host OS in order to finalize the installation of a Windows component. Upon rebooting, I noticed that the while the Oracle Listener had restarted, the DB instance had not – which was unusual, as it was set to automatic and there's only one instance.

So, figuring that something must have interfered with the startup, I went and:

C:\Users\digitalnoise>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 16 12:14:29 2013

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

Connected to an idle instance.

SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>

And immediately alarm bells began ringing, because db_recovery_file_dest is configured to point to a SAN mounted as an iSCSI drive on F:\. So I hop into Windows Explorer, figuring that I'm going to find the drive missing – it's not, however all is not right. F:\ is now showing a completely different set of files – files that were supposed to be deleted in a format before we began using it for FlashRecovery – instead of the FlashRecovery directory and associated files.

I really start to have heartburn and fire off an email to our Server IT people, who are of course out of the building at this time. This morning I get a reply stating that they think the MBR on that drive got corrupted, and so the drive is now handing the old MBR to the OS and really screwing things up. They're going to have to manually overwrite the MBR and slow format the drive. Thankfully this DB was not in production yet, so the loss of the log files isn't a huge problem, but what happens next is.

Since I can't start the DB in mount mode:

SQL> startup mount
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01263: Name given for file destination directory is invalid
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 2) The system cannot find the file specified.

I figure that I need to create a pfile from the spfile and change the db_recovery_file_dest parameter and use the pfile to start the DB instance, so:

SQL> create pfile='myinit.ora' from spfile;

File created.

I go and hunt down the pfile that was created, and open it up and think to myself: "self, this looks kind of odd, but…":

atsm.__db_cache_size=7918845952
atsm.__java_pool_size=33554432
atsm.__large_pool_size=67108864
atsm.__oracle_base='E:\app\<user who installed oracle>'#ORACLE_BASE set from environment
atsm.__pga_aggregate_target=5905580032
atsm.__sga_target=9563013120
atsm.__shared_io_pool_size=0
atsm.__shared_pool_size=1409286144
atsm.__streams_pool_size=33554432
*.audit_file_dest='E:\app\<user who installed oracle>\admin\ATSM\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='E:\oracle\oradata\ATSM\ATSM\control01.ctl','F:\FlashRecoveryArea\ATSM\control02.ctl'
*.db_block_size=8192
*.db_domain='<domain.com>'
*.db_name='ATSM'
*.db_recovery_file_dest='F:\FlashRecoveryArea'
*.db_recovery_file_dest_size=5242880000
*.diagnostic_dest='E:\app\<user who installed oracle>'
*.dispatchers='(protocol=TCP)'
*.memory_target=15457058816
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_servers=1
*.undo_tablespace='UNDOTBS1'

I delete the *.db_recovery_file_dest='F:\FlashRecoveryArea' line and ,'F:\FlashRecoveryArea\ATSM\control02.ctl' from the *.control_files parameter since it also points to the missing path/file, save my file and then do:

SQL> startup pfile='myinit.ora';
LRM-00109: could not open parameter file 'myinit.ora'
ORA-01078: failure in processing system parameters
SQL>

And get both LRM-00109 and ORA-01078. So I think maybe – just maybe – I screwed up the edits to the pfile – so I'll just try exporting it, and then using it – if there's nothing wrong with the export, then I should just get the error about being able to find the path, right? Wrong:

SQL> create pfile from spfile;

File created.

I check, and the default file name it used is INITatsm.ORA, so:

SQL> startup pfile='INITatsm.ORA';
LRM-00109: could not open parameter file 'INITatsm.ORA'
ORA-01078: failure in processing system parameters
SQL>

So Oracle is exporting a pfile from it's spfile that isn't valid.

And that's where I'm at. I did find what I think is the "default" pfile that is created when the database is installed, and I copied it and attempted to use it in the startup pfile= command, but I get the same exact Oracle errors.

Has anyone encountered this before? Do you have any suggestions? We really don't want to have to dump the entire install – which is probably a bit extreme – as the consultant who set it up is no longer with us and we cannot get the funding to hire in another one at this point.

Edited to add: I forgot to mention that I did try giving the full path to the pfile that I want to use, but as you can see, I get the exact same error messages:

SQL> startup pfile='E:\app\<UID_W_INST_ORCL>\product\11.2.0\dbhome_1\database\INITatsm.ORA';
LRM-00109: could not open parameter file 'E:\app\<UID_W_INST_ORCL>\product\11.2.0\dbhome_1\database\INITatsm.ORA'
ORA-01078: failure in processing system parameters