Browse > Home / Howto / SAP 4.6c Oracle recommendations

SAP 4.6c Oracle recommendations

March 10, 2008

I had put this together for a 4.6C system back in 2005. Some of these are now default behavior, but you may still have some older systems that have not yet been converted, or it may provide a good reference at some point. SAP recommendations

  • Server parameter file : (SPFILE) See note 601157

  • Automatic Undo Management : See note 600141

  • Dynamic SGA : See note 617416

  • Automatic PGA Memory Management : See note 619876

  • If required, ASSM : See note 620803

  1. SPFILE (from oss note 601157)
    a. Create SPFILE
    SQL>connect / as sysdba
    SQL>create spfile from pfile;
    b. renaming the old init.ora (for example):
    mv $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/init.ora.SAVE
    c. SQL> connect/as sysdba
    SQL> shutdown
    SQL> startup
    SQL> show parameter spfile

  2. Auto Undo management READ note 600141 below is an overview.
    Calculate the size of the Undo tablespace

    a. Step: Generate spool file (optional)
    Used to log the migration steps.
    SQL> spool undo_process.log
    b. Step: Detect the current management Undo mode
    SQL> show parameter undo
    SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

    c. Step: Create a new PSAPUNDO Undo tablespace
    - manually or using SAPDBA 6.20 or BRSPACE 6.40.
    - Size: as large as possible, at least as big as PSAPROLL
    The size can be specified in K or M.
    - AUTOEXTEND: OFF (see recommendations)

    SQL> create undo tablespace PSAPUNDO datafile /oracle/SMP/sapdata2/undo_1/undo.data1′ size 1600M reuse
    autoextend off;
    d. Step: Assign an Undo tablespace
    init.ora: undo_tablespace = PSAPUNDO
    spfile : SQL> alter system set undo_tablespace = PSAPUNDO scope = spfile;
    e. Step: Define the Undo retention time
    init.ora: undo_retention = 43200 # 12h as start value
    spfile : SQL>alter system set undo_retention = 43200 scope = spfile;
    F. Step: Activate AUM
    init.ora: undo_management = AUTO
    spfile : SQL>alter system set undo_management=auto scope = spfile;
    G. Step: Start instance
    SQL> shutdown immediate
    SQL> startup
    h. Step: Check the current AUM settings
    SQL> show parameter undo
    SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
    SQL> show parameter rollback_segments
    i. CAUTION:
    Provided step J below has not carried out yet, you can switch from the
    Automatic Undo Management to the Manual Undo Management at any time
    within a short time. The following steps are necessary for this:

    SQL> connect / as sysdba
    SQL> alter system set undo_management=manual scope = spfile;
    SQL> shutdown immediate
    SQL> startup

    j. Step: Delete the old rollback segments
    Except for the SYSTEM rollback segment, the old rollback segments are
    now deleted. They are no longer required.
    SQL> select segment_name, tablespace_name, status from dba_rollback_segs
    where status = ‘OFFLINE’ and segment_name not like ‘_SYSSMU%$’;
    SQL> DROP ROLLBACK SEGMENT PRS_;

    k. Check results:
    SQL> select segment_name, tablespace_name, status from dba_rollback_segs;
    Only UNDOs should appear;

    l. Step: Adjust the profile parameters
    spfile : SQL> alter system reset rollback_segments scope = spfile sid=’*';
    spfile : SQL> alter system reset transactions scope = spfile sid=’*';
    spfile : SQL> alter system reset transactions_per_rollback_segment scope = spfile sid=’*';
    spfile : SQL> alter system reset max_rollback_segments scope = spfile sid=’*';

    m. Step: Delete old rollback tablespace
    SQL> DROP TABLESPACE PSAPROLL including contents and datafiles;

  3. Dynamic SGA
    a. SQL> alter system set statistics_level=’TYPICAL’;
    b. Setting the new parameters (if using SPFILE)
    SQL> connect / as sysdba
    SQL> REM Delete old SGA parameter
    SQL> alter system reset buffer_pool_keep scope = spfile sid=’*';
    SQL> alter system reset buffer_pool_recycle scope = spfile sid=’*';
    SQL> alter system reset db_block_buffers scope = spfile sid=’*';
    SQL> REM Set new SGA parameter
    SQL> alter system set sga_max_size=1531868916 scope = spfile;
    SQL> alter system set db_cache_size=476577792 scope = spfile;
    SQL> REM statistics_level = ALL or TYPICAL
    SQL> alter system set statistics_level=’TYPICAL’ scope = spfile;
    If required:
    SQL> alter system set db_keep_cache_size=X scope = spfile;
    SQL> alter system set db_recycle_cache_size=X scope = spfile;
    c. shutdown db / restart.

  4. Auto PGA
    a. SQL> alter system set workarea_size_policy=AUTO scope = spfile;
    b. SQL> alter system set pga_aggregate_target=256M scope = spfile;

Written by admin · Filed Under Howto 

Comments

Got something to say?