Month: June 2021

This is an aggressive script to clear space in an Exadata compute node. Use with caution as it wipes out a lot of logs quickly.

## Cleanup of filesystems by the root user
# First, cleanup system files
journalctl --vacuum-time=1s

# Remove un-needed system files
rm -rf /tmp/OraInstall*
rm -f /var/log/*.1
rm -f /var/log/*gz
# Clear the ones we do need to keep
:> /var/spool/mail/root
:> /var/log/rdbmsaudit.log
:> /var/log/asmaudit.log

## Oracle file cleanup
. /u01/app/oracle/bin/ora_funcs.sh
# Migrate schemas before we run purgelogs
oe PRD
ADR_HOME=`ls -d /u01/app/oracle/diag/rdbms/ftsprd*/$ORACLE_SID|cut -c17-`
#echo $ADR_HOME
export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/DbHome_1;/u01/app/oracle/product/18.0.0.0/DbHome_1/bin/adrci exec="set homepath $ADR_HOME; migrate schema"
# Fix permissions - If these pv dirs are owned by root, you can't login locally
chown -R oracle /u01/app/oracle/diag/rdbms/*/*/metadata_pv
/u01/app/oracle/bin/purgeLogs -orcl 1 -osw 1 -oda 1 -extra /tmp:1,/var/log:1

DIAGDIR=/u01/app/oracle/diag/rdbms
cd $DIAGDIR

# remove core dumps
rm -rf ${DIAGDIR}/*/*/trace/cdmp*
find /u01/app -name "cdmp*" -type d -delete

# remove incident files
rm -rf ${DIAGDIR}/*/*/incident/incdir*

DIAGDIR=/u01/app

# remove audit files
find /u01/app -mount -name "*.aud" -type f -delete

# remove zero length files older than 6 hours
find $DIAGDIR -mount -name "*.trc" -type f -size 0 -mmin +360 -delete
find $DIAGDIR -mount -name "*.trm" -type f -size 0 -mmin +360 -delete

# clear older trace files
#find $DIAGDIR -mount -name "*.trc" -type f -size +0 -mmin +10 -execdir cp /dev/null {} \;
#find $DIAGDIR -mount -name "*.trm" -type f -size +0 -mmin +10 -execdir cp /dev/null {} \;

# Clear of all large trace files
find $DIAGDIR -mount -name "*.trc" -type f -size +0 -execdir cp /dev/null {} \;
find $DIAGDIR -mount -name "*.trm" -type f -size +0 -execdir cp /dev/null {} \;

# Check ownership of directories
ls -ld /u01/app/oracle/diag/rdbms/*/*/metadata_pv

If you are scheduling jobs on a RAC it would be nice to be able to control which instance the job will run in. This is easy to do using job classes with these steps:

1. Create a new job class and assign a service name to the class
2. Grant execute in that job class to all the Oracle accounts that you want to use it with
3. Assign the new job class to existing (or new) jobs.

Here are some SQL snippets to help with steps 2 and 3:

— make executable grants for job classes where needed
select distinct ‘grant execute on SYS.’||o.object_name||’ to ‘||j.owner || ‘;’
from dba_objects o, dba_scheduler_jobs j
where o.object_type = ‘JOB CLASS’ and o.oracle_maintained = ‘N’ and j.owner not like ‘SYS%’
minus
select distinct ‘grant execute on SYS.’||p.table_name||’ to ‘||p.grantee || ‘;’
from dba_tab_privs p where p.privilege = ‘EXECUTE’ and type = ‘JOB CLASS’;

@fix job classes@
— fix job classes
select owner,job_name,job_class,’exec SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => ”’||owner||’.’||job_name||”’, attribute => ”JOB_CLASS”, value => ”ETL_JOB_CLASS”);’
from dba_scheduler_jobs j where owner not in (‘SYS’,’APEX_040200′) and job_class = ‘DEFAULT_JOB_CLASS’
order by 1, 2;