DataGuard Process Architecture

As shown in the figure below, the Data Guard operations involve many processes.



Let us see what each process is used for one by one,

In the Primary Site,

LGWR or ARCH: The redolog/archivelog from the primary database is shipped to the standby database site through LGWR/ARCH processes.

FAL (Fetch Archive Log): When there is a communication loss between primary and standby (for example, network failure), the log shipping is affected and there may be gaps. When the communication is established again the FAL processes enable the automatic gap resolution and resynchronization process.

In the Standby Site,

RFS (Remote File Server): The RFS process receives the redo records from the Primary site.

MRP (Managed Recovery Process): MRP applies the redo records to the Physical Standby databases.

LSP (Logical Standby Process): The LSP process translates the redo records in to SQL and fires the SQL to the logical standby databases.

If Data Guard Broker is enabled then DMON Process is enabled on both the primary and standby sites.

Thanks

Difference between Physical and Logical Standby

The Data Guard Configuration consists of one Production database and upto nine Standby databases. The production database can be a single instance or RAC database similarly Standby database can be single instance or RAC database. It is also possible to have single instance standby database for a RAC production database.

The standby database is a transactionally consistent copy of the production database. It is created initially from the backup of production database. Once created, the data guard automatically synchronizes the standby.

There are two types of standby database, they are

1. Physical Standby
2. Logical Standby

Physical standby uses the recovery technique to sychronize the standby database where as logical standby uses the sql apply method to synchronize both the database.
The following table provides you with the difference between physical and logical standby databases,

S.No	Physical Standby	Logical Standby
1	Identical to the Primary database including the physical organization inthe disk	Same logical information but physical organization and the structure ofdata are different.
2	DG uses Redo Apply technology, which applies redo data using standardrecovery techniques.	DG uses SQL Apply, which first transforms the redo data into SQLstatements and then executes the statement.
3	Can be used for Backups	Can be opened for reporting.
4	All data types are supported	Not all data types are supported for eg. LONG, NCLOB, LONG RAW, BFILE, XML types are not supported.
5	Can open in ‘Read only’ but cannot apply logs. From 11g open in‘read write’.	Can open in normal mode and simultaneously apply the logs.
6	No additional objects can be created.	Additional indexes, materialized views can be created

Usually organizations use Logical Standby databases mainly for reporting purposes and not for failover/switchover operations. For failover and switchover they use physical standby database. The reason is maintaining logical standby is almost a full time job, need extensive tuning of log apply services, and over hundreds of patches, the logical is usually 3 to 5 hours behind the live database, thus making it impossible for failover/switchover.

Thanks

DataGuard Features History

The concept of Standby database was existing since the Oracle version 7.3. Since then it had undergone a lot of changes and improvements. Let us summarise these changes/improvements from version 7.3 through 11g.

Oracle 7.3: Introduces Standby database. In this release the process of transferring archived redo logs was manual.

Oracle 8i: Supported the automatic shipping and application of archived redolog files from the primary site to standby site. It also supported opening of standby database for read only, however the recovery process must be stopped inorder to open it in read only mode.

Oracle 9iR1: Supported automatic gap resolution. Introduced various protection modes. Also introduced DataGuard Broker, an interface to manage the dataguard environment.

Oracle 9iR2: Introduced the concept of Logical Standby Database.

Oracle 10g: Introduced Real Time Apply, Fast Start Failover, Provided RAC support.

Oracle 11g: Introduces Active Dataguard which enables you to open the physical database in read only mode while the redo apply is active. Introduces Snapshot standby and redo compression.

Sample init.ora file for implementing Physical Standby

The following is a sample parameter file used for creating Physical Standby Database.

*.audit_file_dest='/home/oracle/admin/LCDR/adump'

*.background_dump_dest='/home/oracle/admin/LCDR/bdump'

*.compatible='10.2.0.3.0'

*.control_files='+ASMFLASH/LCDR/controlfile/current.261.740682331','+ASMUNDO/LCDR/controlfile/current.261.
740682331','+ASMIND/LCDR/controlfile/current.261.740682331'

*.core_dump_dest='/home/oracle/admin/LCDR/cdump'

*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_cache_size=5368709120
*.db_create_file_dest='+ASMDB'
*.db_create_online_log_dest_1='+ASMFLASH'
*.db_create_online_log_dest_2='+ASMUNDO'
*.db_create_online_log_dest_3='+ASMIND'
*.db_domain=''
*.db_file_multiblock_read_count=32

*.db_file_name_convert='+ASMDB/PRDB','+ASMDB/LCDR','+ASMIND/PRDB','+ASMIND/LCDR','+ASMFLASH/PRDB','+ASMFLASH/LCDR','+ASMUNDO/PRDB','+ASMUNDO/LCDR'

*.db_flashback_retention_target=0
*.db_keep_cache_size=2147483648

*.db_name='PRDB'

*.db_recovery_file_dest='+ASMFLASH'
*.db_recovery_file_dest_size=96636764160

*.db_unique_name='LCDR'

*.db_writer_processes=8

*.dispatchers='(PROTOCOL=TCP)(SERVICE=LCDRXDB)'

*.dml_locks=24220
*.event='44951 trace name context forever,level 1024'
*.filesystemio_options='SETALL'
*.job_queue_processes=10


*.fal_client='LCDR'
*.fal_server='PRDB'

*.instance_name='LCDR'

*.log_archive_config='DG_CONFIG=(LCDR,PRDB)'
*.log_archive_dest_1='LOCATION=/backup/prodarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LCDR'

*.log_archive_dest_state_1='ENABLE'
*.log_buffer=67108864

*.log_file_name_convert='+ASMDB/PRDB','+ASMDB/LCDR','+ASMIND/PRDB','+ASMIND/LCDR','+ASMFLASH/PRDB','+ASMFLASH/LCDR','+ASMUNDO/PRDB','+ASMUNDO/LCDR'

*.open_cursors=5000
*.pga_aggregate_target=4294967296
*.processes=500
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='LCDR'

*.session_cached_cursors=5000
*.sessions=500
*.sga_max_size=21474836480
*.sga_target=21474836480
*.shared_pool_size=1073741824
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

Thanks

Duplicate Script for implementing Physical Standby

After you create backup of primary database using the BACKUP SCRIPT and transfered the files to the standby server run the following script to create the physical standby.

export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/OraHome1
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/home/oracle/OraHome1/bin:.
export ORACLE_SID=LCDR

v_bdest=/backup/RMANBKUP/spool
####################### DUPLICATE DATABASE ##########################

rman target sys/password@PRDB auxiliary / << EOF
spool log to $v_bdest/StandbyDB.log append;
run {
allocate auxiliary channel a1 type disk format '/backup/RMANBKUP/%U';
allocate auxiliary channel a2 type disk format '/backup/RMANBKUP/%U';
duplicate target database for standby;
}
exit
EOF


Thanks

Backup Script for implementing Physical Standby Database.

The following script is used for creating RMAN backup required for implementing Physical Standby database.

export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/OraHome1
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/home/oracle/OraHome1/bin:.
export ORACLE_SID=PRDB

v_bdest=/backup/RMANBKUP
lev0="'$v_bdest/%U'"
ctlbak="'$v_bdest/%U'"

rman target sys/password@PRDB nocatalog << EOF
spool log to $v_bdest/spool/BACKUP.log append;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset incremental level 0 format $lev0 database plus archivelog;
backup current controlfile for standby format $ctlbak;
backup spfile;
release channel c1;
release channel c2;
}
exit
EOF

After the backup is finished, copy the backup files to the standby server and run the RMAN duplicate command to create the standby database.

Steps for implementing Physical Standby Database

In this post Iam going to give step by step procedure for creating the Physical Standby Database.

Production Database: PRDB
Standby Database: LCDR
Production server:x.x.x.41
Standby Server: x.x.x.47
Database Version:10.2.0.4.0

Step1: Enable Force Logging in Primary Database

SQL> Alter Database Force Logging:

Step2: Backup the primary database and copy the backup to the Standby server. The directory structure of the backup must be same as that of the primary server.

For backup script click here

Step3: Update the oratab file in the standby server.

$ cd /etc
$ vi oratab

+ASM:/home/oracle/OraHome1:N
LCDR:/home/oracle/OraHome1:N

$

Step4: Create a password file for LCDR.
Copy the password file from production and rename it.

Step5: Create necessary directories (bdump,cdump etc). Since this is a physical standby the directory structure of the standby database must be identical to that of primary database. If you are using ASM the diskgroup name should be identical to primary ASM.

Step 6: Update tnsnames.ora and listener.ora

Step 7: Test the connectivity from both server.

$ tnsping PRDB
$ tnsping LCDR

Step 8: Create init.ora file for standby database.
Copy the init.ora file from the target database to aux server.
Modify/Add the following parameters,

Audit_file_dest
Background_dump_dest
Core_dump_dest
User_dump_dest
Control_files
Db_name
Db_unique_name
dispatchers
Fal_client
Fal_server
Instance_name
Log_archive_config
Log_archive_dest_1
Service_names
Db_file_name_convert
Log_file_name_convert

Modify/Add the following parameters in the primary database (PRDB),
SQL> alter system set log_archive_dest_2='SERVICE=LCDR valid_for=(online_logfiles, primary_role) db_unique_name=LCDR' scope=both;

SQL> alter system set log_archive_config='dg_config=(LCDR,PRDB)' scope=both;

SQL> alter system set standby_file_management='AUTO' scope=both;

Click here for a sample init.ora file

Step 9: Startup standby database in nomount stage.

From standby server
$ export ORACLE_SID=LCDR
$ sqlplus / as sysdba
connected to an idle instance
SQL> create spfile from pfile;
File created
SQL> Startup nomount;

Step 10: Run the RMAN duplicate command to create standby database.
For duplicate script click here
Step 11: Create the standby redolog (SRL) groups in standby database.

Number of SRL is one more than the no. of online redolog groups in primary database.
For eg. if the no.of online redolog groups in primary database is 3, you have to create four SRL.

SQL> Alter database add standby logfile group 4 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 5 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 6 '+ASMFLASH' size 512M;
SQL> Alter database add standby logfile group 7 '+ASMFLASH' size 512M;

Step 12: Start the Managed Recovery Process on the standby.

SQL> Alter database recover managed standby database disconnect from session;

Step 13: Verify log shipment.

On the primary

SQL> Alter system set log_archive_dest_state_2= 'ENABLE' scope=both;
SQL> Alter system Switch logfile;

Check whether the logfiles are shipped to the standby server.

Thanks