Anuj Singh Oracle DBA: 2 Jun 2013

Sunday, 2 June 2013

Oracle Database report

good

database_check.sql

Database report

=====
Set Heading Off
Set Termout Off

Column Var_Date new_value Var_Date noprint

Select To_Char(Sysdate, 'YYYY-MM-DD') Var_Date from v$database ;

Column Var_Database new_value Var_Database noprint

Select Name Var_Database from v$database ;

Set Termout On

alter system checkpoint;
alter system check datafiles;

spool Chk_db_&Var_Database._&Var_Date..log

@heading.sql
-- ----------------------------------------------------------------------- ---
-- Set Sql prompt (log.sql) ---
-- ----------------------------------------------------------------------- ---

Set Linesize 120
Set Pagesize 60
Set SQLPROMPT 'Sql+>'
Set Desc Linenum On

Set Arraysize 1
Set Long 2000
Set Serveroutput On size 900000 ;

-- Alter session Set nls_date_format="DD-MM-YYYY HH24:MI:SS";

Set Heading Off
Set Feedback Off
Set Verify Off

Column Var_Date new_value Var_Date noprint

-- Select To_Char(Sysdate, 'DD-MM-YYYY HH24:MI') Var_Date from v$database ;

Select To_Char(Sysdate, 'dd-Mon-yyyy:hh:mi') Var_Date from v$database ;

-- Prompt ---

Select
' ** &Var_Date ** DB : ' || Name || ' ** Inst. : '||Instance_Name||' ** '
from v$database , v$instance;

Set Heading On

column sid heading "Id" format 9999
column spid heading "Unix" format A7
column username heading "Utilis." format A10
column terminal heading "Terminal" format A16
column program heading "Programme" format A32 word_wrapped
select s.sid, p.spid, substr(s.username,1,20) username, s.terminal, p.Program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum=1);

Set Heading Off
Set Termout Off

Column Var_Prompt new_value Var_Prompt noprint

Select
''''||Rpad(Lpad(Initcap(Substr(Name,1,4)),3,'*'),4,'*')||'>''' Var_Prompt
from v$database
;

Set SQLPROMPT &Var_Prompt

Set Termout On

-- Prompt

-- Prompt ---

Set Heading On
Set Feedback On

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Instance Information ---

prompt -- ----------------------------------------------------------------------- ---

Set Heading Off
Set Feedback Off
Set Verify Off

column status format a120 wrap heading "Status"

Select status_01 ||' | '||status_02 status
From
(Select ' Database_Name '||Lpad(Name,12) Status_01 from V$Database)
, (Select ' Database_Open_Mode '||Lpad(open_mode,11) Status_02 from V$database)
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Instance_Name '||Lpad(Instance_Name,14) Status_02 from V$Instance)
, (Select ' Instance_Status '||Lpad(Database_Status,10) Status_01 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Host_Name '||Lpad(Host_Name,18) status_02 from V$Instance)
, (Select ' Cpu_Count '||Lpad(value,8) status_01 from V$PARAMETER where name='cpu_count' and value is not null)
Union
Select status_01||' | '||status_02 status
From
(Select ' Startup_Time '||To_Char(Startup_Time, 'DD-MM-YYYY HH24:MI') Status_02 from V$Instance)
, (Select ' Instance_Logins '||Lpad(logins,14) Status_01 from V$Instance)
;

Select status_01||' | '||status_02 status
From
(Select ' DBID '||Lpad(DBID,18) Status_01 from V$Database)
, (Select ' Flashback '||Lpad(FLASHBACK_ON,20) Status_02 from V$database)
Union
Select status_01||' | '||status_02 status
From
(Select ' Version '||Lpad(Version,12) Status_01 from V$Instance)
, (Select ' Instance_Role '||Lpad(Instance_Role,16) Status_02 from V$Instance)
Union
Select status_01||' | '||status_02 status
From (Select ' Checkpoint_Chg# '||Lpad(checkpoint_change#,14) Status_01 from v$database)
, (Select ' Current_Scn '||Lpad(current_scn,20) Status_02 FROM v$database)
;

column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(select ' Dynamic SGA '||Lpad(decode( (select 'OK' From (Select upper(value) val from V$PARAMETER where name='db_cache_advice' and value = 'ON') t1 , (Select Upper(value) val from V$PARAMETER where name='statistics_level' and upper(value) in ('TYPICAL', 'ALL')) t2 , (select value val from V$PARAMETER where name = 'sga_target' ) t3 Where t1.val = 'ON' and t2.val in ('TYPICAL', 'ALL') and t3.val != '0' ) , 'OK', 'Installed', 'Not Installed'),17) status_01 from dual)
, (select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),26-length(Name)) status_02 from V$PARAMETER where name = 'sga_target')
Union
Select status_01||' | '||status_02 status
From
(Select ' Db_Cache_Advice '||Lpad(value,12) status_01 from V$PARAMETER where name='db_cache_advice')
, (Select ' Statistics_Level '||Lpad(value,14) status_02 from V$PARAMETER where name='statistics_level')
Union
Select status_01||' | '||status_02 status
From
(select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) status_01 from V$PARAMETER where upper(name) = 'SGA_MAX_SIZE')
, (select ' Free Memory (Mb)' || Lpad(To_Char(current_size/1024/1024),16) status_02 from V$SGA_DYNAMIC_FREE_MEMORY )
;

--Union
--Select status_01||' | '||status_02 status
-- From
-- (Select ' Cache Read F. 0.85 (Mb) '|| Lpad(to_char(size_for_estimate),6) status_01 FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size') AND advice_status = 'ON' and estd_physical_read_factor >= 0.84 and estd_physical_read_factor < 0.86 and rownum=1)
-- , (Select ' Cache Read F. 0.75 (Mb) '|| Lpad(to_char(size_for_estimate)),8) status_02 FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size') AND advice_status = 'ON' and estd_physical_read_factor >= 0.74 and estd_physical_read_factor < 0.76 and rownum=1)
--;

Select status_01 ||' | '||status_02 status
From
(select ' Automatic PGA '||Lpad(decode( (select 'OK' From (Select upper(value) val from V$PARAMETER where upper(name)='WORKAREA_SIZE_POLICY' and Upper(value) = 'AUTO') t1, (select value val from V$PARAMETER where Upper(name) = 'PGA_AGGREGATE_TARGET' and value != '0') t2 Where t1.val = 'AUTO' and t2.val != '0') , 'OK', 'Installed', 'Not Installed'),15) status_01 from dual)
, (SELECT ' Pga consumption (Mb) '||Lpad(round((VALUE/1024/1024)),11) status_02 FROM V$PGASTAT WHERE NAME = 'total PGA allocated')
Union
Select status_01||' | '||status_02 status
From
(select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) status_01 from V$PARAMETER where Upper(name)='PGA_AGGREGATE_TARGET')
, (Select ' Workarea_size_Policy '||Lpad(value,11) status_02 from V$PARAMETER where Upper(name)='WORKAREA_SIZE_POLICY')
;

Select status_01||' | '||status_02 status
From
(Select ' Cursor_Sharing '||Lpad(value,12) status_01 from V$PARAMETER where name='cursor_sharing')
, (Select ' Remote_login_p. '||Lpad(value,14) status_02 from V$PARAMETER where name='remote_login_passwordfile')
Union
Select status_01||' | '||status_02 status
From
(Select ' Default Temp '||Lpad(property_value,14) status_01 from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE')
, (Select ' Undo tablespace '||Lpad(value,16) status_02 from V$PARAMETER where name='undo_tablespace')
Union
Select status_01||' | '||status_02 status
From
(Select ' Tempfile '||Lpad(decode(To_Char(count(*)),'0', 'KO !!!', 'OK'),15) status_01 from dba_temp_files where tablespace_name in (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE' and property_value != 'SYSTEM') and (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE' and property_value != 'SYSTEM') is not null)
, (Select ' Undo Suppr. Er. '||Lpad(value,16) status_02 from V$PARAMETER where name='undo_suppress_errors')
;

-- (Select ' Nb Tempfile '||Lpad(To_Char(count(*)),14) status_01 from dba_temp_files where tablespace_name in (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'))

select ' '||Name||' '||lpad (value,52-length(Name)) from V$PARAMETER where name like '%stand%' and value != '0' and (select ' '||Name||' '||Rpad (value,68-length(Name)) dest from V$PARAMETER where name like '%log_archive_dest%' and upper(value) like '%SERVICE%' and value != '0') is not null ;

select ' '||Name||' '||Rpad (value,68-length(Name)) dest from V$PARAMETER where name like '%log_archive_dest%' and name not like '%log_archive_dest_state_%' and value != '0';

select ' '||Name||' '||Rpad (value,68-length(Name)) from V$PARAMETER where name in (select substr(name,1,17)||'state_'||substr(name,-1,1) from V$PARAMETER where name like '%log_archive_dest%' and name not like '%log_archive_dest_state_%' and value != '0');

select ' Obsolete Parameter : '||Name||' '||lpad (Name,31-length(Name)) from v$obsolete_parameter where isspecified='TRUE';

select ' Database log mode '||log_mode "Parameter" from V$DATABASE
--union
--select ' Archive destination '||value from V$PARAMETER where (name='log_archive_dest' or name = 'log_archive_dest_1') and value is not null
select ' Spfile '||value from V$PARAMETER where name='spfile' and value is not null
union
select ' Background Dump Dest '||value from V$PARAMETER where name='background_dump_dest' and value is not null
union
select ' Db_recovery_file_dest '||value from V$PARAMETER where name='db_recovery_file_dest' and value is not null
union
select ' Db_recovery_file_dest_size '||value from V$PARAMETER where name='db_recovery_file_dest_size' and value is not null and value !='0'
;

select ' Filesystemio_options '||Lpad(value,12) from V$PARAMETER where name='filesystemio_options' and value is not null
;

Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'Select force_logging from v$database';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
Dbms_OutPut.Put_Line('-- Force_Logging '||W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/

Select status_01||' | '||status_02 status
From
(Select ' Index No Log. Or N. '||Lpad(count(*),10) status_01 from dba_indexes where (logging='NO' or logging is null ) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Table No Log. Or N. '||Lpad(count(*),12) status_02 from dba_tables where iot_type is null and (logging='NO' or logging is null ) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB') and temporary ='N')
Union
Select status_01||' | '||status_02 status
From
(Select ' Index Part. No Log. '||Lpad(count(*),8) status_01 from dba_ind_partitions where logging='NO' and index_owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Index SubPart. No Log. '||Lpad(count(*),7) status_02 from dba_ind_subpartitions where logging='NO' and index_owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
Union
Select status_01||' | '||status_02 status
From
(Select ' Table Row Movement '||Lpad(count(*),11) status_01 from dba_tables where ROW_MOVEMENT='ENABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Table No Row Movement '||Lpad(count(*),10) status_02 from dba_tables where ROW_MOVEMENT='DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
;

Set Heading Off
Set Termout Off

Column Var_DB_LAST_ANALYZED_IND new_value Var_DB_LAST_ANALYZED_IND noprint

Select
to_char(max(last_analyzed), 'DD-MM-YYYY HH24:MI') Var_DB_LAST_ANALYZED_IND
from
dba_indexes
where
owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS')
;

Column Var_DB_LAST_ANALYZED_TAB new_value Var_DB_LAST_ANALYZED_TAB noprint

Select
to_char(max(last_analyzed), 'DD-MM-YYYY HH24:MI') Var_DB_LAST_ANALYZED_TAB
from
dba_tables
where
owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS')
;

Set Termout On

Select status_01||' | '||status_02 status
From
(Select ' Monitoring On '||Lpad(count(*),16) status_01 from Dba_Tables where monitoring = 'YES' and temporary = 'N' and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
, (Select ' Monitoring Off '||Lpad(count(*),17) status_02 from Dba_Tables where monitoring = 'NO' and temporary = 'N' and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Last Tab. An. '||Lpad('&Var_DB_LAST_ANALYZED_TAB.',16) status_01 from dual)
, (select ' Last Ind. An. '||lpad('&Var_DB_LAST_ANALYZED_IND.',18) Status_02 from dual)
;

Select status_01 ||' | '||status_02 status
From
(select ' Cascade '||lpad(replace(dbms_stats.get_param('cascade'),'DBMS_STATS.', ''),22) Status_01 from dual)
, (select ' Degree '||lpad(dbms_stats.get_param('degree'),23) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(select ' Estimate % '||lpad(replace(dbms_stats.get_param('estimate_percent'),'DBMS_STATS.', ''),19) Status_01 from dual)
, (select ' Opt. '||lpad(replace(dbms_stats.get_param('method_opt'),'DBMS_STATS.', ''),27) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(select ' No Invalid.'||lpad(replace(dbms_stats.get_param('no_invalidate'),'DBMS_STATS.', ''),19) Status_01 from dual)
, (select ' Granularity '||lpad(dbms_stats.get_param('granularity'),20) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Last Start Dt.'||Lpad(to_char(last_start_date, 'DD-MM-YYYY HH24:MI'), 16) status_01 from Dba_Scheduler_Jobs Where JOB_NAME = 'GATHER_STATS_JOB')
, (select ' AutoStats '||lpad(dbms_stats.get_param('autostats_target'),22) Status_02 from dual)
;

Prompt

Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_SGA Is
select ' SGA (Mb) '||Lpad(To_Char(Round(sum (value)/1024/1024)),8) status_02 from v$sga;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(40);
--
X Varchar2(100);
Nb_Tf Number(8);
SGA Varchar2(40);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_SGA;
Fetch Cur_SGA Into SGA;
Close Cur_SGA;
--
If X Is Not Null Then
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size';
--
Else
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
--

End If;
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 40);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- '||W_Temp||' | '||SGA||' --');
--
Dbms_Sql.Close_Cursor(Curs);
--
End;
/

Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_Df Is
Select Count(*)
From dba_data_files
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Df;
Fetch Cur_Df Into Nb_Df;
Close Cur_Df;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From dba_temp_files';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5)||' | Nb. Tempfiles '||Lpad(W_Temp,5)||' --' );
-- Session number 1 | Active session number 1
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5));
End If;
--
End;
/

column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(Select ' shared_pool_size (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),22-length(Name)) status_01 from V$PARAMETER where name='shared_pool_size')
, (Select ' Shared_pool_reserved_s. (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),28-length(Name)) status_02 from V$PARAMETER where name='shared_pool_reserved_size')
Union
Select status_01
From
(SELECT ' shared_pool Cons. (Mb) '|| Lpad(To_Char(round(SUM(BYTES)/1024/1024)),7) status_01 FROM V$SGASTAT WHERE POOL = 'shared pool')
;

Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(200);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'select ''-- ''|| component ||'' (Mb)/ Current ''||Lpad(To_char(Round(current_size/1024/1024) ),15-length(component))||'' | User Specified ''||Lpad(To_char(Round( user_specified_size/1024/1024) ),28-length(component)) from v$sga_dynamic_components where component=''shared pool''';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 200);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line(W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/

select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%pga_max_size%' and value != '0'
union
select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%a_target%' and value != '0' and Upper(name)!='PGA_AGGREGATE_TARGET'
union
select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%e_target%' and value != '0'
union
select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),24-length(Name)) from V$PARAMETER where name like '%cache_size%' and value != '0' and name != 'db_cache_size'
;

--union
--select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),33-length(Name)) from V$PARAMETER where name like '%shared%size' and value != '0'

--select ' '||Name||' (Kb) '||Lpad(To_char(Round(to_number(value)/1024)),33-length(Name)) from V$PARAMETER where name like '%area_%size' and value != '0'
--;

Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'select Lpad(to_char(round(bytes/1024/1024)),30-length(Name)) from v$sgainfo where name =''Free SGA Memory Available''';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
Dbms_OutPut.Put_Line('-- Free SGA Memory Av. (Mb) '||W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/

column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(Select ' Session number (client) '||Lpad(To_Char(count(*)),5) status_01 from v$session where SERIAL#!=1 and osuser is not null)
, (Select ' Undo Block Used '||Lpad(To_Char(Nvl(Sum(A.USED_UBLK),0)),7) status_02 from v$transaction A, v$session B, v$process C where C.PID = B.SID and A.ADDR = B.TADDR)
Union
Select status_01||' | '||status_02 status
From
(Select ' Active session number '||Lpad(To_Char(count(*)),3) status_01 from v$session where SERIAL#!=1 and osuser is not null and status='ACTIVE')
, (Select ' Killed session number '||Lpad(To_Char(count(*)),5) status_02 from v$session where SERIAL#!=1 and osuser is not null and status='KILLED')
Union
Select status_01||' | '||status_02 status
From
(Select ' Param. Processes '||Lpad(value,11) status_01 from V$PARAMETER where name='processes')
, (Select ' Param. Sessions '||Lpad(value,13) status_02 from V$PARAMETER where name='sessions')
;

Select status_01||' | '||status_02 status
From
(select ' Data Buffer Hit ratio '||Lpad(Substr(To_Char((1-a.value/(b.value+c.value))*100),1,4),5) status_01 From v$sysstat a, v$sysstat b , v$sysstat c where a.name = 'physical reads' and b.name = 'db block gets' and c.name = 'consistent gets')
, (select ' Library Buffer Hit ratio '||Lpad(Substr(To_Char(sum(pins) / (sum(pins) + sum(reloads)) *100),1,4),5) status_02 from v$librarycache)
Union
Select status_01||' | '||status_02 status
From
(Select ' Dict. Buffer Hit ratio '||Lpad(Substr(To_Char((1-(sum(getmisses)/sum(gets)))*100),1,4),7) status_01 from v$rowcache)
, (Select ' Switch number (last hour) '||Lpad(To_char(count(*)),4) status_02 from sys.v_$log_history where first_time > sysdate - 1/24)
;

Select status_01||' | '||status_02 status
From
(Select ' Sort_Area_Size (Kb) '||Lpad(value/1024,10) status_01 from V$PARAMETER where name='sort_area_size')
, (Select ' Db_File_Multi_Block_RC. '||Lpad(value,8) status_02 from V$PARAMETER where name='db_file_multiblock_read_count')
Union
Select status_01||' | '||status_02 status
From
(Select ' Optimizer Mode '||Lpad(value,15) status_01 from V$PARAMETER where name='optimizer_mode')
, (Select ' Optimizer_Index_Cost_Adj '||Lpad(value,7) status_02 from V$PARAMETER where name='optimizer_index_cost_adj')
Union
Select status_01||' | '||status_02 status
From
(Select ' Hash_Join_Enabled '||Lpad(value,12) status_01 from V$PARAMETER where Upper(name)='HASH_JOIN_ENABLED')
, (Select ' Optimizer_Index_Caching '||Lpad(value,8) status_02 from V$PARAMETER where Upper(name)='OPTIMIZER_INDEX_CACHING')
Union
Select status_01||' | '||status_02 status
From
(Select ' Db_Files '||Lpad(value,21) status_01 from V$PARAMETER where Upper(name)='DB_FILES')
, (Select ' Db_Writer_Processes '||Lpad(value,12) status_02 from V$PARAMETER where Upper(name)='DB_WRITER_PROCESSES')
;

-- ----------------------------------------------------------------------- ---
-- Redo Size ---
-- ----------------------------------------------------------------------- ---

Set Termout Off

create or replace function show_redo_size return varchar2
Is
--
Cursor CX Is
Select distinct To_Char(round(bytes/1024/1024))
from v$log ;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := W_Texte||' ';
W_Texte := Replace(W_Texte,' ', '/');
W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
If Instr(W_Texte, '/', 1,2) != 0 then
W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
End If;
--
Return W_Texte;
--
End;
/

-- show error

Set Termout On

Select status_01||' | '||status_02 status
From
(select distinct ' Redo size (Mb)'|| Lpad(show_redo_size ,'18') Status_02 from v$log)
, (Select ' Nb Redo Gpr.'|| Lpad(To_Char(count(distinct lf.group#)),'18') status_01 from v$logfile lf)
;

Set Termout Off

Drop function show_redo_size;

Set Termout On

-- ----------------------------------------------------------------------- ---
-- File Status ---
-- ----------------------------------------------------------------------- ---

--column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' V$Datafile Status '||Lpad('OK',12)
, 'SYSTEM', ' V$Datafile Status '||Lpad('OK',12)
, ' V$Datafile Status '||Lpad('KO !!!',12)
) STATUS_01 from v$datafile)
, (select distinct
decode (status
, 'ONLINE', ' V$Tempfile Status '||Lpad('OK',14)
, 'SYSTEM', ' V$Tempfile Status '||Lpad('OK',14)
, ' V$Tempfile Status '||Lpad('KO !!!',14)
) STATUS_02 from v$tempfile)
Union
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' Dba_Tablespaces Status '||Lpad('OK',7)
, 'READ ONLY', ' Dba_Tablespaces Status '||Lpad('OK',7)
, ' Dba_Tablespaces Status '||Lpad('KO !!!',7)
) STATUS_01 from dba_tablespaces)
, (select distinct
decode (status
, 'CURRENT', ' V$Log Status '||Lpad('OK',19)
, 'ACTIVE', ' V$Log Status '||Lpad('OK',19)
, 'INACTIVE', ' V$Log Status '||Lpad('OK',19)
, ' V$Log Status '||Lpad('KO !!!',19)
) STATUS_02 from v$log)
union
Select status_01||' | '||status_02 status
From
(select
distinct
decode (count(1)
, 0, ' V$Recover_File '||Lpad('OK',15)
, ' V$Recover_File '||Lpad('KO !!!',15)
) STATUS_01 from v$recover_file)
, (select
distinct
decode (count(1)
, 0, ' V$Recovery_Log '||Lpad('OK',17)
, ' V$Recovery_Log '||Lpad('KO !!!',17)
) STATUS_02 from v$recovery_log)
;

-- ----------------------------------------------------------------------- ---
-- Tablespaces Backup Mode / Lock Number / Tablespace > 90% ---
-- ----------------------------------------------------------------------- ---
Select status_01||' | '||status_02 status
From
(Select ' Tablespace in Backup Mode '||Lpad(To_Char(Count(*)),4) status_01 from v$backup where status != 'NOT ACTIVE')
, (select ' Tablespace > 90% '||Lpad(To_Char(count(*)),4) status_02 from ( select sum (bytes)/1048576 free, max (bytes)/1048576 fragmax, tablespace_name from sys.dba_free_space group by tablespace_name ) fsp, ( select sum(bytes)/1048576 alloc, tablespace_name from sys.dba_data_files group by tablespace_name) df, dba_tablespaces dt where fsp.tablespace_name (+) = df.tablespace_name and df.tablespace_name = dt.tablespace_name and dt.status = 'ONLINE' and (((alloc - nvl (free, 0)) / alloc) * 100 > 90))
Union
Select status_01||' | '||status_02 status
From
(select ' Lock number '||Lpad(To_Char(count(*)),8) status_01 from v$session s, v$process p, v$lock l, dba_objects o where s.paddr = p.addr And l.sid=s.sid And l.id1 = o.object_id And s.username is NOT NULL And l.ctime > 60)
, (select ' Indexes unusable '||Lpad(To_Char(count(*)),8) status_02 from dba_indexes where status = 'UNUSABLE')
Union
Select status_01||' | '||status_02 status
From
(select ' Indexes SubPart. unusable '||Lpad(To_Char(count(*)),6) status_02 from dba_ind_subpartitions where status = 'UNUSABLE')
, (select ' Indexes Part. unusable '||Lpad(To_Char(count(*)),7) status_01 from dba_ind_partitions where status = 'UNUSABLE')
Union
Select status_01||' | '||status_02 status
From
(Select ' Rman Corrupt. Blck. ' || Lpad(To_Char(count(*)),10) status_01 from v$backup_corruption)
, (Select ' Rman Crpt. Blck. Last Bck. ' || Lpad(To_Char(count(*)),5) status_02 from V$DATABASE_BLOCK_CORRUPTION)
;

-- ----------------------------------------------------------------------- ---
-- Jobs Broken / Objects Invalid / Shared_Pool Failure / Latch > 1% ---
-- ----------------------------------------------------------------------- ---
--Select status_01 ||' | '||status_02 status
-- From
-- (select ' Background Proc. Error '||Lpad(To_Char(Count(*)),6) status_01 from v$bgprocess where paddr <> '00' and error > 0)
-- , (select ' Archive Dest. Error '||Lpad(To_Char(Count(*)),8) status_02 From v$archive_dest where status not in ('VALID', 'INACTIVE'))
--;

Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='V$ARCHIVE_DEST'
;
--
Cursor Cur_Bg Is
Select Count(*)
From v$bgprocess
where paddr <> '00'
and error > 0
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Bg Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Bg;
Fetch Cur_Bg Into Nb_Bg;
Close Cur_Bg;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From v$archive_dest where status not in (''VALID'', ''INACTIVE'')';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- Background Proc. Error '||Lpad(To_Char(Nb_Bg),6)||' | Archive Dest. Error '||Lpad(W_Temp,8)||' --' );
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Background Proc. Error '||Lpad(To_Char(Nb_Bg),6));
End If;
--
End;
/

Select status_01||' | '||status_02 status
From
(select ' Jobs Broken '||Lpad(To_Char(Count(*)),8) status_01 From dba_jobs Where broken != 'N')
, (select ' Objects Invalid '||Lpad(To_Char(Count(*)),8) status_02 From dba_objects where status != 'VALID')
Union
Select status_01||' | '||status_02 status
From
(select ' Shared_Pool Failure '||Lpad(To_Char(Count(*)),8) status_01 From v$shared_pool_reserved where request_failures != 0)
, (select ' Latch > 1% '||Lpad(To_Char(Count(*)),8) status_02 From v$latch where (misses/decode(gets,0,1,gets))*100 >= 1 or (immediate_misses/decode(immediate_misses+immediate_gets,0,1,immediate_misses+immediate_gets)*100) >= 1)
;

-- ----------------------------------------------------------------------- ---
-- Trigger Disabled / Constraint Disabled ---
-- ----------------------------------------------------------------------- ---

column status format a120 wrap heading "Status"

Select status_01||' | '||status_02 status
From
(select ' Trigger Disabled '||Lpad(To_Char(Count(*)),8) status_01 From dba_triggers where status != 'ENABLED')
, (select ' Constraint Disabled '||Lpad(To_Char(Count(*)),8) status_02 From dba_constraints where status != 'ENABLED')
Union
Select status_01||' | '||status_02 status
From
(select ' Trigger Enabled '||Lpad(To_Char(Count(*)),9) status_01 From dba_triggers where status = 'ENABLED')
, (select ' Constraint Enabled '||Lpad(To_Char(Count(*)),9) status_02 From dba_constraints where status = 'ENABLED')
;

Select status_01||' | '||status_02 status
From
(Select ' Schedule Ok '||Lpad(count(*),16) status_01 from Dba_Scheduler_Jobs where state <> 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
, (Select ' Schedule Disabled '||Lpad(count(*),13) status_02 from Dba_Scheduler_Jobs where state = 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
Union
Select status_01||' | '||status_02 status
From
(Select ' Alert Last Hour '||Lpad(count(*),14) status_01 from dba_outstanding_alerts where Creation_Time > sysdate -1/24)
, (Select ' Advisor Last Hour '||Lpad(count(*),14) status_02 from dba_advisor_tasks where execution_end > sysdate -1/24)
Union
Select status_01 status
From
(Select ' Sheduler enable '||Lpad(decode(count(value), 0, 'OK', 1, 'KO'),14) status_01 from Dba_Scheduler_Global_Attribute Where Attribute_name = 'SCHEDULER_DISABLED')
;

--column status format a46 wrap heading "Status"

--prompt

--prompt

--prompt -- Installed options :

--prompt -- ----------------------------------------------------------------------- ---

--select
-- ' - ' || parameter || ' option'
-- from
-- sys.v_$option
-- where
-- value = 'TRUE'
--;

set head on

Prompt

-- ----------------------------------------------------------------------- ---
-- V$Recovery_Log Status ---
-- ----------------------------------------------------------------------- ---

column Thread# format 9999999 heading "Thread"
column Sequence# format 9999999 heading "Sequence"
column archive_name format A60 wrap heading "Archive|Name"
column Time heading "Time"

select
*
from
v$recovery_log
order
by thread#
;

-- ----------------------------------------------------------------------- ---
-- V$Recover_File Status ---
-- ----------------------------------------------------------------------- ---

column file# format 9999999 heading "File#"
column online format A10 heading "Online"
column online_status format A10 heading "Online|Status"
column error format A20 wrap heading "Error"
column change format 99999 heading "Change"
column Time heading "Time"

select
*
from
v$recover_file
order
by file#
;

-- ----------------------------------------------------------------------- ---
-- V$Log Status ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$log
where
status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Log Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on redogroup -
skip 1

column redogroup format 99999 heading "Group"
column redothread format 99999 heading "Thread"
column redosequence format 999999 heading "Sequence"
column file_name format A55 heading "RedoLog Name"
column KB format 99999999B heading "Size|(Kb)"
column status format A5 heading "Stat." Trunc

select
lf.group# redogroup
, l.thread# redothread
, l.sequence# redosequence
, lf.member file_name
, l.bytes/1024 Kb
, l.status status
from
v$logfile lf
, v$log l
where
l.group# = lf.group#
and
l.status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
order
by lf.group#
, l.thread#
, l.sequence#
, lf.member
;

clear breaks

-- ----------------------------------------------------------------------- ---
-- V$Tempfile Status ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$tempfile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Tempfile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on status skip 1

column name format a60 wrap heading "DataFile Name"

select
name
, status
from
V$tempfile
where
status not in ('ONLINE', 'SYSTEM')
Order
By Status
, Name
;

clear breaks

-- ----------------------------------------------------------------------- ---
-- V$Datafile Status ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$datafile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Datafile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

clear breaks
break on status skip 1

column name format a60 wrap heading "DataFile Name"
column fid format 999999 wrap heading "DataFile Id"

select
name
, FILE# fid
, status
from V$datafile
where status not in ('ONLINE', 'SYSTEM')
Order By Status, Name
;

clear breaks

-- ----------------------------------------------------------------------- ---
-- Dba_Tablespaces Status ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
dba_tablespaces
where
status not in ('ONLINE', 'READ ONLY')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Dba_Tablespaces Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column tblsp format a20 wrap heading "Tablespace Name"
column contents format a10 heading "Content"

clear breaks
break on contents -
skip 1

select
contents
, tablespace_name tblsp
, status
from
dba_tablespaces
where
status not in ('ONLINE', 'READ ONLY')
order
by contents
;

-- ----------------------------------------------------------------------- ---
-- Tablespaces Backup Mode ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select 'X'
from v$backup bck , dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database);
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Tablespaces in Backup Mode ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column tablespace_name format a18 wrap heading "Tablespace Name"
column file_name format a50 wrap heading "DataFile Name"
column status format a12 wrap heading "Status"
column change# format 999999999999 heading "Change"
column time format a18 wrap heading "Time"

select
df.tablespace_name
, df.file_name
, bck.status
, bck.change#
, To_Char(bck.time,'DD-MM-YYYY HH24:MI') time
from
v$backup bck
, dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database)
order by df.tablespace_name, df.file_name;

-- ----------------------------------------------------------------------- ---
-- Background process in error ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select 'X'
from v$bgprocess
where paddr <> '00'
and error > 0
order by paddr
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Background process in error ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column name format a6
column description format a30
column error format 99999999999

select
paddr
, name
, description
, error
from v$bgprocess
where paddr <> '00'
and error > 0
order by paddr
;

-- ----------------------------------------------------------------------- ---
-- Archive Destination in error ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$archive_dest
where
status not in ('VALID', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Archive Destination in error ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column dest_id format 99999 heading "Dest.|Id"
column dest_name format a19 heading "Dest.|Name"
column error format a30 wrap heading "Error"
column status format a9 heading "Status"
column binding format a10 heading "Binding"
column target format a10 heading "Target"
column name_space format a10 heading "Name|Space"

select
dest_id
-- , dest_name
, status
, binding
, name_space
, target
, error
from v$archive_dest
where status Not in ('VALID', 'INACTIVE')
;

-- ----------------------------------------------------------------------- ---
-- Lock list ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Lock list ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

column username heading "Utilis." format A11
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column state heading "Etat" format A15
column Lmode_H heading "Lock mode" format A15
column terminal heading "Terminal" format A14
column command heading "C" format 99
Column serial# heading "Serial#" format 99999
column ctime heading "Duration" format 999999
column logon heading "Date Connexion" format A16
column object_name heading "Object Name" format A25

select
s.sid
, s.serial#
, p.spid
, substr(s.username,1,15) username
, s.terminal
, l.type
, to_char(s.logon_time,'DD-MM-YYYY HH24:MI') logon
, decode(l.lmode, 1,'null' , 2,'Row share'
, 3,'Row Exclusive' , 4,'Share'
, 5,'Share Row Excl.' , 6,'Exclusive') Lmode_H
, o.object_name
, Round(ctime/60) ctime
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;

-- ----------------------------------------------------------------------- ---
-- Rman Block Corruption ---
-- ----------------------------------------------------------------------- ---

Declare
--
Cursor Cur_Req Is
select
'X'
from
v$DATABASE_BLOCK_CORRUPTION
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Rman Block Corruption ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/

select * from V$DATABASE_BLOCK_CORRUPTION;

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Tablespaces ---

prompt -- ----------------------------------------------------------------------- ---

Set Heading Off
Set Termout Off

create or replace function show_autoext_tbs(P_Tablespace_Name In Varchar2) return varchar2
Is
--
Cursor CX Is
Select distinct Substr(autoextensible,1,1)
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct Substr(autoextensible,1,1)
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := Replace(Substr(W_Texte, 2, 4), ' ', '/');
--
Return W_Texte;
--
End;
/

-- show error

create or replace function show_increment_tbs(P_Tablespace_Name In Varchar2, P_Db_Block_Size In Number) return varchar2
Is
--
Cursor CX Is
Select distinct To_Char(round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct To_Char(Round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := W_Texte||' ';
W_Texte := Replace(W_Texte,' ', '/');
W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
If Instr(W_Texte, '/', 1,2) != 0 then
W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
End If;
--
Return W_Texte;
--
End;
/

-- show error

Set Termout On
Set Heading On

clear breaks
break on contents -
skip 1
compute Sum of alloc used maxbytes nfree nbfrag on contents

column tblsp format a22 wrap heading "Tablespace|Name"
column Alloc format 99999999 heading "Alloc|(Mb)"
column file_name format a45 wrap heading "DataFile Name"
column rfno format 999 heading "R.|Fno"
-- column inc format 9999999 heading "Incr|(Mb)"

column inc format a15 heading "Incr|(Mb)"
column maxbytes format 9999999 heading "Sum Max|(Gb)"
column user_bytes format 999999 heading "Userbytes|(Mb)"
column free format 999999 heading "Free|(Mb)"
column nfree format 99999 heading "Max|Free|(Mb)"
column used format 99999999 heading "Used|(Mb)"
column pused format 990.9 heading "%Used"
column maxpused format 990.9 heading "Max|%Used"
column contents format a5 heading "Cont."
column fragmax format 99999 heading "Larg.|Free|Ext|(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column nbfile format 99 heading "Nb|F."
column autoext format a3 wrap heading "Auto|Ext."
column ftype format a2 wrap heading "F.|T."
column ext_manage format a5 wrap heading "Ext.|M."
column seg_sp_mg format a2 wrap heading "S.|M."
column seg_space_mg format a7 wrap heading "Space|Mg."

set linesize 145

Select
dt.contents contents
, dt.Tablespace_Name tblsp
, df.alloc alloc
, df.alloc - nvl(fs.free,0) used
, ((df.alloc - nvl(fs.free,0))* 100)/df.alloc pused
, dt.autoext autoext
, df.summaxbytes/1024 maxbytes
, dt.Inc inc
-- , nvl(fs.free,0) free
-- , fs.nbfrag
, Nvl(fs.fragmax,0) fragmax
-- , dt.ext_manage
-- , dt.seg_sp_mg seg_sp_mg
, Decode(dt.ext_manage||' '||dt.seg_sp_mg,'LOC MA', ' LMTS', 'LOC AU', ' ASSM',dt.ext_manage||' '||dt.seg_sp_mg) seg_space_mg
, df.nbf nbfile
, df.ftype ftype
-- , decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0)) nfree
, 100 - ((decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0))) * 100 / decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))) maxpused
from
( select
Substr(contents,1,4) contents
, tablespace_name
, Substr(extent_management,1,3) ext_manage
, Substr(segment_space_management,1,2) seg_sp_mg
, show_autoext_tbs(tablespace_name) autoext
, show_increment_tbs (tablespace_name, BLOCK_SIZE) inc
from dba_tablespaces
) dt
, ( select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'D' ftype
from Dba_Data_Files
Group
By tablespace_name
Union
select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'T' ftype
from Dba_Temp_Files
Group
By tablespace_name
) df
, (select
Nvl(Sum(nvl(bytes/1024/1024,0)),0) free
, Nvl(max (bytes)/1048576,0) fragmax
, tablespace_name
, count(*) nbfrag
from dba_free_space --(Select distinct tablespace_name, bytes from dba_free_space)
Group
By tablespace_name
order by max (bytes)/1048576 desc
) fs
Where
df.tablespace_name = fs.tablespace_name (+)
and
df.tablespace_name = dt.tablespace_name
Group
By dt.contents
, dt.Tablespace_Name
, df.alloc
, df.maxbytes
, df.summaxbytes
, df.nbf
, dt.Inc
-- , nvl(fs.free,0)
, df.alloc - nvl(fs.free,0)
-- , fs.nbfrag
, fs.fragmax
, dt.autoext
, dt.ext_manage
, df.ftype
, dt.seg_sp_mg
Order
By contents
, maxpused desc
, dt.inc
;

Prompt

Set Heading Off
Set Termout Off

Drop function show_autoext_tbs;
Drop function show_increment_tbs;

Set Heading On
Set Termout On

set heading on

-- Spool off

spool off

-- exit

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

Server Detail:- apt-amd-02.aptus.co.uk:192.168.2.251 Inst Name and Id->orcl=1, Db Name= orcl OS:- x86_64/Linux 2.4.xx Uptime:-7days18hours33minutes27seconds

cluster_database FALSE
cluster_database_instances 1
db_recovery_file_dest /opt/app/oracle/flash_recovery_area

FlashBack NO

** 22-Oct-2011:06:06 ** DB : ORCL ** Inst. : orcl **

Id Unix Utilis. Terminal Programme
----- ------- ---------- ---------------- --------------------------------
40 19570 SYS pts/22 oracle@apt-amd-02 (TNS V1-V3)

-- ----------------------------------------------------------------------- --
-- Instance Information --
-- ----------------------------------------------------------------------- --

Cpu_Count 1 | Host_Name apt-amd-02
Database_Name ORCL | Database_Open_Mode READ WRITE
Instance_Logins ALLOWED | Startup_Time 14-10-2011 11:33
Instance_Status ACTIVE | Instance_Name orcl

Checkpoint_Chg# 18077481 | Current_Scn 18077491
DBID 1267852645 | Flashback NO
Version 11.2.0.1.0 | Instance_Role PRIMARY_INSTANCE

Db_Cache_Advice ON | Statistics_Level TYPICAL
Dynamic SGA Not Installed | sga_target (Mb) 0
sga_max_size (Mb) 804 | Free Memory (Mb) 280

Automatic PGA Not Installed | Pga consumption (Mb) 200
pga_aggregate_target (Mb) 0 | Workarea_size_Policy AUTO

Cursor_Sharing EXACT | Remote_login_p. EXCLUSIVE
Default Temp TEMP | Undo tablespace UNDOTBS1

Background Dump Dest /opt/app/oracle/diag/rdbms/orcl/orcl/trace
Database log mode ARCHIVELOG
Db_recovery_file_dest /opt/app/oracle/flash_recovery_area
Db_recovery_file_dest_size 4070572032
Spfile /opt/app/oracle/product/11.2/dbs/spfileorcl.ora

Filesystemio_options none
-- Force_Logging NO

Index No Log. Or N. 85 | Table No Log. Or N. 36
Index Part. No Log. 196 | Index SubPart. No Log. 0
Table Row Movement 0 | Table No Row Movement 1059

Last Tab. An. 16-10-2011 06:01 | Last Ind. An. 16-10-2011 06:01
Monitoring On 1030 | Monitoring Off 0

Cascade AUTO_CASCADE | Degree NULL
Estimate % AUTO_SAMPLE_SIZE | Opt. FOR ALL COLUMNS SIZE AUTO
No Invalid. AUTO_INVALIDATE | Granularity AUTO

-- Database Space (Mb) 5705 | SGA (Mb) 800 --
-- Nb. Datafiles 12 | Nb. Tempfiles 1 --

shared_pool Cons. (Mb) 308
shared_pool_size (Mb) 0 | Shared_pool_reserved_s. (Mb) 16
-- shared pool (Mb)/ Current 308 | User Specified 0
-- Free SGA Memory Av. (Mb) 280

Active session number 8 | Killed session number 0
Param. Processes 150 | Param. Sessions 247
Session number (client) 9 | Undo Block Used 0

Data Buffer Hit ratio 98.1 | Library Buffer Hit ratio 99.3
Dict. Buffer Hit ratio 99.5 | Switch number (last hour) 0

Db_Files 200 | Db_Writer_Processes 1
Optimizer Mode ALL_ROWS | Optimizer_Index_Cost_Adj 100
Sort_Area_Size (Kb) 64 | Db_File_Multi_Block_RC. 95

Nb Redo Gpr. 3 | Redo size (Mb) 520

Dba_Tablespaces Status OK | V$Log Status OK
V$Datafile Status OK | V$Tempfile Status OK
V$Recover_File OK | V$Recovery_Log OK

Indexes Part. unusable 0 | Indexes SubPart. unusable 0
Lock number 3 | Indexes unusable 0
Rman Corrupt. Blck. 0 | Rman Crpt. Blck. Last Bck. 0
Tablespace in Backup Mode 0 | Tablespace > 90% 1
-- Background Proc. Error 0 | Archive Dest. Error 0 --

Jobs Broken 0 | Objects Invalid 3
Shared_Pool Failure 1 | Latch > 1% 1

Trigger Disabled 12 | Constraint Disabled 37
Trigger Enabled 951 | Constraint Enabled 11867

Alert Last Hour 0 | Advisor Last Hour 2
Schedule Ok 4 | Schedule Disabled 0
Sheduler enable OK

-- ---
-- ----------------------------------------------------------------------- ---
-- Lock list ---
-- ----------------------------------------------------------------------- ---

Id Serial# Unix Utilis. Terminal TY Date Connexion Lock mode Object Name Duration
----- ------- ------- ----------- -------------- -- ---------------- --------------- ------------------------- --------
35 3865 19220 SYS UNKNOWN AE 22-10-2011 05:59 Share ORA$BASE 7
40 956 19570 SYS pts/22 AE 22-10-2011 06:04 Share ORA$BASE 2
1 1831 10536 SYS pts/6 AE 19-10-2011 10:15 Share ORA$BASE 4071

-- ----------------------------------------------------------------------- --
-- Tablespaces --
-- ----------------------------------------------------------------------- --

Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
PERM DROP1 2 1 50.0 N 0 0 1 ASSM 1 D 50.0
TSAPEXU 110 98 88.6 Y 0 10 13 ASSM 1 D 32.5
RMAN 50 7 13.9 N 0 0 43 ASSM 1 D 13.9
PERFSTAT 1000 115 11.5 N 0 0 885 ASSM 1 D 11.5
TEST 20 1 5.0 N 0 0 19 ASSM 1 D 5.0
SYSTEM 830 813 98.0 Y 32 10 8 LMTS 1 D 2.5
SYSAUX 830 715 86.1 Y 32 10 53 ASSM 1 D 2.2
USERS 458 411 89.7 Y 32 1.3 47 ASSM 1 D 1.3
TSAPEXF 50 1 2.0 Y 0 10 49 ASSM 1 D 0.3
EXAMPLE 100 79 78.8 Y 32 .6 19 ASSM 1 D 0.2
ANUJTEST 10 8 80.0 Y 32 5 2 ASSM 1 D 0.0
***** --------- --------- --------
sum 3460 2248 161

TEMP TEMP 326 326 100.0 Y 32 .6 0 LMTS 1 T 1.0
***** --------- --------- --------
sum 326 326 32

UNDO UNDOTBS1 325 28 8.7 Y 32 5 283 LMTS 1 D 0.1
***** --------- --------- --------
sum 325 28 32

set pagesize 200 linesize 200
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC

SQL> r
  1  SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
  2  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
  3  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
  4  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
  5  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
  6  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
  7  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
  8  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
  9  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
 10  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
 11  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
 12  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
 13  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
 14  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
 15  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
 16  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
 17  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
 18  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
 19  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
 20  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
 21  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
 22  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
 23  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
 24  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
 25  TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
 26  FROM V$LOG_HISTORY
 27  GROUP BY TRUNC(FIRST_TIME)
 28* ORDER BY TRUNC(FIRST_TIME) DESC

DG Dat 12AM  01AM  02AM  03AM  04AM  05AM  06AM  07AM  08AM  09AM  10AM  11AM  12PM  1PM   2PM   3PM   4PM   5PM   6PM   7PM   8PM   9PM   10PM  11PM
------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Jun 02     0     0     0     0     0     2     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     0     0     0
Jun 01     0     0     0     0     0     0     0     0     0     0     0     0     0     0     5     7     2     0     0     0     0     0     0     0
May 26     0     0     0     0     0     0     0     0     0     7     3     5     0     0     0     0     0     0     0     0     0     0     0     0
May 25     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     1     2     1     0     0
Apr 16     0     0     0     0     0     0     8     0     0     0     0     0     0     0     0     0     0     0     0     2     8     1     0     0
Apr 14     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     2     2     1     0     5     0     0     0
Apr 13     0     0     0     0     0     0     0     0     0     0     0     0     3     0     4     9     0     0     6     7     1     0     0     0
Apr 25     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     2     0     1     0     0     0     0
Apr 22     0     0     0     0     0     0     0     0     2     0     0     2     0     0     0     0     1     0     0     0     0     0     0     0
Apr 21     0     0     0     0     0     0     0     0     1     0     0     0     0     0     0     2     3     4     2     0     0     0     0     0
Apr 14     0     0     0     0     0     0     0     0     0     0     0     1     2     0     0     0     0     0     0     0     0     0     0     0
Apr 10     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     2     0     0

12 rows selected.

archive log status and error

set serveroutput on

DECLARE
CURSOR c is
select s.db_unique_name,
s.database_mode,
s.dest_id id, 
s.status stats,
s.recovery_mode,
s.protection_mode, 
s.standby_logfile_count,
s.standby_logfile_active,
s.archived_thread#, 
s.archived_seq#,
s.applied_thread#,
s.applied_seq#,
d.status, 
d.destination, 
d.archiver,
d.transmit_mode, 
d.affirm, 
d.async_blocks,
d.net_timeout, 
d.delay_mins, 
d.reopen_secs,
d.register,
d.binding, 
d.compression, 
d.error, 
to_char(d.FAIL_DATE,'dd-mm-yyyy hh24:mi:ss') FAIL_DATE
from v$archive_dest_status s, v$archive_dest d
where d.dest_id=s.dest_id
and s.db_unique_name != 'NONE'
and d.destination is not null;
BEGIN
dbms_output.put_line('-----------------------------------------------------');

FOR r IN c LOOP
DBMS_OUTPUT.PUT_LINE('Host Name          : ' || UTL_INADDR.GET_HOST_NAME);  
DBMS_OUTPUT.PUT_LINE('Ip Address         : ' || UTL_INADDR.GET_HOST_ADDRESS);  
dbms_output.put_line('Error date         : ' ||r.FAIL_DATE );
dbms_output.put_line('Dest ID            : ' ||r.id );
dbms_output.put_line('Status             : ' ||r.stats);
dbms_output.put_line('DB Name            : ' ||r.db_unique_name );
dbms_output.put_line('DB Mode            : ' ||r.database_mode);
dbms_output.put_line('Recovery Mode      : ' ||r.recovery_mode);
dbms_output.put_line('Protection Mode    : ' ||r.protection_mode);
dbms_output.put_line('SRL Count          : ' ||r.standby_logfile_count );
dbms_output.put_line('SRLActive          : ' ||r.standby_logfile_active);
dbms_output.put_line('Archived Thread#   : ' ||r.archived_thread# );
dbms_output.put_line('ArchivedSeq#       : ' ||r.archived_seq#);
dbms_output.put_line('Applied Thread#    : ' ||r.applied_thread# );
dbms_output.put_line('Destination        : ' ||r.destination);
dbms_output.put_line('Archiver           : ' ||r.archiver);
dbms_output.put_line('Transmit Mode      : ' ||r.transmit_mode);
dbms_output.put_line('Affirm             : ' ||r.affirm);
dbms_output.put_line('Asynchronous Blocks: ' ||r.async_blocks);
dbms_output.put_line('Net Timeout        : ' ||r.net_timeout);
dbms_output.put_line('Delay (Mins)       : ' ||r.delay_mins);
dbms_output.put_line('Reopen (Secs)      : ' ||r.reopen_secs);
dbms_output.put_line('Register           : ' ||r.register);
dbms_output.put_line('Binding            : ' ||r.binding);
dbms_output.put_line('Compression        : ' ||r.compression);
dbms_output.put_line('Error              : ' ||r.error);
dbms_output.put_line('----------------------------------------------------');
END LOOP;
END;
/




SQL> r
  1  DECLARE
  2  CURSOR c is
  3  select s.db_unique_name,
  4  s.database_mode,
  5  s.dest_id id,
  6  s.status stats,
  7  s.recovery_mode,
  8  s.protection_mode,
  9  s.standby_logfile_count,
 10  s.standby_logfile_active,
 11  s.archived_thread#,
 12  s.archived_seq#,
 13  s.applied_thread#,
 14  s.applied_seq#,
 15  d.status,
 16  d.destination,
 17  d.archiver,
 18  d.transmit_mode,
 19  d.affirm,
 20  d.async_blocks,
 21  d.net_timeout,
 22  d.delay_mins,
 23  d.reopen_secs,
 24  d.register,
 25  d.binding,
 26  d.compression,
 27  d.error,
 28  to_char(d.FAIL_DATE,'dd-mm-yyyy hh24:mi:ss') FAIL_DATE
 29  from v$archive_dest_status s, v$archive_dest d
 30  where d.dest_id=s.dest_id
 31  and s.db_unique_name !='NONE'
 32  and d.destination is not null;
 33  BEGIN
 34  dbms_output.put_line('-----------------------------------------------------');
 35
 36  FOR r IN c LOOP
 37  DBMS_OUTPUT.PUT_LINE('Host Name          : ' || UTL_INADDR.GET_HOST_NAME);
 38  DBMS_OUTPUT.PUT_LINE('Ip Address         : ' || UTL_INADDR.GET_HOST_ADDRESS);
 39  dbms_output.put_line('Error date         : ' ||r.FAIL_DATE );
 40  dbms_output.put_line('Dest ID            : ' ||r.id );
 41  dbms_output.put_line('Status             : ' ||r.stats);
 42  dbms_output.put_line('DB Name            : ' ||r.db_unique_name );
 43  dbms_output.put_line('DB Mode            : ' ||r.database_mode);
 44  dbms_output.put_line('Recovery Mode      : ' ||r.recovery_mode);
 45  dbms_output.put_line('Protection Mode    : ' ||r.protection_mode);
 46  dbms_output.put_line('SRL Count          : ' ||r.standby_logfile_count );
 47  dbms_output.put_line('SRLActive          : ' ||r.standby_logfile_active);
 48  dbms_output.put_line('Archived Thread#   : ' ||r.archived_thread# );
 49  dbms_output.put_line('ArchivedSeq#       : ' ||r.archived_seq#);
 50  dbms_output.put_line('Applied Thread#    : ' ||r.applied_thread# );
 51  dbms_output.put_line('Destination        : ' ||r.destination);
 52  dbms_output.put_line('Archiver           : ' ||r.archiver);
 53  dbms_output.put_line('Transmit Mode      : ' ||r.transmit_mode);
 54  dbms_output.put_line('Affirm             : ' ||r.affirm);
 55  dbms_output.put_line('Asynchronous Blocks: ' ||r.async_blocks);
 56  dbms_output.put_line('Net Timeout        : ' ||r.net_timeout);
 57  dbms_output.put_line('Delay (Mins)       : ' ||r.delay_mins);
 58  dbms_output.put_line('Reopen (Secs)      : ' ||r.reopen_secs);
 59  dbms_output.put_line('Register           : ' ||r.register);
 60  dbms_output.put_line('Binding            : ' ||r.binding);
 61  dbms_output.put_line('Compression        : ' ||r.compression);
 62  dbms_output.put_line('Error              : ' ||r.error);
 63  dbms_output.put_line('----------------------------------------------------');
 64  END LOOP;
 65* END;



-----------------------------------------------------
Host Name          : anuj.kumarsingh.co.uk
Ip Address         : 127.0.0.1
Error date         : 02-06-2013 06:49:58
Dest ID            : 2
Status             : ERROR
DB Name            : STANDBY
DB Mode            : UNKNOWN
Recovery Mode      : IDLE
Protection Mode    : MAXIMUM PERFORMANCE
SRL Count          : 0
SRLActive          : 0
Archived Thread#   : 0
ArchivedSeq#       : 0
Applied Thread#    : 0
Destination        : STDY
Archiver           : LGWR
Transmit Mode      : ASYNCHRONOUS
Affirm             : NO
Asynchronous Blocks: 16381
Net Timeout        : 180
Delay (Mins)       : 0
Reopen (Secs)      : 15
Register           : YES
Binding            : OPTIONAL
Compression        : DISABLE
Error              : ORA-12543: TNS:destination host unreachable
----------------------------------------------------




PROMPT *****************************************************************
PROMPT *  Archive Destinations
PROMPT *****************************************************************
set linesize 200 pagesize 200
COLUMN dest_id             HEADING "Dest|Id"                     FORMAT 99
COLUMN dest_name           HEADING "Dest Name"                   FORMAT a20
COLUMN db_unique_name      HEADING "UniqueName"                  FORMAT a15
COLUMN destination         HEADING "Destination"                 FORMAT a28
COLUMN archiver            HEADING "Arch|iver"                   FORMAT a4
COLUMN compression         HEADING "Compress"                    FORMAT a8
COLUMN transmit_mode       HEADING "Tranmit|Mode"                
COLUMN affirm              HEADING "AFFIRM"                      FORMAT a6

COLUMN reopen_secs         HEADING "Reopen|(sec)"                FORMAT 999999
COLUMN delay_mins          HEADING "Delay|(min)"                 FORMAT 99999
COLUMN max_connections     HEADING "Max|Conns"                   FORMAT 99999
COLUMN net_timeout         HEADING "Net|Time|Out"                FORMAT 9999
COLUMN alternate           HEADING "Alertnate"                   FORMAT a10
COLUMN dependency          HEADING "Dependency"                  FORMAT a10

COLUMN register            HEADING "Regi|ster"                   FORMAT a4
COLUMN log_sequence        HEADING "LogSeq"                      FORMAT 9999999

COLUMN async_blocks        HEADING "ASYNC|Blocks"                FORMAT 999999
COLUMN valid_now           HEADING "Valid|Now"                   FORMAT a7
COLUMN verify              HEADING "Verify"                      FORMAT a6

COLUMN fail_sequence       HEADING "FailSeq"                     FORMAT 9999999
COLUMN failure_count       HEADING "Fail|Count"                  FORMAT 99999
COLUMN max_failure         HEADING "Max|Fail"                    FORMAT 99999
COLUMN error               HEADING "Error"                       FORMAT a30

SELECT ad.dest_id
     , ad.dest_name
     , ad.db_unique_name
     , ad.destination
     , ad.status
     , ad.schedule
     , ad.target
     , ad.valid_type
     , ad.valid_role
     , ad.binding
     , ad.name_space
     , ad.compression
     , ad.archiver
     , ad.transmit_mode
     , ad.affirm
  FROM v$archive_dest ad
WHERE status <> 'INACTIVE'
;


Dest                                                                                                                                                               Arch Tranmit
  Id Dest Name            UniqueName      Destination                  STATUS    SCHEDULE TARGET           VALID_TYPE      VALID_ROLE   BINDING   NAME_SP Compress iver Mode         AFFIRM
---- -------------------- --------------- ---------------------------- --------- -------- ---------------- --------------- ------------ --------- ------- -------- ---- ------------ ------
   1 LOG_ARCHIVE_DEST_1   NONE            /u01/app/oracle/ArchiveLog   VALID     ACTIVE   PRIMARY          ALL_LOGFILES    ALL_ROLES    OPTIONAL  SYSTEM  DISABLE  ARCH SYNCHRONOUS  NO
   2 LOG_ARCHIVE_DEST_2   vihaan_stdy     stdy1                        DEFERRED  PENDING  STANDBY          ONLINE_LOGFILE  ALL_ROLES    OPTIONAL  SYSTEM  DISABLE  LGWR ASYNCHRONOUS NO

set lines 300 pagesize 300 numf 9999999999999
column destination format a25
column id 	format 999
column target 	format a7
column seq# 	format 999999999
column proc 	format a10
col transmit_mode for a20
select dest_id as id, status , target,archiver, destination, log_sequence as seq#, process ,register, transmit_mode, affirm,valid_type, valid_role, db_unique_name,applied_scn
from v$archive_dest 
where 1=1
and DB_UNIQUE_NAME!='NONE';





 select name,description from v$bgprocess where paddr<>'00' and description like 'Network%';

NAME  DESCRIPTION
----- ----------------------------------------------------------------
NSS2  Network Server SYNC

process that does the Redo Transport from Primary to Standby has changed from LNS to NSS (for synchronous Redo Transport):


SELECT ad.dest_id
     , ad.reopen_secs
     , ad.delay_mins
     , ad.max_connections
     , ad.net_timeout
     , ad.process
     , ad.register
     , ad.log_sequence
     , ad.alternate
     , ad.dependency
     , ad.async_blocks
     , ad.type
     , ad.valid_now
     , ad.verify
   --, ad.applied_scn
     , ad.fail_date
     , ad.fail_sequence
     , ad.failure_count
     , ad.max_failure
     , ad.error
  FROM v$archive_dest ad
WHERE status <> 'INACTIVE'
;

                             Net
Dest  Reopen  Delay    Max  Time            Regi                                  ASYNC         Valid                               Fail    Max
  Id   (sec)  (min)  Conns   Out PROCESS    ster   LogSeq Alertnate  Dependency  Blocks TYPE    Now     Verify FAIL_DATE  FailSeq  Count   Fail Error
---- ------- ------ ------ ----- ---------- ---- -------- ---------- ---------- ------- ------- ------- ------ --------- -------- ------ ------ ------------------------------
   1     300      0      1     0 ARCH       YES        40 NONE       NONE             0 PUBLIC  YES     NO                      0      0      0
   2     300      0      1    30 LGWR       YES         0 NONE       NONE         61440 PUBLIC  UNKNOWN NO                      0      0








set linesize 200 pagesize 200 


col dest_id             heading "Dest|Id"                     format 9999


col destination         heading "Destination"                 format a15


col dest_status         heading "Dest|Status"                 format a8


col type                heading "Type"                        format a10


col database_mode       heading "Database Mode"               format a15


col recovery_mode       heading "Recovery Mode"               format a23


col protection_mode     heading "Protection Mode"             format a20


col gap_status          heading "GAP|Status"                  format a6


col applied             heading "A|p|p|l|i|e|d"               format a1   truncate


col archived            heading "A|r|c|h|e|v|e|d"             format a1   truncate


col deleted             heading "D|e|l|e|t|e|d"               format a1   truncate


col archlog_status      heading "S|t|a|t|u|s"                 format a1   truncate


col max_sequence#       heading "Max Sequence#"               format 999999


col max_next_time       heading "Max Next Time"               format a18


col max_first_time      heading "Max First Time"              format a18


SELECT l.dest_id


     , s.destination


     , s.status   dest_status


     , s.type


     , s.database_mode


     , s.recovery_mode


     , s.protection_mode


     , s.gap_status


     , l.applied


     , l.archived


     , l.deleted


     , l.status    archlog_status


     , l.thread#


     , max(l.sequence#) max_sequence#


     , TO_CHAR(max(l.next_time),'DD-MON-YY hh24:MI:SS')  max_next_time


     , TO_CHAR(max(l.first_time),'DD-MON-YY hh24:MI:SS') max_first_time


     , ROUND((SUM(l.block_size * l.blocks)/1024/1024/1024),2)  redo_size_GB


 FROM v$archived_log l


    , v$archive_dest_status s


WHERE l.dest_id = s.dest_id     


 GROUP BY l.dest_id


        , s.destination


        , s.status


        , s.type


        , s.database_mode


        , s.recovery_mode


        , s.protection_mode


        , s.gap_status


        , l.applied


        , l.archived


        , l.deleted


        , l.status


       , l.thread#


 ORDER BY l.applied desc


        , l.dest_id ;   



===

set line 500 
col  dest_name 			format a20
col destination 		format a15
col gap_status 			format a10
col db_unique_name 		format a15
col error 			format a25
col applied_scn for 999999999999999
SELECT AL.THREAD#,
       ADS.DEST_ID,
       ADS.DEST_NAME,
       (SELECT ADS.TYPE || ' ' || AD.TARGET      FROM V$ARCHIVE_DEST AD  WHERE AD.DEST_ID = ADS.DEST_ID) TARGET,
       ADS.DATABASE_MODE,
       ADS.STATUS,
       ADS.RECOVERY_MODE,
       ADS.DB_UNIQUE_NAME,
       ADS.DESTINATION,
       ADS.GAP_STATUS,
       (SELECT MAX(SEQUENCE#) FROM V$LOG NA WHERE NA.THREAD# = AL.THREAD#) CURRENT_SEQ#,
       MAX(SEQUENCE#) LAST_ARCHIVED,
       MAX(CASE  WHEN AL.APPLIED = 'YES' AND ADS.TYPE <> 'LOCAL' THEN         AL.SEQUENCE#
           END) APPLIED_SEQ#,
       (SELECT AD.APPLIED_SCN      FROM V$ARCHIVE_DEST AD  WHERE AD.DEST_ID = ADS.DEST_ID) APPLIED_SCN,
		 ADS.ERROR
  FROM (SELECT *    FROM V$ARCHIVED_LOG V
         WHERE V.RESETLOGS_CHANGE# = (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)) AL,
       V$ARCHIVE_DEST_STATUS ADS
 WHERE AL.DEST_ID(+) = ADS.DEST_ID
   AND ADS.STATUS != 'INACTIVE'
 GROUP BY AL.THREAD#,
          ADS.DEST_ID,
          ADS.DEST_NAME,
          ADS.STATUS,
          ADS.ERROR,
          ADS.TYPE,
          ADS.DATABASE_MODE,
          ADS.RECOVERY_MODE,
          ADS.DB_UNIQUE_NAME,
          ADS.DESTINATION,
          ADS.GAP_STATUS
 ORDER BY ADS.DEST_ID,AL.THREAD#;



  


                                                                                                                A


                                                                                                              A r D


                                                                                                              p c e S


                                                                                                              p h l t


                                                                                                              l e e a


                                                                                                              i v t t


 Dest                 Dest                                                                             GAP    e e e u


   Id Destination     Status   Type       Database Mode   Recovery Mode           Protection Mode      Status d d d s    THREAD# Max Sequence# Max Next Time      Max First Time     REDO_SIZE_GB


----- --------------- -------- ---------- --------------- ----------------------- -------------------- ------ - - - - ---------- ------------- ------------------ ------------------ ------------


    2 orajndr         VALID    PHYSICAL   MOUNTED-STANDBY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE  NO GAP Y Y N A          1        265642 10-JUL-17 09:02:50 10-JUL-17 09:01:08      3179.73


    1 +ORLOGS         VALID    LOCAL      OPEN            IDLE                    MAXIMUM PERFORMANCE         N Y N A          1        265643 10-JUL-17 09:10:48 10-JUL-17 09:02:50        44.22




----- ===============










PROMPT *****************************************************************


PROMPT *  D A T A G U A R D    L A G


PROMPT *****************************************************************


COLUMN name                HEADING "Name"                        FORMAT a23


COLUMN value               HEADING "Value"                       FORMAT a20


COLUMN unit                HEADING "Unit"                        FORMAT a30


COLUMN time_computed       HEADING "TimeComputed"                FORMAT a20


COLUMN datum_time          HEADING "datum_time"                  FORMAT a20


SELECT ds.name


     , ds.value


     , ds.unit


     , ds.time_computed


     , ds.datum_time


  FROM v$dataguard_stats ds ;




==

set linesize 300 pagesize 300
col VALUE for a150 wrap
col NAME for a30
select NAME,VALUE from V$SPPARAMETER where NAME like 'log_archive_dest%'
and VALUE is not null
and VALUE like '%db_unique_name%'
--and VALUE like 'LGWR%'
;

set linesize 300
col VALUE for a50
col NAME for a40
select name, value
from v$parameter
where name = 'log_archive_dest'
and value is not null
union all
select p.name, p.value
from v$parameter p where
name like 'log_archive_dest%'
and p.name not like '%state%'
and p.value is not null
and 'enable' = (
	select lower(p2.value)
	from v$parameter p2
	where p2.name =  substr(p.name,1,instr(p.name,'_',-1)) || 'state' || substr(p.name,instr(p.name,'_',-1))
)
union all
select p.name, p.value
from v$parameter p
where p.name like 'log_archive_dest_stat%'
and lower(p.value) = 'enable'
and (
	select p2.value
	from v$parameter p2
	where name = substr(p.name,1,16) || substr(p.name,instr(p.name,'_',-1))
) is not null
/

set linesize 300 pagesize 300

col VALUE for a150 wrap

col NAME for a30

select NAME,VALUE from V$SPPARAMETER where NAME like 'log_archive_dest%'

and VALUE is not null

and VALUE like '%db_unique_name%'

--and VALUE like 'LGWR%'

;

set linesize 300 pagesize 500

col DESTINATION for a40

col RECOVERY_MODE for a30

col DEST_NAME for a20

select DEST_ID,STATUS,DEST_NAME,RECOVERY_MODE,PROTECTION_MODE,DESTINATION,SYNCHRONIZATION_STATUS,SYNCHRONIZED ,ERROR from v$archive_dest_status

where 1=1

and STATUS='VALID'

;

set linesize 300

col ERROR for a80

select db_unique_name, status, error from v$archive_dest where db_unique_name != 'NONE' ;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 2 June 2013

Oracle Database report

archive log status and error

Oracle DBA

anuj blog Archive