Oracle 11gR2 ASM – Recovering Corrupted ASM Instance
It was 4am in the morning. The ASM instance was crashing due to ORA-04031 errors. The current ASM instance was configured with SGA_MAX_TARGET=300M. I needed to remove it and bump up the MEMORY_TARGET and MEMORY_MAX_TARGET.
SQL> alter system set SGA_MAX_TARGET=0M;
Guess what was wrong with this command? Everything! I was trying to remove the cap on the SGA_MAX_TARGET by setting it to 0. However, I set it to “0M;”. This make sure that my SGA_MAX_TARGET is capped at 0M. It will not have any memory to do anything at all!
I was supposed to run this command instead.
SQL> alter system set SGA_MAX_TARGET=0;
When I tried to start the ASM instance, it was hitting ORA-04031 errors. I couldn’t modify the SPFILE at all. I was unable to run any commands on it.
This was what I did to “recover” the ASM instance.
1. Create a pfile manually with the minimal parameters
INSTANCE_TYPE=ASM MEMORY_TARGET=1536M MEMORY_MAX_TARGET=4096M
2. Startup ASM instance with the new pfile
SQL> startup mount pfile='/path/to/pfile'
3. Create pfile from original SPFile
SQL> create pfile='/path/to/original_pfile' from spfile;
4. Shutdown ASM instance
SQL> shutdown immediate;
5.Manually edit the pfile to correct the mistake
6. Startup ASM instance using the edited pfile
SQL> startup mount pfile='/path/to/original_pfile';
7. Create SPFile from pfile. This will overwrite the original SPFile
SQL> create spfile from pfile
8. Bounce the ASM instance
SQL> shutdown immediate; SQL> startup;
And the day is saved! On a hindsight, It was a silly mistake that could be avoided. Number one rule, never make any changes after working 14 hours consecutively!