Oracle Data Guard offers the maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements. Oracle Data Guard is designed specifically for the creation and management of standby databases used as part of an overall Disaster Recovery plan. If your Disaster Recovery plans include creating a standby database that is a transactionally consistent copy of the primary, then use Oracle Data Guard. It is easy to switch over and switch back during planned system outages. In the worst scenario of a complete loss of a data center, users can again have access to their data in a matter of minutes because Oracle Data Guard protects the database.
Data Guard Benefits
Efficient utilization of system resources
The standby database tables that are updated with redo logs received from the primary database can be used for other tasks such as backup operations, reporting, summations, and queries, thereby reducing the primary database workload necessary to perform these tasks, saving valuable CPU and I/O cycles. With a logical standby database, users can perform normal data manipulation operations on tables in schemas that are not updated from the primary database. A logical standby database can remain open while the tables are updated from the primary database and the tables are simultaneously available for read-only access.
Automatic gap detection and resolution
If connectivity is lost between the primary and one or more standby databases (e.g. because of network problems), redo data being generated on the primary database cannot be sent to those standby databases. Once connectivity is re-established, the missing archivelog sequence (or the gap) is automatically detected by Data Guard and the necessary archive logs are automatically transmitted to the standby databases. The standby databases are resynchronized with the primary database, with no manual intervention by the administrator.
Centralized and simple management
The Data Guard Broker automates the management and operational tasks across multiple databases in a Data Guard configuration. The Broker also monitors all of the systems within a single Data Guard configuration. Administrators may use either Oracle Enterprise Manager or the Broker’s own specialized command-line interface (DGMGRL) to take advantage of this integrated management framework.
Integrated with Oracle database
Oracle Data Guard is available as a fully integrated feature of the Oracle Database (Enterprise Edition) at no extra cost.
Physical Standby Commands
1. To start redo apply in foreground:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
2. To stop redo apply process on the Standby database (to stop MRP):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. To start real-time redo apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
4. To start redo apply in background:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
5. To check redo apply and Media recovery service status:
SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
6. If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
7. To gather Data Guard configuration information(standby):
SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE DATABASE_ROLE OPEN_MODE PROTECTION_MODE ---------------- -------------------- -------------------- PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE RECOVERY_MODE!='IDLE'; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY
8. To calculate the Redo bytes per second:
SQL> SELECT SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 60 / 60 / 30 REDO_MB_PER_SEC
FROM GV$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN TO_DATE ('01.05.2016', 'DD.MM.YYYY')
AND TO_DATE ('01.06.2016', 'DD.MM.YYYY')
To check status of Data Guard synchronization(standby):
SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS; NAME VALUE --------------------- ------------------------------- transport lag +00 00:00:00 apply lag +00 00:00:00 apply finish time +00 00:00:00.000 estimated startup time 32
9. To verify there is no log file gap between the primary and the standby database:
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 3; STATUS GAP_STATUS --------- ------------------------ VALID NO GAP
10. To verify that the primary database can be switched to the standby role:
A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY
You can use verify command to verify the switchover. This command will generate warnings in the alert log file and you can check it before switchover:
SQL> ALTER DATABASE SWITCHOVER to STBY_DB_SID VERIFY;
11. To convert the primary database into a physical standby:
Before switchover, the current control file is backed up to the current SQL session trace file and it is possible to reconstruct a current control file, if necessary.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered.
12. To verify Managed Recovery is running on the standby:
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'; PROCESS --------- MRP0
13. To show information about the protection mode, the protection level, the role of the database, and switchover status:
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS ---------------- ---------- ------------ -------------------- -------------------- -------------------- PRIMARY TESTCDB READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
14. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
15. On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
16. To determine which log files were not received by the standby site.
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID = 1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND THREAD# = LOCAL.THREAD#);
17. Archivelog difference: Run this on the primary database. (not for real-time apply):
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SQL> SELECT A.THREAD#, B.LAST_SEQ, A.APPLIED_SEQ, A.LAST_APP_TIMESTAMP, B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF FROM ( SELECT THREAD#, MAX (SEQUENCE#) APPLIED_SEQ, MAX (NEXT_TIME) LAST_APP_TIMESTAMP FROM GV$ARCHIVED_LOG WHERE APPLIED = 'YES' GROUP BY THREAD#) A, ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ FROM GV$ARCHIVED_LOG GROUP BY THREAD#) B WHERE A.THREAD# = B.THREAD#; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF ---------- ---------- ----------- --------------------- ---------- 1 21282 21281 09-IYUL-2016 12:06:5 1 2 23747 23746 09-IYUL-2016 12:16:13 1 2 rows selected.
18. To check archive log apply on primary database:
SQL> SET LINESIZE 150 SET PAGESIZE 999 COL NAME FORMAT A60 COL DEST_TYPE FORMAT A10 COL ARCHIVED FORMAT A10 COL APPLIED FORMAT A10 SELECT SEQUENCE#, NAME, DEST_ID , CASE WHEN STANDBY_DEST = 'YES' THEN 'Standby' ELSE 'Local' END AS DEST_TYPE , ARCHIVED , APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# > (SELECT MAX (SEQUENCE#) FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND APPLIED = 'YES') ORDER BY SEQUENCE# , DEST_ID ; SEQUENCE# NAME DEST_ID DEST_TYPE ARCHIVED APPLIED ---------- -------------------------------------------------------------- ------- ---------- -------- -------- 23748 +FRA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10041.9 1 Local YES NO 23748 +DATA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10062. 2 Local YES NO 23748 TESTSTB 3 Standby YES NO 3 rows selected.
19. Disable/Enable archive log destinations:
alter system set log_archive_dest_state_2 = 'defer'; alter system set log_archive_dest_state_2 = 'enable';
20. Check which logs are missing and log apply gap:
– Run this on the standby:
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; select sequence#, archived, applied, first_time, next_time from v$archived_log order by sequence#;
– Run this on both primary and standby:
select count(*) from v$archive_gap; select * from v$archive_gap; select max(sequence#) from v$log_history;
select local.thread#
, local.sequence# from
(select thread#
, sequence#
from v$archived_log
where dest_id=1) local
where local.sequence# not in
(select sequence#
from v$archived_log
where dest_id=2 and
thread# = local.thread#)
/
DG Broker Commands
1. How to configure Data Guard broker:
– Start the DMON process on both the primary and standby databases:
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; System altered.
– Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing , then try to create the broker configuration (it will automatically set the log_archive_dest_n when you’ll add a database to the configuration):
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''; System altered.
– Connect DGMGRL on the primary DB and create the configuration:
[oracle@primary ~]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/test Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION 'TEST' AS PRIMARY DATABASE IS 'DB12C' CONNECT IDENTIFIER IS DB12C; Configuration "TEST" created with primary database "DB12C"
– Next add a standby database to the Data Guard broker configuration:
DGMGRL> ADD DATABASE 'db12c_stby' AS CONNECT IDENTIFIER IS 'db12c_stby'; Database "db12c" added
– Enable dataguard broker configuration:
DGMGRL> enable configuration; Enabled.
DGMGRL> show configuration; Configuration - TEST Protection Mode: MaxPerformance Members: db12c - Primary database DB12C_STBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 40 seconds ago)
2. To remove DG broker configuration:
DGMGRL> remove configuration; Removed configuration
3. Rename the database name in the Data Guard broker as follows:
DGMGRL> edit database 'db12c_stby' rename to 'STBY';
4. To turn off redo transport to all remote destinations on the primary database:
DGMGRL> edit database 'DB12C' SET STATE="LOG-TRANSPORT-OFF";
5. To stop and start redo transport services to specific standby databases:
DGMGRL> edit database 'db12c_stby' SET PROPERTY 'LogShipping'='OFF'; Property "LogShipping" updated DGMGRL> SHOW DATABASE 'db12c_stby' 'LogShipping'; LogShipping = 'OFF'
DGMGRL> edit database 'db12c_stby' SET PROPERTY 'LogShipping'='ON'; Property "LogShipping" updated DGMGRL> SHOW DATABASE 'db12c_stby' 'LogShipping'; LogShipping = 'ON'
6. To change the state of the standby database to read-only and back APPLY-ON:
DGMGRL> EDIT DATABASE 'db12c' SET STATE='READ-ONLY'; Succeeded.
DGMGRL> show database db12c Database - db12c Role: PHYSICAL STANDBY Intended State: READ-ONLY .... Database Status: SUCCESS
To change it back back:
DGMGRL> shutdown DGMGRL> startup mount; DGMGRL> show database db12c Database - db12c Role: PHYSICAL STANDBY Intended State: OFFLINE ....
DGMGRL> EDIT DATABASE DB12C SET STATE = APPLY-ON; Succeeded. DGMGRL> show database db12c Database - db12c Role: PHYSICAL STANDBY Intended State: APPLY-ON ....
Logical Standby Commands
1. To Restart SQL apply on logical standby:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
2. To Stop SQL apply on logical standby:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3. Run the following SQL against the logical standby to start real-time SQL apply if the SQL apply failed with an error, and you are 100% certain that the transaction is safe to skip:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION;
4. To see unsupported tables for logical standby:
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE ORDER BY OWNER, TABLE_NAME;
5. To know which archive log sequences are at what stage for logical standby?
SQL> SELECT 'RESTART' "TYPE", P.RESTART_SCN "SCN", TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss') "TIME", L.SEQUENCE# "SEQ#" FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.RESTART_SCN < L.NEXT_CHANGE# UNION SELECT 'RESTART', P.RESTART_SCN, TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE# UNION SELECT 'APPLIED', P.APPLIED_SCN, TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.APPLIED_SCN < L.NEXT_CHANGE# UNION SELECT 'APPLIED', P.APPLIED_SCN, TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE# UNION SELECT 'MINING', P.MINING_SCN, TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.MINING_SCN < L.NEXT_CHANGE# UNION SELECT 'MINING', P.MINING_SCN, TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE# UNION SELECT 'SHIPPED', P.LATEST_SCN, TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN < L.NEXT_CHANGE# UNION SELECT 'SHIPPED', P.LATEST_SCN, TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'), L.SEQUENCE# FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#;
6. To know is the SQL Apply up to date:
SQL> SELECT TO_CHAR(LATEST_TIME,'yyyy/mm/dd hh24:mi:ss') "LATEST_TIME", TO_CHAR(APPLIED_TIME,'yyyy/mm/dd hh24:mi:ss') "APPLIED_TIME", APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
7. To know is the Logical standby applying changes? Run the following SQL against the Logical standby database:
SQL> SELECT REALTIME_APPLY, STATE FROM V$LOGSTDBY_STATE;
8. If the value of STATE is “NULL” or “SQL APPLY NOT ON” then the SQL Apply is not running. The value of REALTIME_APPLY should be Y to allow for real-time apply from the standby redo logs. To know what major Sql Apply events have occurred, run the following SQL against the Logical standby database:
SQL> SELECT TO_CHAR (EVENT_TIME, 'YYYY/MM/DD HH24:MI:SS') "EVENT_TIME", STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME;
9. To know what major Dataguard events have occurred, run the following SQL against the Logical standby database:
SQL> SELECT TO_CHAR (TIMESTAMP, 'yyyy/mm/dd hh24:mi:ss') "TIME", ERROR_CODE "ERROR", DEST_ID "DEST", MESSAGE FROM V$DATAGUARD_STATUS WHERE timestamp > TRUNC (SYSDATE + 6 / 24) ORDER BY timestamp DESC;
10 .To know where are the archive logs going and are there any achieving issues, run the following SQL against either the logical standby or primary database:
SQL> SELECT DEST_ID "DID", STATUS, DESTINATION, ARCHIVER, VALID_NOW, VALID_TYPE, VALID_ROLE, ERROR FROM V$ARCHIVE_DEST WHERE STATUS <> 'INACTIVE';