Oracle 11gR2 ASM – Recovering Corrupted ASM Instance

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


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! 



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