Automatically Starting/Stopping Oracle on Redhat Linux

Oracle recommends that we should configure our system to automatically start  Database when the system starts up, and to automatically shut it down when the system shuts down. Automating database startup and shutdown guards against incorrect database shutdown .

To automate database startup and shutdown, oracle use the dbstart and dbshut scripts, which are located in the $ORACLE_HOME/bin  directory. Let's have Look on the above scripts .

1.) Dbstart :  This script is used to start ORACLE  from /etc/rc(.local). It should ONLY be executed as part of the system boot procedure.This script will start all databases listed in the oratab file  whose third field is a "Y".  If the third field is set to "Y" and there is no ORACLE_SID for an entry (the first field is a *), then this script will ignore that entry.

2.) Dbshut :  This script is used to shutdown ORACLE from /etc/rc(.local). It should ONLY be executed as part of the system boot procedure.This script will shutdown all databases listed in the oratab file whose third field is a "Y".  If the third field is set to "Y" and there is no ORACLE_SID for an entry (the first field is a *), then this script will ignore that entry.

We need to create the script which will run dbshut and dbstart scripts in the /etc/init.d directory ,whenever the machine is shutdown or start . Whenever machine boots it runs the scripts beginning with Snnname in /etc/rc3.d ,where the nn indicates the order in which the scripts will run . Similarly in case of shutdown scripts are named as Knnnames which runs from rc0.d .If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

Let's have a demo of Automate Startup/Shutdown on Linux :

Step 1 : Edit the oratab file : 

Oratab file  is created by root.sh and updated by the Database Configuration Assistant when creating a database. The first and second fields are the system identifier and home directory of the database respectively.  The third filed indicates  to the dbstart utility that the database should , "Y", or should not, "N", be brought up at system boot time .In my case it is comcast:/home/oracle/product/10.2.0/db_1:N

So it edit as
comcast:/home/oracle/product/10.2.0/db_1:Y
Where my database name is comcast and ORACLE_HOME is  "/home/oracle/product/10.2.0/db_1"


Step 2 : Create a file called dbora and add the below lines 
[root@tech ~]# cd /etc/init.d
[root@tech init.d]# vi  dbora
add the following line

#! /bin/sh  -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
ORACLE_HOME=/home/oracle/product/10.2.0/db_1
#
# Change the value of ORACLE to the login name of the
# oracle owner at your site.
#
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
#
if [ ! "$2" = "ORA_DB" ] ; then
   if [ "$PLATFORM" = "HP-UX" ] ; then
      remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB"
      exit
   else
      rsh $HOST -l $ORACLE  $0 $1 ORA_DB
      exit
   fi
fi
#
case $1 in
'start')
        if [ "$PLATFORM" = "Linux" ] ; then
          touch /var/lock/subsys/dbora
        fi
        $ORACLE_HOME/bin/dbstart $ORACLE_HOME &
        ;;
'stop')
        $ORACLE_HOME/bin/dbshut $ORACLE_HOME &
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit
        ;;
esac

Step 3 : Change the group of the dbora file and set the permission 
[root@tech init.d]# chgrp dba dbora
[root@tech init.d]# chmod 750 dbora



Step 4 : Create symbolic links to the dbora 

Create symbolic links to the dbora script in the appropriate run-level script directories as follows.We need to add the appropriate symbolic links to cause the script to be executed when the system goes down, or comes up.

[root@tech init.d]# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora

[root@tech init.d]# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
[root@tech init.d]# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora


Step 5 :  Test the scripts 
We can check the script by restarting the machine . Other alternative method  is

[root@tech init.d]#  /etc/init.d/dbora start (for startup)
[root@tech init.d]#  /etc/init.d/dbora stop  (for shutdown)

Above command will work same as we start the services in Window .


Enjoy      :-) 

Happy Birthday To Me

Today is my birthday , 13th March .  I will wish myself  a Very Very Happy Birthday To Me and may god fulfill  my wishes and dreams .

I am honor to say it was my year of Joy, Happiness great turn around and l thank God for all . He has in place for me in many years to come,am just so happy another new year is added to my new sweet life, Glory to God for everthing . He has done and more to do.

Here is a nice Poem which is dedicated to myself .    :)

I'm not afraid of counting years;
Each year is a new myth exploded.
I'm not afraid of wrinkles and gray hair;
This body is how I navigate through the cosmos, so I try to love it and be
kind.
I'm not afraid of death; It is only the final myth to unravel. I'm not even afraid of the devil,
because it is our adversary who gives us life's most precious gifts. I'm
ftwenty years old and lost things are coming back:
Emails from long vanished friends
Bits of shattered faith discovered in between the cracks of the couch and
under the edges of the carpet that I'm piecing together into a mosaic New things
arrive every day too:
New poems
New stories spinning out of my brain
A new sense of my place in the old story
New meaning in the ancient struggle for justice Each year is a new crossroads. 
    Happy birthday to me!!!!

             
          Celebrating the colors  of our beautiful relationship, 
                         I wish you and your family all 
                            the bright hues of life.
                             Have a colourful holi !

         May God gift you all the colors of life ;  colors of joy, 
      colors of happiness ,   colors of friendship ,  colors of love
   and  all  the  other  colors ,  you  want  to paint your life with.
      

                         Happy Holi To You.


 Enjoy   :-) 

Difference between Dataguard and Active Dataguard

I  found  people are bit confused between Dataguard and Active Data guard . They assume that Active dataguard is having different configuration or properties .  Here, i have tried to cover the Active dataguard .

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition . Oracle Active Data Guard enables read-only access to a physical standby database . Active Data Guard is the possibility to query  our Physical Standby Database while it is constantly actualized to reflect the state of the Primary Database . It is additional to 11g Data Guard and comes with an extra charge .

Oracle Active Data Guard enhances the Quality of Service (QoS) for production databases by off-loading resource-intensive operations to one or more standby databases, which are synchronized copies of the production database. With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups. Oracle Active Data Guard thus is a very effective way to insulate interactive users and critical business tasks on the production system from the impact of such long-running operations. Oracle Active Data Guard provides the additional benefit of high availability and disaster protection by quickly failing over to the standby database in the event of a planned or an unplanned outage at the production site.

The Active Data Guard  contains the following features :

• Physical Standby with Real-time Query
• Fast Incremental Backup on Physical Standby
• Automatic Block Repair

If a physical standby database in a Data Guard configuration has any of the above features enabled, then the Active Data Guard option must be licensed for every such physical standby, and also for the primary database.

Conversion from Physical standby to Active Data Guard :
We can convert the physical standby  into active Data Guard standby . Below are the steps

1.) Stop Apply Services
SQL> alter database recover managed standby database cancel ;

2.) Shut the database and open in mount stage
SQL> shut immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect ;
SQL> alter database open ;
It enables us to have a physical standby read only open, while redo apply is still done in the background .

How to Check if Active Data Guard is  Enabled  or Not 
Use the following query to confirm that Data Guard is in active mode:

SQL> select  'Using Active Data Guard' ADG   from v$managed_standby m,v$database d where m.process like 'MRP%'  ;
        ADG
-----------------------
Using Active Data Guard

or from standby datbase

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE                              CONTROLFILE
--------------------                        ------------------
READ ONLY WITH APPLY            STANDBY


If the query does not return the above result, and instead returns : no rows selected, then Active Data Guard is not enabled.


Enjoy     :-) 

Difference Between Unique Indexes and Unique Constraints

There is a very general confusion that whenever we create a unique key constraint or primary key then a corresponding index is created . Primary key and Unique key  creates the unique indexes , but this is not always true .  Lets have a look ...

SQL> create table T1 (id number ) ;
Table created.

SQL> alter table T1 add constraint  T_ID_IDX  unique(id) ;
Table altered.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T1';
INDEX_NAME         TABLE_NAME            UNIQUENES
----------------        -------------------         ---------------
T_ID_IDX                  T1                            UNIQUE

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T1' and owner='HR' ;

CONSTRAINT_NAME      C         TABLE_NAME
------------------------      ----       -----------------
T_ID_IDX                      U             T1

Here, we see that when we have created a table unique index get created . Now have another look ..

SQL> create  table  T2 (id number )  ;
Table created.

SQL>  create  unique  index  T2_id_idx  on  T2(id) ;
Index created.

SQL> select index_name,table_name,uniqueness from dba_indexes where table_name='T2'  ;
INDEX_NAME        TABLE_NAME       UNIQUENES
------------------     ------------------      ---------------
T2_ID_IDX                      T2               UNIQUE

SQL>  select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;

no rows selected

SQL> alter table T2  add constraint  T2_ID_IDX    unique(id) ;
Table altered.

Now, we expecting  two indexes i.e;  one from the unique index and other from unique constraints . let's look on the below query :

SQL> select constraint_name,constraint_type ,table_name from dba_constraints where table_name='T2' and owner='HR' ;

CONSTRAINT_NAME       C         TABLE_NAME
-------------------------     ----        ------------------
T2_ID_IDX                      U                  T2

SQL> drop index T2_ID_IDX;
drop index T2_ID_IDX
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

Above query show only one indexes  . Hence from the above demo, we can only say that  " a unique constraint does not necessarily create an index or a unique constraint does not necessarily create a UNIQUE index "  .

If  we want a unique index in place, it is suggested we should explicitly create it by  using CREATE UNIQUE INDEX .  A primary key or unique constraint is not guaranteed to create a new index, nor is the index they create guaranteed to be a unique index.  Therefore, if  we desire a unique index to be created for query performance issues, we should explicitly create one. 

A question may arises that why do we need a unique constraint when we already have a unique index?
The reason are

1. ) The difference between a unique index and a unique  constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command  .

2.) we can use either a unique OR non-unique index to support a unique constraint. Constraints are metadata, more metadata is good. We can define a foreign key to a unique constraint, not so a unique index. 

3.) A constraint has different meaning to an index. It gives the optimiser more information and allows us to have foreign keys on the column  whereas a unique index doesn't. But most importantly because  it is the right way to do it. 

Enjoy     :-)