To share data between two databases, we can create a database link by running commands such as: 

create database link dblink1 connect to system identified by password1 using ‘ORCL1′;

These commands create a link called “dblink1” which connects as the user SYSTEM to a remote database - using the connect string “ORCL1”.  The connect string “ORCL1” is an entry in the tnsnames.ora file.

Once we’ve created our link, we can then run commands to check that we can access the remote database.   If the account used for the database link has privileges to access v$database, then we are able to use the query below to check the name of the remote database. 

select name from v$database@dblink1;

Alternatively, we could run a query to select data that we know only exists in the remote database.

Once our database link has been created, we then create a synonym (alias) for any remote objects referenced by the link. 

e.g.  create synonym emp2 for scott.emp@dblink1; 

This makes query writing more straightforward and also hides complexity from the end users.

Sometimes, for security or confidentiality reasons, we want to create a link, but don’t want to add an entry to the tnsnames.ora file.

To do this, we can create a link by specifying the full connection string, as shown below:

create database link dblink1 connect to system identified by password1 using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oserver1)(PORT=1526))(CONNECT_DATA
=(SID=ORCL1)))’;

An alternative solution is to use the EZCONNECT naming method, which is available from Oracle 10g onwards:

create database link dblink1 connect to system identified by password1 using ‘oserver1:1526/ORCL1′;

Notes:

(i) This scenario assumes that even if we were using other methods for client name resolution - such as Oracle Internet Directory, or an Oracle Names Server - then we don’t want to add permanent entries to these either.
(ii) For ease of explanation, we are assuming that GLOBAL_NAMES is set to be FALSE in both databases.  If it were set to TRUE, then our database link would need to have the same name as the global_name of the target database.  (Select * from global_name; - to check this value).
(iii) The EZCONNECT syntax assumes that we have an entry for
“EZCONNECT” in our sqlnet.ora file, as a valid naming method under the NAMES.DIRECTORY_PATH parameter.
(iv) The tnsnames.ora file is usually located in   $ORACLE_HOME/network/admin on Unix or  %ORACLE_HOME%/network/admin on Windows. If we had set the TNS_ADMIN environment variable or registry key, then it would be at the location specified by this parameter.

This is the final part 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 and part 2 used Datapump.

Part 3 discussed the use of RMAN (Recovery Manager) and in the final part we’ll be using DBMS_STREAMS_TABLESPACE_ADM to clone a tablespace.  

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, 2 or 3 then run the commands below on the local and remote databases, 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
‘/u02/oradata/ORCL/TTS_TEST01.dbf’ size 10M autoextend on maxsize 100M;

CREATE TABLESPACE TTS_TEST_IDX DATAFILE
‘/u02/oradata/ORCL/TTS_TEST_IDX01.dbf’ size 10M autoextend on maxsize 100M;
  
Create 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′);
COMMIT;

Using DBMS_STREAMS_TABLESPACE_ADM to clone tablespaces

You can create a clone of an existing tablespace and then attach it to a remote database.   (These methods would normally be used for tablespace versioning and are available from 10g Release 1).

Note: It is also possible to detach and then re-attach a tablespace, but as we wish to keep our original tablespace in this scenario, the DETACH option will not be discussed.   

(You should be aware that you can’t use these procedures for objects owned by SYS). 

CLONE TABLESPACE

(i) Create an operating system and corresponding database directory for the new version of the tablespace.  

e.g.        mkdir /u01/oradata/ORCL/copy_datafiles

Then login to the database as SYS as sysdba and run the commands:

CREATE OR REPLACE DIRECTORY OBJ_COPY AS ‘/u01/oradata/ORCL/copy_datafiles’;

This will be used as the setting for the TABLESPACE_DIRECTORY_OBJECT parameter later on.

(ii) Create a clone of the tablespace.   (These commands also add a new version of the tablespace to a filegroup repository called TTS_TEST_FG, which is created automatically.

DECLARE

tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

BEGIN
tbs_set(1) := ‘TTS_TEST’;
tbs_set(2) := ‘TTS_TEST_IDX’;
DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
tablespace_names => tbs_set,
tablespace_directory_object => ‘OBJ_COPY’,
file_group_name => ‘TTS_TEST_FG’,
version_name => ‘V1′);
END;
/

Notes:  version_name and file_group_name can be set to anything you like.   If you need to change the name of the datafile later, then you can use   DBMS_FILE_GROUP.ALTER_FILE.

If you get any errors when running these procedures, run the query: 

SELECT REASON FROM SYS.PLUGGABLE_SET_CHECK WHERE TS1_NAME IN (’TTS_TEST’, ‘TTS_TEST_IDX’);

(iii) A copy of the datafile, the export dumpfile and the logfile from this work will all go to the OBJ_COPY database directory.   The datafile has been cloned, but is not attached to the database, as we can only have one version of a tablespace attached and online at any time.

(iv) We can query DBA_FILE_GROUP_TABLESPACES and DBA_FILE_GROUP_FILES for information on this new version of the tablespace.

ATTACH TABLESPACE

To be able to use the cloned tablespace in another database, we now need to attach the tablespace.

(i) You can use these procedures when working with tablespaces that have previously been processed by DBMS_STREAMS_TABLESPACE_ADM. DETACH_TABLESPACE and CLONE_TABLESPACE procedures, or with files created by the RMAN or Datapump export transport tablespace commands.

(ii)       Copy the datafile to the datafile location of the remote database.  Copy the export dumpfile to the DATA_PUMP_DIR directory of the remote database.   

(iii)     In the remote database,  create a database directory pointing to the location of the new datafiles as SYS as sysdba:

CREATE OR REPLACE DIRECTORY OBJ_LOAD AS ‘/u02/oradata/ORCL2′;

This will be used as the setting for the TABLESPACE_DIRECTORY_OBJECT parameter later on.

(iv) Run the commands below against the remote database from the DATA_PUMP_DIR location, which contains the export dumpfile. This will attach the tablespace to the database.  (Amend the dump_file.file_name parameter to match the name of your export dumpfile before running the commands).
set serveroutput on

DECLARE
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;
  ts_names       dbms_streams_tablespace_adm.tablespace_set;

BEGIN
  dump_file.file_name :=  ‘expdat22.dmp’;
  dump_file.directory_object := ‘DATA_PUMP_DIR’;
   tbs_files( 1).file_name :=  ‘TTS_TEST01.dbf’;
  tbs_files( 1).directory_object :=  ‘OBJ_LOAD’;
   tbs_files( 2).file_name :=  ‘TTS_TEST_IDX01.dbf’;
  tbs_files( 2).directory_object :=  ‘OBJ_LOAD’;
   dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);
  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line(’imported tablespace ‘|| ts_names(i));
    END LOOP;
  END IF;
END;
/

(v)  If you query DBA_TABLESPACES, you should now see that the tablespaces have been added to your remote database.   (Note: These commands won’t create a new file group in the remote database - they’ll just attach the tablespaces). 

(vi) Finally we just need to put the tablespaces into read write mode.

ALTER TABLESPACE TTS_TEST READ WRITE;

ALTER TABLESPACE TTS_TEST_IDX READ WRITE;

Note: There is also a Transport Tablespace Wizard in Oracle Enterprise Manager for carrying out these tasks.

References

(i)  Oracle documentation at: http://www.oracle.com/technology/documentation/database.html

This is the second 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.  In Part 2 we’ll use Datapump transportable 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 part 1 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
‘/u02/oradata/ORCL/TTS_TEST01.dbf’ size 10M autoextend on maxsize 100M;

  CREATE TABLESPACE TTS_TEST_IDX DATAFILE
‘/u02/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′);
COMMIT;

Using Datapump transportable tablespaces to create a copy of a table

This is similar to the export/import method, but the check for self-contained objects is carried out within the datapump command syntax, rather than as a set of separate commands.    The Datapump utilities were introduced with Oracle 10g.

For simplicity, we’ll use the DATA_PUMP_DIR directory for any Datapump work.  (To check this for your database query DBA_DIRECTORIES).
The tablespaces in the local database still need to be in read-only mode.  

(i)     ALTER TABLESPACE TTS_TEST READ ONLY;
         ALTER TABLESPACE TTS_TEST_IDX READ ONLY;

(ii)  Then run the datapump commands to export the tablespace metadata.  (Enter the commands on one continuous line).

expdp system dumpfile=data_pump_dir:TTS_TEST_datapump_export.dmp transport_tablespaces=TTS_TEST,TTS_TEST_IDX transport_full_check=y
logfile=DATA_PUMP_DIR:TTS_TEST_datapump_export.log

Copy the export dumpfile from the DATA_PUMP_DIR of the local database, to the DATA_PUMP_DIR of the remote database.

(iii)  Copy the datafiles for the tablespace from their location on the local database, to the datafile location of the remote database.  To find the names of the files in the local database you can run the query:
Select file_name from dba_data_files where tablespace_name in (’TTS_TEST’,'TTS_TEST_IDX’);

Note: As the tablespaces are currently in read only mode, we can copy the files without taking down the database.

(iv) Put the tablespaces in the local database back into read write mode

             ALTER TABLESPACE TTS_TEST READ WRITE;
             ALTER TABLESPACE TTS_TEST_IDX READ WRITE;

(v) Import the data into the remote database.   Amend the path to the export dump file accordingly. 
impdp system dumpfile=data_pump_dir:TTS_TEST_datapump_export.dmp transport_datafiles=/u02/oradata/ORCL2/TTS_TEST01.dbf,/u02/oradata/ORCL2/TTS_TEST_IDX01.dbf logfile=DATA_PUMP_DIR:TTS_TEST_datapump_import.log

Note:  The transport_datafiles parameter should match the name of the datafile on your remote database.

(vi) Make the newly-imported tablespace read write on the remote database.

ALTER TABLESPACE TTS_TEST READ WRITE;
ALTER TABLESPACE TTS_TEST_IDX READ WRITE;
 
(vii) Finally allocate a quota to our test user on the newly transported tablespaces.

ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST_IDX;

The main drawback with using Datapump and the export/import utilities is that you need to make the tablespace read only before you can take the export.  A solution to this problem is to use the RMAN transport tablespace command, which is discussed in the next post. 

References
(i)                  Oracle documentation at:
http://www.oracle.com/technology/documentation/database.html

Normally when doing a restore and recovery you go back in time. The flashback feature is also meant to be a quick recover solution to minimize downtime. But what if your company wants a read-only database? No problem just do a RMAN duplicate to a point-in-time and you are ready to go. In my case they wanted a read-only database which was a copy of the production environment. This duplicate database is used as a query database during production upgrade and must be up-to-date till 5pm. A standby/dataguard was not an option because they are unexperienced in this field so they choose for a “save” solution, a duplicate from production to acceptation.

I did have some spare space at both sites for the  production rman backups. The production online backup was started at 9 am and was defined with 5 channels and no archivelogs backuped:
 BACKUP as compressed backupset DATABASE;

After completing the RMAN backup the files are SCP-ed to the acceptation server.
Started a RMAN duplicate:
 duplicate target database to nofilenamecheck;
This is all common stuff, no problem at all for a DBA.

However the duplicate fails with an error message:
RMAN-03002: failure of Duplicate Db command at 05/04/2010 12:24:28
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log

Ok, and now? How to get that databases to a future point in time, in my case 5pm?
The trick is to recover the database until cancel and apply all the archived logs you need to get to that point in future time, 5pm!
So, start a SQL session and start recovery in this way:
 recover database using backup controlfile until cancel;

The session waits for input:
 Specify log: {=suggested | filename | AUTO | CANCEL}

Before typing in “AUTO” you have to SCP all the files needed from time of backup till 5pm to recover the duplicate database. After the last applyed log the recovery aborts with
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory

Open the database with resetlogs (alter database open resetlogs) and you have a exact copy of the production database at 5pm!
 

This is the first of four blogs which discuss some of the methods that can be used to copy tablespaces between databases.

We could carry out a standard export/import to move the data, but a more efficient way of doing this is to use transportable tablespaces, which have been available since Oracle 8.1.5.  These perform better than standard export/import or datapump import/export, as we only need to export some metadata and a copy of the datafile to the remote database.  
 
Other useful features of this method are that you can recover the tablespace up to a specific point-in-time;  to a database with a different block size (since 9i) and also to a database running on a different platform (since 10g).
 
For all of this work, our local database is called ORCL and our remote database is ORCL2.  In this blog, we’ll work through an example export/import transportable tablespace procedure.

DISCLAIMERS

The author cannot be held responsible for any data loss or damage that may occur to your database, when working through these notes.    You are strongly advised to take a backup of the database and environment before carrying out any of these procedures.  

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
‘/u02/oradata/ORCL/TTS_TEST01.dbf’ size 10M autoextend on maxsize 100M;
  CREATE TABLESPACE TTS_TEST_IDX DATAFILE
‘/u02/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

To keep things simple, 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′);
COMMIT;
 
Using Export/Import transportable tablespaces

The tablespace in the local database needs to be in read-only mode during this work.  Run the commands below as SYS as sysdba in the local database:

(i)                    ALTER TABLESPACE TTS_TEST READ ONLY;
         ALTER TABLESPACE TTS_TEST_IDX READ ONLY;
 
(ii)                Check that the objects to be moved are self-contained within the tablespaces to be moved.  (i.e.  are there any related objects in other tablespaces?)
           EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(’TTS_TEST,TTS_TEST_IDX’,TRUE);
                       SELECT * FROM TRANSPORT_SET_VIOLATIONS;
 
 Note: No rows should be returned by the last query, as we will be exporting data from both our tablespaces at the same time.  (If any rows were returned, then we may need to add additional tablespaces to our export).

(iii)                Ensure that the lines below are entered on a continuous line to run the export.
 
exp “‘/ as sysdba’” file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=TTS_TEST,TTS_TEST_IDX statistics=NONE

Copy the export dumpfile exp_tts.dmp to the remote server.

(iv)                 select file_name from dba_data_files where tablespace_name in (’TTS_TEST’,'TTS_TEST_IDX’);

Copy the datafiles listed to a new location on the remote server.  

Note: As the tablespaces are currently in read only mode, we can copy the files without taking down the database.

(v)                 Put the tablespaces back into read write mode:

ALTER TABLESPACE TTS_TEST READ WRITE;
ALTER TABLESPACE TTS_TEST_IDX READ WRITE;

 (vi)              Run the import commands below on the remote database.  Amend the path to the export dumpfile. 

imp “‘/ as sysdba’” file=exp_tts.dmp log=imp_tts.log fromuser=TTS_USER touser=TTS_USER transport_tablespace=y datafiles=/u02/oradata/ORCL2/TTS_TEST01.dbf, /u02/oradata/ORCL2/TTS_TEST_IDX01.dbf

Note:  The datafiles parameter should match the names of the datafiles on your remote database.  Ensure that the commands above are entered on a continuous line.

Once the import has completed, the tablespaces will be in read only mode.

(vii)             Put the tablespaces into read write mode on the remote database:

ALTER TABLESPACE TTS_TEST READ WRITE;
ALTER TABLESPACE TTS_TEST_IDX READ WRITE;

  (viii)           Collect statistics for the transported objects:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’TTS_USER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
cascade => TRUE);

 (ix)              Then allocate a quota to our test user on the newly transported tablespaces.

ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST_IDX;
 
From Oracle 11g Release 1, this method of transporting tablespaces is no longer supported, so in part 2 we’ll cover Datapump transportable tablespaces.    Part 3 will go through RMAN transportable tablespaces and part 4 will discuss using DBMS_STREAMS_TABLESPACE_ADM.

References
(i)                  Oracle documentation at:
http://www.oracle.com/technology/documentation/database.html