Use DBMS_SCHEDULER to schedule jobs

In Oracle 10g and 11g, there are two places holding scheduled jobs:

DBA_SCHEDULER_JOBS
DBA_JOBS

Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.

Here is an example on using DBMS_SCHEDULER to schedule a job:

BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      job_name=>'FIXED_OBJECTS_STATUS',
      job_type=>'PLSQL_BLOCK',
      job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
      REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
      ENABLED=>TRUE
      );
END;
/

After that, you can see the job:

SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';

And, run it and check the log. View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
 
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;

Below line will disable a job:

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');


Recently, a database gave errors in alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists

I ran query to verify it is a scheduled job and to identify the job name:

SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;

OBJ#  OBJECT_NAME
--------  -----------------
8912 AUTO_SPACE_ADVISOR_JOB

Then I used dbms_scheduler to disable the job.

NOTES: 
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:

SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected

Check document 414590.1 indicating the DST package on my PC needs an update.

RMAN script for duplicating database

I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.

ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS

DATE=`date +"%h%d%y_%H%M"`

rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF


In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.

In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.

If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.

CPU Usage by User Session

Oracle system views can be used to find the amount of CPU consumed by each database session. This script will display the CPU information:

SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;

The OS commend "ps -ef" also gives CPU info in the 4th column of the output.

To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:

SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID

In Oracle 11g, if the init parameter audit_trail is set to DB level by

SQL> alter system set audit_trail='DB' scope=spfile;

And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.

Memory used by a DB session

When a database session is slow, it may be helpful on finding the cause by checking the memory consumed by the session.

1. Identify the session ID (SID), if necessary. Such as:
select * from v$session where lower(program) like '%exe%';

2. Identify the process ID (DB_os_pid / spid) on the Database server:

-- If you know the database sesssion ID, run below query
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
;

-- If you know the process ID on the client machine (such as Apps server)
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.process = '&clientpid' -- the process ID on the client/MT server
;

3. Run query to see how much memory is used by the session:
select spid, program, PGA_USED_MEM/1024/1024 memory_mb from v$process
where spid = DB_os_pid;

4. Run about query from time to time when the database session is ACTIVE. If the memory value keeps increasing, there may be a memory leak on the session.

5. On the OS level, run "ps -ef | grep 21561522" to confirm the session time and other characters (assuming 21561522 is the DB_os_pid).

SQLPLUS Hang

Discoverer users reports to me that they can not run Discoverer Plus reports. On the "Request Progress" screen after entering Discoverer login information, they get the little clock has been around multiple times without making progress.

To address the issue, I stopped the Discoverer services on the server, and then failed to start them. To make sure there is no problem on the Discoverer configuration, I tested Sql*Plus on the server and found it hung by just staying on the logging in:

SQL> connect userID/passwd@TNS_STRING

The strange thing was that if I logged onto the same Linux (SuSe 10) server as a different OS user using LDAP Authentication, userID could run Sql*Plus to log onto the TNS_STRING database without problem.

The trace on sqlnet.ora did not help much. Finally, an Oracle document (736509.1) suggests that the nscd (name service cache daemon) is reqired to run for Sql*Plus to work. After Systems Administrator started the nscd daemon, both Discoverer services and Sql*Plus worked well on the server.

NSCD should be started automatically during server reboot and keeps running all the time. Now, the question becomes what did stop the nscd running?