Getting the database parameters from a spfile

There’s been some debate about how to get the parameters from a spfile. A spfile is a binary version of the parameter file of the Oracle database.

I added to the debate that my experience is that there are is some weirdness with using the strings command on the spfile. The discussion was on twitter, I didn’t add that doing that it most of the time meant it costed more time than I saved from using the “shortcut” of using strings on a spfile.

Let me show you what it means.

I’ve got a database with storage on ASM. Among other options, there are two simple methods to get the spfile from ASM:

You can get the spfile by logging on to the database, and create a pfile from the spfile, and create a spfile again:

SYS@v11204 AS SYSDBA> show parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/v11204/spfilev11204.ora

Now let’s recreate the spfile on a filesystem:

SYS@v11204 AS SYSDBA> create pfile='/tmp/pfile' from spfile='+DATA/v11204/spfilev11204.ora';

File created.

SYS@v11204 AS SYSDBA> create spfile='/tmp/spfile' from pfile='/tmp/pfile';

File created.

Another option is to copy the spfile out of ASM:
Set the ASM environment and execute asmcmd

[oracle@ol65-oracle11204 [v11204] ~]$ +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ol65-oracle11204 [+ASM] ~]$ asmcmd
ASMCMD>

Now go to the DATA disk group, and the directory of the database (my database is called v11204). If you look here, you’ll see a link to the spfile to its true ASM place:

ASMCMD> cd data/v11204
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilev11204.ora

If you take a long listing, you see the true ASM place:

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    TEMPFILE/
                                                 N    spfilev11204.ora => +DATA/V11204/PARAMETERFILE/spfile.265.847477361

In asmcmd, you can just copy the spfile (the real name, not the “spfilev11204.ora” one, which is a kind of symbolic link):

ASMCMD> cp +DATA/V11204/PARAMETERFILE/spfile.265.847477361 /tmp/spfile.asm
copying +DATA/V11204/PARAMETERFILE/spfile.265.847477361 -> /tmp/spfile.asm

Okay, now back to using strings on the spfile. If I issue strings on the spfile, I get what looks like a complete parameter file:

$ strings spfile.asm
v11204.__db_cache_size=692060160
v11204.__java_pool_size=4194304
v11204.__large_pool_size=8388608
v11204.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
v11204.__pga_aggregate_target=524288000
v11204.__sga_target=943718400
v11204.__shared_io_pool_size=0
v11204.__shared_pool_size=226492416
v11204.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/v11204/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/v11204/controlfile/current.25
6.847475315'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=1024
*.db_name='v11204'
*.diagnostic_dest='/u01/app/oracle'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=v11204XDB)'
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=943718400
*.undo_tablespace='UNDOTBS1'

But is it? If you take a detailed look, you’ll see something which is not alright:

*.compatible='11.2.0.4.0'
*.control_files='+DATA/v11204/controlfile/current.25
6.847475315'
*.db_block_size=8192

This is exactly what I meant: for some reason, the line with ‘control_files’ seems to have been broken into two pieces. I think I don’t need to tell most readers of this blog that tracing this kind of oddities costs a lot of time, especially if you’ve got a big spfile. And: most of the time you are playing around with this, there probably is something wrong, and you simply don’t have the time for this fooling around.

But what why is this happening? Let’s look at the spfile contents using the ‘od’ utility (octal dump):

$ strings spfile.asm | od -t d1 -a -A d
0000000  118   49   49   50   48   52   46   95   95  100   98   95   99   97   99  104
           v    1    1    2    0    4    .    _    _    d    b    _    c    a    c    h
0000016  101   95  115  105  122  101   61   54   57   50   48   54   48   49   54   48
           e    _    s    i    z    e    =    6    9    2    0    6    0    1    6    0
0000032   10  118   49   49   50   48   52   46   95   95  106   97  118   97   95  112
          nl    v    1    1    2    0    4    .    _    _    j    a    v    a    _    p
0000048  111  111  108   95  115  105  122  101   61   52   49   57   52   51   48   52
           o    o    l    _    s    i    z    e    =    4    1    9    4    3    0    4

This is the beginning of the spfile, to get an idea what we are looking at.
The numbers on the left side (00000000, 00000016, etc) are the position numbers in decimal. This shows there are 16 characters per line. The numbers on the line of the position are the ASCII values. The character representation of the ASCII value is BENEATH it. If you now read the line (looking at the character representation, you see ‘v11204.__db_cach (new line) e_size=692060160 (new line)’ and then ASCII value 10, which is represented with ‘nl': newline.

Okay, now we are used to reading this output, now let’s look at the problem section with the control_files line:

0000432   46   48   39   10   42   46   99  111  110  116  114  111  108   95  102  105
           .    0    '   nl    *    .    c    o    n    t    r    o    l    _    f    i
0000448  108  101  115   61   39   43   68   65   84   65   47  118   49   49   50   48
           l    e    s    =    '    +    D    A    T    A    /    v    1    1    2    0
0000464   52   47   99  111  110  116  114  111  108  102  105  108  101   47   99  117
           4    /    c    o    n    t    r    o    l    f    i    l    e    /    c    u
0000480  114  114  101  110  116   46   50   53   10   54   46   56   52   55   52   55
           r    r    e    n    t    .    2    5   nl    6    .    8    4    7    4    7
0000496   53   51   49   53   39   10   42   46  100   98   95   98  108  111   99  107
           5    3    1    5    '   nl    *    .    d    b    _    b    l    o    c    k

If we look closely, you can see ‘*.control_fi’ on the first line, after the ASCII value 10, newline.
If we read on, it’s shows:
*.control_files=’+DATA/v11204/controlfile/current.25 (nl) 6.847475315′ (nl)
In other words, there is an additional newline. But the position (488) seems strange to me.

But when looking at how I generated this, I executed ‘strings’. This means the output is filtered to readable characters. Would there be unreadable characters in a spfile? Let’s look!

$ cat spfile.asm | od -t d1 -a -A d
0000000   67   34    0    0    1    0    0    0    0    0    0    0    0    0    1    4
           C    "  nul  nul  soh  nul  nul  nul  nul  nul  nul  nul  nul  nul  soh  eot
0000016   77   25    0    0    0    0    0    0    0    0    0    0    0    0    0    0
           M   em  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul
0000032    0    0    0    0    0    0    0    0    0    0    0    0    5    0    0    0
         nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  enq  nul  nul  nul
0000048    0    2    0    0    0    0    0    0    0    0    0    0    0    0    0    0
         nul  stx  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul
0000064    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
         nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul  nul

Aha! So strings does filter a lot of stuff! Now let’s look at the control_files parameter again:

0000960   46   48   46   52   46   48   39   10   42   46   99  111  110  116  114  111
           .    0    .    4    .    0    '   nl    *    .    c    o    n    t    r    o
0000976  108   95  102  105  108  101  115   61   39   43   68   65   84   65   47  118
           l    _    f    i    l    e    s    =    '    +    D    A    T    A    /    v
0000992   49   49   50   48   52   47   99  111  110  116  114  111  108  102  105  108
           1    1    2    0    4    /    c    o    n    t    r    o    l    f    i    l
0001008  101   47   99  117  114  114  101  110  116   46   50   53    1   67    0    0
           e    /    c    u    r    r    e    n    t    .    2    5  soh    C  nul  nul
0001024   67   34    0    0    3    0    0    0    0    0    0    0    0    0    1    4
           C    "  nul  nul  etx  nul  nul  nul  nul  nul  nul  nul  nul  nul  soh  eot
0001040   34  121    0    0   54   46   56   52   55   52   55   53   51   49   53   39
           "    y  nul  nul    6    .    8    4    7    4    7    5    3    1    5    '

I think this is quite self explanatory to a lot of people. If not, let me help you: all is as we expect up to position 1018, at which there are a few non-readable characters, until position 1043. This means there are 25 character positions which contain something else, after which the parameter file contents continue.

But now look at the position: it’s around position 1024. It’s my guess that the spfile uses a block size of 1024 bytes (1KB). In order to check for consistency of the parameter file blocks, Oracle puts some extra (internal) data on the borders of the block so integrity can be checked. This is like an Oracle database block.

So, there you have a reason not to use strings on the spfile, unless you like a game of find the random newlines in your new pfile.

How should you create a new pfile then?

The preferred method is using ‘create pfile from spfile’. This requires logging on to the instance (nomount is enough).
An alternative is to look at the alert.log file. When starting an Oracle instance, the non-default parameters are printed in the alert.log. This is a very simple, yet useful method of reconstructing the parameter file.

Update: I’ve gotten an email from Bjoern Rost saying that my statement on the need of the instance at least needing to be in nomount phase is not true, because the ‘create pfile from spfile’ commands can be used with the instance being down.

I decided to take this for an additional test. First of all, there two methods which can be used pfile/spfile manipulation via sqlplus (as far as I know): logging on with SYSDBA privileges, and starting sqlplus on the local node without logging on to any instance (/nolog).

a) instance is OPEN (the which is the same with in the nomount and mount phase)

With SYSDBA privilege.

$ sqlplus / as sysdba 
...
SYS@v11204 AS SYSDBA> create pfile='/tmp/tt' from spfile;

File created.

With /nolog.

$ sqlplus /nolog
...
@ > create pfile='/tmp/tt' from spfile;
SP2-0640: Not connected

Upon given this a little thought, it’s kind of obvious only saying spfile can’t be used with /nolog: there are no settings, we are not connected to any instance. So let’s try specifying a full path for both pfile and spfile:

$ sqlplus /nolog
...
@ > create pfile='/tmp/tt' from spfile='+DATA/v11204/spfilev11204.ora';
SP2-0640: Not connected

No. sqlplus /nolog can’t be used in my situation (Oracle 11.2.0.4, Linux X64 OL 6u5, ASM, instance open).

b) instance down

With SYSDBA privilege:

$ sqlplus / as sysdba
...
Connected to an idle instance.

SYS@v11204 AS SYSDBA> create pfile='/tmp/tt' from spfile='+DATA/v11204/spfilev11204.ora';

File created.

Aha! So we can manipulate the pfile and spfile with the instance being down when we logon as SYSDBA!

Let’s look at another case:

$ sqlplus / as sysdba
...
Connected to an idle instance.

SYS@v11204 AS SYSDBA> create pfile='/tmp/tt' from spfile;
create pfile='/tmp/tt' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

What is shown here, is that when my instance is down, I can’t use ‘create pfile from spfile’ without a specification for spfile. Apparently, it looks at the default location ($ORACLE_HOME/dbs) for the default spfile name (spfilev11204.ora in my case): ORA-01565: error in identifying file ‘?/dbs/spfile@.ora’. ‘?’ means $ORACLE_HOME and ‘@’ means instance name. Let’s see if this works if we create a spfile on that location:

SYS@v11204 AS SYSDBA> create pfile='/tmp/tt' from spfile='+DATA/v11204/spfilev11204.ora';

File created.

SYS@v11204 AS SYSDBA> create spfile from pfile='/tmp/tt';

File created.

The first step is already done, but redone for completeness sake. The second step creates a spfile on the default location from the pfile ‘/tmp/tt’. Now we can ask sqlplus to essentially do the same but in reverse: create ‘/tmp/tt’ from spfile:

SYS@v11204 AS SYSDBA> create pfile='/tmp/tt' from spfile;

File created.

Yes! Now this works. The reason for this behaviour is I explicitly want my spfile not to be on the default location ($ORACLE_HOME/dbs), because this is on a local filesystem. This is not a problem with single instance databases with no shared storage at all, but this is not practical in the case of RAC, and with single instance databases with a cluster and shared storage (think Exadata here!), because it’s practical to have the spfile on shared diskspace so the instance can very easily be started on another node.

Let’s try sqlplus /nolog again:

$ sqlplus /nolog
...
@ > create pfile='/tmp/tt' from spfile='+DATA/v11204/spfilev11204.ora';
SP2-0640: Not connected

Nope. That doesn’t work. I’ve seen blogposts indicating that /nolog can be used for pfile/spfile manipulation, this didn’t work in my case. Bjoern was right that pfile/spfile can be done while the instance is down.

About these ads
5 comments
  1. johnnyq72 said:

    This is good stuff Frits!!

  2. johnnyq72 said:

    Reblogged this on johnnyq72 and commented:
    A good look into (s)pfiles and what to do if their gone!

  3. johnnyq72 said:

    Reblogged this on johnnyq72 and commented:
    A good look into (s)pfiles and what to do if their gone!

    • Hi Hemant, I just tried ‘strings -a’, it gives me exactly the same newline.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,060 other followers

%d bloggers like this: