Wednesday, September 2, 2009

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

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;
-------- ------- -----------------------------------------
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’

Satishbabu Gunukula


  1. Up to Oracle 8i, the initialization parameters were stored in the init.ora file. If a paramter has been dyanammically changed, the changement was lost after the next startup of the database unless it was also changed in the init.ora. SPFILEs ease this as changements made with alter system can be stored and be used for the next startup of the database.

    4gb micro sd karte

  2. Kuchi Jewels is a project of Gem & Gems which is a leading exporter since 2005 to onwards in all over the world. Our company has experienced, educated and motivated staff. Our main goal is to meet the international standard B2C and B2B export target with competitive prices and high quality products. birthstone necklace wholesale france , birthstone necklace wholesale uk