This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Add passwordfile=... option to sqlplus startup command for 12c

User51642 Yong Huang
User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge
edited Jan 26, 2018 9:30AM in Database Ideas - Ideas

By default, the database password file is stored in ASM instead of on the file system in Oracle 12c (if ASM is used). If the ASM instance cannot be brought up due to loss or corruption of the password file, creating a password file on the file system (with command orapwd) is not helpful. You can't run any asmcmd command to tell ASM to use the password file on the file system because asmcmd needs ASM to be up. Even a command like `crsctl query credmaint' (Ref: Doc ID 1313657.1) won't work. This chicken-and-egg problem can be solved by a passwordfile option for the sqlplus startup command, much like the pfile option.

Alternatively, allow certain very basic asmcmd commands to run even when ASM is down, much like the function provided by -init of crsctl, or -prelim of sqlplus.

Sven W.
2 votes

Active · Last Updated

Comments

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Schwalmtal / NiederrheinPosts: 1,094 Employee

    What's the point of running sqlplus when ASM is down?

  • User51642 Yong Huang
    User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge

    What's the point of running sqlplus when ASM is down?

    The point is to bring up ASM manually (if passwordfile option is supported):

    . oraenv

    +ASM

    sqlplus / as sysasm

    SQL> startup passwordfile=/u01/app/grid/dbs/orapw+ASM

    Oracle has added more and more chicken-and-egg situations over the years. This one really got me!

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Schwalmtal / NiederrheinPosts: 1,094 Employee

    The point is to bring up ASM manually (if passwordfile option is supported):

    . oraenv

    +ASM

    sqlplus / as sysasm

    SQL> startup passwordfile=/u01/app/grid/dbs/orapw+ASM

    Oracle has added more and more chicken-and-egg situations over the years. This one really got me!

    In your original post, you are talking about the "database password file". That't why I was asking about the usefulness of using sqlplus (for the database) when ASM is down.

    But still, I don't get it. In order to start the local ASM instance, you do not need access to the password file. Rather, it is used for remote clients (Flex ASM) to be able to connect to that instance once ASM is up.

    Check the Oracle documentation: "You can use a password file located on a disk group for authentication only if the Oracle ASM instance is running and the designated disk group is mounted. Otherwise, operating system authentication must be used to bootstrap the startup of the Oracle ASM instance and stack." (https://docs.oracle.com/database/121/OSTMG/GUID-6165EC00-C329-4140-A007-2FE18D6DEC51.htm )

    Andris

    User51642 Yong Huang
  • User51642 Yong Huang
    User51642 Yong Huang Houston, TXMember Posts: 183 Bronze Badge

    Andris,

    You're right. I did some test and proved that "In order to start the local ASM instance, you do not need access to the password file." In asmcmd, I ran pwget to get the paths of the actual file and the link, rm'ed both of them. Stopped asm. Even stopped the CRS stack. I was able to bring up ASM.

    CRS daemon seems to be unhappy with the password file missing. Also, it's odd that after I pwcopy back into the original location from the file system backup I saved at the beginning of my test, the password file now has UNKNOWN in the actual path:

    ASMCMD [+grid] > pwget --asm

    +GRID/orapwASM

    ASMCMD [+grid] > ls -l +GRID/orapwASM

    Type      Redund  Striped  Time             Sys  Name

    PASSWORD  HIGH    COARSE   JAN 26 13:00:00  N    orapwASM => +GRID/DB_UNKNOWN/PASSWORD/pwddb_unknown.256.966434097

    Before the test, it was:

    orapwasm => +GRID/ASM/PASSWORD/pwdasm.256.965730961

    Other than an unsightly actual path, I guess it doesn't matter, since ASM knows where to find it.

    I'm not using Flex ASM. And I apologize for saying "database password file" when I meant ASM password file.

    I hope I can mark your message as the answer to this thread. I don't see that option here. Thank you!