Oracle Database Administration

Tuesday, 15 August 2017

Configure Oracle 11gR2 Data Guard Physical Standby

Synopsis

In this exercise we will configure Oracle Data Guard for Oracle 11gR2 database. We have two virtual machines configured where we have built Linux machines and installed Oracle 11gR2 database. Please refer below mentioned link to build Linux virtual machine and install Oracle 11gR2 database software

Primary Database

hostname : prim.localdomain
db_name : PRODPRIM
db_unique : PRODPRIM

Standby Database

hostname : stby.localdomain
db_name : PRODPRIM
db_unique : PRODSTBY

Configuration on the Primary Database :-

Step 1 :- Enable archivelog mode

Click on this link to see, how to enable archivelog mode

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

Step 2 :- Enable force logging

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

Step 3 :- Set database parameters to configure the data guard

Here are the list of parameters to be set to configure the dataguard

db_unique_name
log_archive_config
log_archive_dest_2
remote_login_passwordfile
fal_server
fal_client
standby_file_management

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODPRIM,PRODSTBY)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRODSTBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTBY';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> alter system set fal_server=PRODSTBY;

System altered.

SQL> alter system set fal_client='PRODPRIM';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

Step 4 :- Create and start the Listener

We need to create and start the listener on both primary and standby servers

Click Here to Configure and Start Listener

Step 5 :- Add tnsnames for services of primary and standby database

This step has to be performed on both primary and standby database servers. You can update the tnsnames.ora file to add service or service can be added using netca utility. Go to $ORACLE_HOME/network/admin/tnsnames.ora add the below mentioned line to the file

In case you want to add service using netca utility refer this link

Click here to add service using netca utility

PRODPRIM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = prim.localdomain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PRODPRIM)

)

PRODSTBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = stby.localdomain)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = PRODSTBY)

)

Step 6 :- Check the services if its been configured properly using tnsping on both the servers

[oracle@prim dbhome_1]$ tnsping PRODPRIM

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-AUG-2017 14:17:41

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRODPRIM)))

OK (0 msec)

[oracle@prim dbhome_1]$ tnsping PRODSTBY

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-AUG-2017 14:18:51

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stby.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRODSTBY)))

OK (20 msec)

Step 7 :- Backup primary database using RMAN utility

[oracle@prim dbhome_1]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 15 14:20:28 2017

connected to target database: PRODPRIM (DBID=881986728)

RMAN> backup database plus archivelog;

Step 8 :- Create standby control file

SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/PRODPRIM/stbycontrol1.ctl';

Database altered.

Step 9 :- Create pfile of primary database

SQL> create pfile from spfile;

File created.

Step 10 :- Update the newly created pfile with below mentioned changes

log_archive_dest_2 = 'SERVICE=PRODPRIM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODPRIM'
fal_client = PRODSTBY
fal_server = PRODPRIM
db_unique_name = PRODSTBY

Step 11 :- Copy newly created pfile, standby controlfile, password file and RMAN backup pieces to standby database server

[oracle@prim dbs]$ scp initPRODPRIM.ora oracle@stby.localdomain:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

The authenticity of host 'stby.localdomain (192.168.10.2)' can't be established.

RSA key fingerprint is a3:a6:ed:66:82:ad:bf:9b:6b:a7:59:4d:27:f6:f0:a0.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'stby.localdomain,192.168.10.2' (RSA) to the list of known hosts.