How to start database tuning and how to know, what to tune?

How to start database tuning and how to know, what to tune?

This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?

Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.

Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.

So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.

What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.

Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)

And remember that the person suffering from last stage of cancer do not need medicine for headache.

Share your thoughts for the same. Have a happy weekend..

Redefine your funda of "How to crack interview?"

Recently I give interview in one MNC (Multinational company). After few initial general discussion, she ask me to clarify difference between standby database and dataguard. I surprise by this question at first instant because I accept more challenging and discussion about some problematic scenario. In reply of this question I give clarification like one given below:-

Dataguard is set of process(you can call it infrastrcutre/architecture provided by Oracle) to configure standby database in order to protect your database against disaster.

But,.... But she was not convince by my explanation and stick to her question and then she redefine her question and asked me in another way:- why we want dataguard if we configure standby manually.

I thought, she wants more detailed answer for her initial question, So I try and give it as mention below

In absence of dataguard you have to transfer archive redo log manually(or by some code written by you) from primary database to standby database and then you have to apply it on your standby database. This require lots of manual work and efforts. To overcome this Oracle has its own environment/architecture to complete this task automatically, what you need is, just configuration of environment, and then oracle take care of all this stuff.

 After this explanation, I found that still she was not happy by my explanation(not even 1% !!) though my explanation is correct, and she want something else. I don't know, what she was expecting as answer of her question. Still, I am wondering what are the other possible explanation that I miss at that time, Please, let me know if you have some better explanation :) 

Moral of the story:- It is not important what is wrong and what is right during interview process, the important thing is, you should have answer what your interviewer has in her mind (No maters, wrong or right).

Physical standby database configuration (Oracle 9i)

Hi, this is my first blog, I am little bit late on blogging. But, it is good to be late then never.

I decide to start with standby database configuration because lots of people still confuse for data guard. This configuration is for Oracle 9i, little bit old, but still this is very good for beginner to understand architecture of standby database and to familiar with terminology used.

I intentionally keep this very simple to maintain full concentrate on standby database configuration.

Let me first explain my requirements and technical details before starting configuration.

Scenario:- (This is for windows machine.)

I want to create physical standby database for one database named “PriDB” running on host machine named “PrimaryHost”. The database is in Archive Log mode with automatic archiving enable (If it is not in Archive Log mode then first you should place database in Archive Log mode).

I take another server where I want to host my standby database. The name of that machine is “StandbyHost”. I installed Oracle software on it without database.

In next step I have to take backup of my primary database “PriDB”. For that, first I execute following query to get information of data files of primary database “PriDB”.

Sql> SELECT NAME FROM V$DATAFILE;

C:\ORACLE\ORADATA\SSDB\SYSTEM01.DBF

C:\ORACLE\ORADATA\SSDB\UNDOTBS01.DBF

C:\ORACLE\ORADATA\SSDB\CWMLITE01.DBF

C:\ORACLE\ORADATA\SSDB\DRSYS01.DBF

C:\ORACLE\ORADATA\SSDB\EXAMPLE01.DBF

C:\ORACLE\ORADATA\SSDB\INDX01.DBF

C:\ORACLE\ORADATA\SSDB\ODM01.DBF

C:\ORACLE\ORADATA\SSDB\TOOLS01.DBF

C:\ORACLE\ORADATA\SSDB\USERS01.DBF

Now, To take backup, I shutdown (consistent shutdown) primary database “PriDB”, and take physical copy of all data files which is listed above and move it into the standby host (in my case it is “StandbyHost”.).

Note:- You can use RMAN to create standby database without stopping primary database, refer online manual for that

After copying databases to standby host, startup “PriDB” database to create physical standby control file. This control file later on used as control file for standby database.

Sql> Startup pfile=’c:\oracle\ora92\database\initPriDB.ora’

Sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'd:\StbCntrl.ctl’

    Once, standby control file created, move it to the standby host.

      Now, we have all required data files and control file for standby database, now it is require to configure tnsnames.ora, listener.ora 

and initialization parameters file  for both databases.

     To establish connection between primary database and standby database it is require to setup oracle net correctly. 

 At Primary Database site:-

       Verify your listener.ora is properly configure or not for SID “PriDB”. Add one entry of connection 

service for standby database in tnsnames.ora called it “StbyConn”. Don’t try to test the service for connection 

with standby database, as there is no standby database created yet. Just make an entry which is used later on.

Sample of Net config files at PrimaryHost site 

Listener.ora

LISTENER = 

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

    SID_DESC =

      (SID_NAME = PriDB)

      (ORACLE_HOME = C:\oracle\ora92)

    )

  )

TnsNames.ora

StbyConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

PriDb =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

At Standby Database site:-

      As there is no listener.ora and tnsnames.ora on standby database site we have to create it manually. For this, 

I copy these files from primary database and modify it for standby database. First correctly configure listener.ora 

for the SID “PriDB” which is same as primary database SID. (In our case the standby and primary database name are same.)

Now add two entry of service name in tnsname.ora file. One service name is use to make connection with Primary Database, 

called “PriConn”. Another service name for standby database, called “StbyPriConn”. Keep these two files at standby host site, 

we need these files later on.

Sample of Net config files at StandbyHost site 

Listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

    SID_DESC =

      (SID_NAME = PriDB)

      (ORACLE_HOME = C:\oracle\ora92)

    )

  )

TnsNames.ora

PriConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

    )

   (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

StbyPriConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

    Now, copy initialization parameters file of primary database “PriDB” called “InitPriDB.ora” and put it in temp location, 

rename new file to “InitStbPriDB”, which is used for standby database. We have to make some changes in both initialization parameters files.

    It is not require to do changes in all initialization parameters, only some of parameters need to change. 

(1) Keep db_name identical for Primary database and Standby database.

(2) Specify "StandbyHost" as one of destination for Archive redo log files generated at Primary Database site by specifying ,

 Log_archive_dest_2='service= StbyConn MANDATORY reopen=120'

Here, “StbyConn” is service name for standby database which we configure earlier in TnsNames.ora file on PrimaryHost.

(3) Set Remote_archive_enable=send [or true] for PraimryHost and set Remote_archive_enable=receive [or true] for StandbyHost

Note:- Remote_archive_enable is deprecated in oracle 10g. 

(4) Background_dump_dest, User_dump_dest, Core_dump_dest should point to valid physical location in InitStbPriDB.ora

(5) Change control_files parameter in InitStbPriDB.ora by specifying 

 control_files='C:\oracle\oradata\PriDB\StbCntrl.ctl'

 Here, “StbCntrl.ctl” is standby control file which we create from primary database earlier.

(6) To configure FAL service we have to specify two parameters in IniStbPriDB.ora

  Fal_server= PriConn

  Fal_client= StbyPriConn

Here, PriConn  is for Primary Database and StbyPriConn is for Standby Database, which we configure in TnsNames.ora file at standbyHost earlier.

(7) Check Log_archive_dest_1 in InitStbPriDB.ora point to valid location or not. If not then make modification in it.

(8) standby_archive_dest='c:\oracle\oradata\RecArc', is used to specify location in “StandbyHost” where Archive redo log files of primary database 

are placed by Log Transport Service.

Below is listing of sample initialization parameter files for Primary Database and Standby Database.

InitPriDB.ora

*.background_dump_dest='C:\oracle\admin\ PriDB \bdump'

*.core_dump_dest='C:\oracle\admin\ PriDB \cdump'

*.control_files='C:\oracle\oradata\ PriDB \CONTROL01.CTL','C:\oracle\oradata\ PriDB \CONTROL02.CTL','C:\oracle\oradata\ PriDB \CONTROL03.CTL'

*.db_block_size=8192

*.db_cache_size=4194304

*.db_name='PriDB'

*.fast_start_mttr_target=300

*.instance_name='PriDB'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=8388608

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=134217728

*.shared_pool_size=50331648

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.user_dump_dest='C:\oracle\admin\PriDB\udump'

*.log_archive_start=TRUE

*.Log_archive_dest_1='LOCATION=C:\oracle\oradata\PriDBArc MANDATORY'

*.Log_archive_dest_2='service= StbyConn MANDATORY reopen=120'

*.Log_archive_dest_state_1=enable

*.Log_archive_dest_state_2=enable

*.Remote_archive_enable=send

InitStbPriDB.ora

*.background_dump_dest='C:\oracle\admin\PriDB\bdump'

*.control_files='C:\oracle\oradata\PriDB\StbCntrl.ctl'

*.core_dump_dest='C:\oracle\admin\PriDB\cdump'

*.db_block_size=8192

*.db_cache_size=4194304

*.db_name='PriDB'

*.fast_start_mttr_target=300

*.instance_name='PriDB'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=8388608

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=134217728

*.shared_pool_size=50331648

*.undo_management='AUTO'

*.undo_retention=10800

*.user_dump_dest='C:\oracle\admin\ PriDB \udump'

*.Fal_server= PriConn

*.Fal_client= StbyPriConn

*.log_archive_start=False

*.Remote_archive_enable=receive

*.standby_archive_dest='c:\oracle\oradata\RecArc'

Aahhh!!! Now we have all require files are ready to setup new standby database. We have “standbyHost” ready with Oracle software installed into it, we will create database into that. First place the Data files with same directory structure as it is for Primary database and place control files in location specified by control_files init parameter. Place “InitStbPriDB.ora” in <OracleHome>/Database/. Place listener.ora and TnsNames.ora in <OracleHome>/Network/Admin/

To create oracle instance/service for PriDB in StandbyHost, execute following command from command prompt.

C:/> oradim –NEW –SID PriDB –IntPwd PasswordForSys

This will create one instance / service and password file for the PriDB on standByHost. Now Standby Database setup is complete. Perform following steps to start working of Standby database architecture.

On Standby Database

Sql> Startup Nomount

Sql> Alter database mount standby database;

To place standby database in managed recovery mode execute following command (in this mode Log apply service apply Archive redo log file which are coming from Primary database)

Sql> Alter database recover managed standby database disconnect from session;

The inclusion of “disconnect from session” option in sql comand will start MRP (Managed Recovery Process) in background and return control after executing command.

On Primary Database

Sql>Shutdown immediate

Sql> startup pfile=’<OracleHome>/Database/InitPriDB.ora’;

To test these implementations create one table on Primary Database and insert data into it.

Sql> Create table TestStandby ( col1 number, col2 char(1000));

Sql> Insert into TestStandby values(1,’Pratik’);

Sql> Insert into TestStandby select * from TestStandby;

Sql>/

If Archive file is not generated then generate Archive redo log file manually using sql command and verify same archive log file is created or not on “StandbyHost” site (Archived log files are put on “StanbyHost” at location pointed by “standby_archive_dest” parameter, if this parameter is not specified then default location will be <OracleHome>/RDBMS).

Sql> Alter database Archive log current;

If Archived redo log file successfully transformed on “StandbyHost”, then to test whether Log Apply Service works properly or not we need to query table which we create on primary database.

To query on standby database we have to first stop Managed Recovery and open it in read only mode by executing any of following set of sql commands.

Sql> Alter database recover managed standby database cancel;

Sql> Alter database open read only;

OR

Sql> shutdown immediate

Sql> Startup Nomount

Sql> Alter database mount standby database;

Sql> Alter database open read only;

Once standby database open in "Read only" mode, we can query from table “TestStandby” which we created on Primary Database.

Note:- When Physical standby Database is open in Managed Recovery Mode at that time you can’t query from standby Database(In case of Oracle 11g, things are change now). To query or generate report from Standby Database you need to first open it in Read only mode.

I hope this help in clearing doubts without adding new one.