advertisements
_____________________________________________________________________________________________________________________
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"
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment