Data Guard Broker

 Data Guard Broker Configuration:

===========================

dgmgrl of Data Guard Broker is a client of oracle binary assists to ensure high availability at database downtime situation. If primary database server is down permanently or not available then using this dgmgrl command prompt we can failover to standby database to reduce the downtime issue. Especially there are three exclusive feature switchover,failover,reinstate in data guard. dgmgrl can take action and monitor the status of data guard. Easily we can test our dr site activity using switchover to change database role at runtime to different connection identifier. Here it can be added that if dgmgrl configuration is enabled then physical standby database mrp process will be started automatically at mount mode.

step: 1 enable dg broker both in primary and standby server:

sql>ALTER SYSTEM SET dg_broker_start=true;

step: 2 dgbroker create configuration 

DGMGRL> CREATE CONFIGURATION cfg_name AS PRIMARY DATABASE IS primary_db CONNECT IDENTIFIER IS primary_db

step: 3 add standby db to dgbroker

DGMGRL> ADD DATABASE stbydb1 AS CONNECT IDENTIFIER IS stbydb1 MAINTAINED AS PHYSICAL;

DGMGRL> ADD DATABASE stbydb2 AS CONNECT IDENTIFIER IS stbydb2 MAINTAINED AS PHYSICAL;

The following error may be handled

Error: ORA-16796: one or more properties could not be imported from the database

solution: check whether data guard broker facility is on or not both in primary and physical standby db.

step:4 tune configuration status

DGMGRL> show configuration

step:5 enable configuration

step:6 monitor standby db

DGMGRL> show database stbydb1

note: connection identifier from the standby db tnsname.ora file

The following error can be raised. 

ORA-16714: the value of property ArchiveLagTarget is inconsistent with the member setting

Solution:

on standby

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE

--------- -------------------- ------------------------------ ----------------

stbydb1      MOUNTED              stbydb1DG                        PHYSICAL STANDBY

SQL> alter system set log_archive_max_processes=15 scope=both;

System altered.

SQL> alter system set archive_lag_target=0 scope=both;

System altered.

SQL>  alter system set log_archive_min_succeed_dest=1 scope=both;

System altered.

value of parameters should be same both in primary and physical standby db

#after change disable and enable the dgmgrl configuration

DGMGRL> disable configuration

DGMGRL> enable configuration

-->hands on:

#on/off mrp apply at any physical standby db

DGMGRL> edit database stydb set state ='LOG-APPLY-OFF';

#to trace error text and its severity level instance wise

DGMGRL> show database stbydb1 statusreport;

#to trace InconsistentProperties instancewise

DGMGRL> show database stbydb1 InconsistentProperties;

#check readiness for switchover

DGMGRL> validate database stbydb;

#Fast-Start Failover(FSFO):

Enable Fast-Start Failover

step: 1

enable fast_start failover;

step: 2

Start the observer;

#Switchover:

To change database role from physical standby to primary database.

 DGMGRL> switchover database to stby_db

#Failover:

  DGMGRL> failover database to stby_db

#warning:

standby redo logs not configured for thread 1 on dbdcicbs 

#solution:

ok please apply this 

alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;

alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby4.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby5.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby6.log' size 200M reuse;
alter database add standby logfile thread 1 '/u02/oradata/CDB1DG/stbylog/stby7.log' size 200M reuse;

#warning:

Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover 

#solution:

Single Instance Database For this type of database the static service registration
 is formatted as follows:

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

# Convert database

For resource & development for testing before deployment to production according to business requirement we need to convert database to physical standby to snapshot standby database.

DGMGRL> convert database testdb to snapshot standby;
DGMGRL> convert database testdb to physical standby;

Recovery Needed from SCN for Physical Standby

 Recovery Needed from SCN for Physical Standby:

Last week I fall in a scenario that my physical standby db is in unresolvable gap. I felt recovery is needed. Then I decide to recover the physical standby db from current SCN(System Change Number). I want to share with you the following steps to solve your problem.

#in primary:

----------

SQL> select name,database_role,switchover_status from v$database;

NAME      DATABASE_ROLE    SWITCHOVER_STATUS

--------- ---------------- --------------------

db_sid     PRIMARY          UNRESOLVABLE GAP

#in standby:

----------

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

  239220059

#in primary:

-----------

RUN

{

ALLOCATE CHANNEL CH2 TYPE DISK FORMAT 'path/incr_DF2_%d_%T_%s_%U.bkp';

SQL 'ALTER SYSTEM SWITCH LOGFILE';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN 239220059 DATABASE TAG BKP_INCR;

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'path/stby0309_%d_%T_%s_%U.ctl';

}

scp incr_DF2.bkp stby.ctl oracle@standby_db_ip:path

#in primary:

----------

SQL> show parameter control_files;

NAME                                 TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

control_files                        string

path01/control01.ctl, path02/control02.ctl

[oracle@TEST_DB dbs]$ cd path

[oracle@TEST_DB DRICBS]$ ls

control01.ctl

[oracle@TEST_DB DRICBS]$ mv control01.ctl control01.ctl.back

[oracle@TEST_DB ~]$ cd FRA

[oracle@TEST_DB recovery_area]$ ls

 control02.ctl 

[oracle@TEST_DB recovery_area]$ mv control02.ctl control02.ctl.back

#in standby:

-----------

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1287651328 bytes

Fixed Size                  8631192 bytes

Variable Size             713034856 bytes

Database Buffers          557842432 bytes

Redo Buffers                8142848 bytes

SQL>

RMAN> restore controlfile from path/stby.ctl' 

RMAN> alter database mount;

RMAN> recover database;

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

from primary:

-------------

SQL> set lines 300 pages 200;

SQL> select GAP_STATUS,TYPE,STATUS,DATABASE_MODE,RECOVERY_MODE,ERROR,SYNCHRONIZED from V$ARCHIVE_DEST_STATUS where DEST_ID=2;

GAP_STATUS               TYPE             STATUS    DATABASE_MODE   RECOVERY_MODE           ERROR                                                   SYN

------------------------ ---------------- --------- --------------- ----------------------- ----------------------------------------------------------------- ---

NO GAP                   PHYSICAL         VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY                                                         NO