Oracle Data Guard

The software which Oracle Corporation markets as Oracle Data Guard forms an extension to the Oracle RDBMS. It aids in establishing and maintaining secondary "standby databases" as alternative/supplementary repositories to production "primary databases".

Oracle Corporation provides both GUI and command-line tools for managing Data Guard configurations.

Data Guard supports both physical standby and logical standby sites. Oracle Corporation makes Data Guard available only as a bundled feature included within its "Enterprise Edition" of the Oracle RDBMS.^[1]

With appropriately set-up Data Guard operations, DBAs can facilitate failovers or switchovers to alternative hosts in the same or alternative locations.

Configurations

A physical standby database attempts to replicate the exact structure and layout of its primary database across the Oracle Net network layer. It can function either in archiving ("recovery") mode or in read-only mode, but not in both modes at the same time. It can become a standalone database when required, but needs rebuilding before reverting from standalone to standby functionality.

Logical standby databases re-apply SQL transactions generated on their primary node; thus physical structures and organization. As of Oracle release 11g Data Guard may operate across different "CPU architectures, operating systems ... operating system binaries (32-bit/64-bit), and Oracle database binaries (32-bit/64-bit)."^[2] Users (under certain conditions) can write to as well as read from logical standby databases; and such databases can switch in function to and fro between primary and standby roles.

Operation

A primary site which has an archived redo log to transfer invokes the RFS (remote file server) background process within the Oracle instance running on a standby host. The RFS process then performs the task of receiving archived redo logs sent from the primary database.

Alternatively, a supplementary mechanism may transfer the archived redo logs. On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).^[3]

Once the archived redo logs have arrived, other processes (such as an ARCH (Archiver process), an MRP (Managed Recovery Process), and/or an LSP (Logical Standby Process)) may set about applying the log contents to the standby database..

Step By Step process of Configuring Oracle 10gR2 (10.2.0.1) Dataguard on 32 bit RedHat Enterprise Linux 3 (RHEL3)  / CentOS 3

By Bhavin Hingu bhavin@oracledba.org

                                               <<HOME>>

This document explains the step by step process of Configuring 10g R2 Single Instance Dataguard for Single Instance Primary on RedHat Enterprise Linux 32 bit (RHEL3) / CentOS 3.6.

Click HERE for Step By Step Process of Configuring RAC Standby Dataguard for RAC Primary ON Redhat Linux.

Task List:

       10g R2 Dataguard Technical Architecture

Primary DB init parameter Standby DB init parameter Enable Archiving On Primary DB tnsnames.ora/listener.ora configuration Creating Standby Redo logs (SLRs) Backup the Primary DB. Creating the standby controlfile Startig and verifying Standby DB Testing Realtime Apply

Technical Architecture of DataGuard

Primary Database Name: primary Service Name: primary Primary Node:

  SID: primary
  Network name (hostname): node1-prv
  ORACLE_BASE: /u01/app/oracle

Standby Database Name: stndby Service Name: stndby Standby Node:

  SID: stndby
  Network name (hostname): node2-prv
  ORACLE_BASE: /u01/app/oracle

Primary DB init parameter

primary.__db_cache_size=67108864 primary.__java_pool_size=4194304 primary.__large_pool_size=4194304 primary.__shared_pool_size=88080384 primary.__streams_pool_size=0

.archive_lag_target=0
.audit_file_dest='/u01/app/oracle/admin/primary/adump'
.background_dump_dest='/u01/app/oracle/admin/primary/bdump'
.compatible='10.2.0.1.0'
.control_files='/u01/app/oracle/oradata/PRIMARY/controlfile/o1_mf_26lg83r9_.ctl','/u01/app/oracle/flash_recovery_area/PRIMARY/controlfile/o1_mf_26lg844c_.ctl'
.core_dump_dest='/u01/app/oracle/admin/primary/cdump'
.db_block_size=8192
.db_create_file_dest='/u01/app/oracle/oradata'
.db_domain=
.db_file_multiblock_read_count=16
.db_name='primary'
.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
.db_recovery_file_dest_size=2147483648
.db_unique_name='primary'
.dg_broker_start=TRUE
.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
.fal_client='primary'
.fal_server='stndby'
.job_queue_processes=10
.log_archive_config='DG_CONFIG=(primary,stndby)'
.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/PRIMARY/arch

 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
 DB_UNIQUE_NAME=primary'

.log_archive_dest_2='SERVICE=stndby LGWR ASYNC

 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) 
 DB_UNIQUE_NAME=stndby'

.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_archive_format='%t_%s_%r.dbf'

primary.log_archive_format='%t_%s_%r.dbf'

.log_archive_max_processes=2
.log_archive_min_succeed_dest=1

primary.log_archive_trace=0

.open_cursors=300
.pga_aggregate_target=16777216
.processes=150
.remote_login_passwordfile='EXCLUSIVE'
.sga_target=167772160

primary.standby_archive_dest='/u01/app/oracle/oradata/PRIMARY/arch '

.standby_file_management='AUTO'
.undo_management='AUTO'
.undo_tablespace='UNDOTBS1'
.user_dump_dest='/u01/app/oracle/admin/primary/udump'
.local_listener=prim

 Standby DB init parameter

stndby.__db_cache_size=75497472 stndby.__java_pool_size=4194304 stndby.__large_pool_size=4194304 stndby.__shared_pool_size=79691776 stndby.__streams_pool_size=0

.archive_lag_target=0
.audit_file_dest='/u01/app/oracle/admin/stndby/adump'
.background_dump_dest='/u01/app/oracle/admin/stndby/bdump'
.compatible='10.2.0.1.0'
.control_files='/u01/app/oracle/oradata/STNDBY/controlfile/stndby01.ctl','/u01/app/oracle/flash_recovery_area/STNDBY/controlfile/stndby02.ctl'
.core_dump_dest='/u01/app/oracle/admin/stndby/cdump'
.db_block_size=8192
.db_create_file_dest='/u01/app/oracle/oradata'
.db_domain=
.db_file_multiblock_read_count=16
.db_name='primary'
.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
.db_recovery_file_dest_size=2147483648
.db_unique_name='stndby'
.dg_broker_start=TRUE
.dispatchers='(PROTOCOL=TCP) (SERVICE=stndbyXDB)'
.fal_client='stndby'
.fal_server='primary'
.job_queue_processes=10
.log_archive_config='DG_CONFIG=(stndby,primary)'
.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/STNDBY/arch

 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
 DB_UNIQUE_NAME=stndby'

.log_archive_dest_2='SERVICE=primary LGWR ASYNC

 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) 
 DB_UNIQUE_NAME=primary'

.log_archive_dest_state_1='ENABLE'
.log_archive_dest_state_2='ENABLE'
.log_archive_format='%t_%s_%r.dbf'
.log_archive_max_processes=2
.log_archive_trace=0
.db_file_name_convert= 'PRIMARY', 'STNDBY'
.log_file_name_convert='PRIMARY', 'STNDBY'
.open_cursors=300
.pga_aggregate_target=16777216
.processes=150
.remote_login_passwordfile='EXCLUSIVE'
.sga_target=167772160
.standby_archive_dest='/u01/app/oracle/oradata/STNDBY/arch'
.standby_file_management='AUTO'
.undo_management='AUTO'
.undo_tablespace='UNDOTBS1'
.user_dump_dest='/u01/app/oracle/admin/stndby/udump'
.local_listener=stnd

Enabling Archiving on primary DB:

Ensure that the primary is in archive log mode

SQL>shutdown immediate SQL>startup mount; SQL>alter database archivelog; SQL>alter database open;

tnsnames/listener.ora configuration:

tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db10g/network/admin/tnsnames.ora
Generated by Oracle configuration tools.

STNDBY =

 (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) 
   ) 
   (CONNECT_DATA = 
     (SERVICE_NAME = STNDBY) 
   ) 
 )

PRIM =

 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)))

PRIMARY =

 (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = node1-prv)(PORT = 10521)) 
   ) 
   (CONNECT_DATA = 
     (SERVICE_NAME = PRIMARY) 
   ) 
 )

EXTPROC_CONNECTION_DATA =

 (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 
   ) 
   (CONNECT_DATA = 
     (SID = PLSExtProc) 
     (PRESENTATION = RO) 
   ) 
 )

Copy the same file to the standby server and adjust it based on the listener.ora file. Also update the listener.ora file so that it listen the SIDs mentioned in the tnsnames.ora file.

listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db10g/network/admin/listener.ora
Generated by Oracle configuration tools.

SID_LIST_LISTENER_STBY =

 (SID_LIST = 
   (SID_DESC = 
     (SID_NAME = PLSExtProc) 
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) 
     (PROGRAM = extproc) 
   ) 
   (SID_DESC = 
     (SID_NAME = stndby) 
     (GLOBAL_DBNAME = stndby_DGMGRL) 
     (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db10g) 
   ) 
 )

LISTENER_STBY =

 (DESCRIPTION_LIST = 
   (DESCRIPTION = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = node2-prv)(PORT = 10521)) 
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 
   ) 
 )

Standby Redo Logs (SLRs) Creation:

In case of OMF:

Get the max group# of online redo logs on PRIMARY database

SELECT max (group#) from v$logfile;

Create the standby redo logs on the primary database with the same size of that of online redo logs. If the above query retuns the value of 3 and each logfile is 50M in size (from the below query) then, create at least 4 standby redo logs of the size of 50M per thread.

SELECT byte from v$log;

Create the SRL's :

   ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M 
   / 
   ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M 
   / 
   ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M 
   / 
   ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 50M 
   /

Backup the primary DB:

Take a cold/Hot/RMAN backup of the primary database. I used the cold backup in this case.

SQL>SHUTDOWN IMMEDIATE

Backup the datafiles, online redologs and the standby logs if created and scp to the standby server in the corrosponding directory. I used the same directory structure as that with primary. The only differece was the name of the direcory. For e.g,

On primary database, I have a path of /u01/app/oracle/oradata/PRIMARY/datafile whereas On standby server, I have a path of /u01/app/oracle/oradata/STNDBY/datafile,

This is the reason, I have used the db_file_name_convert parameter in the primary init.ora file with the value of db_file_name_convert=’PRIMARY’,’STNDBY’

and in the standby init.ora file with the value of

db_file_name_convert=’STNDBY’, ‘PRIMARY’

Create the Standby Controlfile:

On Primary Database:

 SQL>STARTUP MOUNT;
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stndby01.ctl';
SQL>ALTER DATABASE OPEN;

scp the stndby01.ctl file to the standby site. I have multiplexed it in the initstndby.ora file. So I SCPed the same file to both the locations mentioned in the initstndby.ora file. Also, SCPed the $ORACLE_HOME/dbs/orapwprimary file of the primary to the same ___location on the standby with the name of orapwstndby.

Starting and Verifying the standby DB:

SQL>create spfile from pfile; SQL>STARTUP MOUNT; SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

         Verify the Standby :

- Identify the existing files on the standby

 SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
     FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

  Switch a log on the primary database:

 SQL>ALTER SYSTEM SWITCH LOGFILE;

- Re-Run the same SQL to make sure that the logs are received and applied to

 the standby server.

Verify that these logs were applied :

 SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Testing Realtime Apply:

On Primary Database : create a table 'test' and insert a record.

INSERT INTO test VALUES (sysdate);
COMMIT;

Do not make a log switch because I set up the LGWR ASYNC option so that The redo should be transferred and applied to the standby server in real time.

On the STANDBY DB server:

SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

ALTER DATABASER RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE OPEN READ ONLY;

SELECT * FROM test;

You should see the commited transaction.

Place the standby back in managed recover mode

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This will take the standby directly from read only mode and place it in managed recovery mode.

=============================================================================

Click HERE for Step By Step Process of Configuring RAC Standby Dataguard for RAC Primary ON Redhat Linux.

                                               <<HOME>>

Advantages

Data Guard provides high availability for a database system. It can also reduce the human intervention required to switch between databases at disaster-recovery ("failover") or upgrade/maintenance ("switchover") time.

References

^ http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/standby.htm#insertedID3 Data Guard Concepts and Administration, 11g Release 1 (11.1); Section 2.3.2: "Oracle Software Requirements" Retrieved 2007-10-01
^ http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/standby.htm#insertedID3 Data Guard Concepts and Administration, 11g Release 1 (11.1); Section 2.3.1 "Hardware and Operating System Requirements" Retrieved 2007-10-01
^ Oracle-Base site summary of Data Guard, retrieved 2007-09-05; Oracle Corp version 11.1 documentation links, retrieved 2007-09-05.

External links

Oracle Data Guard Introduction

[1] ttp://download.oracle.com/docs/cd/B28359_01/server.111/b28294/standby.htm#insertedID3 Data Guard Concepts and Administration, 11g Release 1 (11.1); Section 2.3.2: "Oracle Software Requirements" Retrieved 2007-10-01

[2] ttp://download.oracle.com/docs/cd/B28359_01/server.111/b28294/standby.htm#insertedID3 Data Guard Concepts and Administration, 11g Release 1 (11.1); Section 2.3.1 "Hardware and Operating System Requirements" Retrieved 2007-10-01

[3] Oracle-Base site summary of Data Guard, retrieved 2007-09-05; Oracle Corp version 11.1 documentation links, retrieved 2007-09-05.

[1]

[2]

[3]