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
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
When I came to start the database with the srvctl command I got the failure
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
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.