Transport Tablespaces in Oracle Database 11g
How to transport a tablespace in
Oracle database | Steps to transport a tablespace | Manual guide on how to
transport a tablespace | how to check if a tablespace is transportable or not
 |
| Transport Tablespaces in Oracle Database 11g |
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. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size.
In this article, I have shown how to
accomplish the same. Please follow the below mentioned steps to get to know how
to do it.
Step 1: Create a new tablespace, a
separate user for that particular tablespace. Also providing some basic
privileges to the new user.
SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
F:\DATABASE\ORADB11G\SYSTEM01.DBF
F:\DATABASE\ORADB11G\SYSAUX01.DBF
F:\DATABASE\ORADB11G\UNDOTBS01.DBF
F:\DATABASE\ORADB11G\USERS01.DBF
F:\DATABASE\ORADB11G\EXAMPLE01.DBF
SQL>
SQL> create tablespace TRANS_TAB datafile 'F:\DATABASE\ORADB11G\TRANSTAB01.DBF' size 1M autoextend on;
Tablespace created.
SQL> create user TEST_TAB identified by test default tablespace TRANS_TAB quota unlimited on TRANS_TAB;
User created.
SQL> grant create session, resource, create table to TEST_TAB;
Grant succeeded.
Step 2: Create a table and insert some
values in that table. (This is not necessary but I want to store some data in
the tablespace before transporting it to other database).
SQL> conn TEST_TAB@oradb11g
Enter password:
Connected.
SQL>
SQL> create table DEMO_TABLE(id number, description varchar2(20));
Table created.
insert into DEMO_TABLE values (1, 'qwerty');
insert into DEMO_TABLE values (2, 'qwerty1');
insert into DEMO_TABLE values (3, 'qwerty13');
insert into DEMO_TABLE values (4, 'qwerty132');
insert into DEMO_TABLE values (5, 'qwerty1324');
insert into DEMO_TABLE values (6, 'qwerty13245');
commit;
SQL> set lines 200
SQL> select * from DEMO_TABLE;
ID DESCRIPTION
---------- --------------------
1 qwerty
2 qwerty1
3 qwerty13
4 qwerty132
5 qwerty1324
6 qwerty13245
6 rows selected.
Step 3: Before transporting the
tablespace check for any violations because for a tablespace to be
transportable, it must be totally self contained.
SQL> conn sys@oradb11g as sysdba
Enter password:
Connected.
SQL>
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TRANS_TAB', incl_constraints => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected
Step 4: Creating the directory for
Data Pump Export and putting the Tablespace in Read Only mode.
SQL> alter tablespace TRANS_TAB read only;
Tablespace altered.
SQL> create directory DB_DUMP as 'F:\Database\DUMP';
Directory created.
Step 5: Exporting the tablespace
Metadata using EXPDP.
C:\Users\parain>expdp directory=DB_DUMP transport_tablespaces=TRANS_TAB dumpfile=trans_tab.dmp logfile=trans_tab.log
Export: Release 11.2.0.4.0 - Production on Thu May 10 17:21:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system@oradb11g
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@oradb11g directory=DB_DUMP transport_tablespaces=
TRANS_TAB dumpfile=trans_tab.dmp logfile=trans_tab.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
F:\DATABASE\DUMP\TRANS_TAB.DMP
******************************************************************************
Datafiles required for transportable tablespace TRANS_TAB:
F:\DATABASE\ORADB11G\TRANSTAB01.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu May 10 17:22:35 2018 elapsed 0 00:00:28
Step 6: Before Putting the tablespace
back again to Read Write Mode, copy the datafile of that tablespace to target
database. OR if you copy the datafile after you put the tablespace in Read Write
mode then you’ll be getting Oracle Error ORA-19722.
Step 7: Once datafile copying is done,
put that Tablespace back in Read Write Mode.
C:\Users\parain>sqlplus sys@oradb11g as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:23:50 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace TRANS_TAB read write;
Tablespace altered.
Step 8: Create the Data Pump Directory
and New User (as created in the source database)
C:\Users\parain>sqlplus sys@oradb112 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:24:26 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory DB_DUMP as 'F:\Database\DUMP';
Directory created.
SQL> create user TEST_TAB identified by test;
User created.
SQL> grant create session, resource, create table to TEST_TAB;
Grant succeeded.
Step 9: Import the Tablespace Metadata
and mention the copied datafile in the TRANSPORT_DATAFILE parameter.
C:\Users\parain>impdp directory=DB_DUMP dumpfile=TRANS_TAB.DMP logfile=impTRANSTAB.log transport_datafiles=
'F:\DATABASE\ORADB112\TRANSTAB01.DBF'
Import: Release 11.2.0.4.0 - Production on Thu May 10 17:39:57 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system@oradb112
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@oradb112 directory=DB_DUMP dumpfile=TRANS_TAB.DMP
logfile=impTRANSTAB.log transport_datafiles='F:\DATABASE\ORADB112\TRANSTAB01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu May 10 17:40:09 2018 elapsed 0 00:00:02
Step 10: Check for the Tablespace Entry
in the DBA_TABLESPACES view and change it’s status to Read Write Mode.
C:\Users\parain>sqlplus sys@oradb112 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:40:27 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TRANS_TAB READ ONLY
6 rows selected.
SQL> alter tablespace TRANS_TAB read write;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TRANS_TAB ONLINE
6 rows selected.
I hope this helps !!
No comments:
Post a Comment