To run Expdp and impdp command in background of operating system.
1)
create directory DCMCNVP6_SCHEMA_EXPORT as 'H:\Migration_Export\';
grant read , write on DCMCNVP6_SCHEMA_EXPORT to public;
2) expdp "'/ as sysdba'" DIRECTORY=DCMCNVP6_SCHEMA_EXPORT dumpfile=DCMCNVP6_schema_06_Mar_2012.dmp logfile=DCMCNVP6_schema_06_Mar_2012_EXP.log SCHEMAS=MCN_EDM_RPT,mcneil_edm_prd
or
1) Import the Test1 schema1 into Test1schema2 schema
note : dumpfile parameter will be changed accordingly once expdp is completed.
dumpfile = expdp_P1DB0059_Test1schema1_AUD_%U.dmp
logfile = impdp_S1DB0059_P1DB0059_Test1schema1_AUD.log
directory = dpump_dir
version=11.1.0.7
parallel = 4
remap_schema=Test1schema1_AUD:Test1schema1_A
1. Extract the expdp.zip file into the /u022/backuploc/exp on TestServer server
2. set ORACLE_SID to backuploc, ORACLE_HOME to /u01/app/oracle/product/10.2.0.4_2
3. $cd /u022/backuploc/exp
4. $expdp system/<pwd> parfile=expdp_1.par
5. $expdp system/<pwd> parfile=expdp_2.par
6. $expdp system/<pwd> parfile=expdp_3.par
7. $expdp system/<pwd> parfile=expdp_4.par
8. Copy the 4 dump files to a location in TestServer2 server ( /u014/backuploc/exp )
set ORACLE_SID to backuploc(on server TestServer2), ORACLE_HOME, PATH = $PATH:$ORACLE_HOME/bin appropriately
9. Extract the impdp.zip file into a location ( /u014/backuploc/exp ) in TestServer2 server
10. cd /u014/backuploc/exp
11. Login as sys
12. $impdp system/<pwd> parfile=impdp_1.par
13. $impdp system/<pwd> parfile=impdp_2.par
14. $impdp system/<pwd> parfile=impdp_3.par
15. $impdp system/<pwd> parfile=impdp_4.par
nohup impdp system/<pwd> parfile=impdp_2.par &
or
[oracle@TestServer ~]$ nohup impdp \"/ as sysdba\" directory=PUMP dumpfile=FULL_EXPORT.dmp logfile=FULL_IMPORT.log &
[1] 9310
[oracle@TestServer ~]$ nohup: ignoring input and appending output to `nohup.out'
or
imp pain FROMUSER=WMC TOUSER=WMC FILE=td46562_dev_exp.dmp log=dev_td46562_imp.log feedback=10000 ignore=Y
create or replace directory dpump_refresh as '/oraback/backuploc/export/db_loc;
grant read,write on directory dpump_refresh to public;
nohup expdp \'/ as sysdba \' directory=DUMP_SN_NEW schemas=testschema dumpfile=STDB0001_25OCT2012.dmp logfile=STDB0001_25OCT2012.log &
expdp system directory=EXP_DP2 dumpfile=test_23feb_1.dmp,test_23feb_2.dmp logfile =test_23feb.log schemas =eeevs01 job_name =test_23feb_12_new3
Import has started with nohup and we check import process from database.
To Monitor Datapump Jobs
SET LINESIZE 150
COLUMN owner_name FORMAT A20
COLUMN job_name FORMAT A30
COLUMN operation FORMAT A10
COLUMN job_mode FORMAT A10
COLUMN state FORMAT A12
SELECT owner_name,
job_name,
TRIM(operation) AS operation,
TRIM(job_mode) AS job_mode,
state,
degree,
attached_sessions,
datapump_sessions
FROM dba_datapump_jobs
ORDER BY 1, 2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------- ------------------------------ ---------- ---------- ------------ ---------- ----------------- -----------------
SYSTEM SYS_IMPORT_01 IMPORT TABLE EXECUTING 1 0 2
SYSTEM SYS_IMPORT_02 IMPORT TABLE EXECUTING 1 0 2
SYSTEM SYS_IMPORT_03 IMPORT TABLE EXECUTING 1 0 2
SYSTEM SYS_IMPORT_04 IMPORT TABLE NOT RUNNING 0 0 0
SYSTEM SYS_IMPORT_05 IMPORT TABLE NOT RUNNING 0 0 0
SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS;
OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE
-------------------- ------------------------------ ---------- ---------------- --------------
SYSTEM SYS_IMPORT_02 1 07000100B4B7AC88 MASTER
SYSTEM SYS_IMPORT_06 1 07000100B4A6C6B8 MASTER
SYSTEM SYS_IMPORT_03 1 07000100B4EF2AE0 MASTER
SYSTEM SYS_IMPORT_07 1 07000100B54A6068 DBMS_DATAPUMP
SYSTEM SYS_IMPORT_03 1 07000100ACDA58F0 WORKER
SYSTEM SYS_IMPORT_02 1 07000100AC807B80 WORKER
SYSTEM SYS_IMPORT_07 1 07000100ACFF05E8 MASTER
SYSTEM SYS_IMPORT_01 1 07000100B4B84000 MASTER
SYSTEM SYS_IMPORT_01 1 07000100B4C9EA70 WORKER
SYSTEM SYS_IMPORT_06 1 07000100B4DD4F48 WORKER
SYSTEM SYS_IMPORT_07 1 07000100B54A9190 WORKER
To datapump job
There are several method to kill the running datapump job in oracle.
1. When the datapump (expdp or impdp) job is still running :-
execute control+c , it will show you export or import prompt.
Export>
then execute kill_job on the prompt it will ask for the conformation to kill the job or not.
example :-
Export >kill_job
Are you sure you wish to stop this job ([yes]/no): yes
2. If by mistake you are out of import or export prompt then
a) Get the name of the job using
SET LINESIZE 150
COLUMN owner_name FORMAT A20
COLUMN job_name FORMAT A30
COLUMN operation FORMAT A10
COLUMN job_mode FORMAT A10
COLUMN state FORMAT A12
SELECT owner_name,
job_name,
TRIM(operation) AS operation,
TRIM(job_mode) AS job_mode,
state,
degree,
attached_sessions,
datapump_sessions
FROM dba_datapump_jobs
ORDER BY 1, 2;
b) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job (Get the name_of_the_job using the above query)
eg:
TestServer:TestDB:/home/oracle $impdp SYSTEM/abcTestDB@TestDB attach=SYS_IMPORT_TABLE_01
Import: Release 19.0.0.0.0 - Production on Thu Aug 6 13:25:23 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job: SYS_IMPORT_TABLE_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: TRUE
GUID: AC3482F9D4C503C4E0530AA02037A11B
Start Time: Thursday, 06 August, 2020 12:08:44
Mode: TABLE
Remote link: TargetDB.world
Instance: TestDB
Max Parallelism: 1
Timezone: +00:00
Export timezone: +01:00
Timezone version: 32
Export timezone version: 14
Endianness: BIG
NLS character set: WE8ISO8859P1
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/********@TestDB parfile=/exports/248.par
INCLUDE_METADATA 0
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Job heartbeat: 78
Worker 1 Status:
Instance ID: 1
Instance name: TestDB
Host name: TestServer
Object start time: Thursday, 06 August, 2020 12:08:46
Object status at: Thursday, 06 August, 2020 12:08:47
Process Name: DW05
State: EXECUTING
Object Schema: TEST_DBA
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1
Import> exit
c) Once you are attached to job, Type Kill_Job
Ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
To check Session Detail
SELECT OPNAME,
SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM
V$SESSION_LONGOPS
WHERE
OPNAME in (
select
d.job_name from v$session s,v$process p,
dba_datapump_sessions d where
p.addr=s.paddr
and
s.saddr=d.saddr)
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
SQL> select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete" from v$session_longops where sofar <> totalwork;
SID SERIAL# CONTEXT SOFAR TOTALWORK %_complete
---------- ---------- ---------- ---------- ---------- ----------
573 49339 0 0 2031 0
1046 39171 1236752 116 349 33.24
1333 48709 0 0 319 0
SQL>
SQL> SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10
SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;SQL> SQL> SQL> SQL> SQL> 2
OWNER_NAME JOB_NAME OPERATION
---------- -------------------- ----------
SYSTEM SYS_IMPORT_TABLE_04 IMPORT
To Monitor impdp status
--------------------------
SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;
select name, sql_text, error_msg from dba_resumable;
SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;
SELECT
OPNAME,
SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM
V$SESSION_LONGOPS
WHERE
OPNAME in
(
select
d.job_name
from
v$session s,
v$process p,
dba_datapump_sessions d
where
p.addr=s.paddr
and
s.saddr=d.saddr
)
AND
OPNAME NOT LIKE '%aggregate%'
AND
TOTALWORK != 0
AND
SOFAR <> TOTALWORK;Monitor undo tablespace during impdp
COL TABLESPACE_NAME FORMAT A40
col "Tablespace" for a50
col "USED_IN_MB" for 9999999
col "FREE_IN_MB" for 99999
select
a.TABLESPACE_NAME,
a.TOTAL_IN_MB,
to_char(nvl((b.used),0.00),'999990.99') USED_IN_MB,
to_char(nvl((c.free),0.00),'999990.99') FREE_IN_MB,
to_char(nvl(((b.used/a.TOTAL_IN_MB)*100),0.00),'99990.99') PCT_USED
from
(select TABLESPACE_NAME,
to_char(sum(bytes)/(1024*1024),'9999990.99') TOTAL_IN_MB
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,bytes/(1024*1024) used
from sys.SM$TS_USED) b,
(select TABLESPACE_NAME,bytes/(1024*1024) free
from sys.SM$TS_free) c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and
a.TABLESPACE_NAME=c.tablespace_name(+)
alter database datafile '/ora/intl2prd/001/oradata/intl2prd/undo_01.dbf' resize 13000m;
How can I cleanup old (orphaned) datapump jobs in DBA_DATAPUMP_JOBS ?
Cause: In many cases you have stop Oracle data pump jobs, shutdown database during export/import or use undocumented parameter KEEP_MASTER=Y. In these cases the master table remains in the database and it’s better to delete them.
Error often raised by expdp:
ORA-31626: job does not exist
ORA-31633: unable to create master table « SYS.SYS_EXPORT_FULL_01 »
ORA-06512: at « SYS.DBMS_SYS_ERROR », line 95
ORA-06512: at « SYS.KUPV$FT », line 1163
ORA-00955: name is already used by an existing object
ORA-06512: at « SYS.KUPV$FT », line 1056
ORA-06512: at « SYS.KUPV$FT », line 1044
Below is a step-by-step instruction on how to resolve it.
Step 1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary
Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:
SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10
SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;
Step 2: Drop the master tables
set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;
Execute the generated script.
Step 3: Identify orphan DataPump external tables
Check and drop external tables created for datapump jobs with select object_name, created from dba_objects where object_name like ‘ET$%’
Step 4: Purge recycle bin
If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘ »‘ || job_name || ‘ »;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;
Step 5: Confirm that the job has been removed
Run sql statement from step 1.
No comments:
Post a Comment