2012/07/20

Oracle Data Guard 11g – Using DBMS_DG


Oracle Database 11g  Release 1 onward new package - DBMS_DG. Package contains a procedure INITIATE_FS_FAILOVER, yet. This procedure for using to specify a condition string that, when encountered by an application, allows the application to request the primary database to immediately invoke a fast-start failover.
Syntax
DBMS_DG.INITIATE_FS_FAILOVER (condstr IN VARCHAR2)
RETURN BINARY_INTEGER;

condstr - parameter
Specifies the condition string for which a fast-start failover should be requested. If no condition string argument is supplied, the default string of "Application Failover Requested" will be logged in the broker log file and in the database alert log of the primary database.

There are conditions detectable by applications running outside of the Oracle database that may warrant the Oracle Data Guard broker to perform a fast-start failover. Because the range of possible conditions is virtually unlimited, it is left to the applications to determine which conditions warrant a fast-start failover.
When such conditions occur, the application calls the DBMS_DG.INITIATE_FS_FAILOVER procedure to alert the primary database that the application wants a fast-start failover to occur immediately. The primary database then notifies the observer, which immediately initiates a fast-start failover as long as the standby database is in a valid fast-start failover state ("observed" and either "synchronized" or "within lag") to accept a failover.If the configuration is not in a valid fast-start failover state, the INITIATE_FS_FAILOVER subprogram returns an ORA error message (it will not signal an exception) to inform the calling application that a fast-start failover could not be performed.

We can use DBMS_DG package with PL/SQL block. For example :
1. Have a Broker-managed Data Guard Configuration DG, Fast Start Failover is started

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
      Error: ORA-16820: fast-start failover observer is no longer observing this
 database

    stldb - (*) Physical standby database
      Error: ORA-16820: fast-start failover observer is no longer observing this
 database

Fast-Start Failover: ENABLED

Configuration Status:
ERROR

DGMGRL> start observer
Observer started


and now observing both databases.

DGMGRL> show configuration

Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    stldb - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

2. Connect as user SYS  with SYSDBA privileges to SQL*Plus Command Line tool, and call following PL/SQL block.

SET SERVEROUTPUT ON
DECLARE
 v_status INTEGER;
 v_error  VARCHAR2(300);
BEGIN
  v_status := dbms_dg.initiate_fs_failover('Application Failover Requested');
  SELECT 
     DECODE(v_status,0,'normal, successful completion',
               16646,'Fast-Start Failover is disabled',
               16666,'unable to initiate Fast-Start Failover on a standby database',
               16817,'unsynchronized Fast-Start Failover configuration',
               16819,'Fast-Start Failover observer not started',
               16820,'Fast-Start Failover observer is no longer observing this database',
               16829,'lagging Fast-Start Failover') INTO v_error
   FROM dual; 
  dbms_output.put_line('Actual Status = ORA-' ||v_status||' : '||v_error);
END;
/

Result
Actual Status = ORA-0 : normal, successful completion
PL/SQL procedure successfully completed.


3. Observer is performing failover

DGMGRL> start observer
Observer started

11:31:01.36  Friday, July 20, 2012
Initiating Fast-Start Failover to database "stldb"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "stldb"
11:31:06.70  Friday, July 20, 2012


Table INITIATE_FS_FAILOVER Procedure Errors
ErrorDescription
ORA-00000: normal, successful completion
The request to initiate a fast-start failover has been posted to the observer.
ORA-16646: Fast-Start Failover is disabled
Either a broker configuration does not exist or fast-start failover has not been enabled.
ORA-16666: unable to initiate Fast-Start Failover on a standby database
DBMS_DG.INITIATE_FS_FAILOVER was invoked on a standby site.
ORA-16817: unsynchronized Fast-Start Failover configuration
DBMS_DG.INITIATE_FS_FAILOVER was invoked in a maximum available fast-start failover configuration when the configuration was not synchronized.
ORA-16819: Fast-Start Failover observer not started
DBMS_DG.INITIATE_FS_FAILOVER was invoked but an observer had not yet been started.
ORA-16820: Fast-Start Failover observer is no longer observing this database
DBMS_DG.INITIATE_FS_FAILOVER was invoked but the configuration detects that the observer may not be running.
ORA-16829: lagging Fast-Start Failover configuration
DBMS_DG.INITIATE_FS_FAILOVER was invoked in a maximum performance fast-start failover configuration when the configuration was not in the user-specified redo lag limit.



Regards
Mahir M. Quluzade

2012/07/17

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?


When you need safely remove broker-managed Data Guard Configuration then, oracle recommend following steps…

You know dg_broker_start parameter is TRUE and data guard configuration information contains in broker configuration files dg_broker_config_file1, dg_broker_config_file2 with broker-managed Data Guard Configuration.

Following steps I use on my test machines witch is running on Oracle Virtual Box. I captures video on this machines, too. Primary and Standby Database running on Oracle Linux Server 6.2 and installed Oracle Database 11g Release 2 (11.2.0.3).

DG – is my breoker-managed Data Guard configuration. admdb is primary database, stldb – is standby database.

DGMGRL> show configuration
Configuration - dg

  Protection Mode: MaxPerformance
  Databases:
    admdb - Primary database
    stldb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Archive log destinations like as

SQL> show parameter log_archive_dest_

NAME                 TYPE       VALUE
-------------------- ----------- -------------
log_archive_dest_1  string     location=USE_DB_RECOVERY_FILE_
                               DEST, valid_for=(ALL_LOGFILES,
                               ALL_ROLES)
...
log_archive_dest_2  string     service="stldb", LGWR ASYNC NO
                               AFFIRM delay=0 optional compre
                               ssion=disable max_failure=0 ma
                               x_connections=1 reopen=300 db_
                               unique_name="stldb" net_timeou
                               t=30, valid_for=(all_logfiles,
                               primary_role)
...


Safely remove data guard configuration steps :

1. Remove data guard configuration with Data Guard Manager Command Line - DGMGRL use remove configuration .

Connect to Primary database with DGMGRL with SYS user and use command

DGMGRL> remove configuration
Removed configuration

DGMGRL> show configuration 
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

2. On the primary database set dg_broker_start parameter FALSE;

SQL> select name , value from v$parameter  
  2  where name like '%dg_broker%';

NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start            TRUE

dg_broker_config_file1     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1admdb.dat

dg_broker_config_file2     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2admdb.dat

Connect to primary database with SQL*Plus and use command

SQL> alter system set dg_broker_start=FALSE;

System altered.

SQL> select name , value from v$parameter 
2 where name like '%dg_broker%';
NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start          FALSE
dg_broker_config_file1  /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1admdb.dat
dg_broker_config_file2  /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2admdb.dat

3. When we are remove configuration on DGMGRL, then archive destination for service cleared automatically.

SQL> show parameter log_archive_dest

NAME                 TYPE       VALUE
-------------------- ----------- -------------
log_archive_dest_1  string     location=USE_DB_RECOVERY_FILE_
                               DEST, valid_for=(ALL_LOGFILES,
                               ALL_ROLES)
...
log_archive_dest_2  string     
...

4. On standby disable data guard broker

SQL>alter system set dg_broker_start=FALSE;

System altered.

SQL> select name , value from v$parameter 
   2  where name like '%dg_broker%';

NAME                       VALUE
-----------------------------------------------------------------
dg_broker_start            FALSE

dg_broker_config_file1     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr1stbdb.dat
dg_broker_config_file2     /u01/app/oracle/product/11.2.0/dbhome/dbs/dr2stbdb.dat

5. On primary and standby database move broker configuration files.

[oracle@oel62-x64 dbs]$ ls -l dr*.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 18:00 dr1admdb.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 17:59 dr2admdb.dat

[oracle@oel62-x64 dbs]$ mv dr1admdb.dat dr1admdb.dat.bak
[oracle@oel62-x64 dbs]$ mv dr2admdb.dat dr2admdb.dat.bak

and

[oracle@oel62-x64-stb dbs]$ ls -l dr*.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 18:00 dr1stldb.dat
-rw-r----- 1 oracle oinstall 16384 Jul 16 17:59 dr2stldb.dat
[oracle@oel62-x64-stb dbs]$ mv dr1stldb.dat dr1stldb.dat.bak
[oracle@oel62-x64-stb dbs]$ mv dr2stldb.dat dr2stldb.dat.bak 

We are removed data guard configuration safely.

Note : After remove data guard configuration you can use old standby database(s) for developer test after switchover to primary and opened read/write mode.

Regards
Mahir M. Quluzade




2012/07/09

Azerbaijan Oracle User Group - II Meeting

I’ll be speaking at Azerbaijan Oracle User Group II Meeting on July 28th at Qafqaz University. 
As expected, I’ll talk about  "Different operating systems  in same Data Guard configuration"
Here’s the speakers and subjects:
More info (In Azerbaijani) : http://www.azeroug.org/?p=40 

Waiting you to Azerbaijan Oracle User Group II  Meeting

2012/07/06

Oracle Data Guard 11g - Overview Cascaded Redo Transport Destinations



Note:
To use the Oracle Data Guard cascading redo transport destination feature described in this video, you should be using Oracle Database 11g Release 2 (11.2.0.2). Releases prior to 11.2.0.2 have several limitations for this feature that are not present in release 11.2.0.2.
***
We are know usually in data guard configurations standby databases receives primary database generated redo indirectly from  primary.

A cascaded redo transport destination receives primary database redo indirectly from a standby database rather than directly from a primary database.

A standby database that cascades primary database redo to one or more cascaded destinations is known as a cascading standby database.

Cascading offloads the overhead associated with performing redo transport from a primary database to a cascading standby database.

A cascading standby database can cascade primary database redo to up to 30 cascaded destinations, each of which can be a physical, logical, or snapshot standby database.

Primary database redo is written to the standby redo log as it is received at a cascading standby database. The redo is not immediately cascaded however. It is cascaded after the standby redo log file that it was written to has been archived locally. A cascaded destination will therefore always have a greater redo transport lag, with respect to the primary database, than the cascading standby database.

Cascading has the following restrictions:
  • A physical standby database is the only standby database type that can cascade redo
  • The Data Guard broker does not support cascaded destinations
 
Regards
Mahir M. Quluzade



p.s. Watch video with 720 HD quality on youtube : Oracle Data Guard 11g - Overview Cascaded Redo Transport Destinations