We help IT Professionals succeed at work.

Create SYS Directory in Oracle ASM

Julie Kurpa
Julie Kurpa asked
on
Medium Priority
1,044 Views
Last Modified: 2012-06-27
I would like to create a directory in ASM for our existing database.   I've successfully been able to create the directory I want however it tells me the directory is a user defined directory.   In displaying the directory in ASMCMD, I can see a N under the SYS column.  Whereas the other directories have a Y.  

Here is the command I've used to create the directory through ASM SQL:

ALTER DISKGROUP DATA ADD DIRECTORY '+DATA/DBNAME/PARAMETERFILE';

When displaying the directory struction through ASMCMD I see:

Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    FLASHBACK/
                                                 Y    ONLINELOG/
                                                 N    PARAMETERFILE/
                                                 Y    TEMPFILE/

Notice the "N"?
How do I create a SYS defined directory?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Why do you need to see a 'Y'?

Per the docs:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/asm_util.htm

...
whether the file was system-generated (indicated by Y under the SYS column) or user-created (as in the case of an alias, indicated by N under the SYS column).
...

Since you created it, 'N' is the correct value.
Julie KurpaSr. Systems Programmer

Author

Commented:
That's a good question.  Here I go to try and explain myself:

I want to store my spfile in the +data/db1/parameterfile directory.  

Currently when I create my spfile (create spfile='+DATA/db1/spfiledb1.ora' from pfile='newsp.ora';), the database puts an alias in the +data/db1 directory called "spfiledb1.ora" which points to directory +data/db_unknown/parameterfile where the true spfile ends up residing.  

I don't like that it creates this "db_unknown" directory.  Nope..not a bit.

I've got a 2nd database that doesn't do like that.  It's parameter file gets stored in +data/db2/parameterfile directory like it's supposed to.  

The only difference I can see between the two databases in the ASM is that db1 doesn't actually have a +data/db1/parameterfile directory like db2.  Perhaps if I created one, it would use it instead of creating the distasteful "db_unknown".

There is a reference in the Oracle® Database Storage Administrator's Guide that distinquishes between Oracle Managed Files (system-generated) and those created by a user.   Yes, I know it's talking about a file and not a directory.  And no, it doesn't give me any warnings or further information on it.  It just distinquishes.

Still I'm thinking that I want to avoid problems by having a SYS generated directory instead of a user generated one to hold my spfile.    

Of course I'm making a big assumption that my theory about creating the parameterfile directory is the solution to why the spfile gets put in the "db_unknown" directory in the first place.

I hope all that made sense.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
I'm far from an ASM Expert.  Hopefully another Expert will be along if I am unable to help figure this out.

Is the database up?  From what I can see the db_unknown is assigned to the spfile when the db is not up.

The solution I've seen is startup with a pfile, the recreate the spfile and the proper database will be used.

http://it.toolbox.com/blogs/about-that-data/spfiles-and-asm-18651

>>The only difference I can see between the two databases in the ASM

Sorry.  Cannot help with this one.  Only played around with ASM in class.  Cannot remember if we were prompted for this as part of the install/setup or not.

Still don't think the Y/N indicator is the issue.
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks slighwv.
The database is most definitely up.  Both nodes (it's a RAC).  

I read that link you gave me in my earlier research and it didn't seem to apply.  I'd have to research on it more before I try it.

Further info:  This became an issue for me because yesterday the spfile in that "db_unknown" directory got deleted since it seemed to be bogus.  It wasn't realized that the spfile in the +data/db1 directory was actually an aliase to the deleted one.  uh oh.  Database crashed this morning and we had to recreate the spfile from a rebuilt pfile.  

This seems to be similar to the steps outlined in the link except we didn't start the RAC from the pfile before creating the spfile.   Perhaps that's the key.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If you have already utilized the 'Google method' of troubleshooting then I'm afraid I'm about done.

Hopefully another Expert will be along soon that is running RAC and is using ASM.  I'm not running either.



Julie KurpaSr. Systems Programmer

Author

Commented:
I'm being reminded by EE administrator that my question is still open with no activity.  

I'm not able to test out the potential solution since this is a 24x7 database.  I'll have to try the solution during a scheduled outage.

IUntil then, I'm awarding points to slightwv.  Thanks for your feedback.