This discussion is archived
12 Replies Latest reply: Oct 8, 2010 4:22 AM by 618702 RSS

Creating spfile

malhi Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Will it work when both pfile and spfile are lost?
    sql>create pfile from spfile
  • 8. Re: Creating spfile
    618702 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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