Transport Tablespace From One Database To Another

Transport tablespace from one database to another By: Rahul Gupta [email protected] http://onlineerpdba.blogspot.com

You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases . Here we’re transporting a tablespace named “PERFSTAT”


Mandatory for a transport tablespace option : • • • •

The source and target database must be on the same hardware platform. The source and target database must use the same character set and national character set. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. Transportable tablespaces do not support: o Materialized views/replication o Function-based indexes o Scoped REFs o 8.0-compatible advanced queues with multiple recipients

Step 1 : For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure . Some examples of self contained tablespace violations are: • • • •

An index inside the set of tablespaces is for a table outside of the set of tablespaces. Note : It is not a violation if a corresponding index for a table is outside of the set of tablespaces A partitioned table is partially contained in the set of tablespaces. A referential integrity constraint points to a table across a set boundary. A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.

SQL > EXECUTE dbms_tts.transport_set_check('PERFSTAT', TRUE); PL/SQL procedure successfully completed. SQL > SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected

The TRANSPORT_SET_VIOLATIONS view can be used to check for any violations:

Step 2 : Take the required tablespace in READ ONLY mode . It’s mandatory for transporting a tablespace that it should be in READ ONLY mode. If you’re database is in READ-ONLY mode and tablespace is in READ-WRITE . Then it wont work . SQL > alter tablespace PERFSTAT read only;

Note: If your database is in READ ONLY mode and tablespace is in READ WRITE. Then you’ll get the error “ORA-29335: tablespace 'PERFSTAT' is not read only “ .

Step 3 : Export the PERFSTAT tablespace metadata using the export utility: exp transport_tablespace=y tablespaces=PERFSTAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log [veritas:oraprod] /u43/oradata/stats_dump > erfdump.dmp log=/u43/oradata/stats_dump/perfdump.log < Export: Release 9.2.0.5.0 - Production on Sun Sep 13 10:38:57 2009 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Username: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace PERFSTAT ... . exporting cluster definitions . exporting table definitions . . exporting table MGMT_RESPONSE_V$SQL_SNAPSHOT . . exporting table MGMT_RESPONSE_BASELINE . . exporting table MGMT_RESPONSE_CONFIG . . exporting table MGMT_RESPONSE_CAPTURE EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table KIC_INVALID EXP-00091: Exporting questionable statistics. ...............................................

............................................... .............................................. EXP-00091: Exporting questionable statistics. . . exporting table STAT_TABLE_BAK EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table STATS$DDL_LOG . . exporting table PLAN_TABLE . . exporting table URGENT EXP-00091: Exporting questionable statistics. . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully with warnings. [veritas:oraprod] /u43/oradata/stats_dump >

Step 4 : Transport the Tablespace Set Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database. You can use any facility for copying flat files (for example, an operating system copy utility, ftp, or publishing on CDs). scp scp scp scp scp

/u24/oradata/ilproddata/perf01.dbf [email protected]:/perf/oradata/perfdata/ /u19/oradata/ilproddata/perf02.dbf [email protected]:/perf/oradata/perfdata/ /u32/oradata/ilproddata/perf0* [email protected]:/perf/oradata/perfdata/ /u35/oradata/ilproddata/perf04.dbf [email protected]:/perf/oradata/perfdata/ /u40/oradata/ilproddata/perf0* [email protected]:/perf/oradata/perfdata/




create the database with same character set as that of source database . Use the following query to find the character set of a database . If adding / Plug in the tablespace to an existing database , Then change the characterset of that database . SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER -----------------------------NLS_CHARACTERSET NLS_NCHAR_CHARACTERSET

VALUE ---------------------------------------UTF8 AL16UTF16

 How to change the character set of a database SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ] ALTER DATABASE OPEN; select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; ---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1 Step 5 :DATABASE CreateCHARACTER users using tablespace PERFSTAT on destination database ALTER SETthe UTF8; SHUTDOWN IMMEDIATE; STARTUP; Create all the user having any object in the required (PERFSTAT) tablespace. In our case tablespace PERFSTAT @$ORACLE_HOME/rdbms/admin/catmet.sql contains the object of 4 user APPS , Perfstat , BIZMAX , DBSNM . SHUTDOWN IMMEDIATE; STARTUP;

Step 5 : Create users using the tablespace PERFSTAT on destination database Create all the user having any object in the required (PERFSTAT) tablespace. In our case tablespace PERFSTAT contains the object of 4 user APPS , Perfstat , BIZMAX , DBSNM .

User having the object in PERFSTAT tablespace on source database . SQL>

select distinct owner from dba_segments where tablespace_name='PERFSTAT'

;

OWNER -----------------------------APPS BIZMAX DBSNMP KIC PERFSTAT TRCANLZR

Now , create the user .. create user BIZMAX identified by BIZMAX ; grant connect , resource to BIZMAX ; create user TRCANLZR identified by TRCANLZR ; grant connect , resource to TRCANLZR; create user DBSNMP identified by DBSNMP ; grant connect , resource to DBSNMP; create user KIC identified by KIC ; grant connect , resource to KIC; create user perfstat identified by perfstat ; grant connect , resource to perfstat;

create use apps identified by apps; grant connect , resource to apps;

Step 6 : Plug In the Tablespace Set imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump.log tablespaces=PERFSTAT datafiles=('/perf/oradata/perfdata/perf01.dbf','/perf/oradata/perfdata/perf02.dbf','/perf/or adata/perfdata/perf03.dbf','/perf/oradata/perfdata/perf04.dbf','/perf/oradata/perfdata/perf0 5.dbf','/perf/oradata/perfdata/perf06.dbf','/perf/oradata/perfdata/perf07.dbf','/perf/oradat a/perfdata/perf08.dbf')

[plantopiadb:oraperf] /perf/dump> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump.log tabl> Import: Release 9.2.0.7.0 - Production on Sun Sep 13 11:56:56 2009 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Username: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses UTF8 character set (possible charset conversion) . importing SYS's objects into SYS . importing DBSNMP's objects into DBSNMP . . importing table "MGMT_RESPONSE_V$SQL_SNAPSHOT" . . importing table "MGMT_RESPONSE_BASELINE" . . importing table "MGMT_RESPONSE_CONFIG" . . importing table "MGMT_RESPONSE_CAPTURE" . . importing table "PLAN_TABLE" ………………………………………………………………………………………………………………………………… ………………………………………………………………………………………………………………………………… ………………………………………………………………………………………………………………………………… ………………………………………………………………………………………………………………………………… . . importing table "WHOLE_DB_STATS_22JUNE07" . . importing table "MTRL_DB_STATS_23JUN07" . . importing table "STATS$SQL_PLAN" . . importing table "STATS$FILESTATXS" . importing table "STATS$SQL_PLAN_USAGE" . . importing table "STATS$SQL_SUMMARY" . . importing table "STATS$DDL_LOG" . . importing table "PLAN_TABLE" . . importing table "URGENT" About to enable constraints... Import terminated successfully without warnings.

Now , confirm the pluggin ot the tablespace [plantopiadb:oraperf] /perf/dump> sqlplus " /as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Sun Sep 13 11:58:01 2009 Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> select tablespace_name, plugged_in, status where tablespace_name = 'PERFSTAT';

from

dba_tablespaces

TABLESPACE_NAME PLU STATUS ------------------------------ --- --------PERFSTAT YES READ ONLY

Step 7 : Take the source tablespace in READ WRITE mode on source database ALTER TABLESPACE PERFSTAT READ WRITE;

Errors & Troubleshooting

Error 1 : [plantopiadb:oraperf] /home/users/oraperf> bf','/perf/oradata/perfdata/perf08.dbf' < Import: Release 9.2.0.7.0 - Production on Fri Sep 11 14:46:30 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Username: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set . importing SYS's objects into SYS IMP-00017: following statement failed with ORACLE error 29345: "BEGIN sys.dbms_plugts.beginImport ('9.2.0.5.0',871,'2000',NULL,'NULL',348" "9288,1981988601,1); END;" IMP-00003: ORACLE error 29345 encountered

ORA-29345: can not plug a tablespace into a database using a different character set ORA-06512: ORA-06512: ORA-06512: IMP-00000:

at "SYS.DBMS_PLUGTS", line 1794 at "SYS.DBMS_PLUGTS", line 1633 at line 1 Import terminated unsuccessfully

Soltuion : This is because of different character set at the destination database. Run the query on source database SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER -----------------------------NLS_CHARACTERSET NLS_NCHAR_CHARACTERSET

VALUE ---------------------------------------UTF8 AL16UTF16

Now check the character set at the destination database ( where you’re plugging your tablespace ) SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER VALUE ------------------------------ ----------------------------------------

NLS_CHARACTERSET

US7ASCII

NLS_NCHAR_CHARACTERSET

AL16UTF16

If it’s different then change the character set of the database using the steps mentioned below . SHUTDOWN IMMEDIATE; STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; ---- TRUNCATE TABLE SYS.METASTYLESHEET; ---to get rid of ORA-12716 per Metalink Note - 213015.1 ALTER DATABASE CHARACTER SET UTF8; SHUTDOWN IMMEDIATE; STARTUP; @$ORACLE_HOME/rdbms/admin/catmet.sql SHUTDOWN IMMEDIATE; STARTUP;

Error 2 : [plantopiadb:oraperf] /home/users/oraperf> imp transport_tablespace=y file=/perf/dump/perfdump.dmp log=/perf/dump/perfdump> Import: Release 9.2.0.7.0 - Production on Fri Sep 11 15:45:06 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Username: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses UTF8 character set (possible charset conversion) . importing SYS's objects into SYS

IMP-00017: following statement failed with ORACLE error 29342: "BEGIN sys.dbms_plugts.checkUser('APPS'); END;" IMP-00003: ORACLE error 29342 encountered ORA-29342: user APPS does not exist in the database ORA-06512: at "SYS.DBMS_PLUGTS", line 1594 ORA-06512: at line 1 IMP-00000: Import terminated unsuccessfully

Soltuion : APPS user is having object in respective tablespace (PERFSTAT in our case) at the source database. But no APPS user at the destination database. Use the query mentioned below to fine all the users having the object in PERFSTAT tablespace at source database and need to be created at the destination database , in case if not present . SQL> OWNER

select distinct owner from dba_segments where tablespace_name='PERFSTAT'

;

-----------------------------APPS BIZMAX DBSNMP KIC PERFSTAT TRCANLZR

Error 3

:

[veritas:oraprod] /home/oraprod > TAT file=/u43/oradata/stats_dump/perfdump.dmp log=/u43/oradata/stats_dump/perfdump.log < Export: Release 9.2.0.5.0 - Production on Fri Sep 11 12:44:26 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Username: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... EXP-00008: ORACLE error 29335 encountered

ORA-29335: tablespace 'PERFSTAT' is not read only ORA-06512: at "SYS.DBMS_PLUGTS", line 436 ORA-06512: at line 1 EXP-00000: Export terminated unsuccessfully

Soltuion : This is because of the tablespace in READ WRITE mode . Before taking the export of metadata, tablespace needs to be taken in READ ONLY mode Note: If your database is in READ ONLY mode and tablespace is in READ WRITE. In that case also, you’ll get the same error.