ORA-00821 on RAC with ASM

 

Database went down and you can’t bring it up because of this unwelcomed error:

ORA-00821: Specified value of sga_target M is too small

 

Further, “startup nomount” still gives the same error and you can’t alter the spfile and adjust the value that caused this violation.

 

Here are the steps I follow to fix this issue, these steps have been tested on Oracle RAC 11.2.0.3 with ASM 64 bit on Redhat Linux AS V. 5.

Summarized steps to recover:

  1. Create pfile from spfile
  2. Edit pfile and adjust value causing this issue
  3. Start the database up with nomount option
  4. Create spfile from pfile
  5. Adjust ASM alias
  6. Bounce the database
  7. Remove old spfile from ASM

 

Steps should be on a single node only.

 

1)      Create pfile from spfile

create pfile=’/u02/backups/dbatst/initdbatst.ora.new’ from spfile=’+data01/dbatst/spfiledbatst.ora’;

 

For pfile, you need to specify a location, otherwise it will overwrite your pfile in $ORACLE_HOME/dbs, which will further complicate your recovery.

 

 For spfile, you need to find where your spfile file is on ASM, remember your database is not even mounted, so Oracle is totally lost as where the location of database’s spfile is.

 

2)      Edit pfile

 Now change value causing violation.

 

3)    Start the database up with nomount option

Startup nomount pfile=’/u02/backups/dbatst/initdbatst.ora.new’

 This should bring up the database in nomount stage.

  

4)      Create spfile from pfile

create spfile=’+DATA01′ from pfile=’/u02/backups/dbatst/initdbatst.ora’;

Notice now that in nomount stage, Oracle recognizes where to put the spfile and you don’t need to specify the full ASM path to spfile.

spfile Location: <+dg>/database name/parameterfile

In my situation it will be ‘+DATA01/dbatst/parameterfile’

Also note that Oracle will create a new spfile without overwriting the old one. You should delete the old spfile as indicated later.

 

5)      Adjust ASM alias

“Alias” in ASM is like link “ln” operating system command.

 

The location of spfile alias on ASM is in the pfile under $ORACLE_HOME/dbs.

The spfile alias on ASM is pointing to the old spfile file, not the new one.

To fix this issue, you need to delete old alias and recreate a new alias

 

Here are the commands:

asmcmd

cd to alias location, location should be ‘+dg/dbname/spfiledbname.ora’

ls -l  , just to confirm it is an alias

delete alias:

rmalias spfiledbatst.ora

 

recreate alias

mkalias +DATA01/DBATST/PARAMETERFILE/spfile.535.831221333 spfiledbatst.ora

  

6)      Bounce the database

Use srvctl utility to bounce the database.

After database comes up successfully, make sure it is using the spfile.

 From a sqlplus session:

      show parameter spfile

 

7)      Delete old spfile

Just to keep things tidy, remove the old spfile from <+dg>/database name/parameterfile

 

 

Hazem Ameen
Senior Oracle DBA

 

 

How to Find an Oracle DBA Job in Saudi Arabia

I receive several emails through this blog or LinkedIn asking how to find an Oracle DBA job in Saudi Arabia if I reside in some other country.

Before you starting your job hunting, keep in mind that most companies can survive with local talent which became a lot through the last several years due to Oracle’s popularity. So you must have a unique set of skills required immediately for a company to relocate you.

The best and fastest way is through good old fashioned networking (talking to people that know people). This way you resume will make it directly to department heads and decision makers. In this case, you will have an edge because you are already recommended by your connection and there is a chance the company will not even advertise the job opening until they talk to you first. Even if the company doesn’t have an immediate job opening, they will keep you in mind for future openings.

Another less effective way is responding to ads in newspapers or recruiting websites. The company will be waiting for applicants’ resumes and yours will be one of those many resumes (hope your resume will stand out).

The least effective way is posting your resume on a recruiting website, LinkedIn or applying through a company’s website and waiting for the company to contact you. This rarely produces an immediate response. Your resume will eventually age on recruiting websites and will be bypassed in searches. In case of company’s website, if their inbox is not already full, your resume will rarely makes its way to the IT department.

Hope this helps