There's advantages to mastering Microsoft and advantages to diversifying your skill set. The great thing is that data is hot so, as long as your skills are growing, your career is advancing either way.
I have been offered a very exciting Junior BI role for a large insurance company...
What kind of insurance company? I don't know what industry you are in now but you want to take that into consideration too. I've worked for a few types of insurance industries and they're very different. If it's health insurance, that's an excellent industry to be into but has a distinct culture that some people like but I do not care for. Other insurance industries have a better culture but don't have the same level of demand as Healthcare.
opportunity to work with large amounts of data that I don't do at the moment, mainly Oracle, Informatica ETL.
I'm a Microsoft SQL Server/BI stack guy and enjoy that more than Oracle but my advise is to go where the data is. It's more challenging but much more fun to deal with large amounts of data.
I just wanted to ask for any advice or experience on the reality on moving from one RDBMS to another and if people have done that, how did they get on or do people feel they have missed on a position because they are more towards the other.
There are many valuable SQL skills that will help you in any RDBMS... Learning how to use to a Tally table to accomplish SQL and ETL tasks without a SQL loop, recursive CTE or other RBAR, analytic/window functions (Row Number and Ranking functions, OVER & partition by clause, indexing, etc... these skills help you in most RDBMS systems. What's great about Itzik Ben-Gan books is that he talks about the origins of different T-SQL functions which helps me with other systems.
Oracle or SQL Server?... I'll take a SQL Server guru or Oracle guru over someone who's okay with both any day. There's a lot to be said for people who have solid skills in multiple RDBMSs too though.
The major RDBMS systems are very similar and easy to pickup if you understand one very well but there's some major differences. I work with Oracle from time to time and learned it quickly but there's always some random task that's simple in MS SQL Server that gives me fits in Oracle. Keep in mind, to, that a lot of what you need to know for each RDBMS has to do with the OS. If I run out of disk space on a SQL Drive I know what to do and/or who to go to. I would not know where to start if that happened on an Oracle system I'm working with.
That's my 2.5 cents.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001