#### ######Database Health Check ###################
Crontab Details:
35 04 * * * /u04/MasterDB/scripts/daily_health_check/daily_healthcheck_bkp.sh
Script Details :
oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/daily_healthcheck_bkp.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/daily_health_check/dailyreport.log
printf "to:varun.yadav@infotech.com\n" > $DR_log
printf "cc:core_oradba@infotech.com\n" >> $DR_log
printf "subject: Daily RODB Database Healthcheck Details " >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set serverout on
set wrap off
set pages 300
set lines 200
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 a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
#column date_column new_value today_var
/
select to_char(sysdate,'yyyy-Mon-dd_hh-miAM')date_column from dual
/
spool /u04/MasterDB/scripts/daily_health_check/SMRPROD_&today_var.txt
PROMPT =============================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT =============================================================
PROMPT CURRENT DATE and TIME
PROMPT ======================
Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;
PROMPT
PROMPT DATABASE NAME
PROMPT =================
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
PROMPT
PROMPT Users logged information
PROMPT ===============================
set lines 750 pages 9999
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv\$session
where status in ('ACTIVE','INACTIVE')
group by username;
PROMPT
PROMPT Previous day archive logs generation
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 latest Archive No.
PROMPT ======================================
archive log list
PROMPT
PROMPT CONTROL FILES
PROMPT ==================
select * from v\$controlfile;
PROMPT
PROMPT LOG FILE GROUPS
PROMPT ==================
set lines 200
col status for a7
col type for a7
col member for a60
col IS_RECOVERY_DEST_FILE for a3
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v\$logfile;
PROMPT
PROMPT
PROMPT LOG FILE MEMBERS
PROMPT ==================
select * from v\$log;
PROMPT
PROMPT SIZE OF DATABASE Daily
PROMPT ==================
SET LINESIZE 200
SET PAGESIZE 200
col creation_time format a25
COL "Database Size" FORMAT a25
COL "Used Space" FORMAT a25
COL "Used in %" FORMAT a25
COL "Free in %" FORMAT a25
COL "Database Name" FORMAT a25
COL "Free Space" FORMAT a25
COL "Growth DAY" FORMAT a25
COL "Growth WEEK" FORMAT a25
COL "Growth DAY in %" FORMAT a25
COL "Growth WEEK in %" FORMAT a25
SELECT
(select min(creation_time) from v\$datafile) "Created",
(select name from v\$database) "DB Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "F
ree in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3)
|| '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*
7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V\$DATAFILE UNION ALL SELECT BYTES FROM V\$TEMPFILE UNION ALL SELECT BYTES FROM V\$LOG) USED, (SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
PROMPT
PROMPT SIZE OF DATABASE
PROMPT ==================
select 'Data files',sum(bytes)/1024/1024/1024 from dba_data_files
union
select 'Temp Files',sum(bytes)/1024/1024/1024 from dba_temp_files
union
select 'Log files',sum(bytes*members)/1024/1024/1024 from v\$log
union
select 'segments',sum(bytes)/1024/1024/1024 from dba_segments
union
select 'free space',sum(bytes)/1024/1024/1024 from dba_free_space;
PROMPT
PROMPT TABLESPACES AND DATAFILES
PROMPT ============================
col TABLESPACE_NAME for a20
col file_id for a7
col TABLESPACE_NAME for a30
col file_name for a60
col STATUS for a9
select TABLESPACE_NAME, to_char(file_id, '999') "File_id", FILE_NAME, BYTES/1024/1024 "Size in MB" , STATUS
from dba_data_files
order by TABLESPACE_NAME, file_name;
PROMPT
PROMPT DATAFILES Details in AutoExtend Mode
PROMPT ====================================
set lines 200
col FILE_NAME for a60
col tablespace_name for a30
col AUTOEXTENSIBLE for a3
col bytes for a10
select file_name,tablespace_name,AUTOEXTENSIBLE,bytes/1024/1024/1024 "SIZE IN GB " from dba_data_files
where AUTOEXTENSIBLE='YES' order by tablespace_name;
PROMPT
PROMPT UTILIZATION OF TABLESPACES
PROMPT ============================
set lines 200
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace;
PROMPT
PROMPT TABLESPACES utl >85
PROMPT ============================
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
PROMPT OBJECTS WHOSE STATUS ARE INVALID
PROMPT ===================================
Rem Check object status
select OBJECT_NAME, owner, object_type, STATUS from all_objects where
object_type in
('FUNCTION','INDEX', 'LIBRARY','PACKAGE','PACKAGE BODY',
'PROCEDURE', 'SEQUENCE','SYNONYM','TABLE','TRIGGER',
'TYPE','UNDEFINED','VIEW')
and status = 'INVALID'
and OWNER not in ('SYS','SYSTEM')
and status is not null ;
PROMPT FILES THAT NEEDS RECOVERY
PROMPT =============================
Rem Check wether there is any data file that needs media recovery
select * from v\$recover_file;
PROMPT
PROMPT
PROMPT
PROMPT Database users detail
PROMPT =====================
set lines 200
col username for a25
col account_status for a10
col default_tablespace for a20
select username,account_status,default_tablespace from dba_users;
PROMPT
PROMPT
PROMPT
PROMPT
PROMPT Total Objects
PROMPT =========================
set lines 140
col owner for a30
select owner as "SCHEMA",count(*) from dba_objects
group by owner;
PROMPT
PROMPT Archive Log generation History for Last one month
PROMPT ==================================================
set lines 200
SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v\$log_history where to_date(first_time) > sysdate -30 GROUP by to_char(first_time,'YYYY-MON-DD'),
to_date(first_time) order by to_date(first_time)
;
PROMPT
PROMPT
PROMPT dba_jobs assinged
PROMPT ==================================================
set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/
PROMPT
PROMPT
PROMPT List all materialized view logs
PROMPT ==================================================
select log_owner,log_table from dba_mview_logs
/
PROMPT
PROMPT
PROMPT Show all materialized and resfresh times
PROMPT ==================================================
set lines 100 pages 999
col last_refresh format a20
select owner,mview_name,to_char(last_refresh_date,'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/
PROMPT
PROMPT
PROMPT Show materialized view tables and masters
PROMPT ==================================================
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
PROMPT
PROMPT
PROMPT This is useful for spotting failures
PROMPT ==================================================
set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/
prompt ========================================================================
prompt Rman Backup details
prompt ========================================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v\$rman_backup_job_details
WHERE end_time > sysdate -3;
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt DR SYNC STATUS
prompt ========================================================================
prompt ========================================================================
prompt ========================================================================
select process, thread#, sequence#, status from v\$managed_standby;
set heading off trimspool on
select maxSCN AS PRIMARY
from (select max(sequence#) almax
from v\$archived_log
where resetlogs_change#=(select resetlogs_change# from v\$database where THREAD#=1)
) al,
(select max(sequence#) maxSCN
from v\$log_history
where first_time=(select max(first_time) from v\$log_history where THREAD#=1)
) lh;
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test02:~>
OUTPUT DETAILS :
DATE_COLUMN
----------------------------
2020-May-27_04-30AM
=============================================================
DATABASE HEALTH CHECK REPORT
=============================================================
CURRENT DATE and TIME
======================
Current Date/Time
-------------------
2020-05-27 04:30:01
DATABASE NAME
=================
DBID NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
---------- -------------------------------------------------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------
4255065810 test02 test022 test02 READ WRITE PRIMARY YES 0
Users logged information
===============================
STATUS COUNT(*)
---------------- ----------
ACTIVE 96
INACTIVE 149
Previous day archive logs generation
======================================
COUNT(*)
----------
18
latest Archive No.
======================================
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA2/archivelog
Oldest online log sequence 1410
Next log sequence to archive 1412
Current log sequence 1412
CONTROL FILES
==================
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
---------------- -------------------------------------------------- --- ---------- -------------- ----------
+DATA/test02/CONTROLFILE/current.261.1020157971 NO 16384 1926 0
+FRA1/test02/CONTROLFILE/current.256.1020157971 YES 16384 1926 0
LOG FILE GROUPS
==================
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE +DATA/test02/ONLINELOG/group_1.267.1020692667 NO
1 ONLINE +FRA1/test02/ONLINELOG/group_1.260.1020692669 YES
2 ONLINE +DATA/test02/ONLINELOG/group_2.266.1020692669 NO
2 ONLINE +FRA1/test02/ONLINELOG/group_2.259.1020692671 YES
3 ONLINE +DATA/test02/ONLINELOG/group_3.263.1020692675 NO
3 ONLINE +FRA1/test02/ONLINELOG/group_3.258.1020692677 YES
4 ONLINE +DATA/test02/ONLINELOG/group_4.262.1020692677 NO
4 ONLINE +FRA1/test02/ONLINELOG/group_4.257.1020692679 YES
5 ONLINE +DATA/test02/ONLINELOG/group_5.271.1030949473 NO
5 ONLINE +FRA1/test02/ONLINELOG/group_5.284.1030949475 NO
6 ONLINE +DATA/test02/ONLINELOG/group_6.274.1030949487 NO
6 ONLINE +FRA1/test02/ONLINELOG/group_6.285.1030949487 NO
12 rows selected.
LOG FILE MEMBERS
==================
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ------- ------------- --------- ------------ --------- ----------
1 1 1745 524288000 512 2 YES INACTIV 149135858 26-MAY-20 149374540 26-MAY-20 0
2 1 1744 524288000 512 2 YES INACTIV 148883590 26-MAY-20 149135858 26-MAY-20 0
3 2 1412 524288000 512 2 NO CURRENT 149459368 26-MAY-20 2.8147E+14 0
4 2 1411 524288000 512 2 YES INACTIV 149374292 26-MAY-20 149459368 26-MAY-20 0
5 1 1746 524288000 512 2 NO CURRENT 149374540 26-MAY-20 2.8147E+14 0
6 2 1410 52428800 512 2 YES INACTIV 149365860 26-MAY-20 149374292 26-MAY-20 0
6 rows selected.
SIZE OF DATABASE Daily
==================
Created DB Name Database Size Used Space Used in % Free Space F
ree in % Growth DAY Growth DAY in % Growth WEEK Growth WEEK i
--------- --------- ------------- ----------------- ----------------- ----------------- -------------------------------------------- ----------------- ----------------- ----------------- -------------
07-JUL-14 test02 58970 MB 24736 MB 41.95% MB 34233.81 MB 58.05% MB 11.5 MB .02% MB 80.5 MB .137% MB
SIZE OF DATABASE
==================
'DATAFILES SUM(BYTES)/1024/1024/1024
---------- -------------------------
Data files 45.0976563
Log files 4.98046875
Temp Files 10
free space 33.4314575
segments 11.6896362
TABLESPACES AND DATAFILES
============================
TABLESPACE_NAME File_id FILE_NAME Size in MB STATUS
------------------------------ ------- ------------------------------------------------------------ ---------- ---------
SYSAUX 3 +DATA/test02/DATAFILE/sysaux.257.1020157747 5120 AVAILABLE
SYSTEM 1 +DATA/test02/DATAFILE/system.258.1020157783 10240 AVAILABLE
UNDOTBS1 4 +DATA/test02/DATAFILE/undotbs1.260.1020157819 4096 AVAILABLE
UNDOTBS2 5 +DATA/test02/DATAFILE/undotbs2.265.1020158095 4096 AVAILABLE
USERS 6 +DATA/test02/DATAFILE/users.259.1020157817 100 AVAILABLE
8 rows selected.
DATAFILES Details in AutoExtend Mode
====================================
FILE_NAME TABLESPACE_NAME AUT SIZE IN GB
------------------------------------------------------------ ------------------------------ --- -----------
+DATA/test02/
+DATA/test02/DATAFILE/sysaux.257.1020157747 SYSAUX YES 5
+DATA/test02/DATAFILE/system.258.1020157783 SYSTEM YES 10
+DATA/test02/DATAFILE/undotbs1.260.1020157819 UNDOTBS1 YES 4
+DATA/test02/DATAFILE/undotbs2.265.1020158095 UNDOTBS2 YES 4
UTILIZATION OF TABLESPACES
============================
TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) %Used % Free
--------------- --------------- -------------- ------------- ---------- ----------
SYSAUX 5120 2034 3086 39.73 60.27
SYSTEM 10240 834 9406 8.14 91.86
UNDOTBS1 4096 22 4074 .54 99.46
UNDOTBS2 4096 64 4032 1.56 98.44
USERS 100 2 98 2 98
8 rows selected.
TABLESPACES utl >85
============================
no rows selected
ASM DISK GROUP Details
===================================
NAME STATE TOTAL_MB HOT_USED_MB FREE_MB
-------------------------------------------------- ----------- ---------- ----------- ----------
FRA2 CONNECTED 204800 0 152945
BACKUP MOUNTED 167927 0 167822
DATA CONNECTED 204800 0 145661
FRA1 CONNECTED 204800 0 190524
CRS MOUNTED 61440 0 51994
OBJECTS WHOSE STATUS ARE INVALID
===================================
no rows selected
FILES THAT NEEDS RECOVERY
=============================
no rows selected
Database users detail
=====================
USERNAME ACCOUNT_ST DEFAULT_TABLESPACE
------------------------- ---------- --------------------
SCOTT EXPIRED & USERS
ORACLE_OCM EXPIRED & USERS
OJVMSYS EXPIRED & USERS
SYSKM EXPIRED & USERS
XS$NULL EXPIRED & USERS
GSMCATUSER EXPIRED & USERS
MDDATA EXPIRED & USERS
SYSBACKUP EXPIRED & USERS
37 rows selected.
Total Objects
=========================
SCHEMA COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ----------
APEX_040200 3421
DVF 19
MDSYS 1873
PUBLIC 37028
OUTLN 10
CTXSYS 409
OLAPSYS 25
FLOWS_FILES 13
SYSTEM 639
ORACLE_OCM 6
SCOTT 6
DVSYS 292
AUDSYS 12
GSMADMIN_INTERNAL 108
DBSNMP 55
OJVMSYS 24
ORDSYS 3157
ORDPLUGINS 10
APPQOSSYS 5
XDB 986
ORDDATA 292
SYS 42720
WMSYS 389
SI_INFORMTN_SCHEMA 8
LBACSYS 237
26 rows selected.
Archive Log generation History for Last one month ==================================================
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
28-APR-20 2 0 0 0 0 6 0 0 0 1 0 0 1 0 0 2 1 1 0 2 0 0 2 0
29-APR-20 0 0 0 0 0 6 0 0 0 0 1 1 0 0 2 0 1 0 1 0 0 2 1 0
30-APR-20 2 0 0 0 0 6 0 0 0 0 0 0 0 0 1 2 0 1 1 0 0 1 0 1
01-MAY-20 0 0 0 0 0 6 0 0 0 0 0 0 0 0 0 1 0 0 1 1 0 0 1 0
02-MAY-20 1 1 0 0 0 6 0 0 0 0 0 0 0 2 1 15 3 0 0 0 0 0 2 0
03-MAY-20 0 0 0 0 2 6 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 2 0
04-MAY-20 1 0 0 0 0 6 0 0 0 0 0 1 3 0 1 0 0 3 0 1 0 0 3 0
05-MAY-20 0 0 0 0 0 6 0 0 0 0 0 1 0 3 0 1 0 3 1 0 0 2 1 0
06-MAY-20 1 0 0 0 0 6 0 0 0 0 0 1 2 0 1 0 0 3 0 0 1 0 3 0
07-MAY-20 0 0 0 1 0 6 0 0 0 0 0 0 1 1 0 2 0 2 0 0 2 0 2 0
08-MAY-20 0 0 2 0 0 6 0 0 0 0 0 0 1 2 0 1 0 0 1 2 0 0 0 0
09-MAY-20 1 0 0 0 0 6 0 0 0 0 0 0 1 0 2 0 0 0 0 0 0 0 0 0
10-MAY-20 0 0 1 0 0 6 0 0 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0
11-MAY-20 2 0 0 0 0 6 0 0 0 0 0 0 3 0 0 1 0 0 3 0 1 0 3 0
12-MAY-20 0 0 1 0 0 6 0 0 1 2 0 0 1 0 0 3 0 1 0 0 3 0 1 0
13-MAY-20 0 0 3 0 0 6 0 0 0 1 0 0 0 0 3 1 2 1 1 0 0 2 1 0
14-MAY-20 0 1 0 0 0 6 0 0 0 0 0 0 2 0 0 2 0 0 2 0 2 0 0 2
15-MAY-20 0 0 0 0 0 6 0 0 0 0 0 0 1 0 1 3 0 0 3 0 0 0 1 0
16-MAY-20 1 2 0 0 0 6 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
17-MAY-20 3 0 0 0 1 6 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0
18-MAY-20 0 0 2 0 0 6 0 0 0 1 2 0 0 1 3 1 3 0 1 0 3 0 1 0
19-MAY-20 3 0 0 0 1 6 0 0 0 0 0 0 3 0 2 2 0 0 1 0 0 3 0 0
20-MAY-20 1 0 0 0 0 6 0 0 0 1 2 0 0 1 0 3 0 1 0 1 2 0 1 1
21-MAY-20 2 0 0 0 0 6 0 0 0 0 0 1 0 3 1 3 0 1 3 0 1 0 0 2
22-MAY-20 1 0 0 0 0 6 0 0 0 0 1 0 0 3 0 1 0 0 3 0 0 0 1 0
23-MAY-20 0 0 0 0 0 7 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0
24-MAY-20 0 0 0 1 1 6 0 0 0 0 0 0 1 0 0 0 0 0 2 0 0 1 0 0
25-MAY-20 0 1 0 0 0 6 0 0 0 1 1 0 0 2 0 0 0 0 2 0 0 0 2 0
26-MAY-20 0 0 0 0 0 6 0 0 0 0 0 0 3 0 0 1 0 3 0 0 1 0 2 0
29 rows selected.
dba_jobs assinged
==================================================
no rows selected
List all materialized view logs
==================================================
rows will be truncated
no rows selected
Show all materialized and resfresh times ==================================================
rows will be truncated
no rows selected
Show materialized view tables and masters ==================================================
no rows selected
This is useful for spotting failures
==================================================
no rows selected
========================================================================
Rman Backup details
========================================================================
START_TIME END_TIME TIME_TAKEN STATUS INPUT_TYPE Device_TYPE DATA SIZE Backup Size Speed/s COMPRESSION_RATIO
--------------- --------------- ---------- --------- ------------- ----------- ---------- ----------- ---------- -----------------
12-MAY-20 05:05 12-MAY-20 05:11 00:06:19 COMPLETED DB FULL DISK 38.22G 9.08G 24.52M 4.2108604
13-MAY-20 05:05 13-MAY-20 05:11 00:06:34 COMPLETED DB FULL DISK 40.25G 9.62G 25.00M 4.18368123
14-MAY-20 05:05 14-MAY-20 05:12 00:06:56 COMPLETED DB FULL DISK 42.16G 10.14G 24.96M 4.15814145
15-MAY-20 05:05 15-MAY-20 05:12 00:07:05 COMPLETED DB FULL DISK 43.81G 10.58G 25.50M 4.13905218
16-MAY-20 05:05 16-MAY-20 05:12 00:07:11 COMPLETED DB FULL DISK 45.46G 11.02G 26.19M 4.12447591
17-MAY-20 05:05 17-MAY-20 05:13 00:07:47 COMPLETED DB FULL DISK 46.35G 11.28G 24.73M 4.10999059
18-MAY-20 05:05 18-MAY-20 05:12 00:07:25 COMPLETED DB FULL DISK 47.11G 11.48G 26.42M 4.10319997
19-MAY-20 05:05 19-MAY-20 05:12 00:07:17 COMPLETED DB FULL DISK 49.62G 12.15G 28.47M 4.08355395
20-MAY-20 05:05 20-MAY-20 05:12 00:07:16 COMPLETED DB FULL DISK 51.33G 12.62G 29.64M 4.06775725
21-MAY-20 05:05 21-MAY-20 05:12 00:07:21 COMPLETED DB FULL DISK 53.34G 13.15G 30.55M 4.05461637
22-MAY-20 05:05 22-MAY-20 05:12 00:07:22 COMPLETED DB FULL DISK 55.60G 13.74G 31.83M 4.04704181
23-MAY-20 05:05 23-MAY-20 05:13 00:07:42 COMPLETED DB FULL DISK 57.15G 14.16G 31.39M 4.03580041
24-MAY-20 05:05 24-MAY-20 05:13 00:07:38 COMPLETED DB FULL DISK 58.05G 14.42G 32.24M 4.02588048
25-MAY-20 05:05 25-MAY-20 05:13 00:07:51 COMPLETED DB FULL DISK 58.88G 14.64G 31.84M 4.02118405
26-MAY-20 05:05 26-MAY-20 05:14 00:08:44 COMPLETED DB FULL DISK 60.23G 15.00G 29.31M 4.01527924
15 rows selected.
========================================================================
Process Monitor - PMON
========================================================================
oracle 11459 11201 0 04:30 ? 00:00:00 /bin/sh -c ps -ef |grep pmon
oracle 11461 11459 0 04:30 ? 00:00:00 grep pmon
grid 14704 1 0 May02 ? 00:02:31 asm_pmon_+ASM1
oracle 15257 1 0 May02 ? 00:05:36 ora_pmon_test021
========================================================================
Listener status process
========================================================================
oracle 11462 11201 0 04:30 ? 00:00:00 /bin/sh -c ps -ef |grep lsnr
oracle 11464 11462 0 04:30 ? 00:00:00 grep lsnr
grid 15027 1 0 May02 ? 00:00:47 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid 15045 1 0 May02 ? 00:00:42 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
========================================================================
MOUNTPOINT DETAILS
========================================================================
Filesystem Size Used Avail Use% Mounted on
/dev/test024 100G 57G 43G 57% /u01
Filesystem Size Used Avail Use% Mounted on
/dev/test025 400G 49G 352G 13% /u02
Filesystem Size Used Avail Use% Mounted on
/dev/test026 350G 29G 322G 9% /u03
Filesystem Size Used Avail Use% Mounted on
/dev/test027 2.7T 690G 2.1T 26% /u04
========================================================================