Sunday, June 22, 2014

ASM -- Dropping Aliases causes ORA-01157 on database startup

You may use ASM file aliases for datafiles , controlfiles and redolog files of your database configured with the ASM .. However, one day, you may want to drop them ..
This may be the case if your using EBS 12.2 for instance.. EBS 12.2 installer now uses rman  to restore its preconfigured database  at install time, and it defines aliases for the files if your using an ASM instance.

So , if you want to drop ASM aliases, first check v$datafile , v$controlfile, v$log and your init.ora ..

Check those files if you dont want to be suprised after you drop these aliases and restart your database..
What I try to say is, you may end up with controlfile not found or ORA-01157 errors during your database startup..

Okay, lets explain the reason for this errors,

The error related with control file is basically caused by the init.ora/spflile..When you are using aliases for the control_file init.parameter, these errors are expected and the solution is simple.
As you may guess, you just need to modify your init.ora/spfile and reflect the changes in the filenames.
 In other words, you just change your control files to point the system generated aliases rather than the user created aliases you just dropped.
Note that: ASM file names we see normally, like system.dbf.092198 are also aliases but they are system generated aliases.

Okay, so what about ORA-00157 errors then?
The MOS document explains the problem : ORA-01157 on Database Startup After Dropping an Alias (Doc ID 444151.1) . But I didnt like the solution defined there..

You may check your v$datafile view to see/understand the problem , here..
As you know, v$datafile contains datafile information from the control file
The problem is basically, your controlfile has still the user aliases defined for the asm files and that's why, your instance is referring the datafiles and redolofiles by their user defined alias names and not the system generated ones.Anyways, because of  the aliases written in your controlfile  , you end up  with ORA-00157, as your instance will not able to find the datafile through the aliases, which are just dropped..

So, there are three solutions for fixing this,,

1) You can create the alises :) MOS document above suggest that, but I dont like it. "I want to drop the aliases :)"

2) You can mount your database and rename your files, I mean, modify the filenames from dropped aliases to system generated aliases ..But, I dont like that too, because you need to know all the aliases and their associated system generated aliases.. That why, I dont like to this option.. It is exhausting.

3) You may create your controlfile without resetting logs(noresetlogs) . This is my favorite, as you can create a controlfile creation script(which can be gathered from the instance in mount mode already) and then change your datafiles and redologfiles to the system generated alises in the creation script (just run an ls in ASM filesystem and copy&paste the output) and run it. So, when you run the script, your controlfile will be recreated and it will contain the system generated alises.. Thus , you may open your instance without any problems..  In my opinion, this option is the easiest of all..

I feel the need to write this article because there is no information about this specific subject in MOS. Only the document above describes that but I believe, that document needs to be modified, too.

No comments :

Post a Comment

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link:

Register and create an issue in the related category.
I will support you from there.