SPFILE issues by example

==============================
SPFILE
==============================
SPFILE stands for Server Parameters File.
SPFILE is a binary file that exist only on the server and is used to start up the database.

==============================
SPFILE and PFILE priority upon Startup
==============================
Upon Startup, Oracle would use the SPFILE from default location, with default name.
$ORACLE_HOME/dbs/spfile<SID>.ora

If this file is not present, Oracle would use the init.ora file:
$ORACLE_HOME/dbs/init<SID>.ora

For Oracle 11.2

INIT.ORA location: /software/oracle/112/dbs
SPFILE location:   /software/oracle/112/dbs

PFILE location:    /software/oracle/admin/igt/pfile

==============================
SPFILE location
==============================
Default location for spfile and for init_ora is ${ORACLE_HOME}/dbs

It would actually be specified inside ${ORACLE_HOME}/dbs/init<SID>.ora
less init<SID>.ora

spfile='/software/oracle/admin/igt/pfile/spfileigt.ora'

The SPFILE can be placed anywhere as long as the path is specified in init.ora file.

If SPFILE is created in a non default location, first need to create a traditional parameter file containing only the following single line:
SPFILE=/full/path/to/spfile.ora

For example:
>less /software/oracle/111/dbs/initorainst.ora

SPFILE=/software/oracle/admin/orainst/pfile/spfileorainst.ora


==============================
SPFILE in RAC
==============================
All instances in an Real Application Clusters environment must use the same server parameter file. 
When permitted, individual instances can have different settings of the same parameter within this one file. 
The syntax: SID.parameter = value, where SID is the instance identifier.

==============================
CREATE PFILE
==============================
PFILE would be located here: /software/oracle/admin/igt/pfile

It is possible to create PFILE in a several ways.

Best Option: CREATE PFILE FROM SPFILE.
CREATE PFILE command exports binary SPFILE into a text initialization parameter file. CREATE PFILE = '$ORACLE_HOME/dbs/orainst_init_ver01.ora' 
 FROM SPFILE = 'spfile.ora';

Alternative I.

What if CREATE PFILE FROM SPFILE fails?

There is an option CREATE PFILE FROM MEMORY.

CREATE PFILE ='$ORACLE_HOME/dbs/mem_pfile.ora' 

FROM MEMORY;

Alternative II.
use the strings command to transfer binary file to a text file.
cd $ORACLE_HOME/dbs
strings spfile_name > temp_pfile.ora

strings command comment
By default strings command will be looking for sequences of at least 4 printable characters, that are terminated by a NULL character. 
To change the total number of characters that needs to be searched in the binary files, use option -n.
For example:
strings -n 1 spfile_name

strings reference

Alternative III.

What if CREATE PFILE FROM MEMORY fails?

It possible to manually edit the pfile:

- open the alert_<sid> log.

- locate the  last successful DB startup.

- This section of the file shows all non-default parameters & their values.

- Copy/Paste these lines to pfile.

==============================
CREATE SPFILE 
==============================
CREATE SPFILE FROM PFILE = '$ORACLE_HOME/work/orainst_init.ora';

==============================
Oracle startup from PFILE
==============================
init.ora file would be located here:
${ORACLE_HOME}/dbs/

This is an example of creating a text pfile, and then string Oracle from that text pfile.

Create PFILE from SPFILE, as described above.

SHUTDOWN;

vi init<SID>.ora //Edit the text parameters file.
#spfile='/software/oracle/admin/igt/pfile/spfileigt.ora'



Rename the spfile, so it would not be used upon Oracle startup.


/software/oracle/admin/igt/pfile>% mv spfileigt.ora spfileigt.ora_orig_bak


STARTUP PFILE=
'/home/oracle/product/10.1.0/db1/dbs/init<SID>.ora';


CREATE SPFILE FROM PFILE;

===========================================
Database Won’t Start due to invalid parameter in spfile.
===========================================
The flow:
A. Create pfile from SPFILE
B. Startup Oracle using PFILE
C. Create SPFILE
D. Startup Oracle using SPFILE

Step 1 - Shutdown the database, if not already down, and backup existing SPFILE and PFILE files.
SHUTDOWN IMMEDIATE;

Step 2. - Find the pfile
Default path is for pfile is:
$ORACLE_HOME/dbs/init<SID>.ora

If this file not found, continue to Step 3:

Step 3. - Generate text file from binary SPFILE
See above section, "CREATE PFILE"
CREATE PFILE = '$ORACLE_HOME/dbs/orainst_init_ver01.ora' 
FROM SPFILE = 'spfile.ora';

Step 4.
Edit the temp_pfile.ora, to resolve the issue that prevented Oracle from starting.

Step 5.
Startup Oracle by:
STARTUP PFILE=$ORACLE_HOME/dbs/temp_pfile.ora

SQL>STARTUP PFILE=/software/oracle/admin/igt/pfile/spfileigt.ora_strings
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size            1291845632 bytes
Database Buffers          838860800 bytes
Redo Buffers                7876608 bytes
Database mounted.

Database opened.

Step 6
Create a valid SPFILE
CREATE SPFILE FROM PFILE;

spfileigt.ora File created under /software/oracle/193/dbs/

=======================================
Changing SYSTEM Parameter with SCOPE=<SCOPE>
===========================================
To make SYSTEM parameter changes persistent, use SCOPE=<PARAMETER>.
For example:
ALTER SYSTEM SET MAX_SEESIONS=25 SCOPE=SPFILE;

The syntax:
ALTER SYSTEM SET <parameter>=<value> 
                  SCOPE=<memory/spfile/both> 
                  COMMENT=<'comments'> 
                  DEFERRED
                  SID=<sid,*>

SCOPE options:

MEMORY - Changes are active for the current instance only and are lost on restart.

SPFILE - Changes are stored in the SPFILE and are activated on the next startup, the presently active instance is not affected. 
                    This is the way to change static parameters.

BOTH  -     Default.
                  Changes are effective immediately for the present instance and are stored in SPFILE for future startups. 

COMMENT Optional. Free text.

DEFERRED Used to set parameter values for future connecting sessions. 
                      Currently active sessions are not affected and they retain the 
                      old parameter value. 
                      The option is required for parameters that have the 
                      ISSYS_MODIFIABLE column value in V$PARAMETER set to 'DEFERRED'. 
                      It is optional if the ISSYS_MODIFIABLE value is set to 'IMMEDIATE'. 
                      For static parameters, this option is not allowed and cannot be specified.


SID Only for Real Application Clusters. 
          Setting this to a specific SID value changes the parameter value for that particular instance only. 

==============================
How to see contents of SPFILE
==============================

Use strings Linux command

oracle@isr-sdc-1-cgw-1:/software/oracle/admin/igt/pfile>% strings spfileigt.ora
igt.__db_cache_size=201326592
igt.__java_pool_size=4194304
igt.__large_pool_size=4194304
igt.__shared_pool_size=226492416
*.aq_tm_processes=1
*.archive_lag_target=1800
*.background_dump_dest='/software/oracle/admin/igt/bdump'
*.compatible='10.1.0.5.0'
*.control_files='/oracle_db/db1/db_igt/ora_control_01.ctl','/oracle_db/db1/db_igt/ora_control_02.ctl','/oracle_db/db1/db_igt/ora_control_03.ctl'
*.core_dump_dest='/software/oracle/admin/igt/cdump'
*.db_block_size=8192
*.db_cache_size=167772160
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=33554432
*.db_name='igt'
*.disk_asynch_io=true
*.fast_start_mttr_target=180
*.filesystemio_options='asynch'
*.instance_name='igt'
*.job_queue_processes=30
*.log_archive_dest_1='location=/oracle_db/db3/db_igt/arch'
*.log_archive_format='arch%T_%s_%r.arc'
*.log_buffer=157286400
*.nls_length_semantics='char'
*.open_cursors=300
*.open_links=12
*.os_authent_prefix=''
*.pga_aggregate_target=125829120
*.processes=200
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.SESSION_CACHED_CURSORS=100
*.sga_max_size=943718400
*.sga_target=629145600
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/software/oracle/admin/igt/udump'
*.utl_file_dir='/software/oracle/admin/igt/utl_file','/software/oracle/admin/igt/utl_file/tap3/files','/software/oracle/admin/igt/utl_file/tap3','/software/oracle/admin/igt/utl_file/tap3/files/mobilkomfiles'
oracle@isr-sdc-1-cgw-1:/software/oracle/admin/igt/pfile>% strings spfileigt.ora_20151208
igt.__db_cache_size=201326592
igt.__java_pool_size=4194304
igt.__large_pool_size=4194304
igt.__shared_pool_size=226492416
*.aq_tm_processes=1
*.archive_lag_target=1800
*.background_dump_dest='/software/oracle/admin/igt/bdump'
*.compatible='10.1.0.5.0'
*.control_files='/oracle_db/db1/db_igt/ora_control_01.ctl','/oracle_db/db1/db_igt/ora_control_02.ctl','/oracle_db/db1/db_igt/ora_control_03.ctl'
*.core_dump_dest='/software/oracle/admin/igt/cdump'
*.db_block_size=8192
*.db_cache_size=167772160
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=33554432
*.db_name='igt'
*.disk_asynch_io=true
*.fast_start_mttr_target=180
*.filesystemio_options='asynch'
*.instance_name='igt'
*.job_queue_processes=30
*.log_archive_dest_1='location=/oracle_db/db3/db_igt/arch'
*.log_archive_format='arch%T_%s_%r.arc'
*.log_buffer=157286400
*.nls_length_semantics='char'
*.open_cursors=300
*.open_links=12
*.os_authent_prefix=''
*.pga_aggregate_target=125829120
*.processes=200
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.SESSION_CACHED_CURSORS=100
*.sga_max_size=943718400
*.sga_target=629145600
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/software/oracle/admin/igt/udump'

*.utl_file_dir='/software/oracle/admin/igt/utl_file','/software/oracle/admin/igt/utl_file/tap3/files','/software/oracle/admin/igt/utl_file/tap3','/software/oracle/admin/igt/utl_file/tap3/files/mobilkomfiles'

Create SPFILE and PFILE from memory

CREATE PFILE ='/software/oracle/122/dbs/pfile_from_memory.ora' FROM MEMORY;

CREATE SPFILE = '/software/oracle/122/dbs/spfile_from_memory.ora' FROM MEMORY;

-rw-r--r-- 1 oracle dba     9677 Jul  1 14:11 pfile_from_memory.ora

-rw-r----- 1 oracle dba    22016 Jul  1 14:12 spfile_from_memory.ora

=========================

Create PFILE is giving error ORA-01565: error in identifying file

=========================

SQL> CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora.20210805_A' FROM SPFILE;

CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora.20210805_A' FROM SPFILE

*

ERROR at line 1:

ORA-01565: error in identifying file

'/software/oracle/admin/igt/pfile/spfileigt.ora'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SELECT name, value FROM V$PARAMETER WHERE name = 'spfile'

NAME                 VALUE

-------------------- ------------------------------------------------

spfile               /software/oracle/admin/igt/pfile/spfileigt.ora

The path in parameter spfile is not correct

Actual location of spfile is /software/oracle/112/dbs/spfileigt.ora

To work around this issue, specify exact path to spflie

CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora.20210805_A' FROM SPFILE='/software/oracle/112/dbs/spfileigt.ora';

File created.