Step by step Active Dataguard configuration and broker setup using Oracle 11gR2
We will cover the following concepts with expected issues and their fixes:
- Physical Standby and Active dataguard
- Setup & use : Dataguard Broker
- Conversion : Snapshot standby & Physical standby
- Failover & Switchover using broker
- Database protection modes
- Enabling Fast-Start Failover and Starting the Observer
- Reinstate a Database failed primary
Physical Standby and Active dataguard:
Active Data Guard provides the management, monitoring, and automation software to create and maintain one or more synchronized replicas (standby databases) of a production database (primary database). An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster.
Used: Oracle version 11.2.0.3, Linux(RHEL) version 6.3
Info:
Primary database Name: PRIMARY and IP : 192.18.1.18
Proposed Standby Database name: STANDBY and IP: 192.18.1.17
Configure the tnsnames to support the database on both nodes:
a) Listener configuration on primary : adding of standby tnsentry in primary side:
Tnsnames.ora
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.18.1.17)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STANDBY)
)
)
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.18.1.18)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMARY)
)
)
b) tns entry on STANDBY ( add following tns entry after Installing Oracle11g Software)
Tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.18.1.18)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMARY)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.18.1.17)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STANDBY)
)
)
Listener: PRIMARY side
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = UAT-DB)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = PRIMARY)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Listener: STANDBY side:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.18.1.17)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = STANDBY)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
Steps:
1) Create the folder structures:
Login into Standby Database . ( Now we don't have any database)
Note: Install only oracle software. plan for datafile, controlfile. redologfile, archivelog, fra and other admin file locations and create also.
C/D/R location : /u10/STANDBY/ORADATA/PRIMARY
FRA location : /u10/STANDBY/FRA/archivelogs
2) Enable Archiving and set DB Recovery dest in PRIMARY database ( if not done)
C/D/R Location :/u09/PRIMAY/ORADATA/PRIMAY
FRA Location : /u09/PRIMAY/FRA/archivelogs
see sample:
connect as sysdba
startup mount;
alter system set DB_RECOVERY_FILE_DEST_SIZE=10G;
alter system set db_recovery_file_dest='/u09/PRIMAY/FRA/';
alter system set log_archive_dest_1='LOCATION=/u09/PRIMAY/FRA/archivelogs';
shut immediate;
startup;
3) Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
$ sqlplus / as sysdba
SQL> SELECT force_logging FROM v$database; -- if NO, then enable
SQL> alter database force logging;
SQL> SELECT force_logging FROM v$database;
FOR
---
YES
Note : When the primary database is in FORCE LOGGING mode, all database data changes are logged. FORCE LOGGING mode ensures that the standby database remains consistent with the primary database.
4) Create pfile from spfile in PRIMARY
SQL> create pfile from spfile;
Note: Copy pfile into $ORACLE_HOME/dbs location of STANDBY database and rename it with standy database name.
5) Create Password file in PRIMARY ( if not created)
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.
Note: use sys password in your password file.
$ cd $ORACLE_HOME/dbs
$ orapwd force=y file=orapwPRIMARY password=system
Note: Move the same Password file to standby side and rename it with Sandby SID.
6) Configure a Standby Redo Log
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone. You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
Note: if any standby log file found, then drop using following command.
-- For new databases these steps not required:
-- To see stand by log files:
SQL> select GROUP#,SEQUENCE# from v$standby_log;
no rows selected
-- To drop stand by log files:
sql > alter database drop standby logfile group 4;
Note: if, any stand by redo log is dropped, then use following command to add stand by redo log.
– To add standby redolog ( Assume there are 3 redologs are there )
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u09/PRIMAY/ORADATA/PRIMARY/redo04.log') SIZE 50M;
-- To see the path of redo log file location:
SQL> select GROUP#,member from v$logfile;
– To add stand by log file: ( use existing size for standby redologs)
Perform the following steps to configure the standby redo log
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u09/PRIMAY/ORADATA/PRIMARY/redo04.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u09/PRIMAY/ORADATA/PRIMARY/redo05.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u09/PRIMAY/ORADATA/PRIMARY/redo06.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u09/PRIMAY/ORADATA/PRIMARY/redo07.log') SIZE 50M;
Note : Add n+1 standby redologs in Primary database where 'n' is no. of redologs.
-- Now check the status
SQL> select GROUP#,STATUS from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
7) Cross-check pfile and passwd file copid and renamed with Standby database.
$ cd $ORACLE_HOME/dbs
orapwSTANDBY
initSTANDBY.ora
8) Add following into pfile and make sure the following parameters are exists or not.
Set PRIMARY Database Initialization Parameters
PRIMARY.__oracle_base='/u01/app/oracle' #ORACLE_BASE set
*.audit_file_dest='/u01/app/oracle/admin/PRIMARY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u09/PRIMAY/ORADATA/PRIMARY/control01.ctl','/u09/PRIMAY/ORADATA/PRIMARY/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PRIMARY'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u09/PRIMAY/FRA/'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'
*.local_listener='LISTENER_PRIMARY'
*.nls_date_format='DD-MON-RRRR HH24:MI:SS'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
###############################################
# Adding parameters for active dataguard setup
##############################################
*.db_unique_name='PRIMARY'
*.fal_client='PRIMARY'
*.fal_server='STANDBY'
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=/u09/PRIMAY/FRA/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=PRIMARY'
*.log_archive_dest_2='SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=30
*.log_file_name_convert='/u09/PRIMAY/ORADATA/PRIMARY/','/u10/STANDBY/ORADATA/STANDBY/'
*.DB_FILE_NAME_CONVERT='/u09/PRIMAY/ORADATA/PRIMARY/','/u10/STANDBY/ORADATA/STANDBY/'
*.standby_file_management='AUTO'
8.1) Do the following steps after changing parameters:
sql> shut immediate;
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initPRIMARY.ora';
ORACLE instance started.
SQL> create spfile from pfile;
File created.
SQL> shut immediate;
SQL> startup;
Note: Above steps followed to use spfile. Cross check all parameters what are added.
9) Create following directory structure in STANDBY ( STANDBY) database side.
a) paths for Control file, Redolog and Datafiles (CRD):
audit_dest → /home/oracle/STANDBY/STANDBY/adump
CRD dest → /home/oracle/STANDBY/STANDBY/oradata
FRA → /home/oracle/STANDBY/STANDBY/FRA
b) The pfile of STANDBY( STANDBY) database:
*.audit_file_dest='/home/oracle/app/oracle/admin/PRIMARY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u10/STANDBY/ORADATA/PRIMARY/control01.ctl','/u10/STANDBY/ORADATA/PRIMARY/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PRIMARY'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u10/STANDBY/FRA/'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)'
*.nls_date_format='DD-MON-RRRR HH24:MI:SS'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
#########################################
#Active dataguad parameters for standby #
#########################################
*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u10/STANDBY/FRA/archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.db_file_name_convert='/u09/PRIMAY/ORADATA/PRIMARY/','/u10/STANDBY/ORADATA/PRIMARY/'
*.log_file_name_convert='/u09/PRIMAY/ORADATA/PRIMARY/','/u10/STANDBY/ORADATA/PRIMARY/'
*.standby_file_management='AUTO'
10) Crosscheck Password file in STANDBY Database
Move the passwordfile of Primary and rename in standby side with snadby SID. (if not done)
initSTANDBY.ora
orapwSTANDBY
11) Do the following in STANDBY database:
dbs]$ export ORACLE_SID=STANDBY
dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount;
12) Do the following in PRIMRY Database: ( Restore process for STANDBY)
dbs]$ rman target /
RMAN> connect auxiliary sys/system@STANDBY;
OR
$ rman target=sys/system auxiliary=sys/system@STANDBY
RMAN>
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
Note: If rman is configure with disk and tape backups, do the above. If only default disk is configured with rman backup, run the below command only.
OR
RMAN> duplicate target database for standby from active database nofilenamecheck;
(may not work)
13) Recover the standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Note: Now the PRIMARY Database is in OPEN and role is PRIMARY. All transactions can be done into PRIMARY.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
PRIMARY OPEN
Now check the same in STANDBY Database;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
STANDBY MOUNTED
Now you can keep this stage, so that if any archive log will ship, then it apply automatically.
14) To Keep the STANDBY database in Read-only mode and to apply redo-logs also, execute the following command in STANDBY database.
SQL> alter database recover managed standby database cancel;
Database altered. -- to cancel the recovery, if MRP is on
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> alter database open read only;
Database altered.
Note: Now SATANDBY database is in Read-only mode, No Redo-log will apply on STANDBY. So recent transactions can't be reflected.
Now we configured active data-guard setup.
Useful Queries to verify status:
1) Check the Final Status of Standby database:
SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;
NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE
--------------------------------------------------- ----------------
PRMARY STANDBY OPEN READ ONLY WITH APPLY PHYSICAL STANDBY
2) Check the Final Status of PRIMARY database:
SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;
NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE
------------------------------------------ ----------------
PRIMARY STANDBY OPEN READ WRITE PRIMARY
3) Current applied archive log in Standby side:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
6712
4) Archivelog status between primary and standby database
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Sample output:
Thread Last Sequence Received Last Sequence Applied Difference
------ -------------- -------------------------------------------
1 6775 6730 5
Issues and Fixes:
Case:1:
========================================
Error 1017 received logging on to the standby
< ------------------------------------------------------------
< Check that the primary and standby are using a password file
< and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
< and that the SYS password is same in the password files.
< returning error ORA-16191
< ------------------------------------------------------------
< PING[ARC2]: Heartbeat failed to connect to standby 'EHISBLRS'. Error is 16191.
< Sat Jul 06 15:23:28 2013
< Error 1017 received logging on to the standby
If above error found from alert log in DR side,
i.e., some body changed Primary sys password.
Solution: Recreate password file both Primary and DR side with same password and re-synch again.
Case:2:
========================================
channel ORA_DISK_1: SID=33 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/27/2012 15:05:53
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2012 15:05:53
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
Solution:
========================================
RMAN> duplicate target database for standby from active database nofilenamecheck; – – This may not work
do the following:
RMAN>
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
Case-3
========================================
Thu Nov 14 10:52:35 2013
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
Solution:
========================================
1) In primary side:
Ship missed archive logs:
pass the password file again to standby side and rename it with standy database name in standby side.
2) In standby side:
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session;
RMAN> catalog start with '/oracle/EHISBLR/flash_recovery_area/archivelogs';
Now check, sure archive logs will apply
Now you will find following type alerts in alert log file.
Thu Nov 14 12:17:07 2013
Media Recovery Log /u01/flash_recovery_area/archivelogs/1_7945_804801209.dbf
Thu Nov 14 12:17:34 2013
Media Recovery Waiting for thread 1 sequence 7946
Now do the following:
SQL> alter database recover managed standby database cancel;
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
Now sure it will start shiping
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
7951
Case: Accidentally standby DB down. How to again bring to standby mode.
Troubleshoot ORA- 01153
Errors:
SQL> alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> startup nomount;
ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
313
Solution:
$ps -ef|grep mrp
or from view you can check the process "v$managed_standby"
If you use commands as below order then it will work,
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Note:
# Start MRP - Foreground redo apply. Session never returns until cancel
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
#default: Protection mode
SQL> SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
Dataguard Broker - Setup & use
Data Guard Broker [DGMGRL] Configuration quick steps:
This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control GUI as well and the assumption is that there is a Physical Standby database in place and redo transport and redo apply have already been configured and that both the Primary and Standby database are in sync.
Primary Database: PRIMARY || Standby Database: STANDBY
Steps:
1) Stop the MRP
2) Modify the Listener.ora files
3) Modify the init Parameters
4) Create Configuration
5) Enable Configuration
6) Verify the Configuration
7) Setting the Configuration Protection Mode
1) Stop MRP ( on STANDBY database)
SQL> alter database recover managed standby database cancel;
2) Modify the Listener.ora files
Update the listener.ora on all the nodes in Dataguard configuration to register a service with the local listener for each instance for the proper functioning of DGMGRL. Add the below lines to the listener.ora file for each of the specified instances. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
Primary :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY_DGMRGL.UAT-DB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = PRIMARY)
)
)
Standby :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STANDBY_DGMRGL.uatdb1)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = STANDBY)
)
)
Note: Add the above along with exiting setup of listener.
3) Modify the init Parameters:
DB_BROKER_CONFIG_FILEn parameter is used to specify the location of the dataguard configuration. DG_BROKER_START parameter is used to start the broker automatically when the instance starts.
in Primary:
$ sqlplus / as sysdba
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dgb1PRIMARY.ora' sid='*';
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dgb2PRIMARY.ora' sid='*';
SQL> alter system set dg_broker_start=TRUE sid='*';
In Standby:
$ sqlplus / as sysdba
SQL> alter system set dg_broker_config_file1='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/dgb1STANDBY.ora' sid='*';
SQL> alter system set dg_broker_config_file2='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/dgb2STANDBY.ora' sid='*';
SQL> alter system set dg_broker_start=TRUE sid='*';
Note : This is to prevent the ORA-12514 error which can be observed on startup of the standby database after performing a switchover.
4) Create Configuration:
Connect to the DGMGRL and run the below statements to create and enable the DataGuard configuration. Verify that the DG Broker is configured correctly and it has all the databases and instances registered as expected.
in PRIMARY:
– create configuration:
$ dgmgrl
DGMRGL> connect sys/system;
DGMGRL> CREATE CONFIGURATION 'DR_Config' AS PRIMARY DATABASE IS 'PRIMARY' CONNECT IDENTIFIER IS PRIMARY;
Configuration "DR_Config" created with primary database "PRIMARY"
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
Fast-Start Failover: DISABLED
Configuration Status: DISABLED
DGMGRL>
– Add standby database
DGMGRL> ADD DATABASE 'STANDBY' AS CONNECT IDENTIFIER IS STANDBY MAINTAINED AS PHYSICAL;
Database "STANDBY" added
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:DISABLED
DGMGRL>
5) Enable Configuration:
The "DR_config" setup is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it.
You can enable: The entire configuration, including all of its databases:
-- in Primary database
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
6) Verify Configuration: Use below commands.
show configuration;
show database verbose 'PRIMARY';
show database verbose 'STANDBY';
-- Verify in STANDBY side:
[oracle@eh-uatdb1 dbs]$ dgmgrl
DGMGRL> show configuration;
not logged on
DGMGRL>
DGMGRL> connect sys/system@STANDBY;
Connected.
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
STANDBY
Database Status: SUCCESS
DGMGRL>
Enable the database:
This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally, enabling the configuration also enables the standby database.
DGMGRL> ENABLE DATABASE 'STANDBY';
Enabled.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
STANDBY
Database Status: SUCCESS
DGMGRL>
Alter the State of a Standby Database :
a) See the STANDBY database:
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
STANDBY
Database Status: SUCCESS
DGMGRL>
b) Edit the STANDBY database as redo-log off mode:
DGMGRL> EDIT DATABASE 'STANDBY' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 8 seconds
Real Time Query: OFF
Instance(s):
STANDBY
Database Status:
SUCCESS
DGMGRL>
c) Again edit the database in redo-apply mode:
DGMGRL> EDIT DATABASE 'STANDBY' SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
STANDBY
Database Status: SUCCESS
DGMGRL>
Alter the State of a Primary Database :
You might want to stop the transmittal of redo data to the standby database. To change the state of the primary database to accommodate this, use the following command:
a) View the state of the primary database:
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Database Status:SUCCESS
DGMGRL>
b) change the state of the primary database as TRANSPORT-OFF:
DGMGRL> edit database 'PRIMARY' set STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
PRIMARY
Database Status: SUCCESS
DGMGRL>
c) change the state of the primary database back to TRANSPORT-ON:
DGMGRL> edit database 'PRIMARY' set STATE='TRANSPORT-ON';
Succeeded.
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Database Status: SUCCESS
DGMGRL>
Switchover ( Manual Method)
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
On the original standby database issue the following commands.
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
-- Open old standby database as primary
STARTUP;
Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.
Failover (Manual Method):
If the primary database is not available the standby database can be activated as a primary database using the following statements.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.
Snapshot Standby:
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Note : Make sure managed recovery is disabled.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> ALTER DATABASE OPEN;
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.
Use Redo Transport Services Best Practices:
At a high level, the Redo Transport best practices for planning and implementing redo transport services for Oracle Data Guard are as follows:
Use the SYNC redo transport mode for a high degree of synchronization between the primary and standby databases. Use SYNC redo transport for zero data loss protection where performance service levels can tolerate the impact caused by network latency.
Use the ASYNC redo transport mode for minimal impact on the primary database, but with a lower degree of synchronization. Use ASYNC redo transport when zero data loss protection is not required or when the performance impact caused by network latency makes it impractical to use SYNC.
Performing a Switchover Operation via Dataguad Broker:
You can switch the role of the primary database and a standby database using the SWITCHOVER command. Before you issue the SWITCHOVER command, you must ensure:
The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.
All participating databases are in good health, without any errors or warnings present.
The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples).
Standby redo log files on the primary database are set up, and the LogXptMode configurable database property is set to SYNC if the configuration is operating in either maximum availability mode or maximum protection mode.
If fast-start failover is enabled, you can perform a switchover only to the standby database that was specified as the target standby database.
Step 1 : Check the primary database.
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:
DGMGRL> connect sys/system@PRIMARY;
Connected.
DGMGRL> show database verbose 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Properties:
DGConnectIdentifier = 'primary'
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 = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/STANDBY/'
LogFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/STANDBY/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'PRIMARY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=EH-UAT-DB)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=PRIMARY_DGMGRL)(INSTANCE_NAME=PRIMARY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u09/PRIMAY/FRA/archivelogs'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status: SUCCESS
DGMGRL>
Step 2 : Check the standby database that is the target of the switchover.
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the standby database that is the target of the switchover. For example:
DGMGRL> SHOW DATABASE VERBOSE 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
STANDBY
Properties:
DGConnectIdentifier = 'standby'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/PRIMARY/'
LogFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/PRIMARY/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eh-uatdb1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u10/STANDBY/FRA/archivelogs'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status: SUCCESS
DGMGRL>
Step 3 : Issue the switchover command.
Fire SWITCHOVER command to swap the roles of the primary and standby databases. The following example shows how the broker automatically shuts down and restarts the old primary database as a part of the switchover.
DGMGRL> switchover to 'STANDBY';
Performing switchover NOW, please wait...
New primary database "STANDBY" is opening...
Operation requires shutdown of instance "PRIMARY" on database "PRIMARY"
Shutting down instance "PRIMARY"...
ORACLE instance shut down.
Operation requires startup of instance "PRIMARY" on database "PRIMARY"
Starting instance "PRIMARY"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "PRIMARY" of database "PRIMARY"
DGMGRL> exit
Note: Here we face “ORA-12514” error, i.e. there is some issue with listener configuration. We will discuss how to irradiate this error. But now check whether the role is transmitted or not.
After the switchover completes, use the SHOW CONFIGURATION and SHOW DATABASE commands to verify that the switchover operation was successful.
Step 4 : Show the configuration.
Issue the SHOW CONFIGURATION command to verify that the switchover was successful.
Connect to 172.18.17 ( STANDBY)
$ export ORACLE_SID=STANDBY
$ sqlplus / as sysdba
SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;
NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE
--------- ---------------- ------------ ----------------- ----------------
PRIMARY STANDBY OPEN READ WRITE PRIMARY
SQL> quit
$ dgmgrl
DGMGRL> connect sys/system@STANDBY;
Connected.
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
STANDBY - Primary database
PRIMARY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
Here the role is transmitted. STANDBY database now act as primary database. See the above out put from 'sqlplus' and 'dgmgrl'. Manually generate few archive logs in new primary db and check whether applied in new standby db or not. When I did this step, it worked fine.
Connect to New standby ( 172.18.1.18)
SQL> select NAME, INSTANCE_NAME, STATUS, OPEN_MODE, database_role FROM V$DATABASE, V$INSTANCE;
NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE
--------- - --------------------------- ------------------ ----------------
PRIMARY PRIMARY OPEN READ ONLY WITH APPLY PHYSICAL STANDBY
$ dgmgrl
DGMGRL> connect sys/system@PRIMARY
Connected.
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
STANDBY - Primary database
PRIMARY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS
DGMGRL>
DGMGRL> connect sys/system@STANDBY
Connected.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PRIMARY
Intended State: TRANSPORT-ON – 'transport-on' because of primary database
Instance(s):
STANDBY
Database Status:SUCCESS
DGMGRL>
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON - 'apply-on' because of standby database
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
PRIMARY
Database Status: SUCCESS
DGMGRL>
Now Again switchover done. PRIMARY became primary database and STANDBY became standby database.
If you are facing ORA-12514 error then your standby database may not start with mount stage. Do the following :
DGMGRL> switchover to 'PRIMARY';
Performing switchover NOW, please wait...
New primary database "PRIMARY" is opening...
Operation requires shutdown of instance "STANDBY" on database "STANDBY"
Shutting down instance "STANDBY"...
ORACLE instance shut down.
Operation requires startup of instance "STANDBY" on database "STANDBY"
Starting instance "STANDBY"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
DGMGRL>
Please complete the following steps to finish switchover:
start up and mount instance "STANDBY" of database "STANDBY"
DGMGRL> quit
[oracle@eh-uatdb1 ~]$ ps -ef|grep pmon
oracle 27778 1 0 Jul25 ? 00:01:58 ora_pmon_STANDBY
oracle 27891 25639 0 18:26 pts/1 00:00:00 grep pmon
[oracle@eh-uatdb1 ~]$ echo $ORACLE_SID
STANDBY
atdb1 ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select name,instance_name,open_mode,database_role from v$database,v$instance;
NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE
--------- ---------------- -------------------- ----------------
PRIMARY STANDBY MOUNTED PHYSICAL STANDBY
In Primary:
SQL> select name,instance_name,open_mode,database_role from v$database,v$instance;
NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE
--------- ---------------- -------------------- ----------------
PRIMARY PRIMARY READ WRITE PRIMARY
Database Protection Mode:
You can change the protection mode of the configuration at any time. However, it is best if you do this when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes.
Note:
If the protection mode to be set is maximum protection mode, the broker automatically restarts the primary database.
This scenario sets the protection mode of the configuration to the MAXAVAILABILITY mode. Note that this protection mode requires that there be at least one standby database configured to use standby redo log files, with its LogXptMode configurable database property set to SYNC.
Pre-Checks: PRIMARY
DGMGRL> connect sys@PRIMARY
Password:
Connected.
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Database Status:
SUCCESS
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
Pre-checks : STANDBY
DGMGRL> connect sys@STANDBY
Password:
Connected.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 19 minutes 21 seconds
Real Time Query: OFF
Instance(s):
STANDBY
Database Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property
Database Status:
WARNING
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
Steps to Change protection mode:
- from Max Performance (ASYNC) to Max Availability ( SYNC):
Step 1 : Configure standby redo log files, if necessary.
Because you will be setting the protection mode to the MAXAVAILABILITY mode, it is important to ensure that sufficient standby redo log files are configured on the standby database.
Step 2 : Set the LogXptMode configurable database property appropriately.
Use the EDIT DATABASE (property) command on the standby database to set the redo transport service that corresponds to the protection mode you plan to set. If the protection mode to be set is MAXAVAILABILITY, it is required that the redo transport service of at least one standby database is set to SYNC. For example:
– In Standby:
DGMGRL> EDIT DATABASE 'STANDBY' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL>
Note : The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.
Step 3 : Change the overall protection mode for the configuration.
Use the EDIT CONFIGURATION command to upgrade the broker configuration to the MAXAVAILABILITY protection mode:
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins property
Fast-Start Failover: DISABLED
Configuration Status: WARNING
DGMGRL>
Here we are getting ORA-16826. See my troubleshoot mechanism:
In Standby : Cancel the MRP:
SQL> alter database recover managed standby database cancel;
Database altered.
– Start MRP with current logfile as you converted the standby database to Max Availability mode:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> quit
$ dgmgrl
DGMGRL> connect sys@STANDBY;
Password:
Connected.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s): STANDBY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
If the configuration is disabled when you enter this command, the actual protection mode change is not applied until you enable the configuration with the ENABLE CONFIGURATION command. The broker will not allow you to enable the configuration if it does not find a standby database in the configuration that can support the requirements of the protection mode.
Enabling Fast-Start Failover and Starting the Observer
You can enable fast-start failover from any site, including the observer site, while connected to any database in the broker configuration. Enabling fast-start failover does not trigger a failover. Instead, it allows the observer to begin observing the primary and standby databases and initiate a fast-start failover should conditions warrant a failover.
This section describes the steps to enable fast-start failover and start the observer where the configuration property mode is to be set to:
Ensure standby redo logs are configured on the primary and target standby databases.
Ensure the LogXptMode Property is set to SYNC.
Set the FastStartFailoverTarget configuration property.
Upgrade the protection mode to MAXAVAILABILITY, if necessary.
Enable Flashback Database on the primary and target standby databases, if necessary.
Start the observer.
Enable fast start failover.
Verify the fast-start failover configuration.
Step 1 : Ensure standby redo logs are configured on the primary and target standby databases.
Standby redo logs must be configured on the primary and standby databases. You must stop log apply services prior to configuring standby redo logs.
Step 2 : Ensure the LogXptMode Property is set to SYNC.
The LogXptMode configurable database property must be set to SYNC on the primary and target standby databases.
To set the redo transport service that corresponds to the protection mode you plan to set, use the EDIT DATABASE (property) command on the primary and target standby databases. For example, if the protection mode to be set is MAXAVAILABILITY, you must set the LogXptMode property to SYNC on the primary database and on the target standby database, as shown in the following examples:
-- In Primary:
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): PRIMARY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
-- In Standby:
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s): STANDBY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
Note: Both Primary and Standby database should be in 'MaxAVAILABILITY' mode. The broker does not allow these commands to succeed unless the databases are configured with standby redo log files.
Step 3 : Set the FastStartFailoverTarget configuration property.
If you have two or more standby databases, set up the FastStartFailoverTarget configuration property on the primary database to indicate the desired target standby database. For example:
--In PRIMARY]
DGMGRL> connect sys@PRIMARY
Password:
Connected.
DGMGRL> EDIT DATABASE 'PRIMARY' SET PROPERTY FastStartFailoverTarget='STANDBY';
Property "faststartfailovertarget" updated
DGMGRL>
-- In STANDBY]
DGMGRL> connect sys@STANDBY
Password:
Connected.
DGMGRL>
DGMGRL> EDIT DATABASE 'STANDBY' SET PROPERTY FastStartFailoverTarget='PRIMARY';
Property "faststartfailovertarget" updated
DGMGRL>
Step 4 : Upgrade the protection mode to MAXAVAILABILITY, if necessary.
If it is necessary to upgrade the protection mode, use the following DGMGRL EDIT CONFIGURATION command. For example:
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Step 5 : Enable Flashback Database on the primary and target standby databases.
If it is not already enabled on the primary and standby databases, enable Flashback Database by issuing the following statements on each database:
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SHOW PARAMETER UNDO;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET db_recovery_file_dest_size=<size>;
ALTER SYSTEM SET db_recovery_file_dest=<directory-specification>;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
Note : Ensure the UNDO_RETENTION and DB_FLASHBACK_RETENTION_TARGET initialization parameters are set to sufficiently large values so that reinstatement is still possible after a prolonged outage.
Step 6 : Start the observer.
Start the observer by logging into the observer computer and running DGMGRL. Connect to the configuration as SYS and then issue the START OBSERVER command. Note that the command does not return; that is you will not get DGMGRL prompt after issuing the command.
Example:
PRIMARY database version is 11.2.0.3.0 and Oracle Client version is 11.1.0.6.0. So client version is not compatible.
DGMGRL> START OBSERVER;
Error: ORA-16556: observer version mismatch
Failed.
DGMGRL>
Solution:
ORA-16556: observer version mismatch
Cause: This version of the observer was incompatible with the version of the specified Oracle database server to which the observer had been connected. This observer client cannot be used to observe the specified Oracle server.
Action: Use a version of the observer that is compatible with the Oracle database server to be observed. If matched, then “Observer started” message will come.
DGMGRL> CONNECT sys@PRIMARY;
Password: password
Connected.
DGMGRL>
DGMGRL> START OBSERVER;
Observer started
When starting the observer interactively, Oracle recommends that connection credentials be supplied as a command parameter to the DGMGRL CONNECT command, as shown in the example, rather than as a command line parameter to the DGMGRL command. This practice prevents other users on the system from using a utility (for example, the UNIX ps utility) to display the connection credentials.
When starting the observer from a script, Oracle recommends that you use a method that supports 'connect /', so that database connection credentials do not have to be embedded within the script. If you choose to use a client-side Oracle Wallet as a secure external password store (see Oracle Database Advanced Security Administrator's Guide), be sure to add credentials for both the primary and fast-start failover target standby databases. The database connect string that you specify when adding the credentials for each database must match the ObserverConnectIdentifer or DGConnectIdentifier configurable database property.
To maximize the benefits of FSFO, the observer should run on a different host than the primary and standby databases. Ideally the primary, standby, and observer will be in geographically separate areas. The observer is very lightweight, requiring few system resources. Unlike the primary / standby interconnect, where bandwidth and latency are determining performance factors, the observer requires very little network bandwidth and is not overly latency sensitive, allowing the it to be placed practically anywhere a reliable connection is available.
Since the observer is a specialized instance of a DGMGRL session, the observer host should be installed with either the Oracle Client Administrator software or the full Oracle Database software stack.
Note that Now My Oracle Client Version is 11.2.0.1.0. We will proceed to start the observer.
Running a StatusReport on the primary should verify that the error is due to a missing observer.
DGMGRL> show database 'PRIMARY' statusreport;
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
Note: No error came means, you can proceed. Be sure flashback enabled, undo retain value will be greater as discussed earlier. Don't initiate failover unless at least 30 minutes of history is available
Again we will Start the observer:
--In PIMARY]
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS
DGMGRL>
– start observer
DGMGRL> start observer;
Observer started
Note : The terminal session will appear to hang at this point.
Verify the configuration
In a separate terminal session, verify the configuration. This example shows the verbose mode of the 'show configuration' command that provides FSFO-specific information. If the status is SUCCESS, you're ready to start testing role transitions.
Now check the PRIMARY status:
DGMGRL> connect sys@PRIMARY
Password:
Connected.
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS
DGMGRL>
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Database Status:SUCCESS
DGMGRL>
DGMGRL> show database verbose 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRIMARY
Properties:
DGConnectIdentifier = 'primary'
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 = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/STAN
LogFileNameConvert = '/u09/PRIMAY/ORADATA/PRIMARY/, /u10/STANDBY/ORADATA/STAN
FastStartFailoverTarget = 'STANDBY'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'PRIMARY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=EH-UAT-DB)(PO
CT_DATA=(SERVICE_NAME=PRIMARY_DGMGRL)(INSTANCE_NAME=PRIMARY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u09/PRIMAY/FRA/archivelogs'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status: SUCCESS
DGMGRL>
Enable fast start failover (FSFO) :
Step 7 : Enable fast start failover.
You can enable fast-start failover while connected to any database system in the broker configuration. For example:
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL>
If you are facing issues like below error message, then troubleshoot the issue.
DGMGRL> ENABLE FAST_START FAILOVER;
Error: ORA-16651: requirements not met for enabling fast-start failover Failed.
Fix / Solution : Error: ORA-16651: requirements not met for enabling fast-start failover
DGMGRL> ENABLE FAST_START FAILOVER;
Error: ORA-16651: requirements not met for enabling fast-start failover
Failed.
There are various causes not to start fast-start failover. If swich-over is happening properly, then following issues may be the cause. Go through the following steps to troubleshoot.
Be sure of following settings:
1) Ensure standby redo logs are configured on the primary and target standby databases.
2) Ensure the LogXptMode Property is set to SYNC. ( ASYNC allowed also)
3) Set the FastStartFailoverTarget configuration property.
4) Upgrade the protection mode to MAXAVAILABILITY ( if necessary)
5) Enable Flashback Database on the primary and target standby databases.
6) Start the observer
Creating three issues both in Primary and standby database for scenarios testing and issue fixing:
In primary See status:
DGMGRL> show FAST_START FAILOVER;
Fast-Start Failover: DISABLED
Threshold: 30 seconds
Target: (none)
Observer: edc-noc-01d
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions: (none)
DGMGRL>
Note: “edc-noc-01d” hostname is my client machine(windows) host name/
If above are OK, then proceed following steps.
1) Check whether the flashback database is enabled on primary & standby database
PRIMARY]
SQL> Select flashback_on from gv$database;
FLASHBACK_ON
------------------
YES
STANDBY]
SQL> Select flashback_on from gv$database;
FLASHBACK_ON
------------------
NO
So, Now cancel MRP and enable flashback. Follow the below steps:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
2) Check whether you have set the FastStartFailoverTarget parameter.
PRIMARY]
Using dgmgrl prompt, check database in verbose mode and look at "FastStartFailoverTarget" value. Found set as STANDBY
FastStartFailoverTarget = 'STANDBY' -- is set
Do the same thing in STANDBY side.
STANDBY]
FastStartFailoverTarget = ' ' -- is not set
Then do the following using dgmgrl prompt.(if not done)
edit database 'PRIMARY' set property FastStartFailoverTarget='STANDBY'; --- in primary side
edit database 'STANDBY' set property FastStartFailoverTarget='PRIMARY'; --- in standby side
Example:
DGMGRL> edit database 'STANDBY' set property FastStartFailoverTarget='PRIMARY';
Property "faststartfailovertarget" updated
DGMGRL>
Now, STANDBY]
FastStartFailoverTarget = 'PRIMARY'
Now you go ahead to start
3) CHeck LogXptMode both in Primary & Standby:
[PRIMARY]
LogXptMode = 'ASYNC' -- Needs to Change as 'SYNC'
[STANDBY]
LogXptMode = 'SYNC'
-- Edit in primary database.
DGMGRL> connect sys@PRIMARY;
Password:
Connected.
DGMGRL>
DGMGRL> EDIT DATABASE 'PRIMARY' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated.
DGMGRL>
Now Cross-check and start "FAST_START FAILOVER":
DGMGRL> show FAST_START FAILOVER;
Fast-Start Failover: DISABLED
Threshold: 30 seconds
Target: (none)
Observer: edc-noc-01d
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions: (none)
DGMGRL>
Now Enable fast_start failover:
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
Step 8 : Verify the fast-start failover configuration.
Use the SHOW FAST_START FAILOVER command to display the fast-start failover settings:
DGMGRL> show FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: STANDBY
Observer: edc-noc-01d
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions: (none)
DGMGRL>
-- FSFO tnsnames.ora entry but create the service as 'REPORT'. See next.
REPORT =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.18)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REPORT)
(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=5))
)
)
Do the following activities for Failover Operations ( in case of stand-alone databases)
Basic Pr-requisites:
in PRIMARY]
-- create service
SQL>
exec dbms_service.create_service(service_name => 'REPORT',network_name => 'REPORT',aq_ha_notifications =>
TRUE,failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 30,failover_delay => 5);
SQL>
-- Start Service
SQL> exec dbms_service.START_SERVICE('REPORT');
SQL>
-- Create procedure to trigger for starting/ stopping service in case of shut down of Primary database.
SQL> create or replace procedure p_cmc_taf_service is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('REPORT');
else
DBMS_SERVICE.STOP_SERVICE('REPORT');
end if;
end;
/
SQL>
-- Create triggers
SQL> create or replace TRIGGER trg_cmc_taf_service_startup
after startup on database
begin
p_cmc_taf_service;
end;
/
SQL>
SQL> create or replace TRIGGER trg_cmc_taf_manage_rolechange
after db_role_change on database
begin
p_cmc_taf_service;
end;
/
SQL>
in Client ( Oracle Admin Client)]
-- Add tns entry on client side
First do this entry and confirm :
1) tnsnames.ora entry:
REPORT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.18)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REPORT)
)
)
2) lsnrctl status
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-AUG-2014 17:39:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
......
STATUS of the LISTENER
------------------------
Alias LISTENER
...
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PRIMARY" has 2 instance(s).
Instance "PRIMARY", status UNKNOWN, has 1 handler(s) for this service...
Instance "PRIMARY", status READY, has 1 handler(s) for this service...
Service "PRIMARYXDB" has 1 instance(s).
Instance "PRIMARY", status READY, has 1 handler(s) for this service...
Service "PRIMARY_DGB" has 1 instance(s).
Instance "PRIMARY", status READY, has 1 handler(s) for this service...
Service "PRIMARY_DGMRGL.EH-UAT-DB" has 1 instance(s).
Instance "PRIMARY", status UNKNOWN, has 1 handler(s) for this service...
Service "REPORT" has 1 instance(s).
Instance "PRIMARY", status READY, has 1 handler(s) for this service...
The command completed successfully
Note:
1) Able to connect through service 'REPORT'.
2) Listener status showing the created service 'REPORT'
i.e., OK about service. Next to test below tnsnames entry for seamless application connection.
REPORT =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.18)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REPORT)
(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=5))
)
)
To confirm:
1) Run tnsping REPORT -- succeeded
2) Connect sqlplus window using service name 'REPORT' -- succeeded
Now it is ready for failover testing.....
Create Big table to test a “select” query furring failover operation.
-- Create a big table to test during failover in client machine
-- Create structrure
create table big_table as
select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0;
-- Insert data ( input 100000 when execute the pl-sql block)
declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum,OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE,
CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY
from all_objects a where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
-- add a constraint
SQL> alter table big_table add constraint big_table_pk primary key(id);
-- Gather the statistics
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'BIG_TABLE',cascade => TRUE);
Now our preparation is over and ready to test fire the missile “failover”.
PRIMARY]
DGMGRL> connect sys@PRIMARY;
Password:
Connected.
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): PRIMARY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:SUCCESS
DGMGRL>
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: STANDBY
Observer: edc-noc-01d
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions: (none)
DGMGRL>
When Fast-Start Failover Is Enabled and the Observer Is Running:
Once you enable fast-start failover and start the observer, the observer continuously monitors the environment to ensure the primary database is available. This section lists the steps the observer takes to determine if fast-start failover is needed and then performs one, if necessary.
Step 1 - Monitor the environment to ensure the primary database is available.
Step 2 - Reconnect within the time specified by FastStartFailoverThreshold.
Step 3 - Verify the target standby database is ready for failover.
Step 4 - Initiate a fast-start failover.
Step 5 - Reinstate the former primary database as a new standby database.
Failover using dataguad broker:
You invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable. Before you fail over to decide which standby database should be the target of the failover. The following scenario describes a failover to the remote database called DR_config.
Note: If fast-start failover is enabled, you can perform a manual failover only to the standby database that was specified as the target of a fast-start failover and only when the observer is running.
If you want to perform a manual failover to a standby database that is not the fast-start failover target standby database, you must first disable fast-start failover using the FORCE option on the standby database you want to fail over. "Disabling Fast-Start Failover" for more information about the FORCE option.
Step 1 Connect to the target standby database.
DGMGRL> connect sys@STANDBY;
Step 2 Issue the failover command.
Now you can issue the failover command to make the target standby database the new primary database for the configuration. Note that after the failover completes, the original primary database cannot be used as a standby database of the new primary database unless it is re-enabled.
DGMGRL> FAILOVER TO database-name [IMMEDIATE];
e.g.,
DGMGRL> FAILOVER TO 'STANDBY';
Performing failover NOW, please wait...
Error: ORA-16600: not connected to target standby database for failover
Failed.
Unable to failover
DGMGRL>
Solution: You may be in other than Standby database. Be sure you logged into Standby database though broker.
DGMGRL> connect sys@STANDBY;
Password:
Connected.
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
STANDBY
Database Status: SUCCESS
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
PRIMARY - Primary database
STANDBY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
DGMGRL>
DGMGRL> FAILOVER TO 'STANDBY';
Performing failover NOW, please wait...
Failover succeeded, new primary is "STANDBY"
DGMGRL>
+++++++++++++++++++++
****mission successful****
+++++++++++++++++++++
More about Fast-Start Failover (FSFO):
Fast-start failover allows the broker to automatically fail over to a previously chosen standby database in the event of loss of the primary database. Fast-start failover quickly and reliably fails over the target standby database to the primary database role, without requiring you to perform any manual steps to invoke the failover. Fast-start failover can be used only in a broker configuration and can be configured only through DGMGRL or Enterprise Manager.
Either maximum availability mode or maximum performance mode can be used with fast-start failover. Maximum availability mode provides an automatic failover environment guaranteed to lose no data. Maximum performance mode provides an automatic failover environment guaranteed to lose no more than the amount of data (in seconds) specified by the FastStartFailoverLagLimit configuration property. This property indicates the maximum amount of data loss that is permissible in order for an automatic failover to occur. It is only used when fast-start failover is enabled and the configuration is operating in maximum performance mode.
Once fast-start failover is enabled, the broker will ensure that fast-start failover is only possible when the configured data loss guarantee can be upheld. If the configured data loss guarantee cannot be upheld, redo generation on the primary database will be stalled. To avoid a prolonged stall, either the observer or target standby database may allow the primary database to continue redo generation after first recording that a fast-start failover cannot happen.
The broker will restore the ability to automatically failover once the configured data loss guarantee is restored. For a configuration that is operating in maximum availability mode, this occurs once the target standby database has received all missing redo data. For a configuration that is operating in maximum performance mode, this occurs once the target standby database's redo applied point is no longer lagging the primary database's redo generation point by the value specified by the FastStartFailoverLagLimit configuration property.
This section describes how to enable fast-start failover and an observer site that monitors the fast-start failover environment. The observer is a separate OCI client-side component that runs on a different computer from the primary and standby databases and monitors the availability of the primary database.
Once the observer is started, no further user interaction is required. If both the observer and designated standby database lose connectivity with the primary database for longer than the number of seconds specified by the FastStartFailoverThreshold configuration property, the observer will initiate a fast-start failover to the standby database. In addition, the primary database will shut down if it perceives a loss of connectivity for a period longer than FastStartFailoverThreshold seconds, if the FastStartFailoverPmyShutdown configuration property is set to TRUE. After the failover completes, the former primary database is automatically reinstated as a standby database when a connection to it is reestablished, if the FastStartFailoverAutoReinstate configuration property is set to TRUE.
Note : The FastStartFailoverPmyShutdown and FastStartFailoverAutoReinstate configuration properties do not affect whether the former primary database is shut down and automatically reinstated, respectively, when a fast-start failover occurs if either a user configurable fast-start failover condition is detected or if an application initiated a fast-start failover by calling the DBMS_DG.INITIATE_FS_FAILOVER function. In these cases, the former primary database is always shut down and never automatically reinstated.
Test Case:
I have connected to created service 'REPORT' and started executing a long running query. In the mean time I fired command manual failover. I observed, my “select” query continued and not terminated the session. This situation tells the story that application is continued seamlessly. Application server can't disturb. Only Transactions will be failed what are in primary. Next connection onwards it will continue. Now its time to re-instate the old primary as standby. Because existing standby is new primary database.
If the target standby database is an Oracle RAC physical or snapshot standby database, the broker will direct CRS to shut down all instances except the apply instance before it continues the failover. If the other instances cannot be shut down, the failover fails. In this case, you must manually shut down all instances except the apply instance and issue the FAILOVER command again. It is also important that you do not start any new instances during the failover. The broker will direct CRS to restart instances that were shut down prior to the failover.
No instances will be shut down if failing over to a logical standby database.
Step 3 Reset the protection mode.
After a manual failover (complete or immediate), the overall Data Guard protection mode is handled as follows:
If the protection mode was at maximum protection, it is reset to maximum performance. You can upgrade the protection mode later, if necessary
If the protection mode was at maximum availability, it remains at maximum availability.
Note:
If you perform a manual failover when fast-start failover is enabled:
The failover can only be performed to the pre-selected target standby database.
The broker preserves the protection mode at the maximum availability level (which is the mode it was operating in before the failover occurred).
Step 4 Re-establish a disaster-recovery configuration.
To maintain a viable disaster-recovery solution in the event of another disaster, you may need to perform the additional
Reinstate the original primary database to act as a standby database in the new configuration.
Reinstate or re-create standby databases in the configuration that were disabled by the broker.
After a complete failover finishes, any standby database not involved in the failover that is not viable as a standby for the new primary database will be disabled by the broker. This can happen for either of these reasons:
The broker detects that the standby database has applied redo data beyond what has been applied on the new primary database.
For instance, this could happen if a standby database not involved in the failover has applied more redo data than the new primary database itself has applied. The standby database must be reenabled or reinstated before it can serve as a standby for the new primary database
The failover was to a logical standby database, the broker disables all of the (physical, snapshot, and logical) standby databases in the configuration that were not involved in the failover. They must be reenabled before they can serve as standby to the new primary database.
Now Check the New primary database ( here db is STANDBY) status:
Managing the Observer:
The observer is integrated in the DGMGRL client-side component of the broker and typically runs on a different computer from the primary or standby databases and from the computer where you manage the broker configuration. The observer continuously monitors the fast-start failover environment to ensure the primary database is available The observer's main purpose is to enhance high availability and lights out computing by reducing the human intervention required by the manual failover process that can add minutes or hours to downtime.
You can manage the observer through either the Data Guard Overview pages in Oracle Enterprise Manager or using DGMGRL commands. Below Figure shows the observer monitoring a fast-start failover configuration.
The following sections provide information about managing the observer:
Installing and Starting the Observer ( discussed in above)
Viewing Information About the Observer ( use v$database)
Stopping the Observer
Moving the Observer to Another Computer
How the Observer Maintains Fast-Start Failover Configuration Information
What Happens if the Observer Fails?
Stopping the Observer:
You may want to stop the observer when you no longer want to use fast-start failover or if you want to move the observer to a different host machine.
To stop the observer when fast-start failover is enabled, the primary database and target standby database must be connected and communicating with each other. Stopping the observer does not disable the fast-start failover. However, fast-start failover cannot occur when the target standby database is in the unobserved state.
To stop the observer when fast-start failover is not enabled, the primary database must be running.You can stop the observer while connected to any database in the broker configuration that has network connectivity to the primary database, as follows:
•Using Enterprise Manager
Choose the Stop Observer option on the first page of the fast-start failover wizard and click Continue at the bottom of the page. See the Enterprise Manager online help system for more information.
•Using DGMGRL
Issue the following command:
DGMGRL> STOP OBSERVER;
Note: The observer does not stop immediately when you issue STOP OBSERVER command. When the broker receives the STOP OBSERVER request, it informs the observer the next time the observer contacts the broker.
Reinstate the Database:
How to Reinstate a Database?
You can use the broker's REINSTATE command to re-enable the failed primary database after performing a complete failover to either a physical, snapshot, or logical standby database. You can also use the broker's REINSTATE command to reenable any physical standby databases that were not the target of the failover operation but were disabled during a complete failover to a physical standby database.
Databases that can be reinstated will have the following status value:
ORA-16661: the standby database needs to be reinstated
For the REINSTATE command to succeed, Flashback Database must have been enabled on the database prior to the failover and there must be sufficient flashback logs on that database. In addition, the database to be reinstated and the new primary database must have network connectivity.
To reinstate a database:
1. Restart the database to the mounted state
2. Connect to the new primary database
3. Use Enterprise Manager or DGMGRL to reinstate the database
When reinstating a failed primary database, the broker reenables it as a standby database of the same type (physical or logical standby database) as the old standby database. When reinstating physical standby databases that were disabled during a failover, the broker reenables them as physical standby databases to the new primary database.
Reinstating a Failed Primary Database
After failover, now primary database is 'STANDBY'. So old primay database to be re-instead as physical stand by database. See below status from current primary database 'STANDBY'.
DGMGRL> FAILOVER TO 'STANDBY';
Performing failover NOW, please wait...
Failover succeeded, new primary is "STANDBY"
DGMGRL>
DGMGRL> show database 'STANDBY';
Database - STANDBY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s): STANDBY
Database Warning(s):
ORA-16817: unsynchronized fast-start failover configuration
Database Status: WARNING
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
STANDBY - Primary database
Warning: ORA-16817: unsynchronized fast-start failover configuration
PRIMARY - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status: WARNING
DGMGRL>
If your primary database had been configured with Flashback Database, you can easily reinstate the failed primary database as a standby database of the new primary database. The failed primary database will be reinstated as a standby type that matches the old standby database. For example, if you failed over to a physical standby database, the old primary will be reinstated as a physical standby database.
To reinstate the failed primary database, start it to the mounted state. Then run DGMGRL, connect to the new primary database and reinstate the old primary database.
Step 1 Restart the Old Primary Database
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PRIMARY MOUNTED
SQL>
Step 2 Connect to new primary database do the following:
DGMGRL> connect sys@STANDBY;
Password:
Connected.
DGMGRL>
DGMGRL> REINSTATE DATABASE 'PRIMARY';
Reinstating database "PRIMARY", please wait...
Error: ORA-16653: failed to reinstate database
Failed.
Reinstatement of database "PRIMARY" failed
Note: Your primary database may not in mount state.
DGMGRL> REINSTATE DATABASE 'PRIMARY';
Reinstating database "PRIMARY", please wait...
Reinstatement of database "PRIMARY" succeeded
DGMGRL>
Now, old primary database ( PRIMARY) is re-instated as new physical standby database.
Cross-check status of both primary and Standby database:
DGMGRL> SHOW CONFIGURATION;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
STANDBY - Primary database
PRIMARY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
DGMGRL>
Connect to new physical standby ( old primary):
DGMGRL> connect sys@PRIMARY;
Password:
Connected.
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
STANDBY - Primary database
PRIMARY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s): PRIMARY
Database Status: SUCCESS
DGMGRL>
DGMGRL> SHOW DATABASE 'PRIMARY' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL>
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: PRIMARY
Observer: edc-noc-01d
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions: (none)
DGMGRL>
Reinstatement Using Enterprise Manager:
On the Data Guard Overview page, click the Database must be reinstated link. This brings up the General Properties page that provides a Reinstate button. After you click the Reinstate button, Enterprise Manager begins reinstating the database.
When the process is complete, the database will be enabled as a standby database to the new primary database, and Enterprise Manager displays the Data Guard Overview page.
Reinstatement Using DGMGRL
Issue the following command while connected to any database in the broker configuration, except the database that is to be reinstated:
DGMGRL> REINSTATE DATABASE db_unique_name;
The newly reinstated standby database will begin serving as standby database to the new primary database. If the database is not reinstated successfully, then you must reenable it from a copy of the new primary database.
Converting a Physical Standby to a Snapshot Standby using broker:
If you have a physical standby database that you would like to convert to a snapshot standby database, use the DGMGRL CONVERT DATABASE command. Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.
Note that the Flashback Database feature is required to create a snapshot standby database. If Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required.
DGMGRL> CONVERT DATABASE 'STANDBY' to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE 'STANDBY' to PHYSICAL STANDBY;
Performing a Switchover Operation:
( from new primary 'STANDBY' to old primary 'PRIMARY' database)
You can switch the role of the primary database and a standby database using the SWITCHOVER command. Before you issue the SWITCHOVER command, you must ensure:
The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.
All participating databases are in good health, without any errors or warnings present.
The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples in boldface type).
Standby redo log files on the primary database are set up, and the LogXptMode configurable database property is set to SYNC if the configuration is operating in either maximum availability mode or maximum protection mode.
If fast-start failover is enabled, you can perform a switchover only to the standby database that was specified as the target standby database.
Step 1 Check the primary database.
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s): PRIMARY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
STANDBY - Primary database
PRIMARY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status: SUCCESS
DGMGRL>
Step 2 Check the standby database that is the target of the switchover.
Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the standby database that is the target of the switchover. For example:
DGMGRL> show database 'PRIMARY';
Database - PRIMARY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s): PRIMARY
Database Status: SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - DR_Config
Protection Mode: MaxAvailability
Databases:
STANDBY - Primary database
PRIMARY - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL>
Step 3 Issue the switchover command.
Issue the SWITCHOVER command to swap the roles of the primary and standby databases. The following example shows how the broker automatically shuts down and restarts the old primary database as a part of the switchover.
FAQ:
How the Observer Maintains Fast-Start Failover Configuration Information?
The observer persistently maintains information about the fast-start failover configuration in a binary file created in the working directory where you started the observer. By default, the observer creates this file in the current working directory when it is started and names the file fsfo.dat. This file contains connect identifiers to both the primary and the target standby databases.
Ensure this file cannot be read by unauthorized users.
Once the observer is started, you cannot change the file's name and location. However, you can change the name or the location of the file if you start the observer using the DGMGRL START OBSERVER command and include the FILE qualifier. See the START OBSERVER command for more information.
Note:
If the observer is stopped abnormally (for example, by typing CTRL/C), restart it and reference the existing fsfo.dat file with the FILE qualifier.
If you want to use one Oracle home to start multiple observers, with each observer monitoring a different fast-start failover configuration, use the FILE qualifier to specify a unique observer configuration file location for each configuration to be monitored. If you want to capture any logging generated by the observer, use the LOGFILE option and ensure that file name is unique as well. For example:
$ dgmgrl -logfile $ORACLE_HOME/rdbms/log/config1.log
DGMGRL> CONNECT /@primary1;
DGMGRL> START OBSERVER FILE=$ORACLE_HOME/dbs/config1.dat;
$ dgmgrl -logfile $ORACLE_HOME/rdbms/log/config2.log
DGMGRL> CONNECT /@primary2;
DGMGRL> START OBSERVER FILE=$ORACLE_HOME/dbs/config2.dat;
What Happens if the Observer Fails?
If the primary and target standby databases stay connected but the connection to the observer is lost, then the broker reports that the configuration is not observed. The configuration and database status report that the observer is not running and return one of the following
status messages:
ORA-16658: unobserved fast-start failover configuration
ORA-16820: fast-start failover observer is no longer observing this database
While the configuration is in the unobserved state, fast-start failover cannot happen. Therefore, the primary database can continue processing transactions, even if the target standby database fails. The configuration status returns the SUCCESS status after the observer reestablishes its connection to the primary database, which then notifies the target standby database.
Click here to See to admin dataguard broker environment:
How the Broker Performs a Complete Failover Operation?
Once you start a complete failover, the broker:
1. Verifies that the target standby database is enabled. If the database is not enabled, you will not be able to perform a failover to this database. If the target is an Oracle RAC physical or snapshot standby database, the broker the broker directs CRS to shut down all instances except the apply instance.
2. Waits for the target standby database to finish applying any unapplied redo data before stopping Redo Apply (if the target is a physical standby database) or SQL Apply (if the target is a logical standby database).
If the target is a snapshot standby database, the broker first converts the database back to a physical standby and then starts Redo Apply to apply all the accumulated redo before completing the failover and opening the database as a primary database.
3. Transitions the target standby database into the primary database role, as follows:
1. Opens the new primary database in read/write mode.
2. Determines whether or not any standby databases that did not participate in the failover operation have applied redo data beyond the new primary database, and thus need to be reenabled.
If a standby database not involved in the failover is not disabled by the broker during this failover, it will remain in the state it was in before the failover. For example, if a physical standby database was in the APPLY-OFF state, it will remain in the APPLY-OFF state.
By default the broker always determines whether bystander standby databases will be viable standby databases for the new primary when performing a complete failover. If you want the broker to skip this viability check of bystander standby databases during a complete failover, thus decreasing the overall failover time, set the BystandersFollowRoleChange configuration property to NONE.
When this property is set to NONE, the broker will disable all bystander standby databases without checking whether they have applied more redo data than the new primary database. You will have to manually reenable the standby databases after failover has completed. Use the SHOW CONFIGURATION BystandersFollowRoleChange command to see the value of this property. The default value is ALL.
This property also affects whether the broker skips viability checks of bystander standby databases when a fast-start failover occurs.
3. Starts redo transport services to begin transmitting redo data to all standby databases not involved in the failover and that were not disabled.
Note:
Standby databases not directly involved in a failover may be disabled by the broker during the failover, and they must be re-enabled in the configuration before they can serve as standby databases to the new primary database. Oracle recommends configuring Flashback Database on every database so that if failover occurs to a physical standby database, you can more easily reinstate any disabled physical standby databases. However, if failover occurs to a logical standby database, all (physical, snapshot, and logical) standby databases will be disabled by the broker. In this case, Flashback Database cannot be used to reinstate databases.
4. If the target is an Oracle RAC physical or snapshot standby database, the broker directs CRS to restart all instances that it shut down prior to the failover.
The broker allows the failover to proceed as long as there are no errors for the standby database that you selected to participate in the failover. Errors occurring for any standby databases not involved in the failover will not stop the failover. If you initiated a complete failover and it fails, you might need to use immediate failover.
How the Broker Performs an Immediate Fail-over Operation?
Once you start an immediate failover, the broker:
1. Verifies that the target standby database is enabled. If the standby database is not enabled for management by the broker, then the failover cannot occur.
2. Stops Redo Apply or SQL Apply on the standby database immediately, without waiting until all available redo data has been applied. This may result in data loss.
3. Transitions the target standby database into the primary role, opens the new primary database in read/write mode, and starts redo transport services.
After an immediate failover completes, all the standby databases in the configuration, regardless of their type, are disabled. They must be re-enabled before they can serve as standby database to the new primary database.
The broker allows the failover to proceed as long as there are no errors for the standby database that you selected to participate in the failover.
How to Re-create and Reenable a Disabled Database?
If you performed a fail-over or switch-over that requires you to re-create the failed primary database or standby databases that were disabled during the role transition, follow the procedures in Oracle Data Guard Concepts and Administration.
Note that if you are re-creating the old primary database, it must be created as the standby type of the old standby database. For example, if the old standby was a physical standby, then the old primary must be re-created as a physical standby. It can then be properly enabled.
After the database has been re-created, enable broker management of the re-created standby database by using the DGMGRL ENABLE DATABASE command.
Showing parameter file on three-node RAC system (both Primary & Standby)
-- PRIMARY ( dbname = HCMPRD), STANDBY ( dbname=HCMDR)
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/HCMPRD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.cluster_database_instances=3
*.cluster_database=TRUE
*.control_files='+DATA/HCMPRD/CONTROLFILE/current.5454.879867741','+DATA/HCMPRD/CONTROLFILE/current.2323.879867741'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='HCMPRD'
*.db_recovery_file_dest='+FRA'
*.dg_broker_config_file1='+DATA/HCMPRD/BROKER/dr1HCMPRD.dat'
*.dg_broker_config_file2='+DATA/HCMPRD/BROKER/dr2HCMPRD.dat'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_config='dg_config=(HCMPRD,HCMDR)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service="HCMDR"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="HCMDR"
net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_2='ENABLE'
HCMPRD3.log_archive_format='%t_%s_%r.dbf'
HCMPRD2.log_archive_format='%t_%s_%r.dbf'
HCMPRD1.log_archive_format='%t_%s_%r.dbf'
*.fal_server='HCMDR'
*.db_recovery_file_dest_size=900GB
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
HCMPRD3.log_archive_trace=0
HCMPRD2.log_archive_trace=0
HCMPRD1.log_archive_trace=0
*.memory_target=50G
*.open_cursors=600
*.processes=555
*.sessions=500
*.remote_listener='example01:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
HCMPRD3.instance_number=3
HCMPRD1.instance_number=1
HCMPRD2.instance_number=2
HCMPRD1.thread=1
HCMPRD3.thread=3
HCMPRD2.thread=2
HCMPRD1.undo_tablespace='UNDOTBS2'
HCMPRD2.undo_tablespace='UNDOTBS1'
HCMPRD3.undo_tablespace='UNDOTBS3'
*.standby_file_management='AUTO'
*.dg_broker_start=TRUE
- standby pfile
*.audit_file_dest='/u01/app/oracle/admin/HCMDR/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/HCMDR/control01.ctl','+DATA/HCMDR/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='HCMPRD'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=900G
*.db_unique_name='HCMDR'
*.dg_broker_config_file1='+DATA/HCMDR/BROKER/dr1HCMDR.dat'
*.dg_broker_config_file2='+DATA/HCMDR/BROKER/dr2HCMDR.dat'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='HCMPRD'
*.archive_lag_target=0
*.log_archive_config='DG_CONFIG=(HCMPRD,HCMDR)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='SERVICE=HCMPRD lgwr ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=HCMPRD'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
HCMDR2.log_archive_format='%t_%s_%r.dbf'
HCMDR3.log_archive_format='%t_%s_%r.dbf'
HCMDR1.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
HCMDR2.log_archive_trace=0
HCMDR3.log_archive_trace=0
HCMDR1.log_archive_trace=0
*.memory_target=50G
*.open_cursors=600
*.processes=555
*.sessions=500
*.remote_listener='example02:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_archive_dest='USE_DB_RECOVERY_FILE_DEST'
*.standby_file_management='AUTO'
*.cluster_database_instances=3
*.cluster_database=TRUE
HCMDR1.instance_name='HCMDR1'
HCMDR2.instance_name='HCMDR2'
HCMDR3.instance_name='HCMDR3'
HCMPRD1.instance_number=1
HCMPRD2.instance_number=2
HCMPRD3.instance_number=3
HCMDR1.instance_number=1
HCMDR2.instance_number=2
HCMDR3.instance_number=3
HCMPRD1.thread=1
HCMPRD3.thread=3
HCMPRD2.thread=2
HCMPRD1.undo_tablespace='UNDOTBS2'
HCMPRD2.undo_tablespace='UNDOTBS1'
HCMPRD3.undo_tablespace='UNDOTBS3'
-- Listener status PRIMARY
SQL> show parameter remote_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string example1:1521
SQL>
SQL> show parameter local_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=
10.20.30.40)(PORT=1521))
SQL>
-- Listener status STANDBY
SQL> show parameter remote_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string example2:1521
SQL>
SQL> show parameter local_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=
10.20.30.50)(PORT=1521))
SQL>
