Database Health Report

Database Health Report (HTML)


vi Database_Health_Report.sql
:wq

sqlplus "/ as sysdba"

@Database_Health_Report.sql

cat Database_Health_Report.sql


SET MARKUP HTML ON

set feedback off
set serverout on
set wrap off
set pages 300
set lines 150
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a30
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col username format a25
col default_tablespace format a25
col temporary_tablespace format a25

SPOOL Database_Health_Report.html

PROMPT ================================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT ================================================================

PROMPT
PROMPT
PROMPT DATABASE STATUS
PROMPT =================
select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;

PROMPT
PROMPT
PROMPT DATABASE UPTIME
PROMPT =================

select
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
   sys.v_$instance;


PROMPT
PROMPT
PROMPT TOTAL DATABASE USAGE
PROMPT =====================
select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;


PROMPT
PROMPT
PROMPT DB PHYSICAL SIZE
PROMPT ==================
select sum(bytes/1024/1024) "DB Physical Size(MB)" from dba_data_files;


PROMPT
PROMPT
PROMPT DB ACUTAL SIZE
PROMPT ================
select sum(bytes/1024/1024) "DB Actual Size(MB)" from dba_segments;


PROMPT
PROMPT
PROMPT DATABASE NAME AND MODE
PROMPT ========================
select name, open_mode, log_mode from v$database;



PROMPT
PROMPT
PROMPT COUNT OF TABLESPACES
PROMPT =====================
select count(*) AS "No. of tablespaces" from v$tablespace;


PROMPT
PROMPT
PROMPT COUNT OF DATAFILES
PROMPT ===================
select count(*) AS "No. of Datafiles" from dba_data_files;


PROMPT
PROMPT
PROMPT COUNT OF INVALID OBJECTS
PROMPT ==========================
select count(*) from dba_objects where status='INVALID';


PROMPT
PROMPT
PROMPT COUNT OF ARCHIVED GENERATED LAST DAY
PROMPT =====================================
Select count(*) "No. of Archive Logs generated" from v$log_history  where to_char(first_time,'dd-mon-rrrr') in (to_char(sysdate-1,'dd-mon-rrrr'));


PROMPT
PROMPT
PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 90%
PROMPT =========================================================
select (sum(pins)/(sum(pins)+sum(reloads))) * 100 "Library Cache Hit Ratio" from v$librarycache;


PROMPT
PROMPT
PROMPT SGA STATISTICS
PROMPT ===============
set serveroutput on;
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/



PROMPT
PROMPT
PROMPT LOCKS
PROMPT =======
SELECT
   inst_id,
   sid sess,
   (select sql_hash_value from gV$session s where s.sid=lk.sid and s.inst_id = lk.inst_id) SQL_HASH_VALUE,
   ctime, id1, id2, trim(lmode) lmode, request, type,
   (select status from gv$session vs where vs.sid = lk.sid and vs.inst_id = lk.inst_id) SessStatus,
   (select object_name from dba_objects where object_id = id1) obj_locked,
   DECODE(request,0,'Holder: ','Waiter: ') position
FROM gV$LOCK lk
WHERE id1 IN (SELECT id1 FROM gv$LOCK WHERE lmode = 0)
ORDER BY id1,request


PROMPT
PROMPT
PROMPT DATAFILE USED  FREE SPAPCE
PROMPT ===========================
SELECT df.tablespace_name,
       df.file_name,
       df.size_mb,
       f.free_mb,
       df.max_size_mb,
       f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb
FROM   (SELECT file_id,
               file_name,
               tablespace_name,
               TRUNC(bytes/1024/1024) AS size_mb,
               TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
        FROM   dba_data_files) df,
       (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;


PROMPT
PROMPT
PROMPT TEMP TABLESPACE USED FREE
PROMPT ==========================
SELECT tf.tablespace_name,
       tf.file_name,
       tf.size_mb,
       f.free_mb,
       tf.size_mb - f.free_mb as USED,
       tf.max_size_mb,
       f.free_mb + (tf.max_size_mb - tf.size_mb) AS max_free_mb
FROM   (SELECT file_id,
               file_name,
               tablespace_name,
               TRUNC(bytes/1024/1024) AS size_mb,
               TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
        FROM   dba_temp_files) tf,
       (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  tf.file_id = f.file_id (+)
ORDER BY tf.tablespace_name,
         tf.file_name;


PROMPT
PROMPT
PROMPT TABLESPACE USED  FREE SPAPCE
PROMPT ==============================
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;


PROMPT
PROMPT
PROMPT TABLESPACE AVERAGE INCREASE PER DAY
PROMPT ====================================
SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname;


PROMPT
PROMPT
PROMPT TABLESPACE GROWTH
PROMPT ===================
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
 , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
 , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
 , DBA_HIST_TABLESPACE_STAT ts
 , DBA_HIST_SNAPSHOT sp
 , DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts#
 AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name
 AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname desc, days desc;


PROMPT
PROMPT
PROMPT TABLESPACE STATISTICS
PROMPT ======================
COL file_name FORMAT A45
select tablespace_name ,  file_name, autoextensible , bytes/1024/1024 "USED SPACE(MB)", maxbytes/1024/1024 " MAX SIZE(MB) " from dba_data_files order by tablespace_name,file_name;


PROMPT
PROMPT
PROMPT REDOLOG GROWTH RATE PER DAY
PROMPT ============================
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
   SELECT
   To_Char(First_Time,'YYYY-MM-DD') DAY,
   Count(1) Count#,
   Min(RECID) Min#,
   Max(RECID) Max#
FROM
   v$log_history
GROUP BY
   To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B
;


PROMPT
PROMPT
PROMPT NUMBER OF CONNECTED SESSIONS
PROMPT =============================
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       status,
       TO_CHAR(logon_time,'DD-Mon-YYYY HH24:MI:SS'),
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by status;



PROMPT
PROMPT
PROMPT IO ACTIVITIES
PROMPT ==============
select tablespace_name, regexp_replace(file_name,'^.*.\/.*.\/', '') file_name,sum(phyrds) reads, sum(phywrts) writes, sum(phyrds)+sum(phywrts) total from dba_data_files, v$filestat where file_id=file# group by tablespace_name, file_name order by tablespace_name, file_name;



PROMPT
PROMPT
PROMPT DATAFILE PHYSICAL READS AND WRITES
PROMPT ===================================
COL datafile FORMAT A45
select name datafile, phyrds reads, phywrts writes, phyrds+phywrts total from v$datafile a, v$filestat b where a.file# = b.file# order by total desc;


PROMPT
PROMPT
PROMPT BLOCKING QUERY
PROMPT ===============
select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;

PROMPT
PROMPT
PROMPT BLOCKER AND WAITER
PROMPT ===================
Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;


PROMPT
PROMPT
PROMPT NO of USER CONNECTED
PROMPT =====================
select count(distinct username) "No. of users Connected" from v$session where username is not null;



PROMPT
PROMPT
PROMPT NO of SESSIONS CONNECTED
PROMPT =========================
Select count(*) AS "No of Sessions connected" from v$session where username is not null;


PROMPT
PROMPT
PROMPT DISTINCT USERNAME CONNECTED
PROMPT ============================
Select distinct(username) AS "USERNAME" from v$session;



PROMPT
PROMPT
PROMPT INVALID OBJECT LIST
PROMPT ====================
COL object_name FORMAT A40
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;


PROMPT
PROMPT
PROMPT SCHEDULED JOBS
PROMPT ===============
set lines 100
col owner for a8
col job_name for a10
col job_action for a10
col start_date for a10
col repeat_interval for a13
col state for a10
col last_start_date for a10
col next_run_date for a10
select OWNER,JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs;


PROMPT
PROMPT
PROMPT DATAFILE IO
PROMPT =============
SELECT Substr(d.name,1,50) "File Name",
       f.phyblkrd "Blocks Read",
       f.phyblkwrt "Blocks Writen",
       f.phyblkrd + f.phyblkwrt "Total I/O"
FROM   v$filestat f,
       v$datafile d
WHERE  d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;


PROMPT
PROMPT
PROMPT DISPLAY LONG RUNNING OPERATIONS
PROMPT ================================
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;

Spool off

Active Data Guard Enabled / Disabled

How to Check Active Data Guard is  Enabled  or Not 

SQL> select  'Using Active Data Guard' ADG   from v$managed_standby m,v$database d where m.process like 'MRP%'  ;
        ADG
-----------------------
Using Active Data Guard

or from standby datbase

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE              CONTROLFILE
--------------------   ------------------
READ ONLY WITH APPLY   STANDBY

Conversion from Physical standby to Active Data Guard standby
-------------------------------------------------------------

Real-time query
===============

A physical standby database can be opened for read-only access and used to offload queries from a primary database. In addition, if a license for the Oracle Active Data Guard option has been purchased, a physical standby database can also be open while Redo Apply is active. This capability is known as the real-time query feature.

Redo Apply must be stopped before any physical standby database instance is opened.

If one or more physical standby instances are open, those instances must be stopped or restarted in a mounted state before starting Redo Apply

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
MOUNTED

Stop Apply Services
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> SHUT IMMEDIATE

SQL> START NOMOUNT

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>  SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

Database altered.

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

It enables us to have a physical standby read only open, while redo apply is still done in the background.

The Active Data Guard  contains the following features
------------------------------------------------------
Physical Standby with Real-time Query
Fast Incremental Backup on Physical Standby
Automatic Block Repair

Real-Time Apply is Enabled / Disabled

How to check real-time apply is enabled or not on Physical Standby Database

To start the real time apply (standby redo logfiles are must for it)

On Physical Standby
---------------------
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          NO  MANAGED

SQL>alter database recover managed standby database disconnect from session;

On Physical Standby
---------------------
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          YES  MANAGED REAL TIME APPLY

SQL>alter database recover managed standby database using current logfile disconnect from session;

Dataguard Physical Standby Start / Stop

How to start/stop Dataguard Physical Standby Database

SHUTDOWN
========
status of mrp:
NON-RAC:
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
RAC:
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

cancelling mrp:
recover managed standby database cancel;

shut immediate;

STARTUP
=======
startup nomount;

alter database mount standby database;

STARTING MRP0
=============
If a non-real-time apply standby then issue the following SQL statement to start Redo Apply:

alter database recover managed standby database disconnect from session;
--OR--
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 64;

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
MOUNTED

If a real-time apply standby then issue the following SQL statement to start Redo Apply:

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

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Now that the standby is in real-time query mode (that is, the standby is open in read-only mode and Redo Apply is active).

status of mrp:
NON-RAC:
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
RAC:
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

OPEN_MODE = READ ONLY WITH APPLY
--------------------------------
alter database open readonly;

Dataguard Troubleshooting / Commands

Dataguard Troubleshooting / Commands

select * from v$archive_gap;
select * from v$dataguard_stats;

select flashback_on from v$database;
SELECT * FROM v$block_change_tracking;

show parameter fal;
!tnsping <server/client>
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;

SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

RECOVERY_MODE
-------------
MANAGED

On Primary Database
===================
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest where DESTINATION dest_id=2;
/

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence generated"  FROM V$ARCHIVED_LOG  WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)  ORDER BY 1
/
select max(sequence#),thread# from gv$log group by thread#;

set numwidth 15
select max(sequence#) current_seq,archived,status from v$log;
/


On Standby Database
===================
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP
/
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
/
select max(sequence#),thread# from gv$archived_log where applied='YES' group by thread#;
/
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;
/

FIND GAP
--------
select thread#,low_sequence#,high_sequence# from v$archive_log;

LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

DEFER Log Shipping
------------------
alter system set log_archive_dest_state_2='DEFER' scope=both;

alter system set dg_broker_start=false;

ENABLE Log Shipping
-------------------
alter system set log_archive_dest_state_2='ENABLE' scope=both;

alter system set dg_broker_start=true;

DELAY CHANGE
------------
SQL> alter system set log_archive_dest_2='ARCH DELAY=15 OPTIONAL REOPEN=60 SERVICE=S1';


ARCHIVE_LAG_TARGET tells Oracle to make sure to switch a log every n seconds
----------------------------------------------------------------------------
ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1800 SCOPE=BOTH;
This sets the maximum lag to 30 mins.

On Primary to Display info about all log destinations
=====================================================
set pages 300 lines 300
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.archivertype,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
/

On Primary to Display log destinations options
==============================================
set pages 300 lines 300
set numwidth 10
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
/


==================================================================================================================

Standby Database
================
select NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL, CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database;

Some possible statuses for the MRP
----------------------------------
ERROR - This means that the process has failed. See the alert log or v$dataguard_status for further information.

WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed. Switch an archive log on the primary and requery v$managed_standby to see if the status changes to APPLYING_LOG.

WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved. Review the alert log to see if FAL_SERVER has been called to resolve the gap.

APPLYING_LOG - Process is applying the archived redo log to the standby database.

CHECK MANAGED RECOVERY PROCESS : SHOWS STATUS OF ARCH,RFS,MRP PROCESS.
------------------------------
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

select * from gv$active_instances;

!ps -ef|grep -i mrp

STARTING MRP0
-------------
ALTER DATABSE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

STOPING MRP0
------------
RECOVER MANAGED STANDBY DATABASE CANCEL;

To Display MRP0 Speed
---------------------
set pages 300 lines 300
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.Mm.Yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.Mm.Yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);

checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
select count(*) from V$ARCHIVED_LOG where applied='NO';
/

TIME TAKEN TO APPLY A LOG
-------------------------
set pages 300 lines 300

select TIMESTAMP,completion_time "ArchTime",SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services' order by TIMESTAMP desc;
/

CHECKING FOR DATAGAURD ERROR
----------------------------
set pages 300 lines 300
column Timestamp Format a20
column Facility  Format a24
column Severity  Format a13
column Message   Format a80 trunc

Select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') Timestamp,Facility,Severity,error_code,message_num,Message from v$dataguard_status where severity in ('Error','Fatal') order by Timestamp;

select  *  from v$ARCHIVE_GAP;

--OR---
Here is another script with v$dataguard_status:

select *
  from (select TIMESTAMP,
               completion_time "ArchTime",
               SEQUENCE#,
               round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
               round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                      OVER(order by TIMESTAMP)) * 24 * 60 * 60,
                     1) "Diff(sec)",
               round((blocks * block_size) / 1024 /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     1) "KB/sec",
               round((blocks * block_size) / (1024 * 1024) /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     3) "MB/sec",
               round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) -
                     completion_time) * 24 * 60 * 60,
                     1) "Lag(sec)"
          from v$archived_log a, v$dataguard_status dgs
         where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
           and dgs.FACILITY = 'Log Apply Services'
         order by TIMESTAMP desc)
 where rownum < 10;

Finding Missing Logs on 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#)
/

Check which logs have not been applied
--------------------------------------
alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS';
SELECT SEQUENCE#, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

REGISTRYING LOGFILE
-------------------
alter database register logfile '/file/path/';

RECOVERY PROGRESS ON STANDBY SITE
---------------------------------
v$managed_standby
v$archived_standby

v$archive_dest_status -  TO FIND THE LAST ARCHIVED LOG RECEIVED AND APPLIED ON THIS SITE.
select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;

v$log_history
select max(sequence#),latest_archive_log from v$log_history;

v$archived_log - individual archive log
select thread#,sequence#,applied,registrar from v$archived_log;

standby_file_management - playes when attributes of datafiles are modified primary site.
-IF IT IS RAW DEVICE STANDBY_FILE_MANAGEMENT SHOULD BE MANUAL.OTHERWISE AUTO

http://oraclerac.weebly.com/standby.html

==================================================================================================================

TROUBLESHOOTING A PHYSICAL STANDBY DATABASE:

NOTE: Pls check Metalink 232649.1 (Data Guard Gap Detection and Resolution)

On Standby server:

Run the below query to check the type of Standby database, PHYSCIAL or LOGICAL:

sqlplus "/ as sysdba"
select database_role from v$database;

If Physical Standby then follow:

Step1: Check which logs have not been applied:
======
alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS';
SELECT SEQUENCE#, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step2:Check if there is a gap in the archive logs:
======
SELECT * FROM V$ARCHIVE_GAP;

If there is a gap, then it is most likely that the log has been compressed on the Primary server, and the Standby FAL service cannot retrieve the log.If so, then temporarily stop archivelog compression job on the primary and unzip the required archive logs. After a few minutes, the FAL service will retrieve the log and the Standby apply services will resume.Check the progress by running the SQL in step-1 above.
If the logs haven't been processed after 5-10 minutes, then you will have to perform the following tasks:

Step3: Copy the (zipped) log to the standby archive log destination on the Standby server, (unzip the archive), and register,

ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_443.arc';

Step4: Check if this is a 'real-time apply standby:
=======
select recovery_mode from V$ARCHIVE_DEST_STATUS;

Step5: Stop/restart the standby apply services:
=======
alter database recover managed standby database cancel;

If a real-time apply standby then:
alter database recover managed standby database using current logfile disconnect from session;

Found this:
RECOVER MANAGED STANDBY DATABASE cancel;
ORA-16136: Managed Standby Recovery not active

RECOVER MANAGED STANDBY DATABASE disconnect from session;
Media recovery complete.

Else (non- realtime apply):
alter database recover managed standby database disconnect from session;

Check the progress by running the SQL in step-1 above.

Useful Standby query:
----------------------------
Startup standby database

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;

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;


Check which 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#);

Disable/Enable archive log destinations
alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';


Turn on fal tracing on the primary db
alter system set LOG_ARCHIVE_TRACE = 128;

Stop the Data Guard broker
alter system set dg_broker_start=false;

Show the current instance role
select name, open_mode, database_role from v$database;
=====
Logical standby apply stop/start
Stop Logical standby >> alter database stop logical standby apply;

Start Logical standby >> alter database start logical standby apply;

See how up to date a physical standby is: (Run this on the primary)
set numwidth 15
select    max(sequence#) current_seq from    v$log;

Then run this on the standby
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;

Display info about all log destinations (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 (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#;

Script for Standby archivelog monitoring….(removed the duplicate rows)

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;

==================================================================================================================

Dataguard Broker Configured or Not

How to check Dataguard Configured or Not

show parameter fal

show parameter dg_broker_start

show parameter log_archive_dest