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.

.

.

.

RMAN's COPY command

Following some thread on forums, I had logged a documentation query against the 11gR2 RMAN documentation on 31-Jan.

My question to Oracle's 11gR2 documentation team had been

"Why is the COPY command not documented in the Reference ?" (referring to the 11gR2 Backup and Recovery Reference).

I have today received a response from the documentation team :

Dear RMAN Reader,

Thank you for your feedback and question.

The RMAN COPY command was deprecated in Oracle Database 10gR1. This change is highlighted in "Deprecated RMAN Syntax" section of the documentation. The command that replaces it is BACKUP AS COPY. Previously, when you created a backup set with the COPY command you then had to use RMAN to extract individual datafiles from that backup set. The BACKUP AS COPY command creates image copies and this eliminates the need to extract them from a backup set. The BACKUP AS COPY command allows you to copy a database, tablespaces, datafiles, archived redo logs, and control files.


For example:

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '+TEST';


I hope this answers your question.

Regards,
Oracle Documentation Team

Although I can't now recall which forums thread was it that piqued my curiousity about this issue with the documentation, I'd thought I should now post this response here.

.

.

.

Collection of my Oracle Blog posts on Backup and Recovery

I have uploaded a collection of my Oracle Blog posts on Backup and Recovery, Control files, ArchiveLogs and such. This is now available as a PDF file. I hope that it will be useful as it is downloadable and viewable "off the web".

The entries go as far back as December 2006 and are current as of upto 03-May-2011. I have also included comments as of upto 03-May-2011.

I intend to upload collections of posts on Performance and Optimization and on Architecture and Design soon.

Incidentally, this post, today, is my 250th post to the blog.

.

.

.