5 Replies Latest reply on Aug 26, 2014 11:55 PM by sulimo

    How to create spfile from pfile in Oracle 11.2.0.4?

    2625331

      Hi experts:

       

      We have 11.2.0.4 on Linux.

      - I set the compatible = 11.2.0 in the pfile. I installed 11.2.0.4 software. What compatible parameter I should set?

       

      - We use spfile file. A developer wants me to change the compatible to 11.2.0.4. I did in the pfile and bounced the database. When I startup database I used startup pfile=zzzz.ora. But when I did "show parameter compatible" it still shows 11.2.0. Is this because the spfile has not changed?

       

      - How to create a spfile from pfile in my case?

       

      Thank you very much for your help.

        • 1. Re: How to create spfile from pfile in Oracle 11.2.0.4?

          [oracle@localhost ~]$ sqlplus / as sysdba

           

           

          SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 25 11:16:30 2014

           

           

          Copyright (c) 1982, 2010, Oracle.  All rights reserved.

           

           

           

           

          Connected to:

          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

          With the Partitioning, OLAP, Data Mining and Real Application Testing options

           

           

          SQL> create pfile from spfile;

           

           

          File created.

           

           

          SQL> create spfile from pfile;

          create spfile from pfile

          *

          ERROR at line 1:

          ORA-32002: cannot create SPFILE already being used by the instance

           

           

           

           

          SQL> shutdown immediate

          Database closed.

          Database dismounted.

          ORACLE instance shut down.

          SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora

          ORACLE instance started.

           

           

          Total System Global Area  456146944 bytes

          Fixed Size                  1344840 bytes

          Variable Size             339741368 bytes

          Database Buffers          109051904 bytes

          Redo Buffers                6008832 bytes

          Database mounted.

          Database opened.

          SQL> create spfile from pfile;

           

           

          File created.

           

           

          SQL>

          1 person found this helpful
          • 2. Re: How to create spfile from pfile in Oracle 11.2.0.4?
            2625331

            After shutdown you have to remove the existing spfile (Did you not?) Then can issue create spfile from pfile.

            • 3. Re: Re: How to create spfile from pfile in Oracle 11.2.0.4?
              EdStevens

              2625331 wrote:

               

              After shutdown you have to remove the existing spfile (Did you not?) Then can issue create spfile from pfile.

              No, you do not have to first remove the spfile.  You simply have to not be using it at the moment ... not have done a startup with it.

               

               

               

              oracle:orcl$

              oracle:orcl$ sqlplus / as sysdba

               

              SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 26 06:56:03 2014

               

              Copyright (c) 1982, 2009, Oracle.  All rights reserved.

               

              Connected to an idle instance.

               

              Look at the timestamps of the existing files

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug  8 06:42 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 15 12:03 spfileorcl.ora

               

              SQL> create pfile from spfile;

               

              File created.

               

              Compare the timestamps to the previous

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 06:56 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 15 12:03 spfileorcl.ora

               

              SQL> create spfile from pfile;

               

              File created.

               

              Hmm.  I created an spfile without removing the previous.

               

              Compare the timestamps to the previous

               

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 06:56 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 26 06:56 spfileorcl.ora

               

              Now startup using the pfile

               

               

              SQL> startup /nomount pfile='initorcl.ora';

              ORACLE instance started.

               

              Total System Global Area  835104768 bytes

              Fixed Size    2217952 bytes

              Variable Size  503318560 bytes

              Database Buffers  327155712 bytes

              Redo Buffers    2412544 bytes

              SQL> show parameter spfile;

               

              NAME     TYPE VALUE

              ------------------------------------ ----------- ------------------------------

              spfile     string


               

              And create a new spfile

               

               


              SQL> create spfile from pfile;

               

              File created.

               

               

              Again, we created an spfile without removing the previous one

              Compare the timestamps to the previous

               

               

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 06:56 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 26 06:57 spfileorcl.ora

               

              restart, using the spfile

               

               

              SQL> shutdown immediate

              ORA-01507: database not mounted

               

               

              ORACLE instance shut down.

              SQL> startup nomount

              ORACLE instance started.

               

              Total System Global Area  835104768 bytes

              Fixed Size    2217952 bytes

              Variable Size  503318560 bytes

              Database Buffers  327155712 bytes

              Redo Buffers    2412544 bytes

              SQL> show parameter spfile;

               

              NAME     TYPE VALUE

              ------------------------------------ ----------- ------------------------------

              spfile     string /u01/app/oracle/product/11.2.0

              /db_1/dbs/spfileorcl.ora


               

              Now try to create the spfile, while it is actually in use

               

               


              SQL> create spfile from pfile;

              create spfile from pfile

              *

              ERROR at line 1:

              ORA-32002: cannot create SPFILE already being used by the instance

               

               

              SQL>

               

               

              Actually, you do not even have to have *started* the database at all to create either from the other.

               

              oracle:orcl$ sqlplus / as sysdba

               

              SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 26 07:06:43 2014

               

              Copyright (c) 1982, 2009, Oracle.  All rights reserved.

               

              Connected to an idle instance.

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 06:56 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 26 06:57 spfileorcl.ora

               

              SQL> create pfile from spfile;

               

              File created.

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 07:07 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 26 06:57 spfileorcl.ora

               

              SQL> create spfile from pfile;

               

              File created.

               

              SQL> !ls -l *.ora

              -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

              -rw-r--r-- 1 oracle oinstall  958 Aug 26 07:07 initorcl.ora

              -rw-r----- 1 oracle oinstall 2560 Aug 26 07:07 spfileorcl.ora

               

              SQL>

              1 person found this helpful
              • 4. Re: How to create spfile from pfile in Oracle 11.2.0.4?
                2625331

                Thank you very much for your detailed demo. Now I know I can use "create..." when the database is idle. I will try your way next time.

                • 5. Re: How to create spfile from pfile in Oracle 11.2.0.4?
                  sulimo

                  Hello...

                  I set the compatible = 11.2.0 in the pfile. I installed 11.2.0.4 software. What compatible parameter I should set?

                  Mind you don't necessarily have to set it. If you staying with 11.2.0, just reset it. Particularly if you want to avoid ORA-00201 (incompatible control file version)

                   

                  We use spfile file. A developer wants me to change the compatible to 11.2.0.4. I did in the pfile and bounced the database. When I startup database I used startup pfile=zzzz.ora. But when I did "show parameter compatible" it still shows 11.2.0. Is this because the spfile has not changed?

                  If you started up using pfile=zzzz.ora and zzzz.ora is the file you edited, it should be there. Just make sure you used the same file you edited.


                  How to create a spfile from pfile in my case?

                  This was very well explained in previous threads.
                  My 50c, remember in in 11g you can also create spfile from memory

                   

                  cheers