Oracle: Create PFILE from SPFILE

I just ran into an error while trying to bounce a long running Oracle-DB:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
SVR4 Error: 2: No such file or directory

So there must be something wrong with that LOG_ARCHIVE_DEST. But where the heck is it pointing to?!

To get a parameterfile (PFILE), which is human readable as it’s in plain ASCII, one can use “CREATE PFILE FROM SPFILE” in sqlplus. That command will result in a pfile in $ORACLE_HOME/dbs where also the spfile resides. Until now I thought that I had to bring the instance into NOMOUNT-state with “STARTUP NOMOUNT” for beeing able to execute the given command, because the SPFILE is read just then. But…

SQL> startup nomount
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
SVR4 Error: 2: No such file or directory

So, no way to get the value of the incriminated parameter?

When the environmentvariable  “ORACLE_SID=” is set to the desired DB the instance seems to read the spfile from the default-location ($ORACLE_HOME/dbs) in the default-filenameformat (spfileSID.ora) – no matter if the DB is up or not. So one can connect to an idle instance and create a PFILE:

sqlplus '/ as sysdba';
SQL*Plus: Release 9.2.0.6.0 - Production on Mo Apr 12 17:05:39 2010
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.

As the process of creating a PFILE is just a translation of SPFILE to PFILE one even can connect to no instance at all (using only SQL-Plus) and create a PFILE from any given SPFILE while stating the absolute paths of each file:

sqlplus /nolog
SQL*Plus: Release 9.2.0.6.0 - Production on Mo Apr 12 17:41:29 2010
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> create pfile='/usr/people/oracle/initORGDB.ora' from spfile='/opt/oracle/orahome1/dbs/spfileORGDB.ora';
File created.


After getting the PFILE one can examine it, fix the error and startup the instance with the corrected PFILE – or create a SPFILE from that corrected PFILE right away and startup with the SPFILE.

Edit 2014-08-04:
At least with version 11.2.0.3.0 it is no longer possible to generate a pfile from spfile with “sqlplus /nolog”. To get the pfile we must get on the instance.

[oracle@testsrv ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mo Aug 4 14:12:42 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> create pfile='/app/oracle/temp/inittestdb.ora' from spfile='/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestdb.ora';
SP2-0640: Not connected
SQL> exit
Disconnected

[oracle@testsrv ~]$ export ORACLE_SID=testdb
[oracle@testsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 4 14:22:48 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile='/app/oracle/temp/testdb.ora' from spfile;
File created.
SQL>

Another way of getting the parameters is using the “strings”-command at the shell on the spfile (credits go to Frits Hoogland):

[oracle@testsrv ~]$ strings /app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestdb.ora
testdb.__db_cache_size=2969567232
testdb.__java_pool_size=16777216
testdb.__large_pool_size=16777216
testdb.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=2583691264
testdb.__sga_target=4798283776
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=1711276032
testdb.__streams_pool_size=33554432
*.audit_file_dest='/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr2/oracle/oradata/testdb
/control01.ctl','/app/oracle/fast_recovery_area/testdb/control02.ctl'
*.db_block_size=8192
*.db_domain='private.domain.com'
*.db_name='testdb'
*.db_recovery_file_dest='/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=161061273600
*.diagnostic_dest='/app/oracle'
*.log_archive_format='testdb_%t_%s_%r.arc'
*.memory_target=7368343552
*.nls_language='GERMAN'
*.nls_territory='GERMANY'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_ta
blespace='UNDOTBS1'
[oracle@testsrv ~]$

This command extracts all strings from the SPFILE. The result is almost what would be in a PFILE. We just have to care for occasional newlines that would break certain parameters (here: control_files, undo_tablespace) in two lines. When we fix this line-breaks we would end up with the exact PFILE-Content.

Advertisements

One Response to Oracle: Create PFILE from SPFILE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: