15 May, 2011

Getting all the instance parameters

A simple method (in 11g -- here tested in 11.2.0.1) to get all the database instance parameters.

Currently, I am using an SPFILE :
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Jul 7 2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 15 12:59 hc_orcl.dat
-rw-r----- 1 oracle oracle 2560 May 15 12:59 spfileorcl.ora
[oracle@localhost dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:27:26 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/oracle/produc
t/11.2.0/dbhome_1/dbs/spfileor
cl.ora
SQL>

If I create a PFILE, I have 25 lines of parameters :
(UPDATE : As suggested by Coskan, use "create pfile=/tmp/init.ora from spfile;" so as to not overwrite an existing pfile -- e.g. if it happens to be different from the current set of processes.)
SQL> create pfile from spfile;

File created.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls -ltr|tail
total 36
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL
-rw-r----- 1 oracle oracle 1536 Jul 7 2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 15 12:59 hc_orcl.dat
-rw-r----- 1 oracle oracle 2560 May 15 12:59 spfileorcl.ora
-rw-rw-r-- 1 oracle oracle 959 May 15 14:28 initorcl.ora
[oracle@localhost dbs]$ wc -l spfileorcl.ora
25 spfileorcl.ora
[oracle@localhost dbs]$ wc -l initorcl.ora
25 initorcl.ora
[oracle@localhost dbs]$


But what if I want to see all the "hidden" parameters ? I could query some view.

I could also do this :
(UPDATE : As suggested by Coskan, use "create pfile=/tmp/init.ora from memory;" so as to not overwrite an existing pfile -- e.g. if it happens to be different from the current set of processes.)
[oracle@localhost dbs]$ rm initorcl.ora
[oracle@localhost dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:30:07 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from memory;

File created.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ls -l initorcl.ora
-rw-rw-r-- 1 oracle oracle 5625 May 15 14:30 initorcl.ora
[oracle@localhost dbs]$ wc -l initorcl.ora
163 initorcl.ora
[oracle@localhost dbs]$

I now have 163 entries in my PFILE !

And these contain 137 parameters beginning with the "_" character :
[oracle@localhost dbs]$ strings -a initorcl.ora |grep '^\_' | wc -l
137
[oracle@localhost dbs]$


I can also create an SPFILE with all the parameters :
[oracle@localhost dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 14:36:38 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create spfile='spfileorcl.bck' from memory;

File created.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ wc -l spfileorcl.bck
164 spfileorcl.bck
[oracle@localhost dbs]$
[oracle@localhost dbs]$ strings -a spfileorcl.bck | grep '^\*\.\_' | wc -l
136
[oracle@localhost dbs]$


The "CREATE SPFILE || PFILE FROM MEMORY" is a new command in 11g.

UPDATE : You can still query the V$PARAMETER, V$SYSTEM_PARAMETER (and V$PARAMETER2, V$SYSTEM_PARAMETER2) views WHERE ISDEFAULT=TRUE to get the default values (of "normal" parameters) that you hadn't put in your parameter file.

I leave it to you to find the reasons for the slight discrepancies in line counts.
.
.
.

4 comments:

The Human Fly said...

For 'CREATE PFILE/SPFILE FROM MEMORY' v$system_parameter4 is the source. When the above command is executed, oracle internally triggers the following command:
select num,name,type,display_value,update_comment from v$system_parameter4 order by lower(name),ordinal.

References (MOS) 784133.1 & 464781.1

Regards,

Jaffar

Coskan Gundogar said...

I think it might be better if you use pfile='/tmp/init.ora' instead of just "pfile from spfile" so readers won't accidently overwrite their init files which points to shared spfile (did that couple of time in my early career :))

Anonymous said...

SELECT * FROM SYS.X$KSPPI;

Hemant K Chitale said...

Anonymous,

Yes, I do know X$KSPPI. I have been querying this in earlier versions.

Howerver, in 11g, the CREATE SPFILE | PFILE FROM MEMORY is a "neater" method, imho.