LOGICAL BACKUP.pdf

(1)

REFRESHING DATA

REFRESH means MOVING PORTION OF DATA from one database to another.

Taking recent copy of production database and restoring it to the target environment, i.e. applying changes (updates) of the production database to dev/clone database where the database already cloned. Normally refresh can be done for TEST/DEV databases during development and test phases.

Refreshing a particular table, group of tables, schema, tablespace using traditional export/import, transportable tablespaces or Data Pump methods.

TABLE REFRESH

Sometimes requirement comes to us refresh tables from development database server to production database server. Tables tested in development database and need to be in production database.

Capture source database table complete info.

Take export of required table(s) at source database. SCP the export .dmp file to target database server.

Take export of required table(s) at target database -(Recommended). Truncate the required table(s) at destination database -(Recommended). Import the .dmp file into the destination database.

Target database table row count should be same as source database table row count.

PRODUCTION DATABASE  CRMS  192.168.1.130 DEVELOPMENT DATABASE  DEVDB  192.168.1.131

TABLES  QRT_PRD_AP, QRT_PRD_AR, QRT_PRD_TARGET TAKE AN EXPORT OF THE TABLES IN DEVDB

$ exp system/manager file=sham_tables.dmp log=sham_tables.log direct=y tables=(sham.prd_ap, sham.prd_ar, sham.prd_gl) statistics=none

Export: Release 11.2.0.1.0 - Production on Thu Jun 4 00:12:28 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Direct Path ... Current user changed to SHAM

. . exporting table QRT_PRD_AP 1868 rows exported . . exporting table QRT_PRD_AR 88622 rows exported . . exporting table QRT_PRD_TARGET 68261 rows exported

Export terminated successfully without warnings.

TAKE A BACKUP OF THE EXISTING TABLES IN PRODUCTION SERVER

SHAM> create table QRT_PRD_AP_BKP as select * from QRT_PRD_AP; SHAM> create table QRT_PRD_AR_BKP as select * from QRT_PRD_AR;

(2)

SYS> select owner, table_name from dba_tables where table_name like '%BKP';

.. ...

SCP THE EXPORT FILE TO PRODUCTION SERVER

$ gzip sham_tables.dmp

$ scp sham_tables.dmp.gz [email protected]:$HOME [email protected]'s password:

sham_tables.dmp.gz

GUNZIP THE EXPORT FILE IN THE PRODUCTION SERVER

$ gunzip sham_tables.dmp.gz sham_tables.dmp

TRUNCATE TABLES FROM SHAM SCHEMA

SYS> drop table SHAM.QRT_PRD_AP; SYS> drop table SHAM.QRT_PRD_AR; SYS> drop table SHAM.QRT_PRD_TARGET; IMPORT THE EXPORT FILE

$ imp system/manager file=sham_tables.dmp log=sham_tables.log fromuser=sham touser=sham Import: Release 11.2.0.1.0 - Production on Fri Jun 5 10:41:09 2015

Export file created by EXPORT:V11.02.00 via direct path

import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing SHAM's objects into SHAM

. . importing table "QRT_PRD_AP" 1868 rows imported . . importing table "QRT_PRD_AR" 88622 rows imported . . importing table "QRT_PRD_TARGET" 68261 rows imported About to enable constraints...

Import terminated successfully without warnings.

SQL COMAMND TO ENABLE INDEX MONITORING

$ ind_monitoring.sql;

select 'alter index '||index_name||' monitoring usage;' from user_indexes;

After import manually we have to enable index monitoring. You can use above query in spool file.

SHAM> spool enable_ind_montoring; SHAM>@ind_monitoring.sql;

(3)

It is advisable to drop indexes before import to speed up the import process. After import you can recreate the indexes. INDEXFILE – contains index creation statements you can use this file to rebuild the indexes after the import has completed.

indexfile.txt

RECOMPLILE INVALID OBJECTS

SYS>@?/rdbms/admin/utlrp.sql;

.. ...

POINTS TO REMEMBER

Oracle’s utilities EXP/IMP are used to perform logical database backup and recovery. To perform export and import a database must be up and running. EXP and IMP utilities are present in the $ORACLE_HOME/bin directory.

$ which exp imp

/u02/app/oracle/product/11.2.0/dbhome_1/bin/exp /u02/app/oracle/product/11.2.0/dbhome_1/bin/imp

To use export and import, you must have the CREATE SESSION privilege. A user can perform the backup of entire database, the user should have EXP_FULL_DATABASE privilege. In order to import full database, the user should have IMP_FULL_DATABASE.

You can run export and import in two modes. Command line mode & Interactive mode. To get interactive mode just type exp or imp at OS prompt, tools will ask all necessary input.

Logical backups are more useful to perform schema level refreshes. It is also useful for database reorg, schem reorg, and table level reorg..

Using EXP/IMP tools we can transfer data from one database to another database. EXP tool to export data from source database, IMP tool to load data into the target database.

A file .dmp is generated by EXP utility. It is partial binary and partial text file. This file can be read by IMP utility. Manually editing .dmp files are NOT recommended.

During import operations, sometimes we may get errors messages related to undo to overcome this error use commit=Y  loads array of records and issues commit frequently.

To suppress DDL related errors messages like (object already exists) specify ignore=Y.

Export can be done in two methods. Conventional & Direct. By default traditional logical backup is conventional (goes through SQL processing layer). We can also bypass by specifying direct=Y.

CONVENTIOANL PATH Vs DIRECT PATH

Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, then rows are transferred to the evaluation buffer, after passing expression evolution (equivalent insert statement is generated and validated) it is transported to the export client, then writes data into the export .dmp file.

(4)

Direct path export is much faster than conventional path because the data is read from a disk into the buffer cache and rows are transferred directly to the export client. The evaluating buffer (that is, the SQL command-processing layer) is bypassed. Data is already in the format that export expects, thus avoiding unnecessary data conversion then writes the data into the export .dmp file.

Direct path can be much faster than conventional path, because the SQL command processing layer is bypassed. QUERY & BUFFER parameters cannot be used along with DIRECT=Y in export command.

CONSISTENT=Y Vs CONSISTENT=N

CONSISTENT = N is default. In this case each table is usually exported in a single transaction. CONSISTENT = Y  internally specifies “set transaction read only” and ensures data consistency. Export utility exports consistent image of the table i.e. the changes which are done during export will NOT be exported. If ‘Y’ is set confirm sufficient undo segment space, otherwise it may lead to “ORA-1555 snapshot too old error”.

When using CONSISTENT=Y and the volume of update is large, then the rollback segment usage will be large. The export process will be slower, because the rollback segment must be scanned for uncommitted transactions.

COMPRESS =Y Vs COMPRESS=N

COMPRESS=Y is default. Compress specifies how export & import manage initial extent for table data.

COMPRESS=Y means export does not compress the contents of the exported data. The INITIAL storage

parameter is set to the total size of all extents allocated for the object. If extent sizes are

large, the allocated space will be larger than the space required to hold the data.

SCHEMA TABLE_NAME OPTION ACTIVITY 1 ACTIVITY 2 SCHEMA_NAME USR1 T1 COMPRESS=Y  EXPORT T1 IMPORT T1  USR2

USR1> select table_name, initial_extent, next_extent from user_tables where table_name='T1'; TABLE_NAME INITIAL_EXTENT NEXT_EXTENT

--- --- ---

T1 65536 1048576

USR1> select extents, bytes from user_segments where segment_name='T1'; EXTENTS BYTES

--- ---

52 38797312

During export i have specified COMPRESS=Y, after import we can check the value from USR2 SCHEMA.

--- --- ---

T1 38797312 1048576

(5)

COMPRESS=N

If you set COMPRESS=N during export, export uses the current storage parameters, including the values of initial extent size and next extent size. While import at that time of creating table, it will use the same values of INITIAL extent as in the original one. The values of these parameters specified in the CREATE TABLE or ALTER TABLE statement.

SCHEMA TABLE_NAME OPTION ACTIVITY 1 ACTIVITY 2 SCHEMA_NAME USR1 T1 COMPRESS=N  EXPORT T1 IMPORT T1  USR3

--- --- ---

T1 65536 1048576

--- ---

52 38797312

During export i have specified COMPRESS=N, after import we can check the value from USR3 SCHEMA.

--- --- ---

T1 65536 1048576

--- ---

47 33554432

EXPORTING QUESTIONABLE STATISTICS

STATISTICS = ESTIMATE is default. Option includes estimate, compute and none. Exporting statistics may not usable. Mostly none is specified. When export the database object you may encounter warning message as EXP-00091: Exporting questionable statistics.

EXP-00091 is not an error but might want to gather more up-to-date stats, preferably by DBMS_STATS. We can remove the error by setting statistics="none" or by setting the client character set.

..

Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion) ...

The key to resolve the warning message is here. Change the charset before running exp with an $ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

(6)

The file contains list of export parameters or import parameters. Instead of typing the parameters on the command line you can use a parameter file where the parameters are stored.

$ exp username/password parfile=<parameter_file_name> $ imp username/password parfile=<parameter_file_name>

$ cat sham_exp.txt

file=exp_sham_tables.dmp log=exp_sham_tables.log

tables=sham.emp, sham.project, sham.dept, sham.payroll direct=y statistics=none $ cat sham_imp_txt file=sham_exp_tables.dmp log=sham_imp_tables.log fromuser=sham touser=maya indexes=no

$ exp system/manager parfile=sham_exp_txt

.. ...

$ imp system/manager parfile=sham_imp_txt

.. ...

FILESIZE

Export utility supports writing to multiple files and import utility can read from multiple files. Once you specify a value (bytes) for the filesize parameter, export writes number of bytes (as you specified filesize value) for each dump file.

QUERY

When doing table mode export, query parameter allows to select a subset of rows from a set of tables based on WHERE clause.

QUERY parameter cannot be specified in a direct path export.

QUERY parameter cannot be specified for full, user or tablespace mode exports.

query=\"where gender=\'female\'\" query=\"where dept_id=\10\"

FEEDBACK

Every . is determined how often a feedback is displayed. If you specify feedback=n, then displays a dot(.) for every n rows are processed. This is useful for tracking the progress of large export and import operations.

USERID

(7)

IGNORE

Specifies how object creation error should be handled. If already object exists and IGNORE=Y then the rows are imported to the tables otherwise error will be reported and no rows are loaded into the table.

HELP

A basic help screen will appear for export and import. At os, $ exp help=y or imp help=y

FILE

Default: expdat.dmp – stands for EXPORT DATA DUMP. The default extension is .dmp. File specifies the name of the export a dump file.

LOG

Specifies a file name (export.log). Which contains exp and import progress information, error and warning messages.

SHOW

To display contents of the export dump file are listed and NOT imported. imp command with show=y.

FULL

The entire database is exported. You need EXP_FULL_DATABASE role to export in this mode.

TABLES

Specifies the name of the table or tables to be exported. You need to specify a comma separated list of all tables to be exported. When export a table or set of tables, you need to specify TABLE parameter could be used with OWNER parameter. Ex: TABLES = OWNER.TABLE_NAME1, OWNER.TABLENAME2 …

OWNER

Owners object will be exported.

ROWS

Whether the rows of table data are exported.

FROMUSER | TOUSER

FROMUSER: Specifies the schema for import. TOUSER: Specifies target schema for import.

Both parameters need to be used together when import operation.

The IMP_FULL_DATABASE role is required to use FROMUSER and TOUSER parameter.

INDEXES | CONSTRAINTS | GRANTS

INDEX: Whether or not export utility export indexes. Default y.

CONSTARINTS: Whether or not export utility export table constraints. Default y.

GRANTS: Whether or not export utility exports object grants. System privilege grants are always

exported. Object grants are exported depend on whether you use full database mode or user mode.

In FULL database mode, all grants on the table are exported. In USER mode,

(8)

In export, specifies the size (in bytes) of the buffer (array) used to fetch rows. It determines the maximum number of rows in an array, fetched by export. If you specify 0, export fetches only one row at a time. Use below formula to calculate buffer size.

BUFFER_SIZE = ROWS_IN_ARRAY * MAXIMUM_ROW_SIZE

Buffer parameter applies only to conventional path export and no effect with direct path export. For direct path export use record length parameter to specify size of the buffer.

RECORDLENGTH

Operating system dependent. Specifies the length in bytes, of the file record. You can use this parameter the size of the export I/O buffer (value is 64kb). This parameter is necessary when you transfer the export file to another operating system. If you do not define this parameter, it defaults to platform dependent value.

COMMIT

Default=N. Specifies whether import should perform after each insert.

By default commit occurs after loading each table. When an error occurs, import performs a rollback. If you import large table, the undo segment may grow large. To improve performance use COMMIT=Y

OBJECT_CONSISTENT

Default=N. Specifies whether or not export uses SET TRANSACTION READ ONLY to ensure the data is being exported is consistent.

TABLESPACES

Indicates type of mode is tablespace mode. Specifies all the tables in the tablespace to be exported and imported. We must have EXP_FULL_DATABASE role to export all tables in the tablespace to export.

TTS_FULL_CHECK

Default=N. Specifies whether or not to verify a tablespace for dependency. This parameter is applicable for transportable tablespace mode export.

When TTS_FULL_CHECK=Y, export verifies a recovery set (set of tablespaces to be recovered) has no dependencies on objects outside the recovery set.

TRANSPORT_TABLESPACE

Specifies export of transportable tablespace metadata and import transport tablespace metadata from an export file. Transportable tablespace import for the target database must be same or higher version as the source database.

FLASHBACK _SCN

Specifies the SYSTEM CHANGE NUMBER (SCN) that export will use to enable flashback. The export operation is performed data consistent as of specified SCN.

$ exp system/password file=exp.dmp flashback_scn= 10908900

FLASHBACK_TIME

FLASHBACK_TIME enables you to specify a timestamp. Export finds the SCN that closely matches to the specified timestamp.

(9)

FREQUENTLY USING EXP/IMP PARAMETERS EXPORT IMPORT BUFFER BUFFER COMPRESS COMMIT CONSISTENT FILE DIRECT FROMUSER FILE FULL FILESIZE IGNORE FULL INDEXFILE OWNER LOG PARFILE PARFILE QUERY QUERY ROWS TABLESPACES TABLES TOUSER TABLESPACES SHOW PERFORMANCE PARAMETERS

Performance parameter in export are direct and buffer. Performance parameter in import are buffer and commit.

INVOKE EXPORT AND IMPORT

Command line mode, Interactive mode and Parameter file. Mostly preferably method is command line mode.

EXPORT AND IMPORT MODES

A user who has EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use following modes.

FULL: Exports and imports for full database.

USER: User can export and import their own objects. (Tables, triggers, packages, indexes, ..

TABLE: Specific table and associated partitions

TABLESPACE: To move set of tablespaces from one database to another.

ORDER OF IMPORT

Schema objects are imported as import read from export file. Export file contains objects in the following order.

Type definitions, Table definitions, Table data,

Table index,

Integrity constraints, views, procedures, triggers, Bitmap, function based and domain indexes.

The order of import is as follows: New tables are created, data is imported and indexes are built. Triggers are imported. Integrity constraints are enabled on the new tables and any bitmap, function-based and domain indexes are built.

(10)

No. As I said, SYS schema cannot be exported. Account named %SYS% you cannot export. Following schemas cannot be processed by export.

ORDSYS, MDSYS, CTXSYS, LBACSYS, ORDPLUGINS

LOGICAL BACKUP AND IT’S ADVANTAGES

Table refresh and Schema refresh. Move data from one owner to another.

Move data from one tablespace to another. Transporting tablespaces between databases.

Reduce database fragmentation to save disk space.

IMPROVE EXPORT/IMPORT PERFROMACE

If you use conventional path export use buffer and set high value. Use direct=y to bypass SQL command processing layer.

Stop unnecessary applications to free up system resources for your job.

If you run multiple export sessions, ensure they write to different physical disk.

Before import database objects (if they exist in target database, take backup) and drop them. Not advisable to create an index at that time of data loading when import.

If any constraints on the target table, disable it during import and enable after import. Use indexes=N and use INDEXFILE parameter while import.

Create an indexfile so that you can create indexes after you have imported data. If you import large table, use commit=n to overcome undo related errors.

Make sure before import check size of undo and archive destination.

HOW TO IMPORT TABLE IN DIFFERENT TABLESPACE

By default NO parameter to specify different tablespace to import data. When export objects will be re-created actually from where they were exported from. But we can alter this behavior.

Recreate the table(s) in the another tablespace. (Table would be empty). Import the dumpfile using INDEXFILE parameter option.

Edit the indexfile script – to create indexes in the tablespace you want and remover REM also . Import the table using ignore=y (because already table created)

Execute the indexfile to recreate the indexes.

INDEXFILE contains index creation statements you can use this file to rebuild the indexes after

the import has completed.

SYS> select username, default_tablespace from dba_users where username='SHAM'; USERNAME DEFAULT_TABLESPACE

--- ---SHAM TBS1

SYS> select username, default_tablespace from dba_users where username='MAYA'; USERNAME DEFAULT_TABLESPACE

--- --- MAYA USERS

(11)

Sham schema objects and its associated tablespaces

I am going to import sham’s following object (emp, dept, project, emp_audit, grade) into maya.

TABLE DETAILS

SYS> select table_name, tablespace_name from DBA_tables where table_name IN ('EMP','PROJECT','DEPT','EMP_AUDIT','GRADE') and owner='SHAM';

TABLE_NAME TABLESPACE_NAME --- --- PROJECT TBS1 GRADE USERS EMP_AUDIT TBS1 EMP TBS1 DEPT TBS1 INDEX DETAILS

SYS> select index_name, table_name, tablespace_name from dba_indexes where table_name IN ('EMP','PROJECT','DEPT','EMP_AUDIT','GRADE') and owner='SHAM';

INDEX_NAME TABLE_NAME TABLESPACE_NAME

--- --- --- DEPT_DEPTID_C3_PK DEPT TBS1

EMP_EMPID_C1_PK EMP TBS1 EMP_DPID_IN1 EMP USERS GRD_EMPGRD_C12_PK GRADE TBS1 PROJ_PRJID_C4_PK PROJECT TBS1

Above output shows sham users some tables and indexes are from TBS1 tablespace. Grade table and associated index from USERS tablespace. I am going to import above all tables in TBS2 tablespace and associated indexes in TBS3 tablespace into maya user. Let’s start the process.

TAKE AN EXPORT

$ exp system/manager file=sham_tables.dmp log=sham_tables.log tables=sham.emp, sham.grade, sham.dept, sham.project, sham.emp_audit statistics=none

Export: Release 11.2.0.1.0 - Production on Fri Jun 19 12:19:31 2015

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ... Current user changed to SHAM

. . exporting table GRADE 6 rows exported . . exporting table EMP 68 rows exported . . exporting table DEPT 12 rows exported . . exporting table PROJECT 22 rows exported . . exporting table EMP_AUDIT 24 rows exported Export terminated successfully without warnings.

(12)

$ imp system/manager file=sham_tables.dmp log=sham_tables.log fromuser=sham touser=maya indexfile=indx_file.sql

Import: Release 11.2.0.1.0 - Production on Fri Jun 19 12:29:35 2015

Export file created by EXPORT:V11.02.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. . skipping table "GRADE"

. . skipping table "EMP"

. . skipping table "DEPT"

. . skipping table "PROJECT"

. . skipping table "EMP_AUDIT"

Edited indexfile script as per my requirement

indexfile.sql MAYA> @indx_file.sql; Enter password: **** Connected. .. ...

Table structure and indexes created

MAYA> select table_name, tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME --- --- EMP TBS2 GRADE TBS2 DEPT TBS2 PROJECT TBS2 EMP_AUDIT TBS2

MAYA> select index_name, table_name, tablespace_name from user_indexes;

INDEX_NAME TABLE_NAME TABLESPACE_NAME

--- --- --- PROJ_PRJID_C4_PK PROJECT TBS3 GRD_EMPGRD_C12_PK GRADE TBS3 EMP_DPID_IN1 EMP TBS3 EMP_EMPID_C1_PK EMP TBS3 DEPT_DEPTID_C3_PK DEPT TBS3

(13)

TABLES AND INDEXS AND IT’S ASSOCIATED TABLESPCES

TABLES AND CONSTRAINTS

ORA-02291 integrity constraint violated - parent key not found

For an insert statement, ORA-02291 error is common when you are trying to insert a child without a matching parent, as defined by a foreign key constraint. To avoid this error so you need to first insert values in parent table and then insert values in child table.

IMPORT THE DUMP FILE

$ imp system/manager file=sham_tables.dmp log=sham_tables.log fromuser=sham touser=maya ignore=y Import: Release 11.2.0.1.0 - Production on Fri Jun 19 15:50:44 2015

Export file created by EXPORT:V11.02.00 via conventional path

(14)

. . importing table "GRADE" 6 rows imported . . importing table "EMP" 68 rows imported . . importing table "DEPT" 12 rows imported . . importing table "PROJECT" 22 rows imported . . importing table "EMP_AUDIT" 24 rows imported About to enable constraints...

FULL DATABASE EXPORT/IMPORT

In order to perform full database export and import the user must have DBA role or EXP_FULL_DATABASE and IMP_FULL_DATABASE role. Entire database will be exported to a binary dump file. You have to use parameter FULL=Y.

$ exp system/<password> file=fulldb.dmp log=fulldb1.log full=y direct=y $ imp system/<password> file=fulldb.dmp log=fulldb2.log full=y

SPECIFIC USER’S DATA EXPORT/IMPORT

When you import dump file in another database, it is most important to create the users and its associated tablespaces. Make necessary privileges as per source database – such as quota on user associated tablespaces, user session privileges, etc...

$ exp system/<password> file=schema.dmp log=schema1.log owner=(sham,rose) direct=y $ imp system/<password> file=schema.dmp log=schema2.log fromuser=(sham,rose)

If the schema received grant privilege from another schema, when export both schemas at a time oracle exports all received privilege –(If privileges are only made by both schemas). When you import, it imports those privileges.

Once i export/import sham,rose schema but except scott and jhil

SHAM> grant select on emp to rose; -- Will be exported and imported. SHAM> grant update on emp to scott; -- Will get error while import

Because scott user does not exist in the target database – By default user made privileges are always exported. You can’t avoid ORA-01917.

ROSE> grant select on dept to sham; -- Will be exported and imported.

JHIL> grant select on payroll to sham; -- No error. (Received Privilege – read below points)

Points to note

If the schema received grant privilege from others does not get export when we export the schema. So, it does not get import when we import the same schema.

If the schema made grant privilege to others, those privileges are exported when export the schema. Whenever you import the file, those privilege will be imported to the specific users. If users does NOT exist, import will throw warning message. User X dropped so I have warning message.

IMP-00017: following statement failed with ORACLE error 1917:

"GRANT SELECT ON "EMP" TO "X""

IMP-00003: ORACLE error 1917 encountered

(15)

SPECIFIC USER TABLES EXPORT/IMPORT

$ exp system/<password file=exp_tables.dmp log=tables1.log tables=sham.emp,sham.dept direct=y $ imp system/<password> file=tables.dmp log=tables2.log fromuser=sham touser=sham

$ imp system/<password> file=tables.dmp log=tables3.log fromuser=sham touser=rose DATA FROM ONE SCHEMA TO ANOTHER

$ exp system/<password> file=schema.dmp log=schema1.log owner=sham direct=y

$ imp system/<password> file=schema.dmp log=schema2.log fromuser=sham touser=rose TABLE STRUCTURE EXPORT/IMPORT

$ exp system/<password> file=sham_meta.dmp log=meta1.log owner=sham rows=n direct=y $ imp system/<password> file=sham_meta.dmp log=meta2.log fromuser=sham touser=rose

SPECIFIC RECORDS FOR EXPORT/IMPORT

I want to import following records who belongs emp_level='d' and dept_id='60'.

SHAM> select * from emp where emp_level='d' and dept_id=60;

EMP_ID EMP_NAME GENDER DEPT_ID EMP_DESG ISACTI EMP_HIRE_ EMP_TERM_ EMP_LEVEL

--- --- --- --- --- --- --- --- ---

18 Thomas Reeve male 60 Proj-manager active 27-FEB-87 d 19 Andy Campbell male 60 Proj-manager active 23-MAR-87 d 23 Bailey Bond male 60 Proj-manager active 30-JUN-88 d 26 Bret Steyn male 60 Proj-manager active 28-SEP-88 d 29 Carlos Liu male 60 Proj-manager active 01-FEB-89 d 33 Brian Scalzo male 60 Proj-manager active 08-MAY-90 d 17 Donovan Thomson male 60 Proj-manager active 22-JAN-87 d 35 Ricky Martin male 60 Proj-manager active 22-JUN-90 d

8 rows selected.

When using query clause please note the use to escape characters(\) and double quotes (").

$ exp system/<password> file=emp_rows.dmp log=rows1.log tables=sham.emp direct=y query=\"where emp_level=\'d\' and dept_id=\60\" statistics=none

$ imp system/<password> file=emp_rows.dmp log=rows2.log fromuser=sham touser=scott TABLESPACE LEVEL EXPORT/IMPORT

$ exp system/<password> file=tbs.dmp log=tbs1.log tablespaces=crms direct=y $ imp system/<password> file=tbs.dmp log=tbs1.log tablespaces=crms full=y MULIPLE DUMP FILES EXPORT/IMPORT

$ exp system/<password> file=\file1.dmp,\file2.dmp,\file3.dmp owner=scott filesize=100m $ ls –l file*.dmp

..

(16)

As I said already a value of feedback=n displays a dot for every n rows processed.

SHAM> create table tab1(no number, string_val varchar2(15)); Table created.

SHAM> insert into tab1 select rownum,'ORACLE' from dual connect by level <= 400000; 400000 rows created.

SHAM> commit; Commit complete.

$ exp system/<password> file=tab1.dmp log=tab1.log tables=sham.tab1 feedback=50000 direct=y

.. ...

About to export specified tables via Direct Path ... Current user changed to SHAM

. . exporting table TAB1

. . .

400000 rows exported

Here, each dot specifies every 50000 records.

PATTERN MATCHING OBJECTS EXPORT/IMPORT

Suppose you want to EXPORT/IMPORT whose name matches a particular pattern. To do so, use “%” wild character in TABLES option. For example, the following command will EXPORT only tables whose names starts with alphabet “e,d,p”, and will import whose tables names starts with e%,g%

$ exp sham/sham file=sham_tables.dmp log=sham_tab1.log tables=(e%,d%,p%) direct=y

$ imp sham/sham file=sham_tables.dmp log=sham_tab2.log fromuser=sham touser=jhil tables=e%,g% SOME COMMON ERRORS DURING EXPORT/IMPORT

IMP-00001: Unique constraint ... violated

Already object is existing. When you import you might get this error because of duplicate rows. Drop the existing object and continue your import.

IMP-00015: Object already existes

Use IGNORE=Y parameter to ignore this error but you might have duplicate rows.

ORA-30036: unable to extend segment in undo tablespace ...

When you import large table, use COMMIT=Y or make undo tablespace large, such as adding additional datafiles in undo tablespace. Always ensure size of undo tablespace and archive destination before perform export/import process.

In oracle version 9i, it was used rollback segments. Managing rollback segment is complex. Oracle strongly recommends automatic undo management. From 10g and 11g oracle is using automatic undo management mode – UNDO SEGEMENTS IN UNDO TABLESACE.

(17)

DATA PUMP

Data Pump is available from 10g oracle version.

It is the replacement for traditional export and import utilities.

Data pump enables high speed movement of data and metadata from one database to another.

Why Data Pump is much faster than export/import?

EXPORT/IMPORT is just a single process.

EXP/IMP is client based which means, executing queries from client to the server.

Data Pump is a server based process rather than a client one. (This is part of the improvement). Dump files are read/written directly by the server and do NOT require data movement to the client. EXPDP/IMPDP can have multiple process all doing parts of the job. I.e. parallel processes available.

EXP/IMP Vs EXPDP/IMPDP

EXP/IMP is a Client/Server tool and runs outside of the database. EXP pulls data over the network and writes it to the disk.

EXP/IMP can access files in client and server (both).

EXP represents database metadata information as DDL in .dmp files

When you look at export dump files (.dmp), it would be full of SQL statements.

EXP uses OCI – used to connect to the oracle database and is used to run ordinary SQL statements. OCI stands for Oracle Call Interface is an Application Programming Interface (APIS).

OCI consists (set of library functions written in C language) – It is an interface to the oracle

database and it can be used to manipulate data and schema in an oracle database. An OCI application program can process SQL statements that are needed to perform application tasks.

Data Pump is a server side technology (works in the server).

Data Pump can access files in the server using oracle directories.

Data Pump represents database metadata in XML and uses transformations to regenerate the SQL. Data Pump uses Direct Path API – means bypassing the client and writing the dumps directly from the database. Direct path is available with exp/imp and the Data Pump and is independent of other features.

DATA PUMP COMPONENTS

Data Pump is made up of three components.

Data Pump uses PL/SQL packages, they are DBMS_DATAPUMP, DBMS_METADATA.

dbms_datapump PL/SQL package (Data Pump API) dbms_metadata PL/SQL package (Metadata API) The command-line line clients (expdp, impdp)

Data Pump Export uses EXPDP utility and Import uses IMPDP utility.

DBMS_DATAPUMP package is used to move all, or part of, a databases including both data and metadata. DBMS_METADATA package is used to retrieve metadata from the database dictionary as XML and submit

the XML to recreate the object.

The Data Pump client’s expdp and impdp use the procedures in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, as you enter the parameters at the command-line. Similarly when metadata is moved Data Pump uses DBMS_METADATA PL/SQL package. The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.

(18)

$ which expdp impdp

/u01/app/oracle/product/11.2.0/dbhome_1/bin/expdp /u01/app/oracle/product/11.2.0/dbhome_1/bin/impdp

Get quick summaries of Data Pump parameters.

$ expdp help=y - EXPDP for unloading. $ impdp help=y - IMPDP for loading.

Data Pump export and import process is reading and writing of dump files on the server. In order to use Data Pump for NON privileged users, the DATABASE ADMINISTRATOR must create a directory object for the Data Pump files that are read and written on the server and grant privileges to the users on that directory. To create a directory object log on to the oracle database as SYS user with SYSDBA privilege. Let’s us create the directory and grant necessary privileges.

SYS> create or replace directory dpdir as '/u03/datapump/'; Directory created.

Here directory object named dpdir that is mapped to a directory located at '/u03/datapump/'. Here a directory is created, you need to grant read and write privilege on the directory to other users.

SYS> grant read, write on directory dpdir to scott; Grant succeeded.

READ and WRITE permission to a directory object means, an Oracle database requires permission from

the operating system to read/write files in the directories. As a DBA, you must ensure that only approved users access to the directory object associated with the directory path.

A directory object helps to ensure data security and integrity. Data Pump requires directory path

which is already mapped by directory object name because export/import process files such as dump files and log files will be created inside the directory path). Create directory SQL statement does NOT create any directory at Operating System level, you need to create directory manually.

$ mkdir /u03/datapump/

For Privileged users a default directory object is available DATA_PUMP_DIR. By default the user

SYSTEM has read/write access to DATA_PUMP_DIR directory. You can check using following SQL command.

SYS> select * from dba_directories; ..

HOW DATA PUMP ACCESS DATA ?

Data Pump moves data in and out of databases using below methods.

Copy Data file to move data. Using Direct path to move data. Using External tables to move data. Using Conventional path to move data. Using network link to move data.

The fastest method of moving data is to copy database data files to the target database. Data Pump is used to unload structural information (metadata) into the dump file. It is used for transportable tabalespace mode operation. You need to specify TRANSPORT_TABLESPACES parameter.

(19)

DATA ACCESS METHODS - DIRECT PATH Vs EXTERNAL TABLE

Generally Data Pump supports two types of data access methods to load and unload data. One of the following access path is selected by Data Pump.

1) Direct Path.

2) External Tables - new driver called ORACLE_DATAPUMP

Really it does NOT matter to us which method is used. As a DBA You do NOT have control. Data Pump is always choose best method to load/unload data. Data Pump uses external path for complex objects.

As we know, Data Pump automatically uses direct path method for loading and unloading data. In this method, the SQL layer of the database is bypassed and rows are moved to and from the dump file only minimal interpretation. If a table contains a column BFILE, then direct path cannot be used to load the table data so Data Pump would use External tables.

EXTERNAL TABLES

Data Pump provides an external tables access driver (ORACLE_DATAPUMP) that reads and writes files. Oracle 10g has introduced ORACLE_DATAPUMP driver, enabling you to create external tables.

Situations in which direct path load/unload is NOT used

Data Pump uses external tables rather than direct path to load/unload the data for the table, if

any following conditions are exist for a table. When data is being exported/imported at least any following conditions exist. Some examples given here.

Clustered tables.

If any unique index exists on a preexisting table. If the tables is partitioned on a preexisting table. An active triggers on the preexisting table.

A single partition in a table with a global index.

Referential integrity constraint is present on a preexisting table. The table has any encrypted columns.

A table contains BFILE columns.

Domain indexes exist on LOB columns.

Supplemental logging is enabled and table has at least 1 LOB column. Fine-grained access control is in insert mode. – Load.

Fine-grained access control is in select mode. – Unload.

A table is being loaded with active referential constraints or global indexes, cannot be loaded using direct path access method. A table column has LONG datatype, cannot be loaded with external table access method. Data Pump is always choose best method to load data.

When conventional path is used by Data Pump?

Data Pump is NOT able to load data into a table using either direct path or external tables. In such cases conventional path is used. For ex, a table contains an encrypted column and LONG column would be imported using conventional path only.

Direct Path cannot be used to load encrypted columns and external tables cannot be used to import LONG columns. So Data Pump uses conventional Path it has no other choice. Conventional Path is much slower than Direct Path and External table path.

(20)

DATA PUMP PROCESS STRUCTURE

There are number of processes involved in a Data Pump job. As we know Data Pump jobs from user process, either SQL*Plus or through Enterprise manager but all the work done by server process so that Data Pump improves performance over traditional EXP/IMP utilities.

Client Process Shadow Process Worker Process

Master Control Process Parallel Query Process (PQ)

CLIENT PROCESS

This process is initiated by the client utility (EXPDP or IMPDP) that make calls Data Pump API. Since the Data Pump is completely integrated to the database, once the Data Pump job is established, the client process is NOT required to keep the job running. Multiple clients can attach and detach from a job for the purpose of monitoring and control.

SHADOW PROCESS

When a client logs into the oracle database, a foreground process is created. This shadow process services the client Data Pump API requests. Upon receipt of DBMS_DATAPUMP.OPEN request, the shadow process creates the job, which primarily consisting of creating the master table, master control process, and the Advanced Queuing (AQ) queues for communication among various processes.

Once a job is running the shadow process main job is to check the job status for the client process. If the client detaches the shadow process goes away; however the remaining Data Pump job processes are still active. Another client process can create new shadow process and attach to the existing job. You can see on the diagram how shadow process creates all required components.

(21)

Two queues are created for each Data Pump job. A CONTROL QUEUE & STATUS QUEUE.

MASTER CONTROL PROCESS : (MCP)

A master control process (MCP) is created for every Data Pump export job and import job. This is primary process for managing Data Pump job because it controls the execution of the Data Pump job. The master process performs following tasks.

Creates jobs and controls them. Creates and manages worker process. Monitors the jobs and logs the process.

Maintains the job state, dumpfile info, restart info.

To divide loading and unloading of data and metadata tasks. To manage job information in the master table.

Communicating with the clients and records ongoing export/import activities in the logfile.

Once a Data Pump job is launched, Master Process creates the master table in the user schema. At least two processes are started; they are DMnn and DWnn.

A Data Pump master process (DMnn), One or more worker processes (DWnn)

There is 1 MCP per job. This process can be seen when you start export/import job.

$ ps –ef | grep dm0  master background process $ ps –ef | grep dw0  worker process

FROM SQL PROMPT

SYS> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid

from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr;

(22)

MASTER TABLE

As we know, when a job is submitted MCP creates master table.

This master table is heart of the every Data Pump export and import job.

While data/metadata is being transferred, a master table is used to track the progress in the job.

During the operation a master table is maintained in the schema of the current user who initiated the Data Pump export. The master table contains info about details of current Data Pump operation being performed. Master table maintains one row per object with status information.

What kind of information’s is maintained in master table?

Running job, location of objects, parameters of the job, status of the job, worker process, etc...

For export operation,

The master table records the location of the database objects with in a dump file set.

End of the export operation, the content of the master table is written in the dumpfile set. Finally master table is removed from the user schema.

For import operation,

The master table is loaded from the dumpfile set.

It controls the sequence of operations and objects being imported.

The master table is either retained or dropped, it depends on the circumstances.

The master table is dropped, when the job is successfully completed.

The master table is dropped, if a job is killed using the KILL_JOB interactive command. The master table is retained, if a job is stopped using the STOP_JOB interactive command. The master table is retained, if a job terminates unexpectedly.

In the event of failure (job terminates unexpectedly), Data Pump uses the information in the master table to restart the job. For suspended job also, Data Pump is using master table to resume it.

(23)

Master table has the same name as the name of the job. Default name of the job would be,

EX: <SCHEMA>_<OPERATION>_<MODE>_<UNIQUE-COUNT> SYS_EXPORT_FULL_01

The master table is dropped (by default) when the Data Pump job finishes successfully.

INTERPROCESS COMMUNICATION (IPC)

Advance Queuing (AQ) is used for communicating among the various Data Pump processes. Two queues are created for each Data Pump job: a control Queue and a status queue.

COMMAND AND CONTROL QUEUE

This Queue provides a path of communication between master control process and worker process. All the work request created by the master process and the associated response are passed through the command and control queue. I.e. The DMnn divides up the work to be done and places individual tasks that make up the job on this queue. The worker processes pick up these tasks and execute them.

Used by MCP, to command and control of worker process. Queue is owned by SYS and Queue name like

STATUS QUEUE

The status queue is for monitoring purposes. MCP is the only writer to this queue. The Data Pump MCP writes work progress and error messages to the status queue.

DMnn places messages on it describing (state of the job).

Populated by Master control process (DMnn).

Consumed by clients shadow process to retrieve status info.

Info is available for clients; related to status of the running job and Encountered Errors. Queue is owned by SYS and Queue name like

(24)

The worker process handles the request assigned by the MCP.

The MCP creates the worker processes based on the value of the PARALLEL parameter. The worker process performs loading/unloading data and metadata.

The worker process maintains the object rows and type of objects such as tables, indexes or views; as well as maintains and their current status such as (pending, completed ,or failed, in the master table that can be used to restart a failed job.

PARALLEL QUERY PROCESS

The worker process can initiate parallel query process when Data Pump uses External Table API as data access method for loading and unloading data. The worker process uses External Table API creates multiple parallel query processes for data movement.

DATA PUMP FILES

Data Pump can be generate Three types of files.

SQL FILES, DUMP FILES & LOG FILES

SQL FILES are full of DDL statements describing the objects. LOG FILES describe the message of Data Pump operations.

DUMP files are created by EXPDP and IMPDP only can read dump files. DUMP files are used as input by IMPDP utility.

DUMP files contain the exported data – (holds data and metadata in binary format).

Collection of DUMP files are made up of dump file sets. It contains table data, database objects metadata and control information in binary format.

INVOKING EXPORT AND IMPORT

The Expdp/Impdp commands can be executed in three ways. They are following below.

Command line interface – EXPDP/IMPDP parameters in command line

Parameter file interface - EXPDP/IMPDP parameters in the file at OS level.

(25)

DATA PUMP EXPORT MODES

You can use EXPDP utility to export data or say unload (data or metadata) from the oracle database. There are distinct modes of data unloading using expdp, they are

Full Export Mode (entire database is unloaded). Schema Mode (specific schemas are unloaded).

Table Mode (separate table or set of tables and its associated objects are unloaded). Tablespace Mode (all objects in the tablespace are unloaded).

DIRECTORY OBJECT AND ACCESS PERMISSIONS

As we know in order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. The directory object is only a pointer to a physical directory it does NOT actually create the physical directory on the file system.

SYS> CREATE OR REPLACE DIRECTORY dpdir AS '/U01/DATAPUMP'/; SYS> GRANT WRITE ON DIRECTORY dPdir TO scott;  Export

SYS> GRANT READ ON DIRECTORY dpdir TO scott;  Import

SPECIFIC USER TABLES EXPORT /IMPORT

$ expdp scott/<password> dumpfile=table.dmp logfile=tab1.log tables=emp,dept directory=dpdir $ impdp scott/<password> dumpfile=tab1e.dmp logfile=tab2.log directory=dp

IMPORT INTO ANOTHER SCHEMA

The import parameter REMAP_SCHEMA allows us to import objects from one schema to another. Suppose we export tables from SCOTT schema and want to import these objects into HR schema. This can be achieved by REMPA_SCHEMA parameter.

$ expdp scott/<password> dumpfile=table.dmp logfile=tab1.log tables=emp,dept directory=dpdir $ impdp scott/<password> dumpfile=tab1e.dmp logfile=tab2.log remap_schema=scott:hr

directory=dp

In Data Pump the FROMUSER/TOUSER syntax is replaced by the REMAP_SCHEMA Option.

You may get error, if you have required privilege

ERROR: ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

TO PERFORM EXPORT FROM ANOTHER USERS ACCOUNT

SYS> grant exp_full_database to scott; or SYS> grant datapump_exp_full_database to scott; TO PERFORM IMPORT FOR ANOTHER USERS ACCOUNT

SYS> grant imp_full_database to scott; or SYS> grant datapump_imp_full_database to scott;

You can perform import operation even if you did NOT create export dump file, then you must have IMP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role to import operation. Above roles are granted to database users by Database Administrators (DBAs).

(26)

SPECIFIC TABLES FROM DIFFERENT SCHEMAS

$ expdp system/<password> dumpfile=tables.dmp logfile=tab1.log directory=dpdir tables=sham.emp,scott.dept

$ impdp system/<password> dumpfile=tables.dmp logfile=tab2.log directory=dpdir remap_schema=sham:hr,scott:hr

METADATA – TABLE STRUCTURE EXPORT/IMPORT

$ expdp system/<password> dumpfile=meta.dmp logfile=meta1.log tables=scott.payroll directory=dpdir content=metadata_only

$ impdp system/<password> dumpfile=meta.dmp logfile=meta2.log directory=dpdir remap_schema=scott:hr

METADATA EXPORT/IMPORT INTO SAME USER

Before import table structure into Scott, drop the payroll table from the Scott’s account.

$ expdp system/<password> dumpfile=meta.dmp logfile=meta1.log tables=scott.payroll directory=dpdir content=metadata_only

$ impdp system/<password> dumpfile=meta.dmp logfile=meta2.log directory=dpdir

SPECIFIEC RECORDS FOR EXPORT/IMPORT

I want to import following records who belongs emp_level='d' and dept_id='60'.

SHAM> select * from emp where dept_id=60 and emp_level='d';

EMP_ID EMP_NAME GENDER DEPT_ID EMP_DESG ISACTIVE EMP_HIRE_ EMP_TERM_ EMP_LEVEL

--- --- --- --- --- --- --- --- --- 18 Thomas Reeve male 60 Proj-manager active 27-FEB-87 d

19 Andy Campbell male 60 Proj-manager active 23-MAR-87 d 23 Bailey Bond male 60 Proj-manager active 30-JUN-88 d 26 Bret Steyn male 60 Proj-manager active 28-SEP-88 d 29 Carlos Liu male 60 Proj-manager active 01-FEB-89 d 33 Brian Scalzo male 60 Proj-manager active 08-MAY-90 d 17 Donovan Thomson male 60 Proj-manager active 22-JAN-87 d 35 Ricky Martin male 60 Proj-manager active 22-JUN-90 d

8 rows selected.

$ expdp system/<password> dumpfile=rows.dmp logfile=rows1.log tables=sham.emp query=\"where emp_level=\'d\' and dept_id=\60\" directory=dpdir

$ impdp system/<password> dumpfile=rows.dmp logfile=rows2.log directory=dpdir remap_schema=sham:sony

When using query clause please note the use to escape characters(\) and double quotes (").

SHEMA LEVEL EXPORT/IMPORT

If we want to export specific schemas in the database, we can use the SCHEMAS parameter. EXPDP command will create a dumpfile containing data and metadata for Schemas respectively

(27)

$ expdp system/<password> dumpfile=sham.dmp logfile=sham1.log directory=dpdir schemas=sham $ impdp system/<password> dumpfile=sham.dmp logfile=sham2.log directory=dpdir

remap_schema=sham:rose

To import these schemas (SCOTT & SONY) into a database, we can use the following IMPDP command. Drop these (SCOTT & SONY) before start import process.

$ expdp system/<password> dumpfile=schema.dmp logfile=schema1.log schemas=scott,sony directory=dpdir

$ impdp system/<password> dumpfile=schema.dmp logfile=schema1.log schemas=scott,sony directory=dpdir

GENERATE SQL FOR IMPORT OBJECTS

Instead of importing the data and objects, it is also possible to generate a file which will contain the DDL statements (SQL statements) for the objects and it will be saved in the OS. This is achieved using the SQLFILE parameter in 11g.

$ expdp system/<password> dumpfile=schemas.dmp logfile=schema1.log schemas=scott,sony directory=dpdir

$ impdp system/<manager> dumpfile=schemas.dmp logfile=schema2.log sqlfile=sqlinfo.sql directory=dpdir

At OS level you can verify sqlfile; you can give whatever a name you want to the file.

TABLESPACE LEVEL EXPORT/IMPORT

Before you import the dumpfile, you need to drop and recreate the tablespace in the database.

SQL> drop tablespace tbs1 including contents and datafiles; SQL> create tablespace tbs1 datafile...

$ expdp system/<password> dumpfile=tbs_data.dmp logfile=tbs_data1.log tablespaces=TBS1 directory=dpdir

$ impdp system/<password> dumpfile=tbs_data.dmp logfile=tbs_data2.log tablespaces=TBS1 directory=dpdir

FULL DATABASE EXPORT/IMPORT

$ expdp system/<password> dumpfile=fulldb.dmp logfile=fulldb1.log directory=dpdir full=y $ impdp system/<password> dumpfile=fulldb.dmp logfile=fulldb2.log directory=dpdir full=y MULTIPLE DUMP FILES EXPORT/IMPORT

$ expdp system/<password> dumpfile=\schema1.dmp,\schema2.dmp,\schema.dmp logfile=schema1.log schemas=sony filesize=1g directory=dpdir

$ impdp system/<password> dumpfile=\schema1.dmp,\schema2.dmp,\schema3.dmp logfile=schema2.log remap_schema=sony:scott directory=dpdir

In our case, we have used four dump files for export operation. You need to provide all above files to impdp utility, otherwise Data Pump will throw following error.

(28)

PARAMETER FILE FOR EXPORT/IMPORT

We can use parameter file (PARFILE) which contains EXPDP/IMPDP parameters. When we run expdp/impdp job instead of writing parameters in a command line, we can call the file at OS level. Suppose I have parfiles named exp_schema_par.txt and imp_schema_par.txt with some Data Pump parameters.

$ cat exp_schema_par.txt

# Parfile for Schema refresh – Export # dumpfile=schema_par.%u.dmp

logfile=schema_par.log schemas=sony

directory=dpdir parallel=3

$ expdp system/<password> parfile=exp_schema_par.txt

$ cat imp_schema_par.txt

# Parfile for Schema refresh – Import # dumpfile=schema_par.%u.dmp

logfile=imp_schema.log remap_schema=sony:maya directory=dpdir

$ impdp system/<password> parfile=imp_schema_par.txt REMAP FUNCTION

This is most important Data Pump feature. REMAP function allows the user to easily redefine how an object will be stored in the database.

REMAP_DATA - 11g FEATURE REMAP_TABLE - 11g FEATURE REMAP_SCHEMA REMAP_DATAFILE REMAP_TABLESPACE TRANSFORM REMAP_DATA

REMAP_DATA provides a method to mask data – when moving production database to test database.

This remap_data uses a user defined package/function to alter the data, i.e. it allows you to specify remap function that transforms table original data of the designated column and returns some other value (in the dumpfile) during export or import.

To perform a data remap operation, you need to create a stored package function and supply the PACKAGE.FUNCTION_NAME to the REMAP_DATA parameter.

SYNTAX : REMAP_DATA=SCHEMA.TABLE_NAME.COLUMN_NAME:SCHEMA.PKG.FUNCTION

EXAMPLE: REMAP_DATA=SCOTT.EMP.SAL:SCOTT.PACKAGE_NAME.FUNCTION_NAME

You can use this feature to protect (mask/convert) sensitive data such as credit/debit card numbers, customer financial account balance, customers_salary, etc. we can discuss separately.

(29)

REMAP_TABLE

REMAP_TABLE allows us to rename tables during an import operation – This is 11g feature.

SYNTAX : REMAP_TABLE=SCHEMA.TABLE_NAME:NEWTABLE_NAME

EXAMPLE : REMAP_TABLE=SCOTT.SALGRADE:SALGRADE_TEST

$ expdp system/<password> dumpfile=salgrade.dmp tables=scott.salgrade directory=dpdir $ impdp system/<password> dumpfile=salgrade.dmp remap_table=scott.salgrade:salgrade_new directory=dpdir

REMAP_SCHEMA

Loads objects from one schema to another schema, i.e. changing object ownership. If target schema does NOT exist, the import utility will create the target schema. FROMUSER/TOUSER syntax is replaced by the remap_schema.

SYNTAX : REMAP_SCHEMA=SOURCE_SCHEMA:TARGET_SCHEMA

EXAMPLE : REMAP_SCHEMA=SCOTT:HR

$ expdp system</password> dumpfile=scott_schema.dmp schemas=scott directory=dpdir

$ impdp system/<password> dumpfile=scott_schema.dmp remap_schema=scott:hr directory=dpdir REMAP_DATAFILE

REMAP_DATAFILE changes the name or path of the datafile.

When you move database from one server to another server, the source and target server have different mount point names, you can rename the datafiles on the import using remap_datafile.

Source datafile filesystem is '/u01/ora11g/crms/oradata/tbs01.dbf' Target datafile filesystem is '/u02/ora11g/crms/oradata/tbs01.dbf'

When you move database between platforms, obviously that have different file naming conventions, now the REMAP_DATAFILE parameter comes to change file system names.

This parameter changes the source datafilename to the target datafile name in all SQL statements where the source datafile is referenced.This is useful when performing database migration to another system with different file naming conventions.

SYNTAX : REMAP_DATAFILE=SOURCE_DATAFILE:TARGET_DATAFILE

EXAMPLE : REMAP_DATAFILE='/u01/ora11g/crms/tbs01.dbf':'C:\ora11g\crms\tbs01.dbf'

REMAP_TABLESPACE

Tablespace objects are remapped to another tablespace and changing tablespace definition also. Placing all objects into the specified target, i.e. objects to be moved from one tablespace into another tablespace during an export operation. You can easily import a table into different tablespace from which it was originally exported.

SYNTAX : REMAP_TABLESPACE=SOURCE_TABLESPACE:TARGET_TABLESPACE

EXAMPLE : REMAP_TABLESPACE=USERS:EXAMPLE

$ expdp system/<password> dumpfile=scott.dmp schemas=scott directory=dpdir

(30)

Before import the schema (Scott) into the database, you need to drop it. If your remap tablespace is NOT existing in the database, you need to create it and assign necessary privileges and quotas on the tablespace to Scott user. Let’s start the process.

SYS> select username, default_tablespace from dba_users where username='SCOTT'; USERNAME DEFAULT_TABLESPACE

--- --- SCOTT USERS

SCOTT> select tablespace_name, table_name from user_tables; TABLESPACE_NAME TABLE_NAME --- --- USERS BONUS USERS SALGRADE USERS JOBS USERS JOBS_HISTORY USERS LOCATIONS USERS EMP_CONT_INFO USERS REGIONS USERS DEPT USERS EMP .. ...

EXPORT SCOTT SCHEMA

$ expdp system/<password> dumpfile=scott_remap.dmp logfile=scott_remap.log schemas=scott directory=dpdir

.. ...

[Trimmed]

DROP & RECREATE SCOTT USER

SYS> drop user scott cascade; User dropped.

SYS> create user scott identified by tiger default tablespace users; User created.

SYS> alter user scott temporary tablespace temp; User altered.

SYS> alter user scott quota unlimited on users; User altered.

CREATE NEW TABLESPACE & ASSIGN QUOTA

SYS> create tablespace remap_tbs datafile

'/u01/app/oracle/oradata/crms/remap_tbs01.dbf' size 1000m; Tablespace created

(31)

SYS> alter user scott quota unlimited on remap_tbs; User altered.

SYS> grant connect, create session to scott; Grant succeeded.

IMPORT THE DUMPFILE WITH REMAP_TABLESPACE OPTION

$ impdp system/<password> dumpfile=scott_remap.dmp logfile=scott_remap.log directory=dpdir remap_tablespace=users:remap_tbs

.. ...

[Trimmed]

SCOTT> select tablespace_name, table_name from user_tables; TABLESPACE_NAME TABLE_NAME --- --- REMAP_TBS BONUS REMAP_TBS SALGRADE REMAP_TBS JOBS REMAP_TBS JOBS_HISTORY REMAP_TBS LOCATIONS REMAP_TBS EMP_CONT_INFO REMAP_TBS REGIONS REMAP_TBS DEPT REMAP_TBS EMP .. ...

SYS> select username, default_tablespace from dba_users where username='SCOTT'; USERNAME DEFAULT_TABLESPACE

--- --- SCOTT USERS

DATAPUMP IMPORT : TRANSFORMATIONS

Enables you to alter the object-creation DDL for specific object or all objects being loaded.

DDL TRANSFORMATIONS

Object metadata is stored as XML in the dumpfile set, it is easy to apply transformations when DDL is being formed during import. Data Pump import has many options to transform the metadata during the import operation. Those are REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE and the transform.

SYNTAX AND DESCRIPTION

TRANSFORM=TRANSFORM_NAME:BOOLEAN_VALUE[:OBJECT_TYE] BOOLEAN VALUE IS: Y or N

(32)

In case you do NOT want the object storage attributes but just the table content (data), you can use TRANSFORM parameter that instructs the IMPDP to modify the storage attributes of the DDL that creates the object during import job. Applicable transform_names are given below.

Two basic attributes are SEGMENT_ATTRIBUTE & STORAGE.

SEGMENT_ATTRIBUTES : If the value is specified as Y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included. Default is Y.

STORAGE : If the value is specified as Y, the storage clauses are included. Default is Y EX: INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE, FREELIST, etc...

OID ( OBJECT ID) : Determines whether the object ID of abstract data types is reused or created as new. If the value is specified as N, the generation of the export OID clause for object types is suppressed. This is useful when you need to duplicate schemas across databases by using export and import, but you cannot guarantee that the object types will have identical OID values in those databases. Default is Y.

PCTSPACE : The value supplied for this transformation must be greater than zero. It represents the percentage multiplier that is used to alter extent allocations and the size of data files.

OBJECT_TYPE : This is an optional. If you supply, it applies transformations on the specified object_type. If no object_type is specified then the transform applies to all object_types. Ex: CLUSTER, CONSTRAINT, INDEX, SEGMENT, TABLE, TYPE, etc…

In case, the test database may be very small compared to production volume. When you perform Import, it may fail initial extents are defined too large to fit into the test database.

(33)

Instead of creating tables manually you can remove storage clauses of the tables using TRANSFORM parameter when import. If source and target databases sizes are different, then you can specify DDL of the storage attributes should not be generated while import.

STORAGE removes the storage clause from the create statement of its DDL.

SEGMENT_ATTRIBUTE removes physical attributes, storage attributes, logging and tablespaces.

SEGMENT_CREATION : If it is set to Y (the default), then this transform causes the SQL SEGMENT CREATION clause to be added to the CREATE TABLE statement. i.e., the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE.

If the value is n, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to n to use the default segment creation attributes for the table(s) being loaded. This functionality is available starting with Oracle Database 11g R2 (11.2.0.2).

EXPORT OF SHAM.EMP

$ expdp system/<password> dumpfile=sham_emp.dmp tables=sham.emp directory=dpdir

.. ...

$ impdp system/<password> dumpfile=sham_emp.dmp sqlfile=sham_emp.sql directory=dpdir

.. ...

SQLFILE FOR SHAM_EMP.DMP

$ vi sham_emp.sql ..

...

-- new object type path: TABLE_EXPORT/TABLE/TABLE CREATE TABLE "SHAM"."EMP"

( "EMP_ID" NUMBER,

"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),

"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE, "EMP_DESG" VARCHAR2(16 BYTE),

"ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,

"EMP_TERM_DATE" DATE,

"EMP_LEVEL" VARCHAR2(8 BYTE) ) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TBS1" ;

-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX -- CONNECT SHAM

CREATE UNIQUE INDEX "SHAM"."EMP_EMPID_C1_PK" ON "SHAM"."EMP" ("EMP_ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255