DR queries

DOC ID :  (Doc ID 836986.1) --- Steps to restore from incremental backup

Check the Difference :
========================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE
 (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
 FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE
 (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


SET LINES 800
SET PAGESIZE 10000
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF GAP ON REPORT
select primary.thread#,
       primary.maxsequence primaryseq,
       standby.maxsequence standbyseq,
       primary.maxsequence - standby.maxsequence gap
from ( select thread#, max(sequence#) maxsequence
       from v$archived_log
       where archived = 'YES'
         and resetlogs_change# = ( select d.resetlogs_change# from v$database d )
       group by thread# order by thread# ) primary,
     ( select thread#, max(sequence#) maxsequence
       from v$archived_log
       where applied = 'YES'
         and resetlogs_change# = ( select d.resetlogs_change# from v$database d )
       group by thread# order by thread# ) standby
where primary.thread# = standby.thread#;
===============================================================================================================================================

Primay:

select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;

 

Standby:

select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;

==================================================================================================================================================

SELECT dest_id , SEQUENCE# , applied FROM gv$archived_log where SEQUENCE#  > ( SELECT MAX(SEQUENCE#) - 10 FROM  gv$archived_log ) ORDER BY SEQUENCE#;

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG where NAME='+RECOC1/rmsprdx/archivelog/2020_10_29/thread_2_seq_602.2162.1055114445';
===============================================================================================================================================

select status,process,sequence# from gv$managed_standby order by 2;
select unique inst_id,group#,thread#,sequence#,members from gv$log;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
   
alter database recover managed standby database using current logfile disconnect from session parallel 32;


alter system set log_archive_dest_2='SERVICE="TAIP_DDC",LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="TAIP_DDC" register net_timeout=180 valid_for=(online_logfile,primary_role)';


select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- ------------ -------------------- ---------------- --------------------
WHESDGD   ARCHIVELOG   MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE


============================================================================================================================================================

column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
 ,decode(rectype,11,'YES','NO') reclaimable,count(*)
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time
 ,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/


===================================================================================================================================================

select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
           ,count(*),min(sequence#),max(sequence#)
     from v$archived_log left outer join sys.x$kccagf using(recid)
     where is_recovery_dest_file='YES' and name is not null
     group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/

====================================================================================================================================================


Datagurad resolve gap using incremental backup : Doc ID 836986.1



=====================================================================================================================================================

To know the all the parameter of DG
set sqlbl on
set lines 200
col name for a30
col value for a50
select name,value from v$parameter where name in ('db_file_name_convert','log_file_name_convert',
'db_name','db_unique_name','db_domain','service_names','log_archive_config','log_archive_dest_1',
'log_archive_dest_2','db_recovery_file_dest','db_recovery_file_dest_size',
'fal_server','local_listener','instance_name');


=======================================================================================================================================================

SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

SQL> SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;

set num 30
set lines 200 pages 200
col member for a85
select l.group#, l.thread#, l.sequence#, l.first_change#, l.next_change#, l.bytes, f.type, f.member, l.status from v$log l, v$logfile f where l.group#=f.group#
order by f.type, l.group#;


select thread#, max(sequence#) "Last Standby Seq Received"
           from     v$archived_log val, v$database vdb
           where    val.resetlogs_change# = vdb.resetlogs_change#
           group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied"
from     v$archived_log val, v$database vdb
where    val.resetlogs_change# = vdb.resetlogs_change#
and      val.applied in ('YES','IN-MEMORY');


select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS from gv$managed_standby where process like '%MRP%';