Monday 30 January 2017

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