How to make Scheduler job

+ Create Schedule
SQL> begin
2 dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_1_SECONDS',
start_date => trunc(sysdate),
repeat_interval => 'freq=SECONDLY;interval=1',
comments => 'Runtime: Every day all 1 SECONDS'); 3 4 5 6
7 end;
8 /

PL/SQL procedure successfully completed.

+ Create program

SQL> begin
dbms_scheduler.create_program
(program_name=> 'TEST_PROC01',
program_type=> 'STORED_PROCEDURE',
program_action=> 'SYS.TEST_PROC',
enabled=>true,
comments=>'job interval test'
);
end;
/
PL/SQL procedure successfully completed.

+ Create job

SQL> begin
dbms_scheduler.create_job
(job_name => 'JOB_TEST',
program_name=> 'TEST_PROC01',
schedule_name=>'INTERVAL_EVERY_1_SECONDS',
enabled=>true,
auto_drop=>false,
comments=>'job test');
end;
/
PL/SQL procedure successfully completed.

+ Run Job

SQL> begin
dbms_scheduler.run_job('JOB_TEST',TRUE);
end;
/
PL/SQL procedure successfully completed.

OUI hang during oracle 11gR1 install.

These error messages are in install error trace.

Messages:
java.util.zip.ZipException: invalid entry CRC (expected 0xf286e467 but got 0xab338a44)
at java.util.zip.ZipInputStream.readEnd(ZipInputStream.java:378)
at java.util.zip.ZipInputStream.read(ZipInputStream.java:141)
at java.io.FilterInputStream.read(FilterInputStream.java:90)
at oracle.sysman.oii.oiix.OiixFileOps.copyStream(OiixFileOps.java:1473)
at oracle.sysman.oii.oiij.OiijFastJarExtracter.copyFileFromJar(OiijFastJarExtracter.java:309)
at oracle.sysman.oii.oiij.OiijFastJarExtracter.copyJarContents(OiijFastJarExtracter.java:229)
at oracle.sysman.oii.oiij.OiijFastJarExtracter.extract(OiijFastJarExtracter.java:148)
at oracle.sysman.oii.oiij.OiijJarExtractQueue$OiijJarExtractWorker.run(OiijJarExtractQueue.java:341)

solution:
1. Oracle binary download again.
2. unzip download again.
3. extract downloaded binary.
4. retry install again .. SUCCEED.

related case :
case 1: HardWare Problem. http://forums.oracle.com/forums/thread.jspa?threadID=829922
case 2: file corruption. but it works fine in other server. http://forums.oracle.com/forums/thread.jspa?threadID=703575
case ...

Gather AWR Information about estimated MTTR time

set linesize 200
set pagesize 200
column name format a30
column parameter format a30
column value format a30
column size format 999,999,999,999,999

select a.name, b.* from v$database a, (select to_char(completion_time,'YYYY/MM/DD:HH24') dat, sum(blocks*1024) "size"
from v$archived_log
group by to_char(completion_time,'YYYY/MM/DD:HH24') ) b
order by 2;

select b.name, a.name parameter, a.value
from v$parameter a, v$database b
where a.name in ('fast_start_mttr_target','statistics_level');

SELECT c.name,a.instance_number, to_char(begin_interval_time,'YYYY/MM/DD:HH24:MI:SS') dat, TARGET_MTTR, ESTIMATED_MTTR
FROM DBA_HIST_INSTANCE_RECOVERY a, dba_hist_snapshot b, v$database c
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number;

DNS test program for HP

$ ./getaddrinfo localhost
Host: localhost
IPv4 address: 127.0.0.1 (localhost.oracle.com)

$ ./getaddrinfo CAKOA01
Host: CAKOA01
IPv4 address: 211.106.67.221 (CAKOA01)


============== =============
#include
#include
#include
#include
#include
#include
#include

int lookup_host (const char *host)
{
struct addrinfo hints, *res;
int errcode;
char addrstr[100];
void *ptr;

memset (&hints, 0, sizeof (hints));
hints.ai_family = PF_UNSPEC;
hints.ai_socktype = SOCK_STREAM;
hints.ai_flags |= AI_CANONNAME;

errcode = getaddrinfo (host, NULL, &hints, &res);
if (errcode != 0)
{
perror ("getaddrinfo");
return -1;
}

printf ("Host: %s\n", host);
while (res)
{
inet_ntop (res->ai_family, res->ai_addr->sa_data, addrstr, 100);

switch (res->ai_family)
{
case AF_INET:
ptr = &((struct sockaddr_in *) res->ai_addr)->sin_addr;
break;
case AF_INET6:
ptr = &((struct sockaddr_in6 *) res->ai_addr)->sin6_addr;
break;
}
inet_ntop (res->ai_family, ptr, addrstr, 100);
printf ("IPv%d address: %s (%s)\n", res->ai_family == PF_INET6 ? 6 : 4,
addrstr, res->ai_canonname);
res = res->ai_next;
}

return 0;
}

int
main (int argc, char *argv[])
{
if (argc < 2)
exit (1);
return lookup_host (argv[1]);
}

Oracle 11g seems to ignore your /etc/host.conf

Oracle 11g seems to ignore your /etc/host.conf i.e It always uses DNS first to try to resolve ip adresses from hostnames. If you remove the nameserver from your configuration and add all servers needed by the database to /etc/host it should run fine.
...
When nslookup seems to hang, then your DNS server is down. Just wait about 20 seconds for the next entry in resolv.conf to be tried. If there are 3 DNS servers and all of them are unreachable or not working, then it will take 90 seconds to return. The key here is to tell nslookup what you'd like to do. But before you start, tell nslookup to query a specific DNS server as in:

(http://forums.oracle.com/forums/thread.jspa?threadID=625770)

Re-Submit JOB

CONNECT OWNER_USER/PASSWORD;

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

set serveroutput on
DECLARE
  v_jobno BINARY_INTEGER;
  v_what VARCHAR2(2000);
  v_interval VARCHAR2(200);
  v_errnum NUMBER;
  v_errmsg VARCHAR2(100);
  CURSOR c_job IS
    SELECT job, what, interval FROM user_jobs;
  v_job c_job%ROWTYPE;

BEGIN

  OPEN c_job;
  LOOP
    FETCH c_job INTO v_job;
    EXIT WHEN c_job%NOTFOUND;
    DBMS_JOB.REMOVE(v_job.job);
    DBMS_OUTPUT.PUT_LINE('Job '||v_job.job||' removed.');

    DBMS_JOB.SUBMIT(v_jobno, v_what, sysdate, v_interval, FALSE);
    DBMS_JOB.RUN(v_jobno);
    DBMS_OUTPUT.PUT_LINE('Job '||v_jobno||' submitted.');

  END LOOP;
  CLOSE c_job;

EXCEPTION
  WHEN OTHERS THEN
    v_errnum := SQLCODE;
    v_errmsg := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE('Other error occurred with SQLCODE '||v_errnum);
    DBMS_OUTPUT.PUT_LINE('-Error Message: '||v_errmsg);
    CLOSE c_job;
END;
/

response time per call/execute

  SNAP_ID Timestamp               DB time        User call  recursive call    Execute    response time per call    response time per execute

---------- -------------------- ---------- ---------- -------------- ---------- ---------------------- 

     21869 2010-05-27 02:00:35       11.34     62962           8578       2203             .158540411                5.14842533

     21870 2010-05-27 02:10:37        5.92      39674           9372       1944             .120613465                3.04300823

     21871 2010-05-27 02:20:39        1.96       1577           9006       1713             .185081073                1.14344016

     21872 2010-05-27 02:30:41        3.25      18444           7091       1455             .127185001                2.23207491

     21873 2010-05-27 02:40:44           3      10056           9761       1771             .151221628                1.69212818

     21874 2010-05-27 02:50:46        2.92      10073           9369       1663             .150439204                 1.7587727

     21875 2010-05-27 03:00:48         4.2       3797           9505       2950             .315791234                1.42395085

     21876 2010-05-27 03:10:50        4.74      19537           9814       2099             .161380021                2.25662935

     21877 2010-05-27 03:20:52        3.89      18713           9527       1970             .137674433                 1.9735665

     21878 2010-05-27 03:30:54        1.74       1506           5873       1358             .236378642                1.28441679

     21879 2010-05-27 03:40:56        2.92      10126           9324       1773             .150375938                1.64964016

select sn.snap_id,
       to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi:ss') "Timestamp",
       round(v_dbtime.delta/1000000, 2) "DB time",
       round(sum(case when v_call.stat_name in ('user calls')
                                then v_call.delta
                                else 0
                                end), 10) "User call",
       round(sum(case when v_call.stat_name in ('recursive calls')
                                then v_call.delta
                                else 0
                                end), 10) "recursive call",
       round(sum(case when v_call.stat_name = 'execute count'
                                then v_call.delta
                                else 0
                                end), 10) "Execute",
       round((v_dbtime.delta/1000)/
                            sum(case when v_call.stat_name in ('user calls', 'recursive calls')
                                then v_call.delta
                                else 0
                                end), 10) "response time per call",
       round((v_dbtime.delta/1000)/
                            sum(case when v_call.stat_name = 'execute count'
                                then v_call.delta
                                else 0
                                end), 10) "response time per execute"
  from   ( select snap_id,
                   nvl(value - lag(value) over ( partition by stat_name order by snap_id ), 0)  delta
              from dba_hist_sys_time_model
             where stat_name = 'DB time'
               and snap_id between &&snap_fr and &&snap_to
               and instance_number = &&inst_no
          ) v_dbtime,
          ( select snap_id,
                   stat_name,
                   nvl(value - lag(value) over ( partition by stat_name order by snap_id ), 0)  delta
              from dba_hist_sysstat
             where stat_name in ( 'user calls', 'recursive calls', 'execute count' )
               and snap_id between &&snap_fr and &&snap_to
               and instance_number = &&inst_no
          ) v_call,
          ( select snap_id,
                   end_interval_time
              from dba_hist_snapshot
             where snap_id between 1+ &&snap_fr and &&snap_to
               and instance_number = &&inst_no
          ) sn
 where v_call.snap_id = sn.snap_id
   and v_dbtime.snap_id = sn.snap_id
 group by sn.snap_id,
       to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi:ss'),
       v_dbtime.delta
 order by sn.snap_id;

Log switching status

select thread#,
sum(less5) "Log Switch interval <= 5 min",
sum(bet5_10) "5 ~ 10 min",
sum(bet10_30) "10 ~ 30 min",
sum(over30) "> 30 min",
count(*) "7-day Total"
from (
        select
         thread#,
         case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <=  5 then 1
              when (first_time-lag(first_time) over (partition by thread# order by first_time)) is null then null else 0 end less5,
         case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 >   5
               and (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <= 10 then 1 else 0 end bet5_10,
         case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 >  10
               and (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 <= 30 then 1 else 0 end bet10_30,
         case when (first_time-lag(first_time) over (partition by thread# order by first_time))*24*60 >  30 then 1 else 0 end over30
        from v$log_history
        where first_time >= trunc(sysdate-7)
        )
where less5 is not null
group by thread#
order by thread#;


   THREAD# Log Switch interval <= 5 min 5 ~ 10 min 10 ~ 30 min   > 30 min 7-day Total
---------- ---------------------------- ---------- ----------- ---------- -----------
         1                          892         42          83         99        1116
         2                          141        118          53         92         404
         3                          139        117          56         92         404

What operations use the PGA memory.

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/


SQL> /

       SID OPERATION                           ESIZE        MEM    MAX MEM       PASS      TSIZE
---------- ------------------------------ ---------- ---------- ---------- ----------
      1788 IDX MAINTENANCE (SOR                82568      81752      81752          0
      1788 IDX MAINTENANCE (SOR                82568      81512      81512          0
      1788 IDX MAINTENANCE (SOR                82568      81472      81472          0
      1788 IDX MAINTENANCE (SOR                82568      81640      81640          0
      1788 IDX MAINTENANCE (SOR                82568      81712      81712          0

Active Session's recursive call%

column event format a30
column program format a30
column status format a10
column sql_id format a15
set linesize 200
set pagesize 200

select /*+ ordered */ a.sid,event,p1,p2,p3,program,sql_id,status , b.value "user_calls",
       c.value "recursive_calls", d.value "execution",
       round(c.value/(c.value+b.value) * 100,1) "recursive_call%"
from v$session a, v$sesstat b, v$sesstat c, v$sesstat d
where a.sid = b.sid
and b.sid = c.sid
and c.sid = d.sid
and b.STATISTIC# = 6
and c.STATISTIC# = 7
and d.STATISTIC# = 451
and wait_class not in ('Idle')
/

hang analyze

sqlplus /nolog
connect / as sysdba
oradebug setospid forground_pid
oradebug unlimit
oradebug hanganalyze 3
wait 90 seconds
oradebug hanganalyze 3
exit

find UNDO segments in AMU

$ string system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU.txt
$ cat listSMU.txt

Purge the CURSOR on shared pool

SQL> select sql_id,PLAN_HASH_VALUE,address,hash_value,LAST_LOAD_TIME from v$sqlarea where sql_id in ('bnu1qhzppkqw5');

SQL_ID PLAN_HASH_VALUE ADDRESS HASH_VALUE LAST_LOAD_TIME
--------------- --------------- ---------------- ---------- -------------------
bnu1qhzppkqw5 1923980546 C0000007EC93A170 3948501893 2010/04/25 14:36:53

SQL> exec sys.dbms_shared_pool.purge('C0000007EABE6B60.2390770330', 'C');


SQL> select sql_id,PLAN_HASH_VALUE,address,hash_value,LAST_LOAD_TIME from v$sqlarea where sql_id in ('bnu1qhzppkqw5');

SQL_ID PLAN_HASH_VALUE ADDRESS HASH_VALUE LAST_LOAD_TIME
--------------- --------------- ---------------- ---------- -------------------
bnu1qhzppkqw5 1923980546 C0000007EC93A170 3948501893 2010/04/25 18:26:01

dump the Active session history

sqlplus (-prelim) sys/manager as sysdba
oradebug setmypid
oradebug dump ashdump 10 (10 min)
oradebug tracefile_name

find sessions on AWR with given SQL

column start_time format a30
column end_time format a30
column Active_event format 999999
select SESSION_ID,SESSION_SERIAL#,count(*) Active_event ,min(sample_time) Start_time, max(sample_time) End_time,
max(sample_time)-min(sample_time) "Duration(min)"
from dba_hist_active_sess_history
where SQL_ID = &SQL_ID
group by SESSION_ID,SESSION_SERIAL#
order by 6 desc
/

SESSION_ID SESSION_SERIAL# ACTIVE_EVENT START_TIME END_TIME Duration(min)
---------- --------------- ------------ ------------------------------ ------------------------------ -----------------------
2091 1030 549 2010-04-26-22.03.19.111000 2010-04-29-20.02.12.795000 +000000002 21:58:53.684
2094 4777 1210 2010-04-26-22.03.19.111000 2010-04-28-02.48.58.449000 +000000001 04:45:39.338
2100 12331 2747 2010-04-28-18.50.07.503000 2010-04-29-02.32.24.297000 +000000000 07:42:16.794
2094 19156 3 2010-04-22-22.01.18.791000 2010-04-22-22.01.38.991000 +000000000 00:00:20.200
2139 8341 2 2010-04-22-22.01.18.791000 2010-04-22-22.01.28.891000 +000000000 00:00:10.100

Check SQL performance on AWR history data.

set linesize 200
set pagesize 200
column end_interval_time format a30
column sql_id format a15
select end_interval_time, a.instance_number,sql_id, plan_hash_value,BUFFER_GETS_DELTA,EXECUTIONS_DELTA,BUFFER_GETS_DELTA/EXECUTIONS_DELTA BUFFER_EXEC
from dba_hist_sqlstat a, dba_hist_snapshot b where a.snap_id = b.snap_id
and a.instance_number = b.instance_number and sql_id = &SQL_ID
and EXECUTIONS_DELTA > 0
order by 1
/

Finding Library cache pin holder and what waiting for

REM Library Cache Pin holders we are wainting for:

select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse ;

REM What are the holders waiting for?

select sid,substr(event,1,30),wait_time
from v$session_wait
where sid in (select sid from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse );

Check reference constraints.

column r_owner format a20
column r_constraint_name format a30
column owner format a20
column table_name format a30
column constraint_name format a30
column "Constraint Disable COMMAND" format a150
set linesize 200
set pagesize 200
select r_owner,r_constraint_name,owner,table_name,constraint_name,constraint_type
from dba_constraints
where r_constraint_name in (select constraint_name from dba_constraints where table_name = upper('&&TABLE_NAME'))
/
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' "Constraint Disable COMMAND"
from dba_constraints
where r_constraint_name in (select constraint_name from dba_constraints where table_name = upper('&TABLE_NAME'))
/

1. check and list the reference constraints
2. make commands to disable reference constraint

Tested in Oracle 11g R2, 10g R2

Monitoring Alert and Delivery Message in EM grid control

select TARGET_NAME||' /'||METRIC_NAME||' /'||KEY_VALUE METRIC_NAME,TIMESTAMP,DELIVERED,substr(b.MESSAGE,1,100) message,ALERT_STATE,substr(b.DELIVERY_MESSAGE,1,50) DELIVERY_MESSAGE
from sysman.mgmt_notification_log a, SYSMAN.MGMT$ALERT_NOTIF_LOG b
where a.SOURCE_OBJ_GUID(+) =b.SOURCE_OBJ_GUID
and to_char(TIMESTAMP,'MMDD') = '0311'
-- and target_name = 'LISTENER_PMGT_FLPEME01'
-- and DELIVERED ='Y'
order by TIMESTAMP
/

This script is made in Oracle 11g R1 and Em grid control 10.2.0.5.

Monitoring Active DataGuard

column process format a10
column status format a15
column client_pid format a10
column group# format a10
column name format a60
set linesize 200
alter session set nls_date_format = 'MM/DD HH24:MI:SS';
select PROCESS,PID,STATUS,CLIENT_PROCESS,CLIENT_PID,GROUP#,THREAD#,SEQUENCE#,DELAY_MINS,BLOCK#,BLOCKS from V$MANAGED_STANDBY
where status not in ('IDLE','CLOSING')
order by status
/
select b.NAME,b.DEST_ID, b.THREAD#,b.SEQUENCE#,b.STANDBY_DEST,b.ARCHIVED,b.APPLIED,b.STATUS,b.COMPLETION_TIME
from
(select thread#,max(sequence#) sequence from V$ARCHIVED_LOG group by thread#) a, v$archived_log b
where a.thread# = b.thread# and a.sequence = b.sequence#
/
column name format a30
column value format a50
column TIME_COMPUTED format a30
column unit format a30
select * from V$DATAGUARD_STATS
/