12 Replies Latest reply: Jan 28, 2013 3:26 PM by Roshan Jose RSS

    pfile and spfile

    Aryacool
      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
          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
            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
              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
                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
                  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....
                    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
                      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
                        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
                          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....
                            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
                              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
                                and if its on windows, then you are messing the registry, atleast you can save yourself in unix/linux.