Oracle 11g Installation With ASM and Data Guard Setup

In this article we will look at Oracle 11g installation with ASM storage and also setup physical standby on ASM.

We will be following below steps for our configuration:

  • Setup Primary Server
  • Setup Standby Server

Setup Primary Server

Let us create a virtual machine, install Oracle GRID (which provides us ASM) and then install oracle 11g software.

Install OEL 6.5 on VirtualBox

  1. Create new virtual machine

2. Set 4 GB RAM for your machine

3. Choose Create a virtual disk now and click on created

4. Select VDI and click Next

5. Select Dynamically allocated and click next

6. Give 80 GB HD space to your virtual machine and click on create

7. Right click on virtual machine and open the settings > System > disable floppy and bring up Optical option

8. Select storage option and load OEL 6.5 iso image into the CD drive

9. Select network > Adapter 1 > Attached to > Bridged Adapter

10. Start virtual machine and install OEL 6.5

11. Skip media test

12. Click next to continue

13. Go with default language > click next

14. Go with default keyboard type and click on next

15. Select Basic Storage Devices > Click Next

16. Select Yes, Discard and data

17. Give a hostname to your machine prod.oraclgenesis.com

18. Click on configure network and assign a static IP

19. Check Connect automatically option and assign Ip address as per your environment

20. Click next and select your timezone

21. Give a root password

22. Select create custom layout and click next

23. Select Free disk space and click on create

24. Choose standard partition and click Create

25. Create boot partition with 512 MB size

26. Once again choose free disk space and click on create

27. Select file system type as Swap and give 15 GB size (Approx 15000 MB)

28. Again select free space and click on create

29. Give mount point as root (/) and chose option Fill to maximum allowable size

30. Click next and chose Forma option

31. Select Write changes to disk

32. No changes on below screen, just click next

33. Choose customize now and just click next

34. From the list, select packages as below:

Base System:

  • Base
  • Client management tools
  • Compatibility libraries
  • Hardware monitoring utilities
  • Large Systems Performance
  • Network file system client
  • Performance Tools
  • Perl Support

Servers:

  • Server Platform
  • System administration tools
  • Desktops
  • Desktop
  • Desktop Platform
  • Fonts
  • General Purpose Desktop
  • Graphical Administration Tools
  • Input Methods
  • X Window System

Development:

  • Additional Development
  • Development Tools

Applications:

  • Internet Browser

35. Click next and OEL installation will begin

36. Once done, click on Reboot

After reboot, there are few post installation steps. Click on Forward

Accept the license and click on Forward

Choose No and click on Forward

Select No thanks and proceed

Click forward

Do not create any user at this stage, click forward

Choose Yes

Check the Date and Time settings and click on forward

Nothing to choose under Kdump, click forward

OEL 6.5 installation is completed.

Oracle Installation Prerequisites

Connect to prod server virtual machine via putty and put below details under /etc/hosts file. Make sure to change hostname and IP address as per your machine. Only put this info it this not there in /etc/hosts file

Check if your machine is able to speak to internet. You can ping yahoo.com. Then install below package to perform all oracle installation pre-requisites

yum -y install oracle-rdbms*

Change password for oracle user

passwd oracle

Add grid user and update password

useradd -u 54323 -g oinstall -G dba grid

passwd grid

Create necessary directories

mkdir -p /u01/app/oracle
mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
chown -R grid:oinstall /u01
chownoracle:oinstall /u01/app/oracle
chmod -R 775 /u01
mkdir -p /u02
chown -R oracle:oinstall /u02
chmod -R 775 /u02

Disable firewall

/etc/rc.d/init.d/iptables status
/etc/rc.d/init.d/iptables stop
service iptables stop
chkconfigiptables off
echo 0 > /selinux/enforce

Download Oracle 11.2.0.4 gird and database binaries. Copy it to /u02 location

Switch to Oracle user and edit the bash profile

su - oracle
mv .bash_profile .bash_profile_orig
vi .bash_profile

Put below under the bash profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=proddb
export ORACLE_UNQNAME=proddb
export JAVA_HOME=/usr/bin/java
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_TERM=xterm
export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
export TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

Switch to grid user and edit the bash profile

su - grid
mv .bash_profile .bash_profile_orig
vi .bash_profile

Put below under the bash profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=+ASM
export JAVA_HOME=/usr/bin/java
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/11.2.0/grid
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

Configure ASM Disks

Our goal is to setup primary database on ASM disks. Let us proceed and configure ASM disks.

Install ASM packages

yum -y install oracleasm*
yum -y install kmod-oracleasm

Configure ASM

[[email protected] ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

Load kernel module

[[email protected] ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm

Shutdown the virtual machine and Add asm disk to virtual machine of 110 GB size. Go to virtual machine settings > storage > Controller SATA > Add new disk

Select create new disk

Select VDI and click next

Select Fixed size and click next

Give 110 GB size and click on create

It will take some time to create the disk. Once created, start the virtual machine. Check the new added disk is listed via fdisk -l command

Create three partitions on /dev/sdb disk to be used as ASM disks. Use below commands

# fdisk /dev/sdb
n
p
1
<enter>
+50G

n
P
2
<enter>
+50G

n
p
3
<enter>
<enter>

w

w will save the partitions. Check the partitions under /dev/sdb

Create asm disks. Each partition will be used as one disk inside a disk group

oracleasm createdisk DATA1 /dev/sdb1
oracleasm createdisk FRA1 /dev/sdb2
oracleasm createdisk CRS1 /dev/sdb3

Reboot system.

Install Grid Software

Switch to grid user, unzip the grid software under /u02

su - grid
cd /u02
unzip p13390677_112040_Linux-x86-64_3of7.zip

cd grid directory and run the installer

cd grid
./runInstaller

Follow the screens to install oracle grid software

Check the resources status

ps -ef|greppmon
crsctl status resource -t

Create ASM Diskgroups

As grid user, start asmca utility. Type asmca at command prompt

Click on Create. Give a name to your DATA disk group. This disk group will hold database files. Then click on OK

Install Oracle Database Software

Switch to oracle user, unzip oracle software under /u02

su - oracle
cd /u02
unzip p13390677_112040_Linux-x86-64_1of7_DB1.zip
unzip p13390677_112040_Linux-x86-64_2of7_DB2.zip
cd database
./runInstaller

Follow the screens to install oracle database software

Create Primary Database (DBCA)

Initiate dbca and follow below screens

Change the control files parameter to 

("+DATA/{DB_UNIQUE_NAME}/control1.ctl","+FRA/{DB_UNIQUE_NAME}/control2.ctl")

Check the database pmon status at OS level

Setup Standby Server

Now that we have successfully installed Oracle 11g on ASM, we will proceed with setting up standby server.

Install OEL 6.5 on Virtualbox

  1. Create new virtual machine

2. Set 4 GB RAM for your machine

3. Choose Create a virtual disk now and click on created

4. Select VDI and click Next

5. Select Dynamically allocated and click next

6. Give 80 GB HD space to your virtual machine and click on create

7. Right click on virtual machine and open the settings > System > disable floppy and bring up Optical option

8. Select storage option and load OEL 6.5 iso image into the CD drive

9. Select network > Adapter 1 > Attached to > Bridged Adapter

10. Start virtual machine and install OEL 6.5

11. Skip media test

12. Click next to continue

13. Go with default language > click next

14. Go with default keyboard type and click on next

15. Select Basic Storage Devices > Click Next

16. Select Yes, Discard and data

17. Give a hostname to your machine. In this case put drsrv.oraclegenesis.com

18. Click on configure network and assign a static IP

19. Check Connect automatically option and assign Ip address as per your environment. I have assigned 192.168.0.212 to my DR server.

20. Click next and select your timezone

21. Give a root password

22. Select create custom layout and click next

23. Select Free disk space and click on create

24. Choose standard partition and click Create

25. Create boot partition with 512 MB size

26. Once again choose free disk space and click on create

27. Select file system type as Swap and give 15 GB size (Approx 15000 MB)

28. Again select free space and click on create

29. Give mount point as root (/) and chose option Fill to maximum allowable size

30. Click next and chose Forma option

31. Select Write changes to disk

32. No changes on below screen, just click next

33. Choose customize now and just click next

34. From the list, select packages as below:

Base System:

  • Base
  • Client management tools
  • Compatibility libraries
  • Hardware monitoring utilities
  • Large Systems Performance
  • Network file system client
  • Performance Tools
  • Perl Support

Servers:

  • Server Platform
  • System administration tools
  • Desktops
  • Desktop
  • Desktop Platform
  • Fonts
  • General Purpose Desktop
  • Graphical Administration Tools
  • Input Methods
  • X Window System

Development:

  • Additional Development
  • Development Tools

Applications:

  • Internet Browser

35. Click next and OEL installation will begin

36. Once done, click on Reboot

After reboot, there are few post installation steps. Click on Forward

Accept the license and click on Forward

Choose No and click on Forward

Select No thanks and proceed

Click forward

Do not create any user at this stage, click forward

Choose Yes

Check the Date and Time settings and click on forward

Nothing to choose under Kdump, click forward

OEL 6.5 installation is completed.

Oracle Installation Prerequisites

Connect to standby server virtual machine via putty and put below details under /etc/hosts file. Make sure to change hostname and IP address as per your machine. Only put this info it this is not there in /etc/hosts file

Check if your machine is able to speak to internet. You can ping yahoo.com. Then install below package to perform all oracle installation pre-requisites

yum -y install oracle-rdbms*

Change password for oracle user

passwd oracle

Add grid user and update password

useradd -u 54323 -g oinstall -G dba grid
passwd grid

Create necessary directories

mkdir -p /u01/app/oracle
mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
chown -R grid:oinstall /u01
chownoracle:oinstall /u01/app/oracle
chmod -R 775 /u01
mkdir -p /u02
chown -R oracle:oinstall /u02
chmod -R 775 /u02

Disable firewall

/etc/rc.d/init.d/iptables status
/etc/rc.d/init.d/iptables stop
service iptables stop
chkconfigiptables off
echo 0 > /selinux/enforce

Download Oracle 11.2.0.4 gird and database binaries. Copy it to /u02 location

Switch to Oracle user and edit the bash profile

su - oracle
mv .bash_profile .bash_profile_orig
vi .bash_profile

Put below under the bash profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=proddb_st
export ORACLE_UNQNAME=proddb_st
export JAVA_HOME=/usr/bin/java
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_TERM=xterm
export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
export TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

Switch to grid user and edit the bash profile

su - grid
mv .bash_profile .bash_profile_orig
vi .bash_profile

Put below under the bash profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=+ASM
export JAVA_HOME=/usr/bin/java
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/11.2.0/grid
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/local/bin
export PATH
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

Configure ASM Disks

In this lab exercise, we will be adding ASM disks to standby server. Our goal is to setup both primary and standby database on ASM disks. Let us proceed and configure ASM disks.

Install ASM packages

yum -y install oracleasm*kmod-oracleasm

Configure ASM

[[email protected] ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

Load kernel module

[[email protected] ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm

Shutdown the virtual machine and Add asm disk to virtual machine of 110 GB size. Go to virtual machine settings > storage > Controller SATA > Add new disk

Select create new disk

Select VDI and click next

Select Fixed size and click next

Give 110 GB size and click on create

It will take some time to create the disk. Once created, start the virtual machine.

Check the new added disk is listed via fdisk -l command (These commands are same as prod server but run on DR server)

Create three partitions on /dev/sdb disk to be used as ASM disks. Use below commands

# fdisk /dev/sdb
n
p
1
<enter>
+50G

n
P
2
<enter>
+50G

n
p
3
<enter>
<enter>

w

w will save the partitions. Check the partitions under /dev/sdb

Create asm disks. Each partition will be used as one disk inside a disk group

oracleasm createdisk DATA1 /dev/sdb1
oracleasm createdisk FRA1 /dev/sdb2
oracleasm createdisk CRS1 /dev/sdb3

Reboot system.

Install Oracle Grid Software

Switch to grid user, unzip the grid software under /u02

su - grid
cd /u02
unzip p13390677_112040_Linux-x86-64_3of7.zip

cd grid directory and run the installer

cd grid
./runInstaller

Follow the screens to install oracle grid software

Check the resources status

ps -ef|grep pmon
crsctl status resource -t

Create ASM Diskgroups

As grid user, start asmca utility. Type asmca at command prompt

Click on Create. Give a name to your DATA disk group. This disk group will hold database files. Then click on OK

Install Oracle Database Software

Switch to oracle user, unzip oracle software under /u02

su - oracle
cd /u02
unzip p13390677_112040_Linux-x86-64_1of7_DB1.zip
unzip p13390677_112040_Linux-x86-64_2of7_DB2.zip
cd database
./runInstaller

Follow the screens to install oracle database software

Physical Standby Configuration

Now that both our primary and standby servers are ready, we can proceed further and configure physical standby.

Primary Database Changes

Configuration overview:

  1. Primary server must run in archive log mode: This is the basic requirement for primary to run in archive log mode.
  • SQL> archive log list;

If it’s not in archive log mode, use below:

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

2. Enable force logging: 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;

3. 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';

4. 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 and add current + 1 standby log groups

SQL> select group#, member from v$logfile;

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.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '+FRA' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '+FRA' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '+FRA' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '+FRA' SIZE 50M;

Check the standby log files via below query

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

5. 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

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> exit;

OS> cd $ORACLE_HOME/dbs
OS>orapwd file=orapwproddb_st password=<sys_password>

OS>scporapwproddb_st [email protected]<standby_ip>:$ORACLE_HOME/dbs

6. 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_unique_name;

7. 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> select flashback_on from v$database;
SQL> alter database flashback on;

If flashback parameters are not set properly, use below commands

SQL> show parameter recovery;
SQL> alter system set db_recovery_file_dest='+FRA';
SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;

8. Configure tns entries on primary & standby: 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.

OS> vi $ORACLE_HOME/network/admin/tnsnames.ora

proddb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb)
    )
  )

proddb_st =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.204)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb_st)
    )
  )

9. Configure Listener on primary: We need to configure listener via gird user.

OS>su - grid
OS> cd $ORACLE_HOME/network/admin
OS> vi listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

SID_LIST_LISTENER=
 (SID_LIST=
   (SID_DESC=
     (GLOBAL_DBNAME=proddb)
     (SID_NAME=proddb)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
   (SID_DESC=
     (GLOBAL_DBNAME=proddb_DGMGRL)
     (SID_NAME=proddb)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
 )
OS>lsnrctl reload listener

10. Configure listener on standby: We need to configure listener via gird user.

OS>su - grid
OS> cd $ORACLE_HOME/network/admin
OS> vi listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.204)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

SID_LIST_LISTENER=
 (SID_LIST=
   (SID_DESC=
     (GLOBAL_DBNAME=proddb_st)
     (SID_NAME=proddb_st)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
   (SID_DESC=
     (GLOBAL_DBNAME=proddb_st_DGMGRL)
     (SID_NAME=proddb_st)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
 )
OS>lsnrctl reload listener

11. Perform network test: Perform tnspingon both servers to check network configuration is fine.

OS>su - oracle
OS>tnspingproddb				>> must be OK
OS>tnspingproddb_st				>> must be OK

12. 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.

SQL> alter system set log_archive_dest_2 = 'service=proddb_stasync
valid_for=(online_logfiles,primary_role) db_unique_name=proddb_st';

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

SQL> alter system set fal_server = 'proddb_st';

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

SQL> alter system set log_archive_config= 'dg_config=(proddb,proddb_st)';

15. Restart database and once again check if all the parameters are set properly or not

Build Physical Standby

Configuration overview:

  1. Create pfile for standby (on primary): We will create pfile from spfile on primary database and copy the file to standby server.
On primary server
=================
SQL> create pfile='/tmp/initproddb_st.ora' from spfile;
OS>scp /tmp/initproddb_st.ora [email protected]:/tmp

On standby server
=================
OS> vi /tmp/initproddb_st.ora
  • Change FAL_SERVER to the primary SID (proddb)
  • In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the primary SID (in this case proddb)
  • Add parameter db_unique_name=’proddb_st’
  • Save pfile and close
  • Create audit destination at OS level using mkdir
  • Switch to grid user and create respective directories under +DATA and +FRA
OS>su - grid
OS>asmcmd
ASMCMD> cd DATA
ASMCMD>mkdir PRODDB 
ASMCMD>mkdir PRODDB_ST
ASMCMD> cd ..
ASMCMD> cd FRA
ASMCMD>mkdir PRODDB
ASMCMD>mkdir PRODDB_ST
ASMCMD> exit

2. Create spfile on standby: In the previous step, we have created pfile. Let’s create spfile using pfile.

SQL> startup nomountpfile=/tmp/initproddb_st.ora;
SQL> create spfile='+DATA/proddb_st/spfileproddb_st.ora' from pfile='/tmp/initproddb_st.ora';
SQL> shutdown

Note: in case of ORA-00845, edit /etc/fstab as root user

OS>su - root
OS> vi /etc/fstab

Edit below line and add size=5G
===============================
tmpfs                   /dev/shmtmpfsdefaults,size=5G        0 0

OS>mount -o remount /dev/shm

Under $ORACLE_HOME/dbs create initproddb_st.ora file with below spfile location

cd $ORACLE_HOME/dbs
vi initproddb_st.ora
SPFILE='+DATA/proddb_st/spfileproddb_st.ora'

Start the standby DB instance in nomount stage and check spfile parameter

SQL> startup nomount
SQL> show parameter spfile
SQL>exit;			>> must exit or duplicate will fail

3. Duplicate primary database via RMAN: In this step, we will use RMAN to duplicate primary database for our standby database. You can connect to RMAN on primary or standby server. We will connect to RMAN on primary server and perform the duplicate.

On primary server
=================
rman target sys/[email protected]

RMAN> connect catalog rman_rc/[email protected]		>> to connect catalog
RMAN> connect auxiliary sys/[email protected]_st			>> to connect standby db

RMAN> duplicate target database for standby from active database nofilenamecheck;

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

Finished Duplicate Db at 07-DEC-2015

4. Drop multiplexed SRL on standby: The standby redo logs are multiplexed on both +DATA and +FRA diskgroups. We need to delete the +DATA multiplexed copies of SRL

On standby server
=================
select name, database_role, open_mode from v$database;
column member format a50;
select group#, member from v$logfile where type='STANDBY' order by 1,2;

Drop the multiplexed copes of SRL from +DATA only
=================================================
SQL> alter database drop logfile member '+DATA/proddb_stb/onlinelog/group_11.265.940696341';
SQL> alter database drop logfile member '+DATA/proddb_stb/onlinelog/group_12.267.940696345';
SQL> alter database drop logfile member '+DATA/proddb_stb/onlinelog/group_13.266.940696347';
SQL> alter database drop logfile member '+DATA/proddb_stb/onlinelog/group_14.269.940696351';

One of the logfile members will not drop as it is in use. You need to switch logfile in production and then drop SRL on standby

On production:
==============
SQL> alter system switch logfile;

System altered.

5. Start stop MRP on standby: Our standby database is ready and we can start MRP process. Run below command to start recovery process in background.

To start MRP:
SQL>alter database recover managed standby database disconnect;

To stop MRP:
SQL>alter database recover managed standby database cancel;

6. Verifying Redo Apply: Once MRP is started, we must verify if our setup is working fine.

On standby:
===========
SQL>alter database recover managed standby database disconnect;

On both primary & standby:
==========================
SQL> set lines 999;
SQL> select * from v$dataguard_status order by timestamp;
SQL> 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:
===========
SQL> select sequence#, first_time, next_time, applied, archived from v$archived_log where name = 'proddb_st' order by first_time;
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
SQL> archive log list;

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

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

On standby:
==========
SQL>alter database recover managed standby database cancel;
SQL>alter database flashback on;
SQL>alter database recover managed standby database disconnect;

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

rman target / 
configure archivelog deletion policy to applied on all standby;

9. Register standby database with srvctl (grid utility): We need to register standby database with srvctl utility as it will help us manage database easly.

On standby:
===========
sqlplus / as sysdba
SQL>alter database recover managed standby database cancel;
SQL>shutdown immediate
SQL> exit;

switch to grid home location via oracle user:
=============================================
OS> cd /u01/app/11.2.0/grid/bin

OS>./srvctl add database -d proddb -iproddb_st -o /u01/app/oracle/product/11.2.0/dbhome_1 -r PHYSICAL_STANDBY -s MOUNT

OS>./srvctl start database -d proddb
OS>./srvctl status database -d proddb

Note: even though we can start stop standby database via srvctl utility, we must always start MRP manually.

Data Guard Client Connectivity

  1. Create a database service on primary: This service is created on primary database to connect proddb.
exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'proddb_rw', network_name => 'proddb_rw', aq_ha_notifications => TRUE, failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 30, failover_delay => 5);

2. Make above service run only on primary: This service should run only on the primary database. Even when there is switchover or failover, this service should continue to run on new primary.

create or replace procedure proddb_rw_service
is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('proddb_rw');
else
DBMS_SERVICE.STOP_SERVICE('proddb_rw');
end if;
end;
/

3. Create trigger to start above service: We need to create trigger to start above service on database startup and also role change on primary.

create or replace TRIGGER proddb_startup
after startup on database
begin
proddb_rw_service;
end;
/
create or replace TRIGGER proddb_rolechange
after db_role_change on database
begin
proddb_rw_service;
end;
/

4. Start the new service:Start the new service on primary.

SQL> exec proddb_rw_service;
SQL> alter system archive log current;
SQL> show parameter service_names

5. Edit client tnsnames.ora: We need to enable client tns entries to access database via above service.

proddb =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.204)(PORT = 1521))
    )
   (CONNECT_DATA = (SERVICE_NAME = proddb_rw)
     (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=5))
   )
  )

Performing Manual Switchover

  1. Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine.Keep querying below
sqlplus sys/[email protected] as sysdba

SQL> select name, open_mode, db_unique_name, database_role from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ---------------
proddbREAD WRITE           proddb                         PRIMARY

2. Check primary and standby for any gaps:

On primary:
===========
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

On standby:
===========
SQL> select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

3. Convert primary to standby: We will first convert primary to standby and later standby to primary.

On primary:
===========
SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown immediate;			>> Ignore if you get error
SQL> startup mount;

4. Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary.

SQL> select name, open_mode, db_unique_name, database_role from v$database;

5. Convert standby to primary:Our primary is already converted to standby. Now it’s time to convert original standby into primary.

SQL> select SWITCHOVER_STATUS from V$DATABASE;
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;

6. Check client query: Check the query you executed in step 4 on client, it must get executed.

7. On new standby – Initially your primary database:Start MRP

SQL> alter database recover managed standby database using current logfile disconnect from session;

At this point, your Data Guard configuration should look like below:

You can check this via below query:

SQL> select name, open_mode, db_unique_name, database_role from v$database;

8. Revert back: Once again follow the document from top and re-execute steps in proper databases to revert back original setup. At the end, your configuration should look like below:

Performing Manual Failover

Failover is when your primary database is completely lost. When there is a failover, standby is converted into primary but primary is not converted into standby as it is lost. If you do not have Flashback enabled on primary, you must re-create primary from scratch (Using RMAN duplicate method). In our case, we have already enabled flashback on both primary and standby.

Note: In our earlier activity, we have performed switchover. Current state of your servers should be:

  1. Let’s crash primary (proddb): In order to simulate failure, we will shut down the primary server proddb. As root user, shutdown the server without shutting down DB.
[email protected] ~] # shutdown now

2. Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary.

SQL> select name, open_mode, db_unique_name, database_role from v$database;

3. Minimize data loss (proddb): If you can mount the primary database, then flush the logs to standby.

On primary:
===========
SQL> startup mount
SQL> alter system flush redo to 'proddb_st';

If you are not able to mount the database, then check if primary server is up. In that case manually copy archive logs from primary to standby and register those logs on standby database.

On standby:
===========
SQL> alter database register physical logfile '&logfile_path';

4. Check for redo gaps: If any gap exists, copy log files from primary and register on standby as per last step

On standby:
===========
SQL> select THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# from V$ARCHIVE_GAP;

5. Start failover: We need to activate standby so that client can continue to access even after failover.

On standby:
===========

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary

SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;

6. Check client query: Check the query you executed in step 2 on client, it must get executed.

Rebuild Primary After Failover

Post failover, there are two methods of rebuilding your failed primary:

  • Method 1:Rebuild from scratch à RMAN duplicate
  • Method 2: Flashback database à only if Flashback was enabled

Note: In our earlier activity, we have performed Failover. Current state of your servers should be:

  1. Get the SCN at which standby became primary: We need to get the SCN at which the current primary(proddb_st) was activated. This SCN will be used to flashback crashed (proddb) database.
SQL>select to_char(standby_became_primary_scn) from v$database;

2. Flashback crashed primary(proddb): Start the proddb server, mount the database and flashback proddb to SCN from the last step.

SQL>shutdown immediate;
SQL>startup mount;
SQL>flashback database to scn<standby_became_primary_scn>;

3. Convert crashed primary to physical standby(proddb): Now the old primary is at SCN when proddb_st was activated. We can convert proddb into a physical standby and start redo apply.

SQL>alter database convert to physical standby;
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database recover managed standby database disconnect;

Note:Current state of your databases should be:

4. Revert to original configuration: At this stage, if you would like to revert the current state of databases to original, you can perform manual switchover.

Oracle Active Data Guard

In very simple terms, when you open Physical Standby in read only mode, it is known as Active Data Guard. But, Active Data Guard needs license for and you must check with Oracle for same before implementing it.

  1. Enable Active Data Guard: Let us Open Physical Standby and test active data guard.
On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select name, open_mode, database_role from v$database;
SQL> alter database recover managed standby database disconnect;

2. Verify MRP is running in the background: Use below query to check if MRP is running in the background or not.

On standby (active data guard):
===============================
SQL> select process, status, sequence# from v$managed_standby;

3. Test configuration: As our active data guard is open for read only queries and background recover is active, let us create a table on primary and see if it is reflected on standby.

On primary:
===========
SQL> create table test(sno number(2),sname varchar2(10));
SQL> insert into test values (1,'John');
SQL> commit;
SQL> alter system switch logfile;

On standby:
===========
SQL> select * from test;

4. Revert back to physical standby: If you want to convert active data guard back to physical standby, follow below commands.

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL>shutdown immediate;
SQL> startup mount;
SQL> select name, open_mode, database_role from v$database;
SQL> alter database recover managed standby database disconnect;
SQL> select process, status, sequence# from v$managed_standby;

Oracle Snapshot Standby

Let us assume there is a requirement from application team to test something and there is a need to clone production database. We can convert existing physical standby database into snapshot standby database, do the testing and convert it back to physical standby. We can always revert back snapshot standby to the point when we converted it to snapshot standby from physical standby. This way we can repeat the cycle any number of times, perform testing, convert back to physical standby and sync back again with production.

  1. Convert physical standby to snapshot standby: We will now convert the physical standby database to snapshot standby.
On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; 		>> make sure its mounted
SQL>alter database convert to snapshot standby;
SQL> alter database open;				>> open the DB
SQL> select name, open_mode, database_role from v$database;

2. Verifying snapshot standby: Now you must be able to read-write on snapshot standby. Meanwhile, we can even check the standby alert log. The archives received from primary are not applied on standby. We can even check that there is a guaranteed restore point has been created. So that when you convert snapshot back to physical standby, it will be used. Also note, for this snapshot standby, you do not need Flashback enabled at database level.

On standby:
===========
SQL>select name, guarantee_flashback_database from v$restore_point;
SQL> create table student(sno number(2), s_name varchar2(10));
SQL> insert into student values(1,'RAM');
SQL> insert into student values (2,'Max');
SQL> commit;
SQL> select * from student;

3. Revert back snapshot standby to physical standby: Once application testing is done, you can revert back snapshot standby to same point when it was converted from physical standby to snapshot standby.

On standby:
===========
SQL>select name, open_mode, database_role from v$database;
SQL> shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
SQL> select * from student;

Oracle Data Guard Broker

  1. Listener setup: If you look back the listener configuration file that we have used, there is a dedicated service we already created for DGMGRL. You can cat the listener.ora file and see an entry of DGMGRL. This is required in order enable Data Guard Broker. If this is not set, add below entry and restart listener on both primary and standby
su - grid
cd $ORACLE_HOME/network/admin
cat listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.211)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )

SID_LIST_LISTENER=
 (SID_LIST=
   (SID_DESC=
     (GLOBAL_DBNAME=proddb)
     (SID_NAME=proddb)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
(SID_DESC=
     (GLOBAL_DBNAME=proddb_DGMGRL)
     (SID_NAME=proddb)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
   )
 )

2. Stop MRP on standby: We would like to manage our data guard configuration using Data Guard Broker. Stop MRP and clear Log_Archive_dest_2 parameter.

On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

On primary:
===========
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

3. Enable broker on both primary and standby: We need to start the broker by enabling it on both primary and standby.

On primary:
===========
SQL>alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;

On standby:
===========
SQL>alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;

4. Register primary with broker: On primary, connect to DGMGRL utility and register.

On primary:
===========
dgmgrl sys/[email protected]
DGMGRL> create configuration proddb as primary database is proddb connect identifier is proddb;
DGMGRL> show configuration;

5. Register standby with broker: In the same DGMGRL utility, register standby.

DGMGRL> add database proddb_st as connect identifier is proddb_st;
DGMGRL> show configuration;

6. Enable Data Guard Broker: Once primary and standby are registered, we must enable broker.

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE proddb;
DGMGRL> SHOW DATABASE proddb_st;

7. Start / stop redo apply on standby via broker: Like how we start / stop MRP manually, we can start / stop redo apply on standby using broker.

Stop log apply:
===============
dgmgrl sys/[email protected]
DGMGRL> show configuration;
DGMGRL> show database proddb_st;
DGMGRL> edit database proddb_st set state=APPLY-OFF;
DGMGRL> show database proddb_st;

Start log apply:
================
dgmgrl sys/[email protected]
DGMGRL> show configuration;
DGMGRL> show database proddb_st;
DGMGRL> edit database proddb_st set state=APPLY-ON;
DGMGRL> show database proddb_st;

8. Enable / disable log shipping from primary to standby: Like how we can Enable / Disable log shipping manually, we can do same via broker.

Disable log shipping/transport:
===============================
dgmgrl sys/[email protected]
DGMGRL> show configuration;
DGMGRL> show database proddb;
DGMGRL> edit database proddb set state=TRANSPORT-OFF;
DGMGRL> show database proddb;

Enable log shipping/transport:
==============================
dgmgrl sys/[email protected]
DGMGRL> show configuration;
DGMGRL> show database proddb;
DGMGRL> edit database proddb set state=TRANSPORT-ON;
DGMGRL> show database proddb;

Switchover Using Broker

It’s very simple to perform switchover using data guard broker. A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands.

  1. Switchover to proddb_st: connect to primary database proddb and switchover to standby proddb_st.
On primary:
===========
dgmgrl sys/[email protected]

DGMGRL> show configuration;

DGMGRL> SWITCHOVER TO proddb_st;
Performing switchover NOW, please wait...
Operation requires a connection to instance "proddb_st" on database "proddb_st"
Connecting to instance "proddb_st"...
Connected.
New primary database "proddb_st" is opening...
Operation requires startup of instance "proddb" on database "proddb"
Starting instance "proddb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "proddb_st"

DGMGRL> show configuration;

2. Switch back to original primary: Connect to current primary proddb_st and switchover to current stnadbyproddb.

On current primary(proddb_st):
==============================
dgmgrl sys/[email protected]_st

DGMGRL> show configuration;

DGMGRL> switchover to proddb;
Performing switchover NOW, please wait...
Operation requires a connection to instance "proddb" on database "proddb"
Connecting to instance "proddb"...
Connected.
New primary database "proddb" is opening...
Operation requires startup of instance "proddb_st" on database "proddb_st"
Starting instance "proddb_st"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "proddb"

DGMGRL> show configuration;

Failover Using Broker

It’s very simple to perform failover using data guard broker. If primary database is unavailable, we can activate standby using below method. Note, in a failover, we have lost primary.

  1. Crash the primary: Let us simulate failure. We will kill the PMON process at OS level.
On primary:
===========
[[email protected] admin]$ ps -ef|greppmon
grid      2636     1  0 Apr15 ?        00:00:12 asm_pmon_+ASM
oracle   16914     1  0 14:03 ?        00:00:00 ora_pmon_proddb
oracle   17722 15893  0 15:08 pts/0    00:00:00 grep pmon

[[email protected] admin]$ kill -9 16914

2. Failover to proddb_st: connect to standby database proddb_st (as primary crashed or not available) and failover to standby proddb_st.

On standby:
===========
dgmgrl sys/[email protected]_st

DGMGRL> show configuration;
DGMGRL>FAILOVER TO proddb_st;
Performing failover NOW, please wait...
Failover succeeded, new primary is "proddb_st"
DGMGRL> show configuration;

Reinstate Failed Primary Using Broker

Post failover, there are two methods of rebuilding your failed primary:

  • Method 1: Rebuild from scratch à RMAN duplicate
  • Method 2: Flashback database à only if Flashback was enabled

Note: In our earlier activity, we have performed Failover. Current state of your servers should be:

In our case, we have enabled flashback already both on primary and standby.

  1. Reinstate failed primary: When you use data guard broker, with just one command, the primary can be rebuilt. Start the failed primary server, in this case start proddb server.
On current primary (proddb_st):
===============================
dgmgrl sys/[email protected]_st

DGMGRL> show configuration;

DGMGRL> reinstate database proddb;
Reinstating database "proddb", please wait...
Operation requires shutdown of instance "proddb" on database "proddb"
Shutting down instance "proddb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "proddb" on database "proddb"
Starting instance "proddb"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "proddb" ...
Reinstatement of database "proddb" succeeded

2. Verify proddb post reinstate: The best part is broker will automatically recover earlier failed primary proddb, mount the database and start MRP too.

On failed primary (proddb):
===========================
sqlplus / as sysdba
SQL> select name,open_mode from v$database;
SQL> select process, status, sequence# from v$managed_standby;

3. Switchover to get original configuration: At this stage, you can perform switchover to again get back original configuration.

On current primary (proddb_st):
===============================
dgmgrl sys/[email protected]_st
DGMGRL> switchover to proddb;

Enable Fast Start Failover (FSFO)

While Oracle 11g’s Data Guard definitely protects a database when the entire production site is lost via its failover capabilities, it’s still necessary for an Oracle DBA to intervene to complete the failover process. With this activity, we can enable automatic failover using Fast-Start-Failover Observer.

  1. Check Static Connect Identifier: In order to enable FSFO, the Static Connect Identifier parameter must be set both in primary and standby.
On primary(proddb):
===================
dgmgrl sys/[email protected]
DGMGRL> show database proddbStaticConnectIdentifier;
DGMGRL> show database proddb_stStaticConnectIdentifier;

How to fix StaticConnectIdentifier?

The StaticConnectIdentifiertakes its value from LOCAL_LISTENER parameter from the database. If this value is not set for any database above, then connect to sqlplus and edit LOCAL_LISTENER parameter.

SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.204)(PORT=1521))' ;

And then restart the listener via grid user.

2. Define FastStartFailoverTarget: In general, there can be more than one physical standby database. So, we need to pair physical standby with primary to let Fast Start Failover know which physical standby to be activated.

On primary (proddb):
====================
dgmgrl sys/[email protected]
DGMGRL> SHOW FAST_START FAILOVER
DGMGRL> EDIT DATABASE proddb SET PROPERTY FastStartFailoverTarget = 'proddb_st';
DGMGRL> EDIT DATABASE proddb_st SET PROPERTY FastStartFailoverTarget = 'proddb';
DGMGRL> show database verbose proddb;
DGMGRL> show database verbose proddb_st;

3. Define FastStartFailoverThreshold: Next we need to let broker know when to initiate automatic failover. What is the time (in seconds) that FSFO will wait before initiating failover.

DGMGRL>EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;
DGMGRL> show fast_start failover

4. Define FastStartFailoverLagLimit: We can optionally define how much time (in seconds) data we are ready to lose in case the Data Guard is in Max Performance Mode.

DGMGRL>EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30;

5. Enable FSFO: Now we can enable FSFO. Never start observer on production database.

dgmgrl sys/[email protected]_st

DGMGRL>ENABLE FAST_START FAILOVER;
DGMGRL> show configuration;
DGMGRL> start observer;

TEST FSFO CONFIGURATION

Let us simulate a failure. We will abort primary (proddb) instance and wait for FSFO to perform automatic failover.

  1. Simulate failure: On proddb, the current primary, let us shut abort the instance.
On primary(proddb):
===================
sqlplus / as sysdb
SQL> shut abort;

2. Check logfiles: At this stage, check alert log and observer log files. FSFO must perform automatic failover and proddb_st would be your new primary database.

On new primary (proddb_st):
===========================
sqlplus / as sysdba
SQL> select name, open_mode, db_unique_name from v$database;

REINSTATE FAILED PRIMARY POST FSFO

Mount the failed primary(proddb) and it will auto reinstate. Note: do not open the database as it will be switched to physical standby.

On failed primary (proddb):
===========================
sqlplus / as sysdba
SQL> startup mount;

dgmgrl sys/[email protected]
DGMGRL> show configuration;

Once reinstate is done, your current configuration should look like below:

You can perform switchover to get back the original configuration.

On current primary (proddb_st):
===============================
dgmgrl sys/[email protected]_st
DGMGRL> show configuration;
DGMGRL> switchover to proddb;

your current configuration should look like below:

DISABLE FSFO AND STOP OBSERVER

dgmgrl sys/[email protected]
DGMGRL> DISABLE FAST_START FAILOVER;

Stop observer:
==============
dgmgrl sys/[email protected]_st
DGMGRL> stop observer;

Change Data Guard Protection Modes

By now, we know that there are three types of Data Guard protection modes:

Important Points:

  • By default, the protection mode is MAX PERFORMANCE. If you look above, MAX PERFORMANCE uses ASYNC redo transport and rest other protection modes uses SYNC protection mode.
  • Also, looking at MAX PROTECTION and MAX AVAILABILITY, we can say that the MAX PROTECTION mode is not used in real time. The main reason is if standby unavailable, primary will shut down.
  • The ultimate protection modes you must use are: MAX PERFORMANCE and MAX AVAILABILITY!

SWITCH PROTECTION MODE FROM MAX PERFORMANCE TO MAX AVAILABILITY USING DATA GUARD BROKER

  1. Verify the broker configuration, check if it’s enabled and make sure log apply is enabled
dgmgrl sys/[email protected]
show configuration 
show database proddb 
show database proddb_st 
edit database proddb_st set state=apply-on;

2. Change LNS mode from ASYN to SYNC

EDIT DATABASE proddb_st SET PROPERTY LogXptMode='SYNC'; 
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

SWITCH PROTECTION MODE FROM MAX AVAILABILITY TO MAX PERFORMANCE USING DATA GUARD BROKER

3. Verify the broker configuration, check if it’s enabled and make sure log apply is enabled

dgmgrl sys/[email protected]
show configuration 
show database proddb 
show database proddb_st 
edit database proddb_st set state=apply-on;

4. Change LNS mode from ASYN to SYNC

EDIT DATABASE proddb_st SET PROPERTY LogXptMode='ASYNC'; 
EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;

Was this article helpful?

Related Articles

Leave a Comment