Tuesday, January 5, 2010

HOW TO DO TRANSPORT TABLESPACE IN ORACLE 10G

HOW TO DO TRANSPORT TABLESPACE IN ORACLE 10G:

Transportable Tablespace demo:
================================
Transportable Tablespace method is one of the fastest method to move data/tablespaces between/around oracle databases and can also move a tablespace across same and different platforms.
We can perform entire database transport between platforms using same concept.
what platforms a tablespace can be moved is seen using v$transportable_platform and main befit of this is fastest move.
Note:
char,nchar sets (character sets) should be similar in both source and target systems.
there should not be tablespace with same name in target db system, either tablespace name should be renamed in source or target system.
all the objects should be in same tablespace and incase any object resides in tablespace points another object which resides in another tablespace, then both tablespaces should be included.i.e. it should be self containing.
Ensure that the both source and target systems use same endian settings otherwise converting of datafiles using RMAN is needed

This can be done on either the source platform or the target platform db system.
RMAN> CONVERT TABLESPACE ‘tablespacename’
TO PLATFORM = ‘targetplatformname’
DB_FILE_NAME_CONVERT = ‘source.dbf’, ‘target.dbf’
---

Transportable Tablespace demo:

On source system:
-----------------

CREATE TABLESPACE TABLESPACE2
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TABLESPACE2.DBF'
SIZE 10M REUSE AUTOEXTEND ON MAXSIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLE SCOTT.items(
itemtype VARCHAR2(5) NOT NULL PRIMARY KEY
,description VARCHAR2(20) NOT NULL
)
TABLESPACE TABLESPACE2;


CREATE TABLE scott.ctype (
ctype VARCHAR2(5) NOT NULL PRIMARY KEY
,description VARCHAR2(20) NOT NULL
)
TABLESPACE TABLESPACE2;

insert data into two tables;

create a oracle directory
mkdir d:\ttexports;
create directory ttexports as 'd:\ttexports';
GRANT READ, WRITE ON DIRECTORY ttxports TO PUBLIC;

expdp system/xxxx job_name=ttexport1 directory=ttexports dumpfile=ttexport1.dmp logfile=ttsexport1.log transport_tablespaces=tablespace2 transport_full_check=true;

exp file=exp1.dmp log=exp1.log TRANSPORT_TABL
ESPACE=y TABLESPACES=TABLESPACE2;
or
exp USERID='sys/**** AS SYSDBA' TRANSPORT_TABLESPACE=y TABLESPACES=TABLESPACE2 FILE=TABLESPACE2.dmp

copy all the dump fles and dbf files to move target system

alter tablespace tablespace2 read write;

move all the dump files along with db files to target system... and move dbf files to target db folder..

on target system:
------------------
create a oracle directory
mkdir d:\ttimports;
create directory ttimports as 'd:\ttimports';
GRANT READ, WRITE ON DIRECTORY ttimports TO PUBLIC;

IMPDP JOB_NAME = TTSIMPORT1 DIRECTORY = ttimports DUMPFILE = TTEXPORT1.dmp LOGFILE = ttsimport1.log TRANSPORT_DATAFILES = d:\ttimports\TABLESPACE2.DBF

imp USERID='system/xxxx AS SYSDBA' TRANSPORT_TABLESPACE=y DATAFILES='d:\ttimports\TABLESPACE2.DBF' TABLESPACES=TABLESPACE2 FILE=TTEXPORT1.dmp

login into target db system scott user

select * from tab;
select * from items;
select * from ctype;

you will be able to see the data from tables

HOW TO DO TRANSPORT TABLESPACE IN ORACLE 10G

HOW TO DO TRANSPORT TABLESPACE IN ORACLE 10G:

Transportable Tablespace demo:
================================
Transportable Tablespace method is one of the fastest method to move data/tablespaces between/around oracle databases and can also move a tablespace across same and different platforms.
We can perform entire database transport between platforms using same concept.
what platforms a tablespace can be moved is seen using v$transportable_platform and main befit of this is fastest move.
Note:
char,nchar sets (character sets) should be similar in both source and target systems.
there should not be tablespace with same name in target db system, either tablespace name should be renamed in source or target system.
all the objects should be in same tablespace and incase any object resides in tablespace points another object which resides in another tablespace, then both tablespaces should be included.i.e. it should be self containing.
Ensure that the both source and target systems use same endian settings otherwise converting of datafiles using RMAN is needed

This can be done on either the source platform or the target platform db system.
RMAN> CONVERT TABLESPACE ‘tablespacename’
TO PLATFORM = ‘targetplatformname’
DB_FILE_NAME_CONVERT = ‘source.dbf’, ‘target.dbf’
---

Transportable Tablespace demo:

On source system:
-----------------

CREATE TABLESPACE TABLESPACE2
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TABLESPACE2.DBF'
SIZE 10M REUSE AUTOEXTEND ON MAXSIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLE SCOTT.items(
itemtype VARCHAR2(5) NOT NULL PRIMARY KEY
,description VARCHAR2(20) NOT NULL
)
TABLESPACE TABLESPACE2;


CREATE TABLE scott.ctype (
ctype VARCHAR2(5) NOT NULL PRIMARY KEY
,description VARCHAR2(20) NOT NULL
)
TABLESPACE TABLESPACE2;

insert data into two tables;

create a oracle directory
mkdir d:\ttexports;
create directory ttexports as 'd:\ttexports';
GRANT READ, WRITE ON DIRECTORY ttxports TO PUBLIC;

expdp system/xxxx job_name=ttexport1 directory=ttexports dumpfile=ttexport1.dmp logfile=ttsexport1.log transport_tablespaces=tablespace2 transport_full_check=true;

exp file=exp1.dmp log=exp1.log TRANSPORT_TABL
ESPACE=y TABLESPACES=TABLESPACE2;
or
exp USERID='sys/**** AS SYSDBA' TRANSPORT_TABLESPACE=y TABLESPACES=TABLESPACE2 FILE=TABLESPACE2.dmp

copy all the dump fles and dbf files to move target system

alter tablespace tablespace2 read write;

move all the dump files along with db files to target system... and move dbf files to target db folder..

on target system:
------------------
create a oracle directory
mkdir d:\ttimports;
create directory ttimports as 'd:\ttimports';
GRANT READ, WRITE ON DIRECTORY ttimports TO PUBLIC;

IMPDP JOB_NAME = TTSIMPORT1 DIRECTORY = ttimports DUMPFILE = TTEXPORT1.dmp LOGFILE = ttsimport1.log TRANSPORT_DATAFILES = d:\ttimports\TABLESPACE2.DBF

imp USERID='system/xxxx AS SYSDBA' TRANSPORT_TABLESPACE=y DATAFILES='d:\ttimports\TABLESPACE2.DBF' TABLESPACES=TABLESPACE2 FILE=TTEXPORT1.dmp

login into target db system scott user

select * from tab;
select * from items;
select * from ctype;

you will be able to see the data from tables