Skip to main content

Heterogeneous DB Connection Between Oracle And Postgres Plus

Hi ,

Finally,i have configured the heterogeneous (HS) Database Connectivity between Oracle 11.2 and PPAS 9.1. I believe, the same steps will be suitable for the PostgreSQL as well. Please find the steps one by one .

Step 1:-
--------
We need to Configure the odbcinst.ini file which is having all the Drivers Information.
[enterprisedb]
Description=PostgresPlus Advanced Server ODBC driver
Driver=/opt/PostgresPlus/9.1AS/connectors/odbc/lib/edb-odbc.so
Setup=/opt/PostgresPlus/9.1AS/connectors/odbc/lib/libodbcedbS.so
UsageCount=1

Step 2:-
--------
We need to Configure the odbc.ini file which is having all the DNS entries.

[edb]
Driver=enterprisedb
Description=Connection to LDAP/POSTGRESQL
Servername=localhost   
Port=5444
Protocol=7.4
FetchBufferSize=99
Username=enterprisedb
Password=adminedb
Database=edb
ReadOnly=no
Debug=1
Trace = yes
CommLog=1
UseDeclareFetch=0
TraceFile=/tmp/sql.log
UseServerSidePrepare=1
dbms_name=PostgreSQL

Step 3:-
---------
Check the DNS Connectivity
[root@CentOS62STM64bit ~]# isql -v edb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If you are getting any error like "/opt/PostgresPlus/9.1AS/connectors/odbc/lib/edb-odbc.so", then do like "ldd *.so" file.
 
It will show some of the "*.so" files not found. Then fix the issue by downloading those binaries or soft linking the higer version to lower version files.
 
Step 4:-
--------
Create "initedb.ora" file in $ORACLE_HOME/hs/admin Location with the below contents.

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = edb
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Make Sure, your init<sid>.ora file must like init<DNS Name in odbc.ini>.ora. Avoid the Uppercase while giving the DNS names.

Step 5:-
--------
Configure the $ORACLE_HOME/network/admin/listener.ora file with the below contents.

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = edb)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (PROGRAM = dg4odbc)
    )
   )
ADR_BASE_LISTENER = /home/oracle/app/oracle


Step 6:-
--------
Restart the Listener Service.

->lsnrctl stop all
->lsnrctl start
->lsnrctl status

Service "edb" has 1 instance(s).
  Instance "edb", status UNKNOWN, has 1 handler(s) for this service...

Step 7:-
--------
Configure the $ORACLE_HOME/network/admin/tnsnames.ora as below.

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
edb =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))
         (CONNECT_DATA =(SID = edb)
         )
    (HS=OK)
        )
Step 8:-
--------
Testing the "tnsping" to given new HS's SID

-> tnsping edb

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SID = edb)) (HS=OK))
OK (0 msec)

Step 9:-
--------

Created a dblink as below.
-> create public database link toppas connect to "enterprisedb" identified by "adminedb" using 'edb';

Step 10:-
---------
Tested the HS connectivit as below.
SQL> select * from "pg_database"@topg;
datname                                                              datdba
---------------------------------------------------------------- ----------
  encoding datcollate
---------- ----------------------------------------------------------------
datctype                                                         datis datal
---------------------------------------------------------------- ----- -----
datconnlimit datlastsysoid datfrozenxid dattablespace
------------ ------------- ------------ -------------
datacl
--------------------------------------------------------------------------------
template1                                                                10
         6 en_US.UTF-8
en_US.UTF-8                                                      1     1
 
--Dinesh

Comments

Post a Comment

Popular posts from this blog

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…