A Journey from Oracle to MS SQL Server

The Background

I have an Oracle 11g database. Or at least, it’s not mine – I’m not a DBA – but I have query-only access to view the data.

And I need it in a MS SQL Server database. I’ve got SQL Server 2014 Developer Edition.

There are a bunch of ways to copy tables and/or data from one to the other, but the one that seemed most sensible was Microsoft’s SQL Server Migration Assistant for Oracle (or SSMA, to its friends).

I needed to do some tweaking to connect to my local MS SQL database (from memory, I needed to start the SQL Server Browser service in the SQL Server Services section of the  SQL Server Configuration Manager Utility). But that was (comparatively) simple.

The Problem

The real fun started when I tried to connect to the Oracle database. Which I have regular user access to. I can see all the tables, but I’m not a DBA.

And some very clever person discovered that SSMA needs to read from the SYS.MLOG$ table, which regular users can’t see. Certainly I can’t. The suggestion was to simply

grant select on sys.mlog$ to MyUser

…which would probably work, but I don’t have permission to grant access to anyone.

The Workaround – Part 1

So what to do? The SSMA tool wants to see the SYS.MLOG$ table, and I can’t see it, nor can I give myself permission to see it. One thing I could do, though, was create a different table, with the same fields, and hopefully re-direct SSMA to look at that. Sounds easy, no?

The first step was fairly simple. I created a new table in the Oracle database called MYNEWMLOG (a nine-character title – you’ll see why in a moment). It has to have the same fields as the real SYS.MLOG$ table, which I found on this page but have listed here for posterity:

FLAG - Integer
LOG - String
MASTER - String
MOWNER - String
MTIME - Date
OLDEST - Date
OLDEST_NE - Date
OLDEST_OI - Date
OLDEST_PK - Date
OLDEST_SE - Date
OSCN - Integer
TEMP_LOG - String
TRIG - Integer
YOUNGEST - Date
YSCN - String

So now we have a table that we can access, that is a clone of SYS.MLOG$ (though without any data), that SSMA can look at. All we need to do now is tell SSMA to look at it.

The Workaround – Part 2

OK. So this is the bit that’s a bit … well, dodgy. I searched for the SYS.MLOG$ query that David Baxter Browne mentioned, and eventually found it in Program Files (x86)\Microsoft SQL Server Migration Assistant for Oracle\bin\Microsoft.SSMA.Framework.Oracle.Generic.dll.

Yep. The query is embedded in a DLL.

So of course that’s the end of the road, for anyone who wants to remain on the right side of the license agreement. In theory, you could (I expect) download a hex editor and edit the DLL file, and replace the nine-character Unicode string “SYS.MLOG$” with the nine-character Unicode string “MYNEWMLOG” wherever it appears. In theory. The strings are the same length so it wouldn’t change the length of the DLL, which is probably a good thing.

And provided you did that carefully, you could (I imagine) probably use the SSMA tool to copy the schema from your old Oracle database to your new MS SQL database.

In theory. I expect. But if course I wouldn’t know for sure.

Advertisements
A Journey from Oracle to MS SQL Server

CoderDojo

Something at the back of my mind that I’d like to do some day is start a CoderDojo coding club for kids to learn to code.

CoderDojo WA is in WA (obviously) and might be able to help a bit.

But the main website is coderdojo.com which includes whole heap of info on how to set up and run a dojo.

There’s also a private facebook thread that includes some local folk who might be interested.

CoderDojo

Sending email from PHP in Vagrant via sSMTP and Office365

I have a PHP app that needs to send email, and it recently moved from a real server to a Vagrant box (don’t ask!) which was not configured to send email out to the real word.

After trying a few different options the one that ended up working for me was to use sSMTP to send the email via our Office365 system. This is how I got it to work. Continue reading “Sending email from PHP in Vagrant via sSMTP and Office365”

Sending email from PHP in Vagrant via sSMTP and Office365

Sending Mail from a Vagrant box

Spoilers – I went with sSMTP, and the details of how I did it are here.

Option 1 – use sSMTP instead of Sendmail

Most config is from here but using the settings from Outlook Web Access (Settings cog -> Mail -> Accounts -> POP and IMAP, look in the SMTP setting section). This means having a new bit of software installed and configured (possibly via Vagrant?) This is what eventually worked for me (see sSMTP post here). Continue reading “Sending Mail from a Vagrant box”

Sending Mail from a Vagrant box