advertisements
_____________________________________________________________________________________________________________________
Transportable Tablespace Advantages:
Oracle allows you to transport the tablespaces from one database to other database across different OS platforms. It is a quick and most efficient way of bulk data movement
It is much faster than any other traditional data transfer methods like export import.
Why because it is just plugging of datafiles from source to destination after some set of checking. Also, it avoids the index rebuilds after moving the data to the target database if you are copying the index tablespaces along with data tablespaces.
Why because it is just plugging of datafiles from source to destination after some set of checking. Also, it avoids the index rebuilds after moving the data to the target database if you are copying the index tablespaces along with data tablespaces.
Oracle 10g Onwards it allows cross OS platform tablespace movement. Prior to 10g It allows only with the same OS platform.
Endian Formats:
Even if your source and target platforms are compatible or same, you won’t be able to transport the tablespace from source platform to target platform directly. Before you start transporting or copying the datafile, you need to check the endian format of the data files in the source and target platform. Endian format refers to the byte ordering in the datafile. It is just the way bytes are stored in the datafile. There are 2 types of endian formats – Big and Small.
If your target platform is not compatible with your source platform in terms of endian format, then you have to make the endian format of all the datafiles belonging to the tablespace you want to transport same. You can accomplish this using RMAN. I will let you know later how to convert the endian format. For now, we will see the platform compatibility and other details.
Example for Transportable tablespace
Step 1. Connect to the database and create a tablespace with name as TEST. You can use your existing tablespace for real time purpose.
SQL> select name from v$database;
NAME
---------
PRODDB9
SQL> create tablespace test datafile '/data/oracle/oradata/proddb9/test.dbf' size 100m;
Tablespace created.
Moving some objects to test tablespace
SQL> alter table scott.emp move tablespace test;
Table altered.
SQL> select tablespace_name, table_name from dba_tables where table_name='EMP';
TABLESPACE_NAME TABLE_NAME
------------------------------ ----------------------------
TEST EMP
Step 2. Check your tablespaces (source & destination) tablepsaces where compatible for movement. There is a data dictionary view to check the ENDIAN_FORMAT compatibility v$transportable_platform. The endian_format must be similar for transportable tablespace movement. In case if it is different you have to convert to the target format which is described belowJ. You can check the platform version from v$database.
col PLATFORM_NAME for a45
set pages 1000
SQL> set lines 120
SQL> l
1* select * from v$transportable_platform
SQL> /
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- --------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
19 rows selected.
SQL> select platform_name from v$database;
PLATFORM_NAME
---------------------------------------------
Solaris[tm] OE (64-bit)
Step 3. Check the characterset of the source and destination database and it should be of same.
SQL> select * from nls_database_parameters where parameter like '%CHARA%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
Step 4. Also check the below mentioned criterias are passed and these are the General requirements.
- Databases (source & target) must be with Oracle 8i or above
- Database Version need not be identical
- System & Sys related tablespaces or objects cannot be transported
- If you want to transport partitioned table, all the partitions must be included in transportable table set. If you are transporting index, all the tablespace containing respective tables also needs to be transported
- You can transport the tablespace to a target database only if it has same or higher compatibility settting
Step 5. Check whether the tablespace is self contained using DBMS_TTS.TRANSPORT_SET_CHECK procedure.
PROCEDURE DBMS_TTS.TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST',TRUE);
PL/SQL procedure successfully completed.
Step 6. Check for any violations from TRANSPORT_SET_VIOLATIONS table.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
Step 7. Make the tablespace (TEST) read only.
SQL> alter tablespace test read only;
Tablespace altered.
Step 8. Create a export directory for the expdp
create or replace directory exp_dir as '/home/oracle/scott';
Step 9. Export the metadata using expdp utitlity
$ expdp TRANSPORT_TABLESPACES=test directory=exp_dir dumpfile=tt_test.dmp logfile=tt_test.log
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 21 June, 2011 21:13:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABLESPACES=test directory=exp_dir
dumpfile=tt_test.dmp logfile=tt_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/COMMENT
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/scott/tt_test.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST:
/data/oracle/oradata/proddb9/test.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 21:16:14
Step 10. Now convert your datafile(Test tablespaces) to source endian format. If you have the same endian format in target database you just need to copy the source datafile to target. Either way copy the datafile to the target location.
rman target /
RMAN> CONVERT TABLESPACE 'TEST'
2> TO PLATFORM = 'Linux IA (32-bit)'
3> DB_FILE_NAME_CONVERT = '/data/oracle/oradata/proddb9', '/data/oracle/oradata/proddb8';
Starting conversion at source at 21-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00026 name=/data/oracle/oradata/proddb9/test.dbf
converted datafile=/data/oracle/oradata/proddb8/test.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 21-JUN-11
Step 11. Make the tablespace (TEST) read write in source database
SQL> alter tablespace test read write;
Tablespace altered.
Step 12. Rest of the steps are in Target database. Create the imp_dir in target directory for impdp utility
create or replace directory imp_dir as '/home/oracle/scott';
Step 13. Import the table into target database
$ impdp TRANSPORT_DATAFILES='/data/oracle/oradata/proddb8/test.dbf' directory=imp_dir dumpfile=tt_test.dmp logfile=imptt_test.log
Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 21 June, 2011 21:47:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA TRANSPORT_DATAFILES=/data/oracle/oradata/proddb8/test.dbf
directory=imp_dir dumpfile=tt_test.dmp logfile=imptt_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 21:47:12
Step 14. Check whether it is properly transported using following methods.
Alert Log:
DM00 started with pid=38, OS id=25879, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:45:58 2011
DW01 started with pid=39, OS id=25881, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:47:07 2011
DM00 started with pid=28, OS id=25897, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:47:08 2011
DW01 started with pid=35, OS id=25899, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Plug in tablespace TEST with datafile
'/data/oracle/oradata/proddb8/test.dbf'
SQLPLUS
SQL> set pages 100
SQL> set lines 120
SQL> col SEGMENT_NAME for a40
SEGMENT_NAME SEGMENT_TYPE OWNER
---------------------------------------- ------------------ ------------------------------
EMP TABLE scott
1* select tablespace_name, file_name from dba_data_files where tablespace_name ='TEST'
SQL> /
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
TEST /data/oracle/oradata/proddb8/test.dbf
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment