This discussion is archived
12 Replies Latest reply: Jan 28, 2013 1:26 PM by Roshan Jose RSS

pfile and spfile

Aryacool Newbie
Currently Being Moderated
Hi friends,

I have one doubt. Suppose my Prod Database is up and accidentally my pfile deleted. what will happen to my prod database whether it wil fail the instance or any error please tell me. as per concept that i know pfile accessed when we nomount the database for initialization of database.(11g)


in case of SPFILE

Suppose my spfile deleted and previously my database was in up condition. Please tell me what will happen to my instance if i am working in a OLTP/DSS environment.(i dont have backup also how to recover and work in 11g if instance failed)

Regards,
Arya
  • 1. Re: pfile and spfile
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    In both cases (PFILE and SPFILE), the database instance will continue to operate if the file is deleted. You will even be able to SHUTDOWN (altough the update to the SPFILE on SHUTDOWN may complain). But the next STARTUP will fail.

    In 11g you can execute CREATE PFILE FROM MEMORY or CREATE SPFILE FROM MEMORY as long as the Database Instance is running.

    If you don't create the PFILE/SPFILE from MEMORY and SHUTDOWN the database, you will have to restore the file from backups (SPFILEs are included in Autobackups if you have CONTROLFILE AUTOBACKUP ON).

    If you do not have a backup, you can identify non-default parameters from the alert_SID.log file and create a pfile "by hand".


    Hemant K Chitale
  • 2. Re: pfile and spfile
    user10569054 Newbie
    Currently Being Moderated
    PFILE/SPFILE don't need RECOVERY. Only restoration is enough.
    DB_NAME is the only parameter required to start DB. Alert log contains rest of the non-dafault parameters values.

    Regards!
  • 3. Re: pfile and spfile
    Aryacool Newbie
    Currently Being Moderated
    thanks for your reply.

    Clear me one thing if you are telling nothing will happen to instance at any point then, suppose i want to change any modifiable initialization parameter by alter command with scope=both/spfile then what will happen database will work fine in up condition.

    Thanks & Regards
    Arya
  • 4. Re: pfile and spfile
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    suppose i want to change any modifiable initialization parameter by alter command with scope=both/spfile then what will happen database will work fine in up condition.
    I don't know. It's not something I've ever tested.

    Hemant K Chitale
  • 5. Re: pfile and spfile
    Girish Sharma Guru
    Currently Being Moderated
    Clear me one thing if you are telling nothing will happen to instance at any point then, suppose i want to change any modifiable initialization parameter by alter command with scope=both/spfile then what will happen database will work fine in up condition.
    See this demo :
    SQL> show parameter resource_limit;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    resource_limit                       boolean     FALSE
    SQL> alter system set RESOURCE_LIMIT=true scope=spfile;
    alter system set RESOURCE_LIMIT=true scope=spfile
    *
    ERROR at line 1:
    ORA-01565: error in identifying file
    'E:\APP\SERVERROOM\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    
    
    SQL> alter system set RESOURCE_LIMIT=true scope=memory;
    
    System altered.
    
    SQL> show parameter resource_limit;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    resource_limit                       boolean     TRUE
    SQL> alter system set RESOURCE_LIMIT=false scope=both;
    alter system set RESOURCE_LIMIT=false scope=both
    *
    ERROR at line 1:
    ORA-01565: error in identifying file
    'E:\APP\SERVERROOM\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    
    
    SQL> show parameter resource_limit;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    resource_limit                       boolean     FALSE
    SQL>
    So, if spfile dont exists and you says scope=spfile or scope=both, oracle will return ORA-01565. In scope=both case, parameter will be changed but only in memory, because since spfile don't exists, so oracle says ORA-01565.

    Regards
    Girish Sharma
  • 6. Re: pfile and spfile
    Padma.... Newbie
    Currently Being Moderated
    Hi,

    Your current instance is not effected because of non availability of spfile.

    But to have spfile for next startup run the command

    SQL>CREATE SPFILE FROM MEMORY

    Thanks
    Padma...
  • 7. Re: pfile and spfile
    EdStevens Guru
    Currently Being Moderated
    984433 wrote:
    Hi friends,

    I have one doubt. Suppose my Prod Database is up and accidentally my pfile deleted. what will happen to my prod database whether it wil fail the instance or any error please tell me. as per concept that i know pfile accessed when we nomount the database for initialization of database.(11g)


    in case of SPFILE

    Suppose my spfile deleted and previously my database was in up condition. Please tell me what will happen to my instance if i am working in a OLTP/DSS environment.(i dont have backup also how to recover and work in 11g if instance failed)

    Regards,
    Arya
    You could test it yourself.

    ============================================================================
    Every oracle professional (or student) should have their own private computer lab. I'm assuming in this day and age anyone in this category already has their own computer. Starting from there, you should do the following:

    1 - Go to oracle.com and download Virtual Box. Oracle freely distributes this so your cost so far is zero.
    1a - Install Virtual Box

    2 - Go to oracle.com and download Oracle Linux. Oracle freely distributes this, only charging if you want a support contract. As they also provide a free public yum server for distribution of most packages, even a support contract is not needed for your personal use.
    2a - using Virtual Box and your downloaded linux, create a virtual linux machine.

    3 - Go to oracle.com and download whatever db or related product you want. The terms of the oracle license agreement allow you the full use of any product for personal study.

    By doing the above I have a full computer lab running on my Windows 7 Home laptop, for a total cost of US$0.00.
    ============================================================================
  • 8. Re: pfile and spfile
    Aryacool Newbie
    Currently Being Moderated
    Hi Thanks for your nice demo and for error code

    One more doubt suppose my database is open and up if i have deleted my oracle_home or dbs then whether my database will remain open or it will show error or instance will fail.

    Thanks
    Arya
  • 9. Re: pfile and spfile
    sb92075 Guru
    Currently Being Moderated
    984433 wrote:
    Hi Thanks for your nice demo and for error code

    One more doubt suppose my database is open and up if i have deleted my oracle_home or dbs then whether my database will remain open or it will show error or instance will fail.

    Thanks
    Arya
    if the DB was started using spfile & then it gets removed, the DB will eventually CRASH when it tries to access the spfile.

    if the DB was started using pfile, since the DB never tries to access the pfile again, the DB stays up.
    If fact the DB does not know or retain any reference to the pfile used to start the DB.
  • 10. Re: pfile and spfile
    Aman.... Oracle ACE
    Currently Being Moderated
    Aryacool wrote:
    Hi Thanks for your nice demo and for error code

    One more doubt suppose my database is open and up if i have deleted my oracle_home or dbs then whether my database will remain open or it will show error or instance will fail.
    Since the dbs doesn't contain anything that's going to be used by the running instance, it would stay alive. I am not too sure about that if you are going to remove the entire oracle home, it would be still running or not. My guess, it shouldn't .

    Aman....
  • 11. Re: pfile and spfile
    Roshan Jose Newbie
    Currently Being Moderated
    Ldeleting the dbs may not matter at all but ORACLE_HOME definitely should not be deleted. If deleted, the database running on that home will not be able to run as the executables will be missing ending in crashing the database as well as destroying the home. Remember you should never cut paste or delete those files, maybe you can delete files in ORACLE_HOME/rdbms/admin as they are sql files, yet you will be unable to run utlrp and other scripts. But deleting files in home is a definite no.
  • 12. Re: pfile and spfile
    Roshan Jose Newbie
    Currently Being Moderated
    and if its on windows, then you are messing the registry, atleast you can save yourself in unix/linux.

Legend

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