Tag Archives: spfile parameter

Considering the init parameters while creating Oracle database

Take care Parameters while creating the new Database in Oracle

Following are the parameter you need to take care before delivery the database to production environment for avoid future downtime.
Because in production environment once it live, you need to change this parameter you need to bounce the database. In Critical environment, you never ignore this parameters

DB_FILES = (default 200 recommended 1000)
Set of maximum number of files that in a database in future you need to add more data files in database. Handle error ORA-00059: maximum number of DB_FILES exceeded
PROCESSES = (default 150 recommended 600)
Set of maximum number of processes that can be started by instance. Handle error ORA-00020: maximum number of processes (%s) exceeded
SESSIONS = (default 172 )
It automatically set from the processes parameter when you change the processes parameter with alter system command. Handle error ORA-00018.Maximum number of sessions exceed
TRANSACTIONS = default 189
It automatically change when you configure the processes parameter
SESSION_CACHED_CURSORS = default 50 increase to 300
In a session you can open maximum number of cursor.
UNDO_RETENTION = default 900
Please increase the undo retention to 1800, it will increase performance and overcome ORA-01555 Snapshot Too Old error in alert log.
SMTP_OUT_SERVER =
If you need to configure something from mail in future then setup the parameter in advance.
OPEN_CURSORS = 300 default
OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once and overcome with error ORA-01000: maximum open cursors exceed
AUDIT_TRAIL = NONE
Check the audit trail parameter by default it is set to DB level. If you need the audit then remains enable otherwise disable it in advance.

Memory parameter
In Oracle we have 3 major memory components:

MEMORY TARGET is that component which manages all other component means SGA + PGA. It manage according to run time data basis. It is best method. you can set in with two following parameter.
If you set memory target then always keep MEMORY_MAX_TARGET with some greater value so that you don’t need to restart the database in future if you need to add more memory in MEMORY_TARGET.

MEMORY_TARGET
Actual memory consumed by Oracle instance for its processes.
MEMORY_MAX_TARGET
Always set greater value then memory_target parameter. So, in future you can increase memory target without restart the database.

SGA Target having other components like shared pool, large pool, java pool, db cache etc. It manage the size of all the component in run time as needed.

SGA_TARGET
SGA_TARGET specifies the total size of all SGA components.
SGA_MAX_SIZE
SGA MAX SIZE is set greater value than SGA TARGET Parameter, So in future you can increase SGA size without restart the database.

PGA_AGGREGATE_TARGET
PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Advertisements