1 2 Previous Next 19 Replies Latest reply: May 16, 2012 8:14 AM by 566539 RSS

    Unable to start database using pfile

    566539
      Hi All,

      2 Node RAC with ASM on SuSE Linux 10 SP2. Oracle 11g R2.

      I had initial Parameter setting as follows
      SQL> show parameter sga
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      lock_sga                             boolean     FALSE
      pre_page_sga                         boolean     FALSE
      sga_max_size                         big integer 1000M
      sga_target                           big integer 1000M
      SQL> SHOW PARAMETER PGA
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      pga_aggregate_target                 big integer 333M
      SQL> SHOW PARAMETER MEMORY
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      hi_shared_memory_address             integer     0
      memory_max_target                    big integer 0
      memory_target                        big integer 0
      shared_memory_address                integer     0
      I changed them to
      SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;
      
      System altered.
      
      SQL> ALTER SYSTEM SET MEMORY_TARGET=3G SCOPE=SPFILE;
      
      System altered.
      
      SQL> ALTER SYSTEM SET SGA_MAX_SIZE=2333M SCOPE=SPFILE;
      
      System altered.
      
      SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
      
      System altered.
      
      SQL> ALTER SYSTEM SET SGA_TAGRET=0 SCOPE=SPFILE;
      ALTER SYSTEM SET SGA_TAGRET=0 SCOPE=SPFILE
                       *
      ERROR at line 1:
      ORA-02065: illegal option for ALTER SYSTEM
      
      
      SQL> ALTER SYSTEM SET SGA_TAGRET=0;
      ALTER SYSTEM SET SGA_TAGRET=0
                       *
      ERROR at line 1:
      ORA-02065: illegal option for ALTER SYSTEM
      I was unable to restart
      SQL> startup force
      ORA-00845: MEMORY_TARGET not supported on this system
      I created pfile and tried to start, but in vain
      SQL> create pfile='/home/oracle/aqadev.ora' from spfile='+AQADATA/aqadev/spfileaqadev.ora';
      
      File created.
      contents of my pfile (memory_target value is removed)
      oracle@INBGEDV50:~> cat aqadev.ora
      AQADEV2.__db_cache_size=524288000
      AQADEV1.__db_cache_size=398458880
      AQADEV2.__java_pool_size=4194304
      AQADEV1.__java_pool_size=4194304
      AQADEV2.__large_pool_size=4194304
      AQADEV1.__large_pool_size=4194304
      AQADEV1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
      AQADEV2.__pga_aggregate_target=352321536
      AQADEV1.__pga_aggregate_target=352321536
      AQADEV2.__sga_target=1048576000
      AQADEV1.__sga_target=1048576000
      AQADEV2.__shared_io_pool_size=0
      AQADEV1.__shared_io_pool_size=0
      AQADEV2.__shared_pool_size=494927872
      AQADEV1.__shared_pool_size=620756992
      AQADEV2.__streams_pool_size=8388608
      AQADEV1.__streams_pool_size=8388608
      *.audit_file_dest='/u01/app/oracle/admin/AQADEV/adump'
      *.audit_trail='db'
      *.cluster_database=true
      *.compatible='11.2.0.0.0'
      *.control_files='+AQADATA/aqadev/controlfile/current.260.781108849'
      *.db_block_size=8192
      *.db_create_file_dest='+AQADATA'
      *.db_create_online_log_dest_1='+AQADATA'
      *.db_domain=''
      *.db_name='AQADEV'
      *.diagnostic_dest='/u01/app/oracle'
      *.dispatchers='(PROTOCOL=TCP) (SERVICE=AQADEVXDB)'
      AQADEV1.instance_number=1
      AQADEV2.instance_number=2
      *.memory_max_target=3221225472
      *.open_cursors=300
      *.pga_aggregate_target=0
      *.processes=150
      *.remote_listener='myhost.mysite.com:1521'
      *.remote_login_passwordfile='exclusive'
      *.sga_max_size=2446327808
      *.sga_target=0
      *.star_transformation_enabled='TRUE'
      AQADEV2.thread=2
      AQADEV1.thread=1
      AQADEV1.undo_tablespace='UNDOTBS1'
      AQADEV2.undo_tablespace='UNDOTBS2'
      Tail of alert log file
      Tue May 08 17:14:18 2012
      Starting ORACLE instance (normal)
      WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 2449473536 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1401778176 and used is 171085824 bytes. Ensure that the mount point is /dev/shm for this directory.
      memory_target needs larger /dev/shm
      Why is it still reading memory_target, when it is not part of pfile?
        • 1. Re: Unable to start database using pfile
          Fran
          it's bad sintax, check:

          you wrote:
          ALTER SYSTEM SET SGA_TAGRET=0 SCOPE=SPFILE

          correct will be:
          alter system set sga_taRGet=0 scope=spfile;

          *take care, your sga_target can be higher than sga_max_target                                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: Unable to start database using pfile
            anand prakash - oracle
            Hi,

            In the pfile which has been pasted i still do see the below parameter set.
            *.memory_max_target=3221225472
            Remove it and try and start the instance using pfile.


            Anand
            • 3. Re: Unable to start database using pfile
              415289
              Remove "*.memory_max_target=3221225472" and all "__*" parameter and Try to bring up the db from this pfile.

              like
              *.audit_file_dest='/u01/app/oracle/admin/AQADEV/adump'
              *.audit_trail='db'
              *.cluster_database=true
              *.compatible='11.2.0.0.0'
              *.control_files='+AQADATA/aqadev/controlfile/current.260.781108849'
              *.db_block_size=8192
              *.db_create_file_dest='+AQADATA'
              *.db_create_online_log_dest_1='+AQADATA'
              *.db_domain=''
              *.db_name='AQADEV'
              *.diagnostic_dest='/u01/app/oracle'
              *.dispatchers='(PROTOCOL=TCP) (SERVICE=AQADEVXDB)'
              AQADEV1.instance_number=1
              AQADEV2.instance_number=2
              *.open_cursors=300
              *.pga_aggregate_target=0
              *.processes=150
              *.remote_listener='myhost.mysite.com:1521'
              *.remote_login_passwordfile='exclusive'
              *.sga_max_size=2446327808
              *.sga_target=0
              *.star_transformation_enabled='TRUE'
              AQADEV2.thread=2
              AQADEV1.thread=1
              AQADEV1.undo_tablespace='UNDOTBS1'
              AQADEV2.undo_tablespace='UNDOTBS2'
              • 4. Re: Unable to start database using pfile
                Azar
                How do you started using pfile?

                If you execute startup, it will first reference spfile. So use startup pfile='/u01/init.ora' or after edited create spfile from pfile='/u01/init.ora'; and then startup, the modified parameter should be in spfile.
                • 5. Re: Unable to start database using pfile
                  566539
                  Hi,

                  I was able to startup, but i could not startup using spfile which i created using the pfile after startup
                  SQL> startup pfile='/home/oracle/aqadev.ora'
                  ORACLE instance started.
                  
                  Total System Global Area 2438529024 bytes
                  Fixed Size                  2215784 bytes
                  Variable Size            2382364824 bytes
                  Database Buffers           50331648 bytes
                  Redo Buffers                3616768 bytes
                  Database mounted.
                  Database opened.
                  SQL> show parameter sga
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  lock_sga                             boolean     FALSE
                  pre_page_sga                         boolean     FALSE
                  sga_max_size                         big integer 2336M
                  sga_target                           big integer 0
                  SQL> show parameter pga
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  pga_aggregate_target                 big integer 0
                  SQL> show parameter memory
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  hi_shared_memory_address             integer     0
                  memory_max_target                    big integer 0
                  memory_target                        big integer 0
                  shared_memory_address                integer     0
                  SQL> create spfile from pfile;
                  
                  File created.
                  
                  SQL> shut immediate
                  Database closed.
                  Database dismounted.
                  ORACLE instance shut down.
                  SQL> startup
                  ORA-00845: MEMORY_TARGET not supported on this system
                  • 6. Re: Unable to start database using pfile
                    415289
                    is it using right spfile,please crosscheck it ,also remove unnecessary spfile/pfile files from your dbs folder if created any.
                    • 7. Re: Unable to start database using pfile
                      fjfranken
                      Have a look here: MEMORY_TARGET not supported on this system

                      Success!!
                      FJFranken
                      • 8. Re: Unable to start database using pfile
                        415289
                        Appear it using spfile location which is stored in OCR .
                        please modify it by srvctl modify command.

                        srvctl config database -d DBNAME -a
                        oragg1 RAC1 /u01/app/oracle/product/10/db
                        oragg2 RAC2 /u01/app/oracle/product/10/db
                        DB_NAME: RAC
                        ORACLE_HOME: /u01/app/oracle/product/10/db
                        *SPFILE: +DATA01/RAC/spfileRAC.ora*
                        DOMAIN: null
                        DB_ROLE: null
                        START_OPTIONS: null
                        POLICY: AUTOMATIC
                        ENABLE FLAG: DB ENABLED
                        • 9. Re: Unable to start database using pfile
                          anand prakash - oracle
                          Hi,

                          As you had earlier made the changes in spfile with alter system command, you need to create new spfile from the correct pfile.

                          create spfile='+AQADATA/aqadev/spfileaqadev.ora' from pfile='/home/oracle/aqadev.ora'; --> assuming /home/oracle/aqadev.ora is the pfile from which you wanted to create spfile.


                          Anand
                          • 10. Re: Unable to start database using pfile
                            566539
                            I have 2 questions,

                            1. When i fired
                            SQL> create spfile from pfile;
                             
                            File created.
                            Where did the spfile get created/modified?

                            2. How can I use srvctl to over come this problem?

                            Do I have to fire?
                            srvctl modify database -p +AQADATA/AQADEV/spfileAQADEV.ora
                            • 11. Re: Unable to start database using pfile
                              606366
                              Hi,

                              check /dev/shm

                              SQL> !df -h /dev/shm

                              and add more than what in memory target, this shared memory can be increased by toor user.

                              Thanks
                              Athil
                              • 12. Re: Unable to start database using pfile
                                415289
                                1.its created on ASM ,check location under db_create_file_dest.
                                it will be created under PARAMETERFILE folder.

                                2.you have to use below cmd to modify the location
                                srvctl modify database -d racdb -p +RACDG_DATA/racdb1/spfileracdb1.ora                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                • 13. Re: Unable to start database using pfile
                                  566539
                                  1.its created on ASM ,check location under db_create_file_dest.
                                  it will be created under PARAMETERFILE folder.
                                  SQL> SHOW PARAMETER db_create_file_dest
                                  
                                  NAME                                 TYPE        VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  db_create_file_dest                  string      +AQADATA
                                  How can i physically locate the file?

                                  2.you have to use below cmd to modify the location
                                  srvctl modify database -d racdb -p +RACDG_DATA/racdb1/spfileracdb1.ora

                                  If I do this, I have to still locate the file under _AQADATA. How is that made possible?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                  • 14. Re: Unable to start database using pfile
                                    anand prakash - oracle
                                    Hi,

                                    To check the file, set the GRID_HOME and use "asmcmd" utility

                                    asmcmd -p

                                    http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#OSTMG01610

                                    http://www.orafaq.com/wiki/Asmcmd

                                    Once you check the spfile name and location set in OCR from "srvctl config database -d <db_unique_name> -a" simply create, as earlier posted

                                    >
                                    create spfile='+AQADATA/aqadev/spfileaqadev.ora' from pfile='/home/oracle/aqadev.ora';
                                    --> assuming /home/oracle/aqadev.ora is the pfile from which you wanted to create spfile.
                                    Simple create spfile from pfile command will create spfile in OH/dbs location (which is default)


                                    Anand
                                    1 2 Previous Next