1 2 Previous Next 21 Replies Latest reply on May 9, 2010 6:24 PM by sb92075

    start database with spfile

    683218
      Hi,

      How could i start database using spfile permanently.In our case it always start database using pfile while starting startup command.

      Thanks,
        • 1. Re: start database with spfile
          asifkabirdba
          Check that weather you using spfile or not.


          SQL> show user;
          USER is "SYS"
          SQL> show parameters spfile

          NAME TYPE VALUE
          ------------------------------------ ----------- ------------------------------
          spfile string /oracle/product/102/dbs/spfilesyslog.ora


          If your spfile parameter returns a value then your database has started with spfile.


          Otherwise log in as sysdba

          then create a spfile from your pfile.

          Shutdown and startup the database by spfile.


          SQL > create spfile from pfile;

          You will find it on $ORACLE_HOME/dbs/init<sid>.ora


          Regards
          Asif Kabir

          Total Questions: *29 (29 unresolved)*

          Mark your helpful post as correct/helpful
          • 2. Re: start database with spfile
            MsJ
            $>create spfile from pfile

            or

            $>create spfile from pfile=/oracle/dbs/initsid.ora

            $>startup

            it always uses spfile.


            $>SQLPLUS /NOLOG
            SQL>shutdown immediate
            SQL>startup

            SQL>SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Initial file type" FROM sys.v_$parameter WHERE name = 'spfile';
            • 3. Re: start database with spfile
              683218
              Hi,

              I have done same but it starts sometime but after take pfile.

              Thanks
              • 4. Re: start database with spfile
                Oracle expects the spfile in $ORACLE_HOME/dbs
                It will first search for the pfile, if the pfile is not there, it will use the spfile.
                The trick is to have an 1-line pfile
                reading
                spfile=<name of your spfiile>

                Obviously this is documented.

                ----------------
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: start database with spfile
                  asifkabirdba
                  After creating the spfile.

                  1. shutdown immediate

                  2. startup


                  It will always use the spfile.


                  Regards
                  Asif Kabir
                  • 6. Re: start database with spfile
                    Lubiez Jean-Valentin
                    Hello,


                    Normally, the database uses SPFILE<SID>.ora in priority.

                    So, you may check that the SPFILE exists in *$ORACLE_HOME/dbs* (for UNIX) or ORACLE_HOME\database (for Windows).

                    Once the SPFILE is created, you can always move the old init<SID>.ora to another directory, so as to be sure that the SPFILE is the only parameter file used.

                    However, you should always keep the init<SID>.ora in a safe place, as a Backup of the SPFILE.

                    Please find enclosed, a link about PFILE/SPFILE:

                    http://www.dba-oracle.com/concepts/pfile_spfile.htm


                    Hope this help.
                    Best regards,
                    Jean-Valentin

                    Edited by: Lubiez Jean-Valentin on May 9, 2010 2:51 PM
                    • 7. Re: start database with spfile
                      683218
                      Hi Asif,

                      No its not like that in our case I have done same many time.Right now database is started from pfile whereas spfile is located there(dbs).

                      I did try to validate it using rman command to check and also it gave below error.

                      RMAN> backup validate check logical spfile;

                      Starting backup at 09-MAY-10
                      using channel ORA_SBT_TAPE_1
                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-03002: failure of backup command at 05/09/2010 13:15:40
                      RMAN-06062: can not backup SPFILE because the instance was not started with SPFILE


                      Thanks,
                      • 8. Re: start database with spfile
                        683218
                        Hi,

                        Oracle expects the spfile in $ORACLE_HOME/dbs.It will first search for the pfile, if the pfile is not there, it will use the spfile.
                        --spfile and pfile are located at $ORACLE_HOME/dbs, As per your update i have to move pfile at some other location?

                        Thanks,
                        • 9. Re: start database with spfile
                          sb92075
                          ALWAYS
                          Post Operating System (OS) name & version for DB server system.
                          Post results of
                          SELECT * from v$version

                          If Windows post results from command below

                          SET

                          If *NIX post results from command below

                          env | sort
                          • 10. Re: start database with spfile
                            683218
                            Hi,

                            AIX 5.3
                            DB 10.2.0.4

                            Thanks
                            • 11. Re: start database with spfile
                              sb92075
                              if you choose to not answer my requests, then I choose to ignore your problem
                              • 12. Re: start database with spfile
                                683218
                                $ env | sort
                                AIXTHREAD_SCOPE=S
                                AUTHSTATE=compat
                                A__z=! LOGNAME
                                ENV_HOME=/vol01/project_oracle/common/etc
                                HOME=/home/oracle
                                LANG=en_US
                                LC__FASTMSG=true
                                LDPATH=/vol01/app/oracle/product/db/10g/lib:/vol02/rms/dbserver/rms/oracle/lib/b
                                in
                                LD_LIBRARY_PATH=/vol01/app/oracle/product/db/10g/lib:/vol02/rms/dbserver/rms/ora
                                cle/lib/bin
                                LIBPATH=/vol01/app/oracle/product/db/10g/lib:/vol02/rms/dbserver/rms/oracle/lib/
                                bin:/vol01/app/oracle/product/db/10g/lib:/vol02/rms/dbserver/rms/oracle/lib/bin
                                LINK_CNTRL=L_PTHREADS_D7
                                LOCPATH=/usr/lib/nls/loc
                                LOGIN=oracle
                                LOGNAME=oracle
                                MAIL=/usr/spool/mail/oracle
                                MAILMSG=[YOU HAVE NEW MAIL]
                                MMHOME=/vol02/rms/dbserver/rms
                                MMUSER=rms12
                                NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
                                OBJECT_MODE=64
                                ODMDIR=/etc/objrepos
                                ORACLE_HOME=/vol01/app/oracle/product/db/10g
                                ORACLE_SID=rms
                                PASSWORD=ahfr_rms
                                PATH=/vol01/app/oracle/product/db/10g/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home
                                /oracle/bin:/usr/bin/X11:/sbin:.:
                                PWD=/tmp
                                SHELL=/usr/bin/ksh
                                TERM=ansi
                                TZ=SAUST-3
                                USER=oracle
                                _=/usr/bin/env
                                $
                                • 13. Re: start database with spfile
                                  sb92075
                                  COPY lines below & PASTE into terminal window

                                  date
                                  uname -a
                                  ls -ltr $ORACLE_HOME/dbs
                                  uptime
                                  date

                                  COPY commands & results then PASTE all back here
                                  • 14. Re: start database with spfile
                                    Hemant K Chitale
                                    Check your database startup script.

                                    A default "STARTUP" command would look for the spfile / pfile in $ORACLE_HOME/dbs.

                                    However, if the script issues a "STARTUP PFILE=..location_of_file" then it would ignore the default locations and would ignore the presence of an spfile. It would use the PFILE specified in the startup command.


                                    Hemant K Chitale
                                    1 2 Previous Next