Change Oracle Database "instance RAM" after the SAP import

[Former Member]

Hi all

When I import a new SAP system the SWPM asks me for the "Database Instance RAM" and suggests me always the half available on the system. Check the picture below:

Is there a way to change this value(8GB in this case) in future when the SWPM is finished? I use Oracle 11.2.x and 12.1 with AIX 6.1 and 7.1

Thanks,
Marco

[BJarkowski · Accepted solution]

Yes, you can change this value after the install. Please have a look to following notes:

1431798 - Oracle 11.2.0: Database Parameter Settings

789011 - FAQ: Oracle memory areas

Best regards

Bartosz

[Former Member · Accepted solution]

Thanks! I've read those notes and many much things are more clear to me now. But I can't find a correspondence of the 8192MB (DB instance RAM) in my spfile(SAP ECC on ORA12.1 + AIX 7.1):

*.__shared_io_pool_size=150994944
*._advanced_index_compression_options=16
*._awr_mmon_deep_purge_all_expired=TRUE
*._B_TREE_BITMAP_PLANS=FALSE
*._fix_control='5099019:ON','5705630:ON','6055658:OFF','6120483:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','7168184:OFF','7658097:ON','8937971:ON','9196440:ON','9495669:ON','13077335:ON','13627489:ON','14255600:ON','1459527CC" +3:ON','18405517:2','20355502:8','14846352:OFF'#SAP_12102160119_201602 RECOMMENDED SETTINGS
*._IN_MEMORY_UNDO=FALSE
*._INDEX_JOIN_ENABLED=FALSE
*._ktb_debug_flags=8
*._mutex_wait_scheme=1
*._mutex_wait_time=10
*._OPTIM_PEEK_USER_BINDS=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_batch_table_access_by_rowid=FALSE
*._optimizer_extended_cursor_sharing_rel='NONE'
*._OPTIMIZER_MJC_ENABLED=FALSE
*._optimizer_reduce_groupby_key=FALSE
*._oCC"!ptimizer_use_feedback=FALSE
*._securefiles_concurrency_estimate=50
*._SORT_ELIMINATION_COST_RATIO=10
*._suppress_identifiers_on_dupkey=TRUE
*._TABLE_LOOKUP_PREFETCH_SIZE=0
*._use_single_log_writer='TRUE'
*.audit_file_dest='/oracle/MNP/saptrace/audit'
*.compatible='12.1.0.2.0'
*.control_file_record_keep_time=30
*.control_files='/oracle/MNP/origlogA/cntrl/cntrlMNP.dbf','/oracle/MNP/origlogB/cntrl/cntrlMNP.dbf','/oracle/MNP/sapdata1/cntrl/cntrlMNP.dbf'
*.control_management_pack_access='CC"o
                                      DIAGNOSTIC+TUNING'
*.db_block_size=8192
*.db_cache_size=2319282339
*.db_files=200
*.db_name='MNP'
*.db_recovery_file_dest='/oracle/MNP/oraflash'
*.db_recovery_file_dest_size=30000M
*.diagnostic_dest='/oracle/MNP/saptrace'
*.event='10027','10028','10142','10183','10191','10995 level 2','38068 level 100','38085','38087','44951 level 1024'#SAP_121022_201503 RECOMMENDED SETTINGS
*.FILESYSTEMIO_OPTIONS='setall'
*.heat_map='ON'
*.log_archive_dest_1='LOCATION=/oracle/MNP/oraarch/MNParch'
*.CC"+&log_archive_format='%t_%s_%r.dbf'
*.log_checkpoints_to_alert=true
*.max_dump_file_size='20000'
*.open_cursors=2000
*.optimizer_adaptive_features=FALSE
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_index_cost_adj=20
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=3092376453
*.processes=400
*.query_rewrite_enabled='false'
*.recyclebin='off'
*.remote_login_passwordfile='exclusive'
*.replication_dependency_tracking=false
*.sessions=8CC"Vw00
*.shared_pool_size=2319282339
*.star_transformation_enabled='true'
*.undo_tablespace='PSAPUNDO'

It could be that the 8192MB are divided into a sum of some memory parameters?

Thanks

[BJarkowski · Accepted solution]

What about this parameter? 🙂

*.db_block_size=8192

Best regards

Bartosz

[Former Member · Accepted solution]

This value is always 8KB in every SAP System on AIX

[BJarkowski · Accepted solution]

Yes, you are correct. I though about DB_CACHE_SIZE, but in your config file this has a different value. Unfortunately, I don't have access to any Oracle DB server to check the value of this parameter. But this is definitely the place where I'd start my investigation.

Best regards

Bartosz

[Reagan]

The amount of memory you have allocated is not necessarily for one database parameter. The value you specify there under instance RAM is then shared for parameters like SGA, PGA, etc. Once the import has been completed you may modify the Oracle parameters according to your needs. The db_block_size parameter is for the size of the Oracle data blocks and DB_CACHE_SIZE alone is not going to take 8GB. It comes under SGA along with other initialization parameters. Check the SAP note 789011 - FAQ: Oracle memory areas and also read the Oracle documentation about Memory Architecture.