Oracle: Database will not startup due to invalid parameter

Most DBA’s now have to take care of multiple database platforms and they all have their own set of commands. One of my Oracle databases would not startup due to an invalid parameter in the SPFILE. Read on how I was able to get the database back up and running.

One of my servers was rebooted during maintenance and the database would not startup. Below I am trying to startup the database

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>cd\

C:>set ORACLE_SID=prod

C:>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 7 09:35:26 2022

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORA-16032: parameter LOG_ARCHIVE_DEST_2 destination string cannot be translated
ORA-09291: sksachk: invalid device specified for archive destination
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 53) The network path was not found.
SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

According to the error message, it could not find the LOG_ARCHIVE_DEST_2 location.

To investigate this, I need to create a text version of the SPFILE. From the SQLPLUS prompt I type in the following CREATE PFILE command. This creates a file we can modify and startup the database with. The #2 below is just the line number, no need to type it in. Your SPFILE would be named whatever your database is called, mine is called PROD.

SQL> create pfile='D:\oracle\product\10.2.0\db_1\database\jimpfile.ora'
2 from spfile='D:\oracle\product\10.2.0\db_1\database\spfileprod.ora';

File created.

Now if we look in the directory, we see a file called JIMPFILE.ora got created.

I can now open the JIMPFILE.ora file in notepad and see what the settings are.

Aha! I see what the problem is. The server XYZBACKUPS has been retired on our network so the database can no longer get to it. To remedy this, I can remove the entire line in yellow or point to a new location. For now I will just remove it since we already have a working log archive destination.

Below I remove the log_archive_dest_2 and press save to save the file.

Now we can startup the database using the PFILE that we created. This will bypass the SPFILE.

SQL> startup pfile='D:\oracle\product\10.2.0\db_1\database\jimpfile.ora';
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 1293888 bytes
Variable Size 545259968 bytes
Database Buffers 381681664 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.

Success!!! The database is open and now the business can do their work.

Before I tell the business the database is available, I want to create a new SPFILE with the updated parameters.

For this, I use the CREATE SPFILE command.

SQL> create spfile='D:\oracle\product\10.2.0\db_1\database\spfileprod.ora'

from pfile='D:\oracle\product\10.2.0\db_1\database\jimpfile.ora';

File created.

The new file is created. Now just for completeness I shutdown the database and restart it so I know everything is working as it should.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 1293888 bytes
Variable Size 545259968 bytes
Database Buffers 381681664 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>

Now you can alert the business and tell them database is available.

To read more about Oracle and its SPFILE follow this link. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/creating-and-configuring-an-oracle-database.html#GUID-7302C60F-E96E-4202-AC81-25A6C93EEFA3