Oracle DB Set UP & Tuning

Basic thing needs to be taken care during ORACLE Installation

1.Install recent version of oracle DB, if you are still using oracle 10g or earlier version, please upgrade to oracle 11g.

2.When do you install Oracle 10g don’t simply click on next please read carefully the suggestions while you installing the oracle DB.

3.Please allocate RAM properly to your oracle instance while you installing the DB.

4.Never install in C drive

5.If you want to install in C drive please refer your table space path to enough hard drive partition

6.Please avoid to use User Table Space(Default one),If you map your user to user table space it leads to the issue if you want perform any action on your user Table Space.

What is Table Space?

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

Simply your user l data will be stored in to it.

Note: You can’t read it. If you have back up of it which means your Data is safe to restore during critical issues.

Assume now you installed oracle and it’s ready to use. Are you a single user?
No Worry!

Are multiple users needed to access the machine?

Then you have to tune your DB to support multiple sessions and processes. If you have n’t tuned your DB properly, you may leads to face following errors.

What are Processes?

Please refer the link below:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm

Please refer this video how sessions will be established

http://www.kendba.com/sample-chapters/oracle-session-video/

Let’s assume now you are clear with DB setup, Tablespace, Processes and sessions.

How to identify what are your current processes and sessions?

i)Open your command Prompt
ii)Login as sys as sysdba
iii)Enter your password
iv)Write a query as Show Parameters.

The above query will list you all Parameters or use the below query to identify the most important parameters

SELECT name, display_value FROM v$parameter WHERE name IN ('shared_servers', 'max_shared_servers', 'memory_target''sessions', 'processes', 'session_cached_cursors', 'sga_target', 'pga_aggregate_target', 'compatible', 'open_cursors', 'db_name','cpu_count','cursor_sharing')ORDER BY name;

Hint: All parameters of oracle will be stored in PFILE or SPFILE

Where does it locate?

It depends on OS but you can identify Pfile under admin folder and SPfile under DBS folder.

What is PFILE?

The PFILE is a text-based file, and the “init.ora” file has been around for over a decade. Inside the PFILE are a number of database settings called parameters. These parameters help the Oracle programs know how to start. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist.

Note: The PFILE can be edit to modify the parameters.

What is SPFILE?

The SPFILE is different from the PFILE in that it cannot be directly edited. This is because it has a header and footer that contain binary values. Since you cannot change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.

To understand more:-

The parameter of Sessions, processes, SGA_Target, PGA_Aggreagate_Target, Open_cursors can be set through the above files. When your oracle instance starts it refers anyone of the above file.

How to identify your instance using PFILE or SPFILE?

Run the below query in SQL prompt
Show parameter spfile.

If it returns null which means the instance runs with PFILE else SPFILE.

Administering SPFILE and PFILE:-

You can’t edit your SPFile directly as I mentioned earlier. If you want to edit you can use alter command like below

Sql>Alter system set V$Parameter= scope=spfile;

You need to shutdown and restart your instance once you executed the alter command.
Hint: Never modify SPFILE manually if you did so your instance can’t be started.
Hint: Never increase the sessions and processes tremendously high; if you did so your instance can’t be started.

How to handle such situation if you lead to such situation unfortunately?

Simple copy your SPFILE from PFILE and restart your instance.

HINT: Please take back up of these files.

How to copy SPFILE from PFILE?

SQL> create spfile from pfile=/path/to/backup.ora

How to increase sessions and Processes?

Before you increasing session and processes you should consider below formulation
Sessions= (1.1 * PROCESSES) + 5
Sql> alter system set sessions= scope=spfile;
Sql> alter system set processes= scope=spfile;

What are SGA and PGA?

Please refer the below link
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#i10093

When to increase SGA and PGA Parameter?

Only if you face any memory related errors like
ORA-04030: out of process memory when trying to allocate 57868 bytes

How to change SGA_Target?

Please refer the below link:
http://www.orafaq.com/wiki/SGA_target

I post rest in my spare time later….