Moving Oracle Database 12_c_ Tablespaces from One Database to Another Using Transportable Tablespaces
by Alexandre Borges
This document explains how to move data from one Oracle Database 12c database to another by using transportable tablespaces.
Note: The same type of procedure can be done in older Oracle Database versions, such as Oracle Database 11_g_ Release 2. However, Oracle Database 11_g_ Release 2 entered the Extended Support phase as of February 2015, so Oracle recommends moving to Oracle Database 12_c_.
Introduction
It is not unusual for me to hear from administrators (not necessarily database administrators) questions about methods for moving an Oracle Database 12_c_ tablespace from a database A to a database B. There are several methods for accomplishing this task such as using the Oracle Data Pump import/export functionality, using database links, and even using transportable tablespaces.
Recently, a friend asked me about a practical way to move a tablespace between two databases without using a GUI tool (such as Oracle Enterprise Manager) and whether the task can be "scriptable." So this article shows a transportable tablespace procedure using the command line.
The following steps are accomplished in an environment that uses two virtual machines running 64-bit Oracle Linux 7 with 4 GB of RAM each and an instance of Oracle Database 12_c_ installed on both systems. Additionally, the database instances (and datafiles) are those that are installed by default.
The Procedure
The initial idea is to copy the USERS tablespace from system 1 (oracle12c1) to system 2 (oracle12c2). We will be using the sys user on system 1, because the goal of this article is to keep things as simple as possible. (Eventually, it would be more appropriate to use either the "system" user or another user with administration privileges.)
-
On system 1, call the SQL*Plus command-line interface and start the database:
[oracle@oracle12c1 Desktop]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 3 14:17:50 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: sys as sysdbaEnter password:
Connected to an idle instance.
SQL> startupORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 1040191104 bytes
Database Buffers 553648128 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
-
The compatibility of Oracle Database is controlled by the compatible parameter, which enables and disables some important features. As a general rule, it is always possible to move a tablespace between two databases when the target (destination database) has the same or higher compatibility level. (Here the platform is not important; we mainly want to check that the compatible parameter's value is equal to 12.0 or higher.)
On system 1, check the compatible parameter's value, as shown below:
SQL> show parameter compatible
NAME TYPE VALUE
------------------------ --------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
The same value is shown if we repeat the command on the other system (system 2).
-
On our environment, we are running 64-bit Oracle Linux, which has a little endian byte order. But if we needed to move data from a platform such Oracle Solaris for SPARC platforms, which has a big endian byte order, to another platform that has a little endian byte order, we should perform an endian conversion (not shown here) while moving data from the source to the target.
To check the endian values for all possible platforms and to verify the endian value for 64-bit Oracle Linux, execute the following commands on system 1:
SQL> set linesize 300SQL> col platform_name format a30SQL> select platform_id, platform_name, endian_format from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
SQL> col our_platform format a20SQL> select platform_name our_platform, endian_format our_endian_format from v$transportable_platform join v$database using (platform_name);
OUR_PLATFORM OUR_ENDIAN_FOR
-------------------- ---------------
Linux x86 64-bit Little
-
On system 1 and inside the SQL*Plus environment, run the following command to check the available tablespaces (we are looking for the USERS tablespace):
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
--- ------------ --- --- --- ------- --------
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 TEMP NO NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
--- ------------ --- --- --- ------- ---------
3 USERS YES NO YES 3
4 EXAMPLE YES NO YES 3
13 rows selected.
SQL> col file_name format a50SQL> select file_name, blocks, tablespace_name from dba_data_files;
FILE_NAME BLOCKS TABLESPACE_NAME
--------------------------------------------- ------- ---------------
/ora01/app/oracle/oradata/ORA12C/system01.dbf 99840 SYSTEM
/ora01/app/oracle/oradata/ORA12C/sysaux01.dbf 78080 SYSAUX
/ora01/app/oracle/oradata/ORA12C/users01.dbf 640 USERS
/ora01/app/oracle/oradata/ORA12C/undotbs01.dbf 20480 UNDOTBS1
-
Before proceeding, to transport a tablespace from a source database to a target database we need to have a common file system directory between both systems. Therefore, we will use an NFS file system (/source), which will be shared between both systems, as shown below.
Open a terminal window, change to user root, and create the source directory on system 1:
[oracle@oracle12c1 Desktop]$ su - rootPassword:
[root@oracle12c1 ~]# mkdir /source
-
Include a share inside the /etc/exports file pointing to the /source directory on system 1, as shown below:
[root@oracle12c1 ~]# vi /etc/exports
[root@oracle12c1 ~]# more /etc/exports
/source *(rw)
In this case, we are sharing the source directory to all hosts, but in a real case, we should restrict the sharing to a few systems.
To make the changes take effect, execute the following commands on system 1:
[root@oracle12c1 ~]# exportfs -a[root@oracle12c1 ~]# exportfs
/source <world>
-
For testing purposes, stop the firewall that is acting on system 1 by running the following command:
[root@oracle12c1 ~]# systemctl status firewalld.service
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled)
Active: active (running) since Thu 2015-09-03 14:15:04 BRT; 18min ago
Main PID: 608 (firewalld)
CGroup: /system.slice/firewalld.service
└─608 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
Sep 03 14:15:04 oracle12c1.example.com systemd[1]: Started firewalld - dynamic firewall daemon.
[root@oracle12c1 ~]# systemctl stop firewalld.service[root@oracle12c1 ~]# systemctl status firewalld.service
firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled)
Active: inactive (dead) since Thu 2015-09-03 14:34:22 BRT; 11s ago
Process: 608 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
Main PID: 608 (code=exited, status=0/SUCCESS)
Sep 03 14:15:04 oracle12c1.example.com systemd[1]: Started firewalld - dynamic firewall daemon.
Sep 03 14:34:19 oracle12c1.example.com systemd[1]: Stopping firewalld - dynamic firewall daemon...
Sep 03 14:34:22 oracle12c1.example.com systemd[1]: Stopped firewalld - dynamic firewall daemon.
[ OK ]
-
On system 1, verify that the NFS service has started. If it has not, start it:
[root@oracle12c1 ~]# systemctl status nfs-server
nfs-server.service - NFS Server
Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; disabled)
Active: inactive (dead)
[root@oracle12c1 ~]# systemctl start nfs-server[root@oracle12c1 ~]# systemctl status nfs-server
nfs-server.service - NFS Server
Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; disabled)
Active: active (exited) since Thu 2015-09-03 14:40:28 BRT; 3s ago
Process: 4245 ExecStart=/usr/sbin/rpc.nfsd $RPCNFSDARGS $RPCNFSDCOUNT (code=exited, status=0/SUCCESS)
Process: 4243 ExecStartPre=/usr/sbin/exportfs -r (code=exited, status=0/SUCCESS)
Process: 4241 ExecStartPre=/usr/libexec/nfs-utils/scripts/nfs-server.preconfig (code=exited, status=0/SUCCESS)
Main PID: 4245 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/nfs-server.service
Sep 03 14:40:28 oracle12c1.example.com systemd[1]: Starting NFS Server...
Sep 03 14:40:28 oracle12c1.example.com systemd[1]: Started NFS Server.
[root@oracle12c1 ~]# systemctl status rpcbind
rpcbind.service - RPC bind service
Loaded: loaded (/usr/lib/systemd/system/rpcbind.service; enabled)
Active: active (running) since Thu 2015-09-03 14:15:06 BRT; 25min ago
Process: 1391 ExecStart=/sbin/rpcbind -w ${RPCBIND_ARGS} (code=exited, status=0/SUCCESS)
Main PID: 1596 (rpcbind)
CGroup: /system.slice/rpcbind.service
└─1596 /sbin/rpcbind -w
Sep 03 14:15:06 oracle12c1.example.com systemd[1]: Started RPC bind service.
-
On system 1, make the shared temporary directory writeable and readable for all users:
[root@oracle12c1 ~]# chmod 777 /source/
Of course, we could have used an access control list (ACL) to restrict the permissions to only the oracle user. However, for the purpose of this demonstration, the idea is to keep things simple.
-
On system 2, open a terminal window, log in using the root account, and mount the NFS file system (/source) from system 1, as shown in the following commands:
```
[root@oracle12c2 ~]# mkdir /source[root@oracle12c2 ~]# mount 192.168.1.107:/source /source[root@oracle12c2 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol_oracle12c1-root 50G 20G 31G 39% /
devtmpfs 1.8G 0 1.8G 0% /dev
tmpfs 1.9G 148K 1.9G 1% /dev/shm
tmpfs 1.9G 9.0M 1.8G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/mapper/ol_oracle12c1-home 46G 5.3G 41G 12% /home
/dev/sda1 497M 208M 290M 42% /boot
192.168.1.107:/source 50G 20G 31G 40% /source
```
- Create directory objects on the source and target to make the transport possible. Remember that these directory objects must correspond to physical directories.
On the source host (system 1), execute the following:
```
SQL> create directory source_pump_directory as '/source';
Directory created.
SQL> create directory source_datafiles_directory as '/ora01/app/oracle/oradata';
Directory created.
SQL> set linesize 300SQL> col directory_name format a27SQL> col directory_path format a60SQL> select directory_name, directory_path from all_directories;
DIRECTORY_NAME DIRECTORY_PATH
-------------------------- -------------------------------------------
ORACLE_HOME /
ORACLE_BASE /
OPATCH_LOG_DIR /ora01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_SCRIPT_DIR /ora01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_INST_DIR /ora01/app/oracle/product/12.1.0/db_1/OPatch
DATA_PUMP_DIR /ade/b/1281484529/oracle/admin/seeddata/dpdump/
XSDDIR /ora01/app/oracle/product/12.1.0/db_1/rdbms/xml/schema
ORACLE_OCM_CONFIG_DIR /ora01/app/oracle/product/12.1.0/db_1/ccr/state
XMLDIR /ora01/app/oracle/product/12.1.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR2 /ora01/app/oracle/product/12.1.0/db_1/ccr/state
SOURCE_PUMP_DIRECTORY /source
DIRECTORY_NAME DIRECTORY_PATH
--------------------------- ---------------------------------------------
SOURCE_DATAFILES_DIRECTORY /ora01/app/oracle/oradata
12 rows selected.
```
On the target host (system 2), execute the following:
```
SQL> create directory destination_pump_directory as '/source';
Directory created.
SQL> set linesize 300SQL> col directory_name format a28SQL> col directory_path format a60SQL> select directory_name, directory_path from all_directories;
DIRECTORY_NAME DIRECTORY_PATH
----------------------------- ---------------------------------------------
ORACLE_HOME /
ORACLE_BASE /
OPATCH_LOG_DIR /ora01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_SCRIPT_DIR /ora01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_INST_DIR /ora01/app/oracle/product/12.1.0/db_1/OPatch
DATA_PUMP_DIR /ade/b/1281484529/oracle/admin/seeddata/dpdump/
XSDDIR /ora01/app/oracle/product/12.1.0/db_1/rdbms/xml/schema
ORACLE_OCM_CONFIG_DIR /ora01/app/oracle/product/12.1.0/db_1/ccr/state
XMLDIR /ora01/app/oracle/product/12.1.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR2 /ora01/app/oracle/product/12.1.0/db_1/ccr/state
DESTINATION_PUMP_DIRECTORY /source
11 rows selected.
```
- Create the common user
c##aborges and associate the password hacker123! to it. We should remember that common users exist on the root container. A common user must have a unique username across all containers and it must begin with either c## or C##. Then check the default tablespace, as shown below.
**Note**: If you are working with Oracle Database 11_g_, you do not need to use either prefix at the beginning of the username nor do you need to use the `container=all` argument in the command below.
```
SQL> create user c##aborges identified by "hacker123!" container=all;
User created.
SQL> grant connect to c##aborges;
Grant succeeded.
SQL> grant create table to c##aborges;
Grant succeeded.
SQL> alter user c##aborges quota 100M on USERS;
User altered.
SQL> set linesize 300SQL> col username format a15SQL> col default_tablespace format a15SQL> col temporary_tablespace format a15SQL> select username, default_tablespace, temporary_tablespace from dba_users where username= 'C##ABORGES';
USERNAME DEFAULT_TABLESP TEMPORARY_TABLE
------------ --------------- ---------------
C##ABORGES USERS TEMP
```
- Using the
c##aborges user, create two tables (test and test2). Additionally, insert some sample data into them, as shown below:
```
[oracle@oracle12c1 Desktop]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 8 21:03:09 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: c##aborgesEnter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create table test(name varchar2(20), country varchar2(20));
Table created.
SQL> create table test2(name varchar2(20), city varchar2(20));
Table created.
SQL> insert into test values ('Alexandre','Brazil');SQL> insert into test values ('Laura','USA');SQL> insert into test values ('Fernanda','Brazil');SQL> insert into test values ('Marc','Austria');SQL> insert into test2 values ('Alexandre','Sao Paulo');SQL> insert into test2 values ('Laura','Denver');SQL> insert into test2 values ('Fernanda','Sao Paulo');SQL> insert into test2 values ('Marc','Vienna');
SQL> select * from test;
NAME COUNTRY
---------------- --------------------
Alexandre Brazil
Laura USA
Fernanda Brazil
Marc Austria
SQL> select * from test2;
NAME CITY
---------------- --------------------
Alexandre Sao Paulo
Laura Denver
Fernanda Sao Paulo
Marc Vienna
SQL> exit
```
- To verify that the USERS tablespace is self-consistent (there are no external dependencies), run the following commands on system 1 as the
sys user (not the c##aborges user):
```
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
```
If there are no violations, the USER tablespace is free of external dependencies.
- As a fundamental requirement, we need to put the USERS tablespace in read-only mode on system 1:
```
SQL> alter tablespace USERS read only;
Tablespace altered.
```
- On system 2, check which tablespaces are available by running the following commands:
```
SQL> col file_name format a50SQL> set linesize 300SQL> select file_name, blocks, tablespace_name from dba_data_files;
FILE_NAME BLOCKS TABLESPACE_NAME
---------------------------------------------- ------- ------------------
/ora01/app/oracle/oradata/ORA12C/system01.dbf 99840 SYSTEM
/ora01/app/oracle/oradata/ORA12C/sysaux01.dbf 72960 SYSAUX
/ora01/app/oracle/oradata/ORA12C/users01.dbf 640 USERS
/ora01/app/oracle/oradata/ORA12C/undotbs01.dbf 21120 UNDOTBS1
```
- On system 1, we need to generate the metadata that will be used on system 2 during the import process. The following information is provided by the
expdp command:
* `user/password` -> `system/hacker123!`
* `dumpfile` -> `test_aborges.dmp (metadata)`
* `directory` -> `source_pump_directory (/source)`
* `transport_tablespaces` -> `USERS`
Therefore, now we can proceed by executing the following commands as the `oracle` user:
```
[oracle@oracle12c1 ~]$ expdp system/hacker123! dumpfile=test_aborges.dmp directory=source_pump_directory transport_tablespaces=USERS metrics=y exclude=statistics
Export: Release 12.1.0.2.0 - Production on Thu Sep 3 19:02:31 2015
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 and Real Application Testing options
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=test_aborges.dmp directory=source_pump_directory transport_tablespaces=USERS metrics=y exclude=statistics
Startup took 1 seconds
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Completed 1 PLUGTS_BLK objects in 8 seconds
Processing object type TRANSPORTABLE_EXPORT/TABLE
Completed 6 TABLE objects in 20 seconds
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Completed 4 INDEX objects in 4 seconds
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Completed 2 CONSTRAINT objects in 1 seconds
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Completed 6 TABLE_STATISTICS objects in 2 seconds
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Completed 1 PLUGTS_BLK objects in 1 seconds
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/source/test_aborges.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
/ora01/app/oracle/oradata/ORA12C/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Sep 3 19:03:11 2015 elapsed 0 00:00:39
[root@oracle12c2 ~]# ls /sourceexport.log test_aborges.dmp
```
- Here, our life is made simpler because we will use the same
/source directory (shared between both system 1 and system 2) to transfer the users01.dbf datafile (as shown below on system 1) to system 2 (oracle12c2) as the USERS2.dbf datafile. (We can't name the datafile user01.dbf because that file already exists.) We should remember the following:
* `source_datafile_directory` -> `/ora01/app/oracle/oradata`
* `source_pump_directory` -> `/source`
* `USERS2.dbf` -> the new name of the `users01.dbf` datafile from system 1
* `ORA12C` -> `instance`
```
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ORA12C
SQL> begin 2 DBMS_FILE_TRANSFER.PUT_FILE 3 ('source_datafiles_directory', 'ORA12C/users01.dbf', 'source_pump_directory', 'USERS2.dbf','ORA12C'); 4 end; 5 /
PL/SQL procedure successfully completed.
```
- On system 2, create a new user (
c##aborges) according to the same rules from system 1 and grant minimal privileges to it:
```
SQL> create user c##aborges identified by "hacker123!";
User created.
SQL> grant connect to c##aborges;
Grant succeeded.
SQL> set linesize 300SQL> col username format a15SQL> col default_tablespace format a15SQL> col temporary_tablespace format a15SQL> select username, default_tablespace, temporary_tablespace from dba_users where username= 'C##ABORGES';
USERNAME DEFAULT_TABLESP TEMPORARY_TABLE
--------------- --------------- ---------------
C##ABORGES USERS TEMP
```
- On system 2, execute the import, as shown below, where:
* The `user/password` is `system/hacker123!`
* `destination_pump_directory` (`/source`) is the location of the metadata files
* `transport_datafiles` is the complete path from datafile `USERS2.dbf`
* `REMAP_TABLESPACE` renames users to users2 tablespace
* `REMAP_SCHEMA` puts tables under the `c##aborges` schema
```
[oracle@oracle12c2 ~]$ impdp system/hacker123! directory=destination_pump_directory dumpfile=test_aborges.dmp transport_datafiles='/source/USERS2.dbf' REMAP_TABLESPACE=users:users2
Import: Release 12.1.0.2.0 - Production on Thu Sep 3 20:32:08 2015
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 and Real Application Testing options
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=destination_pump_directory dumpfile=test_aborges.dmp transport_datafiles=/source/USERS2.dbf REMAP_TABLESPACE=users:users2
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "OJVMSYS"."OJDS$BINDINGS$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OJVMSYS"."OJDS$INODE$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OJVMSYS"."OJDS$REFADDR$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OJVMSYS"."OJDS$PERMISSIONS$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OJVMSYS"."OJDS$SHARED$OBJ$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "OJVMSYS"."OJDS$ATTRIBUTES$" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 6 error(s) at Thu Sep 3 20:32:12 2015 elapsed 0 00:00:03
```
There are few errors, but they are not relevant because we are not interested in JVM support here. Additionally, this is a locked account, as you can see below on host system 2:
```
SQL> select username,oracle_maintained,account_status from dba_users order by oracle_maintained,username;
USERNAME O ACCOUNT_STATUS
----------- - --------------------------------
C##ABORGES N OPEN
ANONYMOUS Y EXPIRED & LOCKED
APEX_040200 Y EXPIRED & LOCKED
APEX_PUBLIC_USE Y EXPIRED & LOCKED
R
APPQOSSYS Y EXPIRED & LOCKED
AUDSYS Y EXPIRED & LOCKED
CTXSYS Y EXPIRED & LOCKED
DBSNMP Y EXPIRED & LOCKED
DIP Y EXPIRED & LOCKED
USERNAME O ACCOUNT_STATUS
----------- - --------------------------------
DVF Y EXPIRED & LOCKED
DVSYS Y EXPIRED & LOCKED
FLOWS_FILES Y EXPIRED & LOCKED
GSMADMIN_INTERN Y EXPIRED & LOCKED
AL
GSMCATUSER Y EXPIRED & LOCKED
GSMUSER Y EXPIRED & LOCKED
LBACSYS Y EXPIRED & LOCKED
MDDATA Y EXPIRED & LOCKED
MDSYS Y EXPIRED & LOCKED
USERNAME O ACCOUNT_STATUS
----------- - --------------------------------
OJVMSYS Y EXPIRED & LOCKED
OLAPSYS Y EXPIRED & LOCKED
ORACLE_OCM Y EXPIRED & LOCKED
ORDDATA Y EXPIRED & LOCKED
ORDPLUGINS Y EXPIRED & LOCKED
ORDSYS Y EXPIRED & LOCKED
OUTLN Y EXPIRED & LOCKED
SI_INFORMTN_SCH Y EXPIRED & LOCKED
EMA
SPATIAL_CSW_ADM Y EXPIRED & LOCKED
USERNAME O ACCOUNT_STATUS
----------- - --------------------------------
IN_USR
SPATIAL_WFS_ADM Y EXPIRED & LOCKED
IN_USR
SYS Y OPEN
SYSBACKUP Y EXPIRED & LOCKED
SYSDG Y EXPIRED & LOCKED
SYSKM Y EXPIRED & LOCKED
SYSTEM Y OPEN
WMSYS Y EXPIRED & LOCKED
USERNAME O ACCOUNT_STATUS
----------- - --------------------------------
XDB Y EXPIRED & LOCKED
XS$NULL Y EXPIRED & LOCKED
36 rows selected.
```
- On system 2, if something bad happened, drop the tablespace, as shown below, and repeat step 20:
```
SQL> drop tablespace USERS2 including contents cascade constraints;
```
- On system 2, to rename the
users2.dbf datafile (/source/USERS2.dbf) to /ora01/app/oracle/oradata/ORA12C/USERS2.dbf (where all remaining datafiles are), run the following commands:
```
SQL> alter tablespace USERS2 offline normal;
Tablespace altered.
SQL> !cp /source/USERS2.dbf /ora01/app/oracle/oradata/ORA12C
SQL> alter tablespace USERS2 rename datafile '/source/USERS2.dbf' to '/ora01/app/oracle/oradata/ORA12C/USERS2.dbf';
Tablespace altered.
```
- Verify that the USERS2 tablespace exists by running the following command:
```
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
--- ------- --- --- --- ------- ----------
1 SYSAUX YES NO YES 1
0 SYSTEM YES NO YES 1
2 UNDOTBS1 YES NO YES 1
4 USERS YES NO YES 1
3 TEMP NO NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 2
2 TEMP NO NO YES 2
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 TEMP NO NO YES 3
TS# NAME INC BIG FLA ENC CON_ID
--- ------- --- ---- -- ------- ----------
3 USERS YES NO YES 3
4 EXAMPLE YES NO YES 3
6 USERS2 YES NO YES 1
```
- On system 2, verify that the
USERS2.dbf datafile was correctly renamed by executing the following commands:
```
SQL> col file_name format a47SQL> col tablespace_name format a10SQL> select file_name, blocks, tablespace_name from dba_data_files;
FILE_NAME BLOCKS TABLESPACE
----------------------------------------------- ------ ----------
/ora01/app/oracle/oradata/ORA12C/system01.dbf 101120 SYSTEM
/ora01/app/oracle/oradata/ORA12C/sysaux01.dbf 79360 SYSAUX
/ora01/app/oracle/oradata/ORA12C/undotbs01.dbf 21120 UNDOTBS1
/ora01/app/oracle/oradata/ORA12C/users01.dbf 640 USERS
/ora01/app/oracle/oradata/ORA12C/USERS2.dbf USERS2
```
- On system 2, change the USERS2 tablespace to "online" status and read-write mode:
```
SQL> alter tablespace USERS2 online;
Tablespace altered.
SQL> alter tablespace USERS2 read write;
Tablespace altered.
SQL> col file_name format a48SQL> col blocks format 9999999SQL> col tablespace_name format a15SQL> select file_name, blocks, tablespace_name from dba_data_files;
FILE_NAME BLOCKS TABLESPACE_NAME
------------------------------------------------ -------- ---------------
/ora01/app/oracle/oradata/ORA12C/system01.dbf 101120 SYSTEM
/ora01/app/oracle/oradata/ORA12C/sysaux01.dbf 80640 SYSAUX
/ora01/app/oracle/oradata/ORA12C/users01.dbf 640 USERS
/ora01/app/oracle/oradata/ORA12C/undotbs01.dbf 21120 UNDOTBS1
/ora01/app/oracle/oradata/ORA12C/USERS2.dbf 640 USERS2
```
- On system 2, log in as the
c##aborges user and check whether all appropriate tables appear under the schema, by running the following commands:
```
[oracle@oracle12c2 /]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 8 22:10:47 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: c##aborgesEnter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
C##ABORGES
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
TEST2
SQL> select * from test;
NAME COUNTRY
---------------- --------------------
Alexandre Brazil
Laura USA
Fernanda Brazil
Marc Austria
SQL> select * from test2;
NAME CITY
---------------- --------------------
Alexandre Sao Paulo
Laura Denver
Fernanda Sao Paulo
Marc Vienna
```
- Finally, on system 1 and as the
sys user, return the USERS tablespace to read-write mode:
```
SQL> alter tablespace users read write;
Tablespace altered.
```
Conclusion
Oracle Database 12_c_ offers several ways to migrate data between databases and there is no better or worse way. This article showed one way to accomplish this.
See Also
In addition, here are some links to other things I've written:
About the Author
Alexandre Borges is an Oracle ACE in Oracle Solaris and has been teaching courses on Oracle Solaris since 2001. He worked as an employee and a contracted instructor at Sun Microsystems, Inc. until 2010, teaching hundreds of courses on Oracle Solaris (such as Administration, Networking, DTrace, and ZFS), Oracle Solaris Performance Analysis, Oracle Solaris Security, Oracle Cluster Server, Oracle/Sun hardware, Java Enterprise System, MySQL Administration, MySQL Developer, MySQL Cluster, and MySQL tuning. He was awarded the title of Instructor of the Year twice for his performance teaching Sun Microsystems courses. Since 2009, he has been imparting training at Symantec Corporation (NetBackup, Symantec Cluster Server, Storage Foundation, and Backup Exec) and EC-Council [Certified Ethical Hacking (CEH)]. In addition, he has been working as a freelance instructor for Oracle education partners since 2010. In 2014, he became an instructor for Hitachi Data Systems (HDS) and Brocade.
Currently, he also teaches courses on Malware Analysis, Reverse Engineering, Memory Forensic Analysis, Assembly, Digital Forensic Analysis, and Hacking. Alexandre is also an (ISC)2 CISSP instructor and has been writing articles on the Oracle Technical Network on a regular basis since 2013.