Create spfile from pfile

Create spfile from pfile in Oracle:
Use the CREATE SPFILE statement to create a Server Parameter file from PFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the SPFILE example.

SQL> create spfile from pfile;
SQL> Create spfile='/tmp/spfileOradb.ora' from pfile;

Common Errors:
1. If you edit SPFILE or corrupted, you will receive below error (Don not edit spfile).
SQL> alter system set db_files=200 scope=spfile;
alter system set db_files=200 scope=spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

2. If spfile deleted accidentally, you will get below error during Database startup.
SQL> startup
ORA-01078: failure in processing system parameters

Solution: Recreate a binary SPFILE from PFILE.

Remove parameter from Spfile:
You can remove spfile parameter by using RESET command.

SQL> ALTER SYSTEM RESET session_cached_cursors SCOPE=spfile sid='SID*'

Create pfile from spfile:
Use the CREATE PFILE statement to create a Parameter file from SPFILE with SYSDBA/SYSOPER privilege. You can specify the file name and path in the create command. See the pfile example.

SQL> create pfile from spfile;
SQL> create pfile = '/tmp/initDB1.ora' from spfile;

Common Errors:
you will encounter this error while creating pfile/spfile.

ERROR at line 1:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory Additional information: 3

Reason:
1. If database is down and you are trying to create a pfile from spfile.
2. Providing wrong file names.

Change Oracle spfile Location :
If you want to change the spfile location, create a PFILE in the default location and in pfile specify the spfile parameter location to non-default location.

For ex: - PFILE contents look like below
spfile = “New_path”/spfileOradb.ora

Create spfile from pfile in RAC :
In order to use same spfile at startup each RAC instance uses its own pfile and that points to one shared spfile on shared storage. If you are using ASM the spfile then the spfile will be located in Shared ASM file system.

SQL > show parameter spfile;
NAME TYPE VALUE
-------- ------- -----------------------------------------
spfile string +DATA/testdb/spfiletestdb.ora

Use above commands to create spfile from pfile and pfile from spfile.

Common Errors in RAC:
You will receive below error when modifying the parameter SCOPE=memory using SID=’*’

SQL> alter system set sga_target=500m scope=memory SID=’*’;
alter system set sga_target=500m scope=memory
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Solution: - You need to modify the parameter individually on each instance using the SID=’Instance_Name’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/