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 18.104.22.168.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='22.214.171.124.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
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
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