Hacker News Reader

Comment by icedchai | Hacker News Reader

Once you're using an Oracle database, you are probably using proprietary Oracle features, SQL syntax, client libraries, etc. The first chance you get might be a while. I once worked on a project porting an app from Oracle to another commercial DB. When Oracle is deeply embedded in your applications, it's easier said than done.

View on HN

I would think that the way to do this would be to set up realtime replication from the origin oracle DB to a db with the same schema running on something much more 'normal' such as mariadb, and then set up client applications and libraries and queries to the newly replicated db in a read only mode. And then setting up test client application/libraries for write mode to a copy of the replicated db and verify everything is also working normally.

After confirming everything is working okay, at some point in time, have a "rip off the band aid" moment of cutting off the oracle db and declaring the replicated one as the new primary.

Far easier said than done. And that you mentioned MySQL/MariaDB as a possible replacement means that you have never worked with Oracle's SQL dialect.

Didn't say it would be easy - and indeed I'm well aware that Oracle is very different than MySQL. It's a painful transition process that more organizations will find necessary as the costs and hassle of oracle increase. If the end state goal is to have zero oracle in production use, people will have to figure out some kind of transition process to migrate away incrementally.

Oracle even has its own proprietary, alternate JOIN syntax. The devil is always in the details.

Empty strings "" being NULL was a hidden issue I remember causing problems.

I have worked with Oracle and MySQL was the replacement.

I rewrote the dozens of PL/SQL SP into the retarded language that MySQL has.

It's not impossible in any way.

MariaDB has been improving Oracle compatibility in recent years, see https://mariadb.com/kb/en/sql_modeoracle/

> I would think that the way to do this would be to

The devil as always Is in the details. The real problem comes With stored procedures. Maybe this is why I shy away from their used to this day. The last migration I did to Postgres wasn’t too bad; the app had only a few basic stored procedures.

Many legacy apps use tons of them. In the late 90's there was a style of application development where all DB access went through stored procedures. I find PL/SQL to be terrible.

PL/SQL is terrible. But organizing your Oracle database in a way where you can do everything with only application-accessible statements is even worse.

Oracle places still do a lot of stored procedures.

There are still tons of PL/SQL developers, and a huge ecosystem that uses these things. Triggers were big at one time too.

You will be surprised how H1B 'specialised talent' developers are just people who have worked on these ancient technologies all life, are near impossible to replace so you are left with no options but to hire them.

Oracle is a whole different specialisation in itself and its tentacles go very deep and afar.

That's why I wrote "naively". I know it's more complicated than I made it sound. It's the same with companies using mainframes, after a while you are so deeply invested (both financially and technically) that migrating might be more expensive than just feeding the corporate machine.

Still, depending on a vendor that treats their customers as rudely as Oracle does must be really annoying.

I'm surprised there aren't entire businesses based around selling compatibility layers or something.

Oracle can and does sue them, and with enough money that it's easier to pay them to go away.

Hell, even benchmarking their DB goes against the license.

EnterpriseDB does exactly this on top of PostgreSQL and has done so for a long time. Early on, their compatibility layer was their USP.

https://www.enterprisedb.com/docs/epas/latest/fundamentals/e...

Was just about to write that AWS does that, but no, it's only for MS SQL Server: https://babelfishpg.org/, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide....

It's not that complicated.

All our business logic was into PL/SQL stored procedures, I migrated them to MySQL stored procedures.

The MySQL language is a bit retarded, and some extra work was needed, but everything was doable.

Then mysql got bought by oracle

It doesn't matter. The important point is we are no longer being tied to Oracle for it.

Now the code runs in both MariaDB and Aurora in AWS.

We only pay AWS for the servers now.

It's a good thing there aren't numerous open source alternative databases that can be hosted by numerous other services then.

These companies are also using EBS as their ERP, which you most certainly cannot swap out the underlying database.

This is a great way to tell everyone you've never had to do this type of migration.

Oracle's got a lot of nasty things going for it, for sure. But there's a reason people still use it: part of it is vendor lock-in, of course; the other is that the database is actually really nice.

Yeah I mean, I get it. Amazon spent years getting off of oracle. I'm just saying the only reason to be on oracle now is because migration is expensive. It says a lot that it's not a competitive product for new applications, and is in fact a liability.

Oh man, in theory SQL is standard - in practice, you’ll need to rewrite (or do major surgery) on nearly everything that interacts with your Oracle instance. Unless you’ve been incredibly careful, which is unlikely.

And chances are, you started using Oracle because of some enterprise software somewhere for which you don’t even have the source - so good luck.

And don’t forget, Oracle will encourage you to use all their Oracle specific stuff everywhere in their documentation. It’s a giant, idiosyncratic mess.

If you were using SQL so generically that you could easily substitute another database, you never should have been using Oracle to begin with.

I guess you could call it "vendor lock-in" to use the more advanced SQL, analytic functions, and stored procedures or you could call it "getting what you paid for."

It's similar to the cloud lock-in you see today. (If all you're using is VMs, you're probably better off with something else...)

Amazon provide multiple database offerings in their cloud, it was still a multiple year effort to move off Oracle:

  Migration Complete – Amazon’s Consumer Business Just Turned off its Final Oracle Database

  Today I would like to tell you about an internal database migration effort of this type that just wrapped up after several years of work.

https://aws.amazon.com/blogs/aws/migration-complete-amazons-...

I'm aware.

Reply on news.ycombinator.com