Oracle Dataguard

-- Dataguard Versions and History
7:
Standby Database in V 7.3
Manual Copy of Redo/Archive Logs

8i:
Support Multiple Physical Standby Databases
Physical Standby Database Read Only Mode
Managed Recovery Mode ( Max of 4 Standby databases supported in this mode )
Automated Archive log Shipping ( through archive_log_dest_x and tns entry )
RMAN can be used to back up the standby database ( the backup can be used to recover the primary database )

9i:
Renamed as Oracle Data Guard
Switchover Introduced
Data guard Broker configuration with Switchover and Delay Options
Max of 9 Standby databases supported

Data Protect Modes:
maximum protection for no data loss; 
maximum availability for no data loss; 
and maximum performance for minimal data loss.

Logical Standby Database

10g: 
fast start failover ( automatic standby activation )
re-instate or reuse of old primary as new standby database ( flashback database need to be enabled )
Real Time Apply with Fast Start Failover
Flashback Database and Guaranteed Restore Point

data_file_nmae_convert log_file_name_convert


11g:
Active Data Guard ( readonly open and log-apply ) 
Snapshot Standby
RMAN Incremental Backup with "Block Change Tracking"
Lost-write detection
automatic standby activation in MaxPerformance mode


11gR2
Automatic Repair of corrupted blocks with the help of a physical standby database or in a physical standby databaseo
Redo transport compression now also supports SYNC setups
Configurable Real-Time Query Apply Lag Limit per session: STANDBY_MAX_DATA_DELAY = NONE | n
Flush any unsent redo to standby in mount state: ALTER SYSTEM FLUSH REDO to 'PHYCIAL_STANDBY_DB_UNIQUE_NAME'
LogMiner (Logical Standby) supports table compression ( OLTP and BASIC) and SecureFile LOBs  
Max of 30 standby databases supported

12c:
Multi-Instance Redo Apply (In Oracle RAC)
Parallel Multi-instance recovery
Support Diagnostic, Tuning Packs and SQL Plan Analyzer ( awr addm report can be run on active standby database )

18c:
Recovery Standby Database from Service
Global Temporary Tables

19c:
Active Dataguard DML Redirect:  alter system set adg_redirect_dml=true scope=both;
Automatic Standby Recovery through Reset Logs

-- ADG: Active Dataguard
-- 1): startup

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 2): shutdown
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

1): startup

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

2): shutdown

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

3): read only
ALTER DATABASE OPEN READ ONLY;

-- standby to read only

Cancel log apply services:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
SQL> ALTER DATABASE OPEN READ ONLY;

-- read only to standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

1):
1a):
TZ=US/Eastern

1b):

-- 1c): on primary
alter system archive log current;
!sleep 2
alter system archive log current;
!sleep 2
alter system archive log current;
!sleep 2
alter system archive log current;
!sleep 2
!sleep 5

-- 1d):
column thread# format 999999
column host_name format a32
column current_time format a40
set linesize 380 trimspool on pagesize 5000
SELECT h.host_name, d.name db_name,a.thread#,b.last_seq prmy_last_file,
      a.applied_seq stdby_last_file ,TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time,
to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') current_time, dbtimezone
FROM (SELECT   resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
           FROM gv$archived_log
          WHERE applied = 'YES'
           and RESETLOGS_CHANGE# in ( 
                   select RESETLOGS_CHANGE# from v$database 
                   --wrong: select distinct RESETLOGS_ID from gv$archived_log where RESETLOGS_TIME = (select max(RESETLOGS_TIME) from gv$archived_log)
                   --wrong: select distinct RESETLOGS_ID from gv$archived_log where RESETLOGS_TIME = (select max(RESETLOGS_TIME) from gv$archived_log where applied = 'YES')
                 )
       GROUP BY resetlogs_id, thread#) a,       
     (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
           FROM gv$archived_log
           where resetlogs_change# in (
                   select RESETLOGS_CHANGE# from v$database 
                   --wrong select distinct RESETLOGS_ID from gv$archived_log where RESETLOGS_TIME = (select max(RESETLOGS_TIME) from gv$archived_log)
                 )
       GROUP BY resetlogs_id, thread#) b,
      v$database d,v$instance h
WHERE a.thread# = b.thread#
and a.resetlogs_id=b.resetlogs_id
ORDER BY a.thread#;

1e):
column DEST_NAME format a32
select dest_id,dest_name,status,type,srl,recovery_mode from v$archive_dest_status ;
select dest_id,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

1f):

column name format a25
column value format a30
column unit  format a40
set linesize 380 trimspool on pagesize 5000
select name,value,unit from v$dataguard_stats;

1g):
select * from v$archive_gap;

1h):
column client_pid noprint
column CLIENT_DBID noprint
column CLIENT_PROCESS format a20
column group# format a12
set linesize 380 trimspool on pagesize 5000
select * from gv$managed_standby where process in ('MRP0', 'RFS') order by process desc,inst_id, thread#,sequence#;

-- defer and un-defer log shipping
show parameter log_archive_dest_state_3
alter system set log_archive_dest_state_3=DEFER scope=memory;
alter system set log_archive_dest_state_3=ENABLE scope=memory;

-- start standby instance

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- check applying is on or off
SELECT PROCESS, STATUS,SEQUENCE# FROM V$MANAGED_STANDBY where process like '%MRP%';
!ps -ef|grep -i mrp

-- PROCESS   STATUS        SEQUENCE#
-- --------- ------------ ----------
-- MRP0      WAIT_FOR_LOG    1591223

select open_mode,database_role,switchover_status from v$database;

grep -i -n database alert_sid.log|egrep -i "recovery|mount"

SELECT
a.thread#, b.open_mode, a.status thread_status,  
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'
WHEN ((b.open_mode='READ ONLY') AND (a.status='CLOSED')) THEN 'Inst. already open'
ELSE 'Recovery Information Unknown'
END recovery_status
FROM 
v$thread a,
v$database b,
v$instance c
WHERE a.thread#=c.thread#;

-- shutdown standby instance

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SHUTDOWN IMMEDIATE;

dataguard or standby setup

create physical standby database 

data guard setup 
archivelog mode
force logging
db_unique_name
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)'
...  ...

Fast Failover ( FSFO )

Fast Failover ( FSFO )
fast failover 

DGMGRL> show fast_start failover;
DGMGRL> show database srprim 'FastStartFailoverTarget';

select  FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;

Setup Dataguard Broker

dataguard Broker
dataguard broker 
-- alter system set dg_broker_start=true;

dataguard broker 1 
dataguard broker 2 

init.ora in primary and standby

-- on standy
*.fal_server='remote_db(primary_db)'
*.fal_client='remote_db(standby_db)'

*.log_archive_config='DG_CONFIG=(orcl,orclstby,orclstby2)'  # list all db unique names
*.log_archive_dest_1='LOCATION=/u99/oracle/bisp/archives/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstby'# db_unique_name for standby db
*.log_archive_dest_2 ='SERVICE=tns_entry_orcl ARCH NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'  #added primary db

*.log_archive_dest_state_1='ENABLE'# added forDG
*.log_archive_dest_state_2='defer'

*.log_archive_format='orcl_%r_%t_%S.arc'  # %S capital S means fixed length
*.log_archive_max_processes=8# added forDG

*.db_file_name_convert='/u03/oracle/data/orcl/orcl','/u03/oracle/data/orcl/orcl','/u04/oracle/data/orcl/bisp','/u04/oracle/data/orcl/orcl'

*.log_file_name_convert=''/u11/oracle/data/orcl/orcl','/u11/oracle/data/orcl/orcl','/u12/oracle/data/orcl/orcl','/u12/oracle/data/bisp/orcl'

*.standby_file_management='AUTO'

set linesize 180 trimspool on pagesize 1000
column name format A42
column display_value format A100

define mySourceSidLower=ti9p
define myTargetSidLower=tinp

define mySourceSidUpper=TI9P
define myTargetSidUpper=TINP

-- warning: multiple single quote value such as control_files will not work

select 'alter system set '|| name || '=' || 
case when regexp_like(decode ( display_value, null, '', DISPLAY_VALUE ),'^\d+(\.\d+)?$') then '' else '''' end  || 
replace(replace(DISPLAY_VALUE,'&&mySourceSidLower', '&&myTargetSidLower'), '&&mySourceSidUpper','&&myTargetSidUpper') || 
case when regexp_like(decode ( display_value, null, '', DISPLAY_VALUE ),'^\d+(\.\d+)?$') then '' else '''' end  || 
' sid=''*'' scope=both;'  cmd 
from v$parameter 
where 
-- ISDEFAULT='FALSE' and 
display_value is not null and 
name in (
'compatible',
'control_files',
'db_domain',
'db_file_name_convert',
'db_flashback_retention_target',
'db_block_checking',
'db_name',
'db_recovery_file_dest',
'db_recovery_file_dest_size',
'db_unique_name',
'diagnostic_dest',
'dispatchers',
'enable_pluggable_database',
'fal_client',
'fal_server',
'job_queue_processes',
'log_archive_trace',
'log_archive_config',
'log_archive_dest_2',
'log_archive_dest_3',
'log_archive_dest_4',
'log_archive_dest_state_2',
'log_archive_dest_state_3',
'log_archive_dest_state_4',
'log_archive_format',
'log_archive_max_processes',
'log_buffer',
'log_file_name_convert',
'recovery_parallelism',
'remote_login_passwordfile',
'service_names',
'smtp_out_server',
'standby_file_management' 
) order by name;

select distinct  ISDEFAULT  from v$parameter;
select distinct  ISMODIFIED from v$parameter;
select distinct  ISMODIFIED from v$parameter;
select distinct  ISADJUSTED from v$parameter;

set linesize 180 trimspool on pagesize 1000
column name format A42
column display_value format A100

select  name, DISPLAY_VALUE from v$parameter 
where 
-- ISDEFAULT='FALSE' and 
name in (
'compatible',
'control_files',
'db_domain',
'db_file_name_convert',
'db_flashback_retention_target',
'db_block_checking',
'db_name',
'db_recovery_file_dest',
'db_recovery_file_dest_size',
'db_unique_name',
'diagnostic_dest',
'dispatchers',
'enable_pluggable_database',
'fal_client',
'fal_server',
'job_queue_processes',
'log_archive_trace',
'log_archive_config',
'log_archive_dest_2',
'log_archive_dest_3',
'log_archive_dest_4',
'log_archive_dest_state_2',
'log_archive_dest_state_3',
'log_archive_dest_state_4',
'log_archive_format',
'log_archive_max_processes',
'log_buffer',
'log_file_name_convert',
'recovery_parallelism',
'remote_login_passwordfile',
'service_names',
'smtp_out_server',
'standby_file_management' 
) order by name;

-- db_name=orcl
-- db_unique_name=orclsrc or orcltgt

-- fal_client=orclsrc
-- fal_server=orcltgt

-- log_archive_format=orcl_%t_%s_%r.arc
-- log_file_name_convert=/u99/oracle/orcl/archives/, /u99/oracle/orcl/archives/
-- log_archive_config=DG_CONFIG=(orcl,orclsrc,orcltgt)

-- log_archive_dest_2='SERVICE=orclsrc ARCH NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsrc'
-- log_archive_dest_state_2=ENABLE DEFER

select  name, DISPLAY_VALUE from v$parameter where ISDEFAULT='FALSE' order by name;

select  name, DISPLAY_VALUE from v$parameter;

select * from v$parameter;

Start and Stop Apply

alter database RECOVER MANAGED STANDBY DATABASE cancel;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

sqlplus cmd> recover managed standby database cancel;

sqlplus / as sysdba
SHUTDOWN IMMEDIATE

startup nomount ;

alter database mount standby database;

ALTER DATABASE MOUNT STANDBY DATABASE;



-- check apply is on or not
select process from  v$managed_standby where process like 'MRP%'; 
!ps -ef|grep -i mrp
-- if no row selected 
alter database recover managed standby database disconnect from session; 
or 
alter database recover managed standby database using current logfile disconnect from session; -- real  time apply



ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

sqlplus cmd>recover standby database;


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

sqlplus cmd>recover standby database disconnect;


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8 DISCONNECT FROM SESSION;

-- delay or nodelay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


-- real time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

sqlplus cmd> recover managed standby database disconnect using current logfile; 



-- Failover:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER DATABASE OPEN;


select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE



-- BUG or Error Fixing
-- ERROR at line 1:
-- ORA-10877: error signaled in parallel recovery slave

-- check alert.log, find the archivelog file
-- scp -p ... orclarch_1_2134435_dbid.arc
-- alter database register logfile '/u0../orclarch_1_2134435_dbid.arc';

Convert A physical Standby Database Into An Active Dataguard Database

-- in sqlplus
1. open in read-only mode
alter database recover managed standby database cancel;
alter database open; 
2. return to recovery mode
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect; 



shutdown immediate;
startup;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect;

-- in dataguard broker
DGMGRL> edit database prod1dr set state='apply=off';
in sqlplus sql> alter database open read only;
DGMGRL> edit database prod1dr set state='apply=on'; 

Snapshot Standby

1. Enable Flashback Database Features On the Standby Database 
2. Using Dataguard Broker to Manage the Snapshot Standby Database



-- From physical Standby To snapshot standby
DGMGRL> convert database 'prod1s' to snapshot standby;
DGMGRL> show configuration;

## redo should still being sent to the snapshot standby database
-- Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
select process,status,sequence#, block# from v$managed_standby;



-- Revert Back ( From snapshot standby To physical Standby )
DGMGRL> convert database 'prod1s' to physical standby;
DGMGRL> show configuration;

## shutdown the physical standby database and restart it mount mode

Dataguard Status Monitoring

How To Check Data Guard Status 

set linesize 9000
column name format a25
column value format a20
column time_computed format a25
SELECT name, value, time_computed FROM v$dataguard_stats;

column message format a66
SELECT timestamp, facility, message FROM v$dataguard_status ORDER by timestamp;

column name format a32
column value format a32
set pagesize 1000

SELECT 'db_name' name, name value FROM v$database
union all
SELECT 'DB_UNIQUE_NAME' name, DB_UNIQUE_NAME value FROM v$database
union all
select 'open_mode' name, open_mode value from v$database
union all
select 'instance_name' name, instance_name from v$database,v$instance
union all
select 'instance status' name, status from v$instance
union all
SELECT 'log_mode' name, log_mode value FROM v$database
union all
SELECT 'DATABASE_ROLE' name,DATABASE_ROLE from v$database
union all
SELECT 'PROTECTION_MODE' name,PROTECTION_MODE from v$database
union all
SELECT 'PROTECTION_LEVEL' name,PROTECTION_LEVEL from v$database
union all
SELECT 'SWITCHOVER_STATUS' name,SWITCHOVER_STATUS from v$database
union all
select 'CONTROLFILE_TYPE' name, CONTROLFILE_TYPE from v$database
union all
select 'PRIMARY_DB_UNIQUE_NAME' name, PRIMARY_DB_UNIQUE_NAME from v$database
union all
select name, value status from v$parameter where name='db_unique_name'
;

select group#,sum(bytes/1024/1024)"Size in MB",status from v$log group by group#,status order by group#;
select * from v$log;
select group#,bytes,status from v$log;
select group#, thread#, sequence#, status from v$log;

select group#,sum(bytes/1024/1024)"Size in MB",status from v$standby_log group by group#,status order by group#;
select group#,bytes from v$standby_log;
select group#, thread#, sequence#, status from v$standby_log;

select * from v$archive_dest_status;
select * from v$standby_log;
select * from v$logfile order by group#,member;
select sequence#,applied,completion_time,next_time from v$archived_log order by 1;

SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG where APPLIED='YES' ORDER BY SEQUENCE#;

select a.sequence#, a.* from v$archived_log a where a.sequence# = (
SELECT max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES');


dataguard 920  
dataguard 1120

SELECT MESSAGE FROM V$DATAGUARD_STATUS;
set linesize 180 trimspool on

column name format A64
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

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 v$archived_log WHERE applied = 'YES' GROUP BY thread#
) a,           
(SELECT  thread#, MAX (sequence#) last_seq FROM v$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

SELECT name, SEQUENCE#,APPLIED,FIRST_TIME, NEXT_TIME 
FROM V$ARCHIVED_LOG 
where SEQUENCE# > ( select max(sequence#) from v$archived_log ) - 10
ORDER BY SEQUENCE#;

-- archivelog group by date
SET PAGESIZE 9999
col day format a15
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb 
FROM
(
SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# 
FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') 
ORDER BY 1 DESC
) A,
(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log 
) B;

-- floating dest_id for archived_log
select dest_id, count(*) cnt from v$archived_log group by dest_id order by dest_id;

-- too long to run
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#);

set lines 200 trimspool on
col name format a40
col value format a20
select * from v$dataguard_stats;

set serveroutput on;
DECLARE
   v_diff NUMBER := 0;
   v_hrs NUMBER := 0;
   v_min NUMBER := 0;
   v_sec NUMBER := 0;
   p_dte1 DATE;
   p_dte2 DATE;
   date1 long;
   date2 long;
   BEGIN
   date1 := 'select sysdate from dual';
   date2 := 'select MAX (next_time) FROM gv$archived_log where APPLIED=''YES''';
   execute immediate date1 into p_dte1;
   execute immediate date2 into p_dte2;
   v_diff := ABS(p_dte2 - p_dte1);
   v_hrs := TRUNC(v_diff, 0)*24;
   v_diff := (v_diff - TRUNC(v_diff, 0))*24; 
   v_hrs := v_hrs + TRUNC(v_diff, 0);
   v_diff := (v_diff - TRUNC(v_diff, 0))*60;
   v_min := TRUNC(v_diff, 0);
   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
   DBMS_OUTPUT.put_line(
   'Gap between current time and applied time: ' ||
   TO_CHAR(v_hrs) ||' hours '||
   TO_CHAR(v_min) ||' minutes '||
   TO_CHAR(v_sec) ||' seconds ');
END;
/
set serveroutput on;

-- v$recovery_progress, applying speed and estimate
set lines 200 trimspool on
col type format a30
col ITEM format a20
col comments format a20
select * from v$recovery_progress;

-- Estimated Catchup Time
set serveroutput on feedback off
DECLARE
   v_diff NUMBER := 0;
   v_hrs NUMBER := 0;
   v_min NUMBER := 0;
   v_sec NUMBER := 0;
   p_dte1 DATE;
   p_dte2 DATE;
   date1 long;
   date2 long;
   BEGIN
   date1 := 'select sysdate from dual';
   date2 := 'select max(TIMESTAMP) from v$recovery_progress';
   execute immediate date1 into p_dte1;
   execute immediate date2 into p_dte2;
   v_diff := ABS(p_dte2 - p_dte1);
   v_hrs := TRUNC(v_diff, 0)*24;
   v_diff := (v_diff - TRUNC(v_diff, 0))*24; 
   v_hrs := v_hrs + TRUNC(v_diff, 0);
   v_diff := (v_diff - TRUNC(v_diff, 0))*60;
   v_min := TRUNC(v_diff, 0);
   v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
   DBMS_OUTPUT.put_line(
   'Current Time Delay '||
   TO_CHAR(v_hrs) ||' hours '||
   TO_CHAR(v_min) ||' minutes '||
   TO_CHAR(v_sec) ||' seconds ');
END;
/

-- primary db or  read-only db ?
-- not working for physical standby db
select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;

set linesize 380 trimspool on pagesize 500
column DEST_NAME format a36
column DESTINATION format a28

SELECT
  DEST_ID,
  DEST_NAME,
  DESTINATION,
  STATUS,
  TYPE,
  SRL,
  DATABASE_MODE,
  RECOVERY_MODE,
  PROTECTION_MODE,
  SYNCHRONIZATION_STATUS
-- ,
--  STANDBY_LOGFILE_COUNT
FROM
  V$ARCHIVE_DEST_STATUS
WHERE
--  DEST_ID in (1,2,3,4)
DESTINATION is not null 
order by dest_id;

-- database_mode ( STARTED, MOUNTED, OPEN, OPEN_READ-ONLY, MONTED-STANDBY,UNKNOWN( not connected) )
-- status ( VALID, DEFERRED, INACTIVE,ERROR, DISABLED, ALTERNATE, FULL, ERROR )
-- type ( LOCAL, PHYSICAL, LOGICAL, SNAPSHOT, DOWNSTREAM, FAR SYNC, CROSS-INSTANCE, UNKNOWN )
-- SRL ( whether standby redo logfiles are used on the standby database (YES) or not (NO) )
-- RECOVERY_MODE ( MANAGED, IDLE, LOGICAL APPLY, LOGICAL REAL TIME APPLY, MANAGED REAL TIME APPLY )
-- PROTECTION_MODE ( MAXIMUM PERFORMANCE, MAXIMUM AVAILABILITY, MAXIMUM PROTECTION ... ... )

set linesize 180 trimspool on pagesize 5000
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;

set linesize 180 trimspool on pagesize 5000
select dest_id,status, gap_status,delay_mins 
from v$archive_dest_status 
order by dest_id;

set linesize 180 trimspool on pagesize 5000
column dest_name format a32
SELECT dest_id, dest_name,RECOVERY_MODE,db_unique_name, 
SYNCHRONIZATION_STATUS,SYNCHRONIZED,gap_status 
FROM V$ARCHIVE_DEST_STATUS
where db_unique_name not in ( 'NONE' );

select process, status, thread#, sequence#, block#, blocks,delay_mins from v$managed_standby ;

select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b 
where 
a.sid=b.sid and 
b.sid=
(
select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0')
);

col name format a10
select * from  V$STANDBY_EVENT_HISTOGRAM;

-- compare primary db with standby db

select * from v$archive_gap;
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;




select current_scn from v$database;
select scn_to_timestamp(1447102) from dual;

select current_scn, scn_to_timestamp(current_scn) from v$database;

Oracle Data Guard Protection Modes

Performance Versus Protection in Maximum Availability Mode

select * from V$DATAGUARD_CONFIG;

protection mode 1 
protection mode 2 
protection mode 3 

LOG_ARCHIVE_DEST_n attributes SYNC/AFFIRM versus SYNC/NOAFFIRM (FastSync)

-- put in all db unique name 
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CHICAGO,BOSTON)';

SELECT PROTECTION_MODE FROM V$DATABASE;

SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

-- ORA-16629: database reports a different protection level from the protection mode

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

DGMGRL> show configuration;

DGMGRL> edit configuration set protection mode as MAXPERFORMANCE;

tail -10000 /u01/app/oracle/product/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'

datagurad status 1 
datagurad status 2 

dataguard queries 

http://www.datadisk.co.uk/html_docs/oracle_dg/active_dg.htm
http://shivanandarao-oracle.com/2012/03/19/how-to-setup-active-dataguard-in-oracle-11g/

http://shivanandarao-oracle.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/

http://gavinsoorma.com/2010/09/11g-active-data-guard-enabling-real-time-query/

http://www.juliandyke.com/Blog/?p=107


http://www.oracledistilled.com/oracle-database/data-guard-switchover-to-a-physical-standby/
http://jaydeep-mukherjee.blogspot.com/2013/07/oracle-data-guard-switchoverstatus.html

-- password has to be the same:
orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y

listener.ora
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

Create Standby Controlfile

-- Create Control File For Physical Standby 	
-- unix
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/mydb_stby.ctl';
ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS '/home/oracle/control.ctl';

-- windows
ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS 'd:\oradata\control03.ctl';

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'd:\oradata\control03.ctl';

Adding Standby Logfiles

-- make sure all of the logfiles are in the same file size and block size
-- also you can using "alter database backup controlfile to trace;" to check the size

-- log group and size
select group#,sum(bytes/1024/1024)"Size in MB",status from v$log group by group#,status
order by group#;

select group#,sum(bytes/1024/1024)"Size in MB",status from v$standby_log group by group#,status
order by group#;

select distinct bytes, log_type from ( 
select bytes,'redo' log_type from v$log
union all
select bytes, 'standby' log_type from v$standby_log);

select group#,bytes, 'redo' log_type from v$log
union all
select group#,bytes, 'standby' log_type from v$standby_log;

-- ---------------------------------------------------------------------------------------

set pagesize 500 linesize 380 trimspool on
column member format a60
set linesize 380 trimspool on
select * from v$logfile order by group#, member;

column member format a64
select group#, status, type, member from v$logfile order by group#, member;

-- ----------------------------------------------------------------------------------------

alter database backup controlfile to trace;

-- create standby logfile group on both standby and primary db

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
alter database add standby logfile thread <n> group <n> 'file spec' size ....;

-- ALTER DATABASE DROP STANDBY LOGFILE GROUP 9 ;
alter database drop standby logfile group <n>;
alter database drop logfile group <n>;

-- ALTER DATABASE CLEAR LOGFILE GROUP 3;
-- ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

ALTER DATABASE 
  ADD LOGFILE GROUP 5 
(
    '/u01/oracle/data/db11g/db11glog5a.log',
    '/u02/oracle/data/db11g/db11glog5b.log'
  )
      SIZE 100M BLOCKSIZE 512;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 
(
    '/u01/oracle/data/db11g/db11glog5a.log',
    '/u02/oracle/data/db11g/db11glog5b.log'
  )
      SIZE 100M BLOCKSIZE 512;

ALTER DATABASE ADD STANDBY LOGFILE group 5 '/u11/oracle/data/orcl/orclstbylog05a.log' SIZE 100M BLOCKSIZE 512 REUSE;
-- invalid ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u12/oracle/data/orcl/orclstbylog05b.log' reuse TO '/u11/oracle/data/orcl/orclstbylog05a.log';

ALTER DATABASE ADD STANDBY LOGFILE group 1 ('/u01/app/oracle/oradata/DB11G/standby_redo01a.log') SIZE 100M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE group 2 ('/u01/app/oracle/oradata/DB11G/standby_redo02a.log') SIZE 100M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE group 3 ('/u01/app/oracle/oradata/DB11G/standby_redo03a.log') SIZE 100M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/app/oracle/oradata/DB11G/standby_redo04a.log') SIZE 100M BLOCKSIZE 512 REUSE;

-- invalid ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u01/app/oracle/oradata/DB11G/standby_redo01b.log' REUSE TO '/u01/app/oracle/oradata/DB11G/standby_redo01a.log';
-- invalid ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u01/app/oracle/oradata/DB11G/standby_redo02b.log' REUSE TO '/u01/app/oracle/oradata/DB11G/standby_redo02a.log';
-- invalid ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u01/app/oracle/oradata/DB11G/standby_redo03b.log' REUSE TO '/u01/app/oracle/oradata/DB11G/standby_redo03a.log';
-- invalid ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u01/app/oracle/oradata/DB11G/standby_redo04b.log' REUSE TO '/u01/app/oracle/oradata/DB11G/standby_redo04a.log';

Registering archive logfiles on a standby

ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_7.arc';
ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_8.arc';
ALTER DATABASE REGISTER LOGFILE '/standbydb/arch_dest/arcr_1_9.arc';
alter database register logfile '/standbydb/arch_dest/arch_1_10.arc'; 

rman> catalog start with '/standbydb/arch_dest'; 

standby db file and tablespace management

-- flip this flag to let standby db follow or not-follow primary db for tablespace and datafile management
alter system set standby_file_management=AUTO;
alter system set standby_file_management=MANUAL;

Manual switchover between primary database and standby database

primary-to-standby

select switchover_status from v$database;
select DATABASE_ROLE,  switchover_status  from v$database;

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER PENDING - This is a standby database and the primary database, switchover request has been received but not processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

TO STANDBY - This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the switchover request.


During normal operations it is acceptable to see the following values for SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY. 

During normal operations on the standby it is acceptable to see the values of NOT ALLOWED or SESSIONS ACTIVE. 




-- waiting sessions or kill sessions, then do switchover
select sid, process, program from v$session where type = 'USER';
alter session set tracefile_identifier='SWITCHOVERTEST_06032010';

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




-- shutdown old primary db, and start up as new standby db
shutdown immediate;
startup mount
or
startup nomount 
alter database mount standby database; 

-- Defer the remote archive destination on the old primary:
alter system set log_archive_dest_state_2=defer;




select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SWITCHOVER PENDING

if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause
(alter database commit to switchover to physical standby with session shutdown;)

standby-to-primary

select  DATABASE_ROLE, switchover_status  from v$database;
select switchover_status from v$database;
SESSIONS ACTIVE
TO PRIMARY

RECOVER MANAGED STANDBY DATABASE FINISH;

alter session set tracefile_identifier='SWITCHOVERTEST_STBY_06032010';

alter database commit to switchover to primary;
alter database commit to switchover to primary with session shutdown;

alter database open;
or
shutdown immediate ;
startup;




alter system set log_archive_dest_state_2=enable;
or
alter system set log_archive_dest_2='service="proddb" LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="proddb" net_timeout=30 valid_for=(all_logfiles,primary_role)' scope = both;



-- new-standby:
recover managed standby database cancel;

recover standby database;
recover managed standby database disconnect; 
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 parallel 2 using current logfile disconnect; 

set fal_client and fal_server and unset log_archive_dest_2 parameter in the new standby



-- new-primary:
alter system archive log current;
alter system switch logfile;



SQL> audit session whenever not successful;

Disable Active Data Guard For License Issue

Parameter to prevent license violation with Active Data Guard _

MOS Note 1436313.1

SQL> alter system set "_query_on_physical"=false scope=spfile;                             

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  314573800 bytes
Database Buffers	  201326592 bytes
Redo Buffers		    3821568 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> host oerr ora 16669
16669, 00000, "instance cannot be opened because the Active Data Guard option is disabled"
// *Cause:  The attempt to open the instance failed because the Active Data
//          Guard option was not enabled and Redo Apply was either running
//          or was about to be started by the Data Guard broker. 
// *Action: Stop Redo Apply or set the database state to APPLY-OFF and then 
//          open the database.

It is still possible to open the Physical Standby READ ONLY when the MRP background process is stopped:

DGMGRL> edit database physt set state=apply-off;

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Only drawback is that it is an undocumented parameter, 
so you should confirm with Oracle Support that it is okay to use it in your case.
Conclusion: There is an easy way to prevent license violation by accident with Real-Time Query. 

-- fix UNAME file due to standby_file_management off
alter system set standby_file_management=MANUAL;
alter database recover managed standby database cancel;

column name format A64

select * from v$recover_file where error like '%FILE%';

select file#,name from v$datafile where name like '%UNNAMED%';

select file#,name from  v$datafile where file# in (309,316,317,319);

alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00309' as '/u03/oracle/data/rightdbfile01.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00316' as '/u03/oracle/data/rightdbfile02.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00317' as '/u03/oracle/data/rightdbfile03.dbf';
alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00319' as '/u03/oracle/data/rightdbfile04.dbf';

-- for ASM or OMF files, just use the following 
SQL> alter database create datafile '/u01/oracle/ora11.203/dbs/UNNAMED00309' as new;

alter system set standby_file_management=AUTO;

select open_mode from v$database;
alter database recover managed standby database disconnect from session;

Rebuild Standby Database Using Rman Duplicate

-- rebuild standby database
orapwd file=orapworcl
orapwd file=orapworcl password=test entries=5

-- assume file_convert parameter are set

-- startup nomount
rman target sys/sys@primarydb auxiliary sys/sys@standbydb
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;
}