How to setup Active Dataguard and Dataguard Broker

 

Primary Database : TIGGER

 DBNAME : TIGGER
 DB UNIQUE NAME : TIGGER
 Instances : TIGGER on dgprm.racattack
 Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
 GRID Homes:  /u01/app/oracle/product/12.1.0/grid

Standby Database : TIGGER_STBY
 DBNAME : TIGGER
 DB UNIQUE NAME : TIGGER_STBY
 Instances : TIGGER on dgstby.racattack
 Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
 GRID Homes:  /u01/app/oracle/product/12.1.0/grid


--*************************************************
-- Information about Primary Database:
--*************************************************

set linesize 1000
column host_name format a15
select host_name, instance_name, INSTANCE_ROLE, archiver, version, status, database_status  from v$instance
/

HOST_NAME       INSTANCE_NAME    INSTANCE_ROLE      ARCHIVE VERSION           STATUS       DATABASE_STATUS
--------------- ---------------- ------------------ ------- ----------------- ------------ -----------------
dgprm.racattack tigger           PRIMARY_INSTANCE   STOPPED 12.1.0.1.0        OPEN         ACTIVE


select name, db_unique_name, log_mode, force_logging, database_role from v$database;

NAME      DB_UNIQUE_NAME                 LOG_MODE     FORCE_LOGGING                           DATABASE_ROLE
--------- ------------------------------ ------------ --------------------------------------- ----------------
TIGGER    tigger                         NOARCHIVELOG NO                                      PRIMARY


select name from v$controlfile;

NAME
------------------------------------------------------------
+DATA/TIGGER/CONTROLFILE/current.261.856706525
+FRA/TIGGER/CONTROLFILE/current.256.856706525

select name from v$datafile;

NAME
------------------------------------------------------------
+DATA/TIGGER/DATAFILE/system.258.856706425
+DATA/TIGGER/DATAFILE/sysaux.257.856706319
+DATA/TIGGER/DATAFILE/undotbs1.260.856706491
+DATA/TIGGER/DATAFILE/users.259.856706489

select member from v$logfile;

MEMBER
------------------------------------------------------------
+DATA/TIGGER/ONLINELOG/group_3.264.856706541
+FRA/TIGGER/ONLINELOG/group_3.259.856706545
+DATA/TIGGER/ONLINELOG/group_2.263.856706535
+FRA/TIGGER/ONLINELOG/group_2.258.856706539
+DATA/TIGGER/ONLINELOG/group_1.262.856706531
+FRA/TIGGER/ONLINELOG/group_1.257.856706533

SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1tigger.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2tigger.dat
dg_broker_start                      boolean     FALSE


--*************************************************
-- 1. On the primary database enable force logging:
--*************************************************

SQL> alter database force logging;

You can confirm this by following query

SQL> select force_logging from V$DATABASE;

FOR
---
YES

--*************************************************
-- 2. Verify Archivelog Mode is enabled:
--*************************************************

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     52309
Next log sequence to archive   52310
Current log sequence           52310

SQL>

shutdown immediate;
startup mount
alter database archivelog
alter database open

--********************************************************************************
-- 3. Copy the Password File from the Primary database to all of the Standby nodes
--********************************************************************************

$ cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
$ scp orapwtigger oracle@dgstby:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwtigger

--********************************************************************
-- 4. Modify Dataguard related init Parameters on Primary
--********************************************************************

DB_NAME=TIGGER
DB_UNIQUE_NAME=TIGGER
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TIGGER,TIGGER_STBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TIGGER'
LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER_STBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=TIGGER_STBY
DB_FILE_NAME_CONVERT='TIGGER_STBY','TIGGER'
LOG_FILE_NAME_CONVERT= 'TIGGER_STBY','TIGGER'
STANDBY_FILE_MANAGEMENT=AUTO

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TIGGER,TIGGER_STBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TIGGER' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER_STBY' scope=both sid='*'; --- If your enabling the dataguard broker don't set this paramerter. The broker configuration does that for you. Verify the configuration afterwards.
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server='TIGGER_STBY' scope=both sid='*';
alter system set db_file_name_convert='TIGGER_STBY','TIGGER' scope=spfile sid='*';
alter system set log_file_name_convert='TIGGER_STBY','TIGGER' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';

Verify that the values are set correctly for these parameters after bouncing the database.

set linesize 1000 pages 0
col value for a100
col name for a70

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','db_file_name_convert',
                     'log_file_name_convert', 'standby_file_management')
/

--********************************************************************
-- 5. Configure Listeners on each site Primary and Standby
--********************************************************************

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=TIGGER_DGMGRL)
     (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
     (SID_NAME=tigger)
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=TIGGER_STBY_DGMGRL)
     (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
     (SID_NAME=tigger)
    )
  )



--********************************************************************
-- 6. Configure Oracle Networking on both Primary and Standby Nodes
--********************************************************************

TIGGER_DGMGRL =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dgprm)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = TIGGER_DGMGRL)))
)

TIGGER_STBY_DGMGRL =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dgstby)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = TIGGER_STBY_DGMGRL)))
)



--***************************
-- 6.1 Check DB connectivity
--***************************

Check that you are able to connect to database using tnsnames.ora entries

tnsping TIGGER_DGMRL

sqlplus sys@TIGGER as sysdba

Standby Site:

Add an oratab entry to make sure you have a SID to set the environment variables.

tigger:/u01/app/oracle/product/12.1.0/dbhome_1:N:             # line added by Agent

tnsping TIGGER_DGMGRL

sqlplus sys/racattack@TIGGER_DGMGRL as sysdba

sqlplus sys/racattack@TIGGER_STBY_DGMGRL as sysdba


--********************************************************************
-- 7. Duplicate the primary database to the standby site
--********************************************************************

--Please Note: Execute the duplicate from active database command from the Standby site to pull the files over the network.

--Create the adump directory first before running the rman duplicate command. If you don't the rman command will fail.
mkdir /u01/app/oracle/admin/tigger_stby/adump

Force the startup of a “Dummy” instance

rman target /
RMAN> startup nomount force
RMAN> exit

Connect to the target and auxiliary

$ rman target sys@TIGGER_DGMGRL auxiliary sys@TIGGER_STBY_DGMGRL

Perform the Active Duplicate using RMAN

RMAN> 

duplicate target database for standby from active database
spfile
parameter_value_convert 'tigger','tigger_stby'
set db_unique_name='tigger_stby'
set db_file_name_convert='tigger','tigger_stby'
set log_file_name_convert='tigger','tigger_stby'
set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TIGGER_STBY'
set LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_DGMGRL ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER' --- If your enabling the dataguard broker don't set this paramerter. The broker configuration does that for you. Verify the configuration afterwards.
set control_files='+DATA/TIGGER_STBY/CONTROLFILE/control01.ctl','+FRA/TIGGER_STBY/CONTROLFILE/control01.ctl'
set standby_file_management='AUTO'
set db_recovery_file_dest='+FRA'
set FAL_SERVER='TIGGER_DGMGRL'
set log_archive_max_processes='8'
set audit_file_dest='/u01/app/oracle/admin/tigger_stby/adump';

--*****************************************************************************************************************
-- 8.  Create standby redo logs on the primary and standby database that are the same size of the online redo logs. 
--*****************************************************************************************************************

Oracle recommends having the same number plus one additional standby redo log for each thread. 

For example: Minimum of (threads)*(groups Per Threads + 1) 

(3)*(2 + 1) = 9 Redo Logs

You can check the number and group numbers of the redo logs by querying the V$LOG view:
SQL> SELECT * FROM V$LOG;
You can check the results of the previous statements by querying the V$STANDBY_LOG view:
SQL> SELECT * FROM V$STANDBY_LOG;
You can also see the members created by querying the V$LOGFILE view:
SQL> SELECT * FROM V$LOGFILE;

SQL> select max (bytes)/1024/1024, count (1) from v$log;


    GROUP# BYTES/1024/1024
---------- ---------------
         1             250
         2             250
         3             250
         4             250
         5             250
         6             250

6 rows selected.

alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;

--*********************************************************
-- 9 Startup Active Dataguard from the Standby Database
--*********************************************************

SQL> startup mount
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select status,instance_name, database_role,open_mode from v$database, v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         tigger           PHYSICAL STANDBY READ ONLY

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

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING              27
ARCH      CLOSING              28
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         29

--*************************************************************************************
-- Do some log switches from the Primary database an confirm the results on both sides.
--*************************************************************************************

alter system switch logfile;

--*********************************************************
-- 10 Shutdown Active Dataguard from the Standby Database
--*********************************************************

SQL> alter database recover managed standby database cancel;

SQL>shutdown immediate;



--********************************************
-- 11 Configure Dataguard Broker
--********************************************

Primary: 

ASMCMD> mkdir +FRA/TIGGER/DATAGUARDCONFIG

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+FRA/TIGGER/DATAGUARDCONFIG/dr1TIGGER.dat' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/TIGGER/DATAGUARDCONFIG/dr2TIGGER.dat' SCOPE=BOTH sid='*';
alter system set dg_broker_start=true scope=both sid='*';

Standby: 

ASMCMD> mkdir +FRA/TIGGER_STBY/DATAGUARDCONFIG

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+FRA/TIGGER_STBY/DATAGUARDCONFIG/dr1TIGGER.dat' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/TIGGER_STBY/DATAGUARDCONFIG/dr2TIGGER.dat' SCOPE=BOTH sid='*';
alter system set dg_broker_start=true scope=both sid='*';


SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1tigger_stby.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2tigger_stby.dat
dg_broker_start                      boolean     FALSE
use_dedicated_broker                 boolean     FALSE
                                               
--*************************************************************************************************************
-- 11.1 Create the Data Guard configuration using the Primary site. This can only be done from the Primary site.
--*************************************************************************************************************

$> dgmgrl
DGMGRL> connect /
DGMGRL> create configuration tigger_dgb as primary database is tigger connect identifier is TIGGER_DGMGRL;
DGMGRL> add database tigger_stby as connect identifier is TIGGER_STBY_DGMGRL maintained as physical;

Enable the Data Guard configuration
DGMGRL> enable configuration;

--********************************
-- 11.2 Verify Configuration 
--********************************

DGMGRL> show configuration;
DGMGRL> show database verbose tigger
DGMGRL> show database verbose tigger_stby
DGMGRL> show instance verbose tigger on database tigger
DGMGRL> show instance verbose tigger on database tigger_stby

--*******************************************************
-- Useful commands to modify Dataguard Broker Properties
--*******************************************************

DGMGRL> edit database 'tigger' set property  'DGConnectIdentifier' = 'TIGGER_DGMGRL'
DGMGRL> edit database 'tigger' set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TIGGER_DGMGRL)(INSTANCE_NAME=tigger)(SERVER=DEDICATED)))'
DGMGRL> edit database 'tigger_stby' set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstby.racattack)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TIGGER_STBY_DGMGRL)(INSTANCE_NAME=tigger)(SERVER=DEDICATED)))'
DGMGRL> show database tigger InconsistentProperties
DGMGRL> show database tigger_stby InconsistentProperties

SQL> alter system set archive_lag_target=0 scope=both sid='*';
SQL> alter system set log_archive_min_succeed_dest=1 scope=both sid='*';

Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting

--***************************************************
-- Some useful My Oracle Support notes and websites:
--***************************************************

Creating a Standby using RMAN Duplicate (RAC or Non-RAC) (Doc ID 1617946.1)

Doc ID 241438.1 - Script to Collect Data Guard Physical Standby Diagnostic Information
Doc ID 241374.1 - Script to Collect Data Guard Primary Site Diagnostic Information
Doc ID 316740.1 - How to configure Client Failover after Data Guard Switchover or Failover
Doc ID 1199943.1 - Data Guard Physical Standby Changing the SYS password when a broker configuration exists
Doc ID 278641.1 - How do you apply a Patchset, PSU or CPU in a Data Guard Physical Standby configuration
Doc ID 730361.1 - Changing the network used by the Data Guard Broker Version 10.2 for redo transport
Doc ID 1349977.1 - Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network

http://www.oracle.com/au/products/database/dataguard11g-rac-maa-1-134639.pdf
http://askdba.org/weblog/oracle11g/dataguard/11gr2-rac-dataguard-setup-using-asm/
http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimaryracphysicalsta-131940.pdf
http://wiki.tapriuneclak.com/index.php?title=Oracle_-_Step_By_Step_of_Configuring_Oracle_11gR2_(11.2.0.1)_RAC_to_RAC_Dataguard
http://www.oracle.com/technetwork/database/features/availability/dataguardoverview-083155.html
http://www.oracle.com/technetwork/database/features/availability/oracle-database-maa-best-practices-155386.html

Oracle Data Guard Concepts and Administration 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e25608/toc.htm

Oracle® Database High Availability Best Practices 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e10803/toc.htm

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Oracle Data Guard Broker 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e17023/toc.htm

DGMGRL> show configuration;

Configuration - tigger_dgb

  Protection Mode: MaxPerformance
  Databases:
  tigger      - Primary database
    tigger_stby - Physical standby database
      Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

09/11/2014 16:13:06
Failed to connect to remote database tigger. Error is ORA-12154
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
Failed to send message to site tigger. Error code is ORA-16501.

DGMGRL> show configuration

Configuration - tigger_dgb

  Protection Mode: MaxPerformance
  Databases:
  tigger      - Primary database
    tigger_stby - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database tigger_stby InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
          tigger     ArchiveLagTarget                    0                                         0
          tigger LogArchiveMinSucceedDest                    1                                         1

DGMGRL> show database tigger InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE



--**************************************************************
-- 9 Modified Startup Active Dataguard from the Standby Database
--**************************************************************

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

SQL> select status,instance_name, database_role,open_mode from v$database, v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         tigger           PHYSICAL STANDBY READ ONLY

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING              27
ARCH      CLOSING              28
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         29

--*************************************************************************************
-- Do some log switches from the Primary database an confirm the results on both sides.
--*************************************************************************************

alter system switch logfile;

--*********************************************************
-- 10 Shutdown Active Dataguard from the Standby Database
--*********************************************************

SQL> alter database recover managed standby database cancel;

SQL>shutdown immediate;