Establishing a link between oracle databases

This article show how to establish a link between two Oracle databases. Here we'll establish such a link between two 'virtual' databases, called phoenix and zeus.

1. SSH Tunnel

The first step is to create a ssh tunnel between the two machines, hosting the databases. To perform that, we create a tunnel, redirecting phoenix 1522 port to zeus 1521 port (Oracle listening port).

phoenix% ssh oracle@zeus -L 1522:zeus:1521

From now, all connections on port 1522 of phoenix are redirected to zeus, on port 1521.

2. Virtual SID

Second step now, inform Oracle on phoenix of a new SID it can connect to. Of course this SID will be a redirection on zeus. For this, we need to get some informations about the target database, on zeus. Let's open the tnsnames.ora file, located usually in the folder $ORACLE_HOME/network/admin, on zeus machine. Now find the informations about your target database (here i want to connect to REMOTE_DB) :

...
REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = zeus)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db_DGMGRL)

    )
  )
...

We find the remote_db sid definition, so let's write down the associated service name : remote_db_DGMGRL.
We are now able to create a SID on phoenix to connect to this database. So let's open phoenix's tnsnames.ora  (same location, $ORACLE_HOME/network/admin), and add this :

REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = phoenix)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db_DGMGRL)

    )
  )

We just created an alias towards host phoenix on port 1522. But remember, we established a SSH tunnel redirecting every connection on phoenix:1522 to zeus:1521. So actually our sid will connect to zeus machine, on port 1521. Smells good :).  The service name we specified is remote_db_DGMGRL, and it's the service name on zeus associated to our target database. Smells even better :D.
Save and test with a simple tns ping :

phoenix% tnsping remote_db

TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 24-NOV-2010 12:48:58


Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = phoenix)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = remote_db_DGMGRL)))

OK (40 msec)

Connexion time of 40 ms shows this is not a localhost connection.
What is tested here is not the service connection, this will come later. Try to specify a wrong service name, you'll still be ok with tnsping. We just test connexion to the host/port we gave to our new sid.
If you change the sid, the hostname, the port or if you stop the ssh tunnel, tnsping will fail.
Now, on with the show, let's create our database link.

3. Database link

On phoenix, let's connect to the database, with sql*plus.
The following command creates the link :

SQL> create public database link my_far_away_db connect to db_schema identified by db_password using 'remote_db';

Database link created.

Let's check our data now :


SQL> select count(*) from my_table@my_far_away_db;

COUNT(*)
----------
     30266

To compare, assuming phoenix hosts the same database as zeus with different data (for instance a development database on phoenix, and a production database on zeus), you can check the difference without using the link :

SQL> select count(*) from db_schema.my_table;

COUNT(*)
----------
     27137

Voilà !

4. Link deletion

To delete a database link, its a one liner :

SQL> drop public database link my_far_away_db;

Database link dropped.

If you don't need this link anymore, you can remove the sid definition in tnsnames.ora, and stop your ssh tunnel.

Oracle table data comparison through time

Another flashback tips. This one allows you to compare data from your tables through time. Keep in mind the flashback setting of your oracle instance defines how far back in time you can go.

The key here is to perform an inner join between the table and its flashback counterpart. Here's how :

select current.value, past.valuefrom my_table current

inner join my_table as of timestamp sysdate-1 past
on current.id=past.id
where current.blah > 0

This query compares table my_table between its current state and its state one day ago (sysdate-1). Of course, one can use as of timestamp to_timestamp(....) to set a more specific time. Inner join is performed on the id field (if there's one :) ), and i added a filter (the where part), but this is for the show. The core of the functionality is the inner join w/ as of timestamp part.  

Finding blocked sessions

Ever had an oracle command blocked by a hanging session? Here's how to find them...and kill them if you want to.


select 
  c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#, 
  b.status,
  b.osuser,
  b.machine 
from 
  v$locked_object a,
  v$session b,
  dba_objects c 
where 
  b.sid = a.session_id 
  and a.object_id = c.object_id;

This query will display blocked session, along with their owner, and other useful info.

If you wish to kill one of the session displayed, grab the session sid and serial, and execute the following :

alter system kill session sid,serial;

Done.

Show table/schema size difference in time

Here's how to display the size evolution of a table or schema between its current state, and its state some time before. The distance you can go back in time depends of course on the flashback setting of your oracle instance.

When executed, those SQL commands will ask you for a date (past time) and a schema name. For the table version, it will output the list of tables of the given schema. DBA rights are required (access to dba_segments table).

List of table sizes for a schema, compared between now and the given past date :

select ds.tablespace_name,
   ds.owner,
   ds.segment_name table_name,   
   trunc(sum(ods.bytes)/(1024*1024)) previous_size_Mo,
   trunc(sum(ds.bytes)/(1024*1024)) current_size_Mo
from dba_segments ds
inner join dba_segments as of timestamp to_timestamp('&date','YYYY-MM-DD HH:MI:SS') ods
 on ods.owner=ds.owner
 and ods.segment_type=ds.segment_type
 and ods.segment_name=ds.segment_name
where ds.segment_type='TABLE'
and ds.owner=uper('&owner')
group by ds.tablespace_name, ds.owner, ds.segment_name
having sum(ds.bytes)/(1024*1024) > 5
order by current_size_Mo desc;


Schema size difference for the given owner between now and the given past date :

select ds.tablespace_name,
    sum(os.bytes)/1024/1024 AS previous_total_size_Mo,
     sum(ds.bytes)/1024/1024 AS current_total_size_Mo
from dba_segments ds
inner join dba_segments as of timestamp to_timestamp('&date','YYYY-MM-DD HH:MI:SS') os
 on os.owner=ds.owner
 and os.segment_type=ds.segment_type
 and os.segment_name=ds.Segment_name
where ds.owner = upper('&owner')
group by ds.tablespace_name