Missing DBMS_SCHEDULER Jobs via OEM

Under OEM 11.1, have noticed that SYS jobs are not included in the list of current jobs. Of course no DBA would create objects under SYS, however sometimes we do.

If you notice a job is running, even an os command, then query the dba_scheduler_jobs view. This will list all dbms_scheduler jobs, then use notmal dbms_scheduler options to work with this job, prefrebably drop the job.

Invisible Indexes

This is a really nice feature, particulary for me, a development DBA. Essentially you can make an index invisible so the optimizer doesn't use this index. This is excellent testing new indexes. You don't need to spend the time waiting for an index to be recreated each time you want to compare the differences.

To make an index invisable then run:
sql> alter index <index name> INVISIBLE;

To make an index visible:
sql> alter index <index name> VISIBLE;

You can also make the optimizer use all invisible indexes by for the current session by:
sql> alter session set optimizer_use_invisible_indexes = true ;

TAR and Excluding Files

This blog will quickly discuss how to exclude files when using TAR to create an archive.

The syntax to do this is:

tar cvfX <

Packaging Oracle Incidents

Under version 11, oracle introduced an interesting mechanism called "adrci" aka

"AUTOIMATIC DIAGNOSTIC REPOSITORY"

This tool, among other tasks does the following:

 - Automatically purges trace files, alert log files etc
 - Manages "incidents"
 - Packages incidents.

This blog will talk about how to manage the logs and manage incidents. Although many features discussed in this blog can be completed via OEM, I will be discussing how to do this from the command interface.

To Log On, run:

unix> adrci

To show your current environment, run:

adrci> show homes

To set a HOME environment, run:

adrci> set home

To show current incidents, run:

adrci> show incidents

ADR Home = /opt/oracle/admin/diag/rdbms/xxx/XXX:

*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ------------------
12426 ORA 3137 [12333] 2011-11-02 16:47:50.277000 +13:00
1 rows fetched


To purge a particular incident, run:
 
adrci> purge -i n nn nnn
 
To purge a type of data, run:
 
adrci> purge -age nn -type xxx
 
where age is defined in minutes and type can be "alert, incident, trace, dump etc"

11.2 Update Window Details

One of the nice features introduced in recent oracle releases was automated maintenance jobs. This is a nice feature as oracle tries to automate STATS generation and automating execution of advisors.

Sometimes, however the default schedule for these maintenance jobs is not good enough, so to change these windows.

In a nutshell, you need to disable the window, set attributes, duration time, start time etc.

A sample would be:

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SATURDAY_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(1080, 'minute'));
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=3;BYMINUTE=0;BYSECOND=0');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SATURDAY_WINDOW"');
END;