Is it OK to chang DB parameter file format from PFILE to SPFILE?

[Former Member]

Hi all,

Is it OK to change DB parameter file format from PFILE to SPFILE?

Our server got CPU Idle is 0 warning everyday when running program RSDBAJOB. I'm not sure whether it's caused by the SPFILE parameter file. If not, any other possible cause?

Original PFILE:

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

1. (c)Copyright SAP AG, Walldorf #

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

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

1. PART I, TUNING PARAMETERS #

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

1. SYSTEM GLOBAL AREA BUFFERS

1. Total System Global Area = sum(bytes) of all caches

2. Variable Size = shared_pool_size (approx.)

3. Database Buffers = db_block_buffers * db_block_size

4. Redo Buffers = log_buffer

#

1. unit of shared_pool_size: bytes

2. unit of db_block_buffers: number of cached blocks

3. unit of log_buffer: bytes

log_buffer = 1048576

1. MANAGING SORTS

1. A small sort requires sort_area_retained_size in

2. memory. Larger sorts are allocating segments in

3. PSAPTEMP. They are using sort_area_size in memory.

sort_area_retained_size = 0

sort_area_size = 2097152

#sort_spacemap_size = 512

1. PGA-PARAMETERS

open_cursors = 800

#cursor_space_for_time = true

1. LOCKS

1. dml_locks = processes * 50

#dml_locks = 2500

dml_locks = 6250

enqueue_resources = 8000

1. CPU-PARAMETERS

#spin_count = 200

#_cpu_count = 4

1. TUNING ARCHIVING

#log_archive_buffer_size 127

#log_archive_buffers 4

1. USE VECTOR READ

#use_readv = true

1. OTHER TUNING PARAMETERS

#sequence_cache_entries = 100

#sequence_cache_hash_buckets = 89

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

1. PART II, CHANGEABLE PARAMETERS #

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

1. ONLINE ROLLBACK SEGMENTS

#rollback_segments = (PRS_1, PRS_2, PRS_3 )

transactions_per_rollback_segment = 20

1. CONTROL-FILES

1. the controlfiles should be mirrored to every new

2. database mount point /oracle/SHP/sapdata<#>

#control_files = (/oracle/SHP/sapdata1/cntrl/cntrlSHP.dbf, /oracle/SHP/sapdata2/cntrl/cntrlSHP.dbf, /oracle/SHP/sapdata3/cntrl/cntrlSHP.dbf)

1. AUTOMATICLY STARTED BACKGROUND PROCESSES

log_archive_start = true

1. MANAGING LOG SWITCHES

1. checkpoints occur only when switching logs, if

2. log_checkpoint_interval * size of OS blocks > size of

3. the actual redo log

log_checkpoint_interval = 0

#log_checkpoint_timeout = 0

1. PROCESS-PARAMETERS

1. The number of allocated semaphores is equal to the

2. number of processes

3. sessions = 1.2 * processes

#processes = 80

#sessions = 96

processes = 125

sessions = 150

1. AUDITING AND STATISTICS

1. sql_trace=TRUE

2. audit_trail = true

3. db_block_lru_extended_statistics = 1000

4. db_block_lru_statistics = true

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

1. PART III, STATIC PARAMETERS #

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

1. DB-NAME

db_name = SHP

1. DB-BLOCKSIZE

db_block_size = 8192

1. DB-FILES

db_files = 254

1. OPTIMIZER MODE

optimizer_mode = choose

#optimizer_search_limit = 3

1. PATHS / DESTINATIONS / TRACES

1. /oracle/SHP/saptrace/background: trace files of the background

2. processes

3. /oracle/SHP/saptrace/usertrace: trace files of the user processes

4. log_archive_dest is a destination, not a path.

5. The archivefiles get the name

6. /oracle/SHP/saparch/SHParch<thread#>_<log#>

background_dump_dest = /oracle/SHP/saptrace/background

user_dump_dest = /oracle/SHP/saptrace/usertrace

core_dump_dest = /oracle/SHP/saptrace/background

#log_archive_dest = /oracle/SHP/saparch/SHParch

log_archive_dest_1 = "location=/oracle/SHP/saparch"

#log_archive_dest_2 = "service=SHP_STDB OPTIONAL REOPEN=60"

log_archive_format = SHParch_%s.arc

1. OTHER

1. reduce_alarm only supported on HP

#reduce_alarm = TRUE

1. ORACLE OPS PARAMETER

remote_os_authent = true

transaction_auditing = FALSE

1. retention time for RMAN backup information in control file

control_file_record_keep_time = 30

1. include additional parameters

#IFILE = /oracle/SHP/817_32/dbs/init_817.ora

#additional parameter for Oracle 8.1.x

compatible = 9.2.0

log_checkpoints_to_alert = true

#IFILE = /oracle/SHP/817_32/dbs/initora.addon

hash_join_enabled = false

1. OPTIMIZER MODE

optimizer_index_cost_adj = 10

#processes = 80

1. AUDITING AND STATISTICS

timed_statistics = true

#shared_pool_size = 204010946

shared_pool_size = 650000000

#shared_pool_reserved_size = 20401094

#db_block_buffers = 37355

#db_block_buffers = 44000

db_block_buffers = 50000

control_files = ( /oracle/SHP/sapdata1/cntrl/cntrlSHP.dbf,/oracle/SHP/origlogA/cntrl/cntrlSHP.dbf,/oracle/SHP/saparch/cntrl/cntrlSHP.dbf )

MAX_ROLLBACK_SEGMENTS = 400

rollback_segments = (PRS_0,PRS_1,PRS_2,PRS_3,PRS_4,PRS_5,PRS_6,PRS_7,PRS_8,PRS_9,PRS_10,PRS_11,PRS_12,PRS_13,PRS_14,PRS_15,PRS_16,PRS_17,PRS_18,PRS_19)

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

1. PART IV, SAP RECOMMENDATION (NOTE 124361)

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

1. db_block_checkpoint_batch = 8 (no longer support /note 320540)

2. db_block_lru_latches = 2 * number of CPUs on DB

#db_block_lru_latches = 8

1. db_block_lru_extended_statistics = 0 (no longer support/note 320540)

2. db_block_lru_statistics = false (no longer support /note 320540)

db_file_multiblock_read_count = 8

db_writer_processes = 1

dbwr_io_slaves = 0

disk_asynch_io = true

#distributed_transactions = 0

#fast_start_io_target = 0

hash_join_enabled = false

#java_pool_size = 0

log_checkpoints_to_alert = true

1. log_simultaneous_copies = 2 * number of CPUs on DB

2. log_simultaneous_copies = 4 (no longer support /note 320540)

max_rollback_segments = 400

#query_rewrite_enabled = true

replication_dependency_tracking = false

1. row_cache_cursors = 300 (no longer support /note 320540)

timed_statistics = true

1. END OF PART IV ######

#Added by Seashore 20040718

optimizer_features_enable = 9.2.0

DB_DOMAIN = WORLD

btree_bitmap_plans = FALSE

pushjoin_predicate = FALSE

Current SPFILE:

*._b_tree_bitmap_plans=FALSE

*._optimizer_or_expansion='depth'

*._push_join_predicate=FALSE

*.aq_tm_processes=1

*.background_dump_dest='/oracle/SHP/saptrace/background'

*.compatible='9.2.0.0.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/SHP/sapdata/cntrl/cntrlSHP01.ctl','/oracle/SHP/sapdata/cntrl/cntrlSHP02.ctl','/oracle/SHP/sapdata/cntrl/cntrlSHP03.ctl'

*.core_dump_dest='/oracle/SHP/saptrace/background'

*.db_block_size=8192

*.db_cache_size=838860800

*.DB_DOMAIN='WORLD'

*.db_file_multiblock_read_count=8

*.db_files=1024

*.db_name='SHP'

*.dml_locks=6250

*.enqueue_resources=8000

*.event='10028 trace name context forever, level 1','10027 trace name context forever, level 1','10183 trace name context forever, level 1','10191 trace name context forever, level 1','38068 trace name context forever, level 100'

*.fal_client='SHP_std'

*.fast_start_mttr_target=300

*.hash_join_enabled=false

*.instance_name='SHP'

*.java_pool_size=0

*.job_queue_processes=10

*.large_pool_size=20971520

*.log_archive_dest_1='location=/oracle/SHP/saparch'

*.log_archive_dest_2='service=SHP_std optional reopen=60 max_failure=10 delay=1440'

*.log_archive_format='SHP_%s.arc'

*.log_archive_start=TRUE

*.log_buffer=8192000

*.log_checkpoints_to_alert=true

*.open_cursors=800

*.optimizer_features_enable='9.2.0'

*.optimizer_index_cost_adj=10

*.pga_aggregate_target=419430400

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_os_authent=true

*.replication_dependency_tracking=false

*.shared_pool_size=524288000

*.sort_area_size=2097152

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.transaction_auditing=false

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='PSAPROLL'

*.user_dump_dest='/oracle/SHP/saptrace/usertrace'

*.utl_file_dir='/oracle/SHP/saptrace/usertrace'

[Former Member]

hi aimee,

if you're working on SAP environment, use proper tools. For example, SAP have already provide you BRTOOLS to configure and manage your Oracle Database.

Use this tool otherwise may be some problem will happened.

ardhian

http://sapbasis.wordpress.com

http://ardhian.kioslinux.com

[former_member204746]

Personnaly, I prefer PFILE.

both modes are supported and the y both work. I'M not sur eit wil lfix your issue, but anyways, you can try it, it won't hurt your system..

to change to PFILE, shutdown DB, rename SPFILE, edit initSID.ora and delete the start and the dot at each line. restart DB.

good luck.

[Former Member]

Hi,

It is ok to change the DB parameter file format.

If you change the file format from SPfile to Pfile, u should delete the SPfile & vice versa.

But it hardly matters.

Regards,

Siddhartha.