Dataguard useful commands

Verify archived redo log files were applied.
============================================

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


To determine if a gap exists:
============================

SELECT * FROM V$ARCHIVE_GAP;


Query the physical standby database to monitor log apply and log transport services activity at the standby site.
=================================================================================================================

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


Messages in regards to the data guard status
============================================

column message format a80
SELECT to_char(TIMESTAMP,'DD-MM-YY HH24:MM:SS'), MESSAGE FROM V$DATAGUARD_STATUS;

column message format a80
select  to_char(dgs.TIMESTAMP,'DD-MM-YY HH24:MM:SS')
,       dgs.severity
,       dgs.error_code
,       dgs.message
from    v$dataguard_status  dgs
where severity in ('Error','Fatal')
or      error_code <> '0'
/

>> With Borker


show database "DB_NAME" LatestLog;



Recovery start on standby
=========================

>> without broker

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

>> with broker
dgmgrl
connect sys
edit database "DB_NAME" SET STATE='ONLINE';
show database verbose "DB_NAME";





Recovery stoppen  op standby (without broker)
=============================================

>> Without broker

sqlplus / as sysdba
RECOVER MANAGED STANDBY DATABASE CANCEL nowait;

>> With Broker

edit database "DB_NAME" set state=LOG-APPLY-OFF;
show database verbose "DB_NAME";



Transport stop with broker
==========================

edit database "DB_NAME" set state=LOG-TRANSPORT-OFF;

reactivate -> edit database "DB_NAME" set state=online;



Role check
==========

select name, database_role from v$database;


Sessions check
==============

select username, osuser from v$session where username is not null;



Status check archiving
======================

column error format a55 tru
select     dest_id
,    status
,    error
from     v$archive_dest;

select     dest_id
,    process
,    transmit_mode
,    async_blocks
,       net_timeout
,    delay_mins
,    reopen_secs
,    register
,    binding
from     v$archive_dest;




Query archive status
====================

column  Status        format a10      heading "Status"
column  destination     format a35      heading "Destination"
SELECT DEST_ID "ID"
,      STATUS "DB_status"
,      DESTINATION
,      ERROR "Error"
FROM   V$ARCHIVE_DEST
/


To register a log that is not on the catalog (v$archived_logs)
==============================================================

alter database register logfile '/disk05/oradata/SWML/archives/1_0000031863_644148730.arc';

# If you get
# ORA-00317: file type 0 in header is not log file
# ORA-00334: archived log: '/disk05/oradata/DGT/archive/1_1_717000018.arc'
#
cancel recovery on the standby
copy (eventually) the archive log again to the standby
alter database register or replace physical logfile '/disk05/oradata/DGT/archive/1_1_717000018.arc';
resume recovery




 Run this query on the primary to know how big is your GAP and how long it will take:
======================================================================================


select sum(loc.blocks),
sum(((loc.blocks+1)*loc.block_size)/1024/1024)  "Size MB",
sum((((loc.blocks+1)*loc.block_size) * 8 ) / 1000000) "Size Megabits",
sum(((((loc.blocks+1)*loc.block_size) * 8 ) / 1000000 )) /&linkcapacitymbps "Seconds to Transfer (Uncomp)"
from
  (select * from v$archived_log where dest_id = 1) loc,
  (select * from v$archived_log where dest_id = 2) remote
where remote.sequence# (+) = loc.sequence#
  and remote.sequence# is null
  and loc.status = 'A'
order by loc.sequence# desc



Comment me if you find this post is useful also let me know if you need any new topics.