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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: