STEPS FOR REFRESH OR DATAPUMP

Database exports are common task been asked of every Oracle database admin. Close your eyes and imagine a table created in 2015. At the time of creation, asssume it was say 500 GB in size in development (DEV), it was pushed to TEST database and then PROD. 2 years later, the table is now 8TB large. This means we have 7.5 TB of data on the PROD table. The developers and QA testers need this data in DEV and TEST regions respectively. The only way to get this data across is via refresh/datapump as described below;

NB: step 1 to 5 is done only once per database. unless you want to use multiple users for exports. Ideally, most organizations use 1 user for all exports for security reasons

1. Create datapump user

SQL> create user exp_imp_user identified by 1234 account unlock;

User created.
SQL> grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to exp_imp_user;

Grant succeeded.

SQL> grant dba to exp_imp_user; (for class purposes only)

Grant succeeded.

2. create dump directory which will host dumpfile created after export. (dumpfile(.dmp) is the logical version of the Rman backup (.bkp file) which is the physical backup)

mkdir -P /u01/app/oracle/my_dump_dir

3.Create directory object (its masks the dump directory)

create directory expdp_impdp_dir as ‘/u01/app/oracle/my_dump_dir’;

–you can see all existing directory objects using the syntax below
SELECT owner, directory_name, directory_path FROM all_directories;

4.grant read and write on directory to datapump user as below

grant read,write on directory expdp_impdp_dir to exp_imp_user;

5. This step isn’t required if you have the table and data (This is just to test)

SQL> create table abesong_emp as select * from scott.emp;

SQL> create table abesong_dept as select * from scott.dept;

6. We are now ready to export from production database

–To estimate the size of the dumpfile especially during a refresh, you want to ensure the mount point has enough space to host the dumpfile
expdp exp_imp_user/1234 tables=COUNTRIES, abesong_emp, abesong_dept DIRECTORY=expdp_impdp_dir ESTIMATE_ONLY=Y LOGFILE=TABLES.log (This will estimate the size of dumpfile)

–exporting tables
expdp exp_imp_user/1234 tables=COUNTRIES, ABESONG_DEPT, ABESONG_EMP DIRECTORY=expdp_impdp_dir DUMPFILE=TABLES.dmP LOGFILE=TABLES.log

or
–you can export multiple tables. Just seperate them with a comma
expdp dumpfile=xxx.dmp logfile=xxx.log directory=expdp_impdp_dir tables=schemaname.tablename

You will be prompted for username, use sysdba if you choose to or use your datapump user;
username: / as sysdba

–exporting schema expdp exp_imp_user/1234 schemas=A DIRECTORY=expdp_impdp_dir DUMPFILE=TABLES.dmP LOGFILE=TABLES.log

7. After export, we can now import into the development and test databases

impdp exp_imp_user/1234 tables=COUNTRIES, ABESONG_DEPT, ABESONG_EMP DIRECTORY=expdp_impdp_dir DUMPFILE=TABLES.dmP LOGFILE=TABLES.log

–if you want to be prompted for username and pasword use below
impdp tables=COUNTRIES, ABESONG_DEPT, ABESONG_EMP DIRECTORY=expdp_impdp_dir DUMPFILE=TABLES.dmP LOGFILE=TABLES.log

(you will be prompted for the username and password)

You can choose to run the export as sysdba depending on buisness sla, just enter (/ as sysdba) when asked for username as below;

username: / as sysdba

few tips;

1. use %u to split dumpfile in case the dumpfile is too large

2. Enable paralellism by setting e.g setting parallelism to 4;

parallel = 4

Paralellism enables export jobs run faster

3. we can also set compression of dumpfile to either; all, data-only etc

compression = all

4. we can also use include, exclude, append parameters in the export job

5. content=METADATA_ONLY or DATA_ONLY

by Anthony Besong

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s