Active dataguard setup / Disaster Recovery(DR) in Oracle 11g

Article on: Oracle Active Data Guard:

Active Data Guard provides the management, monitoring, and automation software to create and maintain one or more synchronized replicas (standby databases) of a production database (primary database). An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster.

Scope: Oracle 11.2.x.x and in any platform, Best solution for DR plan (Disaster Recovery)

Assumptions:

•You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 5.6 and Oracle Database 11.2.0.2.

•The primary server has a running instance.

•The standby server has a oracle 11g software only installation.

Note: SAme OS and Oracle software version.

Prerequisite for Active DataGuard

1) Same version of OS

2) Oracle Software Version : Oracle11gR2

Step by step Active Dataguad configuration. Follow the below two servers.

Info:    

Primary database Name: DEVDB

IP : 10.40.32.49

Proposed Standby Database name: DEVDBS

IP:  10.33.33.184

Configure the tnsnames to support the database on both nodes

a) Listener configuration on primary

adding of standby tnsentry in primary side:

Tnsnames.ora

DEVDBS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.33.184)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDBS)

    )

  )

b) tns entry on STANDBY  ( add folloing tns entry after Installing Oracle11g Software)

Tnsnames.ora

DEVDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.40.32.49)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB)

    )

  )

DEVDBS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.33.184)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDBS)

    )

  )

Steps:

1) Login into Standby Database . ( Now we don't have any database)

Note: Install only oracle software. plan for datafile, controlfile. redologfile, archivelog, fra and other admin file locations and create also.

2) Enable Archiving in PRIMARY1 database (if, required)

eg.,

telnet 10.33.33.184

User name: oracle

pwd:

alter system set DB_RECOVERY_FILE_DEST_SIZE=5G;

alter system set db_recovery_file_dest='/oracle1/DEVDB/FRA/';

OR

*.db_recovery_file_dest='/oracle1/DEVDB/FRA'

*.db_recovery_file_dest_size=5368709120

-- add in pfile

*.log_archive_dest_1='Location=/oracle1/DEVDB/FRA/archivelogs'

sql> shutdown immediate;

sql> alter database mount;

sql> alter database archivelog;

sql> alter database open;

3) Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

eg.,

telnet 10.33.33.184

User name: oracle

pwd:

$ sqlplus / as sysdba

SQL> SELECT force_logging FROM v$database;

SQL> alter database force logging;

SQL> SELECT force_logging FROM v$database;

FOR

---

YES

4) Create pfile from spfile in PRIMARY

SQL> create pfile from spfile;

Note: Copy $ORACLE_HOME/dbs location/address from STANDBY database ( 10.40.32.49)

5) Create Password file in PRIMARY

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

Note: use sys password in your password file.

$ orapwd force=y file=orapwDEVDB password=sys

Note: Move the same Password file to standby side and rename it with Sandby SID.

6) Configure a Standby Redo Log

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.

Note: if any standby log file found, then drop using following command.

To see stand by log files:

sql> select GROUP#,SEQUENCE# from v$standby_log;

To drop stand by log files:

sql > alter database drop standby logfile group 4;

Note: if, any stand by redo log is dropped, then use following command to add stand by redo log.

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle1/oradata/UATHYDIP/redo04.log') SIZE 50M reuse;

To see the path of redo log file location:

SQL> select GROUP#,member from v$logfile;

To add stand by log file:

Perform the following steps to configure the standby redo log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oracle1/DEVDB/oradata/DEVDB/redo04.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle1/DEVDB/oradata/DEVDB/redo05.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oracle1/DEVDB/oradata/DEVDB/redo05.log') SIZE 50M;

7) Copy pfile file from Primary database to Standby database.

      $ cd $ORACLE_HOME/dbs

dbs]$ ls -lrt

........

-rw-r--r--  1 oracle11g oinstall    1865 Jun 27 11:18 initDEVDB.ora

-rw-r-----  1 oracle11g oinstall    1536 Jun 27 11:34 orapwDEVDB

dbs]$ scp initUATHYDIP.ora oracle11g@172.18.1.18:/Oracle/app/oracle11g/product/11.2.0/dbhome_1/dbs

 8) Add following into pfile and make sure the following parameters are exists or not.

Set Primary Database Initialization Parameters

*.audit_file_dest='/oracle/app/oracle11g/admin/DEVDB/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.DB_UNIQUE_NAME='DEVDB'

*.db_name='DEVDB'

*.control_files='/oracle1/DEVDB/oradata/DEVDB/control01.ctl','/oracle1/DEVDB/oradata/DEVDB/control02.ctl'

*.FAL_CLIENT='DEVDB'

*.FAL_SERVER='DEVDBS'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DEVDB,DEVDBS)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle1/DEVDB/FRA/archivelogs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DEVDB'

*.LOG_ARCHIVE_DEST_2='SERVICE=DEVDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=DEVDBS'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.STANDBY_FILE_MANAGEMENT='AUTO'

*.DB_FILE_NAME_CONVERT='/home/oracle/STANDBY/DEVDBS/oradata/','/oracle1/DEVDB/oradata/DEVDB/'

*.LOG_FILE_NAME_CONVERT='/home/oracle/STANDBY/DEVDBS/oradata/','/oracle1/DEVDB/oradata/DEVDB/'

*.db_block_size=8192

*.db_domain=''

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/oracle1/DEVDB/FRA/'

*.diagnostic_dest='/oracle/app/oracle11g'

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

#*.log_archive_dest_1='Location=/oracle1/DEVDB/FRA/archivelogs'

*.nls_date_format='DD-MON-YYYY HH24:MI:SS'

*.open_cursors=300

*.pga_aggregate_target=134217728

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=536870912

*.undo_tablespace='UNDOTBS1'

8.1) Do the following steps after changing parameters:

sql> shut immediate;

sql> create spfile from pfile='/oracle/app/oracle11g/product/11.2.0/dbhome_1/dbs/initDEVDB.ora';

sql> startup mount;

sql> alter database open;

Note: Cross check all parameters what are added in pfile.

9) Create following directory structure in STANDBY  ( DEVDBS) database side.

a)

Create paths for Control file, Redolog and Datafiles (CRD):

/home/oracle/STANDBY/DEVDBS/adump

/home/oracle/STANDBY/DEVDBS/oradata

/home/oracle/STANDBY/DEVDBS/FRA

b) The pfile of STANDBY( UATHYDIPDR) database:

*.db_name='DEVDB'

*.db_unique_name='DEVDBS'

*.fal_client='DEVDBS'

*.fal_server='DEVDB'

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/home/oracle/STANDBY/DEVDBS/FRA'

.log_archive_dest_1='Location=/home/oracle/STANDBY/DEVDBS/FRA/archivelogs'

*.diagnostic_dest='/oracle/diag'

*.max_shared_servers=15

*.nls_date_format='DD-MON-RRRR HH24:MI:SS'

*.open_cursors=300

*.pga_aggregate_target=134217728

*.processes=400

*.remote_login_passwordfile='EXCLUSIVE'

*.sec_case_sensitive_logon=FALSE

*.sessions=444

*.sga_max_size=536870912

*.sga_target=536870912

*.shared_servers=8

*.undo_tablespace='UNDOTBS1'

DB_FILE_NAME_CONVERT='/oracle1/DEVDB/oradata/DEVDB/','/home/oracle/STANDBY/DEVDBS/oradata/'

LOG_FILE_NAME_CONVERT='/oracle1/DEVDB/oradata/DEVDB/','/home/oracle/STANDBY/DEVDBS/oradata/'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_config='DG_CONFIG=(DEVDB,DEVDBS)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/STANDBY/DEVDBS/FRA/archivelogs  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=DEVDBS'

*.LOG_ARCHIVE_DEST_2='SERVICE=DEVDB  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=DEVDB'

*.standby_file_management='AUTO'

*.log_archive_max_processes=4

10) Password file in STANDBY Database

Move the passwordfile of Primary and rename in standby side with snadby SID. (if not done)

dbs]$ ls

initDEVDBS.ora

orapwDEVDBS

11) Do the following in STANDBY database:

dbs]$ export ORACLE_SID=DEVDBS

dbs]$ sqlplus / as sysdba

SQL> startup nomount  pfile='/home/oracle/app/product/11.2.0/dbhome_1/dbs/initDEVDBS.ora';

SQL> shu immediate;

SQL> startup nomount;

12) Do the following in PRIMRY Database: ( Restore process for STANDBY)

dbs]$ rman target /

RMAN> connect  auxiliary sys/sys@DEVDBS;

RMAN>

run {

allocate channel ch1 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database nofilenamecheck;

}

13) Reover the standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Note: Now the PRIMARY Database is in OPEN and role is PRIMARY. All transactions can be done into PRIMARY.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME          STATUS

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

DEVDB             OPEN

Now check the same in STANDBY Database;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME         STATUS

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

DEVDBS                       MOUNTED

Now you can keep this stage, so that if any archive log will ship, then it apply automatically.

14) Cancel the Recovery and keep it in Read only mode:

In this stage Archive logs will be applying into STANDBY, But do the following to Keep the STANDBY Database in Readonly mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

Note: Now SATANDBY database is in Read-only mode, No Redo-log will apply on STANDBY. So recent transactions can't be reflected.

15) To Keep the STANDBY database in Read-only mode and to apply redo-logs also, execute the following command in STANDBY database.

SQL> alter database recover managed standby database using  current logfile disconnect;

Database altered.

–      – – – Finish – – – –

Useful Queries to verify status:

1) Check the Final Status of Standby database:

SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;

NAME               INSTANCE_NAME         STATUS            OPEN_MODE                          DATABASE_ROLE

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

DEVDB            DEVDBS                      OPEN               READ ONLY WITH APPLY        PHYSICAL STANDBY

2) Check the Final Status of PRIMARY database:

SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;

NAME               INSTANCE_NAME          STATUS           OPEN_MODE               DATABASE_ROLE

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

DEVDB            DEVDBS                      OPEN               READ WRITE                PRIMARY

3) Current applied archive log in Standby side:

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

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

          6712

4) Archivelog status between primary and standby database

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

output:

    Thread    Last Sequence        Received Last Sequence           Applied Difference

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

         1                   6775                  6730                                       5

Issues faced in Active dataguard

Case:1:

Error 1017 received logging on to the standby

< ------------------------------------------------------------

< Check that the primary and standby are using a password file

< and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

< and that the SYS password is same in the password files.

<       returning error ORA-16191

< ------------------------------------------------------------

< PING[ARC2]: Heartbeat failed to connect to standby 'EHISBLRS'. Error is 16191.

< Sat Jul 06 15:23:28 2013

< Error 1017 received logging on to the standby

If above error found from alert log in DR side,

i.e., some body changed Primary sys password.

Solution: Recreate password file both Primary and DR side with same password and re-synch again.

Case:2:

channel ORA_DISK_1: SID=33 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 06/27/2012 15:05:53

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2012 15:05:53

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-01017: invalid username/password; logon denied

ORA-17629: Cannot connect to the remote database server

Solution:

RMAN> duplicate target database for standby from active database nofilenamecheck;

This may not work

do the following

RMAN>

run {

allocate channel ch1 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database nofilenamecheck;

}

 Thanks