This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 9, 2012 3:31 AM by user9808 RSS

ORA-00845: pfile is working but not spfile

user9808 Newbie
Currently Being Moderated
I have a 11.2.0.2 database. When I startup the database using the spfile, I got the ora-00845 error (MEMORY_TARGET not supported on this system). Then I tried to startup the database using the pfile. It worked! No problem. Then I created a new spfile using the current pfile. Shutdown the database and tried to startup using the spfile again. No luck. I still have this error. Why would the pfile work and not the spfile?

Thanks
  • 1. Re: ORA-00845: pfile is working but not spfile
    sb92075 Guru
    Currently Being Moderated
    user10427867 wrote:
    I have a 11.2.0.2 database. When I startup the database using the spfile, I got the ora-00845 error (MEMORY_TARGET not supported on this system). Then I tried to startup the database using the pfile. It worked! No problem. Then I created a new spfile using the current pfile. Shutdown the database and tried to startup using the spfile again. No luck. I still have this error. Why would the pfile work and not the spfile?

    Thanks
    post full directory listing (including file creation date) where pfile & spfile reside
  • 2. Re: ORA-00845: pfile is working but not spfile
    907884 Explorer
    Currently Being Moderated
    Recreate pfile from spfile and try to start it.. mention full path of pfile while starting

    startup pfile='<Full Path'
  • 3. Re: ORA-00845: pfile is working but not spfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    Post the PFILE from which you created the second SPFILE.

    Please also verify that you dropped or renamed the original SPFILE so you know the system is not still looking at that file.
  • 4. Re: ORA-00845: pfile is working but not spfile
    DineshKC Newbie
    Currently Being Moderated
    Looks like for some reason database is not using the correct spfile (same created form pfile). As far as the error message is concerned you need to tweak your kernel parameters as you shared memory file system is not big enough to accommodate the size you have assigned for MEMORY_TARGET. Look at the value of other memory components in pfile. Probably MEMORY_TARGET parameter is not being used in pfile.
  • 5. Re: ORA-00845: pfile is working but not spfile
    stuartu Explorer
    Currently Being Moderated
    Please also post the error you are getting, as we are not able to see your screen output from our locations.
  • 6. Re: ORA-00845: pfile is working but not spfile
    user9808 Newbie
    Currently Being Moderated
    I cannot create pfile from spfile since I can't bring the database up using the spfile. I can bring up the database using the pfile, then I recreated the spfile from pfile. I do see the timestamp on the spfile has been updated to be the current time. However, I can't startup the db using the spfile. How does oracle know the location of the spfile since I can't bring the database up using the spfile. I know that if the db is up using the spfile, when I do "show parameter spfile" then it will provide me with the full path to the spfile. However, since I am bringing up the db using pfile, "show parameter spfile" is emptied.
  • 7. Re: ORA-00845: pfile is working but not spfile
    user9808 Newbie
    Currently Being Moderated
    Both spfile and pfile reside in $ORACLE_HOME/dbs directory.
  • 8. Re: ORA-00845: pfile is working but not spfile
    sb92075 Guru
    Currently Being Moderated
    user10427867 wrote:
    Both spfile and pfile reside in $ORACLE_HOME/dbs directory.
    post results from following OS command

    ls -ltr $ORACLE_HOME/dbs


    pfile can be made while DB is DOWN!
    11:22:17 SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    11:22:36 SQL> create pfile from spfile;
    
    File created.
    
    11:22:46 SQL> startup
    ORACLE instance started.
    
    Total System Global Area 1553305600 bytes
    Fixed Size              2213656 bytes
    Variable Size            922749160 bytes
    Database Buffers       620756992 bytes
    Redo Buffers              7585792 bytes
    Database mounted.
    Database opened.
    11:23:00 SQL> 
  • 9. Re: ORA-00845: pfile is working but not spfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    Please slow down ... some of the advice you are getting is just plain dangerous.

    First ... find the locations of the currently used PFILE and SPFILE. You do not need the database open to do so. Just log in as SYS and do the following:
    SQL> STARTUP NOMOUNT;
    
    SQL> show parameter spfile
    This page may help you:
    http://www.morganslibrary.org/reference/init_dot_ora.html

    Second ... make a safe backup copy of both files.

    Third ... you do not need to make a PFILE ... the problem is your SPFILE. So rename the current SPFILE and try creating a new one.
    Check to see if the new file shows up in the correct location.
    If things do not start ... as I've already requested ... post the PFILE.

    Seriously ... to some of you trying to help this person ... think through your advice. You could make things worse not better.
  • 10. Re: ORA-00845: pfile is working but not spfile
    617785 Explorer
    Currently Being Moderated
    Just as an aside, it is not even necessary to start the instance to NOMOUNT to create spfiles and init files - only that ORACLE_SID be set and you be "connected" (to an idle instance) as SYSDBA:

    Go to the directory where the init files live.
    [oracle@vmlnx01 ~]$ clear
    [oracle@vmlnx01 ~]$ cd $ORACLE_HOME/dbs
    Check ORACLE_SID
    [oracle@vmlnx01 dbs]$ echo $ORACLE_SID
    HOUSTON
    List the files
    [oracle@vmlnx01 dbs]$ ls -l
    total 6960
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-r--r-- 1 oracle oinstall   12920 May  3  2001 initdw.ora
    -rw-r----- 1 oracle oinstall    8385 Sep 11  1998 init.ora
    -rw-r--r-- 1 oracle oinstall    1567 May 22  2011 initSMYRNA.ora
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    -rw-r----- 1 oracle oinstall    4608 Jan  5 15:00 spfileHOUSTON.ora
    Better, list only those associated with ORACLE_SID
    [oracle@vmlnx01 dbs]$ LSls -l *$ORACLE_SID*
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    -rw-r----- 1 oracle oinstall    4608 Jan  5 15:00 spfileHOUSTON.ora
    Connect as sysdba. Notice we have an idle instance.
    [oracle@vmlnx01 dbs]$ SQLPLUsqlplus / as sysdba
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 5 15:09:44 2012
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    Connected to an idle instance.
    Now create a pfile. Notice we did not start the instance, not even to NOMOUNT
    SQL> create pfile='initHOUSTON.ora' from spfile;
    
    File created.
    Check the results, notice we now have the created init file:
    SQL> !ls -l *$ORACLE_SID$ *
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-r--r-- 1 oracle oinstall    1689 Jan  5 15:10 initHOUSTON.ora
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    -rw-r----- 1 oracle oinstall    4608 Jan  5 15:00 spfileHOUSTON.ora
    Remove the spfile and check the results:
    SQL> !rm spfileHOUSTON.ora
    
    SQL> !ls -l *$ORACLE_SID*
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-r--r-- 1 oracle oinstall    1689 Jan  5 15:10 initHOUSTON.ora
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    recreate the spfile file from the pfile and check the results
    SQL> create spfile from pfile='initHOUSTON.ora';
    
    File created.
    
    SQL> !ls -l *$ORACLE_SID*
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-r--r-- 1 oracle oinstall    1689 Jan  5 15:10 initHOUSTON.ora
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    -rw-r----- 1 oracle oinstall    4608 Jan  5 15:11 spfileHOUSTON.ora
    Remove the init file and check the results
    SQL> !rm initHOUSTON.ora
    
    SQL> !ls -l *$ORACLE_SID*
    -rw-rw---- 1 oracle oinstall    1544 May 21  2011 hc_HOUSTON.dat
    -rw-rw---- 1 oracle oinstall      24 May 21  2011 lkHOUSTON
    -rw-r----- 1 oracle oinstall    1536 May 21  2011 orapwHOUSTON
    -rw-r----- 1 oracle oinstall 7061504 May 22  2011 snapcf_HOUSTON.f
    -rw-r----- 1 oracle oinstall    4608 Jan  5 15:11 spfileHOUSTON.ora
    Start the instance and check that it used the spfile
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  285212672 bytes
    Fixed Size    1267068 bytes
    Variable Size   92277380 bytes
    Database Buffers  188743680 bytes
    Redo Buffers    2924544 bytes
    Database mounted.
    Database opened.
    SQL> show parameter spfile;
    
    NAME     TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    spfile     string /ora00/app/oracle/product/10.2
     .0/db_1/dbs/spfileHOUSTON.ora
    SQL> exit
  • 11. Re: ORA-00845: pfile is working but not spfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    Please re-read this from the OP's initial post
    Then I tried to startup the database using the pfile. It worked! No problem.
    I'm lost as to why you are advising to create a pfile ... the pfile is fine. The best case scenario is you are going to overwrite that good file with junk.
  • 12. Re: ORA-00845: pfile is working but not spfile
    EdStevens Guru
    Currently Being Moderated
    damorgan wrote:
    Please re-read this from the OP's initial post
    Then I tried to startup the database using the pfile. It worked! No problem.
    I'm lost as to why you are advising to create a pfile ... the pfile is fine. The best case scenario is you are going to overwrite that good file with junk.
    Dan,

    I wasn't advising to create a pfile -- at least that wasn't my intent. Just pointing out that when doing so, the CREATE PFILE and CREATE SPFILE commands do not require the instance to be started. Invariably, when these commands are advised, it is qualified with "with the database up" or "with the database in MOUNT", or . . . which is not necessary.

    I guess in retrospect my aside could be seen as an endorsement of the advise to create the pfile, but that wasn't my intent.

    - Ed
  • 13. Re: ORA-00845: pfile is working but not spfile
    user9808 Newbie
    Currently Being Moderated
    DB is started fine with pfile. Then I created a new spfile from spfile. When I started up using spfile >startup I still get ORA-00845. Maybe its not looking at the correct spfile, not sure why would it though.

    TEST.__db_cache_size=2348810240
    TEST.__java_pool_size=16777216
    TEST.__large_pool_size=16777216
    TEST.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
    TEST.__pga_aggregate_target=2030043136
    TEST.__sga_target=3019898880
    TEST.__shared_io_pool_size=0
    TEST.__shared_pool_size=603979776
    TEST.__streams_pool_size=0
    *.audit_file_dest='/opt/oracle/admin/test/adump'
    *.audit_sys_operations=TRUE
    *.audit_trail='OS'
    *.compatible='11.2.0.0.0'
    *.control_files='/opt/oracle/oradata/test/control01.ctl','/opt/oracle/oradata/test/control02.ctl','/oracle/oradata/test/control03.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='test'
    *.db_recovery_file_dest='/opt/oracle/fast_recovery_area'
    *.db_recovery_file_dest_size=4227858432
    *.diagnostic_dest='/opt/oracle'
    *.dispatchers=''
    *.log_archive_dest_1='LOCATION=/opt/oracle/arch/test'
    *.log_archive_dest_2='LOCATION=/backup/arch/test'
    *.log_archive_format='%t_%s_%r.arc'
    *.memory_target=5034213376
    *.open_cursors=300
    *.processes=300
    *.remote_login_passwordfile='NONE'
    *.shared_servers=1
    *.sql92_security=TRUE
    *.undo_tablespace='UNDOTBS1'

    Thanks
  • 14. Re: ORA-00845: pfile is working but not spfile
    damorgan Oracle ACE Director
    Currently Being Moderated
    That is why I advised you to rename the SPFILE you think is the problem. If when you've renamed it Oracle still gives you the same message you will know you are looking at the wrong file. Also why I advised you run the query to see, specifically, which SPFILE Oracle is using.

    If you want additional help you need to do both of these things.
1 2 Previous Next

Legend

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