***************************************** * Description: Data Pump Commands Simple * Compatiablity: RDBMS 11g, 12c * Date: 11:56 AM EST, 01/03/2017 ***************************************** In 11g/12c, Oracle Data Pump is RDBMS internal process. So, an Oracle direcotry is needed to be registered in database to indicates physical location. <1> Create Oracle directory in database: | |__ SQL> create directory DIR_NAME as '/physical/location'; <2> Grant privileges to Data Pump database user: | |__ SQL> grant read, write on directory DIR_NAME to user_name; <3> Data Pump login method: | |__ CMD> expdp username/password schemas=INSTALLER estimate_only=Y ........... [ Regular user ] CMD> expdp \'/ as sysdba\' schemas=INSTALLER estimate_only=Y ............. [ Linux - single quote as sysdba] CMD> expdp \"/ as sysdba\" schemas=INSTALLER estimate_only=Y ............. [ Windows - double quote as sysdba] <4> Data Pump simple: | |__ CMD> expdp \'/ as sysdba\' full=Y directory=DIR_NAME dumpfile=dump_file.dmp logfile=dump_file.log CMD> expdp \'/ as sysdba\' schemas=USER1,USER2 directory=DIR_NAME dumpfile=dump_file.dmp logfile=dump_file.log CMD> expdp \'/ as sysdba\' tables=schema.table1, schema.table2 directory=DIR_NAME dumpfile=dump_file.dmp logfile=dump_file.log CMD> expdp \'/ as sysdba\' estimate_only=Y directory=DIR_NAME logfile=EXPDP_LOG:dump_file.log --EXPDP_LOG is a different database direcotry CMD> impdp \'/ as sysdba\' schemas=USER directory=DIR_NAME dumpfile=dump_file.dmp logfile=dump_file.log CONTENT=METADATA_ONLY INCLUDE=constraint sqlfile=constraint.sql CMD> impdp \'/ as sysdba\' tables=APS.PROSTATS REMAP_SCHEMA=LAPPS:LAPP_NEW REMAP_TABLESPACE=APS_DATA:USERS directory=DATA_PUMP_BACKUP dumpfile=expdp.dmp logfile=restore.log <5> How to kill Data PUMP job status within database: | |__ SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ---------- -------------------- ---------- ---------- ------------ SYS TS2_expdp_July4 EXPORT FULL EXECUTING Method 1 - CMD> expdp micore/micore attach=SYS_EXPORT_SCHEMA_01 Method 2 - [Ctrl+C] to termiante the job directly. Export> KILL_JOB <6> Data Pump remotely via TNS entries: | |__ a. Configure TNS entry enclosed /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/network/admin/tnsnames.ora | TNS_ENTRY_NAME = | (DESCRIPTION = | (ADDRESS = (PROTOCOL = TCP)(HOST = HOST_NAME)(PORT = 1521)) | (CONNECT_DATA = | (SERVER = DEDICATED) | (SERVICE_NAME = remote_host_database_service_name.emeralit.com) | ) | ) | |__ b. Verify TNS entry can be reachable for remote database: | |__ $ tnsping emeralitprod | TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 10-JAN-2017 22:40:58 | Used TNSNAMES adapter to resolve the alias | Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ))) | OK (0 msec) | |__ c. Privileges for database direcotry: | |__ SQL> grant read, write on DIR_NAME to ; | | |__ d. Querying source database service name on Linux platform: | |__ SQL> select value from v$parameter where name like '%service_name%'; | | |__ e. Create database link within target database: | |__ SQL> create public database link DUMPTARGET using 'TNS_ENTRY_NAME'; | | |__ f. Date Pump remotely command sample via network_link parameter: |__ expdp micore/micore schemas=micore directory=DP_DUMP dumpfile=tns_expdp.dmp logfile=tns_expdp.log network_link=DUMPTARGET <7> Tablepsace needs to be created before schemas impdp: | |__ a. Finding user default tablesoace in source database: | |__ SQL> select username, default_tablespace from dba_users where username='NEBF'; | |__ b. CREATE TABLESPACE "TBS_NAME" DATAFILE 'C:\app\oracle\oradata\EMERALIT\DATAFILE\tbs.dbf' SIZE 1G AUTOEXTEND ON; Reference: https://dbatricksworld.com/how-to-kill-oracle-datapump-export-job/
Your Comments