Oracle DBA – A lifelong learning experience

spfile woes with ASM

Posted by John Hallas on July 25, 2008

Rather a hotch-potch of an entry this one. Once again proving my incompetence but also showing how I dug myself out of a hole.

I had been trying to build a standby database on cooked filesystem as a single-instance from a RAC cluster on ASM. It is sufficient to say that I had hit a lot of problems but as I got to build the spfile on the standby I was hitting

ORA-00845: MEMORY_TARGET not supported on this system

I was a bit stumped by this as the parameter is valid and it was reading it from the spfile of the RAC instance that it was trying to duplicate.

Someone suggested using OEM to replicate the database which I did and that failed with eventually the same problem. Now this is where the incompetence (or at least not thinking comes in). I thought I would take it out of the spfile and that it would automatically use the values as in sga_target which were still set.

How wrong could I be. After changing the parameters via alter system and restarting the instance it would not restart. ORA-4031 errors, not enough room to load any packages in. No problem I thought, just alter system set the shared_pool_size to something big and away we go. ORA-4031 again. Not enough memory to load the alter system command into the SGA.

Now I was starting to get a bit stumped as I could not create a pfile from the spfile and asmcmd did not have a strings command that allowed me to get the file out. This was a RAC database so there were a lot of parameters that I needed and a init.ora from elswhere (edited) would not be sufficient. I did have a RMAN backup of the database, which in hindsight it would have been easier to recover the spfile from that but I decided to copy the spfile out from ASM

cp spfileNMHPERF.ora /u00/rman_backups/j.ora

source +DATA/NMHPERF/spfileNMHPERF.ora

target /u00/rman_backups/j.ora

copying file(s)…

file, /u00/rman_backups/j2.ora, copy committed.

So far so good. I then did a strings on that and created a pfile called j1.ora in $OH/dbs. I could start an instance with that., so I just need to get it into spfile format in ASM

create spfile=’+DATA/spfileNMHPERF.ora’ from pfile=’/u00/app/oracle/product/11.1.0/db_2/dbs/j1.ora’;

When I came to start the database with the srvctl command I got the failure

mat058 ora.NMHPERF.NMHPERF1.inst:SQL> ORA-01565: error in identifying file ‘+DATA/NMHPERF/spfileNMHPERF.ora’

mat058 ora.NMHPERF.NMHPERF1.inst:ORA-17503: ksfdopn:2 Failed to open file +DATA/NMHPERF/spfileNMHPERF.ora

mat058 ora.NMHPERF.NMHPERF1.inst:ORA-15056: additional error message

mat058: ora.NMHPERF.NMHPERF1.inst: ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/nmhperf/spfilenmh

mat058 ora.NMHPERF.NMHPERF1.inst: ORA-01078: failure in processing system parameters

That was true, as looking in ASM I could see that the file was called spfile.271.660946331

So, to cut a long story short, I removed the old alias and created a new alias for the spfile, which matched where the init.ora file on each node was pointing to





ASMCMD> rmalias spfileNMHPERF.ora

ASMCMD> mkalias +DATA/NMHPERF/PARAMETERFILE/spfile.271.660946331

$cat initNMHPERF1.ora


Overall I was pleased with the end result as I picked up some new experiences with ASM and found my way around it a bit better.

Next steps on my list is to investigate the MEMORY_TARGET error and then get on with the DG standby build.

I hope these notes are of some help to anybody who gets in the same position as me.

2 Responses to “spfile woes with ASM”

  1. MadAsAFish said

    Hey John
    Keep up the good work – one thing you may want to look at is the OS settings.
    If using Linux check the shared memory settings and disk layout [esp /dev/shm] as you’ll only be able to create a db with a memory size up to this limit.
    If you lower your MEMORY_TARGET to below the current size, then you’ll be able to start the DB normally.
    Best of Luck.

  2. John Hallas said

    Thanks for the input madasafish. As I recall, the probem was that the parameter would not be accepted when building the database, not that the parameters/values were too high. I found the exercise very rewarding as I picked up a bit of ASM knowledge.


Leave a Reply

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

You are commenting using your 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

%d bloggers like this: