Create Oracle Data Guard in 11g R2 with Data Guard Broker and DGMGRL


In this example we create a physical standby, which is administered by dgmgrl. The setup of an Active Data Guard is a simple procedure, which does not differ much from a 11G Data Guard setup.

The main advantages of 11G Dataguard are:

  • The ability to open the standby database in read-only mode, while they continue in managed recovery mode runs. This means that it can be used to for high availability purposes, and others for regular reporting purposes.
  • The possibility to open the database in read-write mode and eg Application tests. Through the snapshot standby functionality it can be downgraded back to normal standby mode.

In this example, I start with the following things:
server1.exampe.com (Primary Server)
server2.example.com (Oracle binaries installed no database)
server1.example.com is in archivelog mode

Configuring the database so that it can support the role of a primary database.

SQL> alter database add standby logfile;
SQL> alter database force logging;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbm,dbm_stby)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dbmstby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBMSTBY';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE

Create net service names in the tnsnames.ora

DBM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbm)
    )
  )

DBM_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbm_DGMGRL)
    )
  )
DBMSTBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server2.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbmstby)
    )
  )

DBMSTBY_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server2.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbmstby_DGMGRL)
    )
  )

On the new standby site and on the primary side, we add a static listener registration.

    (SID_DESC =
      (GLOBAL_DBNAME = dbm)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = dbm)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dbmstby)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = dbmstby)
    )

The next steps are that we copy the password file from the primary site to the standby site. Furthermore, we create a file initdbmstby.ora containing only the parameter db_name = dbmstby. Also, make sure that the directories for the audit dump destination and the Fast Recovery Area are available. Once all steps are completed, we start the database „nomount“ state and on the primary side, we run the rman utility

RMAN> connect target sys
RMAN> connect auxiliary sys@dbm_stby

After logging in to „rman“ we run the following command:

run {
        allocate channel prim1 type disk;
        allocate channel prim2 type disk;
        allocate channel prim3 type disk;
        allocate channel prim4 type disk;
        allocate auxiliary channel stby type disk;
        duplicate target database for standby from active database
        spfile
         parameter_value_convert 'dbm','dbmstby'
         set db_unique_name='dbmstby'
         set db_file_name_convert='/u01/app/oracle/oradata/dbm','/u01/app/oracle/oradata/dbmstby'
         set log_file_name_convert='/u01/app/oracle/oradata/dbm','/u01/app/oracle/oradata/dbmstby'
         set log_archive_max_processes='5'
         set fal_client='dbmstby'
         set fal_server='dbm'
         set standby_file_management='AUTO'
         set log_archive_config='dg_config=(dbm,dbmstby)'
         set log_archive_dest_1='service=dbm_ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dbm';
        }

After rman finished creating the database, we check whether the redo transport works.

On the standby database, we select the current sequence#
select sequence#,first_time,next_time
from v$archived_log
order by sequence#;
Creating a new archive log file on the primary site, thereby increasing the sequence#
alter system switch logfile;
Re-examine the sequence # which must have now increased.
select sequence#,first_time,next_time
from v$archived_log
order by sequence#;

Next we setup the Data Guard Broker. Set the database parameter dg_broker_start to true .

alter system set dg_broker_start=true;

This causes the following processes are started on the database:

  • Data Guard Monitor (DMON)
  • Broker Resource Manager (RSM)
  • Data Guard Net Server (NVSn)
  • DRCn

The next step is to expand our listener configuration.

    (SID_DESC =
      (GLOBAL_DBNAME = dbm_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = dbm)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dbmstby_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = dbmstby)
    )

The next step is to take the database in the broker configuration. Therefore we start the CLI (dgmgrl) on the primary side.

Connect to the database
DGMGRL> connect sys
Create a Broker configuration including a profile for the primary database
DGMGRL> create configuration 'DGConfig1' as primary database is 'dbm' connect identifier is dbm_DGMGRL;
Add the Standby database
DGMGRL> add database 'dbmstby' as connect identifier is dbmstby_DGMGRL;
Activate the configuration
DGMGRL> enable configuration
Check your configuration
DGMGRL> show configuration

Let us see now how we can work with Active Data Guard database. The setup is relatively simple. After we open the standby database „read only“ we will start the Apply again, and we will see the transactions of the primary side still further to the standby database „applied“ are.

Stop the apply
DGMGRL> edit database 'dbmstby' set state ='apply-off';
Open the Standby Site read-only
SQL> alter database open read only;
Start the apply
DGMGRL> edit database 'dbmstby' set state ='apply-on'

Test your configuration

Create a table on the primary site
SQL> conn scott/tiger
Connected.
SQL> create table emptest as select * from emp;

Table created.
Check your Standby Site if the table is recoverd
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emptest;

  COUNT(*)
----------
    14

For Enabling Fast Start Failover (FSFO) Flashback is a prerequisite to the database.

SQL> alter database flashback on;
DGMGRL> edit database 'dbmstby' set state ='apply-off';
SQL> alter database flashback on;
DGMGRL> edit database 'dbmstby' set state ='apply-on';
DGMGRL> ENABLE FAST_START FAILOVER

When FSFO different condition can be  passed, it can for example a simple ORA code or a event.

ENABLE FAST_START FAILOVER CONDITION 27102;
DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           dbmstby
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

Well now that we have a standby database, we also want to test the switchover. If we have no lag in the our setup, we can execute the following commands.

Switchover to standby site
DGMGRL> switchover to dbmstby;
Check the configuration
DGMGRL> show configuration
SQL> select database_role from v$database;
Switchback the role
DGMGRL> switchover to dbm;
DGMGRL> show configuration
SQL> select database_role from v$database;

From this point, we can also start a observer (on a third host), which monitors the configuration and possibly engages.

dgmgrl -logfile $HOME/observer.log sys/<Passwort>@dbm_DGMGRL "start observer" &

I you are searching for something similar in a Oracle Standard Edition environment checkout the DBSentinel.

Conclusion:
Dataguard is a cool feature in a Oracle Enterprise Edition High Availibty environment, which makes many things smart forward for the administrator. But be carefull that the Active Dataguard Option must be licensed separately from Oracle and thats quite easy to activated it.

Reference:

Blog from Guenadi Jilevski

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s