PFILE, SPFILE

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

  

Info

SQL> show parameters spfile
SQL> show parameters pfile

/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

May show nothing, if the database was started using initXXX.ora

SQL> SHOW SPPARAMETERS

Show location of the initialization settings (spfile of pfile)

SQL> select decode(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM v$parameter where name = 'spfile';

Init File Type
------------------------
SPFILE

Export values of initialization parameters in effect for the current session

Great case to export initialization parameters from the running database, when no parameters were saved in the database

SQL>
spool /tmp/parameters.txt
set linesize 200;
set pagesize 0;
col NAME format a40;
col VALUE format a60;
select NAME, VALUE from V$PARAMETER;
spool off

Values of initialization parameters in effect for the instance

SQL> select NAME, VALUE from V$SYSTEM_PARAMETER;

RMAN backup

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY '/tmp/control01.ctl';

RMAN> BACKUP CURRENT CONTROLFILE TO '/tmp/controlfile_copy.ctl';
RMAN> BACKUP SPFILE;

SQL backup

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL1.rtc';

Read a script of the controlfile creation

$ cat /u01/app/oracle/product/11.2.0/db_1/dbs/initORCL1.rtc

Export initialization setting from spfile to pfile

SQL> CREATE PFILE = '/home/oracle/pfileORCL.txt' from SPFILE;

$ vi /home/oracle/pfileORCL.txt

SQL> create pfile from spfile;

Import initialization setting from pfile to spfile

SQL> create spfile from pfile='/home/oracle/pfileORCL.txt';

ORACLE_HOME\database\SPFILEORCL.ORA (default location of the binary spfile file, we may open and edit only pfile INITorcl.ORA)

SQL> create spfile from pfile;

Custom database startup, from the pfile and create spfile from it

SQL> startup nomount pfile=/tmp/backups/ORCL12/PARAMETERFILE/pfile.txt

SQL> create spfile from memory;

SQL> shutdown immediate;
SQL> startup

Change INITialization parameters example, applies after db restart

SQL> alter system set db_files = 700 scope = spfile;

Newsletter

Enter your email to join our mailing list

Quick Contact