This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 11, 2013 8:02 AM by 995473 RSS

Unable to use SPFILE

889124 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    SQL> create spfile from pfile=<location of the pfile>;

    *to start database with spfile you must reboot.                                                                                                                                                                                                                   
  • 2. Re: Unable to use SPFILE
    889124 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    ?

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

    srvctl modify database -d <database-name> -p <spfile>
  • 6. Re: Unable to use SPFILE
    889124 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    post results from following OS command

    dir %ORACLE_HOME%\DATABASE\
  • 13. Re: Unable to use SPFILE
    889124 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    So to clarify, I have a standalone database that clearly doesn't want to use a SPFILE. Help !! :)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points