How to move data using Transportable Tablespaces

Mission:

* Move schema FXMART with all of its data from source database proddb01 to destination database testdb01
* In the same operation, rename schema FXMART with FXMARTST in the destination database testdb01.

While there are certainly a number of ways to accomplish such a task, I will show you how to use transportable tablespaces.

1. In your source database, check which tablespaces your schema is writing to:

SELECT UNIQUE TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE OWNER = 'FXMART';

TABLESPACE_NAME
FXDATA_2016
FX_MART_DATA
FXDATA_2014
FXDATA_2015

2. In your source database, check that these are self-contained:

exec dbms_tts.transport_set_check('FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015', true);

Check errors using the view TRANSPORT_SET_VIOLATIONS:

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

If you see no rows here, proceed to step 3.

3. In your source database, set the tablespaces to be transported to read only:

alter tablespace FXDATA_2016 read only;
alter tablespace FX_MART_DATA read only;
alter tablespace FXDATA_2014 read only;
alter tablespace FXDATA_2015 read only;

4. Make sure you actually have a directory object called "DP". If not, create it:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

5. In your source database, export the metadata for the tablespaces to be moved using data pump export.

First, create a parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_TABLESPACES=FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015
EXCLUDE=STATISTICS
METRICS=YES

Then, execute the export:

expdp parfile=expdp_tts.par

6. Identify the data files which belong to the tablespaces to be transported.

To identify the files, use this simple query:

SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN (
    SELECT UNIQUE TABLESPACE_NAME
    FROM DBA_SEGMENTS
    WHERE OWNER = 'FXMART'
  );

FILE_NAME
/u02/oradata/proddb01/fx_mart_data.dbf
/u02/oradata/proddb01/fxdata_2014.dbf
/u02/oradata/proddb01/fxdata_2015.dbf
/u02/oradata/proddb01/fxdata_2016.dbf

7. Transport the resulting dmpfile along with the datafiles of the tablespaces to be transported, over the network to the destination server, using scp

scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/FXMOVE_TSS.dmp oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2016.dbf oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2015.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2014.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fx_mart_data.dbf oracle@destinationserver:/u02/exports/

When the scp session is over, set the tablespaces back to read/write to allow normal usage in the source database:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

8. In the destination database, create a directory object that points to the location where the datafiles and the dump file now resides:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

9. In the destination database, drop the tablespaces to be replaced:

drop tablespace FXDATA_2016 including contents and datafiles;
drop tablespace FXDATA_2014 including contents and datafiles;
drop tablespace FX_MART_DATA including contents and datafiles;
drop tablespace FXDATA_2015 including contents and datafiles;

10. In the destination database, drop and recreate the schema to be repopulated with fresh data. Note that the user's default tablepace need to be temporarily set to USERS, as the tablespaces we intend to use as default tablespace is not yet imported:

drop user FXMARTTST cascade;

CREATE USER FXMARTTST
  IDENTIFIED BY mysecretpassword
  DEFAULT TABLESPACE USERS 
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_SCHEMAS;

-- Grant of system privileges and/or roles here

11. On the destination server, create an import parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=imp_FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_DATAFILES=/u02/oradata/testdb01/fx_mart_data.dbf,/u02/oradata/testdb01/fxdata_2014.dbf,/u02/oradata/testdb01/fxdata_2015.dbf,/u02/oradata/testdb01/fxdata_2016.dbf
REMAP_SCHEMA=FXMART:FXMARTTST
METRICS=YES

12. In the destination database, make the freshly imported tablepaces read write:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

All done. You should now be able to logon to the database using FXMARTST and use the refreshed data.

How to compare the number of objects per schema in two databases

After a migration of a database to a new server, or after any kind of replication, you would like to get some verification that your objects were created.

A potentially very useful query could help you with this task.

You need a database link from the target database back to the source database

create public database link proddb01_old 
connect to system
identified by mysecretpassword
using 'myproddb01';

Verify that the link works as intended:

SQL> select d.name,i.host_name from v$database d, v$instance i;

NAME      HOST_NAME
--------- -------------------------------
PRODDB01 myserver01.mydomain.com

Save the following in a script, and execute it from the target database via sqlplus:

set lines 200
col count_new format 99999
col count_old format 99999
col "object owner" format a30
col "Result" format a20
set pages 100

prompt ============================================
prompt compare users and their object count
prompt ============================================

WITH newprod_users AS(
 SELECT target.owner AS new_owner, count(*) AS count_new
 FROM   dba_objects target
 GROUP BY owner
 ORDER BY 1
),
oldprod_users AS (
 SELECT source.owner AS old_owner, count(*) AS count_old
 FROM   dba_objects@proddb01_old  source
 GROUP BY owner
 ORDER BY 1)
SELECT newprod_users.new_owner "object owner", 
       newprod_users.count_new "NEW PROD",  
       oldprod_users.count_old "OLD PROD",
       DECODE(newprod_users.count_new,
              oldprod_users.count_old,'Num rows identical','Num row differs') "Result"
FROM   newprod_users JOIN oldprod_users
ON     newprod_users.new_owner = oldprod_users.old_owner
ORDER BY "Result" DESC;

Example output:

============================================
compare number of users
============================================

object owner                     NEW PROD  OLD_PROD Result
------------------------------ ---------- ---------- --------------------

USER1                               1329       1329 Num rows identical
USER2                                  2          2 Num rows identical
USER3                               1750       1750 Num rows identical
USER4                                409        389 Num row differs
USER5                                961       1167 Num row differs
USER6                                251        256 Num row differs

The report can be easily modified to produce for example a semi-colon separated list, which can in turn be pulled into an MS Excel file if desirable.