Category: Oracle RDBMS

Houston – we have deleted all SYS DBMS_SCHEDULER Jobs !

Bad, bad PL/SQL…. A customer of me has executed a simple script to cleanup DBMS_SCHEDULER jobs and programs for an application. Unfortunately he did as user SYS. The content of the SQL script: BEGIN FOR JOB IN (SELECT * FROM USER_SCHEDULER_JOBS) LOOP DBMS_SCHEDULER.DROP_JOB(JOB_NAME => JOB.JOB_NAME, FORCE => TRUE); END LOOP; FOR PROG in (select * from USER_SCHEDULER_PROGRAMS) LOOP DBMS_SCHEDULER.DROP_PROGRAM(PROGRAM_NAME => PROG.PROGRAM_NAME, FORCE => TRUE); END LOOP; END; / All the Oracle internal maintenance jobs and programs like the AUTO_SPACE_ADVISOR_PROG, GATHER_STATS_PROG, FILE_WATCHER_PROGRAM are deleted. SQL> SELECT owner,job_name 2 FROM dba_scheduler_jobs 3 ORDER by job_name; no rows selected SQL> SELECT owner,program_name 2 FROM dba_scheduler_programs 3 ORDER by program_name; no rows selected On a 12.1.0.2 single instance database on Linux are per default…

Read More

Oracle Enterprise Manager 13c – Tail your Logfiles

This are my favorite aliases for Enterprise Manager 13c troubleshooting. Just replace $ORACLE_BASE with your own installation directory and add these aliases in your .profile/.bash_profile. Here is the URL to the Oracle documentation: http://docs.oracle.com/cd/E63000_01/EMADM/logging.htm#EMADM11796 – and feel free to create your own aliases e.g. for the *.OUT logfiles. Oracle Management Server alias taem=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log’ alias taeo=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/em/EMGC_OMS1/sysman/log/emoms.log’ NodeManager alias tanm=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/nodemanager/nodemanager.log’ Weblogic AdminServer alias taad=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.log’ alias tagc=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/GCDomain.log’ Weblogic EMGC_OMS1 Managed Server alias taom=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.log’ Weblogic BIP Managed Server alias tabi=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/BIP.log’ OHS alias taha=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/ohs1/logs/access_log’ alias taho=’tail -f -n 50 $ORACLE_BASE/product/gc_inst/user_projects/domains/GCDomain/servers/ohs1/logs/ohs1.log’

Read More

Oracle Enterprise Manager 13c – KILL SESSION for Application Administrators – Part 1

Basically to execute a ALTER SYSTEM KILL SESSION command you have to be a) a DBA or b) you need the ALTER SYSTEM privilege. Granting the ALTER SYSTEM privilege to a Non-DBA has big risks. This user is now able to change a lot of parameters like memory parameters, NLS settings etc. In one of my projects, a small team of well known application administrators is having a read-only account in Enterprise Manager 12c to verify the performance, see the user sessions and many more of their subset of databases. And sometimes, they have to kill a hanging Oracle session. Until now they called the DBA: “Please do it for me”. Sure, we can build a small PL/SQL procedure on every database…

Read More