12 Replies Latest reply: Oct 8, 2010 6:22 AM by 618702 RSS

    Creating spfile

    malhi
      I am asking this for my knowledge, if i lost both spfile and pfile. How can i start oracle. If you can provide link containing information regarding this i shall be thank full.
        • 1. Re: Creating spfile
          Chinar
          malhi wrote:
          I am asking this for my knowledge, if i lost both spfile and pfile. How can i start oracle.
          If you lost spfile and pfile and you do not have backup of spfile then you have to try manually create pfile according sample pfile($ORACLE_HOME/admin/sample/pfile/initsmpl.ora) ,finally try start database.
          In additionally see alert.log there you will see initialization parameter`s values and use these values.

          Edited by: Chinar on Oct 7, 2010 5:03 AM
          • 2. Re: Creating spfile
            618702
            Dear malhi,

            If i am not wrong, only set the control_files and sga_max_size parameters in a pfile and try to start that instance with that pfile.
            SQL> startup pfile='/home/oracle/sample.ora';
            Regards.

            Ogan
            • 3. Re: Creating spfile
              779621
              Hi,

              If you lost both the files and moreover you do not have backup ,

              Refer Path :- $ORACLE_HOME\admin\sample\pfile\initsmpl

              To find out the non-default values, You can always refer the Alert Log file ,where you will get the list of all non-default paramaters
              • 4. Re: Creating spfile
                Billy~Verreynne
                malhi wrote:
                I am asking this for my knowledge, if i lost both spfile and pfile. How can i start oracle. If you can provide link containing information regarding this i shall be thank full.
                One option is to always tick the "+generate database create scripts+" option of dbca when creating the database. In the +$ORACLE_BASE/admin/$ORACLE_SID/scripts+ directory you will have the db create shell and SQL scripts.. together with the original pfile used to create the database. This should suffice in starting the database, updating its settings, and creating a new spfile.

                Another option is to always include the spfile with your rman backups.

                If the spfile is "corrupted" (logically via an errant setting), you can also make a copy of that spfile, and use a text editor (like Linux vim) to turn it into a pfile. You can then fix or remove the problem setting and use that pfile to fire up the instance. If the spfile is on an ASM volume, you can use asmcmd (11g and later) to copy it to a local file system for editing and use.

                Of all the database files to loose, a pfile or spfile is one of the "+better+" (less painful) ones to loose. ;-)
                • 5. Re: Creating spfile
                  askraks
                  Hi,


                  If the you lost pfile or spfile while the db is running, then you can use

                  sql>create pfile from spfile

                  or

                  lets assume db is down

                  Open the alert log.

                  you will see the all the information related to pfile

                  now copy and paste the alertlog contents to one text file pfile.ora

                  sqlplus / as sysdba
                  sql>startup pfile=pfile.ora


                  Kind Regards,
                  Rakesh Jayappa
                  • 6. Re: Creating spfile
                    malhi
                    In the $ORACLE_BASE/admin/$ORACLE_SID/scripts directory you will have the db create shell and SQL scripts.. together with the original pfile used to create the database. This should suffice in starting the database, updating its settings, and creating a new spfile.
                    In my system $ORACLE_BASE/admin/$ORACLE_SID/scripts folder do not exists, rather it has $ORACLE_BASE/admin/$ORACLE_SID/pfile folder, which contains init.ora.332010163123 file, shown below. Is this can serve the purpose...........

                    oracle@tariq-desktop:~$ cd $ORACLE_BASE/admin/tmdb
                    oracle@tariq-desktop:/u01/app/oracle/admin/tmdb$ ls -l
                    total 44
                    drwxr-x--- 2 oracle oinstall 36864 2010-10-08 11:55 adump
                    drwxr-x--- 2 oracle oinstall 4096 2010-04-03 16:21 dpdump
                    drwxr-x--- 2 oracle oinstall 4096 2010-04-03 16:31 pfile
                    oracle@tariq-desktop:/u01/app/oracle/admin/tmdb$ cat pfile/
                    cat: pfile/: Is a directory
                    oracle@tariq-desktop:/u01/app/oracle/admin/tmdb$ cd pfile/
                    oracle@tariq-desktop:/u01/app/oracle/admin/tmdb/pfile$ cat init.ora.332010163123
                    ##############################################################################
                    # Copyright (c) 1991, 2001, 2002 by Oracle Corporation
                    ##############################################################################

                    ###########################################
                    # Cache and I/O
                    ###########################################
                    db_block_size=8192

                    ###########################################
                    # Cursors and Library Cache
                    ###########################################
                    open_cursors=300

                    ###########################################
                    # Database Identification
                    ###########################################
                    db_domain=""
                    db_name=tmdb

                    ###########################################
                    # File Configuration
                    ###########################################
                    control_files=("/u01/app/oracle/oradata/tmdb/control01.ctl", "/u01/app/oracle/flash_recovery_area/tmdb/control02.ctl")
                    db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
                    db_recovery_file_dest_size=4039114752

                    ###########################################
                    # Miscellaneous
                    ###########################################
                    compatible=11.2.0.0.0
                    diagnostic_dest=/u01/app/oracle
                    memory_target=416284672

                    ###########################################
                    # Processes and Sessions
                    ###########################################
                    processes=150

                    ###########################################
                    # Security and Auditing
                    ###########################################
                    audit_file_dest=/u01/app/oracle/admin/tmdb/adump
                    audit_trail=db
                    remote_login_passwordfile=EXCLUSIVE

                    ###########################################
                    # Shared Server
                    ###########################################
                    dispatchers="(PROTOCOL=TCP) (SERVICE=tmdbXDB)"

                    ###########################################
                    # System Managed Undo and Rollback Segments
                    ###########################################
                    undo_tablespace=UNDOTBS1
                    • 7. Re: Creating spfile
                      695836
                      Will it work when both pfile and spfile are lost?
                      sql>create pfile from spfile
                      • 8. Re: Creating spfile
                        618702
                        Dear Yasir Hashmi,

                        I think it is better to show Oracle which pfile you are talking about.
                        SQL> CREATE PFILE='/home/oracle/sample.ora' FROM SPFILE;
                        I think it will not work if you don't have the spfile. If you have the spfile, yes the pfile will be created from the spfile.

                        Regards.

                        Ogan
                        • 9. Re: Creating spfile
                          695836
                          Dear Ogan,
                          I was actually asking the question to Rakesh on his comment:
                          Hi,
                          
                          If the you lost pfile or *spfile* while the db is running, then you can use
                          
                          sql>create pfile from spfile 
                          If we lost spfile then surely it will not work.
                          • 10. Re: Creating spfile
                            618702
                            Yasir Hashmi,

                            Yes, you are right. Please see the following illustration;
                            $ sqlplus / as sysdba
                            
                            SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 8 14:10:09 2010
                            
                            Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                            
                            
                            Connected to:
                            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            
                            SQL> show parameter spfile;
                            
                            NAME                                 TYPE
                            ------------------------------------ --------------------------------
                            VALUE
                            ------------------------------
                            spfile                               string
                            /opt/oracle/product/10.2.0/db_
                            1/dbs/spfileopttest.ora
                            SQL> exit
                            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            $ cd /opt/oracle/product/10.2.0/db_1/dbs/
                            $ cp spfileopttest.ora ../spfileopttest.ora
                            $ sqlplus / as sysdba
                            
                            SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 8 14:10:52 2010
                            
                            Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                            
                            
                            Connected to:
                            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            
                            SQL> shutdown abort
                            ORACLE instance shut down.
                            SQL> exit
                            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options
                            $ pwd
                            /opt/oracle/product/10.2.0/db_1/dbs
                            $ ls -lrt *spfileopttest.ora*
                            -rw-r-----   1 oracle     oinstall      5120 Oct  8 05:00 spfileopttest.ora
                            $ rm spfileopttest.ora
                            $ sqlplus / as sysdba
                            
                            SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 8 14:11:13 2010
                            
                            Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                            
                            Connected to an idle instance.
                            
                            SQL> create pfile from spfile;
                            create pfile from spfile
                            *
                            ERROR at line 1:
                            ORA-27037: unable to obtain file status
                            HPUX-ia64 Error: 2: No such file or directory
                            Additional information: 3
                            
                            
                            SQL>
                            Regards.

                            Ogan
                            • 11. Re: Creating spfile
                              695836
                              Ogan,
                              Thanks for the demo.I know it will not work. But sice Rakesh wrote it so I was asking him that will it really work?Are you sure? sort of thing.
                              • 12. Re: Creating spfile
                                618702
                                Dear Yasir Hashmi,

                                I think he mentioned something like that (for your information); If you lost the spfile, you can create it from the pfile. On the other hand, if you lost the pfile and if you are using spfile in the current instance, you don't have to worry because you can simply create one.

                                I think he could not tell you what he wanted to say but i am waiting for his answer either :)

                                Anyway i am leaving as a parasite between you guys :)

                                Regards.

                                Ogan