1 2 Previous Next 18 Replies Latest reply: Apr 11, 2013 10:02 AM by 995473 RSS

    Unable to use SPFILE

    889124
      Windows 2008 R2 Datacenter / Oracle 11.2.0.2

      I'm trying to configure my database to use an SPFILE as at the moment, its using an PFILE.

      The Step's I've took ..

      I've connected in as sys and issued the following command

      create spfile from pfile;

      This creates correctly and then I shutdown / start the database.

      Once its restarted, I've checked 'show parameter spfile' and the VALVE is blank .. ie, still using the pfile.

      The Oracle windows services for the database was create using

      oradim -new -sid TEST -inpwd password - startmode A - pfile (then the location of the pfile).

      As a test, I've deleted this service and recreated it (using oradim) but this time, used the startmode of manual. The service starts but I can't connect to the service to manually start the database as there is some tns issue.. even though I can TNSPING the instance..

      Any assistance would be greatly appreciated.
        • 1. Re: Unable to use SPFILE
          Fran
          SQL> create spfile from pfile=<location of the pfile>;

          *to start database with spfile you must reboot.                                                                                                                                                                                                                   
          • 2. Re: Unable to use SPFILE
            889124
            Yes, I've restarted the database but its still using the pfile ? I have to admit, I've never had this issue before ..

            Edited by: JamesW on Apr 10, 2013 8:04 AM
            • 3. Re: Unable to use SPFILE
              Girish Sharma
              How will I know if my database is using a PFILE or SPFILE:

              Execute the following query to see if your database was started with a PFILE or SPFILE:

              SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
              FROM sys.v_$parameter WHERE name = 'spfile';

              Regards
              Girish Sharma
              • 4. Re: Unable to use SPFILE
                889124
                ?

                I know it not using an SPFILE.. It still using the PFILE
                • 5. Re: Unable to use SPFILE
                  Fran
                  try this:

                  srvctl modify database -d <database-name> -p <spfile>
                  • 6. Re: Unable to use SPFILE
                    889124
                    Thanks

                    This returned
                    PRCD-1027 : Failed to retrive database TEST
                    PRCR-1115 : Failed to find entries to type respurces that match filters....
                    Cannot commicate with crsd
                    • 7. Re: Unable to use SPFILE
                      Fran
                      what is the result of:

                      sqlplus / as sysdba:

                      sql> select * from V$parameter where name='spfile';

                      and

                      sql> SELECT VALUE FROM V$PARAMETER WHERE NAME='pfile'
                      /
                      • 8. Re: Unable to use SPFILE
                        EdStevens
                        Fran wrote:
                        what is the result of:

                        sqlplus / as sysdba:

                        sql> select * from V$parameter where name='spfile';

                        and

                        sql> SELECT VALUE FROM V$PARAMETER WHERE NAME='pfile'
                        /
                        I think he's pretty well established that his db is not using the spfile. And I've never seen a parameter named 'pfile'.
                        • 9. Re: Unable to use SPFILE
                          EdStevens
                          JamesW wrote:
                          Windows 2008 R2 Datacenter / Oracle 11.2.0.2

                          I'm trying to configure my database to use an SPFILE as at the moment, its using an PFILE.

                          The Step's I've took ..

                          I've connected in as sys and issued the following command

                          create spfile from pfile;

                          This creates correctly and then I shutdown / start the database.

                          Once its restarted, I've checked 'show parameter spfile' and the VALVE is blank .. ie, still using the pfile.

                          The Oracle windows services for the database was create using

                          oradim -new -sid TEST -inpwd password - startmode A - pfile (then the location of the pfile).

                          As a test, I've deleted this service and recreated it (using oradim) but this time, used the startmode of manual.
                          But did you still include the "-pfile" parameter?

                          The service starts but I can't connect to the service to manually start the database as there is some tns issue.. even though I can TNSPING the instance..

                          Any assistance would be greatly appreciated.
                          • 10. Re: Unable to use SPFILE
                            889124
                            SQL> select * from V$parameter where name='spfile';

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            180

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            spfile

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            2

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------


                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------


                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            TRUE FALSE IMMEDIATE TRUE FALSE FALSE FALSE FALSE

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            server parameter file

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------


                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------
                            3653611964

                            NUM
                            ----------
                            NAME
                            -----------------------------------------------------------------------------

                            TYPE
                            ----------
                            VALUE
                            -----------------------------------------------------------------------------

                            DISPLAY_VALUE
                            -----------------------------------------------------------------------------

                            ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS
                            --------- ----- --------- ----- ---------- ----- ----- -----
                            DESCRIPTION
                            -----------------------------------------------------------------------------

                            UPDATE_COMMENT
                            -----------------------------------------------------------------------------

                            HASH
                            ----------


                            SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='pfile';

                            no rows selected

                            SQL>
                            • 11. Re: Unable to use SPFILE
                              EdStevens
                              JamesW wrote:
                              SQL> select * from V$parameter where name='spfile';

                              NUM
                              ----------
                              NAME
                              -----------------------------------------------------------------------------

                              TYPE
                              ----------
                              VALUE
                              -----------------------------------------------------------------------------
                              <snip remainder of nearly unreadable output>


                              Please, when you are posting commands and output, enclose them in the \
                               tag.
                              
                              As you write it, it looks like this:
                              
                              \
                              select sysdate from dual;
                              \
                              And posted, it looks like this:
                              select sysdate from dual;
                              That way, the forum software will preserve exactly what you post and not try to make an (un)intelligent interpretation ..                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                              • 12. Re: Unable to use SPFILE
                                sb92075
                                post results from following OS command

                                dir %ORACLE_HOME%\DATABASE\
                                • 13. Re: Unable to use SPFILE
                                  889124
                                  sb92075 wrote:
                                  post results from following OS command

                                  dir %ORACLE_HOME%\DATABASE\
                                  The system cannot find the path specified
                                  • 14. Re: Unable to use SPFILE
                                    JamesW
                                    So to clarify, I have a standalone database that clearly doesn't want to use a SPFILE. Help !! :)
                                    1 2 Previous Next