Uncategorized

AWS – export and import using datapump utility.

June 4, 2017


Introduction:
AWS Import/Export is a service that accelerates transferring large amounts of data into and out of AWS using physical storage appliances, bypassing the Internet. AWS Import/Export supports transfers data directly onto and off of storage devices you own using the Amazon high-speed internal network.

 Refer http://docs.aws.amazon.com/AWSImportExport/latest/DG/whatisdisk.html

Scenario 1: Take export dump  on AWS database Server for some of the schemas.

Use following parfile to take export on aws database server

directory=<directory name>
dumpfile=<dumpfile name>
logfile=<logfile name>
schemas=<schema name>
compression=all

For example we are using AWSNSMP  database to take schemas export and keep the dumpfile on database server

We need to use DATA_PUMP_DIR directory for keeping export dumpfile and logfiles.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
AWSNSMP   READ WRITE

SQL> select * from dba_directories;

OWNER      DIRECTORY_NAME           DIRECTORY_PATH                 ORIGIN_CON_ID
———- ———————— —————————— ————-
SYS        BDUMP                    /rdsdbdata/log/trace                       0
SYS        ADUMP                    /rdsdbdata/log/audit                       0
SYS        OPATCH_LOG_DIR           /rdsdbbin/oracle/Opatch                   0
SYS        OPATCH_SCRIPT_DIR        /rdsdbbin/oracle/Opatch                   0
SYS        DATA_PUMP_DIR            /rdsdbdata/datapump                        0
SYS        OPATCH_INST_DIR          /rdsdbbin/oracle/OPatch                    0

6 rows selected.

1. Create Parfile and keep all parameters 

vi AWSNSMP_WNL.par

directory=DATA_DUMP_DIR
dumpfile=bkp_AWSNSMP.dpdmp
logfile=bkp_AWSNSMP.log
schemas=STG_MTS_APP,STG_MTS_V,
STG_MTS,
STG_ENTRP_ADM2,
STG_ENTRP_ADM2_V,
STG_ENTRP_ADM2_RO,
STG_ENTRP_ADM2_APP,
STG_ENTRP_ADM1_APP,
STG_ENTRP_ADM1,
STG_ENTRP_ADM1_V,
STG_ENTRP_ADM1_RO,
STG_ENTRP_V,
STG_ENTRP_RO,
STG_ENTRP,
STG_ENTRP_APP
compression=all 

2. Start export as follows.

$ expdp parfile=AWSNSMP_WNL.par

Export: Release 12.1.0.2.0 – Production on Wed Apr 26 02:28:27 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: system@AWSNSMP
Password:  

3. Monitor the export log from Database.

Findout out file name by querying  RDSADMIN.RDS_FILE_UTIL.LISTDIR package from Database.

SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;

FILENAME                                 TYPE         FILESIZE MTIME
—————————————- ———- ———- ———
bkp_AWSNSMP.dpdmp                         file         3051520  18-APR-17
bkp_AWSNSMP.log                           file         12118    18-APR-17
datapump/                                directory     4096     18-APR-17

use following package to view the import logfile.

SQL> select * from table
    (rdsadmin.rds_file_util.read_text_file(
        p_directory => ‘DATA_PUMP_DIR’,
        p_filename  => ‘datapump/bkp_AWSNSMP.log’));   2    3    4

TEXT
——————————————————————————————————————————————————————————————————–
;;;
Export: Release 12.1.0.2.0 – Production on Tue Apr 18 10:16:29 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″:  SYSTEM/********@AWSNSMP directory=DATA_PUMP_DIR dumpfile=bkp_AWSNSMP.dpdmp logfile=bkp_AWSNSMP.log schemas=STG_MTS_APP,STG_MTS_V,STG_
MTS,STG_ENTRP_ADM2,STG_ENTRP_ADM2_V,STG_ENTRP_ADM2_RO,STG_ENTRP_ADM2_APP,STG_ENTRP_ADM1_APP,STG_ENTRP_ADM1,STG_ENTRP_ADM1_V,STG_ENTRP_ADM1_RO,STG_ENTRP_V,STG_ENTRP_RO,STG_ENTRP,STG_ENTRP_APP compression=all encryption_password=********

Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 28.62 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT


. . exported “STG_ENTRP_ADM2″.”RECORD_SEQ_POSITION”           0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”REVINFO”                       0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SCHEDULED_FORM”                0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SIGNATURE”                     0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SIGNATURE_AUD”                 0 KB       0 rows
. . exported “STG_MTS”.”MTS_DEPLOYMENT”                      0 KB       0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /rdsdbdata/datapump/bkp_AWSNSMP.dpdmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Apr 18 14:16:51 2017 elapsed 0 00:00:16 

Scenario 2: Perform export on local server from AWS database.

 Connect to any local database server.

1.    Create directory to accommodate dumpfile and logfile.

SQL> select * from dba_directories where directory_name like ‘%AWS%’;

OWNER      DIRECTORY_NAME       DIRECTORY_PATH                         ORIGIN_CON_ID
———- ——————– ————————————– ————-
SYS        AWS_PUMP_DIR         /mounts/nsgpr01_exports/exp/AWS                     0

2.    Create database link.

SQL> CREATE DATABASE LINK AWSNSMP_SYSTEM_LINK CONNECT TO SYSTEM IDENTIFIED BY  <password>  USING ‘AWSNSMP’;
Database link created.

3.    Create parfile and include network link parameter and encryption option.

Vi AWSNSMP_NL.par
directory=AWS_PUMP_DIR
dumpfile=bkp_AWSNSMP.dpdmp
logfile=bkp_AWSNSMP.log
schemas=STG_MTS_APP,
STG_MTS_V,
STG_MTS,
STG_ENTRP_ADM2,
STG_ENTRP_ADM2_V,
STG_ENTRP_ADM2_RO,
STG_ENTRP_ADM2_APP,
STG_ENTRP_ADM1_APP,
STG_ENTRP_ADM1,
STG_ENTRP_ADM1_V,
STG_ENTRP_ADM1_RO,
STG_ENTRP_V,
STG_ENTRP_RO,
STG_ENTRP,
STG_ENTRP_APP
compression=all
NETWORK_LINK=AWSNSMP_SYSTEM_LINK encryption_password=*******

4.    Initiate export.

$ expdp parfile=AWSNSMP_NL.par

Export: Release 12.1.0.2.0 – Production on Wed Apr 26 03:10:15 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username:system
Password

5.    Monitor export log

$ tail -f bkp_AWSNSMP.log

;;;
Export: Release 12.1.0.2.0 – Production on Tue Apr 18 10:12:00 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  /******** AS SYSDBA parfile=AWSNSMP_NL.par
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.62 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

. . exported “STG_ENTRP_ADM2″.”REVINFO”                       0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SCHEDULED_FORM”                0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SIGNATURE”                     0 KB       0 rows
. . exported “STG_ENTRP_ADM2″.”SIGNATURE_AUD”                 0 KB       0 rows
. . exported “STG_MTS”.”MTS_DEPLOYMENT”                      0 KB       0 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /mounts/nsgpr01_exports/exp/AWS/bkp_AWSNSMP.dpdmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Apr 18 10:14:21 2017 elapsed 0 00:02:18

Scenario 3: Perform data load into AWS database using import.

1.    Create database link on local server to communicate with aws database.

SQL>CREATE DATABASE LINK AWSNSTN_SYSTEM_LINK CONNECT TO SYSTEM IDENTIFIED BY   ***** USING ‘AWSNSTN’;
Database link created.
 
2.    Transfer the dumpfile into aws database server:
Refer below link for transfer file to AWS.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

Use the following DBMS_FILE_TRANSFER.PUT_FILE to transfer file to aws Database server.
 
set timing on
set time on
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE (
source_directory_object        => ‘AWS_PUMP_DIR’,
source_file_name               => ‘expdp_NSMPAC_schema.dmp’,
destination_directory_object   => ‘DATA_PUMP_DIR’,
destination_file_name          => ‘expdp_NSMPAC_schema.dmp’,
destination_database           => ‘AWSNSTN_SYSTEM_LINK’);
END;
/

Source_directory_object  –> local database directory where your dumpfile located
Source_file_name –>      Dumpfile name
destination_directory_object –> aws database directory where you need to place the dumpfile
destination_database –> Database link

3.    Monitor transfer of files from database.

Issue following query to view the filename

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;

$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 3 01:02:25 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: SYSTEM@AWSNSTN
Enter password:

Last Successful login time: Tue May 02 2017 01:13:16 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
AWSNSTN   READ WRITE

SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;

FILENAME                                           TYPE         FILESIZE MTIME
————————————————– ———- ———- ——————
expdp_NSMPAC_schema.dmp                             file       2.0437E+10  18-APR-17
impdp_NSMPAC_schema.log                             file       164046      18-APR-17
datapump/                                          directory     4096      19-APR-17

4.    create parfile for import with parallel parameter to speed up the import process.

vi impdp_AWSNSTN_schemas.ctl
directory=DATA_PUMP_DIR dumpfile=expdp_NSMPAC_schema.dmp logfile=impdp_NSMPAC_schema.log schemas=BRMGR01 transform=oid:n parallel=16
  
5.    Start import in nohup and background

nohup impdp SYSTEM/*****@AWSNSTN parfile=impdp_AWSNSTN_schemas.ctl &

6.    Monitor import log from database.

SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR(‘DATA_PUMP_DIR’)) order by mtime;

FILENAME                                           TYPE         FILESIZE MTIME
————————————————– ———- ———- ——————
expdp_NSMPAC_schema.dmp                             file       2.0437E+10   18-APR-17
impdp_NSMPAC_schema.log                             file       164046       18-APR-17
datapump/                                          directory     4096       19-APR-17


From above results you have file names,
Use rdsadmin.rds_file_util.read_text_file to read import logfile

SQL> select * from table
    (rdsadmin.rds_file_util.read_text_file(
        p_directory => ‘DATA_PUMP_DIR’,
        p_filename  => ‘datapump/impdp_NSMPAC_schema.log’));  2    3    4

TEXT
———————————————————————————————————————————————————————————————————————————————————-
;;;
Import: Release 12.1.0.2.0 – Production on Tue Apr 18 13:07:42 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″:  SYSTEM/********@AWSNSTN parfile=impdp_AWSNSTN_schemas.ctl
Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ completed at Tue Apr 18 19:46:36 2017 elapsed 0 02:38:50

2462 rows selected.

Conclusion:
In this preceding scenario based article  we have learned how to perform export from aws databases and import into aws databases from on-premises to AWS and vice versa.

BY
Name: Omer
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com

Tags: , , , , , ,