On primary :
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;
On Standby :
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 THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
=============================ARCHIVELOG GAP RECOVERY SCENARION====================================================
On Standby :
SELECT TO_CHAR(CURRENT_SCN) FROM v$DATABASE;
—————
24848626949
On Primary :
rman target /
run
{
allocate channel c1 type disk format ‘/SMP_Logs/rman/%U.rmb’;
backup incremental from scn 24848626949 database;
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/SMP_Logs/rman/ForStandbyCTRL.bck’;
}
SCP backup* to standby;
On Standby:
srvctl stop database -d piapdr
startup nomount
rman target /
restore standby controlfile from ‘/u01/rman/ForStandbyCTRL.bck’;
sqlplus / as sysdba >> Both node
ALTER DATABASE MOUNT;
rman target /
catalog start with ‘/u01/rman/’;
catalog start with ‘+ORADATA1’;
catalog start with ‘+ORADATA2’;
report schema;
Note : Check Size column is shouldn’t be contain zero (0) values if yes than follow below steps for each datafiles which is has 0 values.
run
{
set newname for datafile 1 to ‘+ORADATA1/piapdr/datafile/SYSTEM.326.909536701’;
}
run
{
restore datafile 2;
}
run
run
{
set newname for datafile 2 to ‘+ORADATA1/piapdr/datafile/SYSAUX.325.909536707’;
}
run
{
restore datafile 2;
}
run
{
set newname for datafile 3 to ‘+ORADATA1/piapdr/datafile/UNDOTBS1.328.909536651’;
}
run
{
restore datafile 3;
}
run
{
set newname for datafile 4 to ‘+ORADATA1/piapdr/datafile/USERS.324.909536825’;
}
run
{
restore datafile 4;
}
run
{
set newname for datafile 44 to ‘+oradata2/piapdr/datafile/WEBINPWIFI.367.909537767’;
}
run
{
restore datafile 44;
}
ALTER DATABASE OPEN READ ONLY; ===== Both nodes.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING SESSION;