Configure Oracle Dataguard

Today I am going to cover step by step configuration for Oracle Dataguard & may be in next or future posts I will discuss on common mistakes which you can do while configure dataguard / standby database.

Below steps are based on assumptions that Primary Database is on Machine1.domain.com & Standby database is on Machine2.domain.com . Database Instance Name is PROD and database listener is listening on port 1525. Mount point on primary & standby database are same (If they are not you need to set parameter db_file_convert)

Assumptions
I am assuming using LGWR, ASYNC option with log shipping service which means ,
LGWR (Log Writer process will be used to write to standby site instead of ARC archiver process)
ASYNC (Redo logs to standby is asynchronous to primary site)

You may have to change options with log shipping service (LOG_ARCHIVE_DEST_n) depending on data protection mode you wish to choose. (I am using Maximum Performance Mode – Default Mode)

Enable Archive log
For standby database configuration your primary database should be running in achieve log mode. In order to convert your primary database into archive log mode follow these steps

SQL> SHUTDOWN
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> alter system set LOG_ARCHIVE_DEST_1 =’LOCATION=/u01/oracle/data/arch’ scope=spfile; (I am assuming that you are using spfile here , if you are using pfile skip scope=spfile)

SQL> alter system set LOG_ARCHIVE_DEST_2 =’SERVICE=PROD_remote1.domain.com LGWR ASYNC REOPEN=60′ scope=spfile; (We will create above TNS Alias in next step)
SQL> alter system log_archive_dest_2=’DEFER’ (Defer this until you have standby system Up)

SQL> shutdown immediate
SQL> startup

You should see archive logs generated after this on standby site

Enable Force Logging
You should enable Force logging in primary database else if some transaction which doesn’t generate redo log can corrupt your standby database. (Careful in OLTP transactions or long running requests in Apps)
SQL> ALTER DATABASE FORCE LOGGING;

Set Initialization Parameter
SQL> alter system set fal_client =

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Oracle Gold Partner specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

30 comments
Kevin Closson says November 28, 2006

Thanks for this DG post. I do have a question, however. How do you use a copied OH on the standby node? Oracle Home has so many embedded hostname occurances throughout… are you speaking of an OH clone ?

Reply
Atul Kumar says November 29, 2006

Kevin,
I agree that there will be some hostname entries in oracle_home like for db console & that will not work but you can overcome that by using primary database hostname as virtual host name in standby like lets in ex. above primary hostname was hostname1.domainname.com , then in standby host /etc/hosts we can use
IP_of_standby hostname2.domainname.com hostname2 hostname1.domainname.com hostname1

Now be careful when you use above solution, your tnsnames.ora should use IP address in standby site instead of hostname

You can avoid these issues by doing a fresh Install on standby site as well.

Atul

Reply
Anonymous says December 7, 2006

Kevin,

Another Option you have is to clone the Oracle Home.

you have option in runInstaller -clone

you can go through runInstaller help on this.

Suresh
Oracle Corp.

Reply
Anonymous says December 7, 2006

Atul, Excellent urs is the best Blog spot i have ever seen.

Kevin, You can go for Oracle Clone option coming along with runinstaller -clone.

Suresh
Oracle Corp.

Reply
Atul Kumar says December 7, 2006

Suresh,
Thanks a lot for your comments. Thanks for sharing clone option in Installer with everyone .

Everyday is new learning & I would like to explore clone option in my next configuration :)

Atul

Reply
Anonymous says February 27, 2007

Hi,

Thanks for the informative guide so far! Was wondering when you were going to complete it and my main question was that is there a way you can automae monitoring of what logs have been applied to the standby database (on Windows). Hence once applied delete the log?

Thanks in advance

Reply
Atul Kumar says February 27, 2007

Hi Anonymous,
I missed it completely that I have to finish it . Try to cover in a week or two

Reply
Anonymous says May 30, 2007

hi,

when you are going to update the remaing steps?and also it will be helpful if you exlpain more in configuring the DG on the same host.

Thanks;
K

Reply
Anonymous says July 5, 2007

Hi, Atul,

Can I have two different types of standby databases, like one physical and one logical standby database?

I recently build one physical standby, it works well after some workaround. But new requirements is coming, a logical standby in need. Do you have the doc to implement the system?

Thanks,

Hank

Reply
Atul Kumar says July 5, 2007

Check at

http://download.oracle.com/
docs/cd/B19306_01/server.102/b14239/create_ls.htm#g105412

add above two in single line and paste in browser

Reply
gajanan says October 31, 2007

DATAGUARD ON 9I IS SIMPLE COMPARED TO DATAGUARD ON APPS 11I, ACTUALLY I, AM CONFUSED ABOUT ARCHITECTURE OF THE COMPLETE STRUCTURE OF PRIMARY AND SECONDARY DATABASE AS BACKEND AND APPS 11I AS FRONTEND, IS IT POSSIBLE TO GIVE SCHEMATIC OF THIS COMPLETE STRUTURE.

Reply
Atul says October 31, 2007

Gajanana

Dataguard is same for 11i database or simple 9i or 10g database as 11i also in turn uses 9i or 10g database only.

In 11i there are two componnets : Application Tier & Database Tier.

Database Tier in DR is handeled by Data Guard and I think you know about it.

Application Tier in DR is handeled by copying files manually or by o.s. utility like rsynch

Reply
madhum says November 14, 2007

Hi Atul,

Great post .

I am facing a problem with configuring DB_UNIQUE_NAME … what are the changes that should be made if my primary db and secondary db have the same names ? Also in this case can you enlighten me on what should be the tnsnames.ora file ?

thanks

Reply
Atul says November 14, 2007

Madhum,
Yes you can have same db_name but if they are on same machine instance name should be different.

DB_UNIQUE_NAME is optional parameter and required only if LOG_ARCHIVDE_CONFIG is defined.

TNS Name for primary and standby can be any name this is logical representation to reach from primary to standby & vice versa.

For more info check

http://download.oracle.com/
docs/cd/B19306_01/server.102/b14239/init_params.htm

Reply
madhum says November 14, 2007

Thanks Atul.

I have LOG_ARCHIVE_CONFIG enabled . The following is the config I have in init.ora.

ISC.__db_cache_size=641728512
ISC.__java_pool_size=4194304
ISC.__large_pool_size=4194304
ISC.__shared_pool_size=276824064
ISC.__streams_pool_size=0
*.audit_file_dest=’/opt/oracle/admin/ISC/adump’
*.background_dump_dest=’/opt/oracle/admin/ISC/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/ana_data/ISC/control01.ctl’,’/ana_data/ISC/control02.ctl’,’/an
a_data/ISC/control03.ctl’
*.core_dump_dest=’/opt/oracle/admin/ISC/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ISC’
*.db_recovery_file_dest=’/opt/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’PRIMARY’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ISCXDB)’
*.fal_client=’PRIMARY_GSPOSSSOL2′
*.fal_server=’STANDBY_GSPOSSSOL1′
*.job_queue_processes=10
*.log_archive_config=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES
,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY’
*.log_archive_dest_2=’SERVICE=STANDBY
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.open_cursors=300
*.pga_aggregate_target=311427072
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.service_names=’PRIMARY’
*.sga_target=934281216
*.standby_file_management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/opt/oracle/admin/ISC/udump’

Now my doubts are as follows : DB_UNIQUE_NAME should be ISC ? as this is the name of the primary database ? And there is no ‘Primary’ here , so can you pls advice me on what should be the actual config ? Now I tried with DG_CONFIG = (ISC,ISC) , but it did not work as both the names were the same. How do i manage this ?

thanks.

Reply
umair says November 26, 2007

can i use ‘perl adcfgclone.pl
dbTier'(cold backup) while post cloning in DR setup?

Reply
Atul says November 26, 2007

Umair,
If you are cloning from backup of standby database, you have to clone database manually and then run dbTechstack.

If you are cloning from cold backup of primary database then you can run dbTier

Reply
p.srikanth says January 24, 2008

hi atul

i have 11i instance in which i want to create 1 more instance called oracle 10g .
i tried but i unable to create it is showing error like unable to read oraInvetory
i think it is trying to read db tier Inventory
can u help me plz is there any possibility to another oraInventry oracle 10g or it is not possible reply me plz

note:11i insatnce consist 9i i dont want disturb and don’t want to upgrade to 10g. other than this i want to create 10g on 11i instance.
i have space on mount point 67gb in after installing the 11i at same mount point in want to create

Reply
rohitpatel9999 says July 18, 2008

Informative and useful. Thanks.

One problem.

Three db, all with same SID – ORCL.
ORCL -primary db
ORCL -physical standby, read-only for reporting
ORCL -disaster recovery standby (recovery mode)

Because of same SID, unable to configure OID/LDAP – trying to use for configuring for both primary db and physical standby. Also tried with different db_unique_name.

Please give some steps or tnsnames declaration hint to configure OID/LDAP for primary db and physical standby db – both have same SID.

Thanks
Rohit

Reply
Atul says July 18, 2008

Rohit,
I am not clear with what you are trying to achieve

Because of same SID, unable to configure OID/LDAP –
How you are trying to configure OID ? (Are you planning to use this database as OID store or you are planning to register ORCL in OID)

Reply
rohitpatel9999 says July 19, 2008

Atul,

Thanks for helping.

Database instance for OID/LDAP is different – SID for that is LDAPDB.

Need to authenticate users (for primary db as well as read-only standby db) through OID/LDAP – both DBs are on different servers. But SID for primary db and standby db SID is same – ORCL.

Registered primary db as ORCL in OID/LDAP, but unable to register another standby db because it has same SID – ORCL. How to configure another db (same SID) with OID/LDAP. (standby db is on different server).

Thanks.
Rohit.

Reply
Atul says July 19, 2008

You wish to register two database with same SID into OID but they both are trying to sit in same place in DIT (Directory Information Tree) in OID with same DN which is not allowed.

Since these two databases are same one read only and other read/write but on different machines so try to use principle used to register RAC database (I am not sure about how to do this) but check steps to register rac db in OID or check with Oracle Support.

Reply
Atul says July 19, 2008

Rohit,
Check at which location is your database ORCL registered in OID hopefully it should be at
dn: cn=ORCL,cn=OracleContext

In OIDMON
Entry Management >> OracleContext >> ORCL

and check attribute orclnetdescstring

See if you can second node entry in that location

orclnetdescstring: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=firstmachine)(PORT=))(ADDRESS=(PROTOCOL=TCP)(HOST=second_machine_read_only)(PORT=))(LOAD_BALANCE=yes)(FAILOVER=on))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)(failover_mode=(type=select)(method=basic))))

You need to be sure that it does not invalidate your current setup. Not very clean method

Reply
rohitpatel9999 says July 29, 2008

Regarding SID for primary db and standby read-only db:

Version: Oracle Database 10g 10.2.0.4

Some forum question-answers suggest that SID for primary and standby db must be same some say SID for primary and standby db can be different.

If we need to setup primary and standby on same host then SID must be different.

But if we need to setup primary and standby on different host then possibility is – SID can be same or different.

Can we give SID different for primary and standby db (both on different host)?
If yes, any pros and cons? (from recovery or managing point of view)

Thanks for helping.

Reply
sue L says November 5, 2008

I am pretty new to orace admin (like 3 months) and have been set to the task of creating a physical standby database and am getting quite lost after lots and lots of research.

1)I am not clear if Data Guard is a component already existing in the oracle installation already or if it is a separate download? I have EM manager, oracle 11 g.

2) Can I create a physical stnby w/out it?

3) Am I right in thinking that log_dest_1 and Log_dest_2 are having the same data written to them?

4) Which part actually “turns on” the writing of logs to the standby?

any help would be appreciated..

Reply
Atul Kumar says November 5, 2008

1)I am not clear if Data Guard is a component already existing in the oracle installation already or if it is a separate download? I have EM manager, oracle 11 g.

Dataguard is nothing but concept around standby database. Dataguard broker is product (java based tool to graphically configure standby database) but broker is not required to configure dataguard (standby database)

2) Can I create a physical stnby w/out it?
Dataguard is concept for physical standby db.

3) Am I right in thinking that log_dest_1 and Log_dest_2 are having the same data written to them?

Yes

4) Which part actually “turns on” the writing of logs to the standby?

configure log_dest_2 to ship to standby db and then when you put standby database in managed recover mode, archives are applied to standby database automatically

Reply
Sanjeev Nanda says October 30, 2009

hi Atul,
Thanks for the informative guide ,
I do have a question:
How can we compresses archive logs before replicating(for saving bandwidth)
Which process do the log transport from primary standby redolog to Secondary standby redolog ((LGWR OR FAL server process),which process write from standby redolog(Secondary) to orignal redolog.
what is the role of Oracle Streams in Dataguard.

Sanjeev

Reply
Sanjeev Nanda says October 30, 2009

Hi Atul,
As per Rohit
they have Three db,
ORCL -primary db
ORCL -physical standby, read-only for reporting
ORCL -disaster recovery standby (recovery mode)

please let me know the difference in init parameter of all these database.Differnce between last two.

regards
sanjeev

Reply
Umair says January 5, 2010

Hi Atul,

Another nice doc as usual :)

I want guidence wrt R12. Can u plz refer any of your posts or metalink id for the implementation of DG on R12?

Regards,
Umair

Reply
Atul Kumar says January 5, 2010

@ Umair,
Please use metalink note # 452056.1 Business Continuity for Oracle Applications Release 12 on Database Release 10gR2 – Single Instance and RAC

Reply
Add Your Reply