1 2 3 4 Previous Next 46 Replies Latest reply on Feb 19, 2014 5:59 PM by pu297

    ORA-27102: out of memory

    pu297

      Hi All,

       

      I had oracle 11g R2 installed on my windows 7 pc.

      I was trying to configure data guard and do automatic memory management.

      desired total amount of memory was same as maximum SGA size.

      I changed "Maximum SGA Size" in memory advisors to 3000 from 1216.

       

      Now I can't startup my database from enterpise manager its giving me error

       

       

      SQL> Connected to an idle instance.

      SQL> SQL> ORA-27102: out of memory

      OSD-00022: additional error information

      O/S-Error: (OS 8) Not enough storage is available to process this command.

      Disconnected

       

      Please tell me what to do now.

       

      Thanks

        • 1. Re: ORA-27102: out of memory
          B Dave

          I think you have problem with hard drive space.

           

          OSD-00022: additional error information

          O/S-Error: (OS 8) Not enough storage is available to process this command.

          or

          Bounce database if it is okay.

           

          Regards,

          Dave

          • 2. Re: ORA-27102: out of memory
            pu297

            before max sga size change everthing was fine. how can i revert it to original value.

            I am unable to start database instance.

            • 3. Re: ORA-27102: out of memory
              KarK

              Hi,

               

              check the alert log for any information related to this error.

               

              Check the below link:

               

              ORA-27102: out of memory tips

              • 4. Re: ORA-27102: out of memory
                B Dave

                shutdown your Database

                change your init<SID> parameter file.

                start with init file as

                startup pfile=pfilewithlocation

                SQL>create spfile from pfile;

                sql>shutdown immediate

                sql>startup.

                 

                HTH

                Dave

                • 5. Re: ORA-27102: out of memory
                  KarK

                  Change the sga_max_size parameter value to original value and restart the database.

                  • 6. Re: ORA-27102: out of memory
                    onkar.nath

                    locate init<sid>.ora and change sga_max_size and start db using that init file and create spfile. it should be under ORACLE_HOME\database or ORACLE_HOME\dbs

                    BUT if you can not locate init file then locate spfile, copy it and make another copy, rename copied one to init<sid>.ora and then make the changes carefully. Since this would be the copy of spfile, it would be having lot of binary characters which you need to remove carefully. Though this is not very robust solution but it does work.

                     

                    Onkar

                    • 7. Re: ORA-27102: out of memory
                      pu297

                      shutdown is giving me this error

                       

                      SQL> shutdown

                      ORA-01034: ORACLE not available

                      ORA-27101: shared memory realm does not exist

                      • 8. Re: ORA-27102: out of memory
                        pu297

                        init.ora file (copy-pasted)----don't know what to change can't find max_sga_size

                         

                        #

                        # $Header: init.ora 25-jul-01.18:03:38 rajayar Exp $

                        #

                        # Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.  */

                        #

                        ##############################################################################

                        # Example INIT.ORA file

                        #

                        # This file is provided by Oracle Corporation to help you customize

                        # your RDBMS installation for your site.  Important system parameters

                        # are discussed, and example settings given.

                        #

                        # Some parameter settings are generic to any size installation.

                        # For parameters that require different values in different size

                        # installations, three scenarios have been provided: SMALL, MEDIUM

                        # and LARGE.  Any parameter that needs to be tuned according to

                        # installation size will have three settings, each one commented

                        # according to installation size.

                        #

                        # Use the following table to approximate the SGA size needed for the

                        # three scenarious provided in this file:

                        #

                        #                     -------Installation/Database Size------

                        #                      SMALL           MEDIUM           LARGE

                        #  Block         2K    4500K            6800K           17000K

                        #  Size          4K    5500K            8800K           21000K

                        #

                        # To set up a database that multiple instances will be using, use the

                        # same file for all instance.  Place all instance-specific parameters

                        # at the end of the file using the <sid>.<parameter_name> = <value> syntax.

                        # This way, when you change a public

                        # parameter, it will automatically change on all instances.  This is

                        # necessary, since all instances must run with the same value for many

                        # parameters. For example, if you choose to use private rollback segments,

                        # these must be specified differently for each instance, but since all gc_*

                        # parameters must be the same on all instances, they should be in one file.

                        #

                        # INSTRUCTIONS: Edit this file and the other INIT files it calls for

                        # your site, either by using the values provided here or by providing

                        # your own.

                        ###############################################################################

                         

                         

                        # replace "clustdb" with your database name

                        db_name = clustdb

                        compatible=9.0.0

                         

                         

                        db_files = 1024                                                       # INITIAL

                        #INITIAL

                        #db_files=80

                        #SMALL

                        #DB_files=400

                        #MEDIUM

                        #db_files=1000

                        #LARGE

                         

                         

                        control_files = ("\\.\clustdb_control1", "\\.\clustdb_control2")

                         

                         

                        db_file_multiblock_read_count =  8 # INITIAL

                         

                         

                        # db_file_multiblock_read_count = 8

                        #SMALL

                        #db_file_multiblock_read_count = 16

                        #MEDIUM

                        #db_file_multiblock_read_count = 32

                        #LARGE

                         

                         

                        #Datawarehouse

                        db_block_size=8192                  

                        # Transaction processing

                        # db_block_size=4096                   

                         

                        open_cursors=300

                         

                         

                        #Transaction processing

                        #sort_area_size=524288                  

                        #Datawarehouse

                        sort_area_size=1048576                  

                        large_pool_size=1048576                  #Transaction processing, Datawarehouse

                        db_cache_size=50331648                   #Datawarehouse, Transaction processing

                        java_pool_size=67108864                  #Datawarehouse, Transaction processing

                         

                         

                         

                         

                        # db_block_buffers =  200                 # INITIAL

                        # db_block_buffers=100                    #SMALL 

                        # db_block_buffers =550                   # MEDIUM

                        # db_block_buffers=3200                   #LARGE

                         

                         

                        #shared_pool_size =  52428800             # INITIAL

                        shared_pool_size = 67108864              # datewarehouse, transaction processing

                         

                         

                        log_checkpoint_interval = 10000

                         

                         

                        processes =  59                        # INITIAL

                         

                         

                        # processes = 50

                        #SMALL

                        #processes =  100

                        #MEDIUM

                        #processes = 200

                        #LARGE

                         

                         

                        parallel_max_servers = 5

                        #SMALL

                        # parallel_max_servers = 4 x (number of CPUs)

                        #MEDIUM

                        # parallel_max_servers = 4 x (number of CPUs)

                        # LARGE

                         

                         

                        log_buffer =  8192                                    # INITIAL

                        #log_buffer =  8192

                        #SMALL

                        #log_buffer = 32768

                        #MEDIUM

                        #log_buffer = 163840

                        #LARGE

                         

                         

                        # audit_trail = true            # if you want auditing

                        # timed_statistics = true       # if you want timed statistics

                        max_dump_file_size = 10240      # limit trace file size to 10 K each

                         

                         

                        # Uncommenting the line below will cause automatic archiving if archiving has

                        # been enabled using ALTER DATABASE ARCHIVELOG.

                        # log_archive_start = true

                        # log_archive_dest = %ORACLE_HOME%/admin/clustdb/arch

                        # log_archive_format = "%%ORACLE_SID%%T%TS%S.ARC"

                         

                         

                        # If using private rollback segments, place lines of the following

                        # form at the end of this file:

                        # <sid>.rollback_segments = (name1, name2)

                         

                         

                        # If using public rollback segments, define how many

                        # rollback segments each instance will pick up, using the formula

                        #   # of rollback segments = transactions / transactions_per_rollback_segment

                        # In this example each instance will grab 40/10 = 4:

                        # transactions = 40

                        # transactions_per_rollback_segment = 10

                         

                         

                        # Global Naming -- enforce that a dblink has same name as the db it connects to

                        global_names = TRUE

                         

                         

                        # Edit and uncomment the following line to provide the suffix that will be

                        # appended to the db_name parameter (separated with a dot) and stored as the

                        # global database name when a database is created.  If your site uses

                        # Internet Domain names for e-mail, then the part of your e-mail address after

                        # the '@' is a good candidate for this parameter value.

                         

                         

                        # db_domain = us.acme.com       # global database name is db_name.db_domain

                         

                         

                        # Uncomment the following line if you wish to enable the Oracle Trace product

                        # to trace server activity.  This enables scheduling of server collections

                        # from the Oracle Enterprise Manager Console.

                        # Also, if the oracle_trace_collection_name parameter is non-null,

                        # every session will write to the named collection, as well as enabling you

                        # to schedule future collections from the console.

                         

                         

                        # oracle_trace_enable = TRUE

                         

                         

                        # define directories to store trace and alert files

                        background_dump_dest=%ORACLE_HOME%/admin/clustdb/bdump

                        user_dump_dest=%ORACLE_HOME%/admin/clustdb/

                         

                         

                        db_block_size = 4096

                         

                         

                        remote_login_passwordfile = exclusive

                         

                         

                        # text_enable = TRUE

                         

                         

                        # The following parameters are needed for the Advanced Replication Option

                         

                         

                        job_queue_processes = 2

                        # job_queue_processes = 4            #datawarehouse

                         

                         

                        # job_queue_interval = 10

                        # job_queue_keep_connections = false

                         

                         

                        distributed_transactions = 5

                         

                         

                        open_links = 4

                         

                         

                        # The following parameters are instance-specific parameters that are

                        # specified for two instances named clustdb1 and clustdb2

                         

                         

                        undo_management=AUTO            # For automatic undo management

                                                        # = MANUAL For manual/RBS undo management

                        cluster_database= true

                        cluster_database_instances=2

                        remote_listener=LISTENERS_CLUSTDB

                         

                         

                        # First instance specific parameters

                        clustdb1.thread=1

                        clustdb1.instance_name=clustdb1

                        clustdb1.instance_number=1

                        clustdb1.local_listener=listener_clustdb1

                        clustdb1.remote_login_passwordfile = exclusive

                        # Comment out clustdb1.undo_tablespace and uncomment clustdb1.rollback_segments

                        # when undo_management=MANUAL

                        clustdb1.undo_tablespace = UNDOTBS1

                        # clustdb1.rollback_segments = (rbs1_1,rbs1_2)

                         

                         

                        # Second instance specific parameters

                         

                         

                        clustdb2.thread=2

                        clustdb2.instance_name = clustdb2

                        clustdb2.instance_number = 2

                        clustdb2.local_listener = listener_clustdb2

                        clustdb2.remote_login_passwordfile = exclusive

                        # Comment out clustdb2.undo_tablespace and uncomment clustdb2.rollback_segments

                        # when undo_management=MANUAL

                        clustdb2.undo_tablespace = UNDOTBS2

                        # clustdb2.rollback_segments = (rbs2_1,rbs2_2)

                        • 9. Re: ORA-27102: out of memory
                          B Dave

                          windows key+R

                          services.msc

                           

                          find oracleservice<instance_name> something like not sure.

                          restart that service.

                          • 10. Re: ORA-27102: out of memory
                            pu297

                            init.ora from oraclehome\dbs\init.ora...what shall i change here

                             

                            #

                            # $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $

                            #

                            # Copyright (c) 1991, 1997, 1998 by Oracle Corporation

                            # NAME

                            #   init.ora

                            # FUNCTION

                            # NOTES

                            # MODIFIED

                            #     ysarig     05/14/09  - Updating compatible to 11.2

                            #     ysarig     08/13/07  - Fixing the sample for 11g

                            #     atsukerm   08/06/98 -  fix for 8.1.

                            #     hpiao      06/05/97 -  fix for 803

                            #     glavash    05/12/97 -  add oracle_trace_enable comment

                            #     hpiao      04/22/97 -  remove ifile=, events=, etc.

                            #     alingelb   09/19/94 -  remove vms-specific stuff

                            #     dpawson    07/07/93 -  add more comments regarded archive start

                            #     maporter   10/29/92 -  Add vms_sga_use_gblpagfile=TRUE

                            #     jloaiza    03/07/92 -  change ALPHA to BETA

                            #     danderso   02/26/92 -  change db_block_cache_protect to _db_block_cache_p

                            #     ghallmar   02/03/92 -  db_directory -> db_domain

                            #     maporter   01/12/92 -  merge changes from branch 1.8.308.1

                            #     maporter   12/21/91 -  bug 76493: Add control_files parameter

                            #     wbridge    12/03/91 -  use of %c in archive format is discouraged

                            #     ghallmar   12/02/91 -  add global_names=true, db_directory=us.acme.com

                            #     thayes     11/27/91 -  Change default for cache_clone

                            #     jloaiza    08/13/91 -         merge changes from branch 1.7.100.1

                            #     jloaiza    07/31/91 -         add debug stuff

                            #     rlim       04/29/91 -         removal of char_is_varchar2

                            #   Bridge     03/12/91 - log_allocation no longer exists

                            #   Wijaya     02/05/91 - remove obsolete parameters

                            #

                            ##############################################################################

                            # Example INIT.ORA file

                            #

                            # This file is provided by Oracle Corporation to help you start by providing

                            # a starting point to customize your RDBMS installation for your site.

                            #

                            # NOTE: The values that are used in this file are only intended to be used

                            # as a starting point. You may want to adjust/tune those values to your

                            # specific hardware and needs. You may also consider using Database

                            # Configuration Assistant tool (DBCA) to create INIT file and to size your

                            # initial set of tablespaces based on the user input.

                            ###############################################################################

                             

                             

                            # Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at

                            # install time)

                             

                             

                            db_name='ORCL'

                            memory_target=1G

                            processes = 150

                            audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'

                            audit_trail ='db'

                            db_block_size=8192

                            db_domain=''

                            db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'

                            db_recovery_file_dest_size=2G

                            diagnostic_dest='<ORACLE_BASE>'

                            dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

                            open_cursors=300

                            remote_login_passwordfile='EXCLUSIVE'

                            undo_tablespace='UNDOTBS1'

                            # You may want to ensure that control files are created on separate physical

                            # devices

                            control_files = (ora_control1, ora_control2)

                            compatible ='11.2.0'

                            • 11. Re: ORA-27102: out of memory
                              KarK

                              You are assigning memory to parameters individually like db_cache_size=50331648,shared_pool_size = 67108864,etc, thats what you are not seeing SGA_MAX_SIZE parameter.

                               

                              You have to allocate the memory which is available on the server.

                              Either reduce the memory size allocated to all  the parameters or else go with automatic memory management.

                              • 12. Re: ORA-27102: out of memory
                                pu297

                                restarting didn't help,same error on shutdown

                                • 13. Re: ORA-27102: out of memory
                                  pu297

                                  how can I do automatic memory management can't enetr database console in enterprise manager.

                                  • 14. Re: ORA-27102: out of memory
                                    B Dave

                                    hi

                                    what's memory size in your OS?

                                     

                                    after restarting oracleserviceorcl(something likethat)

                                    how are you are connecting to DB?

                                    I mean from cmd or toad/someother tool ?

                                    1 2 3 4 Previous Next