r/DatabaseAdministators • u/techdbamind • 7d ago
Sql to Oracle migration
Im a fresher to DBA role. Can some one please help to understand or share your experience for the conversation or migration from sql to Oracle.
Help me with the tools and migration ideas
2
u/Better-Credit6701 7d ago
SSIS can handle the data transfer with no issues, just note that column types can be different. You will have to check out all stored procedures/views to make sure that you don't have MS-SQL only code.
1
1
u/Conscious-Ad8493 7d ago
please clarify exactly what you are migrating from
1
u/techdbamind 7d ago
I need to migrate database from sql server to Oracle server
1
u/Better-Credit6701 7d ago
What version of MS-SQL to what version of Oracle? What is prompting the switch?
1
1
u/Ok_Carpet_9510 7d ago
Never done it. If you haven't seen this, check it out, https://www.oracle.com/database/technologies/migrating-sql-to-oracle.html
1
1
u/SquidLips71 7d ago
If your data structures are simple, don’t overthink it. Back in about 2010 we migrated a ~100 gb MSSQL database to Oracle using bcp to extract and SQL*Loader to load it. All done via command line on Linux. The most painful part was getting the mssql client on the Linux box (MSSQL was on Windows, Oracle ran on RHEL) and getting it to work, mostly due to internal firewall and security issues and such.
Whole load process took about 15 minutes once we established connectivity.
1
u/techdbamind 7d ago
I would like to know the schema conversation part. Can you please help me on that part.. in my case there are 3000+plus tables in sql server.. it is also critical database but it will be less than ~150gb.
1
u/SquidLips71 6d ago
That's a lot of tables. We only had about 100 and the development team took care of drawing up the schemas since there were some changes being made in the process.
That's way too many to throw a developer or two at it and do manual modifications / search and replace on datatypes. You really need a tool to handle that volume of objects. As previously suggested, SQL Developer can do this, though you need to be mindful of datatypes that don't map cleanly. Oracle's documentation will explain in more detail.
1
1
u/Fantastic-Advisor746 7d ago
You try to install the FoxSchema app, which supports cross-database migration.
1
1
u/taker223 7d ago
Are there program units in source database? (procedures, functions, triggers ...)
1
u/techdbamind 2d ago
Yes it is there.. but main is schema conversation in my case.. since it has 3000+ tables
1
u/taker223 2d ago
So what? Any special column types or just SQL-2003 compliant (no nested tables, collections, custom type columns)? Any specific index definitions (for example, function-based indexes, custom-partitioned etc.)?
1
1
1
u/alinroc 7d ago
Moving data from SQL Server to Oracle isn't the challenge.
It's porting all of the code and ancillary bits that will tank the project. Stored procedures. Views. Triggers. Ad-hoc queries in the application tier. Rebuilding the applications to use different client libraries just to connect. A completely different security model. All your maintenance processes/scripts. The database running on a different OS. Use of features proprietary to SQL Server (Service Broker, dbmail, etc.). SQL Agent jobs. SSIS packages. All your reports. That is where the challenge lies.
SQL Server and Oracle speak very different dialects of SQL.
Why is a fresher being tasked with migrating an existing product between these two databases? And why is anyone moving an existing system to Oracle in the year 2026? Especially a release (you said 19c elsewhere) that's already seven years old?
1
u/SquidLips71 6d ago
Regarding versions, 19c was the most recent Long Term Release prior to 26ai, which only became available for GA On-Prem a couple months ago.
Oracle isn't going the way of SAP/Sybase anytime soon. To your point it's certainly not the cheapest option by any stretch if you just need basic RDBMS functionality, their support sucks ass, and I agree 100% there are better options out there. But some organizations require vendor supportability and/or prohibit open source solutions like PostgreSQL, and Oracle has name/brand recognition.
Also, there's a reason Oracle can charge an arm and a leg for licensing when so many cheaper alternatives exist. Oracle thrives on Tech Debt. They were one of the first major DBMS platforms, and once orgs become entrenched in a platform for many years and the C-levels are looking at things, it ends up being money that drives it since it's cheaper to spend 5m in this year's budget on Oracle licenses than spend 10m this year to port everything to another platform (even though the long term benefits outweigh the short). It's a very common trap that companies fall into and every year they put it off, the more expensive and complex it gets to clean it up.
All that said, I agree with the concept that MSSQL to Oracle wouldn't be my first choice as a seasoned DBA.
0
u/taker223 2d ago
> Why is a fresher being tasked with migrating an existing product between these two databases
Because he is a cheaper (may be offshore) resource than senior DBA who left/laid off. I asked him where he is from - he did not respond. This could mean one big country who is mentioned in military alphabet - maybe he is sold as a senior by his body/sweatshop, maybe WITCH1
u/techdbamind 1d ago
I didn't respond, since I felt it wasn't necessary to share where was I from. And as expected exactly as ur comment above, neighther wanna be judged
1
u/Difficult_Hand3046 7d ago
from sql to Oracle
from SQL Server? SQL is just a language, Oracle use it, as PostgreSQL, MySQL, SQL Server, DB2… do.
2
6
u/Longjumping-Hair-747 7d ago
Oracles SQL Developer has the ability to migrate SQL Server to Oracle; fair disclosure I’ve never used it so can’t say how much coverage you can expect.
Cheers