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 11.1.0.7.0 - 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 11.1.0.7.0 - 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.

SQL>

Similarly, we can create SPFILE from memory using the below mentioned command.
CREATE SPFILE FROM MEMORY;

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

0 comments:

Post a Comment

 

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