Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

Oracle transportable tablespaces

Below I will show you how to use transportable tablespace feature in Oracle:

Creating the tablespace for the demo:

SQL> create tablespace tt_temp_sales datafile '+DATA2' size 10m autoextend on next 1m;

Tablespace created.

SQL> conn sh/sh
Connected.

Creating a table for verification after transportable tablespace:

SQL> create table temp_jan_sales tablespace tt_temp_sales as select * from sales  where time_id between '31-DEC-1999' AND '01-feb-2000';

Table created.

Make the tablespace read-only

SQL> alter tablespace tt_temp_sales read only;

Tablespace altered.


SQL> create directory orcl_dir as '/home/oracle/sshaik';

Directory created.

SQL> grant all on directory orcl_dir to sh;

Grant succeeded.



[oracle@collabn1 ~]$ expdp system dumpfile=orcl_dir:tt_temp_Sales.dmp logfile=orcl_dir:tt_temp_sales.log TRANSPORT_TABLESPACES=tt_temp_sales

Export: Release 11.2.0.1.0 - Production on Fri Aug 7 17:22:55 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=orcl_dir:tt_temp_Sales.dmp logfile=orcl_dir:tt_temp_sales.log TRANSPORT_TABLESPACES=tt_temp_sales
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:
 /home/oracle/sshaik/tt_temp_Sales.dmp
******************************************************************************
Datafiles required for transportable tablespace TT_TEMP_SALES:
 +DATA2/orcl/datafile/tt_temp_sales.319.887131035
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:23:32

Change back the tablespace to READ-WRITE:

SQL> alter tablespace tt_temp_sales read write;

Tablespace altered.


[oracle@collabn1 ~]$ asmcmd
ASMCMD> cd data2
ASMCMD> cd orcl    
ASMCMD> cd datafile
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 07 14:00:00  Y    HR_TEST.283.886951885
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    SYSAUX.265.886783809
DATAFILE  UNPROT  COARSE   AUG 07 14:00:00  Y    SYSTEM.266.886783809
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    TT_TEMP_SALES.319.887131035
DATAFILE  UNPROT  COARSE   AUG 07 14:00:00  Y    UNDOTBS1.264.886783809
DATAFILE  UNPROT  COARSE   AUG 07 14:00:00  Y    UNKNOWN.256.886783857
DATAFILE  UNPROT  COARSE   AUG 07 16:00:00  Y    USERS.288.886868147

Copy the datafile from ASM to the local disk:
or
You can use dbms_copy and so on..

ASMCMD> cp tt_temp_sales.319.887131035 /home/oracle/sshaik/
copying +data2/orcl/datafile/tt_temp_sales.319.887131035 -> /home/oracle/sshaik//tt_temp_sales.319.887131035
ASMCMD>   


Transfer the files to target:

[oracle@collabn1 sshaik]$ ls -lrt /home/oracle/sshaik//tt_temp_sales.319.887131035
-rw-r----- 1 oracle oinstall 10493952 Aug  7 17:32 /home/oracle/sshaik//tt_temp_sales.319.887131035

[oracle@collabn1 sshaik]$ scp /home/oracle/sshaik//tt_temp_sales.319.887131035 collabn2.shaiksameer:$PWD
tt_temp_sales.319.887131035                                                                              100%   10MB  10.0MB/s   00:00    
    
[oracle@collabn1 sshaik]$ scp tt_temp_Sales.dmp collabn2.shaiksameer:$PWD
tt_temp_Sales.dmp                                                                                        100%   92KB  92.0KB/s   00:00    
[oracle@collabn1 sshaik]$


Verify the files on Target:


[oracle@collabn2 sshaik]$ ls -lrt
total 10340
-rw-r----- 1 oracle oinstall 10493952 Aug  7 17:33 tt_temp_sales.319.887131035
-rw-r----- 1 oracle oinstall    94208 Aug  7 17:35 tt_temp_Sales.dmp
[oracle@collabn2 sshaik]$


Create directory On target:

SQL> create directory prod1_dir as '/home/oracle/sshaik';

Directory created.

[oracle@collabn2 ~]$ cd sshaik/

[oracle@collabn2 sshaik]$ ls -lrt
total 10344
-rw-r----- 1 oracle oinstall 10493952 Aug  7 17:33 tt_temp_sales.319.887131035
-rw-r----- 1 oracle oinstall    94208 Aug  7 17:35 tt_temp_Sales.dmp
-rw-r--r-- 1 oracle dba          1032 Aug  7 17:49 tt_temp_sales_imp.log

[oracle@collabn2 sshaik]$ asmcmd
ASMCMD> cd data2

ASMCMD> cd prod1/datafile
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    RCTS.280.886861183
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    SYSAUX.269.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    SYSTEM.268.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    UNDOTBS1.270.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    UNKNOWN.278.886859439
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    USERS.271.886859391

Copy the datafile into  Target ASM.

ASMCMD> cp /home/oracle/sshaik/tt_temp_sales.319.887131035 '+DATA2/prod1/datafile/tt_temp_sales'
copying /home/oracle/sshaik/tt_temp_sales.319.887131035 -> +DATA2/prod1/datafile/tt_temp_sales

Since ASM will automatically append file/incarnation pair to ensure uniqueness, you will have give the name except the incarnation number….


ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    RCTS.280.886861183
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    SYSAUX.269.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    SYSTEM.268.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    UNDOTBS1.270.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    UNKNOWN.278.886859439
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  Y    USERS.271.886859391
DATAFILE  UNPROT  COARSE   AUG 07 17:00:00  N    tt_temp_sales => +DATA2/ASM/DATAFILE/tt_temp_sales.321.887133535

[oracle@collabn2 sshaik]$ impdp system directory=prod1_dir dumpfile=tt_temp_Sales.dmp logfile=tt_temp_sales.log transport_datafiles='+data2/prod1/datafile/tt_temp_sales'

Import: Release 11.2.0.1.0 - Production on Fri Aug 7 18:33:02 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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=prod1_dir dumpfile=tt_temp_Sales.dmp logfile=tt_temp_sales.log transport_datafiles=+data2/prod1/datafile/tt_temp_sales
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 18:33:07


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD1      READ WRITE


SQL> alter tablespace tt_temp_sales read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TT_TEMP_SALES';

TABLESPACE_NAME            STATUS
------------------------------ ---------
TT_TEMP_SALES              ONLINE

SQL> select count(*) from sh.temp_jan_sales;

 COUNT(*)
----------
    22594

No comments: