8 Replies Latest reply: Apr 27, 2011 3:09 AM by KSG RSS

    Oracle 11g init.ora parameter settings

      Hi Friends,

      Oracle on Suse Linux.

      Can you please let me know what are the things need to be followed while setting init.ora parameters for the database?

      And, can you please suggest me the best init.ora settings for the database?

        • 1. Re: Oracle 11g init.ora parameter settings
          As far as I can see from your questions, better to ask for a consultancy on site. But right, that would not come for free.

          • 2. Re: Oracle 11g init.ora parameter settings

            If you have ebs than i suggest you can follow below note:

            Database Initialization Parameters for Oracle Applications Release 12 [ID 396009.1]
            Database Initialization Parameters for Oracle Applications Release 11i [ID 216205.1]


            I just see your post  at http://forums.oracle.com/forums/thread.jspa?threadID=2200465&tstart=0

            I just want to say thank you put explanation why you locks thread. I hope this behaviour could be a nice example for the other forum modators.

            • 3. Re: Oracle 11g init.ora parameter settings
              ji li
              There are NO BEST SETTINGS.

              It all depends on too many things, including what you intend to use it for, type of database, number of simultaneous users, processes, etc.
              • 4. Re: Oracle 11g init.ora parameter settings
                KSG wrote:
                Hi Friends,

                Oracle on Suse Linux.

                Can you please let me know what are the things need to be followed while setting init.ora parameters for the database?

                And, can you please suggest me the best init.ora settings for the database?

                There is no such thing " best init.ora settings".To optimal setting initialization parameter you have to analyze your database and OS properly.All these depend your system activities and available resources.This is generally called instance and OS tuning.Firstly you have to find what is a problem(performance related and problem area).Then after finding these begin investigate that(use tuning tools,dynamic performance views,AWR/STATSPACK report,using tracing feature(sql trace/tkprof utility)).Finally i suggest you refer performance guide.
                • 5. Re: Oracle 11g init.ora parameter settings

                  Thanks for your valuable comments. I do agree with you.

                  My Scenario:

                  To upgrade database from 9i sun os to 11G suse linux.

                  For this, I'm going to perfrom exp/imp.

                  I have to create a database on 11g, where I have to set all the best setting init.ora paremeters. My database is aroung 25-50GB.

                  Please provide your suggesion on the same.

                  • 6. Re: Oracle 11g init.ora parameter settings
                    You can try to run the pre-upgrade information tool that may help (please read http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/upgrade.htm#CACHIDJD).

                    But as already said there are no "best" instance parameter settings. Note also that some experts also say that 80% to 90% of performance improvement are based on application tuning and not on instance tuning. There are also few instance parameters that have a direct impact on performance: please read Performance Considerations for Initial Instance Configuration http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/build_db.htm#i20151
                    • 7. Re: Oracle 11g init.ora parameter settings
                      Jonathan Lewis
                      KSG wrote:

                      To upgrade database from 9i sun os to 11G suse linux.
                      For this, I'm going to perfrom exp/imp.
                      Get a complete list of index definitions before and after the import - a strategy like this can result in some indexes "going missing" on the import for any tables that have multiple indexes that could all support primary key or unique constraints.
                      I have to create a database on 11g, where I have to set all the best setting init.ora paremeters. My database is aroung 25-50GB.
                      Every database needs at least a little special consideration, but you should at least test the effects of removing all the "little tweak" optimizer parameters such as db_file_multiblock_read_count, optimizer_index_cost_adj, optimizer_index_caching - if you have them in place. Similarly check the reason for any EVENT settings or hidden parameter settings and assume that they should be removed on upgrade.

                      You then have to choose whether you want to use manual settings for the SGA, or automatic SGA management (allowing Oracle to move memory between the various SGA areas, or whether you want to go fully automatic on memory and allow Oracle to move memory between the PGA and SGA. You could make an argument for any of these options - for a busy complicated system I'd probably choose manual, for a boring system that has never caused trouble I would probably choose automatic SGA but not fully auomatic memory.

                      For stability, you might want to set the optimizer_features_enable to the value representing your current 9i version, although it would be good to go through at least one test run using all the 11g features.

                      You probably need to watch out most for serial direct path tablescans, and may need to take action to stop them (using a hidden parameter which you can get from Oracle support).

                      Jonathan Lewis
                      • 8. Re: Oracle 11g init.ora parameter settings
                        Hi JL Sir,

                        Thanks for your valuable guidance.

                        We have finally come up with the init.ora parameters. Please review and guide me further.

                        NAME VALUE
                        tracefiles_public TRUE
                        processes 100
                        sessions 120
                        timed_statistics TRUE
                        timed_os_statistics 0
                        resource_limit TRUE
                        license_max_sessions 0
                        license_sessions_warning 0
                        cpu_count 2
                        shared_pool_size 268435456
                        sga_max_size 1075800904
                        shared_pool_reserved_size 13421772
                        large_pool_size 16777216
                        java_pool_size 167772160
                        java_soft_sessionspace_limit 0
                        java_max_sessionspace_size 0
                        pre_page_sga FALSE
                        shared_memory_address 0
                        hi_shared_memory_address 0
                        use_indirect_data_buffers FALSE
                        lock_sga FALSE
                        spfile ?/dbs/spfile@.ora
                        enqueue_resources 1542
                        trace_enabled TRUE
                        nls_language american
                        nls_territory america
                        nls_date_format DD-MON-RR
                        nls_length_semantics BYTE
                        nls_nchar_conv_excp FALSE
                        filesystemio_options asynch
                        disk_asynch_io TRUE
                        tape_asynch_io TRUE
                        dbwr_io_slaves 0
                        backup_tape_io_slaves FALSE
                        file_mapping FALSE
                        control_files /u020/oradata/mecdb/control01.ctl, /u021/oradata/mecdb/control02.ctl, /u026/or
                        db_block_buffers 0
                        db_block_checksum TRUE
                        db_block_size 8192
                        db_writer_processes 1
                        db_keep_cache_size 0
                        db_recycle_cache_size 0
                        db_2k_cache_size 0
                        db_4k_cache_size 0
                        db_8k_cache_size 0
                        db_16k_cache_size 0
                        db_32k_cache_size 0
                        db_cache_size 134217728
                        db_cache_advice ON
                        max_commit_propagation_delay 700
                        compatible 11.2.0
                        remote_archive_enable true
                        log_archive_start TRUE
                        log_archive_dest /app/oracle/admin/mecdb/arch
                        log_archive_max_processes 2
                        log_archive_min_succeed_dest 1
                        standby_archive_dest ?/dbs/arch
                        log_archive_trace 0
                        log_archive_format mecdb_T%t_S%S.arc
                        log_buffer 1048576
                        log_checkpoint_interval 100000
                        log_checkpoint_timeout 72000
                        archive_lag_target 0
                        log_parallelism 1
                        db_files 1022
                        db_file_multiblock_read_count 16
                        read_only_open_delayed FALSE
                        cluster_database FALSE
                        parallel_server FALSE
                        parallel_server_instances 1
                        cluster_database_instances 1
                        standby_file_management MANUAL
                        thread 0
                        fast_start_io_target 0
                        fast_start_mttr_target 0
                        log_checkpoints_to_alert FALSE
                        recovery_parallelism 0
                        control_file_record_keep_time 7
                        logmnr_max_persistent_sessions 1
                        dml_locks 500
                        row_locking always
                        serializable FALSE
                        replication_dependency_tracking TRUE
                        instance_number 0
                        transactions 132
                        transactions_per_rollback_segment 5
                        max_rollback_segments 30
                        transaction_auditing TRUE
                        undo_management AUTO
                        undo_tablespace undo1
                        undo_suppress_errors TRUE
                        undo_retention 900
                        fast_start_parallel_rollback LOW
                        db_block_checking FALSE
                        os_roles FALSE
                        max_enabled_roles 40
                        remote_os_authent FALSE
                        remote_os_roles FALSE
                        O7_DICTIONARY_ACCESSIBILITY TRUE
                        remote_login_passwordfile EXCLUSIVE
                        dblink_encrypt_login FALSE
                        license_max_users 0
                        audit_sys_operations FALSE
                        db_domain world
                        global_names FALSE
                        distributed_lock_timeout 60
                        commit_point_strength 1
                        instance_name mecdb
                        service_names mecdb.world
                        shared_servers 0
                        mts_servers 0
                        max_shared_servers 20
                        mts_max_servers 20
                        max_dispatchers 5
                        mts_max_dispatchers 5
                        circuits 0
                        mts_circuits 0
                        shared_server_sessions 0
                        mts_sessions 0
                        local_listener mecdb_lsnr
                        mts_service mecdb
                        mts_multiple_listeners FALSE
                        serial_reuse DISABLE
                        cursor_space_for_time FALSE
                        session_cached_cursors 0
                        remote_dependencies_mode TIMESTAMP
                        utl_file_dir *
                        plsql_v2_compatibility FALSE
                        plsql_compiler_flags INTERPRETED
                        plsql_native_library_subdir_count 0
                        job_queue_processes 1
                        parallel_min_percent 0
                        create_bitmap_area_size 8388608
                        bitmap_merge_area_size 1048576
                        cursor_sharing EXACT
                        parallel_min_servers 0
                        parallel_max_servers 5
                        parallel_execution_message_size 2152
                        hash_join_enabled TRUE
                        hash_area_size 131072
                        shadow_core_dump partial
                        background_core_dump partial
                        background_dump_dest /app/oracle/admin/mecdb/bdump
                        user_dump_dest /app/oracle/admin/mecdb/udump
                        max_dump_file_size 102400000
                        core_dump_dest /app/oracle/admin/mecdb/cdump
                        audit_file_dest /app/oracle/admin/mecdb/adump
                        hpux_sched_noage 0
                        oracle_trace_enable FALSE
                        oracle_trace_facility_path ?/otrace/admin/fdf
                        oracle_trace_collection_path ?/otrace/admin/cdf
                        oracle_trace_facility_name oracled
                        oracle_trace_collection_size 5242880
                        object_cache_optimal_size 102400
                        object_cache_max_size_percent 10
                        session_max_open_files 10
                        open_links 4
                        open_links_per_instance 4
                        optimizer_features_enable 11.2.0
                        audit_trail DB
                        sort_area_size 65536
                        sort_area_retained_size 0
                        db_name mecdb
                        open_cursors 200
                        sql_trace FALSE
                        os_authent_prefix ops$
                        optimizer_mode CHOOSE
                        sql92_security FALSE
                        blank_trimming FALSE
                        partition_view_enabled FALSE
                        star_transformation_enabled FALSE
                        parallel_adaptive_multi_user FALSE
                        parallel_threads_per_cpu 2
                        parallel_automatic_tuning FALSE
                        optimizer_max_permutations 2000
                        optimizer_index_cost_adj 100
                        optimizer_index_caching 0
                        query_rewrite_enabled false
                        query_rewrite_integrity enforced
                        sql_version NATIVE
                        pga_aggregate_target 536870912
                        workarea_size_policy auto
                        optimizer_dynamic_sampling 1
                        statistics_level TYPICAL
                        aq_tm_processes 0
                        hs_autoregister TRUE
                        dg_broker_start FALSE
                        drs_start FALSE
                        dg_broker_config_file1 ?/dbs/dr1@.dat
                        dg_broker_config_file2 ?/dbs/dr2@.dat
                        olap_page_pool_size 33554432


                        Edited by: KSG on Apr 27, 2011 1:39 PM