6.Data Guard Administration
In the Part II We will perform the following tasks in this lab to manage and validate the data guard configuration (followed in order). if you wonder how we got here check the Part I : Implementation
6.1 Data guard Broker Configuration
» What is a data guard Broker ? (‘DGMGRL’)
Oracle Data Guard Broker is a tool that automates and centralizes the creation, management and monitoring of the Data Guard configuration. All operations can be done via Oracle Enterprise Manager that uses the broker or via command lines (DGMGRL) .
The actions that are automated and simplified by the broker are as follows :
- Create and activate Data Guard configurations (up to 253 members)
- Administration and management of the Data Guard configuration for all sites in the configuration
- Simplifies switchovers and failovers
- Monitor redo transport and the redo application; capture diagnostic information; quickly detect problems with centralized monitoring, test and report events.
» Broker Prerequisite
- Same Oracle version on both Primary and standby databases.
- A parameter file (SPFILE) must be used.
- OraNet network settings must be configured on primary and standby database.
- The value of the initialization parameter “DG_BROKER_START” = TRUE.
- The service name must be registered statically on the listener for each instance.
» Configuring the Broker
a) ORA Net configuration:
- First run the set the local listener on the standby DB using following commands:
---- Standby server SQL> Alter system set local_listener='(address=(host= 192.168.78.52) (port=1522)(protocol=tcp))'; SQL> alter system register;
- Add the following lines in each of the listeners: global_name format should be ‘db_unique_name_DGMGRL.db_domain’
---- Primary server (SID_DESC =(GLOBAL_DBNAME = MTLDB_DGMGRL.evilcorp) –- format ‘db_unique_name_DGMGRL.db_domain’ (ORACLE_HOME =C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLDB))) ---- Standby server (SID_DESC =(GLOBAL_DBNAME = MTLSTD_DGMGRL.evilcorp) (ORACLE_HOME = C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLSTD)))
Note: This step is only necessary when oracle restart is not enabled
b) Create the configuration & add the databases
- Execute the following in the DGMGRL window
C: dgmgrl sys/racattack@Montreal DGMGRL> CREATE CONFIGURATION DG12 as primary database is MTLDB connect identifier is Montreal; //created DGMGRL> add database MTLSTD as connect identifier is Boisbriand maintained as physical; //added
c) Enable the configuration
DGMGRL> enable configuration;
you can check the log out here #tail -f 'C:apporaclediagrdbmsMTLSTDMTLSTDtracealert_MTLSTD.log' DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Databases: mtldb - Primary database mtlstd - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ENABLED
d) Confirm the value of the broker’s StaticConnectIdentifier parameter matches each databases’ static listener IP/port
DGMGRL> EDIT instance mtldb set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.51)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=MTLDB.evilcorp)(INSTANCE_NAME=mtldb)(SERVER=DEDICATED)))'; DGMGRL> EDIT instance mtlstd set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.52)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=MTLSTD.evilcorp)(INSTANCE_NAME=mtlstd)(SERVER=DEDICATED)))';
6.2 Redo transport and Protection modes Go to Top
» Redo transport management
a) Data Guard Broker Mode
- The ASYNC mode is started by default when the Broker configuration is enabled . However the ”
LogXptMode”Broker property enables you to set the redo transport service.
Run the following to keep the change consistent on both standby and primary DB over role transitions :
-- change the Transport mode from ASYNC to SYNC DGMGRL> edit database MTLDB set property logxptmode='SYNC'; DGMGRL> edit database MTLSTD set property logxptmode='SYNC';
b) SQL mode
- Even without data guard broker you can still enable the Redo Transport and application (Managed Recovery process), using the following SQL commands (SQL mode):
-- Start the MRP process in the background SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- Stop the MRP SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
- Notes:
- The DISCONNECT option allows you to run the command in the background.
- Standby redo logs are necessary for a Real-time Apply of redo sources,
- “USING CURRENT LOGFILE” option is no longer needed in 12c.
- Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.
c) Check Redo Apply
- To check that the Redo data is correctly applied on the standby DB (MTLSTD) consult the alert log or one of the v$
views (v$archive_log, v$history_log,v$log) :
--- Standby Database alert.log #tail -f 'C:apporaclediagrdbmsMTLSTDMTLSTDtracealert_MTLSTD.log' Media Recovery Waiting for thread 1 sequence 677 (in transit) Fri Apr 15 05:30:41 2016 Recovery of Online Redo Log: Thread 1 Group 5 Seq 677 Reading mem 0 Mem# 0: +REDO/MTLDB/ONLINELOG/group_5.260.908907615
SQL> Select MAX (SEQUENCE#), APPLIED FROM V$ARCHIVED_LOG GROUP BY APPLIED; MAX(SEQUENCE#) APPLIED -------------- --------- 676 IN-MEMORY 669 N 675 YES SQL> SELECT * FROM V$ARCHIVE_GAP; no rows selected
» Change the protection mode Go to Top
a) Data Guard Broker Mode
The default value for each of these parameters is as follows
- Protection mode: MAXIMUM PERFORMANCE
- Mode de transport (Logxptmode): ASYNC (asynchrone)
To change the protection mode, i.e to MAXIMUM AVAILABILITY (synchronous), the following Broker commands are executed:
DGMGRL> EDIT DATABASE MTLDB SET PROPERTY 'LogXptMode'='SYNC'; DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxAvailability
a) SQL mode
Execute the following SQL statement while choosing one of the 3 values on the primary database after a restart in mount mode:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
» Startup modes of a standby database
There are several modes for a standby DB :
- MOUNT : Default mode, allows continuous application of the redo logs upon reception.
- READ-ONLY: Allows Read-Only access to the standby DB while continuing redo data reception from the primary database. Redo application is interrupted until the Database is restarted (mounted back).
- SNAPSHOT (READ-WRITE): Allows to read and write on standby DB, but all changes will be lost when the database is restarted (mounted back).
Note : Modes other than MOUNT are part of the features requiring an ACTIVE DATAGUARD license (on top of the Enterprise E license)
6.2 Manage role transitions Go to Top
6.2.1 Switchover
Fig10 : Switchover scenario
» Data Guard Broker Method
a) Test the validity of the switchover before execution:
DGMGRL> validate database mtlstd; Database Role: Physical standby database Primary Database: mtldb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: mtldb: Off mtlstd: Off Current Log File Groups Configuration: Thread# Online Redo Log Groups Standby Redo Log Groups Status (mtldb) (mtlstd) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread# Online Redo Log Groups Standby Redo Log Groups Status (mtlstd) (mtldb) 1 3 2 Insufficient SRLs ------- Standby alert log: Mon Dec 29 21:44:17 2014 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database SWITCHOVER VERIFY COMPLETE
c) execute the switchover :
DGMGRL> switchover to mtlstd Performing switchover NOW, please wait... Operation requires a connection to instance "mtlstd" on database "mtlstd" Connecting to instance "mtlstd"... Connected as SYSDG. New primary database "mtlstd" is opening... Oracle Clusterware is restarting database "mtldb" ... Switchover succeeded, new primary is "mtlstd" DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Members: mtlstd - Primary database mtldb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 38 seconds ago) --- alert log from primary database: Active, synchronized Physical Standby switchover target has been identified Preventing updates and queries at the Primary Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 682 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x6dce3f ARCH: End-Of-Redo Branch archival of thread 1 sequence 682 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received all redo Waiting for target standby to apply all redo Archivelog for thread 1 sequence 682 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully Switchover: Complete - Database shutdown required Sending request(convert to primary database) to switchover target mtlstd Switchover complete. Database shutdown required Completed: ALTER DATABASE SWITCHOVER TO 'mtlstd'
» SQL Method
This method is based only on SQL commands to initiate role switching ( Primary/Standby).
Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.
a) Check the status of the switchover on each of the databases (Primary/Standby)
--- Primary Database PRIMARY_SQL> select switchover_status from v$database; SWITCHOVER_STATUS ----------------- TO STANDBY --- Primary database STANDBY_SQL> select switchover_status from v$database; SWITCHOVER_STATUS ----------------- NOT ALLOWED
b) Test the validity of the switchover before execution
--- Primary Database PRIMARY_SQL> ALTER DATABASE SWITCHOVER TO MTLSTD VERIFY; Database altered. ---- ceci confirme que l’opération est valide.
c) execute the switchover
--- Primary Database PRIMARY_SQL> ALTER DATABASE SWITCHOVER TO MTLSTD; Database altered. ---- ceci confirme que l’opération est valide. Alert log /nouvelle base secondaire (MTLDB) : Switchover complete. Database shutdown required Thu Apr 14 17:59:57 2016 Instance shutdown complete Alert log /nouvelle base primaire (MTLSTD): Switchover: Complete - Database mounted as primary SWITCHOVER: completed request from primary database.
d) Startup the new primary and standby databases
- New Primary database (MTLSTD):
--- start the new Primary Database MTLSTD> alter database open; Database altered.
- New Standby DB (MTLDB) :
--- Start the New standby Database
MTLDB> startup mount;
Database mounted.
---- Check SCN and DATE of the switchover
MTLSTD> SELECT TRUNC(scn_to_timestamp(STANDBY_BECAME_PRIMARY_SCN)) swicth_date,
STANDBY_BECAME_PRIMARY_SCN scn_switch FROM v$database;
SWICTH_DA SCN_SWITCH
--------- ----------
14-APR-16 7046772
6.2.3 Failover Go to Top
Fig11 : Failover scenario
» Data Guard Broker Method
This method relies on the commands available on the broker as well as on the FLASHBACK database option to simplify the recovery of the corrupt primary database (Reinstate)
a) Enable flashback on the primary DB MTLDB :
SQL> ALTER SYSTEM SET FLASHBACK ON scope=both; SQL> ALTER SYSTEM SET db_flashback_retention_target=60 scope=both;
b) Check the status of the configuration on each of the databases (primary/Standby) :
DGMGRL> validate database mtlstd Database Role: Physical standby database Primary Database: mtldb Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: mtldb: On
c) Simulate the failover by stopping the primary database abnormally:
SQL> SHUTDOWN ABORT;
d) Apply the failover :
dgmgrl /@Boisbriand DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Members: mtldb - Primary database Error: ORA-12519: TNS:no appropriate service handler found mtlstd - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ERROR (status updated 0 seconds ago) ---------Failover DGMGRL> failover to MTLSTD; Performing failover NOW, please wait... Failover succeeded, new primary is "mtlstd"
e) Recovery of the former primary database:
DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Members: mtlstd - Primary database mtldb - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 16 seconds ago) ----- Restart of the MTLDB database C: sqlplus /@Montreal sys@MTLDB.evilcorp> startup mount; Database mounted. ------ Reinstate dgmgrl /@Boisbriand DGMGRL> Reinstate database MTLDB; Reinstating database "mtldb", please wait... Reinstatement of database "mtldb" succeeded DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Members: mtlstd - Primary database mtldb - Physical standby database
f) Restore the initial Primary/Standby roles (Primary:mtldb /Standby:mtlstb):
DGMGRL> switchover to MTLDB; Performing switchover NOW, please wait... Operation requires a connection to instance "mtldb" on database "mtldb" Connecting to instance "mtldb"... Connected as SYSDG. New primary database "mtldb" is opening... Oracle Clusterware is restarting database "mtlstd" ...succeeded Switchover succeeded, new primary is "mtldb"
» SQL Method
This method is based only on SQL commands to initiate role failover (Primary/Standby) in case of failure (manual/automatic).
Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.
a) Check the switchover status on each of the databases(Primary/standby)
--- Primary database PRIMARY_SQL> select switchover_status from v$database; SWITCHOVER_STATUS ----------------- TO STANDBY --- Standby database STANDBY_SQL> select switchover_status from v$database; SWITCHOVER_STATUS ----------------- NOT ALLOWED
b) Test the validity of the switchover before execution
PRIMARY_DB> ALTER DATABASE SWITCHOVER TO MTLSTD VERIFY;
c) Execute the failover
---- Minimum data loss (recommended) SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ---- OR with data loss SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
d) Recovery of the former primary database
-- Verify the SCN at failover time SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; STANDBY_BECAME_PRIMARY_SCN -------------------------- 7917322
- The obtained SCN will serve to restore the lost primary database into a new standby database using one of these method
- Via RMAN backups
- Via FLASHBACK
- Now that you have gotten this far into this lab, it’s time to have a real fun.You read me right let’s get wild and get this broker rock !!Fast-Start Failover here we Go !!!!