Standby Commands

Start recovery of standby :

alter database mount standby database

recover managed standby database    or
alter database recover managed standby database using current logfile disconnect from session  or

alter database recover managed standby database cancel


-- Check standby role and disk space

select name,db_unique_name,database_role,switchover_status from v$database;


select NAME,TOTAL_MB/1024,FREE_MB/1024,STATE from v$ASM_DISKGROUP;



--Check Gap from Primary : : Check applied and archived sequence difference

set lines 200
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4

select ds.dest_id id, ad.status, ds.database_mode db_mode, ad.archiver type, ds.recovery_mode,
         ds.protection_mode, ds.standby_logfile_count "SRLs", ds.standby_logfile_active active,
         ds.archived_seq# , ds.applied_seq#
from v$archive_dest_status ds, v$archive_dest ad
where ds.dest_id = ad.dest_id
--and ad.status != 'INACTIVE'
order by ds.dest_id;


select recid, dest_id, thread#, sequence#, first_time, completion_time, creator, registrar, archived, applied, deleted, status
from v$archived_log
where standby_dest='YES' and status='A';


For RAC :

select arc.dest_id,arc.thread# ,arc.Archived  , app.Applied ,  (arc.Archived  - app.Applied)  gap
from
(select dest_id,thread#,max(sequence#) Archived from v$archived_log where ARCHIVED='YES' group by dest_id,thread# order by DEST_ID,THREAD# ) arc ,
(select dest_id,thread#,max(sequence#) Applied from v$archived_log where APPLIED='YES' group by dest_id,thread# order by DEST_ID,THREAD# ) app
where arc.dest_id = app.dest_id(+)
  and arc.thread# = app.thread#(+)

order by arc.dest_id,arc.thread#

-- Check MRP running

select process from v$managed_standby where process like 'MRP%';

--Last Applied archive log in standby

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


--Keep few and delete applied archives

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

rman> delete archivelog until sequence ? thread 1;

rman> delete archivelog until sequence ?  thread 2;


-- Startup commands for Standby

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;

-- Cancel managed recovery and then shutdown

First on primary : alter system set log_archive_dest_state_2 = 'defer';
alter database recover managed standby database cancel;



-- From Primary : Check log destinations properties

set numwidth 8 lines 100
column id format 99

select dest_id id, archiver, transmit_mode, affirm, async_blocks async, net_timeout net_time,
         delay_mins delay, reopen_secs reopen, register,binding
from v$archive_dest
order by
dest_id;

---Check FRA usage

SELECT file_type, space_used * percent_space_used / 100 / 1024 / 1024 used,
space_reclaimable* percent_space_reclaimable/ 100/ 1024/ 1024 reclaimable,frau.number_of_files
FROM v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

-- Check if any standby redo logs used

set lines 100 pages 999
col member format a70

select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st , v$logfile lf
where st.group# = lf.group# ;