Search This Blog

Thursday, 29 June 2017

Standby SYNC check

Check redo received and applied on standby:
STANDBY SIDE:

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;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  56063                 54996       1067
         2                     85                    85          0


Primary:

select ads.dest_id,max(sequence#) "Current Sequence",
 max(log_sequence) "Last Archived"
   from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
   where ad.dest_id=al.dest_id
   and al.dest_id=ads.dest_id
   and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
 group by ads.dest_id;


   DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
         1              678           678
         2              678           679

Check for GAP on Standby:
Primary:
select max(sequence#) from v$log_history;

Standby :
select max(sequence#) from v$log_history; 


How to find the GAP on Standby with primay:


SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 Both primary and standby:

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

select sequence#, first_time, next_time, applied from v$archived_log;

========================================================================
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:

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

or

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING current ARCHIVED LOGFILE DISCONNECT;
     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;



This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This command does not disconnect the current SQL session.
=======================================================================
To stop Redo Apply, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


--Monitoring Redo Apply on Physical Standby Databases---

SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

select sequence#, applied from v$archived_log order by sequence# ;

select THREAD#,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED from v$archived_log;
select RECOVERY_MODE from v$archive_dest_status;

select ERROR,STATUS from v$archive_dest_status;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT FS_FAILOVER_STATUS "FSFO STATUS",FS_FAILOVER_CURRENT_TARGET TARGET,FS_FAILOVER_THRESHOLD THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" FROM V$DATABASE;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$LOG_HISTORY;

SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';
col DEST_NAME format a30;

SELECT DEST_NAME,STATUS,APPLIED_SEQ#,SYNCHRONIZATION_STATUS,GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_NAME='STANDBY_ARCHIVE_DEST';

============================================
Restore Point:

col name format a30
col value format a30

SELECT name, value  FROM gv$parameter WHERE name LIKE '%flashback%';

SELECT flashback_on, log_mode FROM v$database;

-- normal restore point
CREATE RESTORE POINT BOPPUDI_1_BEFORE_DEMAGE;

col name format a15
col time format a32
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- ----------------------------------- --------------------- --- ------------
BOPPUDI_1_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 05.28.57.000000000 AM                         8 YES            0
BOPPUDI_2_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 08.39.53.000000000 AM                         8 YES   4.2950E+10


HOW TO RECOVER FROM RESTORE POINT:
-----------------------------------------------------------
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

shutdown immediate;

startup mount;

flashback database to restore point before_damage;

alter database open resetlogs;


DROP RESTORE POINT:
-------------------------------------
DROP RESTORE POINT BOPPUDI_RESTORE;