3 Replies Latest reply: Feb 14, 2014 11:56 PM by Baris Yildirim RSS

    RDBMS SPFile on file system and on ASM diskgroup

    Hezzeld-Oracle

      ### Database created with DBCA -

      1. Database is created with spfile on diskgroup.
      2. init.ora has init parameter entries (looks like spfile). There is NO entry like spfile='path'
      3. Database is started with spfile on diskgroup.
      4. If following command is issued -
      "create spfile from pfile"
      Then spfile gets created on the disk.
      5. If 2 spfiles are present (one on DG and another on disk) while startup the one on DG is used.

      ### Database not created with DBCA

      1. Created spfile on diskgroup at location +DG/DBName/spfile<DBName>.ora
      2. Put entry in init.ora,
      > more $ORACLE_HOME/dbs/initDN112ASM.ora
      spfile=+OBTDG2/DN112ASM/spfileDN112ASM.ora
      3. Database is started with spfile on DG.
      4. If following command is issued -
      "Create spfie from pfile"
      Then spfile gets created on the disk.
      5. When 2 spfiles are present, one on DG and another on disk, then the one on disk is used for startup.

      Questions:

      1. When is spfile created on diskgroup?
      2. When is spfile on diskgroup considered as default spfile which database uses for startup?
      3. When and how oracle decides spfile on which Disk Group is to be used for startup more than 1 spfiles are present?
      4. If there are more than 1 diskgroups and all these have spfiles for the database, then how will Oracle decide which spfile to pick?
      5. What is the default location of spfile on diskgroup? As we have $ORACLE_HOME/dbs location in case of Linux ?

        • 1. Re: RDBMS SPFile on file system and on ASM diskgroup
          JohnWatson

          If you look at the scripts generated by DBCA, you'll see the command that puts the spfile on a diskgroup. You will also see that the command that registers the database in the OCR specifies where the spfile is.

          You should use similar commands when you create your database manually.

          • 2. Re: RDBMS SPFile on file system and on ASM diskgroup
            Girish A

            1. When is spfile created on diskgroup?

             

             

             

            DBCA places the SPFILE in an Oracle ASM disk group, and also causes this stub PFILE to be created.

             

             

             

            2. When is spfile on diskgroup considered as default spfile which database uses for startup?

             

             

             

            When Oracle ASM is present SPFILE is searched in the same disk group as the datafiles

             

             

             

            3. When and how oracle decides spfile on which Disk Group is to be used for startup more than 1 spfiles are present?

             

             

             

            It uses SPFILE in the disk group as the datafiles. But its path must be present in stub PFILE

             

             

             

            4. If there are more than 1 diskgroups and all these have spfiles for the database, then how will Oracle decide which spfile to pick?

             

             

             

            It uses disk group as the datafiles

             

             

             

            5. What is the default location of spfile on diskgroup? As we have $ORACLE_HOME/dbs location in case of Linux ?

             

             

             

            Its on disk group.

             

             

            <DGNAME>/dbunique_name/PARAMETERFILE/

             

            Thanks

            Girish

            • 3. Re: RDBMS SPFile on file system and on ASM diskgroup
              Baris Yildirim

              Hi,

               

              Have you ever read so far about Oracle Restart, grid infra, crs.

               

              Regards