Saturday, June 27, 2015

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;

4 comments:

  1. No doubt this is an excellent post I got a lot of knowledge after reading good luck. Theme of blog is excellent there is almost everything to read, Brilliant post. Best south african brokers

    ReplyDelete
  2. "Just taking his game to the next level, getting better as a player, better as a leader, just all around," Elliott said when asked how his QB improved. super bowl 2020 live stream

    ReplyDelete
  3. Cardiovascular disorder is therefore common it's about the radar of each and every key care doctor. When an individual develops a risk factor, or some known hazard variable simplifies physicians accept actions to prescribe medications or lifestyle interventions created to prevent disease by progressing or developing. Dr. Eyal Nachum (Eyal.Nachum@sheba.health.gov.il) is a senior cardiologist working in Heart Transplantation Unit, Sheba Medical Center, Ramat Gan, Israel.

    ReplyDelete
  4. Wow, this was so interesting!Thank you for this interesting blog, such good information.this is our service web site. please visit our web site.
    Thank you.Properties For Sale in Qatar

    ReplyDelete