top of page
Search
  • Writer's pictureArun Kumar
    • 6 min read

Oracle Data Guard Physical Standby Configuration

There are various steps in which you can configure physical standby database. We need to make several changes to the primary database before we can even setup the standby database. In this article, we will set up physical standby on Oracle Linux 7.6 server.

This article applies to Oracle 12c R2 database version

Physical Standby Configuration Overview


Primary details

SID: ip7
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1
Host Name: srv1.dbagenesis.com

Standby details

SID: ip7
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1
Host Name: srv2.dbagenesis.com

Assumption: we assume that primary server has a database (SID=ip7) up and running. The standby database has Oracle 12cR2 installation done in the same oracle home location as primary.



Primary database changes


Primary must run in archive log mode. Check the archive log mode

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

If it is not running in archive log mode, then enable it

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

Enable force logging on primary: In oracle, users can restrict redo generation for SQL by using NOLOGGING clause. This NOLOGGING transaction will be a problem for physical standby. Hence, we force logging so even user uses NOLOGGING clause, every SQL will be logged on to redo

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

Standby file management: We need to make sure whenever we add/drop datafile in primary database, those files are also added / dropped on standby

SQL> alter system set standby_file_management = 'AUTO';

Create standby log files: You must create standby log files on primary. These files are used by a standby database to store redo it receives from primary database.


Our primary may become standby later and we would need them, so better to create it. First check the current log groups

SQL> select GROUP#, THREAD#, bytes/1024/1024, MEMBERS, STATUS from v$log;

    GROUP#    THREAD# BYTES/1024/1024    MEMBERS STATUS
---------- ---------- --------------- ---------- ----------------
         1          1             200          1 INACTIVE
         2          1             200          1 CURRENT
         3          1             200          1 INACTIVE

SQL> select member from v$logfile;

MEMBER
---------------------------------------------------
/u01/data/db_files/ip7/redo03.log
/u01/data/db_files/ip7/redo02.log
/u01/data/db_files/ip7/redo01.log

Add the standby logfiles, make sure group number should be from a different series like in this case we choose to start with 11 and above. This helps in easy differentiation.


Make sure to keep the thread# and logfile size exactly same. Oracle also recommends to always create n+1 standby log files. Where n is the total number of logfiles

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 '/u01/data/db_files/ip7/stb_redo1.log' SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 '/u01/data/db_files/ip7/stb_redo2.log' SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 '/u01/data/db_files/ip7/stb_redo3.log' SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 '/u01/data/db_files/ip7/stb_redo4.log' SIZE 200M;

Check the standby log files via below query

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Enable flashback on primary: Flashback database is highly recommended because in case of failover, you need not re-create primary database from scratch

SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;
SQL> select flashback_on from v$database;

If flashback parameters are not set properly, use below commands

SQL> show parameter recovery;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;

Check DB Unique name parameter on primary: Make sure your primary database has DB_UNIQUE_NAME parameter set for consistency. If it’s not set properly, use ALTER SYSTEM SET command

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_name                              string      ip7

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_unique_name                       string      ip7

Create password file for standby: This is needed for cloning purpose. Even if there is one password file in $ORACLE_HOME/dbs location, create a new one with standby SID

$ orapwd file=$ORACLE_HOME/dbs/orapwip7 entries=10 force=y

$ scp orapwip7 oracle@srv2:$ORACLE_HOME/dbs


Configure network


Use below tns entries and put them under ORACLE user HOME/network/admin/tnsnames.ora. Change host as per your environment and execute on both primary and standby.


Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.

vi $ORACLE_HOME/network/admin/tnsnames.ora

ip7 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ip7)
    )
  )

ip7_stb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ip7)
    )
  )

Configure listener on primary database. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database.

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ip7_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = ip7)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Configure listener on standby. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database.

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ip7_stb_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
      (SID_NAME = ip7)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Once the listener.ora changes are in place, restart the listener on both servers

lsnrctl stop
lsnrctl start


Configure redo transport


Note: if you plan to use Oracle Data Guard broker, then you can skip this section “configure redo transport” and jump to “Build Standby” section.

Configure redo transport from primary to standby:  The below statement says that if the current database is in primary role, then transport logs to standby. We need to change service and db_unique_name for same parameter on standby server

On Primary Server
=================
SQL> alter system set log_archive_dest_2 = 'service=ip7_stb async
valid_for=(online_logfiles,primary_role) db_unique_name=ip7_stb';

Setup FAL (Fetch Archive Log) server: This parameters tell the primary as to where it will get archives from (FAL Server=proddb_st)

On Primary Server
=================
SQL> alter system set fal_server = 'ip7_stb';

Setup Data Guard configuration on primary: This parameter will let primary database know which databases are in data guard configuration

On Primary Server
=================
SQL> alter system set log_archive_config = 'dg_config=(ip7,ip7_stb)';


Build standby


On standby server, create parameter file with below contents

On standby server
=================

vi /tmp/initip7.ora
*.db_name='ip7'

Create pfile on primary, open it and create the necessary directories on the standby server

On Primary Server
=================
SQL> create pfile from spfile;
exit

$ cd $ORACLE_HOME/dbs
$ cat initip7.ora

On Standby Server
=================
on standby, create directories as you find in the initip7.ora file

mkdir -p /u01/app/oracle/admin/ip7/adump
mkdir -p /u01/data/db_files/ip7
mkdir -p /u01/FRA/ip7

Start the auxiliary instance on the standby server by starting it using the temporary init.ora file

On Standby Server
=================

$ export ORACLE_SID=ip7
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/tmp/initip7.ora';
SQL> exit;

--you must exit from sqlplus, else cloning will fail

Duplicate primary database via RMAN: In this step, we will use RMAN to duplicate primary database for our standby database.


On primary, connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication or the database cloning will fail

On primary server
=================

$ rman TARGET sys@ip7

RMAN> connect auxiliary sys@ip7_stb

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='ip7_stb' COMMENT 'Is standby' NOFILENAMECHECK;

Once cloning is done, you should see below at RMAN prompt

Finished Duplicate Db at 07-DEC-2015

Set Standby Parameters: We need to make some changes to standby parameters that will enable log shipping to the current primary (ip7) when a switchover happens

On Standby Server
=================
SQL> create pfile from spfile;

$ cd $ORACLE_HOME/dbs
$ vi initip7.ora

--change
*.fal_server='ip7_stb'

--to
*.fal_server='ip7'

--change
*.log_archive_dest_2='service=ip7_stb async
valid_for=(online_logfiles,primary_role) db_unique_name=ip7_stb'

--to
*.log_archive_dest_2='service=ip7 async
valid_for=(online_logfiles,primary_role) db_unique_name=ip7'

Now re-create spfile for standby using the pfile you just modified

On Standby Server
=================
SQL> create spfile from pfile;
SQL> shut immediate;
SQL> startup mount;
SQL> show parameter spfile;

Enable flashback on standby: As we know the importance of flashback in data guard, we must enable it on standby as well

On Standby Server
=================
SQL> alter database flashback on;


Verify standby configuration


Once MRP is started, we must verify if our setup is working fine

On standby:
===========
--start MRP command
SQL> alter database recover managed standby database disconnect;

On both primary & standby:
==========================
set lines 999;
select * from v$dataguard_status order by timestamp;

select dest_id, status, destination, error from v$archive_dest where dest_id<=2;

IF you see any ORA error like ORA-16058, do this on primary:
============================================================
SQL> alter system set log_archive_dest_state_2='DEFER';
SQL> alter system set log_archive_dest_state_2='ENABLE';
SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2;

On primary:
===========
select sequence#, first_time, next_time, applied, archived from v$archived_log where name = 'ip7_stb' order by first_time;
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
archive log list;

On standby:
===========
select process, status, sequence# from v$managed_standby;
select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#;

Configure Archive deletion policy: We must set this policy in order to prevent accidental deletion of archive logs on primary database

On Primary:
===========
rman target / 
configure archivelog deletion policy to applied on all standby;


23,191 views

Recent Posts

See All
bottom of page