Install Oracle Data Guard on Oracle Database 11GR2 Steps by Steps (physical standby)

This is my post to guide you install and configure Oracle Data Guard with Physical Standby on Oracle Database 11G R2 steps by steps.

In this serries:

Our topology

Assume that we have 2 server, we will install Oracle Data Guard on them. The primary server will be DG9. The standby server will be DG10.

Primary DG9 -------------------> Physical Standby DG10
172.16.23.41                     172.16.23.42
Oracle Linux 6.10                Oracle Linux 6.10
Oracle Database 11GR2            Oracle Database 11GR2

Setup Primary Server

Step 1: Install the database

Install primary server as normal with Oracle Linux 6.10 operating system and Oracle database 11G R2 onto that os.

Now you have a complete database with:

  • IP: 172.16.23.41
  • ORACLE_UNQNAME=DG9
  • ORACLE_SID=DG9

Replace or create you listener.ora file at /u01/app/oracle/product/11.2.0.1/db_1/network/admin with the following:

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dg9.localdomain)(PORT = 1521))
     )
   )
 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = DG9_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)
       (SID_NAME = DG9)
     )
   )
 ADR_BASE_LISTENER = /u01/app/oracle

You should backup your original listener.ora file before doing.

Step 2: Enable archivelog mode

Run this command to check if the database if in archivelog mode or not:

 SELECT log_mode FROM v$database; 

Switch to archivelog mode if not using:

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

Enable forced logging

 ALTER DATABASE FORCE LOGGING; 

Step3: Setup paramters

Set LOG_ARCHIVE_CONFIG

Check DB_NAME and DB_UNIQUE_NAME:

SQL> show parameter _name;
 NAME                                 TYPE        VALUE
 
 db_file_name_convert                 string
 db_name                              string      DG9
 db_unique_name                       string      DG9
 global_names                         boolean     FALSE
 instance_name                        string      DG9
 lock_name_space                      string
 log_file_name_convert                string
 service_names                        string      DG9

Note that the primary and standby must have the same DB_NAME but different DB_UNIQUE_NAME.

In this case, they will have DB_NAME=DG9 and DB_UNIQUE_NAME=DG9 and DG10 simultaneously. We have to set LOG_ARCHIVE_CONFIG using DB_UNIQUE_NAME.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG9,DG10)'; 

Set remote log destinations

Here we’ll use FRA (Fast Recovery Area) but you can use any other location if needed.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

The SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

Set LOG_ARCHIVE_FORMAT, LOG_ARCHIVE_MAX_PROCESSES and REMOTE_LOGIN_PASSWORDFILE

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; 

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; 

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; 

Set FAL_SERVER, STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET FAL_SERVER=DG10;
 
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DG10','DG9' SCOPE=SPFILE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

Edit tnsnames.ora

Edit /etc/hosts file to:

172.16.23.41 dg9.localdomain dg9
172.16.23.42 dg10.localdomain dg10

Edit tnsnames.ora at $ORACLE_HOME/network/admin/tnsnames.ora:

DG9 =   
  (DESCRIPTION =     
    (ADDRESS_LIST =       
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg9)(PORT = 1521))     
    )     
    (CONNECT_DATA =  (SERVICE_NAME = DG9)     
  )   
) 


DG10 =   
  (DESCRIPTION =     
    (ADDRESS_LIST =       
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg10)(PORT = 1521)
    )     
  )     
  (CONNECT_DATA =       (SERVICE_NAME = DG10)     
  )   
)

Backup primary database

$ rman target=/ 
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; 

Create standby controlfile and pfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/dg10.ctl'; 

CREATE PFILE='/tmp/initDG10.ora' FROM SPFILE; 

Edit the newly created PFILE to:

*.db_unique_name='DG10' 
*.fal_server='DG9' 
*.log_archive_dest_2='SERVICE=DG9 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG9' 

That’s all for now you have to do with primary. Now we need to setup physical standby DG10.

Setup Standby Server

All steps below run on Standby server.

Install physical standby server with Oracle Linux 6.10 operating system described here.

Perform all Step 1 to Step 5 in this tutorial to configure Oracle database with following parameter:

  • IP: 172.16.23.42
  • /etc/hosts: 172.16.23.42 dg10.localdomain dg10
  • /etc/hostname: dg10
  • ORACLE_UNQNAME=DG10
  • ORACLE_SID=DG9

Step 6: Run Installer and select options

At first step, if you have MOS (My Oracle Support) then fill in. If you don’t, uncheck and press next.

oraerr.com-install-oracle-data-guard-on-oracle-11g
oraerr.com-install-oracle-data-guard-on-oracle-11g

Select install database software only.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3

Choose Single Instance

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5

Choose Enterprise Edition

oraerr.com-install-oracle-database-on-ol6.10-setup-02
oraerr.com-install-oracle-database-on-ol6.10-setup-02

Choose location

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10

Choose Ignore All and Next/Finish. Now waiting for Oracle to install software on your server.

oraerr.com-install-oracle-data-guard-on-oracle-11g-02
oraerr.com-install-oracle-data-guard-on-oracle-11g-02

Run the scripts and finish the installation as normal. As installed software only, you’ll have an idle instance (mounted by not open):

$ sqlplus / as sysdba
 SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 4 14:46:59 2019
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 Connected to an idle instance.
 SQL>

Step 7: Standby server configuration

Create or replace your listerner.ora with following:

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dg10.localdomain)(PORT = 1521))
     )
   )
 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = DG10_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)
       (SID_NAME = DG9)
     )
   )
 ADR_BASE_LISTENER = /u01/app/oracle

Edit the /etc/hosts file to:

172.16.23.41 dg9.localdomain dg9
172.16.23.42 dg10.localdomain dg10 

Create necessary folders on standby

$ mkdir -p /u01/app/oracle/oradata/DG9 
$ mkdir -p /u01/app/oracle/flash_recovery_area/DG9 
$ mkdir -p /u01/app/oracle/admin/DG9/adump 

Copy controlfile file from primary to standby:

scp oracle@dg9:/tmp/dg10.ctl /u01/app/oracle/oradata/DG9/control01.ctl 

cp /u01/app/oracle/oradata/DG9/control01.ctl /u01/app/oracle/flash_recovery_area/DG9/control02.ctl 

Copy archivelogs and backup

scp -r oracle@dg9:/u01/app/oracle/flash_recovery_area/DG9/archivelog/ /u01/app/oracle/flash_recovery_area/DG9

scp -r oracle@dg9:/u01/app/oracle/flash_recovery_area/DG9/backupset/ /u01/app/oracle/flash_recovery_area/DG9

Copy remote login passsword file

scp oracle@dg9:$ORACLE_HOME/dbs/orapwDG9 $ORACLE_HOME/dbs 

Copy pfile

scp oracle@dg9:/tmp/initDG10.ora /tmp/initDG10.ora 

Start listener

lsnrctl start

Restore backup on standby

Set if ORACLE_SID not set yet

export ORACLE_SID=DB11G 

Create SPFILE from PFILE

CREATE SPFILE FROM PFILE='/tmp/initDG10.ora'; 

Restore backup files

rman target /
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create online redo logs on standby (should be the same with primary, by default, Oracle database creates 3 redo log file (redo01.log, …) and 50MB each.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo01.log') SIZE 50M; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo02.log') SIZE 50M; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo03.log') SIZE 50M; 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

Create standby redo log

Run on BOTH primary and standby server

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo01.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo02.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo03.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo04.log') SIZE 50M; 

The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs.

Step 8: Start apply process

Background redo apply. Control is returned to the session once the apply process is started. Run on STANDBY server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

If you need to cancel apply process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

After you start the apply process, the primary database will continously backup to standby. The standby database is mounted but not opened.

Common errors during startup apply process

ORA-01153: an incompatible media recovery is active

How to fix:

Cancel and re-apply the process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 Database altered.
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
 Database altered.

ORA-16826: apply service state is inconsistent with the DelayMins property

How to fix:

This Problem can only happen if Managed Recovery is started manually which should not be done at all if there is an active Data Guard Broker configuration.

Typically the Data Guard Broker should start Managed Recovery—>should not apply process manually if there’s broker.

Solution is cancel the manual apply and re-enable via broker.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 Database altered.
SQL> ALTER SYSTEM SET dg_broker_start=true;
 System altered.

DGMGRL> edit database dg10 set state=apply-on;
 Succeeded.

Enable broker

Run this command to enable the broker

ALTER SYSTEM SET dg_broker_start=true;

Now you can access DGMGRL with following command:

$ dgmgrl sys/Password1@dg9

Create configuration

DGMGRL> CREATE CONFIGURATION mydgconfig AS PRIMARY DATABASE IS DG9 CONNECT IDENTIFIER IS DG9; 

Add standby database (run on PRIMARY):

DGMGRL> ADD DATABASE DG10 AS CONNECT IDENTIFIER IS DG10 MAINTAINED AS PHYSICAL; 

Enable the configuration

DGMGRL> ENABLE CONFIGURATION; 

Check the sync with show configuration command

 DGMGRL> SHOW CONFIGURATION;

 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg9  - Primary database
     dg10 - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS

Step 9: Final check and configures

Check the protection mode, by default is Maximum performance.

SELECT protection_mode FROM v$database; 
  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

Protection mode switching

 -- Maximum Availability. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; 

-- Maximum Performance. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 

-- Maximum Protection. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG100 AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; 
ALTER DATABASE OPEN; 

Oracle Data Guard Database Switchover

Switch primary to standby

 -- Convert primary database to standby 
CONNECT / AS SYSDBA 
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; 

-- Shutdown primary database 
SHUTDOWN IMMEDIATE; 

-- Mount old primary database as standby database 
STARTUP NOMOUNT; 
ALTER DATABASE MOUNT STANDBY DATABASE; 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

Switch standby to primary

 -- Convert standby database to primary 
CONNECT / AS SYSDBA 
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 

-- Shutdown standby database 
SHUTDOWN IMMEDIATE; 

-- Open old standby database as primary 
STARTUP; 

Oracle Data Guard Failover

if the primary server down.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 
ALTER DATABASE ACTIVATE STANDBY DATABASE;