Oracle Transport Tablespace

-

Transport tablespace is a feature of oracle database which is used to migrate application data from database to another database.

Transportabletablespace is a much-awaited new feature available in Oracle 8.1.5. This feature allows users to export the metadata of the tablespace instead of the data in the tablespace. By doing so, the consistent backup of the tablespace concerned could be transferred to the desired location and straightaway plugged in by importing the meta data instead of importing the whole data. This feature is very useful for data warehousing/applications which needs data to be distributed to various places. Using this feature, we can reduce the time required to export import the data. Transportable tablespace can also be used with RMAN, TSPITR. The examples given in this note are from NT. But the steps are identical even in Windows NT like any other Oracle Operations.

 

RESTRICTIONS

1. Operating system and processor type must be the same at source and target database

2. Database block size must be the same for the source and target database

3. Characterset of the source and the target must be the same

4. Users whose default tablespace is getting exported must exist in the target database before importing

5. Target database must not have tablespace of the same name

6. Oracle versions in source and target must be 8.1.x and above

7. Snapshot/replication, function-based indexes, Scoped REFs, domain indexes, 8.0-compatible advanced queues with multiple recipients are not supported

8. The tablespace must be self-contained to ensure that the table is complete in case of partitioning.

 SETUP

The required scripts for transportable tablespaces are run by catproc.sql itself. Check that DBMS_PLUGTS and DBMS_TTS are valid. If not run the following scripts

$ORACLE_HOME/rdbms/admin/catplug.sql

$ORACLE_HOME/rdbms/admin/dbmsplts.sql  

$ORACLE_HOME/rdbms/admin/plvtplts.plb

as 'SYS' user.

If you are importing the tablespace as a user other than 'SYS', create a public synonym for SYS.DBMS_PLUGTS as DBMS_PLUGTS else you would get the errors similar to the following

 

import done in US7ASCII character set and US7ASCII NCHAR character set

IMP-00003: ORACLE error 6550 encountered

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

IMP-00000: Import terminated unsuccessfully

 

The user should also have dba privilege.

Below are the steps to migrate tablespace.

1) Violation check 

execute dbms_tts.transport_set_check('data_test,data_INDEX',TRUE);

Made the tablespace READ only


2) Export the meta data


3) Copy Datafile from Source database to target database.


4) Import metadata to target database along with datafiles.


5) Make Tablespace Read Write.


You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

 ==========================================================

Please check our other blogs for Migration

Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more