Pages

Thursday, July 18, 2013

Create SPFILE from PFILE

PFILE: traditional init.ora file, changes via eg. vi
SPFILE: binary file, changes via SQL 'alter system...'

To create a spfile login to your database as sysdba locally:

$ sqlplus "sys/oracle as sysdba"

Run the following SQL statement to create a binary spfile from a text pfile: 

SQL> create spfile='/u01/product/11.2.0/db_1/dbs/spfileDB.ora' from pfile='/u01/product/11.2.0/db_1/dbs/initDB.ora';
File created.


Check that the spfile was created:

SQL> host
$ ls -la /u01/product/11.2.0/db_1/dbs/spfileDB.ora
-rw-r-----   1 oracle   dba         3584 Jul 18 11:04 /.../spfileDB.ora


Next move the pfile to a different location and rename it:

$ mv /u01/product/11.2.0/db_1/dbs/initDB.ora /u01/initDBOLD.ora
$ exit


Restart the database to check if the database starts with the newly created spfile:

SQL> shutdown immediate;
...
ORACLE instance shut down.

SQL> startup
...
Database opened.


Finally change the parameter max_dump_file_size to check if the values can be changed and to check if the values will be stored persistent.
First get the current value for max_dump_file_size:

SQL> select value from v$parameter where name = 'max_dump_file_size';
VALUE
-----
50M


Change it to eg. 60MB and recheck it:

SQL> alter system set max_dump_file_size = '60M';
System altered.

SQL> select value from v$parameter where name = 'max_dump_file_size';
VALUE
-----
60M


Restart your database one more time and check the value for max_dump_file_size:

SQL> shutdown immediate;
...
ORACLE instance shut down.

SQL> startup
...
Database opened.

SQL> select value from v$parameter where name = 'max_dump_file_size';
VALUE
-----
60M


The value for max_dump_file_size is the same.