In this post I am going to describe you Oracle Database Monitoring Scripts for every DBA for oracle database.
Oracle database health check:-
Oracle Database Monitoring Scripts.
DBA Daily checklists:
1: Health check of the Database.
2: Viewing the Alert log file to analyze the ORA errors if any.
3: Tablespace Utilization.
4: Rebuilding of Indexes, if bulk load of data is inserted.
5: Viewing the temporary files, through the DBA_TEMP_FILES.
6: Database Growth Comparision.
7: User Management.
8: Backing up the archive log files.
9: Monitoring Backups.
10: Monitoring the log files, backups, database space usage and the use of system resources.
11: Exports/Imports
12: User Management
13: Monitoring Tablespace Segments
14: Monitoring Production Database Performance
15: Solving the ORA errors.
16: Take a COLD/RMAN backups at night time.
17. Checking invalid objects
18. Check free space is available in all tablespaces for objects to extend if required.
19. Check all last night backups were successful.
20. Check all database archiving are done.
Viewing the temporary files, through the DBA_TEMP_FILES.
Check for the invalid objects
Identify bad growth projections.
Clear the trace files in the udump and bdump directory as per the policy.
Verify all the monitoring agent, including OEM agent and third party monitoring agents.
Make a habit to read DBA Manual.
DBA Weekly checklists:
1: Growth of the Database.
2: Full backup of the database through hot backup.
3: Taking logical backups, in case of physical backups failure.
4. Cloning DB & Application
5: Cleaning of alert logs
All core dumps are deleted from the $CDUMP area.
All *.trc files are deleted from the $UDUMP area.
DBA Monthly checklists:
1: Plan for Gather Stats for entire database.
2: Index Rebuild.
3: Tablespace Reorganization.
4: Plan for Backup restoration activity to validate database backup.
DBA Quarterly checklists:
1: Patching
2: Database Reorganization
Some useful scripts for oracle database health check:
——————————————————————————————–
Script: To check Tablespace size .
SELECT /* + RULE */ df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
Script: To check Invalid objects.
SQL>select owner,object_name,status,object_type from dba_objects where status=’INVALID’;
Script: To check RMAN backup completion status.
select (select NAME from v$database) Instance,
object_type,operation,status,session_stamp,
round((end_time – start_time) * 24 * 60, 2) Elapsed_Time,
decode(to_char(start_time, ‘d’), 1, ‘Sunday’, 2, ‘Monday’,
3, ‘Tuesday’, 4, ‘Wednesday’,
5, ‘Thursday’, 6, ‘Friday’,
7, ‘Saturday’) dow,
to_char(start_time, ‘mm/dd/yyyy hh:mi:ss’) start_time,
to_char(end_time, ‘mm/dd/yyyy hh:mi:ss’) end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb,
round((output_bytes/(1024*1024*1024)),2) output_gb
from v$rman_status
where start_time > trunc(sysdate-10) and operation = ‘BACKUP’;
object_type,operation,status,session_stamp,
round((end_time – start_time) * 24 * 60, 2) Elapsed_Time,
decode(to_char(start_time, ‘d’), 1, ‘Sunday’, 2, ‘Monday’,
3, ‘Tuesday’, 4, ‘Wednesday’,
5, ‘Thursday’, 6, ‘Friday’,
7, ‘Saturday’) dow,
to_char(start_time, ‘mm/dd/yyyy hh:mi:ss’) start_time,
to_char(end_time, ‘mm/dd/yyyy hh:mi:ss’) end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb,
round((output_bytes/(1024*1024*1024)),2) output_gb
from v$rman_status
where start_time > trunc(sysdate-10) and operation = ‘BACKUP’;
Script: To check free, pct_free, and allocated space within a tablespace
SELECT tablespace_name, largest_free_chunk, nr_free_chunks,
sum_alloc_blocks, sum_free_blocks, to_char(100*sum_free_blocks/sum_alloc_blocks, ‘09.99’) || ‘%’ AS pct_free FROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name),
sum_alloc_blocks, sum_free_blocks, to_char(100*sum_free_blocks/sum_alloc_blocks, ‘09.99’) || ‘%’ AS pct_free FROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name;
Script: To analyze tables and indexes
BEGIN
dbms_utility.analyze_schema ( ‘&OWNER’, ‘ESTIMATE’, NULL, 5 ) ;
END ;
Script: To find out any object reaching <threshold>
SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , ‘999,999.90’) as MB
FROM dba_extents e , dba_segments s WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents – count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc;
The above query will find out any object reaching <threshold> level extents, and then you have to manually upgrade it to allow unlimited max_extents (thus only objects we expect to be big are allowed to become big.
Script: To identify space-bound objects. If all is well, no rows are returned.
SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk;
Run the above query to find the space bound object. If all is well no rows are returned if found something, then look at the value of next extent. Check to find out what happened then use coalesce (alter tablespace <foo> coalesce;). and finally, add another datafile to the tablespace if needed.
Script: To find tables that don’t match the tablespace default for NEXT extent.
SELECT segment_name, segment_type, ds.next_extent as Actual_Next
, dt.tablespace_name, dt.next_extent as Default_Next
FROM dba_tablespaces dt, dba_segments ds
WHERE dt.tablespace_name = ds.tablespace_name
AND dt.next_extent !=ds.next_extent AND ds.owner = UPPER ( ‘&OWNER’ )
ORDER BY tablespace_name, segment_type, segment_name;
Script: To find tables with nonunique PK indexes.
SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name like ‘&PKNAME%’
AND owner = ‘&OWNER’ AND uniqueness = ‘NONUNIQUE’
SELECT c.constraint_name, i.tablespace_name, i.uniqueness
FROM all_constraints c , all_indexes i
WHERE c.owner = UPPER ( ‘&OWNER’ ) AND i.uniqueness = ‘NONUNIQUE’
AND c.constraint_type = ‘P’ AND i.index_name = c.constraint_name;
Script: To check datatype consistency between two environments
SELECT table_name, column_name,
data_type, data_length,data_precision,data_scale,nullable
FROM all_tab_columns — first environment
data_type, data_length,data_precision,data_scale,nullable
FROM all_tab_columns — first environment
WHERE owner = ‘&OWNER’
MINUS
SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,nullable
FROM all_tab_columns@&my_db_link — second environment
WHERE owner = ‘&OWNER2’
order by table_name, column_name;
Script: To find out any difference in objects between two instances
SELECT object_name, object_type
FROM user_objects
MINUS
SELECT object_name, object_type
FROM user_objects@&my_db_link;
Script: To check existing extents
SELECT segment_name, segment_type, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = ‘&OWNER’
GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent;
The above query will find how many of each object’s extents differ in size from the tablespace’s default size. If it shows a lot of different sized extents, your free space is likely to become fragmented. If so, need to reorganize this tablespace.
Script: To find tables without PK constraint
SELECT table_name FROM all_tables
WHERE owner = ‘&OWNER’
MINUS
SELECT table_name FROM all_constraints
WHERE owner = ‘&&OWNER’ AND constraint_type = ‘P’;
Script: To find out which primary keys are disabled
SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = ‘&OWNER’ AND status = ‘DISABLED’ AND constraint_type = ‘P’;
Good work
growth monitoring is the regular measurement of a child's size Abstract.https://www.jainclinic.in/growth-monitoring.html BACKGROUND Growth monitoring consists of routine measurements to detect abnormal growth, combined with some
action when this is detected. It aims to improve nutrition, reduce the risk of deathor inadequate nutrition, help educat
carers, and lead to early referral for conditions manifest by growth drjaineyeclinic.in order to document growth. The child's size measurementsmust then be plotted on a growth chart.
Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials , Oracle Project Portfolio Management (PPM) Cloud Tutorial and Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Manufacturing , Oracle APEX , Oracle Financials Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can check more aboutOracle Fusion Supply Chain Management Cloud , Oracle HCM Cloud , Oracle Project Portfolio Management (PPM) Cloud , Oracle Cloud Applications to better understand
Thanks for sharing this blog. The content is beneficial and useful. Very informative post. Visit here to learn more about Data Mining companies and Data analytics Companies.
It's really a great and helpful piece of info. I'm glad that you just shared this useful information with us. Please keep us up to date like this. Thank you for sharing.Here is the right place to Submit Guest Post Big Data.
Nice Blog, When I was read this blog, I learnt new things & it’s truly have well stuff related to developing technology, Thank you for sharing this blog. If Someone wants to know about Top Big Data Companies this is the Right place for you!