Moving Oracle Database 12c Tablespaces Using Transportable Tablespaces

Версия 1

    Moving Oracle Database 12c 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 11g Release 2. However, Oracle Database 11g Release 2 entered the Extended Support phase as of February 2015, so Oracle recommends moving to Oracle Database 12c.

     

    Introduction

     

    It is not unusual for me to hear from administrators (not necessarily database administrators) questions about methods for moving an Oracle Database 12c 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 12c 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.)

     

    1. 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 sysdba
      Enter password: Connected to an idle instance. SQL> startup
      ORACLE 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.

       

    2. 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).

       

    3. 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 300
      SQL> col platform_name format a30
      SQL> 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 a20
      SQL> 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

       

    4. 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 a50
      SQL> 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

       

    5. 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 - root
      Password: [root@oracle12c1 ~]# mkdir /source

       

    6. 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>

       

    7. 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  ]

       

    8. 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.

       

    9. 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.

       

    10. 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

       

    11. 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 300
      SQL> col directory_name format a27
      SQL> col directory_path format a60
      SQL> 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 300
      SQL> col directory_name format a28
      SQL> col directory_path format a60
      SQL> 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.

       

    12. 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 11g, 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 300
      SQL> col username format a15
      SQL> col default_tablespace format a15
      SQL> col temporary_tablespace format a15
      SQL> select username, default_tablespace, temporary_tablespace from dba_users where username= 'C##ABORGES';
      USERNAME     DEFAULT_TABLESP TEMPORARY_TABLE ------------ --------------- --------------- C##ABORGES   USERS           TEMP

       

    13. 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##aborges
      Enter 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

       

    14. 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.

       

    15. 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.

       

    16. On system 2, check which tablespaces are available by running the following commands:

      SQL> col file_name format a50
      SQL> set linesize 300
      SQL> 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

       

    17. 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 /source
      export.log  test_aborges.dmp

       

    18. 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.

       

    19. 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 300
      SQL> col username format a15
      SQL> col default_tablespace format a15
      SQL> col temporary_tablespace format a15
      SQL> select username, default_tablespace, temporary_tablespace from dba_users where username= 'C##ABORGES';
      USERNAME        DEFAULT_TABLESP TEMPORARY_TABLE --------------- --------------- --------------- C##ABORGES      USERS           TEMP

       

    20. 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.

       

    21. 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;

       

    22. 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.

       

    23. 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

       

    24. On system 2, verify that the USERS2.dbf datafile was correctly renamed by executing the following commands:

      SQL> col file_name format a47
      SQL> col tablespace_name format a10
      SQL> 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

       

    25. 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 a48
      SQL> col blocks format 9999999
      SQL> col tablespace_name format a15
      SQL> 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

       

    26. 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##aborges
      Enter 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

       

    27. 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 12c 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.