Some very usefull sql's for Datagaurd

Backup - DataGuard

Startup commands
To remove a delay from a standby
Cancel managed recovery
Register a missing log file
If FAL doesn't work and it says the log is already registered
Check which logs are missing
Disable/Enable archive log destinations
Turn on fal tracing on the primary db
Stop the Data Guard broker
Show the current instance role
Logical standby apply stop/start
See how up to date a physical standby is
Display info about all log destinations
Display log destinations options
List any standby redo logs


Startup commands

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


To remove a delay from a standby

 alter database recover managed standby database cancel;
alter database recover managed standby database nodelay disconnect;

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

Cancel managed recovery

 alter database recover managed standby database cancel;

Register a missing log file

 alter database register physical logfile '<fullpath/filename>';

If FAL doesn't work and it says the log is already registered

 alter database register or replace physical logfile '<fullpath/filename>';

If that doesn't work, try this...
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;

wait for the recovery to finish - then cancel
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;



TO Check which archive logs are missing

Run this on the standby...

select local.thread#
,      local.sequence# from
       (select thread#
       ,       sequence#
       from    v$archived_log
       where dest_id=1) local
where  local.sequence# not in
       (select sequence#
       from v$archived_log
       where dest_id=2 and
       thread# = local.thread#)
/


To Disable/Enable archive log destinations 

 alter system set log_archive_dest_state_2 = 'defer';
defer =stop shipping the archive logs to target

alter system set log_archive_dest_state_2 = 'enable';
defer =start shipping the archive logs to target

Turn on fal tracing on the primary db

 alter system set LOG_ARCHIVE_TRACE = 128;

Stopping the Data Guard broker

 alter system set dg_broker_start=false
/

Check the current instance role in primary or standby

 select    database_role
from    v$database
/


Logical standby apply stop/start (if you are standby DB is a logical standby)

Stop...
alter database stop logical standby apply;

Start...
alter database start logical standby apply;



Check how upto date sync is in physical standby

Run this on the primary to check max applied sequence

set numwidth 15
select    max(sequence#) current_seq
from    v$log
/


Then run this on the standby to check max applied sequence

set numwidth 15
select    max(applied_seq#) last_seq
from    v$archive_dest_status
/


Display info about all log destinations

To be run on the primary
set lines 100
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#
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
/


Display log destinations options

To be run on the primary
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
/


List any standby redo logs

 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#
/