Cross Platform Transportable Tablespace
I am going to write transport tablespace from one plateform to another platform step by step.
Note:This is only for study purpose. If you experiment on production database its own risk.
Scenario
Source
Database : Oracle 10gR2 Platform : Linux 32 bit
Target
Database : Oracle 10gR2
Platform : Windows XP 32 bit
Step 1
The Target and Source db characterset and national characterset should be identical
Source Database
SQL> set pages 500
SQL> set line 100
SQL> COL PROPERTY_NAME FORMAT A30
SQL> COL PROPERTY_VALUE FORMAT A30
SQL> COL DESCRIPTION FORMAT A30
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%CHARACTERSET%';
Output
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8ISO8859P1 Character set
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
Target Database
SQL> set pages 500
SQL> set line 100
SQL> COL PROPERTY_NAME FORMAT A30
SQL> COL PROPERTY_VALUE FORMAT A30
SQL> COL DESCRIPTION FORMAT A30
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%CHARACTERSET%';
OUTPUT
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET WE8ISO8859P1 Character set
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
If NLS_CHARACTERSET is not same on source and target Database then follow following step on the target database
SQL> connect sys/**********@ORCL as sysdba
SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';
SQL> UPDATE PROPS$ SET VALUE$='WE8ISO8859P1' WHERE NAME LIKE 'NLS_CHARACTERSET';
SQL> COMMIT;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';
STEP 2
Compatible
Target Database
SQL> SHOW PARAMETER COMPATIBLE;
Source Database
SQL> SHOW PARAMETER COMPATIBLE;
Block Size
From Oracle 9i onwords we can transport tablespace across the database with different standard block size.
STEP 3
Create tablespace on source database( Linux )
SQL> connect sys/****** as sysdba
SQL> CREATE TABLESPACE sales_delhi
DATAFILE '/u03/app/oracle/product/10.2.0/oradata/sales_delhi01.dbf' SIZE 200M;
SQL> CREATE USER dilip IDENTIFIED BY dilip ACCOUNT UNLOCK;
SQL> GRANT CREATE SESSION TO DILIP;
SQL> GRANT CREATE TABLE, SELECT ANY TABLE TO DILIP;
SQL> GRANT DROP TABLE TO DILIP;
SQL> GRANT UPDATE ANY TABLE TO DILIP;
SQL> CONNECT DILIP/dilip;
SQL> CREATE TABLE ORDER( ORDER_ID NUMBER(5) PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
ADDRESS VARCHAR2(30),
PRODUCT_NAME VARCHAR2(15) NOT NULL,
QTY NUMBER(3),
ORDER_DATE DATE,
SALES_PERSON VARCHAR2(20))
TABLESPACE SALES_DELHI;
Enter order data collected by sales person
SQL> commit;
SQL>select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';
STEP 4
Transport tablespace SALES_DELHI from Linux 32 bit platform to Windows XP 32 bit platform.
First we validate whether the tablespace SALES_DELHI is self contained or not. Execute following query.
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('SALES_DELHI',TRUE,TRUE);
OUTPUT
PL/SQL procedure successfully complited.
SQL> SELECT * FROM FROM TRANSPORT_SET_VIOLATIONS;
OUTPUT
No ROW SELECTED.
STEP 5
Now I put the tablespace in READ ONLY mode.
SQL> ALTER TABLESPACE SALES_DELHI READ ONLY;
SQL> SELECT tablespace_name,status FROM DBA_TABLESPACES;
STEP 6
Now I export tablespace with DATAPUMP(expdp) and I use the TRANSPORT_TABLESPACE and the TRANSPORT_FULL_CHECK attributes.
Before using DATAPUMP(expdp) I would have to create directory.
[oracle@dilip~] mkdir -p /u03/app/oracle/transport
[oracle@dilip~] export ORACLE_SID=ORCL
[oracle@dilip~] sqlplus sys/******@ORCL as sysdba
SQL> CREATE DIRECTORY TTS_DIR AS '/u03/app/oracle/transport';
SQL> HOST
[oracle@dilip~] expdp userid=system/****** DUMPFILE=tts.dmp DIRECTORY=tts_dir TRANSPORT_TABLESPACE=sales_delhi TRANSPORT_FULL_CHECK=Y
STEP 7
Execute following Query on both source and target platform
Source Platform
SQL> select * from v$transportable_platform order by platform_id;
SQL> SELECT tp.platform_name,
tp.endian_format,
FROM V$database d, V$transportable_platform tp
WHERE d.platform_name=tp.platform_name;
OUTPUT
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux IA (32-bit) Little
Target Platform
SQL> select tp.platform_name,tp.endian_format
from v$database d, v$transportable_platform tp
where tp.platform_name=d.platform_name;
OUTPUT
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
STEP 7
I will use the RMAN's CONVERT COMMAND
[oracle@dilip~] export ORACLE_SID=orcl
[oracle@dilip~] rman target /
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
RMAN> CONVERT TABLESPACE SALES_DELHI
TO PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u03/app/oracle/transport/%U';
Enable RMAN Compression Again
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
RMAN> EXIT
STEP 8
Now I move both tts.dmp (exported by datapump using expdp) and converted datafile ( using RMAN) towords the target platform.
Any medium we can use to move these file from source to target.
Here I am using Pen Drive because my tablespace is near about 1GB.
Source platform
Copy both files(tts.dmp and rman converted datafile) from /u03/app/oracle/transport and save to pen derive.
Target Platform
Create Directory in Target Platform
C:\> cd oracle
C:\oracle> mkdir import
Save both files(tts.dmp and RMAN converted datafile) from pen derive to "C:\Oracle\import"
Change Datafie name which is converted by RMAN. Changed to SALES_DELHI_2011
C:\oracle> set ORACLE_SID=MYDB
C:\oracle> sqlplus sys/******@MYDB as sysdba
SQL> create directory imp_dir as 'C:\oracle\import';
SQL> HOST
C:\oracle> cd import
C:\oracle\import> impdp system/****** DUMPFILE=tts.dmp DIRECTORY=imp_dir TRANSPORT_DATAFILE='C:\oracle\import\sales_delhi_2011'
C:\oracle\import> sqlplus
SQL> create user dilip identified by dilip account unlock;
SQL> alter tablespace sales_delhi_2011 read write;
SQL> connect dilip/dilip
SQL> SQL> select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';
I am going to write transport tablespace from one plateform to another platform step by step.
Note:This is only for study purpose. If you experiment on production database its own risk.
Scenario
Source
Database : Oracle 10gR2 Platform : Linux 32 bit
Target
Database : Oracle 10gR2
Platform : Windows XP 32 bit
Step 1
The Target and Source db characterset and national characterset should be identical
Source Database
SQL> set pages 500
SQL> set line 100
SQL> COL PROPERTY_NAME FORMAT A30
SQL> COL PROPERTY_VALUE FORMAT A30
SQL> COL DESCRIPTION FORMAT A30
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%CHARACTERSET%';
Output
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8ISO8859P1 Character set
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
Target Database
SQL> set pages 500
SQL> set line 100
SQL> COL PROPERTY_NAME FORMAT A30
SQL> COL PROPERTY_VALUE FORMAT A30
SQL> COL DESCRIPTION FORMAT A30
SQL> SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%CHARACTERSET%';
OUTPUT
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET WE8ISO8859P1 Character set
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
If NLS_CHARACTERSET is not same on source and target Database then follow following step on the target database
SQL> connect sys/**********@ORCL as sysdba
SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';
SQL> UPDATE PROPS$ SET VALUE$='WE8ISO8859P1' WHERE NAME LIKE 'NLS_CHARACTERSET';
SQL> COMMIT;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SELECT * FROM V$NLS_PARAMATER WHERE PARAMETER = 'NLS_CHARACTERSET';
STEP 2
Compatible
Target Database
SQL> SHOW PARAMETER COMPATIBLE;
Source Database
SQL> SHOW PARAMETER COMPATIBLE;
Block Size
From Oracle 9i onwords we can transport tablespace across the database with different standard block size.
STEP 3
Create tablespace on source database( Linux )
SQL> connect sys/****** as sysdba
SQL> CREATE TABLESPACE sales_delhi
DATAFILE '/u03/app/oracle/product/10.2.0/oradata/sales_delhi01.dbf' SIZE 200M;
SQL> CREATE USER dilip IDENTIFIED BY dilip ACCOUNT UNLOCK;
SQL> GRANT CREATE SESSION TO DILIP;
SQL> GRANT CREATE TABLE, SELECT ANY TABLE TO DILIP;
SQL> GRANT DROP TABLE TO DILIP;
SQL> GRANT UPDATE ANY TABLE TO DILIP;
SQL> CONNECT DILIP/dilip;
SQL> CREATE TABLE ORDER( ORDER_ID NUMBER(5) PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(20) NOT NULL,
ADDRESS VARCHAR2(30),
PRODUCT_NAME VARCHAR2(15) NOT NULL,
QTY NUMBER(3),
ORDER_DATE DATE,
SALES_PERSON VARCHAR2(20))
TABLESPACE SALES_DELHI;
Enter order data collected by sales person
SQL> commit;
SQL>select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';
STEP 4
Transport tablespace SALES_DELHI from Linux 32 bit platform to Windows XP 32 bit platform.
First we validate whether the tablespace SALES_DELHI is self contained or not. Execute following query.
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('SALES_DELHI',TRUE,TRUE);
OUTPUT
PL/SQL procedure successfully complited.
SQL> SELECT * FROM FROM TRANSPORT_SET_VIOLATIONS;
OUTPUT
No ROW SELECTED.
STEP 5
Now I put the tablespace in READ ONLY mode.
SQL> ALTER TABLESPACE SALES_DELHI READ ONLY;
SQL> SELECT tablespace_name,status FROM DBA_TABLESPACES;
STEP 6
Now I export tablespace with DATAPUMP(expdp) and I use the TRANSPORT_TABLESPACE and the TRANSPORT_FULL_CHECK attributes.
Before using DATAPUMP(expdp) I would have to create directory.
[oracle@dilip~] mkdir -p /u03/app/oracle/transport
[oracle@dilip~] export ORACLE_SID=ORCL
[oracle@dilip~] sqlplus sys/******@ORCL as sysdba
SQL> CREATE DIRECTORY TTS_DIR AS '/u03/app/oracle/transport';
SQL> HOST
[oracle@dilip~] expdp userid=system/****** DUMPFILE=tts.dmp DIRECTORY=tts_dir TRANSPORT_TABLESPACE=sales_delhi TRANSPORT_FULL_CHECK=Y
STEP 7
Execute following Query on both source and target platform
Source Platform
SQL> select * from v$transportable_platform order by platform_id;
SQL> SELECT tp.platform_name,
tp.endian_format,
FROM V$database d, V$transportable_platform tp
WHERE d.platform_name=tp.platform_name;
OUTPUT
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux IA (32-bit) Little
Target Platform
SQL> select tp.platform_name,tp.endian_format
from v$database d, v$transportable_platform tp
where tp.platform_name=d.platform_name;
OUTPUT
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
STEP 7
I will use the RMAN's CONVERT COMMAND
[oracle@dilip~] export ORACLE_SID=orcl
[oracle@dilip~] rman target /
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
RMAN> CONVERT TABLESPACE SALES_DELHI
TO PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/u03/app/oracle/transport/%U';
Enable RMAN Compression Again
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
RMAN> EXIT
STEP 8
Now I move both tts.dmp (exported by datapump using expdp) and converted datafile ( using RMAN) towords the target platform.
Any medium we can use to move these file from source to target.
Here I am using Pen Drive because my tablespace is near about 1GB.
Source platform
Copy both files(tts.dmp and rman converted datafile) from /u03/app/oracle/transport and save to pen derive.
Target Platform
Create Directory in Target Platform
C:\> cd oracle
C:\oracle> mkdir import
Save both files(tts.dmp and RMAN converted datafile) from pen derive to "C:\Oracle\import"
Change Datafie name which is converted by RMAN. Changed to SALES_DELHI_2011
C:\oracle> set ORACLE_SID=MYDB
C:\oracle> sqlplus sys/******@MYDB as sysdba
SQL> create directory imp_dir as 'C:\oracle\import';
SQL> HOST
C:\oracle> cd import
C:\oracle\import> impdp system/****** DUMPFILE=tts.dmp DIRECTORY=imp_dir TRANSPORT_DATAFILE='C:\oracle\import\sales_delhi_2011'
C:\oracle\import> sqlplus
SQL> create user dilip identified by dilip account unlock;
SQL> alter tablespace sales_delhi_2011 read write;
SQL> connect dilip/dilip
SQL> SQL> select table_name,tablespace_name from dba_tables where tablespace_name like 'SALE_%';