Tag Archives: db files

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.
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
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.

Actual memory consumed by Oracle instance for its processes.
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 specifies the total size of all SGA components.
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 specifies the target aggregate PGA memory available to all server processes attached to the instance.


Resize operation completed for file# tempfile alert log


Resize operation completed for file# tempfile alert log

On checking one issue on Oracle Database Server, find alert log file which show me the file 1001 is resized.
But on my Server only 10 files are exists in ORADATA folder.

Resize operation completed for file# 1001, old size 1024K, new size 2048K
Resize operation completed for file# 1001, old size 2048K, new size 3072K
Resize operation completed for file# 1001, old size 3072K, new size 4096K

On checking on net found that alert log done resize operation for datafiles and tempfiles

SQL> select count(*) from v$datafile;


SQL> select file# from v$tempfile;


Then on researched found that alert log consider the tempfile number according to dba_files parameter on oracle database.

Show parameter db_files

-------- -------- --------
db_files integer 1000

So, the alert log, its using tempfile numbering with db_files parameeter value plus temp file id (file#)
Suppose db_files is 500 then tempfile of file# id 1 is consider by alert log is 501.

That’s why alert log show 1001 value in above example as resize operation:
Resize operation completed for file# 1001, old size 1024K, new size 2048K