Configure DataGuard 11gR2

Posted on Posted in Oracle, Oracle DB

Once we have our Production DB mounted and running, we’ll mount a new Standby Database, thatis all the changes made in Production (Primary) Database apply in the Standby, so in case of catastrophe we can make a switchover to the Secondary.

This is my infraestructure:

  • Two primary DB (RAC 11gR2)
    • PRIMARY1
    • PRIMARY2
  • Two standby DB (RAC 11gR2)
    • STANDBY1
    • STANDBY2

Duplicate Database

First, we need duplicate the primary Database to the standby Database.

Be sure that the standby database have only the binary software, not any database running it.

  • Spfile

To start our proyect we’ll need a temporary pfile to start database in nomount mode. Copy from the primary database the current spfile and edit it.

/u01/app/oracle/product/11.2.0.3/db_1/dbs/initSTANDBY.ora-temp

STANDBY2.__db_cache_size=33554432
STANDBY1.__db_cache_size=16777216
STANDBY2.__java_pool_size=16777216
STANDBY1.__java_pool_size=16777216
STANDBY2.__large_pool_size=16777216
STANDBY1.__large_pool_size=16777216
STANDBY2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
STANDBY1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
STANDBY2.__pga_aggregate_target=587202560
STANDBY1.__pga_aggregate_target=587202560
STANDBY2.__sga_target=2063597568
STANDBY1.__sga_target=2063597568
STANDBY2.__shared_io_pool_size=0
STANDBY1.__shared_io_pool_size=0
STANDBY2.__shared_pool_size=318767104
STANDBY1.__shared_pool_size=335544320
STANDBY2.__streams_pool_size=268435456
STANDBY1.__streams_pool_size=268435456
*.aq_tm_processes=1
*.archive_lag_target=1800
*.audit_file_dest='/u01/app/oracle/admin/STANDBY/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.cluster_database_instances=2
*.compatible='11.2.0.0'
*.control_files='+DATA/STANDBY/controlfile1','+FRA/STANDBY/controlfile2'
*.db_16k_cache_size=318767104
*.db_block_checking='TRUE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='SNAIDER'
*.db_file_name_convert='+DATA/PRIMARY/','+DATA/STANDBY/','+FRA/PRIMARY/','+FRA/STANDBY/'
*.db_files=1024
*.db_flashback_retention_target=14400
*.db_name='PRIMARY'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=161061273600
*.db_unique_name='STANDBY'
*.dg_broker_config_file1='+DATA/STANDBY/dr1STANDBY.dat'
*.dg_broker_config_file2='+FRA/STANDBY/dr2STANDBY.dat'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'
*.event=''
STANDBY1.fal_client='STANDBY1_DG'
STANDBY2.fal_client='STANDBY2_DG'
*.fal_server='PRIMARY_DG'
*.gcs_server_processes=3
*.global_names=TRUE
STANDBY1.instance_number=1
STANDBY2.instance_number=2
*.job_queue_processes=10
*.log_archive_config='dg_config=(STANDBY,PRIMARY)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_format='%t_%s_%r.dbf'
STANDBY1.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
STANDBY1.log_archive_trace=0
*.log_file_name_convert='+DATA/STANDBY/','+DATA/PRIMARY/','+FRA/STANDBY/','+FRA/PRIMARY/'
*.nls_language='SPANISH'
*.nls_territory='SPAIN'
*.open_cursors=2000
*.optimizer_dynamic_sampling=2
*.pga_aggregate_target=338860800
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=1524
*.sga_max_size=2147483648
*.standby_file_management='AUTO'
*.streams_pool_size=268435456
STANDBY1.thread=1
STANDBY2.thread=2
*.undo_management='AUTO'
*.undo_retention=3600
STANDBY1.undo_tablespace='UNDOTBS1'
STANDBY2.undo_tablespace='UNDOTBS2'
*.utl_file_dir='/u01/app/oracle/admin/STANDBY/utldir'
  • Test if, all the parameters are correct.
[oracle@standby1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mar Mar 6 21:17:45 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='?/dbs/initSTANDBY.ora-temp';
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2230072 bytes
Variable Size		 1761609928 bytes
Database Buffers	  369098752 bytes
Redo Buffers		    4947968 bytes
SQL>
  • Create the Spfile
SQL> create spfile='+DATA/STANDBY/spfileSTANDBY.ora' from pfile='?/dbs/initSTANDBY.ora-temp';

SQL> shutdown immediate;
ORACLE instance shut down.
SQL>

The Database need to know, where is the new spfile:

cat /u01/app/oracle/product/11.2.0.3/db_1/dbs/initSTANDBY1.ora

SPFILE='+DATA/standby/spfileSTANDBY.ora'

cat /u01/app/oracle/product/11.2.0.3/db_1/dbs/initSTANDBY2.ora

SPFILE='+DATA/standby/spfileSTANDBY.ora'

Test if the database start:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2230072 bytes
Variable Size		 1761609928 bytes
Database Buffers	  369098752 bytes
Redo Buffers		    4947968 bytes
SQL>
  • Orapw

We need a common password file in all the nodes to start with the duplicate and finish creating the DataGuard.

[oracle@standby1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/dbs/
[oracle@standby1 dbs]$ mv orapwPRIMARY1 orapwSTANDBY1
The same in the other node:

[oracle@standby2 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/dbs/
[oracle@standby2 dbs]$ mv orapwPRIMARY1 orapwSTANDBY2Tn
  • Tnsnames.ora

To connect databases together, add the different services and nodes in all $ORACLE_HOME/network/admin/tnsnames.ora

A common tnsnames.ora for all the node’s:

cat $ORACLE_HOME/network/admin/tnsnames.ora

PRIMARY =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary1.snaider.es) (PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary2.snaider.es) (PORT = 1521))
   (LOAD_BALANCE = yes)
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
  )
)

PRIMARY1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary1.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
    (INSTANCE_NAME = PRIMARY1)
  )
)

PRIMARY2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary2.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
    (INSTANCE_NAME = PRIMARY2)
  )
)

STANDBY =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby1.snaider.es) (PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby2.snaider.es) (PORT = 1521))
   (LOAD_BALANCE = yes)
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
  )
)

STANDBY1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby1.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
    (INSTANCE_NAME = STANDBY1)
  )
)

STANDBY2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby2.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
    (INSTANCE_NAME = STANDBY2)
  )
)

Run the Duplicate

  • Clean the ASM
. /home/oracle/bin/oraASM.env >/dev/null

asmcmd << EOF
cd data
cd $DB_NAME
rm -f datafile/*
rm -f onlinelog/*
rm -f tempfile/*
cd ..
cd ..
cd fra
cd $DB_NAME
rm -f onlinelog/*
rm -rf archivelog/*
exit
EOF
  • Start the Duplicate
[oracle@standby1]$ rman target sys/*****@PRIMARY1 auxiliary sys/*****@STANDBY1

duplicate target database to 'STANDBY' from active database

Starting Duplicate Db at 06/03/2012 21:29:39
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 instance=STANDBY1 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=783 instance=STANDBY1 device type=DISK
.....
...
.

When the Primary controlfile is recovered in the standby, is possible that RMAN display a error:

sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/06/2012 21:30:03
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 03/06/2012 21:30:03
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: the database name 'PRIMARY' in the control file not is 'STANDBY'

Ok change the controlfile recovered:

SQL>  ALTER SYSTEM SET DB_NAME=STANDBY scope=spfile;

Restart the duplicate:

[oracle@standby1]$ rman target sys/*****@PRIMARY1 auxiliary sys/*****@STANDBY1

duplicate target database to 'STANDBY' from active database

Starting Duplicate Db at 06/03/2012 21:29:39
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 instance=STANDBY1 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=783 instance=STANDBY1 device type=DISK
....
...
..

Starting backup at 06/03/2012 21:37:16
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00020 name=+DATA/primary/datafile/cain00.578.776433057
channel ORA_DISK_2: starting datafile copy
input datafile file number=00238 name=+DATA/primary/datafile/psindex.577.776433057

Ok if the duplicate finished correctly, we’ll start with the DataGuard:

DataGuard

  • Listener.ora

Edit the listener.ora in all the node’s and add the DataGuard service:

In case Of Grid Software

cat $GRID_HOME/network/admin/listener.ora

If we don’t have GRID software
cat $ORACLE_HOME/network/admin

PRIMARY1

SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM1)
         (SERVICE_NAME = +ASM)
         (ORACLE_HOME = /u01/app/11.2.0.3/grid)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = PRIMARY_DGMGRL.SNAIDER)
         (SID_NAME = PRIMARY1)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY.SNAIDER)
         (SID_NAME = PRIMARY1)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
    )

PRIMARY2

SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM2)
         (SERVICE_NAME = +ASM)
         (ORACLE_HOME = /u01/app/11.2.0.3/grid)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = PRIMARY_DGMGRL.SNAIDER)
         (SID_NAME = PRIMARY2)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY.SNAIDER)
         (SID_NAME = PRIMARY2)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
    )

STANDBY1

SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM1)
         (SERVICE_NAME = +ASM)
         (ORACLE_HOME = /u01/app/11.2.0.3/grid)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY_DGMGRL.SNAIDER)
         (SID_NAME = STANDBY1)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY.SNAIDER)
         (SID_NAME = STANDBY1)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
    )

STANDBY2

SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM2)
         (SERVICE_NAME = +ASM)
         (ORACLE_HOME = /u01/app/11.2.0.3/grid)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY_DGMGRL.SNAIDER)
         (SID_NAME = STANDBY2)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY.SNAIDER)
         (SID_NAME = STANDBY2)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
      )
    )
  • Tnsnames.ora

Now adjust the tnsnames.ora in all the node’s, with the same configuration

PRIMARY_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary1.snaider.es) (PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary2.snaider.es) (PORT = 1521))
   (LOAD_BALANCE = yes)
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
  )
)

PRIMARY1_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary1.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
    (INSTANCE_NAME = PRIMARY1)
  )
)

PRIMARY2_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = primary2.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = PRIMARY.SNAIDER)
    (INSTANCE_NAME = PRIMARY2)
  )
)

STANDBY_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby1.snaider.es) (PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby2.snaider.es) (PORT = 1521))
   (LOAD_BALANCE = yes)
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
  )
)

STANDBY1_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby1.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
    (INSTANCE_NAME = STANDBY1)
  )
)

STANDBY2_DG =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP) (HOST = standby2.snaider.es) (PORT = 1521))
  (CONNECT_DATA =
    (SERVER =DEDICATED)
    (SERVICE_NAME = STANDBY.SNAIDER)
    (INSTANCE_NAME = STANDBY2)
  )
)

Test in all the nodes, if the service’s respond:

[oracle@primary1 dbs]$ sqlplus sys/********@standby_dg as sysdba
[oracle@primary2 dbs]$ sqlplus sys/********@standby_dg as sysdba

[oracle@standby1 dbs]$ sqlplus sys/********@primary_dg as sysdba
[oracle@standby2 dbs]$ sqlplus sys/********@primary_dg as sysdba
  • Standby RedoLogs

To mount the DataGuard we’ll need a standby’s redo log’s, test if exits:

SQL> set line 150
SQL> col MEMBER format a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       106	   ONLINE  +DATA/standby/onlinelog/group_106.273.776440803		NO
       106	   ONLINE  +FRA/standby/onlinelog/group_106.2735.776440805		YES
       105	   ONLINE  +DATA/standby/onlinelog/group_105.272.776440801		NO
       105	   ONLINE  +FRA/standby/onlinelog/group_105.315.776440801		YES
       104	   ONLINE  +DATA/standby/onlinelog/group_104.271.776440797		NO
       104	   ONLINE  +FRA/standby/onlinelog/group_104.2748.776440799		YES
       103	   ONLINE  +DATA/standby/onlinelog/group_103.586.776440791		NO
       103	   ONLINE  +FRA/standby/onlinelog/group_103.3530.776440795		YES
       102	   ONLINE  +DATA/standby/onlinelog/group_102.585.776440787		NO
       102	   ONLINE  +FRA/standby/onlinelog/group_102.1190.776440789		YES
       101	   ONLINE  +DATA/standby/onlinelog/group_101.584.776440777		NO

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       101	   ONLINE  +FRA/standby/onlinelog/group_101.2446.776440779		YES
       107	   ONLINE  +DATA/standby/onlinelog/group_107.615.776440733		NO
       107	   ONLINE  +FRA/standby/onlinelog/group_107.2271.776440737		NO
       108	   ONLINE  +DATA/standby/onlinelog/group_108.579.776440739		NO
       108	   ONLINE  +FRA/standby/onlinelog/group_108.3233.776440741		NO
       109	   ONLINE  +DATA/standby/onlinelog/group_109.580.776440743		NO
       109	   ONLINE  +FRA/standby/onlinelog/group_109.3296.776440743		NO
       110	   ONLINE  +DATA/standby/onlinelog/group_110.581.776440745		NO
       110	   ONLINE  +FRA/standby/onlinelog/group_110.2062.776440747		NO
       111	   ONLINE  +DATA/standby/onlinelog/group_111.582.776440747		NO
       111	   ONLINE  +FRA/standby/onlinelog/group_111.1055.776440747		NO

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       112	   ONLINE  +DATA/standby/onlinelog/group_112.583.776440749		NO
       112	   ONLINE  +FRA/standby/onlinelog/group_112.2643.776440751		NO

We don’t have StandBy RedoLogs, add it’s:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 113 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 114 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 115 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 116 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 117 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 118 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 119 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 120 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 121 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 122 SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 123 SIZE 300M;
    
SQL> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       106	   ONLINE  +DATA/standby/onlinelog/group_106.273.776440803		NO
       106	   ONLINE  +FRA/standby/onlinelog/group_106.2735.776440805		YES
       105	   ONLINE  +DATA/standby/onlinelog/group_105.272.776440801		NO
       105	   ONLINE  +FRA/standby/onlinelog/group_105.315.776440801		YES
       104	   ONLINE  +DATA/standby/onlinelog/group_104.271.776440797		NO
       104	   ONLINE  +FRA/standby/onlinelog/group_104.2748.776440799		YES
       103	   ONLINE  +DATA/standby/onlinelog/group_103.586.776440791		NO
       103	   ONLINE  +FRA/standby/onlinelog/group_103.3530.776440795		YES
       102	   ONLINE  +DATA/standby/onlinelog/group_102.585.776440787		NO
       102	   ONLINE  +FRA/standby/onlinelog/group_102.1190.776440789		YES
       101	   ONLINE  +DATA/standby/onlinelog/group_101.584.776440777		NO

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       101	   ONLINE  +FRA/standby/onlinelog/group_101.2446.776440779		YES
       107	   ONLINE  +DATA/standby/onlinelog/group_107.615.776440733		NO
       107	   ONLINE  +FRA/standby/onlinelog/group_107.2271.776440737		NO
       108	   ONLINE  +DATA/standby/onlinelog/group_108.579.776440739		NO
       108	   ONLINE  +FRA/standby/onlinelog/group_108.3233.776440741		NO
       109	   ONLINE  +DATA/standby/onlinelog/group_109.580.776440743		NO
       109	   ONLINE  +FRA/standby/onlinelog/group_109.3296.776440743		NO
       110	   ONLINE  +DATA/standby/onlinelog/group_110.581.776440745		NO
       110	   ONLINE  +FRA/standby/onlinelog/group_110.2062.776440747		NO
       111	   ONLINE  +DATA/standby/onlinelog/group_111.582.776440747		NO
       111	   ONLINE  +FRA/standby/onlinelog/group_111.1055.776440747		NO

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       112	   ONLINE  +DATA/standby/onlinelog/group_112.583.776440749		NO
       112	   ONLINE  +FRA/standby/onlinelog/group_112.2643.776440751		NO
       113	   STANDBY +DATA/standby/onlinelog/group_113.646.778160003		NO
       113	   STANDBY +FRA/standby/onlinelog/group_113.2146.778160011		YES
       114	   STANDBY +DATA/standby/onlinelog/group_114.645.778160021		NO
       114	   STANDBY +FRA/standby/onlinelog/group_114.2608.778160023		YES
       115	   STANDBY +DATA/standby/onlinelog/group_115.644.778160091		NO
       115	   STANDBY +FRA/standby/onlinelog/group_115.1302.778160091		YES
       116	   STANDBY +DATA/standby/onlinelog/group_116.643.778160093		NO
       116	   STANDBY +FRA/standby/onlinelog/group_116.1414.778160093		YES
       117	   STANDBY +DATA/standby/onlinelog/group_117.642.778160095		NO

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       117	   STANDBY +FRA/standby/onlinelog/group_117.1628.778160103		YES
       118	   STANDBY +DATA/standby/onlinelog/group_118.641.778160107		NO
       118	   STANDBY +FRA/standby/onlinelog/group_118.1717.778160109		YES
       119	   STANDBY +DATA/standby/onlinelog/group_119.640.778160109		NO
       119	   STANDBY +FRA/standby/onlinelog/group_119.3505.778160111		YES
       120	   STANDBY +DATA/standby/onlinelog/group_120.639.778160113		NO
       120	   STANDBY +FRA/standby/onlinelog/group_120.3469.778160115		YES
       121	   STANDBY +DATA/standby/onlinelog/group_121.638.778160115		NO
       121	   STANDBY +FRA/standby/onlinelog/group_121.364.778160119		YES
       122	   STANDBY +DATA/standby/onlinelog/group_122.637.778160121		NO
       122	   STANDBY +FRA/standby/onlinelog/group_122.581.778160121		YES

    GROUP# STATUS  TYPE    MEMBER							IS_
---------- ------- ------- ------------------------------------------------------------ ---
       123	   STANDBY +DATA/standby/onlinelog/group_123.636.778160123		NO
       123	   STANDBY +FRA/standby/onlinelog/group_123.2196.778160125		YES
  • DataGuard Config File’s

Be sure that the DataGuard configuration is in the correct directory’s.

SQL> show parameter broker;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 /u01/app/oracle/product/11.2.0
						 .3/db_1/dbs/dr1STANDBY.dat
dg_broker_config_file2		     string	 /u01/app/oracle/product/11.2.0
						 .3/db_1/dbs/dr2STANDBY.dat
dg_broker_start 		     boolean	 TRUE

In this case I’m going to change the directory to ASM.

SQL> alter system set dg_broker_config_file1='+DATA/standby/dr1standby.dat' scope=both sid='*';

SQL> alter system set dg_broker_config_file2='+FRA/standby/dr2standby.dat' scope=both sid='*';

Check again

SQL> show parameter broker

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1		     string	 +DATA/standby/dr1STANDBY.dat
dg_broker_config_file2		     string	 +FRA/standby/dr1STANDBY.dat
dg_broker_start 		     boolean	 TRUE
SQL>

Be sure that this new configuration aplied in all the node’s!!!!!

  • Create DataGuard

Active the service in PRIMARY and STANDBY

SQL> alter system set dg_broker_start=true scope=both sid='*';

Test if an old configuration is activated

[oracle@11gnode1 dbs]$ dgmgrl
DGMGRL> connect /

DGMGRL> show configuration;

Configuration - OLDPRM

  Protection Mode: MaxPerformance
  Databases:
    OLDPRIM- Primary database
    OLDSTAN - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Remove any configuration:

DGMGRL> remove configuration;

Now create the configuration with DGMGRL

DGMGRL> create configuration 'PRIMARY' as primary database is 'PRIMARY' connect identifier is primary_dg;
Configuration "PRIMARY" created with primary database "PRIMARY"

Now STANDBY

DGMGRL> add database 'STANDBY' as connect identifier is standby_dg maintained as physical;
Database "STANDBY" added

Enable Configuration

DGMGRL> enable configuration;

Test it

DGMGRL> show configuration;

Configuration - PRIMARY

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now Primary database:

DGMGRL> show database verbose 'PRIMARY';

Database - PRIMARY

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARY1
    PRIMARY2

  Properties:
    DGConnectIdentifier             = 'primary_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/STANDBY/, +DATA/PRIMARY/, +FRA/STANDBY/, +FRA/PRIMARY/'
    LogFileNameConvert              = '+DATA/STANDBY/, +DATA/PRIMARY/, +FRA/STANDBY/, +FRA/PRIMARY/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

The Standby:

DGMGRL> show database verbose 'STANDBY';

Database - STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBY1 (apply instance)
    STANDBY2

  Properties:
    DGConnectIdentifier             = 'standby_dg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/PRIMARY/, +DATA/STANDBY/, +FRA/PRIMARY/, +FRA/STANDBY/'
    LogFileNameConvert              = '+DATA/PRIMARY/, +DATA/STANDBY/, +FRA/PRIMARY/, +FRA/STANDBY/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

Leave a Reply

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