Switchover and Failover in Standby Oracle 11g

Data Guard uses two terms when cutting over the standby server, switch-over which is a planned and failover which a unplanned event .

1.) Switchover: Switchover is a planned event, it is ideal when we might want to upgrade the primary database or change the storage/hardware configuration (add memory, cpu networking), we may even want to upgrade the configuration to Oracle RAC .

What happens during a switchover is the following :


1.) Notifies the primary database that a switchover is about to occur
2.) Disconnect all users from the primary database
3.) Generate a special redo record that signals the End of Redo (EOR)
4.) Converts the primary database into a standby database

5.) Once the standby database applies the final EOR record, guaranteeing that no data loss has been lost, converts the standby database into the primary database.

The new standby database (old primary) starts to receive the redo records and continues process until we switch back again. It is important to remember that both databases receive the EOR record so both databases know the next redo that will be received. Although we can have users still connecting to the primary database while the switchover occurs (which generally takes about 60 seconds) I personal have a small outage just to be on the safe side and just in case things don't go as smoothly as I hoped.

We can even switch over form a linux database to a windows database from a 64 bit to a 32 bit database which is great if we want to migrate to a different O/S of 32/64 bit architecture, also our rollback option is very easy simply switchback if it did not work.

2.) Failover: Failover is a unplanned event, this is where the EOR was never written by the primary database, the standby database process what redo it has then waits, data loss now depends on the protection mode in affect .

• Maximum Performance - possible chance of data loss
• Maximum Availability - possible chance of data loss
• Maximum Protection - no data loss

we have the option to manually failover or make the whole process automatic, manual gives  the DBA maximum control over the whole process obliviously the the length time of the outage depends on getting the DBA out of bed and failing over. Otherwise Oracle Data Guard Fast-Start Failover feature can automatically detect a problem and failover automatically for us. The failover process should take between 15 to 25 seconds.

Which Role Transition Operation Should I Use  ?

When faced with the decision on which role transition is best for the given situation, we need to always choose one that best reduces downtime and has the least potential for data loss. Also to consider is how the change will affect any other standby database in the configuration. We should consider the following when making the decision on which operation to use:

• What is the current state of the primary database just before the transition? Is it available?
• What is the state of the selected standby database to be used in the role transition at the time of transition?
• Is the standby database configured as a physical or logical standby database?

The following decision tree can be used to assist when making this critical decision as to which operation to perform:

One key point to consider is that if it would be faster to repair the primary database (from failure or a simple planned hardware/software upgrade), the most efficient method would be to perform the tasks and then to bring up the primary database as quickly as possible and not perform any type of role transition. This method can impose less risk to the system and does not require any client software to be re-configured.

Another consideration involves a Data Guard configuration which includes a logical standby database. A switchover operation can be performed using either a physical or logical standby database. Take note, however, of the following issues you may run in to regarding physical and logical standby configurations. If the configuration includes a primary, a physical standby, and a logical standby, and a switchover is performed on the logical standby, the physical standby will no longer be a part of the configuration and must be rebuilt. In the same scenario, if a switchover operation is performed on the physical standby, the logical standby remains in the Data Guard configuration and does not need to be rebuilt. Obviously, a physical standby is a better option to be a switchover candidate than a logical standby when multiple standby types exist in a given configuration.

Hence finally we come to conclusion that the order to setup Data Guard is the following : 

• The primary database is up and running
• Create a standby database
• Setup the redo transport rules
• Create the SRL files 
• Execute one of the Following 

SQL> Alter database set standby to Maximum Performance; //(Default)

SQL> Alter database set standby to Maximum Availability;

SQL> Alter database set standby to Maximum Protection;

How to Reduce DB File Sequential Read Wait

DB File Sequential Read wait event occurs when we are trying to access data using index and oracle is waiting for the read of index block from disk to buffer cache to complete.  A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.Db file sequential read wait events may also appear when undo blocks are read from disk in order to provide a consistent get(rarely).

To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait .  A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2 indicates a file header read) ,where p1,p2 and p3 gives the the absolute file number ,the block being read ,and  the number of blocks (i.e, P3 should be 1) respectively. 

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance.Hence to reduce this wait event follow the below points .

1.) Tune Oracle - tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.) Tune Physical Devices - Distribute(stripe) the data on diferent disk to reduce the i/o . Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
3.) Faster Disk - Buy the faster disk to reduce the unnecessary I/O request .
4.) Increase db_block_buffers - A larger buffer cache can (not will, "might") help .

How to Exclude Tablespace from Rman Backup ?

Sometimes we  may want to omit a  specified  tablespace  from  part of  the regular  backup schedule. Suppose  in  a tablespace  the data don't change  or  the tablespace  contains test  data only or  sometimes  a scenario may occur when we are  clonning  the database  using   the  rman backup where we  do not  need all  the schemas . In such cases, either we  might change our  backup  strategy or  skip the certain tablespace  in  the database. 

To overcome this type of issue, we can configure the exclude option to exclude the specified tablespace from the Backup Database command . Though we can generally skip the tablespace during the Backup Database command but only when the tablespace is offline or readonly .The exclusion condition applies to any data files that we add to this tablespace in the future. Below are the steps to configure the Exclude  option 

C:\> rman  target  /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 28 18:58:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: NOIDA (DBID=1523131116)


RMAN> configure exclude for tablespace "EXAMPLE" ;
Tablespace EXAMPLE will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

To check this parameter , use the below command 

RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name NOIDA are:
CONFIGURE EXCLUDE FOR TABLESPACE 'EXAMPLE';

If  "exclude" option is configured ,  even then we can backup the excluded tablespsace by explicitly specifying them in a Backup command or by specifying the NOEXCLUDE option on a "Backup Database" command as 

RMAN> backup database noexclude ;

or
RMAN> backup tablespace example ;
Starting backup at 29-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\NOIDA\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 29-DEC-11
channel ORA_DISK_1: finished piece 1 at 29-DEC-11
piece handle=E:\RMAN_BACKUP\0UMVEC3G_1_1 tag=TAG20111229T150832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-DEC-11

We can disable the exclusion feature tablespace example as : 

RMAN> configure exclude for tablespace example clear ;
Tablespace EXAMPLE will be included in future whole database backups
old RMAN configuration parameters are successfully deleted


RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name NOIDA are :
RMAN configuration has no stored or default parameters


In order to skip  READONLY  and  OFFLINE  tablespace we can issue backup database command as,
RMAN>backup database skip readonly, skip offline ;