DataGuard - Step by Step

1. Intro

Oracle9i Data Guard provides an extensive set of data protection and disaster recovery features to help you to survive disasters, human errors, and corruptions that can incapacitate a database. Based on business requirements, these features can be advantageously combined with other Oracle9i high-availability and disaster recovery features for enhanced levels of high availability and disaster protection.

Oracle9i Data Guard is the management, monitoring, and automation software that works with a production database and one or more standby databases to protect your data against failures, errors, and corruptions that might otherwise destroy your database. It protects critical data by automating the creation, management, and monitoring of the databases and other components in a Data Guard configuration. It automates the otherwise manual process of maintaining a transactional consistent copy of an Oracle production database, called a standby database, that can be used if the production database is taken offline for routine maintenance or becomes damaged.

In a Data Guard configuration, a production database is referred to as a primary database. Using a backup copy of the primary database, you can create from one to nine physical and logical standby databases and incorporate them in a Data Guard configuration. Primary and standby databases can be running on a single node or in a Real Application Clusters environment. Every standby database is associated with only one primary database. However, a single primary database can support multiple physical standby databases, logical standby databases, or a mix of both in the same configuration:

Physical standby databases A physical standby database is physically identical to the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The physical standby database is updated by performing recovery. It can either be recovering data or open for read-only reporting.

Logical standby databases A logical standby database is logically identical to the primary database. The logical standby database is updated using SQL statements. The tables in a logical standby database can be used simultaneously for recovery and for other tasks such as reporting, summations, and queries.

2. Implementation:

2.1 Checking Force Logging:

This check need to be done on both databases i.e. (SID01 & SID02)

SQL> select force_logging from v$database;

If the result of above command shows “NO” then we need run following command.

2.2. Enable Force Logging:

SQL> alter database force logging;

2.3 Checking Archive log mode:

Both databases have to be in Archive Mode. If they are NOT we have to enable Archive mode.

Checking Archive Mode:

SQL> archive log list Database log mode Archive Mode Automatic archival Enable Archive destination /opt/oracle/archive/SID01 Oldest online log sequence 2386 Current log sequence 2388

If you get “Database log mode NO ARCHIVE MODE” it means you have to do following steps to convert it in ARCHIVE MODE.

If your database is already in ARCHIVE MODE skip the following step.

Enabling Archive Mode:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=’SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT =’’ SCOPE= SPFILE; SQL> ALTER SYSTEM SET LOG_ARCHIVE_START= TRUE SCOPE=SPFILE; SQL> ALTER SYSTEM SET DG_BROKER_START= TRUE SCOPE=BOTH; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> ARCHIVE LOG LIST;

2.4 Creating RMAN base backup:

SID01:

{ allocate channel canal01 type disk ; allocate channel canal02 type disk ; sql 'alter database backup controlfile to trace'; sql 'alter system archive log current'; backup filesperset 4 format “/backup/rman/dumps/SID01/SID01_db_%U.bkp" ( database ); backup format “/backup/rman/dumps/SID01/SID01_stdby_%U.cf" current controlfile for standby keep forever nologs; backup format "/backup/rman/dumps/SID01/SID01_arch_%U.bkp" archivelog all; release channel canal01; release channel canal02 }

SID02:

{ allocate channel canal01 type disk ; allocate channel canal02 type disk ; sql 'alter database backup controlfile to trace'; sql 'alter system archive log current'; backup filesperset 4 format “/backup/rman/dumps/SID02/SID02_db_%U.bkp" ( database ); backup format “/backup/rman/dumps/SID02/SID02_stdby_%U.cf" current controlfile for standby keep forever nologs; backup format "/backup/rman/dumps/SID02/SID02_arch_%U.bkp" archivelog all; release channel canal01; release channel canal02 }

2.5 Coping RMAN Backup to DR:

SID01:

SID01:/backup/rman/dumps/SID01 >scp * oracle@10.2.24.52:/backup/rman/dumps/SID01

SID02:

SID02:/backup/rman/dumps/SID02 >scp * oracle@10.2.24.52:/backup/rman/dumps/SID02

2.6 Coping Archive from primary to DR:

SID01:

SID01:/opt/oracle/archive/SID01 >scp *.arc oracle@10.2.24.52: /opt/oracle/archive/SID01

SID02:

SID02:/opt/oracle/archive/SID02 >scp *.arc oracle@10.2.24.52: /opt/oracle/archive/SID02

2.6 Checking tnsnames alias & listener:

You need to check tnsnames alias and listener status on both site from both direction.

SID01:

From Primay to DR:

$tnsping SID01dr

From DR to Primary:

$tnsping SID01prod

SID02:

From Primay to DR:

$tnsping SID02dr

From DR to Primary:

$tnsping SID02prod

2.7 Create pfile for standby on primary

You have to create pfile from spfile at primary database and later copy it to DR site.

SID01:

SQL> create pfile='/tmp/initSID01.ora.standby' from spfile;

SID02:

SQL> create pfile='/tmp/initSID02.ora.standby' from spfile;

2.8 Coping pfile from primary to standby – DR site

SID01:

scp /tmp/initSID01.ora.standby oracle@10.2.24.52:/opt/oracle/admin/SID01/pfile/

SID02:

scp /tmp/initSID02.ora.standby oracle@10.2.24.52:/opt/oracle/admin/SID02/pfile/

2.9 Edit standby parameter file

SID01:

$vi initSID01.ora.standby output: *.background_dump_dest='/opt/oracle/admin/SID01/bdump' *.background_core_dest='/opt/oracle/admin/SID01/bdump' *.background_user_dest='/opt/oracle/admin/SID01/bdump' *.compatible='9.2.0.0.0' *.control_files='/opt/oracle/oradata/SID01/control01.ctl', '/opt/oracle/oradata/SID01/control02.ctl','/opt/oracle/oradata/SID01dr/control03.ctl' *.core_dump_dest='/opt/oracle/admin/SID01/cdump' *.db_block_size=8192 *.db_cache_size=50331648 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='SID01' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='SID01' *.java_pool_size=0 *.large_pool_size=33554432 *.log_archive_dest_1='LOCATION=/opt/oracle/archive/SID01' *.log_archive_format='arch_SID01_%t_%s.arc' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50331648 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=268435456 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/SID01/udump' *.dg_broker_start='TRUE' *.standby_archive_dest='/backup/archive/SID01/standby' *.standby_file_management='AUTO' *.remote_archive_enable = 'TRUE'

SID02:

$vi initSID02.ora.standby output: *.background_dump_dest='/opt/oracle/admin/SID02/bdump' *.background_core_dest='/opt/oracle/admin/SID02/bdump' *.background_user_dest='/opt/oracle/admin/SID02/bdump' *.compatible='9.2.0.0.0' *.control_files='/opt/oracle/oradata/SID02/control01.ctl', '/opt/oracle/oradata/SID02/control02.ctl','/opt/oracle/oradata/SID02/control03.ctl' *.core_dump_dest='/opt/oracle/admin/SID02/cdump' *.db_block_size=8192 *.db_cache_size=50331648 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='SID01' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='SID01' *.java_pool_size=0 *.large_pool_size=33554432 *.log_archive_dest_1='LOCATION=/opt/oracle/archive/SID02' *.log_archive_format='arch_SID02_%t_%s.arc' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50331648 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=268435456 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/SID02/udump' *.dg_broker_start='TRUE' *.standby_archive_dest='/backup/archive/SID02/standby' *.standby_file_management='AUTO' *.remote_archive_enable = 'TRUE'

PS: In above text block the important parameters that needs to be changed are in biger fonts.

2.10 Copy initSID01.ora.standby to $ORACLE_HOME/dbs/:

SID01:

cp /opt/oracle/admin/pfile/initSID01.ora.standby $ORACLE_HOME/dbs/initSID02.ora

SID02:

cp /opt/oracle/admin/pfile/initSID02.ora.standby $ORACLE_HOME/dbs/initSID02.ora

2.11 Create executa_duplcate.sh:

SID01:

Vi executa_duplcate.sh Output: export ORACLE_SID=SID01 sqlplus /nolog @/opt/oracle/admin/SID01/standby/inicia_SID01_standby.sql rman @/opt/oracle/admin/SID01/standby/executa_duplicate.rman sqlplus /nolog @/opt/oracle/admin/SID01/standby/gera_stb_redolog.sql

SID02:

Vi executa_duplcate.sh Output: export ORACLE_SID=SID02 sqlplus /nolog @/opt/oracle/admin/SID02/standby/inicia_SID02_standby.sql rman @/opt/oracle/admin/SID02/standby/executa_duplicate.rman sqlplus /nolog @/opt/oracle/admin/SID02/standby/gera_stb_redolog.sql

PS: executa_duplcate.sh calls three sub scripts, that should be created in same directory as following:

2.11.1 create inicia_SID02_standby.sql

SID01:

Vi inicia_SID01_standby.sql Output: connect / as sysdba; shutdown abort; startup nomount pfile='/opt/oracle/admin/SID01/pfile/initSID01.ora.standby'; create spfile='/opt/oracle/admin/SID01/pfile/spfileSID01' from pfile='/opt/oracle/admin/SID01/pfile/initSID01.ora.standby'; shutdown immediate; startup nomount; exit;

SID02:

Vi inicia_SID02_standby.sql Output: connect / as sysdba; shutdown abort; startup nomount pfile='/opt/oracle/admin/SID02/pfile/initSID02.ora.standby'; create spfile='/opt/oracle/admin/SID02/pfile/spfileSID02' from pfile='/opt/oracle/admin/SID02/pfile/initSID02.ora.standby'; shutdown immediate; startup nomount; exit;

2.11.2 Create executa_duplicate.rman

SID01:

Vi executa_duplicate.rman Output: connect auxiliary / ; connect target sys/******@SID01prod; run { allocate auxiliary channel canal01 device type disk format '/backup/rman/dumps/SID01/SID01_db_%U.bkp'; allocate auxiliary channel canal02 device type disk format '/backup/rman/dumps/SID01/SID01_db_%U.bkp'; duplicate target database for standby nofilenamecheck dorecover; release channel canal01; release channel canal02; } exit;

SID02:

Vi executa_duplicate.rman Output: connect auxiliary / ; connect target sys/******@SID02prod; run { allocate auxiliary channel canal01 device type disk format '/backup/rman/dumps/SID02/SID02_db_%U.bkp'; allocate auxiliary channel canal02 device type disk format '/backup/rman/dumps/SID02/SID02_db_%U.bkp'; duplicate target database for standby nofilenamecheck dorecover; release channel canal01; release channel canal02; } exit;

2.11.3 create gera_stb_redolog.sql

SID01:

Vi gera_stb_redolog.sql Output: alter database add standby logfile group 4 ( '/opt/oracle/oradata/SID01/SID01_sblog04_a.dbf' ) size 100M; alter database add standby logfile group 5 ( '/opt/oracle/oradata/SID01/SID01_sblog05_a.dbf' ) size 100M; alter database add standby logfile group 6 ( '/opt/oracle/oradata/SID01/SID01_sblog06_a.dbf' ) size 100M; alter database add standby logfile group 7 ( '/opt/oracle/oradata/SID01/SID01_sblog07_a.dbf' ) size 100M;

SID02:

Vi gera_stb_redolog.sql Output: alter database add standby logfile group 4 ( '/opt/oracle/oradata/SID02/SID02_sblog04_a.dbf' ) size 100M; alter database add standby logfile group 5 ( '/opt/oracle/oradata/SID02/SID02_sblog05_a.dbf' ) size 100M; alter database add standby logfile group 6 ( '/opt/oracle/oradata/SID02/SID02_sblog06_a.dbf' ) size 100M; alter database add standby logfile group 7 ( '/opt/oracle/oradata/SID02/SID02_sblog07_a.dbf' ) size 100M;

2.12 run executa_duplcate.sh

SID01:

$./executa_duplcate.sh

SID02:

$./executa_duplcate.sh

2.13 Shutdown and Startup to mount:

SID01:

SQL> shutdown abort SQL> startup nomount SQL> alter database mount standby database;

SID02:

SQL> shutdown abort SQL> startup nomount SQL> alter database mount standby database;

2.14: Enable Archive Dest for standby at Primary:

At Primary:

SID01:

SQL> alter system set log_archive_dest_2='SERVICE=SID01dr arch' scope=both; SQL> alter system set log_archive_dest_state_2=enable scope=both;

SID02:

SQL> alter system set log_archive_dest_2='SERVICE=SID02dr arch' scope=both; SQL> alter system set log_archive_dest_state_2=enable scope=both;

2.15 Enable automatic recovery at standby:

SID01:

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

SID02:

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

2.16 Define FAL server & client at standby database

SID01:

SQL> alter system set fal_server='SID01prod' scope=both; SQL> alter system set fal_client='SID01dr' scope=both;

SID02:

SQL> alter system set fal_server='SID02prod' scope=both; SQL> alter system set fal_client='SID02dr' scope=both;

cheers