====== Oracle Data Guard ======

==== Enable / Stop Redo Apply ====
<code>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;</code>

==== 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).\\

<code>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;</code>

On Primary Database ... 
<code>ALTER SYSTEM SWITCH LOGFILE;</code>

Transport and Apply Lag should now always be 0 seconds.

==== SQL Scripts ====
Primary
<code>-- 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';</code>

Primary/Standby
<code>-- 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#;</code>

Standby
<code> -- 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;

-- Check if Real Time Apply is Enabled
col DEST_NAME format a25
select DEST_ID,dest_name,status,type,srl,recovery_mode 
from v$archive_dest_status 
where status <> 'INACTIVE';</code>

==== 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.\\
Primary Database: DBPRIM\\
Standby Database: DBSTBY\\

<code>PRIMARY: alter system set log_archive_trace=8191;
STANDBY: alter system set log_archive_trace=8191;

PRIMARY: srvctl modify database -d DBPRIM -s mount
STANDBY: srvctl modify database -d DBSTBY -s open

PRIMARY: select switchover_status from v$database; (Value should say TO_STANDBY or SESSIONS_ACTIVE)
PRIMARY: srvctl stop instance -d DBPRIM -i DBPRIM2
PRIMARY: alter system archive log current;

STANDBY: srvctl stop instance -d DBSTBY -i DBSTBY2

PRIMARY: alter database commit to switchover to physical standby with session shutdown;

STANDBY: select switchover_status from v$database; (Value should say TO_PRIMARY or SESSIONS_ACTIVE)
STANDBY: alter database commit to switchover to primary;
STANDBY: alter database open;
STANDBY: srvctl start instance -d DBSTBY -i DBSTBY2

PRIMARY: shutdown immediate;
PRIMARY: srvctl start database -d DBPRIM
PRIMARY: alter database recover managed standby database using current logfile disconnect;

STANDBY: srvctl modify database -d DBSTBY -r primary
PRIMARY: srvctl modify database -d DBPRIM -r physical_standby

PRIMARY: alter system set log_archive_trace=0;
STANDBY: alter system set log_archive_trace=0;</code>

==== Failover ====
https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB4773
<code>
SELECT STATUS, INSTANCE_NAME, DATABASE_ROLE from V$DATABASE, V$INSTANCE;

ALTER DATABASE RECOVER MANAGED DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED DATABASE FINISH;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE OPEN;

SELECT STATUS, INSTANCE_NAME, DATABASE_ROLE from V$DATABASE, V$INSTANCE;
</code>



==== 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 ... \\

<code>ORA-16669: instance cannot be opened because the Active Data Guard option is disabled </code>