Oracle Data Guard Setup in Max Performance with a physical Standby from Active database

The data guard environment : -

• Two machine with oracle Linux 6.8 installed (Names: Primary and Standby)
• Oracle 11g database release 2 installed on each computer.
• On the primary database Oracle software is installed with a database with the name primary , on the standby database only the oracle software has been installed the database will be created from active primary database.
• Oracle home's path is identical on both databases.
• Control files are also on identical path

The data guard process will be implemented in two processes :-

• Primary database preparation.
• Standby database preparation

Primary database Preparation : -

1. enable force logging on the primary databases

SYS @ primary > alter database force logging;

SYS @ primary > select force_logging from v$database;

2. Add standby log files on the primary databases for storing redo received from primary database before being propagated to the standby database during swicthover or failover, they must be equal or more to the redo on the primary database.

SYS @ primary > select * from v$log;

SYS @ primary > alter database add standby logfile size 50M;

SYS @ primary > alter database add standby logfile size 50M;

SYS @ primary > alter database add standby logfile size 50M;

SYS @ primary > alter database add standby logfile size 50M;

SYS @ primary > select * from v$loglogfile;

3. set the log_archive_config and log_archive_config_2 parameters which disable or enable sending the sending of redo streams to standby.

SYS @ primary > show parameter db_name

NAME TYPE VALUE

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

db_name string primary

SYS @ primary > show parameter db_unique_name

NAME TYPE VALUE

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

db_unique_name string primary

SYS @ primary > alter system set log_archive_config='dg_config=(primary,standby)';

where primary is the primary database and standby is the standby database.

SYS @ primary > alter system set log_archive_dest_2='service=primary async valid_for=(online_logfile,primary_role) db_unique_name=standby';

where primary is the primary service name and standby is the db unique name of the standby database.

4. Set the standby file management to auto so that when oracle files are dropped or added to the primary database the changes are made on the standby automatically.

SYS @ primary > alter system set STANDBY_FILE_MANAGEMENT=AUTO;

5. Set the Primary database to run in archive log mode

SYS @ primary > archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 31

Next log sequence to archive 33

Current log sequence 33

SYS @ primary > shutdown immediate;

SYS @ primary > startup mount;

SYS @ primary > alter database archivelog;

SYS @ primary > alter database open;

6. Use net manager to ensure oracle net services aliases for both primary and standby are added to the tnsnames.ora.

[oracle@linux admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

then copy the tnsnames.ora to the standby database using secure copy

[oracle@linux admin]$ scp /u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora oracle@host1:/u01/app/oracle/product/11.2.0/home/network/admin/tnsnames.ora;

7. Copy the password file to the standby server

[oracle@host1 dbs]$ scp /u01/app/oracle/product/11.2.0/home/dbs/orapwprimary oracle@host1:/u01/app/oracle/product/11.2.0/home/dbs/orapwstandby

Standby database Preparation : -

- The SID will be Standby

1. Configure a listener on the standby with a static service entry of the standby databases

[oracle@host1 admin]$ more listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/home/n

etwork/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = standby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)

(SID_NAME = standby)

)

(SID_DESC =

(GLOBAL_DBNAME = standby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/home)

(SID_NAME = standby)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2. Create audit, fast recovery and oradata directories in the standby with the following path

[oracle@host1 adump]$ mkdir -p /u01/app/oracle/admin/standby/adump

[oracle@host1 standby]$ mkdir /u01/app/oracle/FRA/standby

[oracle@host1 oradata]$ mkdir /u01/app/oracle/oradata

3. Create a parameter file in the dbs directory with the db_name as the only value

[oracle@host1 oradata]$ cd $ORACLE_HOME/dbs

[oracle@host1 dbs]$ vi initstandby.ora

DB_NAME=standby

~

~

~

~

4. start the standby database in nomount mode

SYS @ standby > startup nomount

- Go back to the primary database and connect to rman in order to start the duplication by connecting to both primary and auxiliary databases

[oracle@linux admin]$ rman target / auxiliary sys@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 25 21:06:59 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRIMARY (DBID=1774373217)

auxiliary database Password:

connected to auxiliary database: PRIMARY (DBID=1774373217)

RMAN>

Run the below Rman script to duplicate the databases

run {

allocate channel p1 type disk;

allocate channel p2 type disk;

allocate channel p3 type disk;

allocate channel p4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'primary','standby'

set db_unique_name = 'standby'

set db_file_name_convert='/primary/','/standby/'

set log_file_name_convert='/primary/','/standby/'

set log_archive_max_processes='5'

set fal_server = 'primary'

set standby_file_management='AUTO'

set log_archive_config ='dg_config=(primary,standby)'

set log_archive_dest_2='service=primary async valid_for=(online_logfile,primary_role) db_unique_name=primary';

}

- After the script has completed successful run the following command on the standby databases

SYS @ standby > alter database recover managed standby database using current logfile disconnect;

The command enable redo apply to be started where current logfile allows redo to be applied as soon as it's received and disconnect option allow the redo apply to run in the background.

Now the data guard configuration is completed and can be tested by switching logfile on the primary database and checking the sequence number in the standby database.

On standby

SYS @ standby > select sequence#,first_time from v$archived_log;

SEQUENCE# FIRST_TIM

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

25 25-FEB-17

25 25-FEB-17

26 25-FEB-17

26 25-FEB-17

27 25-FEB-17

27 25-FEB-17

28 25-FEB-17

30 25-FEB-17

29 25-FEB-17

31 25-FEB-17

32 25-FEB-17

33 rows selected

On Primary database

SYS @ primary > alter system switch logfile;

System altered.

SYS @ primary >

On standby database

SYS @ standby > select sequence#,first_time from v$archived_log;

SEQUENCE# FIRST_TIM

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

25 25-FEB-17

25 25-FEB-17

26 25-FEB-17

26 25-FEB-17

27 25-FEB-17

27 25-FEB-17

28 25-FEB-17

30 25-FEB-17

29 25-FEB-17

31 25-FEB-17

32 25-FEB-17

SEQUENCE# FIRST_TIM

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

33 25-FEB-17

34 rows selected.

In the next article I will discuss Data broker configuration and basic tasks like switching over roles from primary to standby database.