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;
====================================================
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;
No comments:
Post a Comment