Blog Post

  1. Blog
  2. Oracle 11.2.0.4 Dataguard Setup

Oracle 11.2.0.4 Dataguard Setup

Oracle Data Guard is a high-availability solution provided by Oracle for creating and maintaining standby databases. It is designed to ensure the availability, reliability, and data protection of Oracle databases. Data Guard provides a comprehensive set of tools, services, and features to automate the management, monitoring, and maintenance of a standby database environment.

Data Guard works by maintaining one or more standby databases that are synchronized with a primary database. The primary database is the production database that handles transactions and serves user requests, while the standby database(s) act as replicas and remain synchronized with the primary database by applying redo logs received from the primary database.

The standby database can be used for various purposes, such as disaster recovery, reporting, backups, or offloading resource-intensive operations from the primary database. In case of a primary database failure, Data Guard can automatically failover to a standby database, minimizing downtime and ensuring business continuity.

DGMGRL (Data Guard Manager Command-Line Interface) is a command-line tool provided by Oracle for managing and monitoring Data Guard configurations. It is used to perform administrative tasks, configure and monitor the Data Guard environment, and automate failover and switchover operations.

Using DGMGRL, you can perform operations such as creating and configuring a Data Guard broker configuration, adding or removing standby databases, monitoring the status of the primary and standby databases, initiating failover or switchover operations, and managing the overall Data Guard configuration.

DGMGRL provides a command-line interface as well as a scriptable API, allowing administrators to automate Data Guard operations and integrate them into scripts or third-party management tools.

Overall, Oracle Data Guard and DGMGRL work together to create a robust and highly available database environment, ensuring data protection, disaster recovery, and efficient utilization of resources.

GENERAL STEP TO SETUP ORACLE DATAGUARD AND CONFIGURE DATAGUARD BROKER (DGMGRL)

  1. Make database run in Archivelog mode if it is not already running.
  2. Enable FORCE_LOGGING
  3. Add STANDBY REDO LOG files
  4. Configure Dataguard Parameters
  5. Create Password file and Parameter file and copy to standby side
  6. Configure tnsnames.ora and listener.ora
  7. Startup Standby instance.
  8. Run RMAN DUPLICATE command 
  9. Start syncing primary and standby database
  10. Modify and remove unnecessary entries from listener.ora and tnsnames.ora
  11. dgmgrl configuration

STEP 1: Make database run in Archivelog mode if it is not already running.

STEP 2: Enable FORCE_LOGGING

[oracle@rac1prod ~]$ srvctl status database -d LNTR1P -v
Instance LNTR1P1 is running on node rac1prod. Instance status: Open.
Instance LNTR1P2 is running on node rac2prod. Instance status: Open.
[oracle@rac1prod ~]$ srvctl stop database -d LNTR1P
[oracle@rac1prod ~]$ srvctl start database -d LNTR1P -o mount

SQL> alter database archivelog;

Database altered.

SQL> alter system set db_recovery_file_dest_size=9g scope=both sid='*';

System altered.

SQL> alter system set db_recovery_file_dest='+FRA' scope=both sid='*';

System altered.

SQL> alter database force logging;

Database altered.


[oracle@rac1prod ~]$ srvctl stop database -d LNTR1P
[oracle@rac1prod ~]$ srvctl start database -d LNTR1P

STEP 3: Add STANDBY REDO LOG files

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

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         2         ONLINE  +DATA/lntr1p/onlinelog/group_2.285.1138240147
         1         ONLINE  +DATA/lntr1p/onlinelog/group_1.286.1138240147
         3         ONLINE  +DATA/lntr1p/onlinelog/group_3.281.1138240205
         4         ONLINE  +DATA/lntr1p/onlinelog/group_4.280.1138240205

SQL> select group#,thread#,bytes/1024/1024 Size_MB from v$log;

    GROUP#    THREAD#    SIZE_MB
---------- ---------- ----------
         1          1         50
         2          1         50
         3          2         50
         4          2         50

SQL>

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA','+DATA') SIZE 50m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATA','+DATA') SIZE 50m;


ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+DATA','+DATA') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 12 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 13 ('+DATA','+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 ('+DATA','+DATA') SIZE 50M;


SQL> select group#,status,type,member from v$logfile order by group#;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         1         ONLINE  +DATA/lntr1p/onlinelog/group_1.286.1138240147
         2         ONLINE  +DATA/lntr1p/onlinelog/group_2.285.1138240147
         3         ONLINE  +DATA/lntr1p/onlinelog/group_3.281.1138240205
         4         ONLINE  +DATA/lntr1p/onlinelog/group_4.280.1138240205
         5         ONLINE  +DATA/lntr1p/onlinelog/group_5.278.1138241623
         5         ONLINE  +DATA/lntr1p/onlinelog/group_5.277.1138241623
         6         ONLINE  +DATA/lntr1p/onlinelog/group_6.276.1138241623
         6         ONLINE  +DATA/lntr1p/onlinelog/group_6.275.1138241623
         7         STANDBY +DATA/lntr1p/onlinelog/group_7.274.1138241623
         7         STANDBY +DATA/lntr1p/onlinelog/group_7.273.1138241623
         8         STANDBY +DATA/lntr1p/onlinelog/group_8.272.1138241623
         8         STANDBY +DATA/lntr1p/onlinelog/group_8.271.1138241623
         9         STANDBY +DATA/lntr1p/onlinelog/group_9.270.1138241623
         9         STANDBY +DATA/lntr1p/onlinelog/group_9.269.1138241623
        10         STANDBY +DATA/lntr1p/onlinelog/group_10.267.1138241623
        10         STANDBY +DATA/lntr1p/onlinelog/group_10.266.1138241623
        11         STANDBY +DATA/lntr1p/onlinelog/group_11.262.1138241623
        11         STANDBY +DATA/lntr1p/onlinelog/group_11.261.1138241623
        12         STANDBY +DATA/lntr1p/onlinelog/group_12.265.1138241625
        12         STANDBY +DATA/lntr1p/onlinelog/group_12.264.1138241625
        13         STANDBY +DATA/lntr1p/onlinelog/group_13.259.1138241625
        13         STANDBY +DATA/lntr1p/onlinelog/group_13.258.1138241625
        14         STANDBY +DATA/lntr1p/onlinelog/group_14.257.1138241625
        14         STANDBY +DATA/lntr1p/onlinelog/group_14.256.1138241625

24 rows selected.

SQL> select group#,thread#,bytes/1024/1024 Size_MB from v$log;

    GROUP#    THREAD#    SIZE_MB
---------- ---------- ----------
         1          1         50
         2          1         50
         3          2         50
         4          2         50
         5          1         50
         6          2         50

6 rows selected.

SQL>

STEP 4: Configure Dataguard Parameters

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(LNTR1P,LNTR1P_STBY)' sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=LNTR1P_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LNTR1P_STBY' sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER='LNTR1P_STBY' sid='*';

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT='LNTR1P' sid='*';

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';

System altered.

SQL>

set lines 999 pages 999
col value for a110
col name for a30
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','fal_client','standby_file_management','db_recovery_file_dest','service_names');

STEP 5: Create Password file and Parameter file and copy to standby side

SQL> create pfile='/tmp/initLNTR1P.ora' from spfile;

File created.

SQL>

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y
orapwd file=$ORACLE_HOME/dbs/orapwLNTR1P1 password=oracle entries=5 force=y

[oracle@rac2prod dbs]$ cp orapwLNTR1P1 orapwLNTR1P2


copy password file and init file to stadby node

[oracle@rac1prod dbs]$ scp /tmp/initLNTR1P.ora rac1stby:/tmp/initLNTR1P.ora
oracle@rac1stby's password:
initLNTR1P.ora                                                                                                  100% 1609     2.1MB/s   00:00
[oracle@rac1prod dbs]$ scp orapwLNTR1P1 rac1stby:/tmp/orapwLNTR1P1
oracle@rac1stby's password:
orapwLNTR1P1                                                                                                     100% 1536     1.6MB/s   00:00
[oracle@rac1prod dbs]$

STEP 6: Configure tnsnames.ora and listener.ora

Make sure same tnsnames. ora entries should be there in both node of primary and one active node of standby database. You should be able to connect to both instances( or ideal instance) with this tns-entry.

In my environment, I had same tnsnames.ora in rac1prod, rac2prod and rac1stby servers. I am not yet started configuring tnsnames.ora in rac2stby server, and will configure it once database is in sync with primary.

For LNTR1P_STBY tns entry, I had provided HOST as rac1stby, which is hostname of that server, instead of scan name, I am using different port as well. Moreover, I am specifying SID instead of SERVICE_NAME

[oracle@rac1prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LNTR1P =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racprod-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LNTR1P)
    )
  )

LNTR1P_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1stby)(PORT = 1525))
    (CONNECT_DATA =
      (SID = LNTR1P1)
      (UR=A)
    )
  )

In listener.ora file add entry for static database registration as below,

Here, we are updating listener.ora file from GRID_HOME and in this entry we are specifying ORACLE_HOME as database home for our primary database.

[oracle@rac1prod admin]$ pwd
/u01/app/11.2.0/grid_1/network/admin

cat listener.ora ( ORACLE home path need to be specified)

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

Standby side LISTENER.ora file:

Here, we are configuring listener.ora file in standby server from ORACLE_HOME, not GRID_HOME

We do have a LISTENER configuration in GRID_HOME as well.

[oracle@rac1stby admin]$ pwd 
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1stby admin]$ cat listener.ora
SID_LIST_LISTENER1 =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = LNTR1P_STBY)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
   (SID_NAME = LNTR1P1)
  )
 )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1stby)(PORT = 1525))
    )
  )

[oracle@rac1stby admin]$ pwd
/u01/app/11.2.0/grid/network/admin
[oracle@rac1stby admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = LNTR1P_STBY)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                (SID_NAME = LNTR1P1)
        )

)

STEP 7: Startup Standby instance

Copy init file and password file in all 3 remaining nodes

Make sure you are creating directory for audit trail on both the standby node.

[oracle@rac1stby tmp]$ cp /tmp/orapwLNTR1P1 $ORACLE_HOME/dbs/orapwLNTR1P1
[oracle@rac1stby tmp]$ cp /tmp/initLNTR1P.ora $ORACLE_HOME/dbs/initLNTR1P.ora

[oracle@rac1stby dbs]$ mkdir -p /u01/app/oracle/admin/LNTR1P/adump
[oracle@rac1stby admin]$ ssh rac2stby mkdir -p /u01/app/oracle/admin/LNTR1P/adump


[oracle@rac1stby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 31 03:30:26 2023

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initLNTR1P.ora'
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2255632 bytes
Variable Size             671089904 bytes
Database Buffers         1728053248 bytes
Redo Buffers               20426752 bytes
SQL>

STEP 8: Run RMAN DUPLICATE command

Run RMAN duplicate command after connecting with primary and auxiliary instance.

After duplicate command completed create spfile for instance.

[oracle@rac1stby dbs]$ rman target sys@LNTR1P auxiliary sys@LNTR1P_STBY/oracle

Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 31 03:34:10 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: LNTR1P (DBID=914852776)
connected to auxiliary database: LNTR1P (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;

Starting Duplicate Db at 31-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=194 instance=LNTR1P1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwLNTR1P2' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwLNTR1P1'   ;
}
executing Memory Script

Starting backup at 31-MAY-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=142 instance=LNTR1P2 device type=DISK
Finished backup at 31-MAY-23

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/lntr1p_stby/controlfile/current.290.1138246479';
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''+DATA/lntr1p_stby/controlfile/current.290.1138246479'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Starting backup at 31-MAY-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_LNTR1P2.f tag=TAG20230531T033439 RECID=2 STAMP=1138246480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-MAY-23

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2421825536 bytes

Fixed Size                     2255632 bytes
Variable Size                687867120 bytes
Database Buffers            1711276032 bytes
Redo Buffers                  20426752 bytes

sql statement: alter system set  control_files =   ''+DATA/lntr1p_stby/controlfile/current.290.1138246479'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2421825536 bytes

Fixed Size                     2255632 bytes
Variable Size                687867120 bytes
Database Buffers            1711276032 bytes
Redo Buffers                  20426752 bytes

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   datafile  6 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 31-MAY-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/lntr1p/datafile/system.268.1138240097
output file name=+DATA/lntr1p_stby/datafile/system.289.1138246511 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/lntr1p/datafile/sysaux.260.1138240097
output file name=+DATA/lntr1p_stby/datafile/sysaux.288.1138246515 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/lntr1p/datafile/example.283.1138240149
output file name=+DATA/lntr1p_stby/datafile/example.287.1138246517 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/lntr1p/datafile/undotbs1.263.1138240099
output file name=+DATA/lntr1p_stby/datafile/undotbs1.286.1138246519 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/lntr1p/datafile/undotbs2.282.1138240195
output file name=+DATA/lntr1p_stby/datafile/undotbs2.285.1138246519 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/lntr1p/datafile/users.288.1138240099
output file name=+DATA/lntr1p_stby/datafile/users.284.1138246521 tag=TAG20230531T033511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-MAY-23

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/system.289.1138246511
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/sysaux.288.1138246515
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/undotbs1.286.1138246519
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/users.284.1138246521
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/example.287.1138246517
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=1138246522 file name=+DATA/lntr1p_stby/datafile/undotbs2.285.1138246519
Finished Duplicate Db at 31-MAY-23

RMAN> 


SQL> create pfile from spfile;

File created.

STEP 9: Start syncing primary and standby database

Modify below parameter in newly created pfile. Copy this file to node 2 and start 2nd standby instance as well.

*.cluster_database_instances=2
*.cluster_database=true
LNCR1P1.instance_number=1
LNCR1P2.instance_number=2
LNCR1P1.instance_name='LNCR1P1'
LNCR1P2.instance_name='LNCR1P2'
LNCR1P1.thread=1
LNCR1P2.thread=2
LNCR1P1.undo_tablespace='UNDOTBS1'
LNCR1P2.undo_tablespace='UNDOTBS2'

[oracle@rac1stby dbs]$ scp initLNTR1P1.ora rac2stby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initLNTR1P2.ora
initLNTR1P1.ora                                                                                                 100% 6962     7.0MB/s   00:00

[oracle@rac1stby dbs]$ srvctl add database -d LNTR1P_STBY -o /u01/app/oracle/product/11.2.0/dbhome_1 -r physical_standby -n LNTR1P -a DATA,FRA
[oracle@rac1stby dbs]$ srvctl add instance -d LNTR1P_STBY -i LNTR1P1 -n rac1stby
[oracle@rac1stby dbs]$ srvctl add instance -d LNTR1P_STBY -i LNTR1P2 -n rac2stby

sqlplus / as sysdba
startup mount

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.
STEP 10: Modify and remove unnecessary entries from listener.ora and tnsnames.ora

Make sure you have same tnsentry on all 4 nodes, 2 primary and 2 standby, please notice the changes we made.

Remove the entry made in standby node-one in listener.ora of ORACLE_HOME

[oracle@rac1prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LNTR1P =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racprod-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LNTR1P)
    )
  )


LNTR1P_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racstby-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LNTR1P_STBY)
    )
  )

STEP 11: dgmgrl configuration

Add intry in GRID listener.ora in all 4 nodes, 2 primary and 2 standby

(SID_DESC =
                (GLOBAL_DBNAME = LNTR1P_DGMGRL)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                (SID_NAME = lntr1p1/2)
        )
		
		
(SID_DESC =
                (GLOBAL_DBNAME = LNTR1P_STBY_DGMGRL)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                (SID_NAME = LNTR1P1/2)
        )

check parameter values are set for dg_broker_config_file1, dg_broker_config_file2,dg_broker_start, in primary and standby

asmcmd 
cd +data/LNTR1P/
mkdir DGCONFIG

On primary:
ALTER SYSTEM SET dg_broker_config_file1='+DATA/LNTR1P/DGCONFIG/dgconfig1.ora' SCOPE=BOTH SID='*';
ALTER SYSTEM SET dg_broker_config_file2='+DATA/LNTR1P/DGCONFIG/dgconfig2.ora' SCOPE=BOTH SID='*';

On standby
ALTER SYSTEM SET dg_broker_config_file1='+DATA/LNTR1P_STBY/DGCONFIG/dgconfig1.ora' SCOPE=BOTH SID='*';
ALTER SYSTEM SET dg_broker_config_file2='+DATA/LNTR1P_STBY/DGCONFIG/dgconfig2.ora' SCOPE=BOTH SID='*';

SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
dg_broker_config_file1               string      +DATA/lntr1p/dgconfig/dgconfig
                                                 1.ora
dg_broker_config_file2               string      +DATA/lntr1p/dgconfig/dgconfig
                                                 2.ora
dg_broker_start

Configuration commands

[oracle@rac1prod admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@LNTR1P
Password:
Connected.
DGMGRL> create configuration 'LNTR1P_DGMGRL' as primary database is 'LNTR1P' connect identifier is LNTR1P;
Configuration "LNTR1P_DGMGRL" created with primary database "LNTR1P"
DGMGRL> ADD DATABASE 'LNTR1P_STBY' AS CONNECT IDENTIFIER IS LNTR1P_STBY;
Database "LNTR1P_STBY" added
DGMGRL> show configuration

Configuration - LNTR1P_DGMGRL

  Protection Mode: MaxAvailability
  Databases:
    LNTR1P      - Primary database
    LNTR1P_STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL>

Oracle DBA

Experienced OCM-certified Oracle Database Administrator with over 18 years of expertise in designing, implementing, and managing complex database solutions. My expertise spans performance optimization, security, and high-stakes solution implementation. Adept at managing complex environments with precision.

No Comments

    Leave a Message

    Your email address will not be published. All fields are mandatory. **

    Learn OCI in Hindi

    Level up your cloud career with OCI expertise tailored for AWS, Azure, and GCP professionals.

    • For AWS Architects
    • For GCP Architects
    • For Azure Architects

    Three dedicated courses launching shortly.

    Register for Early Access






    Search

    Recent Posts

    Categories

    Tags

    Archives