This is the third of four blogs which discuss some of the methods that can be used to copy tablespaces between databases. Part 1 worked through an example using export/import transportable tablespace procedures and part 2 used Datapump transportable tablespaces.
In part 3 we’ll use RMAN (Recovery Manager) to transport the tablespaces.
For all of this work, our local database is called ORCL and our remote database is ORCL2.
If you have carried out the procedures in parts 1 or 2, then run the commands below on the local and remote database, otherwise skip these drop commands.
Drop tablespace TTS_TEST including contents and datafiles;
Drop tablespace TTS_TEST_IDX including contents and datafiles;
Drop user tts_user cascade;
Create Example Tablespaces in the local database
We’ll begin by creating some tablespaces in our local database. Run the commands below as SYSTEM.
CREATE TABLESPACE TTS_TEST DATAFILE
‘/u01/oradata/ORCL/TTS_TEST01.dbf’ size 10M autoextend on maxsize 100M;
CREATE TABLESPACE TTS_TEST_IDX DATAFILE
‘/u01/oradata/ORCL/TTS_TEST_IDX01.dbf’ size 10M autoextend on maxsize 100M;
Creating an Example user in the local and remote databases
Now we’ll create an example user in our local and remote databases.
CREATE USER TTS_USER IDENTIFIED BY tester1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CREATE SESSION, CREATE TABLE TO TTS_USER;
Next - just on the local database – we’ll run the commands:
ALTER USER TTS_USER DEFAULT TABLESPACE TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST_IDX;
Create an Example Table in the local database
We can then create a very basic table with an associated index. The table and index will be in separate tablespaces and will use the default storage options for the tablespace.
CREATE TABLE TTS_USER.T1 (C1 NUMBER(10) , C2 VARCHAR2(10) )
TABLESPACE TTS_TEST ;
ALTER TABLE TTS_USER.T1 ADD (CONSTRAINT t1_pk PRIMARY KEY(C1) USING INDEX TABLESPACE TTS_TEST_IDX);
INSERT INTO TTS_USER.T1 VALUES (1,’TESTROW1′);
INSERT INTO TTS_USER.T1 VALUES (2,’TESTROW2′);
Using RMAN transportable tablespaces to create a copy of a table
The main advantage of this method over using export/import or the datapump utilities is that we do not need to make our source tablespace read-only when we are creating a copy of the tablespace.
RMAN works around this limitation by creating a temporary auxiliary instance and then making a copy of the original tablespace in this instance read-only. This read-only copy is then transported by a call to Datapump export.
Firstly, check that a full online RMAN backup of the local database - including the TTS_TEST and TTS_TEST_IDX tablespaces has been taken. The backup should have been taken using an RMAN recovery catalog. The tablespaces should be in read write mode before taking any backups and throughout this work.
Once we have taken the backup, we can then carry out the following:
(i) Create a directory to temporarily store the datafiles for the auxiliary instance.
e.g. mkdir /u01/oradata/ORCL/aux_files
(ii) Also create a directory to store the copy of the original tablespace datafile.
e.g. mkdir /u01/oradata/ORCL/copy_files
(iii) Switch a logfile on the source database. RMAN will use the last archived redo log file to determine the SCN for this work automatically.
ALTER SYSTEM ARCHIVE LOG CURRENT;
(iv) Run RMAN commands to transport the tablespace. (In this scenario, RMANCAT is the name of the RMAN catalog database).
rman catalog rman/rmanpass@RMANCAT target /
TRANSPORT TABLESPACE ‘TTS_TEST’,'TTS_TEST_IDX’
AUXILIARY DESTINATION ‘/u01/oradata/ORCL/aux_files’
DATAPUMP DIRECTORY DATA_PUMP_DIR
DUMP FILE ‘T1_tbs.dmp’
EXPORT LOG ‘T1_tbs.log’
IMPORT SCRIPT ‘T1_tbs_imp.sql’
TABLESPACE DESTINATION ‘/u01/oradata/ORCL/copy_files’;
Notes: AUXILIARY DESTINATION should match the temporary directory we created previously for the auxiliary instance files. TABLESPACE DESTINATION should match the location we created previously to hold the copy of our original tablespace datafile.
(v) Transfer the copy of our original datafile and the dynamically created script T1_tbs_imp.sql from the TABLESPACE DESTINATION to the new location on the local or remote server. Copy the DATA_PUMP_DIR dumpfile (T1_tbs.dmp) to the DATA_PUMP_DIR location for the other local or remote database. (Refer to part 2 to check the location of DATA_PUMP_DIR for your database).
(vi) The automatically created import sql script then provides two options for adding the tablespace to the remote copy of the database.
Option 1: Commands to run a standard datapump tablespace import are listed first in the file, but are commented out.
Option 2: Commands to run a PL/SQL script which calls DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES are also provided. This is the default option. (DBMS_STREAMS_TABLESPACE_ADM is discussed in the next part of this blog). We could pick whichever one of the two options we preferred to load the data, after appropriate editing.
(vii) On the remote database we would then need to place the tablespace into read write mode.
ALTER TABLESPACE TTS_TEST READ WRITE;
ALTER TABLESPACE TTS_TEST_IDX READ WRITE;
(Note: If we wanted to try both options for comparison, we would need to:
(a) Drop tablespace TTS_TEST INCLUDING CONTENTS AND DATAFILES;
(b) Drop tablespace TTS_TEST_IDX INCLUDING CONTENTS AND DATAFILES;
(c) Re-copy the original datafiles from the local server to our remote server.
(d) Re-run the import with the second option.
In the final part of this blog we’ll use DBMS_STREAMS_TABLESPACE_ADM to clone a tablespace.
(i) Oracle documentation at:
(ii) Oracle Support Note 455593.1 Creating a transportable tablespace set from RMAN backupsets