1 2 Previous Next 17 Replies Latest reply: Jan 22, 2013 8:40 PM by user539415 RSS

    Problem with creating Pfile

    user539415
      Hello,

      My simple issued dragged me a few days but it looks simple i cannot solve it :(. I appreciate your idea to my probelm.

      I have issued to create pfile from spfile. When I run the create command it is done sucessfully but in my pfile content has only one parameter of *.SPfile='D:\app\oracle\product\11.2.0\dbhome_1\database\SPFILEORCLE1.ORA'

      I want to have a normal pfile with many parameters in the pfile that it can be changed.


      Thanks you.
        • 1. Re: Problem with creating Pfile
          sb92075
          user539415 wrote:
          Hello,

          My simple issued dragged me a few days but it looks simple i cannot solve it :(. I appreciate your idea to my probelm.

          I have issued to create pfile from spfile. When I run the create command it is done sucessfully but in my pfile content has only one parameter of *.SPfile='D:\app\oracle\product\11.2.0\dbhome_1\database\SPFILEORCLE1.ORA'

          I want to have a normal pfile with many parameters in the pfile that it can be changed.


          Thanks you.
          Issue commands EXACTLY as below

          cd D:\app\oracle\product\11.2.0\dbhome_1\database\
          dir /o *.ora
          sqlplus / as sysdba
          create pfile from spfile;
          exit
          dir /o *.ora


          COPY the results then PASTE all back here
          • 2. Re: Problem with creating Pfile
            user539415
            Thanks for your reply.

            C:\>dir cd D:\app\oracle\product\11.2.0\dbhome_1\database\*.ORA
            Volume in drive C is OS
            Volume Serial Number is 9AED-365A

            Directory of C:\

            File Not Found

            Volume in drive D is DATA
            Volume Serial Number is 2832-B575

            Directory of D:\app\oracle\product\11.2.0\dbhome_1\database

            12/22/2012 08:37 AM 26 INITorcle1-bk1.ORA
            01/22/2013 10:20 AM 32 INITorcle1.ORA
            01/21/2013 10:46 AM 76 INITorcle1_work.ORA
            01/15/2013 11:47 AM 1,536 PWDorcle1.ora
            12/20/2012 04:12 PM 9,846,784 SNCFORCL.ORA
            12/20/2012 03:55 PM 1,536 SPFILEORCL.ORA
            01/18/2013 02:11 PM 3,584 SPFILEORCLE1 - Copy.ORA
            01/22/2013 10:19 AM 1,536 SPFILEORCLE1.ORA
            8 File(s) 9,855,110 bytes
            0 Dir(s) 15,460,028,416 bytes free


            SQL> create pfile from spfile;

            File created.

            C:\>dir cd D:\app\oracle\product\11.2.0\dbhome_1\database\*.ORA
            Volume in drive C is OS
            Volume Serial Number is 9AED-365A

            Directory of C:\

            File Not Found

            Volume in drive D is DATA
            Volume Serial Number is 2832-B575

            Directory of D:\app\oracle\product\11.2.0\dbhome_1\database

            12/22/2012 08:37 AM 26 INITorcle1-bk1.ORA
            01/22/2013 10:34 AM 32 INITorcle1.ORA
            01/21/2013 10:46 AM 76 INITorcle1_work.ORA
            01/15/2013 11:47 AM 1,536 PWDorcle1.ora
            12/20/2012 04:12 PM 9,846,784 SNCFORCL.ORA
            12/20/2012 03:55 PM 1,536 SPFILEORCL.ORA
            01/18/2013 02:11 PM 3,584 SPFILEORCLE1 - Copy.ORA
            01/22/2013 10:19 AM 1,536 SPFILEORCLE1.ORA
            8 File(s) 9,855,110 bytes
            0 Dir(s) 15,449,485,312 bytes free
            • 3. Re: Problem with creating Pfile
              Catfive Lander
              Try **specifying** the file to be created.

              create pfile='c:\my-new-pfile.ora' from spfile;

              Honestly: you've got so many files sitting there, and I've no idea what's supposed to be new, old or inbetween. Just spell out the directory and name you want created, then there can't be confusion about which one is being written to where.
              • 4. Re: Problem with creating Pfile
                sb92075
                user539415 wrote:
                Thanks for your reply.

                C:\>dir cd D:\app\oracle\product\11.2.0\dbhome_1\database\*.ORA
                Volume in drive C is OS
                Volume Serial Number is 9AED-365A

                Directory of C:\

                File Not Found

                Volume in drive D is DATA
                Volume Serial Number is 2832-B575

                Directory of D:\app\oracle\product\11.2.0\dbhome_1\database

                12/22/2012 08:37 AM 26 INITorcle1-bk1.ORA
                01/22/2013 10:20 AM 32 INITorcle1.ORA
                above is the recently created pfile
                but I don't understand while it is so small; only 32 bytes
                01/21/2013 10:46 AM 76 INITorcle1_work.ORA
                01/15/2013 11:47 AM 1,536 PWDorcle1.ora
                12/20/2012 04:12 PM 9,846,784 SNCFORCL.ORA
                12/20/2012 03:55 PM 1,536 SPFILEORCL.ORA
                01/18/2013 02:11 PM 3,584 SPFILEORCLE1 - Copy.ORA
                01/22/2013 10:19 AM 1,536 SPFILEORCLE1.ORA
                8 File(s) 9,855,110 bytes
                0 Dir(s) 15,460,028,416 bytes free


                SQL> create pfile from spfile;

                File created.

                C:\>dir cd D:\app\oracle\product\11.2.0\dbhome_1\database\*.ORA
                Volume in drive C is OS
                Volume Serial Number is 9AED-365A

                Directory of C:\

                File Not Found

                Volume in drive D is DATA
                Volume Serial Number is 2832-B575

                Directory of D:\app\oracle\product\11.2.0\dbhome_1\database

                12/22/2012 08:37 AM 26 INITorcle1-bk1.ORA
                01/22/2013 10:34 AM 32 INITorcle1.ORA
                01/21/2013 10:46 AM 76 INITorcle1_work.ORA
                01/15/2013 11:47 AM 1,536 PWDorcle1.ora
                12/20/2012 04:12 PM 9,846,784 SNCFORCL.ORA
                12/20/2012 03:55 PM 1,536 SPFILEORCL.ORA
                01/18/2013 02:11 PM 3,584 SPFILEORCLE1 - Copy.ORA
                01/22/2013 10:19 AM 1,536 SPFILEORCLE1.ORA
                8 File(s) 9,855,110 bytes
                0 Dir(s) 15,449,485,312 bytes free
                • 5. Re: Problem with creating Pfile
                  sb92075
                  below is done on my *nix system
                  [oracle@localhost ~]$ cd $ORACLE_HOME/dbs
                  [oracle@localhost dbs]$ ls -ltr
                  total 40
                  -rw-r--r-- 1 oracle oracle 2851 May 15  2009 init.ora
                  -rw-rw---- 1 oracle oracle 1544 Oct  2  2010 hc_DBUA0.dat
                  -rw-r----- 1 oracle oracle   24 Oct  2  2010 lkORCL
                  -rw-r----- 1 oracle oracle 1536 Jan  6 08:10 orapworcl
                  -rw-r----- 1 oracle oracle 1118 Jan  7 08:22 initorcl.ora
                  -rw-rw-r-- 1 oracle oracle 8859 Jan  8 11:33 afiedt.buf
                  -rw-r----- 1 oracle oracle 3584 Jan 18 08:43 spfileorcl.ora
                  -rw-rw---- 1 oracle oracle 1544 Jan 18 08:43 hc_orcl.dat
                  [oracle@localhost dbs]$ sqlplus / as sysdba
                  
                  SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 21 20:32:37 2013
                  
                  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> exit
                  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  [oracle@localhost dbs]$ ls -ltr
                  total 40
                  -rw-r--r-- 1 oracle oracle 2851 May 15  2009 init.ora
                  -rw-rw---- 1 oracle oracle 1544 Oct  2  2010 hc_DBUA0.dat
                  -rw-r----- 1 oracle oracle   24 Oct  2  2010 lkORCL
                  -rw-r----- 1 oracle oracle 1536 Jan  6 08:10 orapworcl
                  -rw-rw-r-- 1 oracle oracle 8859 Jan  8 11:33 afiedt.buf
                  -rw-r----- 1 oracle oracle 3584 Jan 18 08:43 spfileorcl.ora
                  -rw-rw---- 1 oracle oracle 1544 Jan 18 08:43 hc_orcl.dat
                  -rw-r----- 1 oracle oracle 1118 Jan 21 20:32 initorcl.ora
                  Notice that my new pfile (initorcl.ora) is freshly made & in excess of 1000 bytes
                  • 6. Re: Problem with creating Pfile
                    baskar.l
                    Hi,
                    My simple issued dragged me a few days but it looks simple i cannot solve it . I appreciate your idea to my probelm.
                    
                    I have issued to create pfile from spfile. When I run the create command it is done sucessfully but in my pfile content has only one parameter of *.SPfile='D:\app\oracle\product\11.2.0\dbhome_1\database\SPFILEORCLE1.ORA'
                    
                    I want to have a normal pfile with many parameters in the pfile that it can be changed.
                    Is this a RAC database? Because in RAC DB the pfile in OH/rdbms will have only one entry like you have posted SPFILE=....

                    Baskar.l
                    • 7. Re: Problem with creating Pfile
                      Catfive Lander
                      Good call. Given the '1' in the file name, I'd say you'd nailed this in one.
                      • 8. Re: Problem with creating Pfile
                        user539415
                        There is no oracle rac.
                        • 9. Re: Problem with creating Pfile
                          user539415
                          Hi Catfive Lander

                          Please explain futher to your previous reply. I recall the originally i create pfile map to spfile location under drive D:/ than this issue start from that day.
                          • 10. Re: Problem with creating Pfile
                            user539415
                            SQL> create pfile='c:\my-new-pfile.ora' from spfile;

                            File created.

                            SQL>

                            same result the content of 'c:\my-new-pfile.ora file contains only *.SPfile='D:\SPFILEORCLE1.ORA'

                            i don think the location of created pfile is giving this kind of problem, but the parameter in spfile that we use to create pfile is causing this issue. my idea only. pls correct me.
                            • 11. Re: Problem with creating Pfile
                              Girish Sharma
                              What happens if you says :
                              SQL> create pfile='c:\mypfile.txt' from spfile='D:\app\oracle\product\11.2.0\dbhome_1\database\SPFILEORCLE1.ORA';
                              and now check what is in c:\mypfile.txt ?

                              Regards
                              Girish Sharma
                              • 12. Re: Problem with creating Pfile
                                user539415
                                Oh it still give same resoult

                                *.SPfile='D:\SPFILEORCLE1.ORA'
                                • 13. Re: Problem with creating Pfile
                                  Azar
                                  show parameter spfile ?
                                  • 14. Re: Problem with creating Pfile
                                    user539415
                                    More info for your advise to my problem. pls

                                    SQL> show parameter spfile

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    spfile string D:\APP\ORACLE\PRODUCT\11.2.0\D
                                    BHOME_1\DATABASE\SPFILEORCLE1.
                                    ORA
                                    SQL> select name,value from v$spparameter
                                    2 where name='spfile';

                                    NAME -------VALUE------------

                                    spfile D:\SPFILEORCLE1.ORA



                                    SQL> select name,value from v$system_parameter
                                    2 where name='spfile';

                                    NAME
                                    --------------------------------------------------------------------------------

                                    VALUE
                                    --------------------------------------------------------------------------------

                                    spfile
                                    D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCLE1.ORA


                                    SQL>
                                    1 2 Previous Next