Create a plugged-in tablespace by using the transportable tablespace feature

Summary

Use the Transport Tablespace Wizard in Cloud Control if you can tolerate read-only mode for certain tablespaces.

Use the RMAN command, TRANSPORT TABLESPACE, otherwise, or if you want an older snapshot of the data.

Encountered an issue with unusable indices as described in Technical Note about Unusable Indices in TTS.

References

Oracle Manuals

My Oracle Support Documents

GitHub

Notes

Overview

“Purpose of Creating Transportable Tablespace Sets” says:

A key benefit of the RMAN TRANSPORT TABLESPACE command is that it does not need access to the live data files from the tablespaces to be transported. In contrast, the transportable tablespace technique described in Oracle Database Administrator's Guide requires that the tablespaces to be transported are open read-only during the transport. Thus, transporting from backups improves database availability, especially for large tablespaces, because the tablespaces to be transported can remain open for writes during the operation. Also, placing a tablespace in read-only mode can take a long time, depending on current database activity.

The RMAN TRANSPORT TABLESPACE command also enables you to specify a target point in time, SCN, or restore point during your recovery window and transport tablespace data as it existed at that time (see "Creating a Transportable Tablespace Set at a Specified Time or SCN"). For example, if your >backup retention policy guarantees a 1 week recovery window, and if you want to create transportable tablespaces based on the contents of the database on the last day of the month, then RMAN can perform this task at any time during the first week of the next month.

“Creating a transportable tablespace set from RMAN backupsets (Doc ID 455593.1)” says:

As of 10.2, you can create an transportable tablespace set from RMAN backupsets without any impact to the current live database. Further, it can be used as a work-around to the error ORA-29308 when trying to perform TSPITR against a LOB object.

Use of Cloud Control

“15.3.1 Introduction to Transportable Tablespaces” says of using the Transport Tablespace Wizard in Cloud Control:

Note:

    • This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process. If this is undesirable, you can use the transportable tablespaces from backup feature, described in Oracle Database Backup and Recovery User's Guide.

Procedure

This procedure follows the new way of transporting a tablespace without going into read-only mode.

Check Transport Set is Self Contained

Following the procedure in “15.3.3.1 Task 1: Pick a Self-Contained Set of Tablespaces”, I called the following PL/SQL program to see if the DEMO tablespace was self-contained on on the PERSONAL database:

execute dbms_tts.transport_set_check('DEMO', TRUE)

The output was:

PL/SQL procedure successfully completed.

To see if there were any violations, I ran the following query on the PERSONAL database:

select * from transport_set_violations;

The output was:

no rows selected

Thus, the DEMO tablespace was self-contained on on the PERSONAL database.

Simplest Transport of DEMO

Followed the example given in TRANSPORT TABLESPACE, and used the following RMAN commands:

TRANSPORT TABLESPACE demo TABLESPACE DESTINATION '/opt/app/oracle/oradata/transport' AUXILIARY DESTINATION '/opt/app/oracle/oradata/auxdata';

A demonstration script has been created as Transport DEMO Tablespace.sh on GitHub. The following three (3) files are also needed for the demonstration:

  1. Check Transport Set is Self Contained.sql
  2. Create_Transport_Set_for_DEMO.rcv
  3. list_demo_ts.sql

The log file is attached as transport_demo.log.

There is a warning:

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

However, the following error appears at the end of the log:

Could not delete auxiliary instance file dmpfile.dmp ORACLE error from target database: ORA-29280: invalid directory path Recovery Manager complete.

The following three (3) files are now in the transport directory (/opt/app/oracle/oradata/transport/):

-rw-r-----. 1 oracle oinstall 180K May 1 10:51 dmpfile.dmp -rw-r--r--. 1 oracle oinstall 1.7K May 1 10:51 impscrpt.sql -rw-r-----. 1 oracle oinstall 101M May 1 10:51 o1_mf_demo_fghgcry5_.dbf

Looks like everything was created correctly.

Load DEMO Tablespace into PLUM Using IMPDP

There is no need to transport the files as they are on the same machine, and this is a simple demonstration. However, it would be a good idea to copy the files into another directory in case the import fails.

Followed the procedure in “15.3.3.6 Task 6: Import the Tablespace Set”.

Used the following command (impdp) to import the transported tablespace into the PLUM PDB:

impdp system@plum directory=transport_dir dumpfile=dmpfile.dmp \ transport_datafiles=/opt/app/oracle/oradata/transport/o1_mf_demo_fghgcry5_.dbf

The output was:

Import: Release 12.1.0.2.0 - Production on Tue May 1 10:52:54 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully loaded/unloaded Source time zone is +10:00 and target time zone is +11:00. Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02": system/********@plum directory=transport_dir dumpfile=dmpfile.dmp transport_datafiles=/opt/app/oracle/oradata/transport/o1_mf_demo_fghgcry5_.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully completed at Tue May 1 10:53:35 2018 elapsed 0 00:00:33

Instead of using impdp, the RMAN-generated import script can be used as described in “Experiences with RMAN TTS Import Script”.

Validate Transport Tablespace Move

To validate the success of the transport tablespace move, I used the script, list_demo_ts.sql.

For the PERSONAL database, the results were (extra blank lines have been removed):

Current database name is PERSONAL. DEMO tablespace exists in PERSONAL database. Find the owner(s) of all segments in the DEMO tablespace OWNER ------------------------- DEMO DEMO user exists in PERSONAL database. Show the system privileges of the DEMO user PRIVILEGE ---------------------------------------- CREATE TABLE CREATE SESSION CREATE SEQUENCE DEMO owns 3 segments Show the segments owned byhe DEMO user SEGMENT_NAME TABLESPACE_NAME ------------------------- ------------------------- COMP_TAB_PK DEMO COMP_TAB DEMO COPY_TAB DEMO

For the PLUM PDB, the results were (extra blank lines have been removed):

Current database name is JAR. DEMO tablespace exists in JAR database. Find the owner(s) of all segments in the DEMO tablespace OWNER ------------------------- DEMO DEMO user exists in JAR database. Show the system privileges of the DEMO user PRIVILEGE ---------------------------------------- CREATE SESSION DEMO owns 3 segments Show the segments owned byhe DEMO user SEGMENT_NAME TABLESPACE_NAME ------------------------- ------------------------- COMP_TAB_PK DEMO COPY_TAB DEMO COMP_TAB DEMO

Please note the only difference is in the system privileges for the DEMO user.