Oracle Data Pump

Oracle Data Pump

 PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

 

ORA-31633: unable to create master table.

You can check the  running Job:

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;

 

You can see the master table.

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;

 

drop table SYS.EXPDP_JOB;

STEPS:

1.  Create or change a directory for the expdp

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/fast_recovery_area/dpump_DB';

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;

 

Directory location.

col DIRECTORY_PATH format a43;
col OWNER format a10;
col DIRECTORY_NAME format a20;
set linesize 200;

select * from dba_directories where directory_name='DATA_PUMP_DIR';

Export FULL DB:

. /users/oracle/set_home_env KARDBTES

expdp "'"/ as sysdba"'" directory=DATA_PUMP_DIR logfile=dexp_ISMDB.log JOB_NAME= dexp_KARDBTES DUMPFILE=KARDBTES_%U.dmp FULL=YES

Import FULL DB:

impdp "'"/ as sysdba"'" DUMPFILE=kardbtes_%U.dmp logfile=imp_FULLDB_KARDBTES.log directory=DATA_PUMP_DIR PARALLEL=4 full=y

2.   Export TABLE:

expdp system/xxxxx directory=DATA_PUMP_DIR dumpfile=table_data_%U.dmp tables=KARDBTES.MIGTAB01 content=data_only logfile=export_to_fix_.log

 

3.   Import TABLES:

cd /oracledb/datapump

impdp system/xxxx directory=DATA_PUMP_DIR dumpfile=table_data_%u.dmp tables=KARDBTES.MIGTAB01 , KARDBTES.MIGTAB02 logfile=import_to_fix.log parallel=4

 Export Schema: 

#!/bin/bash

. /users/oracle/set_home_env KARDBTES

expdp "'"/ as sysdba"'" schemas=KARDBTES01 directory=DATA_PUMP_DIR logfile=exp_KARDBTES01_`date +%b_%d_%H_%M_%S.`log JOB_NAME=expdp_KARDBTES01 DUMPFILE=KARDBTES01_%U_`date +%b_%d_%H_%M_%S.`dmp

Or:

4.  Create a script to export multiple schemas.

vi datapump_exp_schema.sh

. /users/oracle/set_home_env KARDBTES

expdp "'"/ as sysdba"'" schemas=KARDBTES01, KARDBTES02 directory=DATA_PUMP_DIR parallel=5 logfile=dexp_KARDBTES .log JOB_NAME= dexp_KARDBTES DUMPFILE=kardbtes_%U.dmp

Execute the script as follow.

nohup /oracledb/datapump/datapump_exp_schema.sh >/oracledb/datapump/datapump_exp_schema.log &

 

Import data into another DB with same Schemaname.

vi datapump_imp_schema.sh

. /users/oracle/setdb_home KARDBTES

impdp "'"/ as sysdba"'" schemas= KARDBTES01, KARDBTES02 directory=DATA_PUMP_DIR parallel=5 logfile=dimp_ KARDBTES.log JOB_NAME= dimp_KARDBTES DUMPFILE= KARDBTES _%U.dmp TRANSFORM=oid:n

#Execute JOB es follow: 

nohup /oracledb/datapump/datapump_imp_schema.sh >/oracledb/datapump / datapump_imp_schema.log &

Import as different Schemaname 

vi datapump_imp_schema.sh

#!/bin/bash

. /users/oracle/setdb_home KARDBTES

impdp \'/ as sysdba\' schemas=KARDB_DEV REMAP_SCHEMA=KARDB_DEV:KARDB_TST exclude=statistics directory=data_pump_dir LOGFILE=SANC_DEV_imp.$(date +%d%m%y-%H%M).log JOB_NAME=imp_kardb_job DUMPFILE=KARDB_DEV01.210624-1109.dmp

 

 

5.  At the end, do execute the following command. It depend whether you just exporting table or schema

sqlplus /

SQL> exec dbms_stats.gather_table_stats (ownname  => 'SCHEMA_NAME', tabname => 'TABLE_NAME' , estimate_percent => 5, degree => 8, cascade => true);

SQL> alter system flush shared_pool;

 

6.  If you want to just export and import Schema structure without data. Create a script as follow. (no_date)

more expdb_full_schema_description.sh

 

#!/bin/bash

. set_home_env KARDBTES

echo $ORACLE_SID

date

YYYYMMDD=`date +%Y%m%d`

expdp "'"/ as sysdba"'" content=METADATA_ONLY \

schemas=schema1,schema2,schema3 \

directory=DATA_PUMP_DIR reuse_dumpfiles=y \

dumpfile=kardbtes_description_$YYYYMMDD.dmp \ logfile=kardbtes_description_$YYYYMMDD.log

date

7.       You can import as schema as follow.

You may nee to create table and index befor you import.

example:

 Tablespace: 

CREATE TABLESPACE DATA
DATAFILE '+DATA01' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
DEFAULT
NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO 

FLASHBACK ON;

Index

CREATE TABLESPACE INDX
DATAFILE '+DATA01' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
DEFAULT
NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO 

FLASHBACK ON;

 

#!/bin/bash

. /users/oracle/set_home_env KARDBTES

echo $ORACLE_SID

date

YYYYMMDD=`date +%Y%m%d`

impdp "'"/ as sysdba"'"  content=metadata_only \

schemas=schema1,schema2,schema3 \

exclude=statistics \

logfile=kurs1_nodata_MDV_MASTER_impdp_$YYYYMMDD.log \ JOB_NAME=impdp_nodataKARDBTES DUMPFILE=KARDBTES_description_schema_20210319.dmp

date

 

Exclude some tables expdp (export complete schema except some tables):

 

#!/bin/bash

. /users/oracle/set_home_env KARDBTES

expdp "'"/ as sysdba"'"  schemas=schemaname directory=DATA_PUMP_DIR \ logfile=exp_schema.log PARALLEL=7 JOB_NAME=expdp_exclude \ DUMPFILE=schema_name_%U.dmp \

EXCLUDE=TABLE:\"IN\(\'table1\',\'table2\',\'table3\',\'table4\'\)\"

 

Exclude some tables imp(Import complete schema except some tables):

#!/bin/bash

. /users/oracle/set_home_env KARDBTES

impdp "'"/ as sysdba"'"  schemas=schemaname directory=DATA_PUMP_DIR \ logfile=imp_schema.log PARALLEL=7 JOB_NAME=impdp_exclude \ DUMPFILE=schema_name_%U.dmp \

EXCLUDE=TABLE:\"IN\(\'table1\',\'table2\',\'table3\',\'table4\'\)\" \ TABLE_EXISTS_ACTION=REPLACE