Database Technologies‎ > ‎Oracle‎ > ‎

Standby database

Maximum Protection Mode.
Here data is synchronously transmitted to the standby database
from the primary database and transactions are not committed on
the primary unless the redo data is available on the standby database

If the standby database is un-available,processing stops on
the primary database.
 
For both the above modes the standby redo logs are used, since the date from
the log buffer of the primary database server is written to them. As for the Maximum
Performance mode the archived redo logs are transferred to the standby database

Maximum availability
Just like above Maximum protection, but except that ,when standby is down or
un-available the processin of the primary doesn't stop.
When standby is made up/available it is re-synchronized with primary.

Maximum Performance

Archive logs are shipped to the standy server Asynchronously.
Here Primary database does it works as normal doesn't depend
on any acknowledgement from the standby, so even if standby is down
processin of primary doesn't stop

 
 
Maximum Performance Modes
 
All the database technolgies have the same mechanism for creating a standby
server. First we have to copy the whole database from production to standby
then recording all the new transaction on the production in transaction log files
at regular intervals and copying those transaction on to the standby sever and
applying it to the standby database
 
As could be seen from above we have a production database, and a standby database.
Any transaction done on the production database are logged into archived log files
which are then copied to the standby server and are applied to the standby database,
so the standby database is the exact copy of the production database.
 
Now lets gets started in configuring the standby database, for the above given structure.
 
Steps
  1)  Configure the database in force logging mode  ALTER DATABASE FORCE LOGGING
  2)  Create a pfile from spfile  CREATE PFILE='C:\NEW_PFILE.ORA FROM SPFILE
  3)  Create a standby control file ALTER DATABASE CREATE STANDBY CONTROL FILE AS 'C:\NEW_CONTROL.CTL'
  4)  Shut the database and copy all the datafiles to the standby sever
  5)  Create standby database instance, with required modification in init.ora file
  6)  On the primary server configure the tnsname for the standby server.
  7)  Execute  ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=samsung'
        and    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE
  8)  Startup the standby database with nomount and with the above modified pfile
  9)  Execute ALTER DATABASE MOUNT STANBY DATABASE; on the standby
10)  Execute ALTER DATABASE RECOVER STANDBY DATABASE DISCONNECT
   FROM SESSION; on the standby
 
Thats it ! standby is configured
 
Now Lets take a detailed look at the above provided steps
 
          ............................................... will be updated soon
 
 
 
 
 
 
 
 
 
 
 
Here we look at the switchover scenario,
If we want to make the switchover i.e primary to standby and standby to primary
 
Current Production Server
 
1) Make sure the DR is in sync with prodution

SQL>SELECT MAX (SEQUENCE#) FROM V$LOG_HISTORY;
 
2) Check the Primary Production for switchover status
 
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
-----------------
TO STANDBY                --Indicates it is possible for switchover
.
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer;
 
Old Standby database

SQL>select switchover_status from v$database;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate
SQL>startup
SQL>alter system set log_archive_dest_state_2=enable;
 
Old Primary Database
 
SQL>alter database recover managed standby database disconnect from sesson;
 
 




==Standby server Arguments
 
 LGWR SYNC AFFIRM in Oracle Data Guard is used for zero data loss redo block generated at the primary reaches the standby across the network (SYNC)then the block has to be written on disk on the standby (AFFIRM)
 
 
 LGWR SYNC NOAFFIRM  Synchronous network transport, It has reached remote standby memory but not committed to disk yet
 
 
Comments