User Tools

Site Tools


oracledataguard

This is an old revision of the document!


Oracle Data Guard

Enable / Stop Redo Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 240 DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Standby Redo Logs

This will allow you to do real-time apply, rather than waiting for a log switch.
Creating standby redo logs allows the current redo log on the primary to be written to the standby redo logs on the physical standby.
Data loss is therefore timed in the seconds if you need to failover.

Always create 1 more standby redo log group than the number of redo log groups on the primary.
It is best practice to create them on the primary database as well (in case of switchover).

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('F:\ORADATA\DUBETECH\STANDBY10A.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('F:\ORADATA\DUBETECH\STANDBY11A.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('F:\ORADATA\DUBETECH\STANDBY12A.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('F:\ORADATA\DUBETECH\STANDBY13A.LOG') SIZE 50M;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

On Primary Database …

ALTER SYSTEM SWITCH LOGFILE;

Transport and Apply Lag should now always be 0 seconds.

SQL Scripts

Primary

-- Check Specific Standby error for log_archive_dest_2
set linesize 170
col destination format a90
col error format a20
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

-- Check Standby Errors for log_archive_dest_n
set linesize 170
col destination format a90
col error format a20
SELECT DEST_ID, DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET = 'STANDBY';

Primary/Standby

-- Compare Archived Logs on Primary and Standby
SELECT THREAD# THREAD, MAX(SEQUENCE#) SEQUENCE FROM GV$ARCHIVED_LOG 
WHERE ARCHIVED = 'YES' 
AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM GV$ARCHIVED_LOG)
GROUP BY THREAD#
ORDER BY THREAD#;

Standby

 -- Check Apply and Transport Lag
col name format a16
col value format a16
col time_computed format a30
SELECT name, value, time_computed FROM V$DATAGUARD_STATS WHERE name like '%lag';

-- Media Recovery Information
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
set linesize 160
col timestamp format a20
col facility format a24
col severity format a13
col message format a100 trunc
select timestamp, facility, severity, message
from v$dataguard_status
order by timestamp;

Switchover

If the database you are using RAC and the database version is 11gR2 or less then you need to shut down all database instances apart from the first one before doing a switchover. This is not neccessary any more in 12c.

The example below assumes a 2 Node RAC system using 10gR2.

Active Data Guard

It is very easy to accidentally open a Physical Standby database and doing so records use of Active Data Guard.
You would then need to recreate the physical standby in order to remove this usage from the Oracle views.
There is however an unsupported way to stop this occurring.
I'm not recommending that this is implemented but it is a nice feature to know.
Use a hidden initialisation parameter called “_query_on_physical” and set this to FALSE.
Now if you attempt to open the Physical Standby database you will get the following error message …

ORA-16669: instance cannot be opened because the Active Data Guard option is disabled 
oracledataguard.1470904311.txt.gz · Last modified: 2025/03/08 22:23 (external edit)