Query Parameter in Datapump (EXPDP/IMPDP) utility

Sometimes we need to export or imprt only some data from a huge table. This can be done by using query parameter in Datapump utility. Query parameter can be used in both EXPDP as well as IMPDP utility.

1. EXPDP :-

Query parameter can be used in two ways  :-

a. Using parameter file :-

bash-3.2$
bash-3.2$ cat test.par
TABLES=AMIT
directory=datapump
DUMPFILE=expdp_test.dmp
logfile=expdp_test.log
query=amit:"where DATE > '01-DEC-2013'"
bash-3.2$

bash-3.2$ expdp user/user parfile=test.par

Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:20:47 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":   user/****** parfile=test.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_etst.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:20:53

b. In Command Line :-

expdp user/user directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:\"where DATE \> \'01-DEC-2013\'\" tables=AMIT

Export: Release 11.2.0.1.0 - Production on Fri Dec 13 12:24:50 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER"."SYS_EXPORT_TABLE_01":  user/******** directory=datapump dumpfile=expdp_taa_01.dmp logfile=expdp_taa_01.log query=amit:"where DATE > '01-DEC-2013'" tables=AMIT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USER"."AMIT"             30.67 KB      75 rows
Master table "USER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER.SYS_EXPORT_TABLE_01 is:
  /disk1/datapump/expdp_taa_01.dmp
Job "USER"."SYS_EXPORT_TABLE_01" successfully completed at 12:24:56

2. IMPDP :-

a. Using par file :-

bash-3.2$ cat test.par

schemas=DATA
include=TABLE:"IN('TEST')"
directory=datapump
DUMPFILE=expdp_fullDB_12_Dec_13.dmp
logfile=expdp_etst.log
query=test:"where DATE > '01-DEC-2013'"
remap_schema=DATA:AMIT

bash-3.2$ impdp amit/amit parfile=test.par

Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:44:22 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** parfile=test.par
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:44:44

b. In Command line :-

bash-3.2$ impdp amit/amit directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=TEST:\"where DATE \> \'01-DEC-2013\'\" schemas= DATA  include=TABLE:\"IN\(\'TEST\'\)\" remap_schema=DATA:AMIT

Import: Release 11.2.0.1.0 - Production on Fri Dec 13 12:38:29 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "AMIT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "AMIT"."SYS_IMPORT_SCHEMA_01":  amit/******** directory=datapump dumpfile=expdp_fullDB_12_Dec_13.dmp logfile=test.log query=test:"where DATE > '01-DEC-2013'" schemas=DATA include=TABLE:"IN('TEST')" remap_schema=DATA:AMIT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "AMIT"."TEST"                    51.17 KB      75 out of 1614 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "AMIT"."SYS_IMPORT_SCHEMA_01" successfully completed at 12:38:55

I hope this article helped you.

Regards,
Amit Rath

How to restore a dropped table using Flashback Drop

Flashback Drop is a feature in Oracle Database through which we can restore table which has been accidentally dropped.

To use this feature FLASHBACK has to be enabled in your database . To enable FlASHBACK
visit Enable Flashback in Oracle

Database has to be in Archive log mode. To enable Archive log mode in Database visit Change Archive mode of Database

This feature uses recycle bin to restore dropped table. We can specify either the name of the table in recycle bin or the original table name to restore. We can also rename the table while restoring it from the recycle bin using "RENAME TO" clause.

PFB example to restore a table and its dependent objects through Flashback Drop :-

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                    TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV   VIEW                               VALID

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

8 rows selected.

SQL> drop table AMIT cascade constraints;

Table dropped.

########### if we use purge in Drop command then we cannot restore it from recycle bin##########

SQL> drop table AMIT cascade constraints purge;

Table dropped.

Query the user_recyclebin to get the details of dropped table and its dependents. Recyclebin is the synonym for user_recyclebin. The system generated name of the dropped object is unique across database. Dropping a table drop the associated indexes , triggers but not Procedures , Functions and views . 

When we restore a table from Recycle bin , the dependent objects such as triggers , indexes etc do not get their original names back, they have their system generated names with them . We have to manually rename them after the table restore operation. So before restoring the table from Recycle bin, make a note of the system generated recycle bin names of the dependent objects. 

After dropping AMIT table , before restoring it run below query :-

SQL> select  object_name, original_name,type, createtime FROM recyclebin;

OBJECT_NAME                                                             ORIGINAL_NAME                    TYPE                      CREATETIME
-----------------------------------------------------------------          --------------------------------   ------------------------- -------------------
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                         D_UI                      INDEX   2013-07-01:11:03:38
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                       ID_NI                     INDEX   2013-07-01:11:03:38
BIN$YagDo75NS0enszVEvCtv+g==$0                           D_NI                      INDEX   2013-07-01:11:03:38
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                           ORDER_NI            INDEX   2013-07-01:11:03:38
BIN$kow3P8xOSwC1fzhLep5wgg==$0                          USERID                 INDEX   2013-07-01:11:03:38
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                              ID_TIME               INDEX   2013-07-01:11:03:39
BIN$MPqYce8sQLGUoxTor3zMVw==$0                        H_NI                      INDEX   2013-07-01:11:03:39
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                       DEACT_H_NI        INDEX   2013-07-01:11:03:39
BIN$kzeXKoZoSwqycBiH6X8FzA==$0                          AMIT_TT              TRIGGER   2013-07-09:12:48:03
BIN$C5EACqXLRZyACHAhLjC/LQ==$0                       AMIT                     TABLE   2013-07-01:11:02:10

Now restore table through Flashback drop :-

SQL> flashback table AMIT to before drop;

Flashback complete.

############ if you want to rename AMIT to TEST then use RENAME ################

SQL> flashback table AMIT to before drop rename to TEST;

Flashback complete

Once the table has been restored , dependent objects also have been restored. Dependent objects like indexes , triggers have their system generated names with them and are in Valid state. Dependent objects like procedures , Functions, View associated with table are in invalid state . We have to compile them manually to make them valid.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME                                                             STATUS
------------------------------------------------------                  --------
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                  VALID
BIN$MPqYce8sQLGUoxTor3zMVw==$0                  VALID
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                       VALID
BIN$kow3P8xOSwC1fzhLep5wgg==$0                    VALID
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                     VALID
BIN$YagDo75NS0enszVEvCtv+g==$0                    VALID
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                 VALID
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                    VALID

8 rows selected.

SQL> select TRIGGER_NAME,status from user_triggers where TABLE_NAME='AMIT';

TRIGGER_NAME                                          STATUS
------------------------------                                  --------
BIN$kzeXKoZoSwqycBiH6X8FzA==$0        ENABLED

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 INVALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_VV    VIEW                               INVALID

We have to manully rename Indexes and triggers according to the note we have made before restoring.

SQL> alter trigger "BIN$kzeXKoZoSwqycBiH6X8FzA==$0" rename to AMIT_TT;

Trigger altered.

SQL> alter index "BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0" rename to ID_NI;

Index altered.

SQL> alter index "BIN$MPqYce8sQLGUoxTor3zMVw==$0" rename to D_UI;

Like this rename all the indexes . Now compile the procedures and Views.

SQL> alter view AMIT_VV compile;

View altered.

SQL> alter procedure AMIT_PP compile;

Procedure altered.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV    VIEW                               VALID

Restoration of table from Recylebin completed.

I hope this article helped you.

Regards,
Amit Rath

Cross platform transportable tablespaces in oracle 11g

Cross platform transportable tablespace is a term which is used when we have to transport tablespaces between databases having different endian formats between source and destination platforms. Endian Formats of the datafiles must be converted to match the destination platforms.

This conversion can be performed in two ways :-
1. RMAN convert tablespace , converting on source host.
2. RMAN convert datafile , converting on target host.

This feature is compatible from Oracle Database Release 10.1 and above.

Limitations :-

1. Source Database and Target Database must have same Character set and National Character set.
2. Target database must not have tablespace with same name which has to be transported from Source.
3. Target database version must have to be same or higher than source database version. (exporting using parameter VERSION in expdp does not work here)

PFB steps to transport a tablespace  :-

1. Conversion to be done at source DB from AIX 64 bit system to Microsoft Windows IA (32-bit) :-

 a. Operating system detail on source and target system :-

SQL > select  * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          1 Solaris[tm] OE (32-bit)                       Big
          2 Solaris[tm] OE (64-bit)                       Big
          7 Microsoft Windows IA (32-bit)                 Little
         10 Linux IA (32-bit)                             Little
          6 AIX-Based Systems (64-bit)                    Big
          3 HP-UX (64-bit)                                Big
          5 HP Tru64 UNIX                                 Little
          4 HP-UX IA (64-bit)                             Big
         11 Linux IA (64-bit)                             Little
         15 HP Open VMS                                   Little
          8 Microsoft Windows IA (64-bit)                 Little

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          9 IBM zSeries Based Linux                       Big
         13 Linux x86 64-bit                              Little
         16 Apple Mac OS                                  Big
         12 Microsoft Windows x86 64-bit                  Little
         17 Solaris Operating System (x86)                Little
         18 IBM Power Based Linux                         Big
         19 HP IA Open VMS                                Little
         20 Solaris Operating System (x86-64)             Little
         21 Apple Mac OS (x86-64)                         Little

b. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

c. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

d. Connect to RMAN at source DB and convert tablespace AMIT

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:38:44 2013

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

connected to target database: CDR (DBID=903650087)

RMAN> convert tablespace amit to platform 'Microsoft Windows IA (32-bit)' format ='/backup/%U';

Starting conversion at source at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3237 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00033 name=/disk1/oracle/oradata/amit.dbf
converted datafile=/backup/data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 03-JUL-13

e. Export Metadata of the tablespace

bash-3.2$ expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT &
[1] 50855988

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
bash-3.2$
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /disk1/daapump/transportable_tablespace.dmp
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  /disk1/oracle/oradata/amit.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

[1]+  Done                    expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT
bash-3.2$

f. Move the dumpfile and datafile to the target database where target database has permissions to access that

g. Plug the tablespace into target database by using IMPORT and RMAN

C:\Users\amit.rath\Downloads> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:12 2013

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

connected to target database: ORCL (DBID=1345007053)

RMAN> convert datafile 'C:\Users\amit.rath\Downloads\data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b' format 'C:\app\amit.rath\oradata\orcl\amit.dbf' ;

Starting conversion at target at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=C:\USERS\AMIT.RATH\DOWNLOADS\DATA_D-CDR_I-903650087_TS-AMIT_FNO-33_6AODQT3B
converted datafile=C:\APP\AMIT.RATH\ORADATA\ORCL\AMIT.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 03-JUL-13

RMAN> exit

C:\Users\amit.rath\Downloads>impdp system/orcl directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'

Import: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:36 2013

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

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
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:17:38

2. Conversion to be done at Target DB from Microsoft Windows IA (32-bit) to AIX 64 bit system :-

a. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

b. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

c. Export Metadata of the tablespace in source DB 

expdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_tablespaces = EEEE 

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\app\datapump\EXPDP_TRANS_EEEE.DMP
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  C:\app\EEEE.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

d. Move the datafile and export dump to the target DB where it has permission to access those.

e. Convert the datafile using RMAN utility

RMAN> convert datafile '/disk2/oracle/EEEE.DBF' DB_FILE_NAME_CONVERT '/disk2/oracle/' , '/disk2/oracle/oradata/pacs' FROM PLATFORM 'Microsoft Windows IA (32-bit)' ;

Starting conversion at target at 05-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/disk2/oracle/EEEE.DBF
converted datafile=/disk2/oracle/oradata/pacsEEEE.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 05-JUL-13

f. Use the IMPDP utility to import the metadata of the tablespace

bash-3.2$ impdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles= '/disk2/oracle/oradata/pacsEEEE.DBF'

Import: Release 11.2.0.1.0 - Production on Fri Jul 5 13:58:22 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles=
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:58:25

g. check that the tablespace has been successfully added to that target DB 

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EEEEE

6 rows selected.

SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='EEEEE';

SEGMENT_NAME              SEGMENT_TYPE       OWNER
------------------------- ------------------ ------------------------------
AAAAA                     TABLE              AMIT

NOTE :- If you want to change the owner of the objects associated with the tablespace then while doing import you can use remap_schema parameter to do that.

I hope this article helped you.

Regards,
Amit Rath

EXPDP and IMPDP utility

Expdp and Impdp utility are datapump utilities They are used to take logical backup of the database. They can be used to take database level, schema level, object level , tablespace level backups.

Command for Full Database export :-

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &   ----- (if database is 11g)

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=METADATA_ONLY full=y &   ----- (if database is 10g)

############# Compression = all is not available in 10g version##############

Command for Full Database import :-

1. First create all the tablespaces which are there in old database either with same name or with different name. If tablespace names are different then remap_tablespace has to be used.

2. If you want to create new users then while importing remap_schema has to be used, otherwise it will create users with the same name.

Schema with same name and tablespaces with same name as they are in old database then PFB command to import full database  :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y

Schema and tablespaces with different name then PFB command to import full database :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y remap_schema= amit:test,amit1:test1,amit2:test2 remap_tablespace=amit_tbs:test_tbs,amit1_tbs:test1_tbs,amit2_tbs:test2_tbs &

Schema Level Export :-

C:\Users\NewAdmin>expdp amit/amit directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=expdp_schema_29062013.log compression=all &

Schema level import :-

If we want to import Amit schema contents to test schema contents and that to in tablespace of test schema the PFB command :-

C:\Users\NewAdmin>impdp test/test directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=impdp_schema_29062013.log remap_schema=srilanka:test remap_tablespace=amit_tbs:test_tbs &

Schema Level export without data :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=METADATA_ONLY compression=all &

Schema Level export without metadata :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=DATA_ONLY compression=all &

Table Level Export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_table_29062013.dmp logfile=expdp_table_29062013.log tables=temp,temp1 compression=all &  

Table Level Import :-

C:\Users\NewAdmin> impdp test/test directory=datapump  dumpfile=expdp_table_29062013.dmp logfile=impdp_table_29062013.log remap_schema=amit:test &

Table Level Import from a schema or database level dump :-

impdp test/test schemas=amit directory=datapump dumpfile=exp_am.dmp remap_schema=amit:test INCLUDE=TABLE:\"IN \(\'DB_T_ALARMS_TTT\'\)\"

Exclude some table or index from a schema level export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" compression=all &

Exclude Some Schema and some tables from a Full Database Export :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" exclude=schema:\"in\(\'AMIT\'\)\" compression=all full=y &

I hope this article helped you.

Regards,
Amit Rath

How to kill, cancel, restart, stop data pump jobs

Datapump jobs (expdp, impdp) can be stopped, killed or resumed from the database level. Killing or stopping a datapump job from Oslevel does not kill or stop it, we have to do it from database . PFB :-

Exporting a Full database backup :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 19:46:41 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Stop a datapump job :-

Datapump jobs can be stopped in two ways, either press CTRL + C you will get EXPDP prompt or attach to a already running job and after that write STOP_JOB

If datapump job is not running in background then once you press CTRL+C you will get EXPDP prompt . PFB :-

Export> STOP_JOB

Are you sure you wish to stop this job ([yes]/no): y

Datapump job is stopped.

if datapump is running in background then you have to attach a datapump job, find out the datapump job name by using below query :-

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE

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

SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      EXECUTING

Now attach to a running job:-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:01:45 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02

  Owner: SYSTEM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 42902560C22D46BDA6924724126F59FD

  Start Time: Saturday, 29 June, 2013 20:01:08

  Mode: FULL

  Instance: orcl

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y

     COMPRESSION           ALL

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: C:\DATAPUMP\EXPDP_FULL_29062013_1.DMP

    bytes written: 4,096

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING

Export> STOP_JOB

Are you sure you wish to stop this job ([yes]/no): y

you will get a message like this :-

Job "SYSTEM"."SYS_EXPORT_FULL_02" stopped by user request at 20:02:01

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE

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

SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      NOT RUNNING

Restart a Stopped datapump Job :-

Attach to a stopped datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:08:17 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02

  Owner: SYSTEM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 42902560C22D46BDA6924724126F59FD

  Start Time: Saturday, 29 June, 2013 20:08:20

  Mode: FULL

  Instance: orcl

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y

     COMPRESSION           ALL

  State: IDLING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: C:\datapump\expdp_full_29062013_1.dmp

    bytes written: 4,096

Worker 1 Status:

  Process Name: DW00

  State: UNDEFINED

Export> START_JOB

Export> CONTINUE_CLIENT

Job SYS_EXPORT_FULL_02 has been reopened at Saturday, 29 June, 2013 20:08

Restarting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y

Processing object type DATABASE_EXPORT/TABLESPACE

Job is again restarted.

Kill a datapump job :-

Attach to a running datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:12:55 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01

  Owner: SYSTEM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 452263D9B00047A9BCD6E97DE83878F4

  Start Time: Saturday, 29 June, 2013 20:12:57

  Mode: FULL

  Instance: orcl

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y

     COMPRESSION           ALL

  State: IDLING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: C:\datapump\expdp_full_29062013.dmp

    bytes written: 5,570,560

Worker 1 Status:

  Process Name: DW00

  State: RUNNING

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): y

DIfference between KILLING and STOPPING a datapump job is that, once you killed a datapump job you cannot able to restart it . But if we stop a datapump job we can restart it. Killing a datapump job will delete the dump and log files also.

I hope this article helped you.

Regards,

Amit Rath

RMAN backup of Oracle Database

Backing up Oracle Database using RMAN in background :-

HOT Backup( Database in Archive log mode) :-

1. Create a RCV file for backup.
[cognos@rac1 ~]$ vi compressed_bkp_20_OCT_12.rcv

contents inside this file :-

run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
sql 'alter system archive log current';
backup as compressed backupset database plus archivelog delete input format '/backup/BKPSETS_20OCT12_%U' filesperset 8;
backup as compressed backupset format '/backup/BKPSETS_20OCT12_CONTROLFILE_%U' current controlfile;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
 
2. create a new file backup.

[cognos@rac1 ~]$ vi rman_hotbackup.sh

 contents inside this file 

 cd 
. ./.bash_profile
rman target / cmdfile=compressed_bkp_20_OCT_12.rcv log=backup_20_OCT_12.log << EOF
exit


3. Run this file from oracle user prompt to start backup
[cognos@rac1 ~]$ sh rman_hotbackup.sh >> rman_hotbackup.txt & 

COLD Backup( Database in NOArchive log mode) :- 

1. create a RCV file for backup.
[cognos@rac1 ~]$ vi compressed_bkp_20_OCT_12.rcv

contents inside this file :-

run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
backup as compressed backupset database format '/backup/BKPSETS_20OCT12_%U' filesperset 8;
backup as compressed backupset format '/backup/BKPSETS_20OCT12_CONTROLFILE_%U' current controlfile;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
}
 
2. create a new file backup.
[cognos@rac1 ~]$ vi rman_coldbackup.sh

 contents inside this file

 cd 
. ./.bash_profile
rman target / cmdfile=compressed_bkp_20_OCT_12.rcv log=backup_20_OCT_12.log << EOF
exit


3. Run this file from oracle user prompt to start backup
[cognos@rac1 ~]$ sh rman_coldbackup.sh >> rman_coldbackup.txt &

NOTE :-- For taking Cold backup(Consistent backup) through RMAN database must be in MOUNT mode.

Backup of only Archive logs through RMAN :-
  
1. Backup all Archive logs :-

[cognos@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 18:35:40 2012

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

connected to target database: ORCL (DBID=1318569746)

 RMAN> run
 {
 allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
 backup as compressed backupset archivelog all delete input format '/backup/archive/arc_%U';
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}


2. Backup all archivelogs between dates eg :- created more than 7 and less than 30 days ago.

RMAN> run
2> {
3> allocate channel t1 type disk;
4>allocate channel t2 type disk;
5>allocate channel t3 type disk;
6>allocate channel t4 type disk;
7> 

backup as compressed backupset

 archivelog from time 'SYSDATE-30' until time 'SYSDATE - 7' format '/backup/archive/arc_%U';
8>release channel t1;
9>release channel t2;
10>release channel t3;
11>release channel t4;
12 }

3. Backup archivelogs between log sequence eg:- from sequence 120 to 220

RMAN> run
2> {
3> allocate channel t1 type disk;
4>allocate channel t2 type disk;
5>allocate channel t3 type disk;
6>allocate channel t4 type disk;
7> 

backup as compressed backupset

 archivelog from logseq 120 until logseq 220 thread 1 format '/backup/archive/arc_%U';
8>release channel t1;
9>release channel t2;
10>release channel t3;
11>release channel t4;
12 }

I Hope this article helped you.

Regards,
Amit Rath