Saturday 4 June 2016

                   DATA GUARD SETUP WITH DG BROKER IN A SINGLE INSTANCE
                    ====================================================
 CONFIGURATION

PRIMARY
==========
hostname : testvm1
DATABASE: DGDB
DB_UNIQUE_NAME: DGDB

STANDBY
==========
HOSTNAME: testvm2
DATABASE: DGDB
DB_UNIQUE_NAME: DGDBSTBY

BROKER SERVER
===========
DGBroker Server

HOSTNAME: myhost1
DATABASE: TEST
INSTANCE: TEST

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
PRIMARYDB

SQL> select DB_UNIQUE_NAME from v$database;

DB_UNIQUE_NAME
------------------------------
DGDB

SQL> show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DGDB
SQL>

password file creation on primary
================================
$orapwd  file=orapwPRIMARYDB password=SYSTEM force=y

copy the file to standby database and mv the name like below

$mv orapwPRIMARYDB orapwSTANDBYDB


Enable archivelog mode on primary
================================
SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

force logging on primary
=========================

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

parameter setting in primary
==============================
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGDB,DGDBSTBY)' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGDBSTBY' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=DGDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGDB' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

ALTER SYSTEM set log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE;


ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=20 SCOPE=SPFILE;


ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/home/oracle/app/oradata/DGDB/','/home/oracle/app/oradata/DGDBSTBY/' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/home/oracle/app/oradata/DGDB/','/home/oracle/app/oradata/DGDBSTBY/'  SCOPE=SPFILE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;

Add standby redolog files to primary
====================================
ALTER DATABASE ADD STANDBY LOGFILE  ('/home/oracle/app/oradata/DGDB/stbyredo01.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE  ('/home/oracle/app/oradata/DGDB/stbyredo02.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE  ('/home/oracle/app/oradata/DGDB/stbyredo03.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE  ('/home/oracle/app/oradata/DGDB/stbyredo04.log') size 50M;


take a backup with archivelog
==============================

sql>BACKUP DATABASE PLUS ARCHIVELOG;


sql>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stbyctl.ctl';


STANDBY CONFIGURATION:
======================
COPY PASSWORD FILE, PARAMETER FILE,TNSFILE,LISTENER,BACKUPS TO STANDBY


RMAN>man TARGET sys/SYSTEM@PRIMARY AUXILIARY sys/SYSTEM@STANDBY



[oracle@testvm2 backups]$ rman TARGET sys/SYSTEM@PRIMARY AUXILIARY sys/SYSTEM@STANDBY

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 13 20:40:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DGDB (DBID=2204161043)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections


SOLUTION
===========
INITIAL LISTENER.ORA CONFIGURATION
=================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testvm2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app

NEW CONFIGURATION
===================

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DGDBSTBY )
      (ORACLE_HOME = /home/oracle/app/oracle/11.2.0/db_1/)
      (SID_NAME = STANDBYDB)
    )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testvm2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app



restore the database in standby
=================================
COPY BACKUPS AND STANDBY controlfile to standby location '/home/oracle/backups/'

and then
connect to

$rman TARGET sys/SYSTEM@PRIMARY AUXILIARY sys/SYSTEM@STANDBY

DUPLICATE DATABASE
FOR STANDBY
BACKUP LOCATION '/home/oracle/backups/'
DORECOVER
NOFILENAMECHECK;

Start Apply Process ON STANDBY
================================

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


==========Validating configuration=================

on primary
============
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1310745

on standby
===========
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1310750

on both side to check the status
=============================
sql>select message from v$dataguard_status;

parameter for PRIMARY
========================

*.audit_file_dest='/home/oracle/app/admin/DGDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oradata/DGDB/control01.ctl','/home/oracle/app/fast_rec   overy_area/DGDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/home/oracle/app/oradata/DGDB/','/home/oracle/app/oradata/DGDBST   BY/'
*.db_name='DGDB'
*.db_recovery_file_dest='/home/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/home/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYDBXDB)'
*.fal_server='DGDBSTBY'
*.log_archive_config='DG_CONFIG=(DGDB,DGDBSTBY)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGDB'
*.log_archive_dest_2='SERVICE=DGDBSTBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGDBSTBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=20
*.open_cursors=300
*.pga_aggregate_target=1671430144
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5016387584
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

parameter for STANDBY
=======================

*.audit_file_dest='/home/oracle/app/admin/DGDBSTBY/adump'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oradata/DGDBSTBY/control01.ctl','/home/oracle/app/fast_recovery_area/DGDBSTBY/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/home/oracle/app/oradata/DGDB/','/home/oracle/app/oradata/DGDBSTBY/'
*.db_name='DGDB'
*.db_recovery_file_dest='/home/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='DGDBSTBY'
*.diagnostic_dest='/home/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STANDBYDBXDB)'
*.fal_server='DGDB'
*.log_archive_config='DG_CONFIG=(DGDB,DGDBSTBY)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGDBSTBY'
*.log_archive_dest_2='SERVICE=DGDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=20
*.log_file_name_convert='/home/oracle/app/oradata/DGDB/','/home/oracle/app/oradata/DGDBSTBY/'
*.open_cursors=300
*.pga_aggregate_target=1671430144
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5016387584
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


after changing the parameter files need to create the password file once again

Note: create the password file one primary machine and copy it using scp to standby side and rename it accordingly

DG Broker configuration
==========================
alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1TEST.dat' scope=both;

alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dr2TEST.dat' scope=both;

alter system set dg_broker_start=TRUE scope=both


[oracle@myhost1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/SYSTEM@DGDB
Connected.
DGMGRL>
DGMGRL>

DGMGRL>create configuration 'TESTCONF' as primary database is 'DGDB' connect identifier is DGDB;

before running the above configure dataguard broker on  primary

DGMGRL> add database 'DGDBSTBY' as connect identifier is DGDBSTBY;
DGMGRL>ENABLE CONFIGURATION;


DGMGRL> show database 'DGDB'

Database - DGDB

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARYDB

Database Status:
SUCCESS

DGMGRL> show database 'DGDBSTBY'

Database - DGDBSTBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBYDB

Database Status:
SUCCESS

DGMGRL>

DGMGRL> show database verbose  'DGDB';

Database - DGDB

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARYDB

  Properties:
    DGConnectIdentifier             = 'dgdb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '20'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'PRIMARYDB'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testvm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGDB_DGMGRL)(INSTANCE_NAME=PRIMARYDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


DGMGRL> show database verbose 'DGDBSTBY';

Database - DGDBSTBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBYDB

  Properties:
    DGConnectIdentifier             = 'dgdbstby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '20'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    LogFileNameConvert              = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'STANDBYDB'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testvm2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGDBSTBY_DGMGRL)(INSTANCE_NAME=STANDBYDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

dgmgrl>EDIT DATABASE 'DGDB' set property LogXptMode='SYNC';
dgmgrl>edit database 'DGDBSTBY' set property LogXptMode='SYNC';

dgmgrl>EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

enabling fsfo
==============
dgmgrl>enable fast_start failover;

Error: ORA-16651: requirements not met for enabling fast-start failover

dgmgrl>edit database 'DGDB' set property StandbyFileManagement='AUTO';

dgmgrl>edit database 'DGDBSTBY' set property StandbyFileManagement='AUTO';



failover and switchover testing using DG Broker
===============================================

Data Guard Broker - Configure Fast Start Failover, Data Protection Levels and the Data Guard Observer (Doc ID 1508729.1)


DGMGRL> show configuration verbose;

Configuration - TESTCONF

  Protection Mode: MaxAvailability
  Databases:
    DGDBSTBY - Primary database
    DGDB     - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           DGDB
  Observer:         myhost1
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


Try to enable fsfo
====================
DGMGRL> enable fast_start failover;
Error: ORA-16651: requirements not met for enabling fast-start failover

Failed.

Disable managed recovery in the standby site
DGMGRL> edit database DGDB set state=APPLY-OFF;
Succeeded.


Data Guard Broker - Configure Fast Start Failover, Data Protection Levels and the Data Guard Observer (Doc ID 1508729.1) 
 
In this Document
     The Current Broker Configuration
     The Primary Site Broker Configuration
     The Standby Site Broker Configuration
     Configuring Failover Thresholds
     The Observer OracleNET configuration
     The Observer site TNSNAMES.ORA entries
    

Testing the Observer Site TNS Aliases
     Configuring the ObserverConnectIdentifier Property in the Data Guard Configuration
     Enable Fast Start Failover
     Attempt to Enable Fast Start Failover
     Troubleshooting a failure in enabling Fast Start Failover
     Establish Max Protection Mode
     The sites' current configuration.
    

Disable managed recovery in the standby site
     Enable Flashback Database
     Set Log shipping to LGWR SYNC by setting the property LogXPTMode to SYNC
     Alter the configuration to enable MaxProtection.
     To set the environment to run MaxAvailability Mode
     Enable Automatic file management in the sites.
     Enable Fast Start Failover
     Start the Data Guard Broker Observer in the Observer Site



Once a broker configuration has been established  addressing the Business' requirements for protecting the environment from data loss need to be considered and potentially addressed through further configuration of the Data Guard environment.

This document examines in detail the process for enabling the MaxAvailability and MaxProtection protection modes, Fast Start Failover and the Data Guard Observer.
Solution
The Current Broker Configuration

All sites are running 11.2.0.3 Enterprise Edition


Configuring Failover Thresholds

Should you to alter the default Thresholds that determine when and why a failover may occur edit the following configuration settings

To alter the amount of time the observer attempts to reconnect to the Primary after it has detected problem and the number of seconds it waits on failed connect attempts before initiating a failover edit the configuration property FastStartFailoverThreshold.

DGMGRL> edit configuration set property FastStartFailoverThreshold=45;
Property "faststartfailoverthreshold" updated



Display the configuration to confirm the settings have been successfully altered.

 Attempt to Enable Fast Start Failover
DGMGRL> enable fast_start failover;
Error: ORA-16651: requirements not met for enabling fast-start failover

Failed.


Currently there is the configuration does not allow fast start failover to be enabled.


Troubleshooting a failure in enabling Fast Start Failover

Looking at the error there are a number of potential causes for this error that include the mix of protection mode and Log shipping modes that the sites are currently configured with.  These will need to altered.


Establish Max Protection Mode

As this Data Guard configuration is to run in MaxProtection mode the following is performed to establish this mode prior to enabling fast start failover.

Currently it is running MaxPerformance mode

DGMGRL> show configuration verbose;

Configuration - TESTCONF

  Protection Mode: MaxAvailability
  Databases:
    DGDB     - Primary database
    DGDBSTBY - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           DGDBSTBY
  Observer:         myhost1
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

Log Shipping is being performed using LGWR ASYNC as seen via the LogXPTMode property, Standby file management is set to Manual (StandbyFileManagement property) and no Fast Start Failover target (FastStartFailover property in the primary site) has been set. These will need to be altered.

Each site must also have Flashback Database enabled.


The sites' current configuration.
DGMGRL> show database verbose 'DGDB';

Database - DGDB

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PRIMARYDB

  Properties:
    DGConnectIdentifier             = 'dgdb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '20'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = 'DGDBSTBY'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'PRIMARYDB'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testvm1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGDB_DGMGRL)(INSTANCE_NAME=PRIMARYDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


DGMGRL> show database verbose 'DGDBSTBY'

Database - DGDBSTBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STANDBYDB

  Properties:
    DGConnectIdentifier             = 'dgdbstby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '20'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    LogFileNameConvert              = '/home/oracle/app/oradata/DGDB/, /home/oracle/app/oradata/DGDBSTBY/'
    FastStartFailoverTarget         = 'DGDB'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'STANDBYDB'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testvm2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGDBSTBY_DGMGRL)(INSTANCE_NAME=STANDBYDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


Disable managed recovery in the standby site
DGMGRL> edit database rs1_stb set state=APPLY-OFF;
Succeeded.


Enable Flashback Database
============================
Flashback must be enabled in ALL the database sites.  It was turned on in the primary but had not yet been enabled in the standby site.

The Standy site
SQL> select open_mode,flashback_on from v$database;

OPEN_MODE            FLASHBACK_ON
MOUNTED              NO

SQL> alter database flashback on;

Database altered.


Set Log shipping to LGWR SYNC by setting the property LogXPTMode to SYNC
=========================================================================
The Log shipping mode needs to be set to SYNC in both the sites.

DGMGRL> connect sys@rs1_prm_obsrv
Password:
Connected.

DGMGRL> edit database 'DGDB' set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database 'DGDBSTBY' set property LogXptMode='SYNC';
Property "logxptmode" updated

Alter the configuration to enable MaxAvailability.
===================================================
DGMGRL> edit configuration set protection mode as MaxAvailability;

DGMGRL> exit

In both sites set the property StandbyFileManagement to automatic
===================================================================
DGMGRL> edit database 'DGDB' set property StandbyFileManagement='AUTO';
Property "standbyfilemanagement" updated

DGMGRL> edit database 'DGDBSTBY' set property StandbyFileManagement='AUTO';
Property "standbyfilemanagement" updated

Enable Fast Start Failover
=================================

Now that the broker configuration is complete fast start failover can be enabled.
DGMGRL> enable fast_start failover;
Enabled.


Start the Data Guard Broker Observer in the Observer Site

Now that fast start failover is enabled the broker can be started. Log into the observer machine and start the Faststart Failover observer.
[oracle@grid2vm1 ]$ dgmgrl -logfile /home/oracle/obsrv.log sys/dba4ora@rs1_prm_obsrv "start observer" &


FSFO TESTING
====================

NOW FSFO IS ENABLED IN DG BROKER CONFIGURATION

DGMGRL> show configuration verbose;

Configuration - TESTCONF

  Protection Mode: MaxAvailability
  Databases:
    DGDB     - Primary database
    DGDBSTBY - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           DGDBSTBY
  Observer:         myhost1
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


STEP-1
===========

on primary DGDB
=================
sql>shutdown abort
ORACLE instance shut down.

IT WILL AUTOMATICALLY CONFIGURE DGDBSTBY AS PRIMARY.

NOW WE NEED TO MAKE DGDB DATABASE AS STANDBY

We need to make DGDB AS STANDBY for that we need to follow the below

mount the DGDB DATABASE

sql>startup mount

now connect the broker server myhost1
and
$dgmgrl
CONNECT THE DGDBSTBY DATABASE
DGMGRL>CONNECT sys/SYSTEM@DGDBSTBY
DGMGRL> reinstate database 'DGDB'
Reinstating database "DGDB", please wait...
Reinstatement of database "DGDB" succeeded
DGMGRL>


After that we can check primary(DGDBSTBY) AND STANDBY (DGDB)

SQL>SELECT OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;


Posted by at No comments:
Subscribe to: Posts (Atom)