DBA

###To get all the database instance name on a consolidated Unix/Linux server: #because some of the RAC-ONE-NODE db instance has ckpt process name like this ora_ckpt_dbname_1, we have to deal with the third "_" and forth field: ps -ef|grep ckpt|awk '{print $8}'|awk ' FS="_" { print $3"_"$4}'|sed 's/_$//g'|egrep -ve "^$|+ASM|-MGMT" ###To find any running db instance missing in /etc/oratab: for db in `ps -ef|grep ckpt|grep -v -i asm|grep -v -i "MGMTDB"|awk '{print $8}'|awk ' FS="_" {print $3"_"$4}'|sed 's/_$//g'|sort`  do export ORACLE_SID=$db if ! grep -w $ORACLE_SID /etc/oratab 1>/dev/null ; then echo $ORACLE_SID else : fi done ###To loop through the databases on the consolidated server: for example, to find RAC db export ORAENV_ASK=NO for db in `ps -ef|grep ckpt|awk '{print $8}'|sort|awk ' FS="_" { print $3"_"$4;}

After database upgrade from 10g to 12c, some of queries have bad performance, elapsed time are changed from less than 1 second to a few minutes, the immediately fix is to force the application to use 10g optimizer by either change database parameter or use database logon trigger to force specific application to choose the 10g optimizer. I used the database trigger. CREATE OR REPLACE TRIGGER sys.triggername    AFTER LOGON ON DATABASE WHEN (upper(sys_context('USERENV','MODULE')) like '%APP_UPPERCASE%' or upper(sys_context('USERENV','MODULE')) like '% APP_UPPERCASE %') BEGIN /* some functions of application running slow after db upgrade from 10g to 12c, this trigger helps to force them to use 10g optimizer and get their normal performance*/    EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_features_enable=''10.2.0.5'''; END  triggername ;   / I engaged oracle support on troubleshooting this, sent MOS

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [