Oracle daily maintenance-invalid object

1.About check_mk metric:ORA_ORCL_Object_Invalid

This is a custom monitoring metric on check_mk platform,
the threshold is defined in /usr/lib/check_mk_oracle/MAIN/conf/perf_IYOA_object.json

[oracle@idpcn-iyoa conf]$ cat perf_IYOA_object.json 
{"target_info":[{
"perf_min":"0",
"chk_interval":5000,
"output_cmd":"echo",
"post_action":"",
"export2rdb":"Y",
"perf_crit":"30",
"chk_cmd":"sh run_invalid.sh",
"perf_max":"0",
"chk_result_separator":";",
"perf_warn":"1",
"database":"IYOA"
}]}

notice the two threshold value:“perf_warn”:“1”,“perf_crit”:“30”,Represent warning and critical respectively

2.solution

2.1 check whether there are invalid objects in DB

SELECT  *  FROM  dba_objects   
        WHERE   status='INVALID'  
        AND OWNER NOT IN ('SYSMAN','WMSYS','PUBLIC','SYS','OLAPSYS','CTXSYS','XDB','EXFSYS','ORDSYS','MDSYS') 
        AND OBJECT_TYPE NOT IN ('MATERIALIZED VIEW')   ;

2.2 Generate batch sql for recompile purpose

SELECT  'alter '||object_type||' '||owner||'.'||object_name||' compile;'  batch_script FROM  dba_objects   
        WHERE   status='INVALID'  
        AND OWNER NOT IN ('SYSMAN','WMSYS','PUBLIC','SYS','OLAPSYS','CTXSYS','XDB','EXFSYS','ORDSYS','MDSYS') 
        AND OBJECT_TYPE NOT IN ('MATERIALIZED VIEW');

output:

2.3 paste the previous output results to linux text file in linux

vi /home/oracle/alter.sql

alter PROCEDURE MYSIS.P_BUMILSAKT_ALERT compile;
alter PROCEDURE MYSIS.PCR_WORK6DAYSTEST compile;
alter FUNCTION MYSIS.FCR_CALCOT2_TESTVPLG compile;
alter SYNONYM MYSIS.CR_TRANS compile;
-- INSERT --                                  

2.4 save and quit text editor, then go into sqlplus

sqlplus /nolog
connect / as sysdba

2.5 execute the sql script in sqlplus

@/home/oracle/alter.sql
Warning: Procedure altered with compilation errors.
Procedure altered.

“Procedure altered” mean successful recompile, “Warning” mean unsuccessful compile

2.6 co-work with developer

for unsuccessful recompilation, need to re-execute step2.1, and post the output result to relevant developer, ask them review source code and provide necessary support