Create PFILE SPFILE from memory 11g New Feature

Oracle 11g provides a new feature to create PFILE or SPFILE using the current values in the Oracle Memory (active Instance). This will be useful if you delete the spfile or pfile by mistake. J see examples below.

$ sqlplus "/ as sysdba"

SQL*Plus: Release - Production on Wed Jun 29 01:32:04 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
23:06:46 SQL> l
  1* create pfile from memory
23:06:48 SQL> /

File created.

In the above example the pfile will create in the $ORACLE_HOME/dbs directory. You can specify the location while creating the pfile.

SQL> create pfile='/home/oracle/scott/initprod9.ora' from memory;

File created.


Similarly, we can create SPFILE from memory using the below mentioned command.

You need to have SYSDBA or SYSOPER role to execute above statements. You can execute this statement either before or after instance startup. In a RAC database, the create file will contain the parameter settings for each instance.
The Source of the Memory is the V$SYSTEM_PARAMETER4 dictionary view. V$SYSTEM_PARAMETER4 returns the rows for all parameter which have been modified internally (e.g. self_tuning), including the parameters which are double-underscored(Paraphrased from Oracle Source code).

Source code can get it using this query

select num,name,type,display_value,update_comment from v$system_parameter4 order by lower(name),ordinal

It is a new feature beginning in Oracle 11gR1.
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google