DATAGUARD SYNC - How to register the archive logs in standby - How to delete archivelogs from standby:

#################################################################################
DATAGUARD SYNC
#################################################################################

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;


select max(sequence#), thread#
from v$archived_log
group by thread#
order by 2;


select max(sequence#), thread#, applied
from v$archived_log
where applied='YES'
group by thread#, applied
order by 2,3;


set linesize 300
SELECT INST_ID,PROCESS,   STATUS, THREAD#,  SEQUENCE#,    BLOCK#,   BLOCKS, DELAY_MINS from gv$managed_standby;

select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') TIME from V$MANAGED_STANDBY where PROCESS='MRP0';

#################################################################################
How to register the archive logs in standby
#################################################################################

select PROCESS, SEQUENCE#, THREAD#, BLOCK#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') TIME from V$MANAGED_STANDBY where PROCESS='MRP0';

Step 1:
------

copy the missing file from sourec.

Step 2:
------

alter database register logfile '/tmp/patches/sagardb_100313/thread_2_seq_118083.3191.827805009';


#################################################################################
How to delete archivelogs from standby:
#################################################################################

option-3)
Here I am pulling the max sequence numbers of the archivelog for each thread( since I have 3 -node RAC) and doing minus 10 so that I will have at least 10 archivelogs in my disk at any given time for any thread and then passing these sequence numbers to RMAN to delete the archivelogs.

Below is the script.

#!/bin/ksh
export ORACLE_HOME=/u02/u0001/oracle/sagaruct/10.2.0/db_1
export ORACLE_SID=STANDBYDBY1
export NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
rm /fin02/u0001/scripts/logs/1.tmp
$ORACLE_HOME/bin/sqlplus -s / as sysdba<set heading off
set pages 0 echo off feedback off
spool /fin02/u0001/scripts/logs/1.tmp
select thread#,max(sequence#)-10 from v\$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;
spool off;
exit;
EOF

seq1=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==1{print $2}'`
seq2=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==2{print $2}'`
seq3=`cat /fin02/u0001/scripts/logs/1.tmp | awk 'NR==3{print $2}'`
echo $seq1 $seq2 $seq3

$ORACLE_HOME/bin/rman msglog /fin02/u0001/scripts/logs/STANDBYDBY_ARC_DEL.${TODAY}_${TIME}.log << EOF

connect target /
run{
delete noprompt archivelog until sequence $seq1 thread 1;
delete noprompt archivelog until sequence $seq2 thread 2;
delete noprompt archivelog until sequence $seq3 thread 3;
}
EOF

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

   THREAD# MAX(SEQUENCE#)-10
---------- -----------------
         1             19515
         2             19316

archived log file name=+RECO/pgrptdg/archivelog/2014_08_15/thread_2_seq_15797.1527.855650967 RECID=30269 STAMP=855650972

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

   THREAD# MAX(SEQUENCE#)-10
---------- -----------------
         1             19515
         2             19316

delete noprompt archivelog until sequence 19515 thread 1;

delete noprompt archivelog until sequence 19316 thread 2;


delete noprompt archivelog until sequence 19316 thread 2;

SQL> select thread#,max(sequence#)-10 from gv$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;

   THREAD# MAX(SEQUENCE#)-10
---------- -----------------
         1             19567
         2             19368

delete noprompt archivelog until sequence 19316 thread 2;
delete noprompt archivelog until sequence 19567 thread 1;

   THREAD# MAX(SEQUENCE#)-10
---------- -----------------
         1             19618
         2             19418



RMAN> delete noprompt archivelog until sequence 19515 thread 1;