Anuroop Challa

export/ import job monitor from data-pump (STATUS/ stop immediate)

when export/import running if you want to check status of use below procedure to check.

step1:
check JOB_NAME using dba_datapump_job.(like below ).

set lines 300;
set pages 300;
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
select * from dba_datapump_jobs;

ouput like below
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- -------------------- ----------- ----------- -----------
OPER SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA EXECUTING

step2: now you know current running job

impdp userid=usenmae/password attach=SYS_IMPORT_SCHEMA_01

(or)

impdp attach=SYS_IMPORT_SCHEMA_01 => eneter next it will ask user to connect you can connect thoutgh "/ as sysdba"

ouput like below some lines remove due to naming or safety perpous
Job: SYS_IMPORT_SCHEMA_01
Owner: OPER
Operation: IMPORT
Creator Privs: TRUE
Start Time:
Mode: SCHEMA
Instance:
Max Parallelism:
EXPORT Job Parameters:
CLIENT_COMMAND
IMPORT Job Parameters:
Parameter Name Parameter Value:
C
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File:
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema:
Object Name:
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 3,437,594
Completed Bytes: 40,101,139,632
Percent Done: 2
Worker Parallelism: 1

Import>
---------------if you want check status eneter command
Import>STATUS

---------------if you want check STOP_JOB eneter command
Import>STOP_JOB=IMMEDIATE
---------------and then it will ask you to conform if you really want to stop yes and proceed.
Are you sure you wish to stop this job ([yes]/no): yes

HELP
STATUS
CONTINUE_CLIENT
KILL_JOB
START_JOB
PARALLEL
STOP_JOB

-----------------all suggestion are welcome to receive-----------------------------

Monitor expdp/imp job script when it slow

srdc_expdp_performance.sql ==> create sql from below script and run

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

REM srdc_exp_performance.sql - Gather Information for EXPDP Performance Issues
define SRDCNAME='EXPDP_PERFORMANCE'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off verify off TRIMSPOOL on HEADING off
set lines 132 pages 10000
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/

set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===

set concat "#"
SET PAGESIZE 9999
SET LINESIZE 256
SET TRIMOUT ON
SET TRIMSPOOL ON
Column sid format 99999 heading "SESS|ID"
Column serial# format 9999999 heading "SESS|SER|#"
Column session_id format 99999 heading "SESS|ID"
Column session_serial# format 9999999 heading "SESS|SER|#"
Column event format a40
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
Column pga_used_mem format 9,999,999,999
Column pga_alloc_mem format 9,999,999,999
Column status heading 'Status' format a20
Column timeout heading 'Timeout' format 999999
Column error_number heading 'Error Number' format 999999
Column error_msg heading 'Message' format a44
Column sql_text heading 'Current SQL statement' format a44
Column Number_of_objects format 99999999
Column object_type format a35
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET MARKUP HTML ON PREFORMAT ON

--====================Retrieve sid, serial# information for the active export process(es)===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine sid, serial# details for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
set feedback on
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr and
(UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
set feedback off

--====================Retrieve sid, serial#, PGA details for the active export process(es)===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine PGA details for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
set feedback on
SELECT sid, s.serial#, p.PGA_USED_MEM,p.PGA_ALLOC_MEM
FROM v$process p, v$session s
WHERE p.addr = s.paddr and
(UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
set feedback off

--====================Retrieve the number of objects per object_type===========================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Determine the number of objects per object_type:' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
SELECT count(*) Number_of_objects, object_type
FROM dba_objects
GROUP BY object_type
ORDER BY Number_of_objects desc;

--====================Retrive all wait events and time in wait for the running export process(es)====================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'All wait events and time in wait for the active DataPump export process(es):' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
select session_id, session_serial#, Event, sum(time_waited) total_waits
from v$active_session_history
where sample_time > sysdate - 1 and
(UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
session_id in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
session_state = 'WAITING' And time_waited > 0
group by session_id, session_serial#, Event
order by session_id, session_serial#, total_waits desc;

--====================Export progress - retrieve current sql id and statement====================
SET HEADING OFF
SELECT '=================================================================================================================================' FROM dual
UNION ALL
SELECT 'Export progress - retrieve current SQL id and statement:' FROM dual
UNION ALL
SELECT '=================================================================================================================================' FROM dual;
SET HEADING ON
select sysdate, a.sid, a.sql_id, a.event, b.sql_text
from v$session a, v$sql b
where a.sql_id=b.sql_id and
(UPPER (a.program) LIKE '%DM0%' or UPPER (a.program) LIKE '%DW0%')
order by a.sid desc;

SET HEADING OFF MARKUP HTML OFF
SET SERVEROUTPUT ON FORMAT WRAP

declare
v_ksppinm varchar2(30);
CURSOR c_fix IS select v.KSPPSTVL value FROM x$ksppi n, x$ksppsv v WHERE n.indx = v.indx and n.ksppinm = v_ksppinm;
v_long_op_flag number := 0 ;
v_target varchar2(100);
v_sid number;
v_totalwork number;
v_opname varchar2(200);
v_sofar number;
v_time_remain number;
stmt varchar2(2000);
v_fix c_fix%ROWTYPE;

begin
stmt:='select count(*) from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '||
'''%DM0%'''||' or UPPER (program) LIKE '||'''%DW0%'')'||' and totalwork <> sofar';
DBMS_OUTPUT.PUT_LINE ('<pre>');
dbms_output.put_line ('=================================================================================================================================');
dbms_output.put_line ('Check v$session_longops - Export pending work');
dbms_output.put_line ('=================================================================================================================================');
execute immediate stmt into v_long_op_flag;
if (v_long_op_flag > 0) then
dbms_output.put_line ('The number of long running export processes is: '|| v_long_op_flag);
dbms_output.put_line (chr (10));
for longop in (select sid,target,opname, sum(totalwork) totwork, sum(sofar) sofar, sum(totalwork-sofar) blk_remain, Round(sum(time_remaining/60),2) time_remain
from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and
opname NOT LIKE '%aggregate%' and totalwork <> sofar group by sid,target,opname) loop
dbms_output.put_line (Rpad ('Export SID', 40, ' ')||chr (9)||':'||chr (9)||longop.sid);
dbms_output.put_line (Rpad ('Object being read', 40, ' ')||chr (9)||':'||chr (9)||longop.target);
dbms_output.put_line (Rpad ('Operation being executed', 40, ' ')||chr (9)||':'||chr (9)||longop.opname);
dbms_output.put_line (Rpad ('Total blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.totwork);
dbms_output.put_line (Rpad ('Total blocks already read', 40, ' ')||chr (9)||':'||chr (9)||longop.sofar);
dbms_output.put_line (Rpad ('Remaining blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.blk_remain);
dbms_output.put_line (Rpad ('Estimated time remaining for the process', 40, ' ')||chr (9)||':'||chr (9)||longop.time_remain|| ' Minutes');
dbms_output.put_line (chr (10));
end Loop;
else
DBMS_OUTPUT.PUT_LINE ('No export session is found in v$session_longops');
dbms_output.put_line (chr (10));
end If;

for i in 1..6 loop
if i = 1 then
v_ksppinm := 'fixed_date';
elsif i = 2 then
v_ksppinm := 'aq_tm_processes';
elsif i = 3 then
v_ksppinm := 'compatible';
elsif i = 4 then
v_ksppinm := 'optimizer_features_enable';
elsif i = 5 then
v_ksppinm := 'optimizer_index_caching';
elsif i = 6 then
v_ksppinm := 'optimizer_index_cost_adj';
end if;

dbms_output.put_line ('=================================================================================================================================');
DBMS_OUTPUT.PUT_LINE ('Is the '||upper (v_ksppinm)||' parameter set?');
dbms_output.put_line ('=================================================================================================================================');

open c_fix;
fetch c_fix into v_fix;
close c_fix;
if nvl (to_char (v_fix.value), '1') = to_char ('1') then
DBMS_OUTPUT.PUT_LINE ('No value is found for '||upper (v_ksppinm)||' parameter.');
else
DBMS_OUTPUT.PUT_LINE ('The '||upper (v_ksppinm)||' parameter is set for this database and the value is: '||v_fix.value);
end if;
dbms_output.put_line('=================================================================================================================================');
dbms_output.put_line (chr (10));
end loop;
DBMS_OUTPUT.PUT_LINE('</pre>');
end;
/

spool off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm

exit

=======================================

-- note :- this i get from vendor when have some fallow up

data pump export size estimate script

Data pump export size estimate script

following script will provide you data in each schema on your database(MB).
you can change if want GB as per need.
Hope with the help of this script you will get aprox size of export backup.
-----------------------------------------------------------------------------------------------------------------------
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM
(
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
--AND TABLESPACE_NAME LIKE 'COSTE%'
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%'
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW'
--AND ROUND(bytes/(1024*1024),2) > 1000)
)
GROUP BY owner
ORDER BY MBytes DESC;

---------------------------------------
any suggestion are well come, you can comment if you have better script in botom plz.

Data pump orphan jobs cleanup.

when export/import job fail time it exit from procees level but still not in dabase.
But it does not effect your next job job or current job it give another job name like _02 runs.
only impact is still it occupies space in db level so better check and clean up orphan jobs from database to free up system table space.

1.For now we don’t have export job is running but as checked found oracle orphaned datapump job , in other database not found like this
SQL>SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

-- output for refrence
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
SYS SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

select count(*) from sys.SYS_EXPORT_FULL_01
8535

2. So, we have to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS by drop:

SQL> DROP TABLE SYS.SYS_EXPORT_FULL_01; ==> make sure current table-name.

select sum(bytes/1024/1024) from dba_segments where segment_name = 'SYS_EXPORT_FULL_01' and owner = 'SYS'
3MB

select owner,created,object_name,last_ddl_time from dba_objects where object_name = 'SYS_EXPORT_FULL_01'
OWNER CREATED OBJECT_NAME LAST_DDL_TIME
SYS 12/23/2015 17:13 SYS_EXPORT_FULL_01 12/23/2015 17:13

SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
VALID 26326 TABLE SYS.SYS_EXPORT_FULL_01

LISTNER STUFF commands (lsnrctl)

=======================
LISTNER commands (lsnrctl)
========================

First try to use help for all commands

$ lsnrctl

-------------some un wanted lines skip-- to minimize size--

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start stop status services
version reload save_config trace
spawn quit exit set*
show*

LSNRCTL>

--let`s try one by one (direct os/listenr cmd level)

start

$ lsnrctl start [listener_name]

stop

$ lsnrctl stop [listener_name]

status

$ lsnrctl status [listener_name]

services

$ lsnrctl services [listener_name]

reload

$ lsnrctl reload [listener_name]

--listenr cmd level i will show one example all similar just skip lsnrctl

start

LSNRCTL>start [listener_name]

---------cmd level more you can do with commands show/set

LSNRCTL> show

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

rawmode displaymode

rules trc_file

trc_directory trc_level

log_file log_directory

log_status current_listener

inbound_connect_timeout startup_waittime

snmp_visible save_config_on_stop

dynamic_registration enable_global_dynamic_endpoint

oracle_home pid

connection_rate_limit valid_node_checking_registration

registration_invited_nodes registration_excluded_nodes

remote_registration_address

LSNRCTL> show [above_modifier name]

========================

LSNRCTL> set

The following operations are available after set

An asterisk (*) denotes a modifier or extended command:

rawmode displaymode

trc_file trc_directory

trc_level log_file

log_directory log_status

current_listener inbound_connect_timeout

startup_waittime save_config_on_stop

dynamic_registration enable_global_dynamic_endpoint

connection_rate_limit valid_node_checking_registration

registration_invited_nodes registration_excluded_nodes

----------if you want to set use like below

LSNRCTL> set [above_modifier name ]

Check and move indexes to Index tablespace.

========================================================================
Check and move indexes to Index tablespace.(please check change tablespace name in script data and index)
========================================================================
SELECT 'ALTER INDEX Schema_Name.' ||index_name|| ' REBUILD TABLESPACE Tablescpae_index;'
FROM dba_indexes WHERE owner = 'Schema_Name'
AND TEMPORARY='N' AND tablespace_name = 'Tablescpae_DATA';

NOLOGGING to LOGGING

======================================================================
Check tables as in NOLOGGING
======================================================================
SELECT LOGGING,COUNT(*) FROM dba_tables WHERE owner = 'Schema_Name' AND TEMPORARY = 'N' GROUP
BY LOGGING ORDER BY 2 DESC;

======================================================================
If found tables in NOLOGGING the move change to LOGGING from the script.
======================================================================
SELECT 'ALTER TABLE Schema_Name.'||table_name|| ' LOGGING;'
FROM dba_tables WHERE LOGGING='NO' AND owner = 'Schema_Name' AND TEMPORARY = 'N';

======================================================================
Check indexes as in NOLOGGING
---------------------------------------
SELECT LOGGING,COUNT(*) FROM dba_indexes WHERE owner = 'Schema_Name' AND TEMPORARY = 'N' GROUP
BY LOGGING ORDER BY 2 DESC;

========================================================================
If found indexes in NOLOGGING the move change to LOGGING from the script.
========================================================================
SELECT 'ALTER INDEX Schema_Name.'||index_name|| ' LOGGING;'
FROM dba_indexes WHERE LOGGING='NO' AND owner = 'Schema_Name' AND TEMPORARY = 'N' ;

========================================================================