Data Guard Physical Standby Setup in Oracle Database 11gR1

Hi DBA-Mates,
As we have already seen the DataGuard Concepts and details. Today, I am going to share the Dataguard setup. How to do dataguard setup and configuarions.
Here, in these documents, I show you guys step by step dataguard steup and configuration which I have done in my real time environments.

Setup:

Configure Primary Database Tier

Preparing the Primary Database for Standby Database Creation

If the database is running on pfile then create spfile and make sure that the database is running on spfile so that we can set the parameter when the database is up and running. Check whether the database is in archive log mode or not.

Set initialization parameters on primary database

Before setting the parameters we must know the difference between few parameters on primary and standby databases

Primary Database Standby database

SID PROD PROD

DB_UNIQUE_NAME PROD PRDSTBY

Oracle Net Service name PROD PRDSTBY

The database SID is same on both the primary and physical standby databases.

Connect to Primary database server

If we are using spfile we have to set all the parameters using “Alter system set” syntax and if pfile is being

used then we need to make the changes in the pfile manually and bounce the database to take effect.

Log_archive_config ='dg_config=(prod,prdstby)'

Log_archive_dest_1 ='location=/test2/archives'

Log_archive_dest_2 ='service=prdstby lgwr async valid_for=(online_logfiles,primary_role)

db_unique_name=prdstby'

Log_archive_dest_state_2 ='defer'

Fal_server ='prdstby'

Fal_client ='prod'

REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

Standby_file_management ='auto'

Create standby redolog

Create standby redo logs on the primary database to support the standby role. Standby redo logs are additional redo log groups, with a different type. You can only have as many redo log groups overall as the maximum setting for your database for logfile groups and logfile members.

Standby redo log groups should be multiplexed in the same manner as online redo log groups. 
The standby redo logs must be the same size as the primary database online redo logs.

Query to find the standby redo-logs that have to be created depending on the current redologs

select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

union all

select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

and rownum <=2

/

SQL> alter database add standby logfile thread 1 group 3 ( '/test1/db/apps_st/data/stdbylog03a.dbf',

'/test1/db/apps_st/data/stdbylog03b.dbf') size 1G;

SQL> alter database add standby logfile thread 1 group 4 ( '/test1/db/apps_st/data/stdbylog04a.dbf',

'/test1/db/apps_st/data/stdbylog04b.dbf') size 1G;

SQL> alter database add standby logfile thread 1 group 5 ( '/test1/db/apps_st/data5/stdbylog05a.dbf',

'/test1/db/apps_st/data5/stdbylog05b.dbf') size 1G;

[oraprod@proddb ~]$ cd $ORACLE_HOME/appsutil/scripts/PROD_proddb/

[oraprod@proddb PROD_proddb]$ perl adpreclone.pl dbTier

Copyright (c) 2002 Oracle Corporation

Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adpreclone Version 120.20.12010000.2

Enter the APPS User Password:

Running:

perl /test1/db/tech_st/11.1.0/appsutil/bin/adclone.pl java=/test1/db/tech_st/11.1.0/appsutil/jre mode=stage

stage=/test1/db/tech_st/11.1.0/appsutil/clone component=dbTier method=CUSTOM

dbctx=/test1/db/tech_st/11.1.0/appsutil/PROD_proddb.xml showProgress

APPS Password :

Beginning database tier Stage - Wed May 28 15:24:31 2014

/test1/db/tech_st/11.1.0/appsutil/jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=false

-Doracle.installer.oui_loc=/test1/db/tech_st/11.1.0/oui -classpath

/test1/db/tech_st/11.1.0/lib/xmlparserv2.jar:/test1/db/tech_st/11.1.0/jdbc/lib/ojdbc6.jar:/test1/db/tech_st/11.1.0/ap

psutil/java:/test1/db/tech_st/11.1.0/oui/jlib/OraInstaller.jar:/test1/db/tech_st/11.1.0/oui/jlib/ewt3.jar:/test1/db/tech

_st/11.1.0/oui/jlib/share.jar:/test1/db/tech_st/11.1.0/oui/jlib/srvm.jar:/test1/db/tech_st/11.1.0/jlib/ojmisc.jar

oracle.apps.ad.clone.StageDBTier -e /test1/db/tech_st/11.1.0/appsutil/PROD_proddb.xml -stage

/test1/db/tech_st/11.1.0/appsutil/clone -tmp /tmp -method CUSTOM -showProgress

APPS Password :

Log file located at /test1/db/tech_st/11.1.0/appsutil/log/PROD_proddb/StageDBTier_05281524.log

- 50% completed

Completed Stage...

Wed May 28 15:25:56 2014

Tar the database software (ie., tech stack) of the primary database,

Fetch.

[oraprod@proddb db]$ tar -czvf /nfsbackup/erp/prod_db_techstack_28may2014.tar.gz tech_st/

Copy the tar file to the standby server

[oraprod@proddb db]$ scp /nfsbackup/erp/prod_db_techstack_28may2014.tar.gz

orastby@10.10.10.123:/files/

Configure Standby Database Tier

Connect to Standby database server

[orastby@ebs bin]$ cd /files/tech_st/11.1.0/appsutil/clone/bin

[orastby@ebs bin]$ perl adcfgclone.pl dbtechstack

Copyright (c) 2002 oracle corporation

Redwood shores, california, usa

Oracle applications rapid clone

Version 12.0.0

Adcfgclone version 120.31.12010000.1

Enter the apps password :

Running:

/files/tech_st/11.1.0/appsutil/clone/bin/../jre/bin/java -xmx600m -cp

/files/tech_st/11.1.0/appsutil/clone/jlib/java:/files/tech_st/11.1.0/appsutil/clone/jlib/xmlparserv2.jar:/files/tech_st/

11.1.0/appsutil/clone/jlib/ojdbc5.jar oracle.apps.ad.context.CloneContext -e

/files/tech_st/11.1.0/appsutil/clone/bin/../context/db/ctxorig.xml -validate -pairsfile /tmp/adpairsfile_6635.lst

-stage /files/tech_st/11.1.0/appsutil/clone -dbtechstack 2> /tmp/adcfgclone_6635.err; echo $? >

/tmp/adcfgclone_6635.res

Log file located at /files/tech_st/11.1.0/appsutil/clone/bin/clonecontext_0528171240.log

Provide the values required for creation of the new database context file.

Target system hostname (virtual or normal) [ebs] :

Target instance is rac (y/n) [n] :

Target system database sid : prod

Target system base directory : /files/

Target system utl_file_dir directory list : /usr/bin/

Number of data_top's on the target system [5] :

Target system data_top directory 1 [/test1/db/apps_st/data] : /files/apps_st/data

Target system data_top directory 2 [/test1/db/apps_st/data2] : /files/apps_st/data2

Target system data_top directory 3 [/test1/db/apps_st/data4] : /files/apps_st/data4

Target system data_top directory 4 [/test1/db/apps_st/data5] : /files/apps_st/data5

Target system data_top directory 5 [/test1/db/apps_st/data3] : /files/apps_st/data3

Target system rdbms oracle_home directory [/files/db/tech_st/11.1.0] : /files/tech_st/11.1.0

Do you want to preserve the display [proddb:0.0] (y/n) ? : n

Target system display [ebs:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? :

Complete port information available at /files/tech_st/11.1.0/appsutil/clone/bin/out/prod_ebs/portpool.lst

Backing up /files/tech_st/11.1.0/appsutil/prod_ebs.xml to /files/tech_st/11.1.0/appsutil/prod_ebs.xml.bak

Creating the new database context file from :

/files/tech_st/11.1.0/appsutil/template/adxdbctx.tmp

The new database context file has been created :

/files/tech_st/11.1.0/appsutil/prod_ebs.xml

Log file located at /files/tech_st/11.1.0/appsutil/clone/bin/clonecontext_0528171240.log

Check clone context logfile /files/tech_st/11.1.0/appsutil/clone/bin/clonecontext_0528171240.log for details.

Running rapid clone with command:

Perl /files/tech_st/11.1.0/appsutil/clone/bin/adclone.pl java=/files/tech_st/11.1.0/appsutil/clone/bin/../jre

mode=apply stage=/files/tech_st/11.1.0/appsutil/clone component=dbtechstack method=custom

dbctxtg=/files/tech_st/11.1.0/appsutil/prod_ebs.xml showprogress contextvalidated=true

Running:

Perl /files/tech_st/11.1.0/appsutil/clone/bin/adclone.pl java=/files/tech_st/11.1.0/appsutil/clone/bin/../jre

mode=apply stage=/files/tech_st/11.1.0/appsutil/clone component=dbtechstack method=custom

dbctxtg=/files/tech_st/11.1.0/appsutil/prod_ebs.xml showprogress contextvalidated=true

Apps password :

Beginning rdbms home apply - wed may 28 17:13:39 2014

/files/tech_st/11.1.0/appsutil/clone/bin/../jre/bin/java -xmx600m -dcontext_validated=true

-doracle.installer.oui_loc=/files/tech_st/11.1.0/oui -classpath

/files/tech_st/11.1.0/appsutil/clone/jlib/xmlparserv2.jar:/files/tech_st/11.1.0/appsutil/clone/jlib/ojdbc6.jar:/files/te

ch_st/11.1.0/appsutil/clone/jlib/java:/files/tech_st/11.1.0/appsutil/clone/jlib/oui/orainstaller.jar:/files/tech_st/11.1.

0/appsutil/clone/jlib/oui/ewt3.jar:/files/tech_st/11.1.0/appsutil/clone/jlib/oui/share.jar:/files/tech_st/11.1.0/appsuti

l/clone/jlib/oui/srvm.jar:/files/tech_st/11.1.0/appsutil/clone/jlib/ojmisc.jar

oracle.apps.ad.clone.ApplyDBTechStack -e /files/tech_st/11.1.0/appsutil/prod_ebs.xml -stage

/files/tech_st/11.1.0/appsutil/clone -showprogress

Apps password : log file located at /files/tech_st/11.1.0/appsutil/log/prod_ebs/applydbtechstack_05281713.log

/ 4% completed

Completed apply...

Wed may 28 17:15:14 2014

Starting database listener for prod:

Running:

/files/tech_st/11.1.0/appsutil/scripts/prod_ebs/addlnctl.sh start prod

You are running addlnctl.sh version 120.1.12010000.2

Logfile: /files/tech_st/11.1.0/appsutil/log/prod_ebs/addlnctl.txt

Starting listener process prod ...

Addlnctl.sh: exiting with status 0

[orastby@ebs bin]$

[orastby@ebs bin]$ cd $ORACLE_HOME

[orastby@ebs 11.1.0]$ ln -s /files/tech_st/11.1.0/prod_ebs.env /home/orastby/prod_ebs.env

[orastby@ebs 11.1.0]$ . Prod_ebs.env

[orastby@ebs 11.1.0]$ cd $oracle_home/network/admin/prod_ebs

[orastby@ebs 11.1.0]$ vi listener.ora

Metalink id for listener blocked status before doing rman duplicate: connection to auxiliary using connect

string failed with ora-12528 (doc id 419440.1)

Create a static entry for the auxiliary database in the listener.ora file of the auxiliary database oracle_home and

restart the listener. For example

Create the listener as below on the standby

Prod =

(description_list =

(description =

(address = (protocol = tcp)(host = 10.10.10.123)(port = 1521))

)

)

Sid_list_listener =

(sid_list =

(sid_desc =

(sid_name = plsextproc)

(oracle_home = /files/tech_st/11.1.0)

(program = extproc)

)

(sid_desc =

(sid_name = prdstby)

(oracle_home = /files/tech_st/11.1.0)

(service_name = prdstby)

)

)

Or/and

Use the oracle10g feature ( specify (UR=A) in connect data )

make below entry in the tnsnames.ora file of the auxiliary database oracle_home

Tnsnames. Ora on production

Prdstby =

(description =

(address_list =

(address = (protocol = tcp)(host = 10.10.10.123)(port = 1521))

)

(connect_data =

(service_name = prdstby)(UR=A)

)

)

Perform tnsping on both the servers

Connect to standby database server

[orastby@ebs prod_ebs]$ tnsping prod

Tns ping utility for linux: version 11.1.0.7.0 - production on 03-jun-2014 14:56:49

Copyright (c) 1997, 2008, oracle. All rights reserved.

Used parameter files:

Used tnsnames adapter to resolve the alias

Attempting to contact (description = (address_list = (address = (protocol = tcp)(host = 10.10.10.456)(port =

1521))) (connect_data = (service_name = prod) (UR=A)))

Ok (30 msec)

[orastby@ebs prod_ebs]$ tnsping prdstby

Tns ping utility for linux: version 11.1.0.7.0 - production on 03-jun-2014 14:56:52

Copyright (c) 1997, 2008, oracle. All rights reserved.

Used parameter files:

Used tnsnames adapter to resolve the alias

Attempting to contact (description = (address_list = (address = (protocol = tcp)(host = 10.10.10.123)(port =

1521))) (connect_data = (service_name = prdstby)(UR=A)))

Ok (0 msec)

Connect to Primary database server

[oraprod@proddb ~]$ tnsping prod

Tns ping utility for linux: version 11.1.0.7.0 - production on 04-jun-2014 13:05:11

Copyright (c) 1997, 2008, oracle. All rights reserved.

Used parameter files:

/test1/db/tech_st/11.1.0/network/admin/prod_proddb/sqlnet_ifile.ora

Used tnsnames adapter to resolve the alias

Attempting to contact (description= (address=(protocol=tcp)(host=proddb.ora-data.blogspot.in)(port=1521))

(connect_data= (service_name=prod) (instance_name=prod)))

Ok (10 msec)

[oraprod@proddb ~]$ tnsping prdstby

Tns ping utility for linux: version 11.1.0.7.0 - production on 04-jun-2014 13:05:14

Copyright (c) 1997, 2008, oracle. All rights reserved.

Used parameter files:

/test1/db/tech_st/11.1.0/network/admin/prod_proddb/sqlnet_ifile.ora

Used tnsnames adapter to resolve the alias

Attempting to contact (description = (address_list = (address = (protocol = tcp)(host = 10.10.10.123)(port =

1521))) (connect_data = (service_name = prdstby)(UR=A)))

Ok (0 msec)

[oraprod@proddb ~]$

Connect to standby database server

New initialization file will be created now add/change the below parameter in the pfile and now create spfile

using that pfile,

Db_unique_name = prdstby

Log_file_name_convert

='/test1/db/apps_st/data','/files/apps_st/data','/test1/db/apps_st/data5','/files/apps_st/data5'

Log_archive_config ='dg_config=(prdstby,prod)'

Log_archive_dest_1 ='location=/data/standby_archives/'

Log_archive_dest_2 ='service=prod lgwr async valid_for=(online_logfiles,primary_role)

db_unique_name=prod'

Log_archive_dest_state_2 ='defer'

Fal_server ='prod'

Fal_client ='prdstby'

REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'

Standby_file_management ='auto'

Now check whether we are able to connect to the databases from both the ends (production & standby)

Connect to Primary database server

[oraprod@proddb ~]$ sqlplus sys/*********@prod as sysdba

Sql*plus: release 11.1.0.7.0 - production on wed jun 4 13:07:28 2014

Copyright (c) 1982, 2008, oracle. All rights reserved.

Connected to:

Oracle database 11g enterprise edition release 11.1.0.7.0 - 64bit production

With the partitioning, olap, data mining and real application testing options

Sql> exit

Disconnected from oracle database 11g enterprise edition release 11.1.0.7.0 - 64bit production

With the partitioning, olap, data mining and real application testing options

[oraprod@proddb archives]$ sqlplus sys/*********@prdstby as sysdba

Sql*plus: release 11.1.0.7.0 - production on wed jun 4 13:07:34 2014

Copyright (c) 1982, 2008, oracle. All rights reserved.

Connected to:

Oracle database 11g enterprise edition release 11.1.0.7.0 - 64bit production

With the partitioning, olap, data mining and real application testing options

Sql> exit

Duplicate the database from primary

Once we are able to get connected, we need to run the rman from the primary database

[oraprod@proddb ~ ]$ rman target sys/********@prod auxiliary sys/*********@prdstby

Recovery manager: release 11.1.0.7.0 - production on wed jun 4 13:09:58 2014

Copyright (c) 1982, 2007, oracle. All rights reserved.

Connected to target database: prod (dbid= ***********)

Connected to auxiliary database: prod (not mounted)

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

Once the backup is completed, the standby database will be in mount stage. Keep the database in recovery

mode so that the archives which are shipped from primary server will be applied directly on the standby server.

Connect to standby database server

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 26 05:34:09 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

MOUNT

Once the database is in mount state, we need to put the database in the MRP (Managed Recovery Process) mode

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

Database altered.

Check the sequence number on the standby

SQL> select sequence#,process,status,block# from v$managed_standby;

SEQUENCE# PROCESS STATUS BLOCK#

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

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

4790 MRP0 WAIT_FOR_LOG 0

Catalog and apply the archives through RMAN if there is a lot of gap.

Connect to Primary database server

Enable the transmission of redo log to the physical standby database

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE ;

System altered.

Connect to Standby database server

SQL> select sequence#,process,status,block# from v$managed_standby;

SEQUENCE# PROCESS STATUS BLOCK#

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

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

4794 MRP0 APPLYING_LOG 726764

0 RFS IDLE 0

4798 RFS IDLE 2836481

4797 RFS IDLE 194561

SQL> /

SEQUENCE# PROCESS STATUS BLOCK#

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

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

0 ARCH CONNECTED 0

4804 MRP0 WAIT_FOR_LOG 0

0 RFS IDLE 0

0 RFS IDLE 0

0 RFS IDLE 0

4804 RFS IDLE 975235

Now the Standby database is completey synced with the Primary database

Connect to Standby database server Validate whether the archives shipped are applied on the

standby or not

Connect to the Primary database server

SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS ERROR

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

VALID

SQL> select name,dest_id,sequence#,creator,registrar,standby_dest,archived,applied,deleted,status, completion_time from v$archived_log order by completion_time desc;

If the applied column is YES against dest_id : 2 , then the standby is applying archives from

production.

Now check the alert log file on both the servers,

From the Primary database server log :

[oraprod@proddb ~]$ tail -f /test2/diag/diag/rdbms/prod/PROD/trace/alert_PROD.log

Wed Jun 04 11:32:12 2014

LNS1 started with pid=277, OS id=28046

Beginning log switch checkpoint up to RBA [0x12c0.2.10], SCN: 6815176626618

Thread 1 advanced to log sequence 4800 (LGWR switch)

Current log# 4 seq# 4800 mem# 0: /test1/db/apps_st/data/log04a.log

Current log# 4 seq# 4800 mem# 1: /test1/db/apps_st/data/log04b.log

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Wed Jun 04 11:32:39 2014

Completed checkpoint up to RBA [0x12c0.2.10], SCN: 6815176626618

Wed Jun 04 11:40:28 2014

Sweep Incident[32057]: completed

Wed Jun 04 11:42:54 2014

NAME DEST_ID SEQUENCE# CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME

/test2/archives/1_4804_842894508.dbf 1 4804 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4804 LGWR LGWR YES YES YES NO A 04-JUN-14

/test2/archives/1_4803_842894508.dbf 1 4803 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4803 LGWR LGWR YES YES YES NO A 04-JUN-14

/test2/archives/1_4802_842894508.dbf 1 4802 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4802 LGWR LGWR YES YES YES NO A 04-JUN-14

/test2/archives/1_4801_842894508.dbf 1 4801 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4801 LGWR LGWR YES YES YES NO A 04-JUN-14

/test2/archives/1_4800_842894508.dbf 1 4800 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4800 LGWR LGWR YES YES YES NO A 04-JUN-14

PRDSTBY 2 4799 ARCH ARCH YES YES YES NO A 04-JUN-14

/test2/archives/1_4799_842894508.dbf 1 4799 ARCH ARCH NO YES NO NO A 04-JUN-14

PRDSTBY 2 4797 ARCH ARCH YES YES YES NO A 04-JUN-14

PRDSTBY 2 4798 ARCH ARCH YES YES YES NO A 04-JUN-14

From the Primary database server log :

[orastby@ebs ~]$ tail -f

/files/tech_st/11.1.0/admin/PROD_ebs/diag/rdbms/prdstby/PROD/trace/alert_PROD.log

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[4]: Assigned to RFS process 8458

RFS[4]: Identified database type as 'physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

RFS[4]: No standby redo logfiles of size 2867200 blocks exist

Wed Jun 04 10:45:19 2014

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[5]: Assigned to RFS process 8461

RFS[5]: Identified database type as 'physical standby'

RFS[5]: No standby redo logfiles of size 1865162 blocks exist

RFS[5]: Archived Log: '/data/standby_archives/1_4799_842894508.dbf'

Wed Jun 04 10:45:31 2014

Redo Shipping Client Connected as PUBLIC

Some more useful link:

RAC Concepts and Details

Oracle Database

R12.2 Architecture and Concepts

Interviews Questions and Answers

Job/Career Tips
Regards,
ora-data Team