Configure Oracle Data Guard Part II

This is in continuation of Oracle Dataguard configuration part I  which I posted long back and forgot to update next one.

   One of my friend was looking for steps so I thought of finishing this (If you think I missed any post just leave comment and I’ll try to post on that topic)

Ensure that you have gone through Part I and finished following task
1. Primary Database is configured in archivelog mode
2. Configure LOG_ARCHIVE_DEST_2 on primary database
3. Create TNS alias (PROD_remote1) in tnsnames.ora on primary site
4. Install Oracle Database on Standby Site (Use same directory structure as on primary site)
* Dataguard allows you to configure primary & standby database with different directory structure but for simplicity this doc assume they are with same directory structure

Here are step by step Oracle dataguard cnfiguration in detail once again  

1. Make sure your production database is in force logging.

 Query from v$database  if not set change to force logging by running
sqlplus> alter database force logging;

2. Set Primary Database in archivelog  mode
If log mode is not “Archive Mode”, and automatic archival is not “Enabled”, you must set these parameters in your init<SID>.ora file:
log_archive_start = TRUE
log_archive_format = <format for database, using %s, %t>
log_archive_dest_1 = ‘LOCATION=<primary DB archive log dest> MANDATORY’
log_archive_dest_state_1 = enable

For these parameters to take effect, you must shut down your database, then re-start it in this manner:

sqlplus>  startup mount
sqlplus> alter database archivelog
sqlplus> alter database open

3. Create standby redo logs
Query v$logfile ( If there is no standby redo log, create equal number & of same size as of online redo logs )
sqlplus> alter database add standby logfile group <> (‘<full path for first member>’,’<full path for second member’) size <> ;
4. Define log_archive_dest_state_2 in primary database
open init<SID>.ora  in primary server & add remote_archive_enable = true
log_archive_dest_2 = ‘SERVICE=<standby_service_name> OPTIONAL REOPEN=60 LGWR AFFIRM SYNC’
log_archive_dest_state_2 = enable
If log_archive_dest is specified in your init.ora file, you must comment out or remove that parameter.5. Add entries to your tnsnames.ora and listener.ora files for the standby database.
The tnsnames.ora file is found in $TNS_ADMIN
<standby_service_name> = (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)
   (PORT=<same port as production>)
   (HOST=<standby host name>))
   (CONNECT_DATA=(SID=<same SID as production>)))
The listener.ora file is also found in $TNS_ADMIN
  
<standby_service_name> =
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
   (PORT=<same port as production>)
   (HOST=<standby host name>)))
   SID_LIST_<standby_service_name>=(SID_LIST=
   (SID_DESC=(SID_NAME=<same SID as production>)
   (ORACLE_HOME=<path to database home>)))

Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files. 
             
6. Create the secondary database standby control file.
From SQL*Plus as sys or system, on the primary site environment, create a standby control file:

SQL> alter system archive log current;
SQL> alter database create standby controlfile as ‘<directory for control files>/stdbyctl.dbf’

Note the standby control file must have a name different from the primary database control  file.

7. Restore the primary database to the database server at the secondary site
This step assumes that you have installed database on standby site with same directory structure, you can delete datafiles from database which is going to become standby site (or replace them from primary site as given below).  

7.1 Shutdown your primary database
7.2
Copy your datafiles & online redologs including standby redologs


8. Copy the standby control file to the redundant control file directories.

On the standby database server, make redundant copies of the standby control file to the appropriate directories ( standby control file which you created in step 6 above ).  Put them in the same directories your redundant production control files are kept.  Log in as the database owner and use operating system utilities to make the copies.

9. Adjust the init<SID>.ora file for the standby database.

On the standby database server, edit your init<SID>.ora file to point to the standby control files by changing the CONTROL_FILES entry and set up your standby archive destination:
CONTROL_FILES = <directory 1 for control files>/stdbyctl.dbf, <directory 2 for control files>/stdbyctl.dbf, etc.
log_archive_start             = true      # if you want automatic archiving
FAL_SERVER                    = <PRIMARY_SID>
FAL_CLIENT                    =  <TNS_ALIAS_STANDBY>  (Defined above)
STANDBY_ARCHIVE_DEST          = /prodarch/oracle/prodarch
STANDBY_FILE_MANAGEMENT       = AUTO

10. Add entries to your tnsnames.ora and listener.ora files for the standby database on standby database.

<standby_service_name> = (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)
   (PORT=<same port as production>)
   (HOST=<standby host name>))
   (CONNECT_DATA=(SID=<same SID as production>)))

The listener.ora file is also found in $TNS_ADMIN
<standby_service_name> =
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
   (PORT=<same port as production>)
   (HOST=<standby host name>)))
SID_LIST_<standby_service_name>=(SID_LIST=
   (SID_DESC=(SID_NAME=<same SID as production>)
   (ORACLE_HOME=<path to database home>)))

Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files.

11. Start the listener on the secondary database server, for the standby database.
On the standby server, as the database owner, run the following operating system command :

$ lsnrctl start <standby_service_name


12. Mount the standby database
On the standby database server, from sqlplus
SQLPLUS> startup nomount
SQLPLUS > alter database mount standby database;


13. Place the standby database in managed recovery mode.

SQLPLUS> alter database recover managed standby database disconnect;

14. Modify the standby database listener.ora file  ( If this parameter is not set )

Change the value of the HOST parameter in the production service entry of the listener.ora file on the standby database server to point to the secondary database server.

<production_service_name> =
            (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
    (PORT=<same port as production>)
    (HOST=<standby host name>)))

SID_LIST_<production_service_name>=(SID_LIST=
    (SID_DESC=(SID_NAME=<same SID as production>)
    (ORACLE_HOME=<path to database home>)))


15. Check your protection mode .

Check from v$database ( on standby database )
If want to change to maximum availability mode by running

Sqlplus > alter database set standby database to maximize { PROTECTION | AVAILABILITY | PERFORMANCE }

Once you have set it to maximum availability  mode , you can start your primary database .

Check your alert<SID>.log for any errors

Related

10g R2 Dataguard Setup Doc from Oracle

About the Author Atul Kumar

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

follow me on:

Leave a Comment:

12 comments
PhaniK says May 21, 2008

Hi Atul,

Small change FAL_SERVER in place of AL_SERVER.

Regards,
Phani.K

Reply
Atul says May 22, 2008

Thanks Phani for pointing this out (corrected now)

for others
FAL stands for Fetch Archive Log

Reply
sahil says October 22, 2008

will the dbnames for standby database and primary databse will be same or it can be different

Reply
sahil says October 22, 2008

and also the service name and the listener names should be same or they can be different.

Reply
sahil says October 22, 2008

if the dircory structure is different what parameters need to be changed.

Reply
seethu says September 30, 2009

Hi,

If we r using log_archive_config parameter in
initialization files means we need to specify
fal_server and fal_client r these things
optional.

Reply
Sanjeev Nanda says October 30, 2009

hi Atul,

How to set use of checksum to ensure Oracle logs have transferred correctly.

regards
sanjeev

Reply
Sanjeev Nanda says October 30, 2009

Hi Atul,

How to uses checksum to ensure Oracle logs have transferred correctly.

regards
sanjeev

Reply
Kshitij says December 14, 2011

Dear Atul,

on standby server, many archivelogs are missing.
How to ship all missing logs.

thanks
kshitij

Reply
Atul Kumar says December 17, 2011

@ Kshitij
FAL – Fetch Archive Log should pull missing archives automatically. If for some reason it can’t then copy them manually on standby server and apply them manually.

Reply
Susmit says January 5, 2012

Hi Atul,

What i did while setting up the secondary is,
i installed an oracle s/w on the secondary node.(No database) and then after step 7 i copied datafiles,pfile,control file from primary to secondary. And then configured LISTENAR on the secondary and started the secondary database and then make it open in readonly mode(for 11 g only).
Is this a correct approch??

Reply
Atul Kumar says January 5, 2012

@ Susmit,
Yes there is nothing wrong with this approach.

Just ensure that primary and standby are in sync .

Add database in /etc/oratab (/var/opt/oracle/oratab for Solaris)

Reply
Add Your Reply

Not found