TRANSPORT TABLESPACE MADE SAFE AND EASY...

Migrating data to and fro in a database(or between database) is quite a tedious task when it comes to data loss and other critical issues for the organisation.

But it can be very handy using few commands of DATAPUMP.

Here is the show for you...

We have a tablespace "maptest" which will be tranported.Here is the data contained in this
in a table called "tra"

SQL> select * from ret.tra;

        ID
----------
        10
        20
        30
        40
        10
        20
        30
        40
        10
        20
        30

11  rows selected.

----Make the tablespace read only-----------

SQL> alter tablespace maptest read only;

Tablespace altered.

-----------META DATA of tablespace is exported------------------

SQL> host expdp system/rman dumpfile=trans.dmp logfile=trans.log transport_table
spaces=maptest transport_full_check=y

Export: Release 11.1.0.6.0 - Production on Thursday, 06 January, 2011 16:33:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=trans
.dmp logfile=trans.log transport_tablespaces=maptest transport_full_check=y
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:
  D:\ORACLE\ADMIN\RMAN\DPDUMP\TRANS.DMP
******************************************************************************
Datafiles required for transportable tablespace MAPTEST:
  C:\MAPTEST01.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:35:03

---------------Now connect to target database-----------------

C:\>sqlplus sys/sys@orcl as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 6 16:37:12 2011

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


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

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE

6 rows selected.

----------Copy the required datafile in target database location using OS commands.--------

c:\>copy   <source> to <destination>


SQL> host impdp system/rman dumpfile= trans.dmp transport_full_check=y transport_d
atafiles='d:\oracle\oradata\orcl\maptest01.dbf'


Lets verify the data again...


SQL> select * from ret.tra;

        ID
----------
        10
        20
        30
        40
        10
        20
        30
        40
        10
        20
        30


11 rows selected.



:-) Its safe and correct as you see...

RECOVER DATAFILE WHEN YOU LEFT WITH NO BACKUP

Sometimes you are in a situation where u have no backup at all but have all archive redo-logs intact.So in this situation you can easily recover your datafile without actually use any backup.

Here is the magic starts..........

alter tablespace USERS add datafile ‘/u02/oradata/mydatabase/users02.dbf’ size 10M;

here you see the list of datafiles....

select FILE_ID, FILE_NAME, ONLINE_STATUS, STATUS from dba_data_files;

FILE_ID FILE_NAME

———- ————————————————–

1 /u02/oradata/mydatabase/system01.dbf

2 /u02/oradata/mydatabase/undotbs01.dbf

3 /u02/oradata/mydatabase/sysaux01.dbf

4 /u02/oradata/mydatabase/users01.dbf

5 /u02/oradata/mydatabase/users02.dbf

Now we remove the recently added file i.e.users02.dbf

SQL> startup mount

SQL> alter database datafile 5 offline;

SQL> alter database open;

SQL> alter database create datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’;

SQL> recover datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’;

Type:

auto

SQL> alter database datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’ online;

:-) Try this one out..

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", ORA-29283: invalid file operation

This problem is encountered when you try to import a data-pump dump file.
Actually, it is somewhere related to permission issues and also due to mismatch of the steps performed during import operation.

Solution : Perform the below steps to resolve this issue(---in sequence)

1.)Create data-pump directory
--Create directory datapump as 'c:\data\';

2.) Grant permission now to this directory

--Grant read,write on directory datapump to SCOTT;

3.)Copy the desired dump-file at this location('c:\data\')

4.)Start import now..

:-)best of luck