Oracle Dblink from 11g to 8i

How to connect 8i database from 11g database:

After upgrading the production system from 10g to 11G, our dblinks to 8i databases are not working. All the custom codes has dblinks to 8i system. ( @OLDY) We needed to keep this dblink alive.

Since 11g to 8i dblinks are not supported as MOS note: 207303.1  We have to use a bridged connection through 10g database:

Client Version	Server Version
	12.1.0	11.2.0	11.1.0	10.2.0	10.1.0	9.2.0
12.1.0	Yes	Yes	ES	LES #7	No #3	No #3
11.2.0	Yes	Yes	ES	LES #7	No	Was #5
11.1.0	ES	ES	ES	LES #7	Was #6	Was #5
10.2.0	LES #7	LES #7	LES #7	LES	Was	Was #5
10.1.0(#4)	No	Was #6	Was #6	Was	Was	Was
9.2.0	No#8	Was #5	Was #5	Was #5	Was	Was

Key:

Yes	Supported
LES	Limited Extended Support
ES	Supported but fixes only possible for customers with Extended Support .
Was	Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible.
No	Has never been Supported

11g --> 10g(SID=BRIDGE) --> 8i (SID=OLDY)

Example setup:


On 10g database (BRIDGE) we shoud create a dblink to 8i database(OLDY): (10g-->8i)

CREATE PUBLIC DATABASE LINK "OLDY"
 CONNECT TO username
 IDENTIFIED BY <Password>
 USING '(DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=oldy_8i_hardware)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SID=OLDY)
    )
  )';

On 11g database (PRODUCTION) we should create a database link to 10G database, However we will name it as "OLDY". (Note that this OLDY dblink was pointing to 8i database before upgrading to 11g.)


CREATE PUBLIC DATABASE LINK "OLDY"
 CONNECT TO bridge_user
 IDENTIFIED BY <Password>
 USING '(DESCRIPTION=
            (SDU=32768)
                (ADDRESS=(PROTOCOL=tcp)(HOST=10g_hardware_server)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=BRIDGE))
            )';

Then we need to create synonyms on "bridege_user" (on 10g BRIDGE database) for every single table on OLDY database.

example:

> login to BRIDGE 10G database as bridge_user:

run:

SQL> CREATE OR REPLACE SYNONYM table1 FOR hr.table1@OLDY

Now:

login to 11g database and run:

SQL> select * from table1@OLDY;

it works.

It goes firstly to 10g database with user bride_user, then bridge_user selects from its synonym pointing to 8i database.

Yoy have to create synonyms for all table you need to connect from 10g to 8i. 

Hope this helps. We are using this method on PROD system that is 11g.