3 ms·

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

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.

__loam 3y ago
It's a good thing there aren't numerous open source alternative databases that can be hosted by numerous other services then.
- SteveNuts 3y ago
  These companies are also using EBS as their ERP, which you most certainly cannot swap out the underlying database.
- mickeyp 3y ago
  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.
  - __loam 3y ago
    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.
- lazide 3y ago
  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.
  - SoftTalker 3y ago
    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."
    - icedchai 3y ago
      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...)
- travem 3y ago
  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-consumer-business-just-turned-off-its-final-oracle-database/ https://aws.amazon.com/blogs/aws/migration-complete-amazons-...
  - __loam 3y ago
    I'm aware.
walrus01 3y ago
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.
- shrubble 3y ago
  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.
  - icedchai 3y ago
    Oracle even has its own proprietary, alternate JOIN syntax. The devil is always in the details.
    - robocat 3y ago
      Empty strings "" being NULL was a hidden issue I remember causing problems.
  - walrus01 3y ago
    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.
  - Shorel 3y ago
    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.
RobotToaster 3y ago
I'm surprised there aren't entire businesses based around selling compatibility layers or something.
- SR2Z 3y ago
  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.
- sbuttgereit 3y ago
  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/epas_fundamentals/epas_compat_ora_dev_guide/ https://www.enterprisedb.com/docs/epas/latest/fundamentals/e...
- aflukasz 3y ago
  Was just about to write that AWS does that, but no, it's only for MS SQL Server: https://babelfishpg.org/ https://babelfishpg.org/, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide....
Shorel 3y ago
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.
- throwaway44773 3y ago
  Then mysql got bought by oracle
  - Shorel 3y ago
    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.
krylon 3y ago
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.

__loam 3y ago

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

SteveNuts 3y ago
These companies are also using EBS as their ERP, which you most certainly cannot swap out the underlying database.
mickeyp 3y ago
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.
- __loam 3y ago
  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.
lazide 3y ago
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.
- SoftTalker 3y ago
  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."
  - icedchai 3y ago
    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...)
travem 3y ago
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-consumer-business-just-turned-off-its-final-oracle-database/ https://aws.amazon.com/blogs/aws/migration-complete-amazons-...
- __loam 3y ago
  I'm aware.

walrus01 3y ago

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.

shrubble 3y ago
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.
- icedchai 3y ago
  Oracle even has its own proprietary, alternate JOIN syntax. The devil is always in the details.
  - robocat 3y ago
    Empty strings "" being NULL was a hidden issue I remember causing problems.
- walrus01 3y ago
  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.
- Shorel 3y ago
  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.

RobotToaster 3y ago

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

SR2Z 3y ago
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.
sbuttgereit 3y ago
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/epas_fundamentals/epas_compat_ora_dev_guide/ https://www.enterprisedb.com/docs/epas/latest/fundamentals/e...
aflukasz 3y ago
Was just about to write that AWS does that, but no, it's only for MS SQL Server: https://babelfishpg.org/ https://babelfishpg.org/, https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide....

Shorel 3y ago

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.

throwaway44773 3y ago
Then mysql got bought by oracle
- Shorel 3y ago
  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.

krylon 3y ago

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.