Wednesday, 15 October 2014

DBLINK creation in Oracle RDS instnaces(cloud databases)

As the new world of Cloud databases converges we have to adapt to new ways of using the database concepts.
In Oracle RDS instances  each instance is a indigenous machine in itself to setup a communication between various Oracle RDS instances through DBLINK can be done through:--


  1. Logon to Source RDS database "TEST" ( from where data is needed)
  2. Check whether this Table exists in source.
    1. SELECT * FROM PS_JOB
  3. If it exists, check whether any user is assigned to it.
    1. SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME ='PS_JOB';
  4. If not, we need to Create a user using following
    1. CREATE USER Username  IDENTIFIED BY Password;
    2. GRANT CREATE SESSION TO Username;
  5. Give select privilege to Username.
    1. GRANT SELECT ON PS_JOB TO Username

Following are the steps in Target:
  1. Logon to Target RDS database
  2. Create a DBLINK (here we have the change)
    1. CREATE PUBLIC DATABASE LINK ABC CONNECT TO Username IDENTIFIED BY "Password" USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = sss-rds.sfggee44.xxxxx.rds.amazonaws.com)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = TEST)))''
So we mention the complete connection string as can be found in the local tnsnames.ora.

  1. Check whether DB link is created.
    1. SELECT * FROM DBA_DB_LINKS
  2. Try to access data using dblink
    1. SELECT COUNT(*) FROM SYSADM.PS_JOB@ABC

Keep experimenting!!

1 comment:

  1. I suggest not to create PUBLIC Db link . Go for user specific one.

    ReplyDelete