10g Data Guard Configuration on RHEL/OEL- 5

In this blog we'll see how to configure 10g Data Guard. In this, we'll do the configuration in two Parts.

1) Data Guard Configuration

2) Configure Data Guard processes.

Part-1: Data Guard Configuration

Configuration on primary site:-

Step 1) Configure the database in archive log mode and enable the force logging.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE OPEN;

Step 2) Configure standby redo log files to enable fast failover and real time apply. (Assume we have three redo log group)

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/dg01/sredo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/dg01/sredo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/dg01/sredo06.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/dg01/sredo07.log’ size 50m;

Note: - Number of standby redo log group must be more than number of redo log group and size of the standby logfile must be same as the size of redo log file.

Steps 3) CREATE PFILE FROM SPFILE.

Now configure following parameters for primary database:-

LOG_ARCGIVE_CONFIG='DG_CONFIG=(dg01,dg02)'

DB_UNIQUE_NAME=dg01

SERVICE_NAMES=dg01

DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_ARCHIVE_DEST_1=’location=/u01/app/oracle/oradata/dg01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG01’

LOG_ARCHIVE_DEST_2=’service=dg02 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG02’

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg01

FAL_SERVER=dg02

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 4) Take the entire database backup on pre configured staging directory.

mkdir /home/oracle/rman (Staging directory for rman backup)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ DATABASE;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ CURRENT CONTROLFILE FOR STANDBY;

RMAN> SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”;

Step 5) Copy the following items from machine1 to machine2.

PASSWORD FILE

PARAMETER FILE

RMAN BACKUP TAKEN ABOVE

Note: - RMAN backup must be copy at the same directory on machine2 i.e. /home/oracle/rman.

Step 6) Configure net service for dg01 (primary database) as well as for dg02 (standby database).

DG02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg02)

    )

  )

DG01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dg01)

    )

  )

------------------------ ON STANDBY DATABSE --------------------------

Step-1: Edit Pfile from Standby Database:

dg02.__db_cache_size=440401920

dg02.__java_pool_size=4194304

dg02.__large_pool_size=4194304

dg02.__shared_pool_size=163577856

dg02.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/dg02/adump'

*.background_dump_dest='/u01/app/oracle/admin/dg02/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/dg02/control01.ctl'

*.core_dump_dest='/u01/app/oracle/admin/dg02/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='dg01'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

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

*.job_queue_processes=10

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

*.open_cursors=300

*.pga_aggregate_target=204472320

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=613416960

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/dg02/udump'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg01,dg02)'

DB_UNIQUE_NAME=dg02

SERVICE_NAMES=dg02

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dg01','/u01/app/oracle/oradata/dg02'

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dg01','/u01/app/oracle/oradata/dg02'

LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/dg02/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG02'

LOG_ARCHIVE_DEST_2='service=dg01 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG01'

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg02

FAL_SERVER=dg01

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 2: Create the required directory for standby database

# Mkdir -p  /u01/app/oracle/admin/dg02/adump

# Mkdir -p  /u01/app/oracle/admin/dg02/bdump

# Mkdir -p  /u01/app/oracle/admin/dg02/cdump

# Mkdir -p  /u01/app/oracle/admin/dg02/udump

# Mkdir -p  /u01/app/oracle/admin/dg02/create

# Mkdir -p  /u01/app/oracle/admin/dg02/pfile

# Mkdir -p  /u01/app/oracle/admin/dg02/scripts

Step-3: Configure net service for dg01 (primary) as well as dg02 (standby) database.

DG02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg02)

    )

  )

DG01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.oracle.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dg01)

    )

  )

Step-4: Start dg02 at nomount stage

$ export ORACLE_SID=dg02

SQL> CREATE SPFILE FROM PFILE;

SQL> STARTUP NOMOUNT

SQL> EXIT

Step-5: Now using RMAN duplicate command create standby database

RMAN TARGET SYS/ORACLE@DG01

RMAN> CONNECT AUXILIARY SYS/ORACLE@DG02

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;

Step-6: Add standby log on standby database

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/dg01/sredo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/dg01/sredo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/dg01/sredo06.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/dg01/sredo07.log’ size 50m;

Step-7: Start Flashback on standby database to avoid any problem

SQL> alter database flashback on;

So, in this way we can configure 10g Data Guard.

Part-2: Configure Data Guard Process

NOTE:

Now, After configuration you'll have to start few process so that your Data Guard could work properly. Here are the following steps that you'll gave to follow:

Step-1: Start MRP Process on Standby Node

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

SQL>  select PROGRAM from v$process where PROGRAM like '%MRP%';

PROGRAM

------------------------------------------------

oracle@node2.oracle.com (MRP0)

Step-2: Start Real Time Apply (From standby Redo Log)

SQL> select dest_name,recovery_mode from v$archive_dest_status;

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect from session; (Before this command fire alter system switch logfile from DG01)

SQL> select dest_name,recovery_mode from v$archive_dest_status;

SQL> select sequence#, applied from v$archived_log;

This is the first part of Data Guard in coming parts i'll give you more explanations and demos for Data Guard.
Thanks,