A transportable tablespace allows you to move a tablespace data of an Oracle database from one Oracle database to another. In Oracle 10g allow you to move tablespace across different platforms.
Steps to move data accross Cross-Platform
1 ) Check Platform Support and File Conversion Requirement
The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian. Big endian means the most significant byte comes first, and little endian means the least significant byte comes first. If the source platform and the target platform are of different endianness, then an additional step must be taken on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform
SQL> select platform_name , endian_format from v$transportable_platform
SQL> /
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
Solaris Operating System (x86- Little
64)
HP IA Open VMS Little
19 rows selected.
2)Identify Tablespaces to be Transported and Verify Self-containment
SQL> execute dbms_tts.transport_set_check('NEW',true);
PL/SQL procedure successfully completed.
OR
SQL> BEGIN
2 SYS.dbms_tts.transport_set_check
3 ('NEw', incl_constraints=>TRUE, full_check=>FALSE);
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SYS.transport_set_violations;
no rows selected
SQL>
3)Make Tablespace read only
sql> alter tablespace NEw read only;
Tablespace altered.
4)Create Directory for EXPDUMP
SQL> create directory dumdir as '/home/oracle/dumpdir';
Directory created.
SQL> grant read on directory dumdir to system;
Grant succeeded.
SQL> grant write on directory dumdir to system;
Grant succeeded.
4)Take BACKUP OF TABLESPACE
[oracle@dbdatah-server ~]$ expdp dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespace=new
LRM-00101: unknown parameter name 'transport_tablespace'
[oracle@dbdatah-server ~]$ expdp dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespaces=new transport_full_check=y
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 09 August, 2011 13:30:15
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespaces=new transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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/dumpdir/new.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:31:37
5)Copy the datafiles on Destination Platform
6)convert name of the datafiles belongs to Transportable tablespace
C:\oracle\product\10.2.0\db_1>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Aug 9 16:04:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: GEN (DBID=1378351019)
RMAN> convert datafile "c:\dumpdir\new2.dbf" to platform="Microsoft Windows IA (32-bit)" db_file_nam
e_convert=('/home/oracle/oradata/dbdata/','c:\dumpdir');
Starting backup at 09-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=C:\DUMPDIR\NEW2.DBF
converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-DBDATA_I-504259222_TS-NEW_FNO-7_0
2MJIPC6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36
Finished backup at 09-AUG-11
RMAN> exit;
Recovery Manager complete.
7) Import tablespace
C:\oracle\product\10.2.0\db_1>impdp dumpfile=new.dmp logfile=new.log directory=dumpdir transport_da
tafiles=c:\dumpdir\new1.dbf,c:\dumpdir\new2.dbf
Import: Release 10.2.0.4.0 - Production on Tuesday, 09 August, 2011 16:06:44
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=new.dmp logfile=new.log d
irectory=dumpdir transport_datafiles=c:\dumpdir\new1.dbf,c:\dumpdir\new2.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:06:55
C:\oracle\product\10.2.0\db_1>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 9 16:07:21 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\USERS01.DBF
SYSAUX
C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\SYSAUX01.DBF
UNDOTBS1
C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\UNDOTBS01.DBF
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\SYSTEM01.DBF
NEW
C:\DUMPDIR\NEW2.DBF
NEW
C:\DUMPDIR\NEW1.DBF
6 rows selected.
SQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='NEW';
TABLESPACE_NAME STATUS
------------------------------ ---------
NEW READ ONLY
8)Change tablespace in Read Write Mode
SQL> alter tablespace new read write;
Tablespace altered.
SQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='NEW';
TABLESPACE_NAME STATUS
------------------------------ ---------
NEW ONLINE
SQL>
No comments:
Post a Comment