DBMS_SCHEDULER FAILS log
Oracle SCHEDULER log
SCHEDULER log
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
-- AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;
Monday, 16 January 2012
Generate DDL for scheduler jobs
Generate DDL for scheduler jobs
dbms_metadata job
set long 1000000000 pagesize 0 trimspool on
select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',JOB_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_scheduler_jobs
where JOB_TYPE is not null
and owner !='SYS';
or select from dba_objects
set long 1000000000 pagesize 0 trimspool on
select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',object_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_objects
where object_type='JOB'
and owner !='SYS';
or
expdp \"/ as sysdba\" dumpfile=scott_job.dmp SCHEMAS=SCOTT content=metadata_only include=JOB
****************************************************************************************************
Dbms_metadata.Get_ddl Can Not Capture Ddl For Scheduler Jobs [ID 567504.1] for sys owner ????????
not work for owner ='SYS'; no idea why !!!!!!!!!!!!!!!!!!!!
as per metalink <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Currently the DBMS_METADATA does not support the new 'job' type created by DBMS_SCHEDULER.
********************************************************************************************************
Saturday, 14 January 2012
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
in Datapump if you are getting this error
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/Oracle_Datapump/orcl.datapump..dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 16:02:28
Then check folloing pl
select * from v$option where parameter like 'Transparent Data Encryption%';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption TRUE
encryption_password=xxx provide some password
expdp "'/ as sysdba'" directory=anuj_datapump SCHEMAS=scott EXCLUDE=STATISTICS parallel=4 encryption_password=vihaan
this time no error
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/Oracle_Datapump/orcl.datapump.14012012.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:34:30
Friday, 13 January 2012
AWR Load Trending Report
AWR Load Trending Report
AWR Load report
set lines 130 pages 1000
col stat_name for a25
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
col redo_size for '999,999,990.99'
col sess_l_reads for '999,999,990.99'
col blk_change for '999,999,990.99'
col phy_reads for '999,999,990.99'
col phy_writes for '999,999,990.99'
col user_calls for '999,999,990.99'
col parse_count_tot for '999,999,990.99'
col parse_count_hard for '999,999,990.99'
col sort_disk for '999,999,990.99'
col logons for '999,999,990.99'
col execute_count for '999,999,990.99'
col trans for '999,999,990.99'
select
date_time,
sum(case WHEN stat_name='redo size' then round((e_val - b_val)/sec,2) else null end) redo_size,
sum(case WHEN stat_name='session logical reads' then round((e_val - b_val)/sec,2) else null end) sess_l_reads,
sum(case WHEN stat_name='db block changes' then round((e_val - b_val)/sec,2) else null end) blk_change,
sum(case WHEN stat_name='physical reads' then round((e_val - b_val)/sec,2) else null end) phy_reads,
sum(case WHEN stat_name='physical writes' then round((e_val - b_val)/sec,2) else null end) phy_writes,
sum(case WHEN stat_name='user calls' then round((e_val - b_val)/sec,2) else null end) user_calls,
--sum(case WHEN stat_name='parse count (total)' then round((e_val - b_val)/sec,2) else null end) parse_count_tot,
--sum(case WHEN stat_name='parse count (hard)' then round((e_val - b_val)/sec,2) else null end) parse_count_hard,
--sum(case WHEN stat_name='sorts (disk)' then round((e_val - b_val)/sec,2) else null end) sort_disk,
sum(case WHEN stat_name='logons cumulative' then round((e_val - b_val)/sec,2) else null end) logons,
sum(case WHEN stat_name='execute count' then round((e_val - b_val)/sec,2) else null end) execute_count,
round((sum(case WHEN stat_name='user commits' then (e_val - b_val)/sec else null end) +
sum(case WHEN stat_name='user rollbacks' then (e_val - b_val)/sec else null end)),2) trans
from
(
select
to_char(sn.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24_mi')|| to_char(sn.END_INTERVAL_TIME,'_hh24_mi') Date_Time,
b.stat_name stat_name,
e.value e_val,
b.value b_val,
(extract( day from (end_interval_time-begin_interval_time) )*24*60*60+
extract( hour from (end_interval_time-begin_interval_time) )*60*60+
extract( minute from (end_interval_time-begin_interval_time) )*60+
extract( second from (end_interval_time-begin_interval_time)) ) sec
FROM
dba_hist_sysstat b,
dba_hist_sysstat e,
dba_hist_snapshot sn
where
trunc(sn.begin_interval_time) ='&Date' and
b.snap_id(+) = e.snap_id-1
and e.snap_id = sn.snap_id
and b.dbid(+) = e.dbid
and e.dbid = (select dbid from v$database)
and sn.dbid = (select dbid from v$database)
and b.instance_number(+) = e.instance_number
and e.instance_number = (select instance_number from v$instance)
and sn.instance_number = (select instance_number from v$instance)
and b.instance_number(+) = e.instance_number
and b.stat_name = e.stat_name
and b.stat_name in (
'redo size',
'session logical reads',
'db block changes',
'physical reads',
'physical writes',
'user calls',
'parse count (total)',
'parse count (hard)',
'sorts (disk)',
'logons cumulative',
'execute count',
'transactions',
'user commits',
'user rollbacks'
)
)
group by date_time
Order by date_time
;
Enter value for date: 13-jan-12
old 32: trunc(sn.begin_interval_time) ='&Date' and
new 32: trunc(sn.begin_interval_time) ='13-jan-12' and
DATE_TIME REDO_SIZE SESS_L_READS BLK_CHANGE PHY_READS PHY_WRITES USER_CALLS LOGONS EXECUTE_COUNT TRANS
-------------------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ------------- ----------
01/13/12_00_00_01_00 10226.85 120.02 68.66 2.69 1.55 .07 .02 4.44 .02
01/13/12_01_00_02_00 495.21 7.27 1.36 .1 .23 .07 .02 1.07 .01
01/13/12_02_00_03_00 480.02 5.44 1.2 .03 .22 .07 .02 .88 .01
01/13/12_03_00_04_00 417.28 5.71 1.02 .02 .21 .07 .02 .88 .01
01/13/12_04_00_05_00 413.48 5.25 .99 .01 .19 .07 .02 .86 .01
01/13/12_05_00_06_00 405.08 5.32 .9 0 .2 .07 .02 .78 .01
01/13/12_06_00_07_00 406.53 5.35 .91 0 .19 .07 .02 .8 .01
01/13/12_07_00_08_00 397.37 4.84 .89 0 .18 .07 .02 .79 .01
01/13/12_08_00_09_00 458.1 5.46 1.02 0 .2 .07 .02 .79 .01
01/13/12_09_00_10_00 384.42 4.72 .85 0 .19 .06 .02 .77 .01
01/13/12_10_00_11_00 400.58 6.78 .94 .08 .18 .07 .02 .87 .01
01/13/12_11_00_12_00 434.46 6.46 1.02 .19 .2 .06 .02 .8 .01
12 rows selected.
Monday, 9 January 2012
Estimate the dumpfile size before taking Data pump export
If you want to estimate datapump file size then run following command
estimate datapump export size
datapump export file size
$ expdp "'/ as sysdba'" estimate_only=y full=Y
. estimated "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB
. estimated "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
Total estimation using BLOCKS method: 257.5 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:41:20
estimate datapump export size
datapump export file size
$ expdp "'/ as sysdba'" estimate_only=y full=Y
. estimated "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB
. estimated "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
Total estimation using BLOCKS method: 257.5 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:41:20
Thursday, 5 January 2012
Oracle Memory Allocation Per User / Process
-- Check Memory Allocation Per User/Process
Memory Allocation Per Process
Memory Allocation Per User
session wise memory
session memory
set linesize 150
SET PAGESIZE 9999
COLUMN sid_serial format a12 HEADING 'sid_serial#'
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN SERIAL# FORMAT 99999 HEADING 'SERIAL#'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY left
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a12 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
set linesize 150
SELECT
-- s.sid
s.sid||','||SERIAL# sid_serial
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,12) session_machine
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
sid_serial# Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
------------ ------------ --------- ------------------ ------------ -------------- -------------- -------------- --------------
21,2 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
23,1 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
19,5 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
22,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
11,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
10,1 oracle oracle@apt-amd-02 apt-amd-02 7 32 0 0
15,1 oracle oracle@apt-amd-02 apt-amd-02 3 4 1 2
17,10 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 2 0 1
13,1 oracle oracle@apt-amd-02 apt-amd-02 2 4 1 3
1,9 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 24 1 3
56,622 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 2 0 0
62,47 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 14 1 6
32,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
34,3 oracle oracle@apt-amd-02 apt-amd-02 1 5 1 2
40,15 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
60,443 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
30,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 1 1
27,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
16,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
14,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 1
12,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
9,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
8,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
7,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
6,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
5,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
4,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
3,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
2,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
29 rows selected.
SET LINESIZE 300 PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a35 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT /*+ PARALLEL(8) */
s.sid sid
, s.sql_id
, PREV_SQL_ID
,SQL_EXEC_ID
, lpad(s.username,20) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,20) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
Subscribe to:
Posts (Atom)
