Sunday, November 17, 2013

DATAPUMP CONCEPTS & SCENARIO

Important Datapump view "dba+datapump_jobs"
Common parameters IN(Traditional Export vs Expdp)

FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)

Common parameters IN(Traditional Import vs Impdp)

FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES

Comparing (Traditional Export vs  Expdp) parameters



 Export
 Expdb
 FEEDBACK
 STATUS
 FILE
 DUMPFILE
 LOG
 LOGFILE
 OWNER
 SCHEMAS
 TTS_FULL_CHECK  
 TRANSPROT_FULL_CHECK



Comparing (Traditional Import vs  Impdp) parameters

Import
Importdp
DATAFILE
TRANSPORT_DATAFILES
DESTROY
REUSE_DATAFILES
FEEDBACK
STATUS
FILE
DUMPFILE
FROMUSER
SCHEMAS, REMAP_SCHEMAS
IGNORE
TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)

LOG
LOGFILE
INDEXFILE, SHOW
SQLFILE
TOUSER
REMAP_SCHEMA




Parameters in expdp Utility

ATTACH Attach the client session to existing data pump jobs
ATTACH [=[schema_name.]job_name]

COMPRESSION  Specifies which data to compress before writing to the dump file set.
COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

DATA_OPTIONS   Default: There is no default. If this parameter is not used, then the special data handling options it provides simply do not take effect.

DIRECTORY   Default: DATA_PUMP_DIR   Location to write the dump file and log file.

DUMPFILE     Default: we have to give  exp.dmp

ENCRYPTION  The default value depends upon the combination of encryption-related parameters that are used
ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

ENCRYPTION_ALGORITHM   Specifies which cryptographic algorithm should be used to perform the encryption
ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

ENCRYPTION_MODE  The default mode depends on which other encryption-related parameters are used. Specifies the type of security to use when encryption and decryption are performed.
ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

ENCRYPTION_PASSWORD   Default: There is no default; the value is user-provided. Specifies a password for encrypting encrypted column data, metadata, or table data in the export dumpfile. This prevents unauthorized access to an encrypted dump file set.

ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE={BLOCKS | STATISTICS}

ESTIMATE_ONLY It estimate the space, but does not perform export
ESTIMATE_ONLY={y | n}

EXCLUDE List of objects to be excluded

FILESIZE  Default: 0 (equivalent to the maximum size of 16 terabytes).Specifies the maximum size of each dump file. If the size is reached for any member of the dump file set, then that file is closed and an attempt is made to create a new file, if the file specification contains a substitution variable or if additional dump files have been added to the job.
FILESIZE=integer[B | KB | MB | GB | TB]

INCLUDE List of jobs to be included

JOB_NAME Name of the export job

KEEP_MASTER Specify Y not to drop the master table after export

NETWORK_LINK Specify database dblink to export from remote database

NOLOGFILE Specify Y if you do not want to create log file
NOLOGFILE={y | n}

PARALLEL  Specify the maximum number of threads for the export job. also help to split the dumpfile into number dump-file set

QUERY  Default: There is no default. Allows you to specify a query clause that is used to filter the data that gets exported.
QUERY = [schema.][table_name:] query_clause

VERSION objects that are incompatible with the specified version will not be exported.

SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. Thesample_percent indicates the probability that a block of rows will be selected as part of the sample.


Parameters in impdp Utility

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

EXCLUDE List of objects to be excluded

ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.
 
FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.

FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.

INCLUDE List of jobs to be included

NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.

REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.

REMAP_SCHEMA Loads objects to a different target schema name.

REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.

TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.

TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.
For instance, TRANSFORM=storage:N:table

Data Pump differnt Export Levels in command line:
o   Full Export Level
o   Schema Level
o   Table Level
o   Tablespace  Level
o   Transportable Tablespace Level





EXAMPLE 1 :          
EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY


USERS01 tablespace need to be export  from cust_schema in source_db And  import to sample table in USERS02 tablespace from cust_schema in Target_db
SCHEMAS
REMAP_TABLESPACE
TABLE_EXISTS_ACTION
REMAP_TABLESPACE: we importing the data to different tablespace.

TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

Exclude few objects during the import

EXCLUDE
And exclude few objects and import  into the table named "sample" in USERS02 tablespace

Expdp parfile information
Import parfile information
userid=system/password@alias_name
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dump_dir
schemas=cust_schema
userid=system/password@alias_name
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
remap_tablespace=res:users
exclude=sequence,view,package,cluster,table:"in('sample')"




EXAMPLE 2.
EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
USERID
DUMPFILE,
LOGFILE
DIRECTORY
USERID
DUMPFILE,
LOGFILE
DIRECTORY


Here going to export the sample table FROM cust_schema from the instance called source . And import into sample table FROM cust_schema from the instance called
target
SCHEMAS
TABLE_EXISTS_ACTION

TABLE_EXISTS_ACTION: the table already present in the source location. so replacing with new data




Expdp parfile information
Import parfile information
userid=system/password@alias_name
dumpfile=table_expdb.dmp
logfile=table_expdb.log
directory=dump_location
tables= cust_schema.sample


userid=system/password@alias_name
dumpfile=table_expdb.dmp
logfile=tabimpdb.log
directory=dump_location
table_exists_action=REPLACE




EXAMPLE 3:                                     
EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY


Export the sample tables from cust_schema. this sample has partitions now we are going to export the contents in partition1 and partition2
TABLES
TABLE_EXISTS_ACTION
TABLE_EXISTS_ACTION: APPEND  the content of  sample table in the target database

(NOTE: partition information not required if target has same table name with same partition


sql> delete sample where deptno=1;


sql> delete sample where deptno=2;



NOTE: Before overwrite the partition in target database we need to manually delete partition and import the dump file set to the target database.

EX: If we want to overwrite the exported data in target database, then we need to delete value in the partition has sample table information of  deptno in(1,2).


Expdp parfile information
Import parfile information

userid=system/password@alias_name
dumpfile=partition_tab_expdb.dmp
logfile=partition_tab_expdb.log
directory=dump_location
tables=cust_schema.sample:partition1, cust_schema.sample:partition2



userid=system/password@alias_name
dumpfile=partition_expdb.dmp
logfile=tab_impdb.log
directory=dump_location
table_exists_action=append




EXAMPLE 4:

EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY


To export only table in cust_schema at source instance and import into the target instace.
CONTENT=table
TABLE_EXISTS_ACTION
TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data



Expdp parfile information
Import parfile information

userid=system/password@source
dumpfile=schema_tab_expdb.dmp
logfile= schema_tab_expdb.log
directory=dump_location
include=table
schemas=cust_schema



userid=system/password@target
dumpfile=schema_tab_expdb.dmp
logfile=schema_tab_impdb.log
directory=dump_location
table_exists_action=replace





EXAMPLE 5:

EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY


Export the rows belongs to the sampdept,  sample need to export the value of dept 10,20.
Need to export only the content is  data of the table.
INCLUDE,
QUERY,
CONTENT,

SCHEMAS
TABLE_EXISTS_ACTION,
QUERY
SCHEMAS,

TABLE_EXISTS_ACTION: APPEND  the content of  sample table in the target database

QUERY: we have the content of samdept 10,20 but we need to import only the content of samdept 20.
(note:always check the expdp logfile what content are exported)

Expdp parfile information
Import parfile information

userid=system/password@source
dumpfile=data_filter_expdb.dmp
logfile=data_filter_expdb.log
directory=dumplocation
schemas=cust_schema
query="where deptno in(10,20)"
content=data_only
include=table:"in('SAMPLE','SAMDEPT')"



userid=system/password@target
dumpfile=data_filter_expdb.dmp
logfile=data_filter_impdb.log
directory=dumplocation
schemas=cust_schema
query="where deptno = 20"
table_exists_action=APPEND




EXAMPLE 6:
EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY

In using impdp multiple files can be detected automatically detected by passing the value "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 70MB in size. Import the dump file into target database. so this expdp will create the backupsets in 70mb in size. by using "%U" in dumpfile it will automatically create the sequence dumpset
EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the of total size of dump set.
INCLUDE,
SCHEMAS,

FILESIZE
TABLE_EXISTS_ACTION,
REMAP_TABLESPACE
TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

REMAP_TABLESPACE=tbs1:tbs01
Import the value to new tablespace


Expdp parfile information
Import parfile information

userid=system/password@alias_name
dumpfile=schema_exp_split_%U.dmp
logfile=schema_exp_split.log
directory=dump_location
schemas=cust_schema
include=table
filesize=70M




userid=system/password@alias_name
dumpfile=schema_exp_split_%U.dmp
logfile=schema_imp_split.log
directory=dump_location
table_exists_action=replace
remap_tablespace=tbs1:tbs01




EXAMPLE 7:
EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY

In using impdp multiple files can be detected automatically detected by passing the value "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 6 parts of total size of export data. Import the dump file into target database. so this expdp will create the backupsets in total size of six parts. by using "%U" in dumpfile it will automatically create the sequence dumpset
EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the dumpset upto schema_exp_split_06.dmp
INCLUDE,
SCHEMAS,

PARALLEL
TABLE_EXISTS_ACTION,
REMAP_TABLESPACE
TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

REMAP_TABLESPACE=tbs1:tbs01
Import the value to new tablespace


Expdp parfile information
Import parfile information

userid=system/password@source
dumpfile=schema_exp_split_%U.dmp
logfile=schema_exp_split.log
directory=dump_location
schemas=cust_schema
include=table
parallel=6



userid=system/password@target
dumpfile=schema_exp_split_%U.dmp
logfile=schema_imp_split.log
directory=dump_location
table_exists_action=replace
remap_tablespace=tbs1:tbs01
exclude=grant



EXAMPLE 8




EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY

In using impdp multiple files can be detected automatically detected by passing the value "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 6 parts of total size of export data in different loaction. Import the dump file into target database. So this expdp will create the backupsets in total size of six parts. by using "%U" in dumpfile it will automatically create the sequence dumpset
EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the dumpset upto schema_exp_split_06.dmp
INCLUDE,
SCHEMAS,

FILESIZE
TABLE_EXISTS_ACTION,

TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data




Expdp parfile information
Import parfile information

userid=system/password@alias_name
logfile=schemaexp_split.log
dumpfile=dumplocation1:schemaexp_%U.dmp,
dumplocation2:schemaexp_%U.dmp,
dumplocation3:schemaexp_%U.dmp
directory=dump_location
schemas=user_schema
include=table
filesize=100M




Impdp parfile content:
userid=system/password@alias_name
logfile=schemaimp_split.log
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
directory=dumplocation
table_exists_action=replace




EXAMPLE :9.a
Creating the database link (for destination system schema)
Before performing exporting the data over a network link it is required  to create a database link in the DESTINATION database first. The link can be created by either destination locations's user schema or in the SYSTEM schema. If the link is created in SYSTEM schema the user will be created automatically with the same grants during the import.

Creating the link as SYSTEM schema:

create database link source_link connect to system identified by manager using 'source';
(Note: The database connection string for the database “source” is defined in the local TNSNAMES.ORA

Creating the link as USER schema Creating the database link (in destination user schema):

For creating the database link in the destination user schema you need to create the user first. If done create the link with:

create database link source_link connect to test identified by user using 'source';

Warning raise when mixing privileged user and non-privileged users
There is one warning when mixing privileged (e.g. DBA users) with non privileged users data pump will complain about that:

ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
You need to look out for database links created in the SYS or SYSTEM schema pointing to a non-DBA-user in the remote database or vice versa.

For example the following link created the in SYSTEM schema wont work because SYSTEM is a dba user and test is not:

create database link source_link connect to test identified by test using 'source';

EXAMPLE:9 (b)



EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
DUMPFILE,
LOGFILE
DIRECTORY

In using impdp multiple files can be detected automatically detected by passing the value  "%U" to dumpfile
Currently working in source database. but taking the export from destination location and storing the dump file set in source location using network_link this method of export is possible. But users of the source and destination must have identical privileges. if users privileges fail to match it throws an error. Using tns entry we can achieve this job

INCLUDE,
SCHEMAS,

NETWORK_LINK
TABLE_EXISTS_ACTION,

TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data



If user privilage fails to match we get this error:

ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
You need to look out for database links created in the SYS or SYSTEM schema pointing to a non-DBA-user in the remote database or vice versa.


Expdp parfile information
Import parfile information

userid=user/user@source
logfile=netwrok_exp.log
directory=dump_location
dumpfile=network_exp.dmp
schemas=user
include=table
network_link=target




userid=system/password@source
logfile=network_imp.log
directory=dump_location
dumpfile=network_exp.dmp
table_exists_action=replace

EXAMPLE 10:



EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition

LOGFILE,
DIRECTORY


Currently working in target database. So without using the export the dump file set here directly taking import the data  from target location to source location. using  network_link  this  method is possible.

TABLE_EXISTS_ACTION,

TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data




(NOTE: From the target server contact the source server the extract and import the data into source database. this methos is very useful when we have no space to create dump file set. then we can implement this operation over network.)


Import parfile information
userid=user/user@target
network_link=source
logfile=network_imp.log
directory=dumplocation
table_exists_action=replace


EXAMPLE 11:



EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
LOGFILE,
DIRECTORY


Currently working in target database. But performing  the export in target database itself. Importing the dump file set from target to source. without moving the dumpfile in the source location.
 EX: imagine we have no space on source database but target have enough space so to utilize the target space without consuming the source space we can achieve this method  
INCLUDE,
SCHEMAS,


TABLE_EXISTS_ACTION,

NETWORK_LINK
TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data



Expdp parfile information
Import parfile information

userid=user/user@target
logfile=netwrok_exp.log
dumpfile=network_exp.dmp
directory=dump_location
schemas=cust_schema
include=table




userid=system/password@target
logfile=network_imp.log
dumpfile=network_exp.dmp
directory=dump_location
table_exists_action=replace
remap_schema=user:user01



EXAMPLE 12:



EXPDP explanation
SOURCE DB-EXPDP
TARGET DB-IMPDP
IMPDP explanation
Common for most of the condition
DUMPFILE,
LOGFILE
DIRECTORY
LOGFILE,
DIRECTORY


SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. if table name is not specified then entire job 40% percentage value will exported
TABLE,
SAMPLE,


TABLE_EXISTS_ACTION,


TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data



Expdp parfile information
Import parfile information

userid=system/password@source
dumpfile=sample_expdb.dmp
logfile= sample_expdb.log
directory=dump_location
tables=user.sample
SAMPLE=40




userid=system/password@target
dumpfile=sample_expdb.dmp
logfile= sample_impdb.log
directory=dump_location
table_exists_action=replace


 

No comments:

Post a Comment