Infrastructure at your Service

Oracle Team

OCM 12c preparation: Transportable Tablespaces

By Franck Pachot

.
I use transportable tablespaces a lot since 8i, here are just a few examples.
Same disclaimer here as in the first post of the series: don’t expect to get those posts close to what you will have at the exam, but they cover important points that matches the exam topics.

Documentation

Information about the exam says: Create a plugged-in tablespace by using the transportable tablespace feature. this is the TTS feature we know for a long time. The ‘plugged-in’ term is how it was originally called (see patent: http://www.google.tl/patents/US5890167 from 1997). When 12c came with pluggable database, I said that it was just an extension of the TTS feature where the SYSTEM tablespace can be transported as well, thanks to multitenant architecture that separates application dictionary.
What’s new in 12c is the ability to transport a whole database, which means use TTS for all non-system tablespaces.
So about documentation, open the Oracle® Database Administrator’s Guide, search for ‘transport’ and you have a whole chapter about transporting data. then go to: Transporting Database

Read only

I’ve a PDB pluggable database with some tablespaces and tables, and I set the user tablespace read-only;


sqlplus / as sysdba
create pluggable database PDB admin user admin identified by admin file_name_convert=('/u01/app/oracle/oradata/pdbseed','/u02/app/oracle/oradata/PDB');
alter pluggable database PDB open;
alter session set container=PDB;
grant dba to DEMO identified by demo;
create tablespace USERS datafile '/tmp/usersPDB.dbf' size 10M;
create table DEMO.DEMO tablespace USERS as select current_timestamp ts from dual;
alter tablespace USERS read only;
quit

Transport database

Then I create a new pluggable database from seed and create a database link to transport the tablespace into it:


sqlplus / as sysdba
create pluggable database PDBNEW admin user admin identified by admin file_name_convert=('/u01/app/oracle/oradata/pdbseed','/u02/app/oracle/oradata/PDBNEW');
alter session set container=PDBNEW;
startup
create database link DBL_PDB connect to system identified by oracle using '//localhost/PDB';
create directory TMP as '/tmp';
quit
cp /tmp/usersPDB.dbf /tmp/usersPDBNEW.dbf
impdp '"sys/[email protected]//localhost/PDBNEW as sysdba"' network_link=DBL_PDB directory=TMP FULL=Y TRANSPORTABLE=always transport_datafiles=/tmp/usersPDBNEW.dbf

This is database transport. It’s just the possibility to use both FULL=Y and TRANSPORTABLE. I don’t like FULL imports because they bring some mess with pseudo system schemas. But with multitenant, what is in a PDB is only user schemas.

Transport tablespace

If I wanted to transport only the tablespace, I need to have the user created before:


sqlplus / as sysdba
alter pluggable database PDBNEW close;
drop pluggable database PDBNEW including datafiles;
create pluggable database PDBNEW admin user admin identified by admin file_name_convert=('/u01/app/oracle/oradata/pdbseed','/u02/app/oracle/oradata/PDBNEW');
alter session set container=PDBNEW;
startup
create database link DBL_PDB connect to system identified by oracle using '//localhost/PDB';
create directory TMP as '/tmp';
grant dba to DEMO identified by demo;
quit
impdp '"sys/[email protected]//localhost/PDBNEW as sysdba"' network_link=DBL_PDB directory=TMP TRANSPORT_TABLESPACES=USERS transport_datafiles=/tmp/usersPDBNEW.dbf

This is the simple transport tablespace we know from a long time. Need to create the user, then all metadata is imported by data pump and data comes with the files. Here I used NETWORK_LINK because it’s the most simple, but everything is the same with a dumpfile. Need to copy datafiles with it to the target.
On different endianness, RMAN can be used to convert. All is referenced in the ‘transporting data’ chapter of documentation.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team