Skip to main content

Dblink From PostgresPlus To Oracle


Hi,

Dblink_Ora_* functions are useful for getting oracle connection into PostgresPlus Advanced Server. i.e, we can retrive oracle data from postgresplus. Please find the steps how to proceed.


Step 1
======
For doing dblink_ora* setup, we need a OCI(Oralce Client Interface which is nothing but libpq in Postgresql)file. If you have oracle 11*, then no need to download this file from any where. if not, we need to donwload this file from oracle site.

Filename Required is : "libclntsh.so" in Linux
      "OCI.DLL" in Windows

In Oracle 11g, you will get this directly from $ORACLE_HOME/lib. In this example, we have created a symlink for this from "/lib64" to "$ORCLE_HOME/lib".

ln -s /home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so /lib64/libclntsh.so
Step 2
======
[root@localhost PGBAR]# chmod -R 766 /home/oracle/

Because, we are accessing this "libclntsh.so" as a enterprisedb user. Hence, we have given the complete permissions to /home/oracle.

Step 3
======
in postgresql.conf from PPAS, please add the below entries.

edb=# show oracle_home ;
 oracle_home 
-------------
 /lib64

Because, we have created a symlink for this "libclntsh.so" at "/lib64".

Step 4
======
Log into as a enterprisedb user, and set the OCI required ENVIRONMENT Variables.

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib64

Step 5
======
Restart the PostgresPlus for taking this effect.

Step 6
======
Make sure your's ORACLE listener works.

edb=# select dblink_ora_connect('oralink','localhost','orcl','scott','tiger',1521,false);
 dblink_ora_connect 
--------------------
 OK
(1 row)

Step 7
======
edb=# select * from dblink_ora_record('oralink','SELECT dbms_metadata.get_ddl(''TABLE'',''DINESH'',''SCOTT'',''9'',''ORACLE'',''DDL'') FROM DUAL') AS T(TABLEDEF TEXT);
                               tabledef                                
-----------------------------------------------------------------------
                                                                      +
   CREATE TABLE "SCOTT"."DINESH"                                      +
    (    "T" NUMBER(*,0)                                              +
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING+
   TABLESPACE "USERS"                                                 +


For more information, please refer the below doc.

/opt/PostgresPlus/9.1AS/doc/README-dblink_ora_setup.txt

--Dinesh

Comments

  1. Thanks for your valuable information.Helped a lot.
    Also,it would be helpful if you include some steps for setting up the same on remote host.

    ReplyDelete

Post a Comment

Popular posts from this blog

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  …

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

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…