Export tablespace Oracle 10g: Transportable Tablespace

Transportable Tablespace across Different Platforms
___________________________________________________

In our example we will transport a tablespace called TEST. The tablespace should be self-contained without any objects referring to other tablespaces so for that check if the tablespace os self contained using below query:

EXECUTE dbms_tts.transport_set_check(‘TEST’, TRUE, TRUE);

SELECT * FROM transport_set_violations;

If you have cross referenced objects. Drop those objects from the TEST tablespace(Please note this is being done in a test environment so I can drop the cross-referenced objects easily. Do not drop any objects without consulting your senior DBA in a production environment. You never know what object has what dependency. Suppose you have indexes in the TEST tablespace pointing to tables in another tablespace. You can safely move those indexes using below pl/sql procedure:

begin
for c1 in (select index_name a1 from user_indexes)
loop
execute immediate ‘alter index ‘||c1.a1||’ rebuild online tablespace <tablespace name>’;
end loop;
end;
/

Now proceed and check violations again. If you get an error like:

SELECT * FROM transport_set_violations;

VIOLATIONS
——————————————————————————–
Violation 46 : contact Oracle support
Violation 46 : contact Oracle support
Violation 46 : contact Oracle support

Run this query to check why the violation is throwing

SELECT TRIM(OBJ1_OWNER) || ‘, ‘ || TRIM(OBJ1_NAME) || ‘, ‘ ||
TRIM(OBJ1_SUBNAME) || ‘, ‘ || TRIM(OBJ1_TYPE) || ‘, ‘ ||
TRIM(TS1_NAME) || ‘, ‘ || TRIM(OBJ2_OWNER) || ‘, ‘ ||
TRIM(OBJ2_NAME) || ‘, ‘ || TRIM(OBJ2_SUBNAME) || ‘, ‘ ||
TRIM(OBJ2_TYPE) || ‘, ‘ || TRIM(TS2_NAME) || ‘, ‘ ||
TRIM(CONSTRAINT_NAME) || ‘, ‘ || TRIM(REASON)
FROM PLUGGABLE_SET_CHECK
WHERE MESG_ID=46;

This will give output of what exactly the violation is for the transportable tablespace. Fix those violations and start the actual procedure for transporting the tablespace. In my example i had used Transparent Data Encryption(TDE) for few columns on the database. The query reported that you cannot use TT_TBS when you have encrypted columns. So i had to decrypt the columns which I encrypted using a query like.

SELECT * FROM dba_encrypted_columns;
ALTER TABLE account MODIFY (cardholder DECRYPT);

After decrypting the columns. We proceed further

1. Check the ENDIAN version of both source and target using below script:

—–
— What are the available Transportable Tablespace Platform possibilities?
—–
SELECT
platform_name
,endian_format
FROM v$transportable_platform
ORDER BY platform_name
;

—–
— What’s the current ENDIAN-ness of my database and platform?
—–

SELECT
D.name
,TP.endian_format
FROM
v$transportable_platform TP
,v$database D
WHERE TP.platform_name = D.platform_name
;

it will report if the ENDIAN is “little” or “big”. If the endian is the same on both target and source, no need to go for transportable tablespace. You can simply export the tablespace and import it to the target. If not proceed like below

2. Alter the tablespace and put it in read-only mode on source. In our example we will transport a tablespace called TEST to the target server.

ALTER TABLESPACE test READ ONLY;

3. now export the tablespace metadata.

##Windows###
exp USERID=’system/oracle@Source AS SYSDBA’ TRANSPORT_TABLESPACE=y TABLESPACES=test FILE=test_repository.dmp log=test_repository.txt

###Unix###
exp \’sys/oracle AS SYSDBA\’ TRANSPORT_TABLESPACE=y TABLESPACES=test FILE=test_repository.dmp log=test_repository.txt

Go to target and copy dumpfile to any location along with the datafiles of that tablespace.

imp \’sys/oracle AS SYSDBA\’  TABLESPACES=test TRANSPORT_TABLESPACE=y FILE=test_repository.dmp datafiles=’users_01.dbf’

4. Till above is for when you are exporting a tablespace to a machine with same endian information. If you a different endian proceed like below. We will convert from Solaris 64-bit SPARC(BIG) to Windows 32-bit(Little)

5. Convert the datafiles using RMAN and db_file_name_convert

#do not use this method: On Source machine: With original filename
RMAN> convert tablespace ‘TEST’
to platform ‘Microsoft Windows IA (32-bit)’
format=’/datafiles/rmanbkp/%N_%f’
parallelism = 4;

## use this method: On Source machine: With converted file name filename

RMAN> convert tablespace ‘TEST’
2> to platform ‘Microsoft Windows IA (32-bit)’
3> db_file_name_convert ‘/datafiles/oradata/SWX’,’/datafiles/rmanbkp’
4> parallelism = 4;

After the files are converted it can be found in ‘/datafiles/rmanbkp’. Copy these converted files along with metadata dump of TT_TBS and put it on source server and run import:

imp USERID=’system/password@target AS SYSDBA’
TRANSPORT_TABLESPACE=y
DATAFILES=’C:\datafiles\test01.DBF’,’C:\datafiles\test02.DBF’
TABLESPACES=test
FILE=test_repository.dmp

In the above cases, we converted the files on the source platform. However, you can do that on the target platform as well. For example, you can copy file test01.dbf along with the metadata dump on a Windows Server 2003 32-bit target machine and convert it there:

RMAN> convert
2> datafile ‘c:/users01.dbf’  ##directory where  you’ll put your datafile from source server
3> format ‘c:/datafiles/rmanbkp/%N_%f’ ##directory where  you’ll get converted file
4> ;

This approach will create a file in the format specified in the directory.

But why would you want to convert the datafiles on the target platform, exactly? One reason could be shorter downtime, which requires the tablespaces to be READ ONLY state only for the duration of the copy to the target host. You could triple-mirror the datafile, make the tablespace read only, break the third mirror, and immediately make the tablespace read/write. This third mirror could then be mounted on the target system and converted at leisure. This arrangement minimizes the duration for which the tablespace must remain read only.

Another reason could be performance. The OLTP database may be under a constant load and using the RMAN convert operation may strain the system more than desired. Instead, the conversion can be offloaded to the data warehouse server, where more CPUs are usually available for parallel operations.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s