Davis Apps DBA: Oracle Dataguard Configuration

Here in this post we'll see about Oracle Dataguard Configuration.

I've configured Oracle Dataguard Configuration using two methods. One is Oracle Standard Method. It is the approach recommended by oracle and the second one is configuring using a different method. The second one is much easier to configure, but it is not recommended and also manual intervention is needed to sync the log files between Primary and Standby after Role transitions (i.e) Switch-Over/Switch-Back

Now we'll see about the Oracle recommended approach to configure Dataguard setup. If you want to configure using the second method Click Here

Naming Conventions:

Primary DB Name : asdb
Primary DB Unique Name : asdb

Standby DB Name : asdb
Standby DB Unique Name : stdasdb

Below is the High-Level steps for Dataguard Configuration

Add entries in hosts file
Configure Primary Database for Disaster Recovery
Backup Primary Database for Standby Database creation
Install Oracle Home for Standby Database
Edit and change the pfile on the Standby side
Create Standby Database using Primary backup
Edit listener, tnsnames and sqlnet files of both the databases
Check tnsping and sql connectivity from both the ends
Start MRP process
Important Queries

Add entries in hosts file

Add entries in hosts file of both Primary and Standby servers in such a way that both servers should have host entries of Primary and Standby servers.

Configure Primary Database for Disaster Recovery

Configuration of the Primary Database includes the following tasks to be done.

Enabling Archive log

Open a command prompt to your operating system.

sqlplus '/as sysdba'

Configure how much space you want to dedicate to your Fast Recovery Area:

alter system set db_recovery_file_dest_size = 50G;

This example dedicates 50GB. You should see this:

System altered.

Choose the destination:

alter system set db_recovery_file_dest=’/orahome/app/oracle/product/FRA’;

You should see this:

System altered.

Now Oracle automatically creates your archive logs under the Fast Recovery Area. The archive process creates a folder for your database and the subfolder for the date the archives were created. It organizes them very nicely.

If you want to see how much of your Fast Recovery Area is used, log in to SQL*Plus and query the view V$FLASH_RECOVERY_AREA_USAGE. It’s sort of a misnomer. Oracle still hasn’t changed the name of this view from FLASH to FAST even though they made the change in the documentation two versions ago.

After determining where you want to keep the archive logs, you can turn on archiving. These steps walk you through the process:

Open a command prompt to your operating system.

sqlplus '/as sysdba'

Shut down the database:

shutdown immediate

You see this:

Database closed.

Database dismounted.

ORACLE instance shut down.

Start the database in mount mode:

startup mount

You see something like this:

ORACLE instance started.

Total System Global Area 1336176640 bytes

Fixed Size 2287480 bytes

Variable Size 1258293384 bytes

Database Buffers 67108864 bytes

Redo Buffers 8486912 bytes

Database mounted.

Issue the command to enable archive mode:

alter database archivelog;

You should see this:

Database altered.

Open the database:

alter database open;

You should see this:

Database altered.

Now your database is in archive log mode, and archive log files should show up in your Fast Recovery Area.

Enable Forced logging

SQL> alter database force logging;

Configuring log archive destination parameters

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=asdb';

SQL> alter system set log_archive_dest_2= 'SERVICE=asdbdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdasdb'

Configuring log archive dest state parameter

SQL> alter system set log_archive_dest_state_2='enable';

Creating password file

In password file creation below points needs to be taken care and they are mandatory.

I. Password file should be created from Primary

II. Password files’s password should match the password of the the SYS user

III. Password file should be copied from Primary to Standby and not should not be created separately on Standby server

orapwd file=orapwasdb password=Manager_123 force=y

Configuring fal_server and fal_client parameter

SQL> alter system set fal_server=' asdbdr';

SQL> alter system set fal_client='asdb';

Configuring log archive config parameter

SQL> alter system set log_archive_config='DG_CONFIG=(asdb,stdasdb)';

Configuring remote login parameter

SQL> alter system set remote_login_passwordfile='EXCLUSIVE';

Configuring case sensitive login parameter

SQL> alter system set sec_case_sensitive_logon=TRUE;

Configuring standby file management parameter

SQL> alter system set standby_file_management='AUTO';

Configuring local listener parameter

SQL> alter system set local_listener='LISTENER_ASDB';

Backup Primary Database for Standby Database creation

Use the below syntax to create a backup for Standby database from Primary

Syntax

rman target /

RMAN> backup database include current controlfile for standby plus archivelog backup location ‘<backup location>’;

Install Oracle Home for Standby Database

Edit and change the pfile on the Standby side

First of all copy the pfile from Primary to Standby Database, make a backup copy and then follow the below steps to incorporate the required changes on the Standby Database.

Configuring log archive destination parameters

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdasdb';

SQL> alter system set log_archive_dest_2='SERVICE=asdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=asdb';

Configuring log archive dest state parameter

SQL> alter system set log_archive_dest_state_2='enable';

Configuring fal_server and fal_client parameter

SQL> alter system set fal_server=' asdb';

SQL> alter system set fal_client='asdbdr';