Datapump interview questions to expect

Data pump interview questions helps dba aspirant clear interview with ease. Datapump is the oracle database utility used for export, import of data, database refresh to name a few. No dba interview ends without datapump interview questions. Here are some common questions that you can definitely expect during a datapump interview:
1) What are all the many different command-line parameters that can be used with datapump?
List of command-line parameters used with Datapump export expdp isgiven below:
ATTACH
COMPRESSION
CONTENT
DATA_OPTIONS
DIRCTORY
DUMPFILE
ENCRYPTION
ENCRYPTION_ALGORITHM
ENCRYPTION_MODE
ENCRYPTION_PASSWORD
ESTIMATE
ESTIMATE_ONLY
EXCLUDE
FILESIZE
FLASHACK_SCN
FLASHBACK_TIME
FULL
HELP
INCLUDE
JOB_NAME
LOGFILE
NETWORK_LINK
NOLOGFILE
PARALLEL
PARFILE
QUERY
REMAP_DATA
REUSE_DUMPFILES
SAMPLE
SCHEMAS
STATUS
TABLES
TABLESPACES
TRANSPORT_FULL_CHECK
TRANSPORT_TABLESPACES
TRANSPORTABLE
VERSION
2) What happens when legacy commands are used with datapump?
exp/imp tools are not totally unsupported in 10g and hence datapump will enter legacy mode automatically whenever we use exp/imp related commands with expdp/impdp commandline. We get the information and it is advisable to change the parameters to fit expdp and impdp parameters
D:>expdp file=test.txt sys

Export:

Username: sys@sid as sysdba
Password:
———————————————————————————
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “file=test.txt,system” Location: Command Line, Replaced w
ith: “dumpfile=test.txt,sys”
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: system/********@sid AS SYSDBA dumpfile=
test.txt,sys reuse_dumpfiles=true nologfile=true
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
D:ORACLEORA11RDBMSLOGTEST.TXT
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 15:54:54
3) Does datapump address corruption of tables and indexes?
Whenever there is a corruption in the table or an index the handy tool dbms_repair.fix_corrupt_blocks can be run against the object to fix corruption. In real-time scenario this is not found to address corruption in many instances. To address this there is a simple solution. Performing simple export/import of table/schema/database (full) internally addresses the corruption in table structures. This process is called as database refresh.To confirm the the issue has been fully addressed and table is corruption free, run the analyze command against the table
analyze table tablename validate structure cascade;
The command will return no rows ensuring that corruption has been fully fixed
As a DBA we need to be pro-active if database refresh is to be used to fix corruption. Note that this invovles downtime and may not be best fit for databases that are large in size. If there is a valid full backup restore and recovery may be less time consuming in some cases. There are scenarios where BLOCK RECOVER will perform the recovery on the fly in live production environment with zero downtime. This is an enterprise feature that can be availed from RMAN prompt. Best judgement is needed in corruption fix scenarios. However, database refresh will ensure that the DB is 100% free of corruption as the migration is to happen onto fresh disks
4) Give details on add_file usage clause of datapump:-
Basics of oracle datapump is the creation of logical backup of entire database or subset of database within a directory object which is ideally a folder location at operating system level. At times physical drives in a database server may not be sufficient to accommodate all the information in the dumpfile.
We can choose to create the dumpfiles in one of the two different ways
1) Create a second directory object create additional dumpfile here- Make use of add_Files datapump option
ADD_FILES=newdirectoryobject:new.dmp
2) Create a second dumpfile in same directory object in case the current dumpfile has reached maximum limit
ADD_FILES=new.dmp
Usually we need to perform these commands from export datapump command prompt expdp
5) What is the benefit of datapump?
The older export/import technology were client-based. The Data Pump technology is purely server-based. All dump, log and other files are created on the server by default.Data Pump technology offers several benefits over the traditional export and import data utilities . Few benefits are given below :
1) Improved Performance
2) Ability to restart jobs
3) Parallel execution capabilities
4) Ability to attach to running jobs
5) Ability to estimate space requirements
6) Network mode of operation
7) Fine-grained data import capability
8) Remapping capabilities
6) Give details on datapump include and exclude parameter:-
Data Pump INCLUDE And Exclude Parameters are two mutually exclusive parameters.We can use them to perform metadata filtering..Metadata filtering enables us to selectively leave out or include certain type of objects during a data pump export or import job.Since 9i, in the old export and import utility we were using GRANTS, CONSTRAINTS, INDEXES parameters to specify whether we wanted to export/import those objects.INCLUDE and EXCLUDE can be used for :
1) INDEXES
2) GRANTS
3) CONSTRAINTS
4) PACKAGES
EXCLUDE Parameter – omit specific database object types from an export or import operation
INCLUDE Parameter – helps us include a specific set of objects
Usage :
EXCLUDE=object_type[:name_clause]
INCLUDE=object_type[:name_clause]
name_clause is optional
6) What is the role of datapump in upgrade?
We can make use of datapump to perform indirect upgrade of an oracle database. Say, you want to test an upgrade, create a test version that is 100% in-sync with production after major prod release or patch release. In all these cases datapump comes handy.
Coming to the latest oracle 12c, this needs oracle 10g minimum for direct upgrade. If the database is at a version lower than 10g, upgrade it to 10g, use datapump create a dump, install oracel 12c software, perform import using impdp
7) What is the database role needed to perform full database export?
Role EXP_FULL_DATABASE is needed for full export. Normally, SYSTEM user is the recommended user for performing datapump export and import. SYSTEM schema is the user that has been granted DBA role and can be used directly without any explicit grant access
8) Which option should be set during export for consistency purpose?
expdp option FLASHBACK_TIME=SYSTIMESTAMP should be set. this comes at the expense of increased undo requirements and size your undo properly to avoid errors during export
9) How would you improve the speed of datapump export?
It is to be noted that datapump can start the logical backup with consistency. However, incremental transactions that do happen after the export starts are still considered equivalent to downtime. As such this process should be fast to minimize downtime. Here are the real-time steps a DBA can implement while performing export using expdp
9.1) Set parallel parameter to value twice that of number of CPU’s. In a Linux environment details on number of CPU’s can be obtained as follows:
nproc
cat /proc/cpuinfo
lscpu
In case of solaris environment make use of the commands:
prtdiag
psrinfo -p
Once you get the detail set PARALLEL=2x value from above commands
9.2) Always EXCLUDE STATISTICS during export and include METRICS
EXCLUDE=STATISTICS
METRICS=YES
10) Can database link be used as a parameter with datapump?
Yes. NETWORK_LINK parameter can be used during import using impdp. It is interesting to note that no export is needed. Rather, remote import via database link is possible with NETWORK_LINK impdp parameter. As this is not a normal process there will be no dumpfile, no disk space needed. However there is a restriction on usage of this parameter with datatypes like LONG, LONG RAW.
11) Is it always advantageous to make use of NETWORK_LINK?
This parameter saves disk space, saves I/O transfer. But this comes at the expense of network bandwidth, CPU load in target database. This is a CPU intensive task
12) What is unique about views_As_tables option in datapump 12c?
Oracle datapump 12c comes with a new expdp option called views_As_Tables. It is used as follows:
expdp viws_As_Tables=schema_name.view_name
impdp remap_table=view_name:schema_name.table_name
As we see the above command demonstration views are exported as tables. All the columns that are mentioned as part of view , underlying rows, dependent objects including grants and constraints are also exported
It is to be noted that remap_table is not a mandate. IF not specified impdp is going to create a table with same name as view name. If we want a new name for the target table mention that
13) How is full transportable export/import specified in datapump?
This is a new feature in oracle datapump 12c. FULL=Y, TRANSPORTABLE=ALWAYS should be used for this purpose. This is a feature that does make use of NETWORK_LINK option
14) How does datapump record timestamp in logfile?
Datapump 12c does come with option called LOGTIME to record time in status and log files. It can have values like – NONE, STATUS, LOGFILE,ALL
15) Is there a way to compress dumpfile?
Yes. Datapump offers option called COMPRESSION that can take values like all, COMPRESSION_ALGORITHM whose values can be HIGH, MEDIUM, BASIC,LOW. This does compress the dumpfile
16) Can archived logging be disabled during import?
Starting oracle 12c, datapump has an option to disable archive logging during import. this is a value that can be set as part of TRANSFORM option. This can be set at table, index level as well
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TRANSFORM=DISABLE_ARCHIVE_LOGGING:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:INDEX
17) Can data_pump_dir object be specified at client level?
Datapump is a server based tool. All the dump files, trace files, log files are stored in a location that is secured, has read and write permission to user performing export/import using datapump utilities. For this to happen a directory location in database server having sufficient space to hold information must be always associated with data_pump_dir. It is possible to specify this from client as an environment variable. However, this is a directory in server
First create a directory object in server as follows:
create DIRECTORY SERVER_DATAPUMP_DIR AS ‘/opt/oracle/dumpfile_Dest’;
Now, set the environment variable from client as follows
setenv DATA_PUMP_DIR SERVER_DATAPUMP_DIR
Perform expdp/impdp as follows:
expdp username/password tables=tab1 dumpfile=test.dmp
18) Is there a way to get better performance using expdp and impdp?
Yes. Datapump utilities come with buffer option whose default value can be increased to higher value for better performance. Also, if there are no other resource consuming jobs or applications running while export or import is in progress, this will increase speed. Also, parallel option can be used to increase number of threads used for performing the job with better speed
19) What are all the different remap options available with datapump?
Datapump offers option to import the objects exported onto different schema(user), tablespace, datafile, table, data. accordingly all of them start with remap_ prefix. Following are the many different remap options that come as part of datapump utility
REMAP_SCHEMA
REMAP_TABLE
REMAP_DATA
REMAP_TABLESPACE
REMAP_DATAFILE
20) Can we ignore errors during import?
In some imports the errors are well tested and can be ignored. To stop panic during production upgrade, maintenance it is possible to ignore the datapump errors and proceed further using the option IGNORE