Configure Dataguard Physical Standby Database Setup in Oracle 11g/12c

Search
Generic filters
Exact matches only
Filter by Custom Post Type

Steps To Configure Dataguard or Standby Database

Dataguard is one of the primary component of Oracle’s Maximum Available Architecture ( MAA). Dataguard ensures high availability, data protection, and disaster recovery for enterprise data. The dataguard  configuration consists  of Primary database and one or more standby database . In this document we cover the steps to configure dataguard in Oracle 11g and Oracle 12c

Server and Database Configuration Information

 

Server Name OS Version DB Name Instance Name  Db Version Database Role TNS Entry
Primary wysheid12crac56 OEL 6.5 prim prim 12.1 Primary primtns
Standby wysheid12crac57 OEl 6.5 stand stand 12.1 Standby standtns

The following are the steps to configure dataguard  with a primary database and a standby database

Prepare Primary Database 

Enable Archivelog Mode

 Dataguard depends on the redo generated on the primary database . So its mandatory to have the primary database running on the Archive log mode.

Check if the primary database is in the archivelog mode

SQL > select log_mode from v$database

ARCHIVELOG   --> Indicates that the database is in Archivelog Mode
NOARCHIVELOG --> Indicates that the database is in Noarchivelog Mode

 If the database is in Non Archivelog Mode , use the following Steps to convert into Archivelog Mode

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

Enable Force Logging

SQL> alter database force logging
SQL> select force_logging from v$database;

Configure Initialization Parameters

The following parameters to be changed . Please change the values as per your set up  . Please take the following as an example

 DB_NAME=prim
 DB_UNIQUE_NAME=prim
 SERVICE_NAMES=prim
 LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)‘
 DB_FILE_NAME_CONVERT= ‘stand’,’prim
 LOG_FILE_NAME_CONVERT= ‘stand’,’prim
 LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
 LOG_ARCHIVE_DEST_1= 'LOCATION=/oracle/prim/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
 LOG_ARCHIVE_DEST_2= 'SERVICE=standtns VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand'
 LOG_ARCHIVE_DEST_STATE_1=ENABLE
 LOG_ARCHIVE_DEST_STATE_2=ENABLE
 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 STANDBY_FILE_MANAGEMENT=AUTO
 INSTANCE_NAME=prim
 FAL_SERVER=stand
 FAL_CLIENT=prim
 

Run the below Commands on the primary to make change on the Spfile

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='locatin=/oracle/prim/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standtns NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SQl> ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='prim','stand' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='prim','stand' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Create PFILE from SPFILE

SQL> Create pfile from spfile ;

This pfile can be modified on the standby as per the requirement.

Create the  TNSNAMES for Both Primary and Standby

Please note the TNS entry should be created on both primary and standby. You can create it using ‘netca’ or using any text editor.

primtns =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST =wysheid12crac56)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = prim)
    )
    )

standtns =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = wysheid12crac57)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = stand)
   )
   )

Create backup of Primary Database

RMAN > connect target /
RMAN >  backup database plus archive log;

Create standby Controlfile

 RMAN > backup current controlfile for standby ;

Copy the files to standby

Copy the following file to standby server

1)Backup pieces. Make sure to copy the backup pieces to the same location as that of primary
2)Password file. Rename the password file on the standby with naming format orapw<sid>
 eg :$ scp orapwdprim [email protected]:$ORACLE_HOME/dbs/orapwdstand

3)tnsnames.ora file which contains the entry for primary and standby database instance.

 

Prepare Standby  Database Instance

Rename the password file

The password file , which was copied from the primary has to renamed on standby . Please note: We are not creating a new password file on standby .

$ cd $ORACLE_HOME/dbs
$ cp orapwprim  orapwstand    ---> where prim and stand are the SID of the primary and
                                   standby . Please note: the password file have the
                                   naming format orapw<SID>

Sample Parameter File For Standby Instance

Update the parameter file copied from primary as per that of standby . Below is a sample based on the example in the article

DB_NAME=prim 
DB_UNIQUE_NAME=stand
SERVICE_NAMES=stand 
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)‘ 
DB_FILE_NAME_CONVERT= ‘prim’,’stand
LOG_FILE_NAME_CONVERT= ‘prim’,’stand 
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc 
LOG_ARCHIVE_DEST_1= 'LOCATION=/oracle/stand/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand' 
LOG_ARCHIVE_DEST_2= 'SERVICE=primtns VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
STANDBY_FILE_MANAGEMENT=AUTO 
INSTANCE_NAME=stand 
FAL_SERVER=primtns

Startup standby instance

Modify the parameter file for standby instance and startup the standby instance in nomount stage. We will call this instance as Auxiliary Instance while RMAN duplication.

SQL> startup nomount;

Please you the below as sample and make the changes as per your configuration

Create standby using the RMAN Duplicate command

Set the OS variables for standby instance

Startup the standby to the nomount stage

RMAN > connect auxiliary /

RMAN >  connect target sys/[email protected]

RMAN> run {

allocate auxiliary channel c1   device type disk;

allocate channel c2  device type disk;

duplicate target database for standby nofilenamecheck dorecover;

}

Create standby redolog files

Take the below command as sample and create the standby logs as per your configuration.The number of standby redo logfiles should  one greater that normal redologfiles , for each thread. For example , if your primary database has three redo log groups, you must create 4 standby redo log groups on standby. In this example execute the command 4 times, with different file name path

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/wysheid/oracle/oradata/standb/standby_redo03.log') SIZE 50M;

Startup the media recovery Process

$ SQL > alter database recover managed standby database disconnect from session;
$ ps -eaf|grep mrp       #   you can see the media recovery process up and running

Check if data syncing taking place

  • Do a log switch from primary
sql > alter database switch logfile
  • Verify if the redo logs applied on standby

 

On Primary
SQL> select thread#, max(sequence#) from v$archived_log where archived='YES'   group by thread#
On Standby
SQL> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#

If the primary and standby is in SYNC , both command will result the same value.

 

Hope this helps,

Happy Learning

Wysheid Team

 

 

Liked this Article.?

We have lot more interesting posts like this!  Join more than 5000 subscribers who stays ahead on technology.

By entering your email, you agree to the Terms and Conditions and Privacy Policy and cookies usage

You Make like the below posts

About The Author

Share on

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest

Leave a comment

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

Can't Find What You Are Looking For?.

Please enter the details below. We will try our best for you :-)

We have more amazing posts

Try searching here

lick edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

We assure our best to meet up to your expectations

Your Feedback will help us

Glad That You Liked It

Your Feedback will help us to improve