Wikipedia

Search results

Oracle's biggest database foe: Could it be Postgres?



Deutsche Bank analyst Karl Keirstead wants to soothe Oracle that it needn't fear the open-source reaper, arguing that open-source databases "don't represent a near-term threat" to Oracle's database lead.
Though Keirstead is right that NoSQL databases like MongoDB and DataStax's Cassandra may merely nibble at Oracle's lead, primarily finding purchase in new applications, old-school Postgres may be another matter.
That is, if a new Postgres survey conducted by EnterpriseDB is to be believed. While it has been evident for years that Postgres is undergoing a serious renaissance, it's harder to believe that it's chewing into core Oracle deployments. That said, if any database could threaten to replace Oracle in applications being refreshed by enterprises tired of paying Oracle's eternal maintenance fees, it's Postgres.

Hegemony isn't what it used to be

Once upon a time, Oracle could dismiss open-source databases as test and development toys that couldn't compete when it came to mission-critical production. Unfortunately, reality hasn't been kind to that supposition.
Gartner, for example, forecasts that more than 70% of new in-house applications will be developed on an open-source database by 2018, and that 50% of existing commercial RDBMS instances will have been converted to open-source databases or will be in process.
In other words, open-source databases are almost certainly cutting off Oracle's oxygen when it comes to new applications, but it may also be cutting into its hegemony within existing workloads.
If true, that's new.
Though from a biased source, an EnterpriseDB survey of Postgres users certainly suggests that Postgres users are running the venerable open-source database for increasingly mission-critical workloads, including those that used to pay the Oracle tax:
  • 55% of users—up from 40% two years ago—are deploying Postgres for mission-critical applications
  • 77% of users are dedicating all new application deployments to PostgreSQL, and 37% reported they had migrated existing applications from Oracle or Microsoft SQL Server to Postgres
  • 37% of PostgreSQL users expect to gradually replace their legacy systems with Postgres, compared to 29% in 2013
  • End users predict their deployments of Postgres will expand significantly, with 32% saying they anticipate production deployments of Postgres to increase by at least 50% over the next year.
That's a lot of new Postgres deployments. Nor is it particularly surprising, given that 41% of those surveyed report they had first-year cost savings of 50% or more. While big data applications are naturally going to gravitate to MongoDB, Cassandra, and other NoSQL databases, Postgres is carving out its place as a like-for-like replacement of Oracle, DB2, and other traditional RDBMSes.
Or is it? In many ways, these Postgres numbers seem too good to be true. For example, some longtime Oracle users have turned to Postgres as a bargaining chip in negotiations with Oracle, without serious intention to switch, as was the case with Salesforce.com.

This will only hurt a lot

And yet... something is clearly going on.
In its latest earnings miss, Oracle blamed currency valuations for its poor performance. But this is just one miss among many. It's actually becoming difficult to remember the last time Oracle nailed earnings estimates for two consecutive quarters. Over the last few years, it has missed more than it has hit earnings.
Over the years, Oracle has blamed sales execution, currency fluctuations, and other factors.
What it hasn't blamed, but Bloomberg has, are open-source databases: "The impact [of open-source databases] shows up in Oracle's sales of new software licenses, which have declined for seven straight quarters compared with the period a year earlier."
Bloomberg points to startups that have almost entirely forsaken Oracle as a source of Oracle's weakening new license sales, but startups have never driven the heft of Oracle's earnings. It's much more telling, as the article points out, that giants like Goldman Sachs are eschewing Oracle for open-source databases. As its co-head of technology told Businessweek, "It's hard not to go into our datacenter and see a tremendous amount of open source running our applications and middleware."
There are good reasons to believe an enterprise's next database will be open source—and not just any open-source database, it would seem, based on the Postgres survey data. With Postgres, enterprises don't need to learn new query languages or alternative ways to structure data. They don't sacrifice functionality and can get equal or better performance as Oracle.
Oh, and the price? Nada. Zilch. Zero.
Jobs data suggests Postgres is hot with the startup crowd. But with excellent performance and Oracle-esque functionality, it's not surprising it's heating up with enterprise customers, too. And while the EnterpriseDB survey may overstate just how fast this is happening, it's clear that Postgres is making a dent on Oracle's ability to dominate database purchasing decisions in the way it once did.

Migrating from oracle to postgresql with ora2pg

PostgreSQL is one of the most used Rdbms.

Here we gonna talk about migrating from oracle to postgresql using ora2pg. 
After installing ora2pg tool, we will see how to configure and to run it to migrate our data. We are using an oracle 12.1 database and a postgresql 9.6.2. The server is runing on OEL 7.2
The oracle database and the postgresql server are running on the same server. But different servers can be used.
To install ora2pg we need following:
-DBD-Oracle-1.75_2.tar.gz : Oracle database driver for the DBI module
-DBD-Pg-3.6.0.tar.gz : PostgreSQL database driver for the DBI module
-DBI-1.636.tar.gz : Database independent interface for Perl
-ora2pg-18.1.tar.gz : ora2pg archive
The DBI modules can be found here
And the ora2pg can be downloaded from here
Install DBI module
[root@serveroracle postgres]#tar xvzf DBI-1.636.tar.gz [root@serveroracle postgres]#cd DBI-1.636 [root@serveroracle postgres]#perl Makefile.Pl [root@serveroracle postgres]#make [root@serveroracle postgres]#make install
Install DBD-Oracle
[root@serveroracle postgres]# tar xvzf DBD-Oracle-1.75_2.tar.gz [root@serveroracle postgres]# cd DBD-Oracle-1.75_2/ [root@serveroracle DBD-Oracle-1.75_2]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 [root@serveroracle DBD-Oracle-1.75_2]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib [root@serveroracle DBD-Oracle-1.75_2]# perl Makefile.PL [root@serveroracle DBD-Oracle-1.75_2]# make [root@serveroracle DBD-Oracle-1.75_2]# make install
Install DBD-Pg
[root@serveroracle postgres]# tar xvzf DBD-Pg-3.6.0.tar.gz [root@serveroracle postgres]# cd DBD-Pg-3.6.0 [root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL Configuring DBD::Pg 3.6.0 Path to pg_config? /u01/app/postgres/product/96/db_2/bin/pg_config [root@serveroracle DBD-Pg-3.6.0]# make [root@serveroracle DBD-Pg-3.6.0]# make install
When we first run the command perl MakeFile.PL, we got following errors
[root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL Configuring DBD::Pg 3.6.0 PostgreSQL version: 90602 (default port: 5432) POSTGRES_HOME: /u01/app/postgres/product/96/db_2 POSTGRES_INCLUDE: /u01/app/postgres/product/96/db_2/include POSTGRES_LIB: /u01/app/postgres/product/96/db_2/lib OS: linux Warning: prerequisite version 0 not found. Could not eval ' package ExtUtils::MakeMaker::_version; no strict; BEGIN { eval { # Ensure any version() routine which might have leaked # into this package has been deleted. Interferes with # version->import() undef *version; require version; "version"->import; } } local $VERSION; $VERSION=undef; do { use version; our $VERSION = qv('3.6.0'); }; $VERSION; ' in Pg.pm: Can't locate version.pm in @INC (@INC contains: t/lib /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 11) line 16, line 19. BEGIN failed--compilation aborted at (eval 11) line 16, line 19. WARNING: Setting VERSION via file 'Pg.pm' failed at /usr/share/perl5/vendor_perl/ExtUtils/MakeMaker.pm line 619. Using DBI 1.636 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/ Writing Makefile for DBD::Pg
We correct errors by running the command below.
[root@serveroracle DBD-Pg-3.6.0]# yum -y install 'perl(version)' Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package perl-version.x86_64 3:0.99.07-2.el7 will be installed --> Finished Dependency Resolution
Install ora2pg
[postgres@serveroracle ~]$ tar xvzf ora2pg-18.1.tar.gz [root@serveroracle postgres]# cd ora2pg-18.1/ [root@serveroracle postgres]# perl Makefile.PL [root@serveroracle postgres]# make [root@serveroracle postgres]# make install
[root@serveroracle postgres]# ora2pg -version Ora2Pg v18.1 [root@serveroracle postgres]#
Now that ora2pg is installed, we can proceed with the migration. The first step is to configure the ora2pg.conf file. We can do a copy of the default template and then modify the file. In our case the configuration file is located in /etc/ora2pg directory.
[root@serveroracle ora2pg]# cp ora2pg.conf.dist ora2pg.conf [root@serveroracle ora2pg]# vi ora2pg.conf
In our configuration file, following changes where done. We are exporting only the HR schema
ORACLE_DSN dbi:Oracle:host=serveroracle.localdomain;sid=ORCL ORACLE_USER system ORACLE_PWD root SCHEMA HR TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION OUTPUT HR_output.sql
ora2pg works by exporting and importing schemas. We can only export one schema at a time if we use the option SCHEMA. If we want to export all schemas we can just comment the option SCHEMA. In this case all non-oracle users will be extracted.
In the documentation we also have the option SYSUSERS
# Allow to add a comma separated list of system user to exclude from # from Oracle extraction. Oracle have many of them following the modules # installed. By default it will suppress all object owned by the following # system users: # CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,OLAPSYS,ORDDATA,OWBSYS, # ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,WK_TEST, # WKSYS,WKPROXY,WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000, # FLOWS_040100,FLOWS_FILES,MDDATA,ORACLE_OCM,SPATIAL_CSW_ADMIN_USR, # SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,SQLTXPLAIN,DMSYS,TSMSYS,WKSYS, # APEX_040200,DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS # Other list of users set to this directive will be added to this list. #SYSUSERS OE,HR
Once configuration done, we can run the orap2g command. Note that you can see all options by running ora2pg -help
[root@serveroracle ora2pg]# ora2pg [========================>] 7/7 tables (100.0%) end of scanning. [> ] 0/7 tables (0.0%) end of scanning. [========================>] 7/7 tables (100.0%) end of table export. [========================>] 0/0 packages (100.0%) end of output. [========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec) [==> ] 25/215 total rows (11.6%) - (0 sec., avg: 25 recs/sec). [========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec) [=====> ] 52/215 total rows (24.2%) - (1 sec., avg: 52 recs/sec). [========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec) [=================> ] 159/215 total rows (74.0%) - (1 sec., avg: 159 recs/sec). [========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec) [===================> ] 178/215 total rows (82.8%) - (1 sec., avg: 178 recs/sec). [========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec) [====================> ] 188/215 total rows (87.4%) - (2 sec., avg: 94 recs/sec). [========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec) [=======================> ] 211/215 total rows (98.1%) - (2 sec., avg: 105 recs/sec). [========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec) [========================>] 215/215 total rows (100.0%) - (3 sec., avg: 71 recs/sec). [========================>] 215/215 rows (100.0%) on total estimated data (3 sec., avg: 71 recs/sec) [========================>] 1/1 views (100.0%) end of output. [========================>] 3/3 sequences (100.0%) end of output. [========================>] 1/1 triggers (100.0%) end of output. [========================>] 0/0 functions (100.0%) end of output. [========================>] 2/2 procedures (100.0%) end of output. [========================>] 0/0 types (100.0%) end of output. [========================>] 0/0 partitions (100.0%) end of output. [root@serveroracle ora2pg]#
Once finished, a file HR_output.sql is generated. This file can be used to load data in the postgresql database. We can also load data directly without using a file. We just have to specify the connection info for the postgresql.
Let’s know prepare the postgresql server to receive our data. First we create the user HR.
[postgres@serveroracle ~]$ psql psql (9.6.2 dbi services build) Type "help" for help. postgres=# \c orclpg You are now connected to database "orclpg" as user "postgres". orclpg=# create user HR WITH PASSWORD 'root';
And then we can execute the file. The first time we ran the file, we had some constraints errors
orclpg=# \i HR_output.sql ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk" DETAIL: Key (region_id)=(2) is not present in table "regions". STATEMENT: COPY countries (country_id,country_name,region_id) FROM STDIN; psql:HR_output.sql:224: ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk" DETAIL: Key (region_id)=(2) is not present in table "regions". orclpg=#
To correct this, we put the option in the configuration file DROP_FKEY to 1
DROP_FKEY 1
With this option all foreign keys will be dropped before all data import and recreate them at the end of the import. After the load was successful.
orclpg=# \i HR_output.sql SET CREATE SCHEMA ALTER SCHEMA SET CREATE TABLE COMMENT SET SET SET SET SET BEGIN ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE SET COPY 107 SET COPY 19 SET COPY 10 SET COPY 23 SET COPY 4 ALTER TABLE ALTER TABLE ALTER TABLE … … COMMIT
We can verify that tables were created and that data were inserted.
orclpg=# \d List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- hr | countries | table | postgres hr | departments | table | postgres hr | employees | table | postgres hr | job_history | table | postgres hr | jobs | table | postgres hr | locations | table | postgres hr | regions | table | postgres (7 rows)

orclpg=# select count(*) from countries; count ------- 25 (1 row) orclpg=#
Conclusion
As we can see ora2pg is a free easy tool to migrate data from oracle to postgresql. In coming blog we will talk about other tools that can be used to move data from oracle to postgresql
You can find more information in the official web site