User Managed Hot Backups in Oracle

A cold backup does have the somewhat bad side effect of wiping out our shared pool, our buffer cache and preventing our users from logging in to do work. Our database is  like a car, it runs better when it is warmed up.  If  we want to cold start it - be  prepared for rough running when we restart as we have to rebuild that shared pool, that  buffer cache and so on . I would never pick cold over hot given the chance.  No benefit, only downsides (Acc. to Tkye). The only kind of backup we do on our production systems here is hot .

There are two ways to perform Oracle backup and recovery : 

1.)  Recovery Manager (RMAN) : It is an Oracle utility that can backup, restore, and recover database files. It is a feature of the Oracle database server and does not require separate installation.

2.) User-Managed backup and recovery : We use operating system commands for backups and SQL*Plus for recovery. This method is  called user-managed backup and recovery and  is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.

There are basically two types of  backup .The backup are as 

1.) Consistent Backup :  This is also know as Cold Backup . A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time .

2.) Inconsistent Backup :  This is also known as Hot backup . An inconsistent backup is a backup in which the files being backed up do not contain all the changes made at all the SCNs . This can occur because the datafiles are being modified as backups are being taken. 

There are  some DBAs which prefer oracle user-managed backups.They put their database into backup mode prior to backing  up and take it out of backup mode after backup. If  we 're going to perform user-managed backups, we must back up all of the following file : 

• Datafiles
• Control files
• Online redo logs (if performing a cold backup)
• The parameter file (not mandatory )
• Archived redo logs
• Password file if used

The below diagram shows the Whole Database Backup Options :  

A hot backup requires quite a bit more work than cold backup.Below are steps required for Hot backup.

Step 1 :  Check the log mode of the database  Whenever we go for hot backup then the database must be in archivelog  mode . 
SQL> SELECT LOG_MODE FROM V$DATABASE ;
LOG_MODE
---------------
ARCHIVELOG

Step 2 :  Put the database into backup mode  If we are using the oracle 10gR2 or later , then we can put the entire database into backup mode and if we are using the oracle prior to 10gR2 ,then we have to put each tablespace in backup mode . In my case , I am having 11gR2 . 
SQL> alter database begin backup ; 
Database altered.
In case of oracle prior to 10gR2 use the below command as 
SQL> set echo off 
SQL> set heading off 
SQL>  set feedback off 
SQL> set termout  off 
SQL> spool backmode.sql 
SQL> select 'alter tablespace  '||name||'  begin backup ;'   "Tablespace in backup mode"  from v$tablespace;
SQL> spool off 
SQL>  @C:\backmode.sql 

Step 3 :  Backup all the datafiles  Copy all the datafile using the operating system command and Paste it on the desired backup location .Meanwhile,we can verify the status of the datafile by using the v$backup view  to check the status of the datafiles.

SQL> select  *  from  v$backup ; 
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                3967181 03-APR-12
         2 ACTIVE                3967187 03-APR-12
         3 ACTIVE                3967193 03-APR-12
         4 ACTIVE                3967199 03-APR-12
         5 ACTIVE                3967205 03-APR-12
         6 ACTIVE                3967211 03-APR-12
         7 ACTIVE                3967217 03-APR-12
         8 ACTIVE                3967223 03-APR-12
         9 ACTIVE                3967229 03-APR-12
The Column STATUS=ACTIVE  shows that the datafiles are in backup mode . 

Step  4  : Take out the database from backup mode  If we are using 10gR2 or above version of oracle , we use the below command to take out the database from backup mode as 

SQL> alter database end backup ; 
Database Altered 
If we are having version prior to 10gR2 , then we use the below command as above : 
SQL> set echo off 
SQL> set heading off 
SQL> set feedback off 
SQL> set termout  off 
SQL> spool end_mode.sql 
SQL> select  'alter tablespace  '||name||'  end backup ;'   "tablespace in backup mode"  from v$tablespace ; 
SQL> spool off 
SQL> @C:\endmode.sql 

Step 5 :  Switch the redolog file and backup archivelogs   After taking the database out of Hot Backup we must switch logfile (preferably more than once) and backup the archivelogs generated .We may backup archivelogs while the database is in backup mode but we must also backup the first archivelog(s) after the end backup. The best method to do both is to run the SQL command alter system archive log current. This switches the logfile but does not return the prompt until the previous redo log has been archived. We can run alter system switch logfile, but then we won't be sure that the latest redo log has been archived before we move on to the next step. 

SQL> alter system archive log current ; 
System altered.
SQL> / 
System altered.
Now backup the archivelogs to the backup location .


Step 6  : Back up the control file  Now , we can backup the controlfile as binary file and as human readable .We should use both methods to back up the control file; either one may come in handy at different times . The commands are as 
(Human readable)
SQL> alter database backup controlfile to trace ;  or 
Database altered.
SQL> alter database backup controlfile to trace as '<backup location>' ; 
Database altered.
(Binary format)
SQL> alter database backup controlfile to '<backup location>' ; 
Database altered.

Step 7 : Backup the passwordfile and spfile  We can backup the passwordfile and spfile though it is not mandatory.

Some Points Worth Remembering 

• We  need to backup all the archived log files, these files are very important to do recovery. 
• It is advisable to backup all of  tablespaces (except read-only tablespaces), else complete recovery is not possible.
• Backup of online redo log files are not required, as the online log file has the end of backup marker and would cause corruption if used in recovery.
• It is Preferable to start the hot backups at low activity time.
• When hot backups are in progress we  "cannot" shutdown the database in NORMAL or IMMEDIATE mode (and it is also not desirable to ABORT).

For More Click Here 


Enjoy    :-) 

Again A Time To Celebrate !!!!!!!

Today , my blog visitor's hits crossed 50,000 and offcourse a time to celebrate this moment . I am blogging from last year (i.e, march 2011)  and almost taken one year to acheive this . This month is very special to me as ever. There are lots of thing to celebrate in this month . Holi one of my favourite festival , My Birthday and this time my Blog Hits and others .

This blogs helps to find some  good friends whom i have never met and they are very kind and helpful to me. Whenever I am publishing any post on my blog, it boost up my confidence and motivate me to post  further . Every comment by you all is helping me to go more deep . Kindly free feel to comment and ask your doubt and i will try my best to give  solution .

I need  your support and wishes  to continue posting in future .  


Cheers  !!!!!!!

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      :-)