Friday , October 20 2017
Home / Oracle DBA / Step by Step process on how to configure Dataguard for Standby Database

Step by Step process on how to configure Dataguard for Standby Database

Dataguard is used to create standby database for live production database which will be used as disaster recovery for the live database. Below are the steps to be followed to configure dataguard.

Steps to be followed in Primary Database:

STEP 1: Check whether the database is in Archive Log Mode or not.

STEP 2: Enable force logging using below command:

 
SQL> alter database force logging;

STEP 3: Check following parameter. SYSDBA value should be set to TRUE.

 
SQL> select * from v$pwfile_users;

STEP 4: Check below parameters.

 
SQL> show parameter db_name;

SQL> show parameter db_unique_name;

STEP 5: Check below parameter and set the value for dataguard configuration as below:

 
SQL> show parameter log_archive_config;

SQL> alter system set log_archive_config='dg_config=(metro,standby)' scope=both;

NOTE: Here we have specified that the primary database name is metro and standby database name is standby.

STEP 6: Set following parameters:

 
SQL> alter system set log_archive_dest_1='location=/u01/app/arch valid_for=(all_logfiles, all_roles) db_unique_name=metro' scope=both;

SQL> alter system set log_archive_dest_2='service=standby valid_for=(online_logfile, primary_role) db_unique_name=standby' scope=both;

SQL> alter system set log_archive_dest_state_2='defer' scope=both;

NOTE: We have to set this parameter to specify the location for log shipping from primary to standby database.

Also set following parameters:

 
SQL> show parameter fal;

SQL> alter system set fal_server=standby scope=both;

SQL> alter system set fal_client=metro scope=both;

STEP 7: Check that remote_login_passwordfile parameter is set to EXCLUSIVE.

 
SQL> show parameter remote;

STEP 8: Set standby_file_management parameter to AUTO.

 
SQL> alter system set standby_file_management=AUTO scope=both;

STEP 9: Set following parameters in primary database. By doing this, it will change the locations while cloning a primary database into standby.

 
SQL> alter system set db_file_name_convert='/oradata/standby','/oradata/metro' scope=spfile;

SQL> alter system set log_file_name_convert='/oradata/standby','/oradata/metro' scope=spfile;

STEP 10: Create standby redo log files.

 
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/metro/standby1.log') size 50m;

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/metro/standby2.log') size 50m;

SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/metro/standby3.log') size 50m;

SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/metro/standby4.log') size 50m;

STEP 11: Create PFILE from SPFILE and copy it to the standby server and make following changes:

 
SQL> create pfile='/tmp/initstandby.ora' from spfile;

Pfile looks like this before changes:

>> Replace “metro” with “clonedb1” as we have given this name for our standby server.

>> fal_client and fal_server should be vice versa (i.e fal_client should be “clonedb1” and fal_server should by primary database “metro”)

STEP 12: Create all directories configured in pfile in standby server.

mkdir -p /u01/app/oracle/admin/clonedb1/adump

mkdir -p /u01/app/oracle/oradata/clonedb1

mkdir -p /u01/app/oracle/flash_recovery_area/clonedb1

STEP 13: Connect RMAN and take full database backup and current control file backups.

 
RMAN> backup database plus archivelog;

RMAN> backup current controlfile for standby;

STEP 13: Create same backup directory in standby server and copy all backup pieces to it.

$ mkdir -p /u01/app/oracle/flash_recovery_area/METRO/backupset/2017_07_14 (In standby server)

$ scp * oracle@192.168.43.122: /u01/app/oracle/flash_recovery_area/METRO/backupset/2017_07_14 (in primary server)

NOTE: Below steps are to be performed in Standby Server.

STEP 14: Create clonedb1 entry in /etc/oratab file.

STEP 15: Set the oracle environment as “clonedb1”.

STEP 16: Login to SQLPLUS with sysdba user and start up the database with nomount. This will start an auxiliary instance for clonedb1 database.

STEP 17: Connect to RMAN and follow below steps:

 
[oracle@localhost flash_recovery_area]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 12 22:01:09 2017

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

RMAN> connect auxiliary /

connected to auxiliary database: CLONEDB1 (not mounted)

RMAN> connect target sys/oracle@metro

connected to target database: METRO (DBID=2811244290)

RMAN> run

2> {

3> allocate auxiliary channel c1 device type disk;

4> allocate channel c2 device type disk;

5> duplicate target database to 'clonedb1';

6> }

NOTE: Once the database duplication is completed, perform following validation steps to see that the log shipping is happening and mrp process in standby server is activated.

Validation STEP 1 Query below to check the database role and it should be PHYSICAL STANDBY.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

Validation STEP 2 Execute below to start mrp process on the standby server.

SQL> alter database recover managed standby database disconnect;

Database altered.

This will start the mrp process which can checked using below command.

ps -ef|grep mrp

oracle    5986     1  0 14:27 ?        00:00:01 ora_mrp0_standnew

oracle    6020  5194  0 14:29 pts/1    00:00:00 /bin/bash -c ps -ef|grep mrp

Validation STEP 3 Once the mrp process is up and running, the log shipping starts which you can trace from “background_dump_dest” path by executing command “tail –f alert_standnew.log”

SQL> show parameter dump;


NAME                                   TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/standnew/standnew/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/standnew/standnew/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/standnew/standnew/trace

SQL> !

[oracle@localhost admin]$ cd /u01/app/oracle/diag/rdbms/standnew/standnew/trace

[oracle@localhost trace]$ tail -f alert_standnew.log

Managed Standby Recovery not using Real Time Apply

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /u01/app/arch/1_31_949486367.dbf

Completed: alter database recover managed standby database disconnect

Media Recovery Log /u01/app/arch/1_32_949486367.dbf

Media Recovery Log /u01/app/arch/1_33_949486367.dbf

Media Recovery Log /u01/app/arch/1_34_949486367.dbf

Media Recovery Log /u01/app/arch/1_35_949486367.dbf

Media Recovery Waiting for thread 1 sequence 36 (in transit)

Mon Jul 31 14:32:19 2017

Archived Log entry 19 added for thread 1 sequence 36 ID 0x4491721b dest 1:

Mon Jul 31 14:32:19 2017

RFS[4]: Selected log 4 for thread 1 sequence 37 dbid 1150379035 branch 949486367

Mon Jul 31 14:32:24 2017

Media Recovery Log /u01/app/arch/1_36_949486367.dbf

Media Recovery Waiting for thread 1 sequence 37 (in transit)

Comments

comments

Check Also

INS-41814 The installer has detected that some of the services of Oracle Grid Infrastructure are not running on this system.

ERROR DESCRIPTION ================= INS-41814 The installer has detected that some of the services of Oracle …

Leave a Reply

Your email address will not be published. Required fields are marked *