Oracular Installatium!

hpglassesIn ancient history, before I finally managed to install Red Hat 6.1 or SuSE 6.2 and long before the new Millenium had dawned, I dabbled with various numbered versions of Mandrake Linux.

It was produced in France and (originally) based on Red Hat, but with a thick veneer of usability and user-friendliness. Unlike Red Hat itself, for example, it installed easily; specifically, it detected hardware like no other distro at the time could. It also looked pretty decent, as far KDE desktops of the period ever looked decent.

The French company ended up merging with Brazil’s Connectiva Linux and became “mandriva”, a name I always hated. They then went to the brink of bankruptcy several times before releasing their last version of Mandriva in 2011. The company was finally wound up last year.

Happily, Mandriva was forked before it breathed its last: Mageia is the heir to the Mandriva legacy. The name means ‘magic’ in Latin.

Having nothing much else to do on an early Sunday evening and in a deep fit of hopeful nostalgia, I gave the latest release of Mageia (version 5.1) a spin. Sadly, I wasn’t too keen on the results: I chose their KDE desktop and that turned out to be old-school KDE 4.something, meaning a pretty clunky experience complete with weird blue glows around active windows!

So then I tried its Gnome flavour: it’s pretty vanilla Gnome 3 as far as I can tell, which means it’s practically unusable.

In desperation, I tried LXDE. It’s supposed to be a very lightweight desktop environment and it’s not one I’ve ever really bothered with as a result… but I must say, it has all the bells and whistles needed to make an attractive desktop experience and I ended up thinking it suited Mageia much better than any other DE I had tried. So that would be my recommendation, I think, if you wanted to give the distro a whirl.

Whichever desktop environment you choose, the software selection is adequate, but a bit dated: Firefox is back at version 45.5 out-of-the-box (the current version is 50.0); LibreOffice is at 4.4.7.2 (current version is 5.2.3) and so on.

In short: it’s not exactly a bad distro, but it’s another of those ‘I wonder why they bother?’ ones.

For old time’s sake, I wondered if Mageia ran Oracle 12c without drama, and it does (no matter what desktop environment you choose): and I therefore decided to throw another of my ‘preinstaller scripts’ together to make repeated installs of 64-bit 12c Enterprise Edition painless on it. (And yes, it’s only 12c and only 64-bit).

Scripts need names, of course. I’ve called this one Potter, since if mageia is Latin for magic, then we’re in need of the name of someone famous in the world of magic who dabbles in a bit of Latin-esque spellery when occasion demands.

As usual, there’s an article containing screenshotted walk-throughs of how to install Mageia in the first place; how then to run the Potter script; and finally how to perform a subsequent Oracle 12c install.

Mmmm… Manjaro

logoI mentioned at the start of the year how much I had enjoyed having a play around with Manjaro Linux -which is essentially ‘Arch Linux done right’.

It has taken me a while (and several different distros en route) but now it is the end of the year, and I’ve given up my daytime job for a few weeks, I finally have some time on my hands… and the inclination to wipe every machine I’ve been using lately!

So, Manjaro is my new desktop distro of choice and I rather recommend it! Accordingly, I’ve put together a new article explaining how I go about ‘polishing’ a fresh Manjaro install so that it does what I need it to do in the way I like it to do it.

If you’ve ever had a desire for the lean, mean and minimalist Arch approach to running Linux, I think you’ll find Manjaro highly desirable!

Try it, Sam. Try Fedora.

bluefedora02Fedora 25, the latest release of the Fedora project, was released on 22nd November. Unfortunately, I was busy celebrating Benjamin Britten’s birthday and thus missed the whole thing.

I’d already tested my Bogart script (to help install Oracle 12c on Fedora) with the first beta of the release, so I wasn’t expecting any problems with the eventual production release -and there aren’t any. The thing works as advertised.

Fedora remains one of my ‘bargepole’ distros (as in, “I wouldn’t touch it with a…”), but I know a lot of people that like it for some reason or other, so Bogart is for them!

My Dear Watson…

I was idly browsing through Distrowatch today and noticed that at Number 5 on their list of popular distros for the past six months was one called Elementary OS.

It’s not one I’ve ever heard of before, but it’s Ubuntu-derived, and I got to wondering (a) what it was like and (b) would it run Oracle?

In regards to question (a)… it’s a fairly weird desktop and I don’t particularly like it. The default browser is Epiphany, which is about as non-mainstream as you can get and sets the tone for much else. Fancy changing the colour theme of the default terminal? Well, you can’t (at least, there’s no obvious place to do it that I could find). Fancy writing a letter? Or doing a quick bit of spreadsheeting? Tough, then… because there’s no default Office suite installed (you can add one, obviously. But the point remains that the distro appears to be lacking much of what you’d expect to be included in a modern Linux distro). Windows are closed by clicking on the left-hand side of their top bar, where most “normal” folk are used to clicking in the top right-hand corner. You can’t minimise windows, either: there’s one button that acts as a generic ‘resizer’. Click once to maximise and click twice to resize back to its previous non-maximised size. If you want actual minimization, you have to right-click the app’s title bar and select that option from the context menu… which doesn’t seem terribly sensible to me.

It’s mostly all remediable, I suppose, but you’re in for a lot of configuration and downloading …and I can’t frankly see why you’d go to the effort.

On the other hand, I found the distro very fast (to start, to reboot, to open apps, to close them). It seemed extremely responsive… but I still don’t think that makes the lack of normality a sensible trade-off, personally.

So it’s not a distro I’m going to be doing much with, albeit after a fairly cursory look at it. (Read this article for lots more screenshots and what seems to me a reasonable overall assessment). But I can see that someone wanting a fast, straightforward distro that doesn’t invite a lot of tinkering, would find a lot in it that pleases. (I can imagine parents finding its interface useful for young kids, for example).

I guess that means I needn’t worry too much about whether Oracle runs on it or not, then: there aren’t going to be too many young kids clamouring for it, after all!

But it does. Its Ubuntu heritage means you can download my Mandela script, and make one alteration to it: change line 122 to read:

if [ "$VERCHECK" != "Description: elementary OS 0.4 Loki" ]; then

(i.e., alter the text that the command lsb_release -d is tested to return). Once that’s done, run the script and reboot …and then proceed as if the distro were actually Ubuntu (as described in this article) and you’ll have 12c Enterprise Edition running on it in no time at all:

virtualbox_holmes_28_11_2016_17_41_10

Anyway: for a bit of fun on a wet Wednesday (or Monday!) afternoon, it’s worth a dabble, but I’m frankly surprised the distro rates as highly as it does on Distrowatch and I wouldn’t be losing sleep over it if you’ve never tried it. 🙂

New openSUSE

opensuse-64-bit-2016-11-19-16-03-09I was a bit busy this week, what with having my second cataract removed and all… so I missed the release of a new version of openSUSE on 16th November.

However, I’ve caught up now and as the thumbnail to the left attests, my quatro script works on the new release just fine to facilitate easy Oracle Enterprise Edition installations.

See the Quatro main page for screen-shotted walk-through details of how to perform the installation.

SQL Server Replication to Oracle 12c

No version of SQL Server supports replicating to Oracle 12c out of the box.

This took us a little by surprise at work, since things had been replicating just fine in our equally-12c test environment. The difference in Production that arose this week, however, was that somebody decided to re-initialise the SQL Server publish-and-subscribe replication in that environment. This caused SQL Server to drop the replication-controlling table from Oracle and to re-create it from scratch -and only at that point does the lack of 12c support reveal itself.

The symptoms are easy to describe: if you check your replication monitor in the SQL Server Management Studio, you’ll see this error (or variations on it):

sqlserverrepl01

The cause of this is also quite easily found. In the SQL Server msdb database (under Programmability > Stored Procedures > System Stored Procedures, if you’re browsing in the Management Studio), you’ll find a procedure called sys.sp_MSrepl_createdatatypemappings. It contains this code snippet:

IF OBJECT_ID(N'dbo.MSdbms_datatype', 'U') IS NULL
 BEGIN
 print 'Creating table MSdbms_datatype'

create table MSdbms_datatype
 (
 datatype_id int NOT NULL IDENTITY,
 dbms_id int NOT NULL,
 type sysname NOT NULL,
 createparams int NOT NULL DEFAULT 0,
 CONSTRAINT pk_MSdbms_datatype PRIMARY KEY (datatype_id),
 CONSTRAINT fk_MSdbms_datatype_dbms_id FOREIGN KEY (dbms_id) REFERENCES MSdbms (dbms_id)
 )
 
 exec dbo.sp_MS_marksystemobject 'MSdbms_datatype'

--
 -- Define default dbms data types
 --
 exec sys.sp_MSrepl_MSSQLdatatypes 'MSSQLServer'
 exec sys.sp_MSrepl_DB2datatypes 'DB2'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '8'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '9'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '10'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '11'
 exec sys.sp_MSrepl_SASdatatypes 'SYBASE'
 END

You’ll note that it’s creating a table to govern the ‘data type mappings’ between SQL Server and various other databases -in other words, and as an example, where SQL Server says ‘INT’ or ‘BIGINT’, Oracle will say ‘NUMBER’. SQL Server needs to be able to translate its data types to those used by ‘foreign’ databases, and this code sets that translation mechanism up.

Unfortuately, you’ll also note that whilst the code goes on to populate this data mapping table with translation entries for Oracle versions 8, 9, 10 and 11… there’s nothing at all for Oracle 12! It’s this lack of a description as to which SQL Server data types should become which Oracle 12c ones that explains replication’s inability to create its MSREPL7 table correctly.

If, despite receiving that earlier error when first attempting to create a new subscription to an Oracle database, you describe the MSREPL7 table that will actually have been created in Oracle, you’ll see this sort of thing:

sqlserverrepl02

Notice all those “INTERVAL DAY(2) TO SECOND(6)” columns? They’re actually supposed to be NUMBER(10) and NUMBER(3) columns. Those BINARY FILE LOB columns are all supposed to be RAW(16), too.

MSREPL7 is the ‘governor’ for SQL Server replication: it records what tables are supposed to be replicating and what transaction they’re up to in the destination (Oracle) database. When the SQL Server source database next wants to send transactions to Oracle, it’s this table which tells it which transactions have already been sent and which have not. So when this ‘governor’ table is screwed up, you won’t get very far with any replication!

As an aside, things are slightly more complicated if you upgrade to 12c from a previously-working 11g. In that case, you’d already have had replication working nicely, even after completing the 12c upgrade process, and thus the MSREPL7 table would have all the right data types and there’d be no impediment to replication continuing to work. The other tables you’re replicating will also already exist in your newly-upgraded 12c database and they’ll have all the right columns too. So they will replicate nicely as well. So, you will think that all is well… because it is!

It’s only when someone decides to reinitialize  replication that it will all go pear-shaped, because it’s only at that point that SQL Server will drop the tables being replicated and attempt to re-create them… with all the wrong data types! When you then attempt to push a number from SQL Server into a column in Oracle that’s been freshly-created as an INTERVAL data type, that’s when sparks will fly.

This is why we didn’t realise that upgrading to 12c would be an issue in our various test environments: no-one had attempted to re-initialize the replication; and with many multi-hundred-million rows to be pushed across into Oracle, it was a fair bet that no-one would be daft enough to do that in Production either! Unfortunately, it turned out that my gambling instincts were wrong on this occasion: someone was daft enough to hit the wrong menu options, and thus the trouble began.

I couldn’t find much about this problem on Google, which was rather surprising (if one major database has trouble replicating to another major database, I would have expected to hear a lot about it by now!)

I did find enough to make me look at the column types in MSREPL7, and notice that all sorts of odd data types were in use. That got me hunting down any references to how SQL Server knows what data type maps to what in Oracle. And finally, that got me to the stored procedure the code from which appears above.

Looking at that code, it’s just begging for an extra line to be added to it, no?! I mean, if it read:

exec sys.sp_MSrepl_ORAdatatypes 'Oracle'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '8'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '9'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '10'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '11'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

…then maybe all the troubles would go away, right?

But you can’t just run that sort of thing in the standard SQL Server Management Studio. For this, you need to run it in DAC (or ‘dedicated administrative connection’) mode. To do that, fire up SSMS as normal and connect to your SQL Server instance as normal. Click New Query to start a new query window. Finally, click the ‘change connection’ button, just to the left of the database selector:

sqlserverrepl03

That will pop-up a new dialog box to allow you to make a fresh connection to your SQL Server instance:

sqlserverrepl04

But notice this time that I’ve stuck ADMIN: in front of my usual ‘server name’ connection string. That will connect you in the right ‘god mode’. You can’t really tell anything is different from a non-DAC connection, apart from the ‘admin:’ identifiers scattered all round the SSMS interface if you look in all the right places!

Anyway, once you’ve got a DAC connection, you can type in this lot of SQL and run it:

USE master

GO

exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

GO

exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

GO

If all goes according to plan, you should see this sort of response:

sqlserverrepl05

Once the data types have been created for Oracle 12, drop the MSREPL7 table from your Oracle database and then try re-initializing your replication subscription once again. This time, it should just work:

sqlserverrepl06

That shows you me dropping MSREPL7 in a SQL*Plus window in blue; then I went and re-initialized the replication in SQL Server (which you can’t see me doing). You then see me describing the structure of MSREPL7 in the blue window: notice (a) the table has been re-created despite just being dropped; and (b) it’s been created with NUMBER and RAW data types for its columns, not INTERVALS and BLOBs. The Synchronization Status progress window finally shows that replication transactions were successfully delivered to the Oracle database.

You can also issue the required fixup SQL in the command-line sqlcmd tool. Connect to your database in DAC mode with the “-A” switch:

sqlcmd -S winsql\winsqldb -E -A

The -S takes the form it does here because it describes the server AND the instance to connect to (I use named instances). Hence “winsql” is the server name and “winsqldb” is the instance to connect to running on that server. The -E switch means “use Windows authentication”, because that’s what I do! If you use named users, you’d use the -U switch and specify the username (and the -P switch if you fancied typing his password in clear text on the command line!). The -A switch means, as I’ve mentioned, connect in DAC mode.

Note that DAC mode is only available locally: that is, you have to be working on the actual server that’s hosting the SQL Server instance. If you insist on running the fixup SQL remotely, you can enable remote DAC access by right-clicking the instance in SSMS, clicking Facets, selecting Surface Area Configuration from the drop-down menu and then setting RemoteDacEnabled to True. You also need the SQL Browser service to be running (which you can switch on from the normal services utility, or by running the SQL Server Configuration Manager, clicking the SQL Server Services item and then the SQL Browser item in the right-hand pane (right-click it and click Start).

Incidentally: you may have thought (as I innocently did) that upgrading your SQL Server from (say) 2008 R2 to (say) 2016 would help. All I can tell you is that the code snippet which appears at the top of this piece is as identically 12c-deficient in SQL Server 2016 as it is in any previous version. I found this surprising and completely inexplicable: I’m prepared to forgive SQL Server vintage 2008 for not knowing about Oracle 12c; I find it much harder to understand why SQL Server 2016 is silent on the subject!

So, as far as I know, this trick of manually adding a reference to 12c in DAC mode is the only way to get any version of SQL Server replicating to Oracle at all.

Whether bolting on a ’12’ to the end of a bit of code and running it is actually supported by Microsoft, I can’t say: I’ve looked for any indication it’s a properly-supported technique in all the usual places and turned up nothing either way.

But if you’re replicating SQL Server to Oracle 11g and thinking of upgrading to 12c any time soon, it might be useful to bear in mind that you’re going to need this technique should you ever need to re-initialize your subscriptions 🙂

We interrupt our broadcast…

…to announce that we’ve just sold our house. So it’s bye-bye wallabies and kangas. 🙁

The plan is now to rent a small-ish apartment somewhere nearer the city for a while. In the longer term, we’re planning to buy a house back in the UK: our days in Oz are numbered (simply because we want to travel more -and whereas 8 hours in the air from Heathrow gets you to most of Europe and lots of North America, 8 hours in the air from Sydney gets you to about the edge of Australian airspace).

Moving house is never fun. So expect the blog upkeep to suffer in the meantime. But I’ll do my best to keep things ticking over.

State of Play revisited

churchill150I mentioned some time ago that Churchill was looking pretty sick since no RHCSL 6.x distro with a version number greater than 6.6 seemed to deliver its software on a single DVD any more, and since multi-DVD installs break Churchill, Churchill was doomed.

But I was (happily) quite wrong on that point. It turns out that, in fact, CentOS is the only RHCSL distro that has chosen to package its distro in such a way that it breaks Churchill.

I’ve checked OEL 6.6, 6.7 and 6.8; and then I checked Scientific 6.6., 6.7 and 6.8; and I even checked genuine Red Hat 6.6, 6.7 and 6.8… and every single one of them works fine with Churchill.

So, as I mentioned last time, that now means Scientific Linux is the distro that Churchill will assume is in use if you use its ‘speed keys’ to build your Oracle server nodes. (Rather more bluntly, it means I’ve stopped using CentOS at all, for anything). You can still use OEL or Red Hat if you prefer, by spelling out your requirements on the boot strap line. But use a speed key to save some typing, and SL will be assumed.

And, of course, if you want to use CentOS 6.7 or 6.8 to build your Oracle servers using Churchill, forget it: because they just don’t work.

Why is the new default Scientific Linux and not, say, OEL? Simply because it’s (a) free to obtain without signing up for anything and (b) it’s not as butt-ugly as OEL when you boot it up (white text on a red background is neither pretty nor particularly legible).

Other than this issue of CentOS stopping working, the latest version of Churchill (we’re now up to version 1.6) introduces a rather more radical change of tack: no more support for Oracle 11g installations, of any sort, whatsoever. Of course, version 1.5 is still available which does still do 11g quite nicely -but 11g is so long in the tooth now, and completely out of support unless you happen to be using 11.2.0.4, for which an expensive paid support agreement is needed before it can even be downloaded, that it seemed a waste of time to keep 11g support in newer Churchill releases. So, 12c only it is.

Which of course means that Churchill’s hardware requirements have just gone through the roof to match Oracle’s own. A 2GB VM just doesn’t cut it any more. The new hardware minima are: 5GB RAM and a 40GB hard disk. So building a 2-node RAC and a 2-node Active Data Guard setup on your desktop now basically demands a 32GB desktop… and no laptops need apply.

All of which in turn means that I think it might be time to re-write the Churchill documentation (especially as it currently exists only as pages on the old version of this site): take the opportunity to spruce it up, refresh it, make it part of the native WordPress site and so on. Expect something soon-ish.

You can’t kick this around anymore…

nixonscan-finalRichard Nixon is the US president I admire the most, I think. He had a grasp of strategic thinking that few have matched. My admiration is often a source of amusement (and embarrassment) to my American friends, though, because that grasp of strategy failed him spectacularly during the Watergate affair, resulting in his resignation and disgrace.

But from my father, I sought, and was granted, special permission to watch his resignation speech, live, at 2AM London time in August 1974. I was a pretty committed 10 year-old! I also strongly suspect that without Nixon, none of us might be here today: the Cold War could have got very hot and very nasty on numerous occasions, until Nixon’s policy of détente made us all a lot safer.

Anyway, my fascination with the man lead me, several years ago now, to look to obtain his autograph (as one does with heroes). But they were (and are) not cheap, and ToH would not permit money-siphoning to take place on such a grand scale, despite my entreaties that a great man warrants grand money-wastage! Cheaper ones can be found if you look long enough, though-but I wasn’t that diligent back then, so everything seemed awfully pricey.

And then I happened to stumble across an auction on Ebay, for a letter allegedly signed by Nixon whilst he was serving as Vice President -so back in 1960. The asking price was about US$89, and no international shipping.

Well, at that price, it couldn’t be genuine, could it?! But the lady selling it said it had been sent to her father for some reason, he had died recently, she had no use for it and therefore wasn’t asking the world for it either. However, she couldn’t substantiate it more than that and knew nothing about its authenticity. But she would ship it to me in Australia if I paid some token sum or other (maybe an extra US$25).

And that is how I came to acquire my Nixon autograph for the piffling price of about US$115. It eventually arrived safely in the post -and this was my first chance to actually look and touch what I’d paid for. Well: I wasn’t disappointed. It certainly looked genuine (the signature is strong and definitely from a fountain pen). It also felt genuine: the paper is definitely old, the typewriting is definitely from a real typewriter.

Looks can be deceptive, of course! Nevertheless, the thing was framed and put in a place of honour on my study wall, where it has remained to this day.

But was it genuine??! It would bug me occasionally.

So early this week, I finally decided to do something about it: I sent the scanned copy of it you can see at the top of this piece to the Nixon Presidential Library, in Yorba Linda, California. I asked them if they had any records proving that, as the letter claimed, Nixon had been in Akron, Ohio on October 1st 1960 -and whether they could tell me anything about the addressee, Mr. Giampetro.

Their website is not the best! They don’t appear to know how to get https certificates to work, either! So I wasn’t hugely hopeful… but efficiency lurks in other corners of the library, it would seem, because this evening, not two days after I emailed, I received a very nice email from their archivist, one Dorissa Martinez, saying (and I quote):

According to our Pre-Presidential Materials (Laguna Niguel) Appearances (Series 207) finding aid, Vice President Nixon attended a rally at Memorial Hall in Akron, Ohio on October 1, 1960.

After searching through the Pre-Presidential Materials (Laguna Niguel) General Correspondence (Series 320) collection and Campaign 1960: PPS 57: Election. 1960. Post Election Correspondence, Acknowledgments, and Thank You’s, box 5, folder Thank you Letters Completed – Nationwide, I was unable to locate materials relating to Frank Giampetro.

I’m not sure I made entire sense of either sentence! But the general gist of it is that if you poke around the Pre-Presidential Materials long enough, you can indeed find evidence that Nixon was in Akron, Ohio on the date the letter claims he was. It doesn’t prove my letter is genuine… but it goes a long way to reassuring me on the point anyway.

It’s a shame there weren’t records that Frank Giampetro was, for example, a Republican Party hired driver, routinely used to transport any bigwigs that came by Ohio… so that part of the mystery remains. I’d certainly like to find out more about him (but the letter stays with me, come what may!)

Anyhow: I don’t think it would have mattered to me either way, but it’s nice to have my own little bit of near-confirmed Nixon-alia in front of me as I type. Let’s just hope greatness rubs off and shady dealings don’t!

State of Play

churchill150It has been almost a year since I did anything to my Churchill automation framework. (If you didn’t know or realise, Churchill lets you easily create virtual multi-node RAC and Active Data Guard environments).

I took a look at it lately to see about refreshing it. I discovered that whilst Churchill’s speed keys will assume that you’re using CentOS 6.6, CentOS has actually released versions 6.7 and 6.8 since the last Churchill refresh (as have Red Hat, Scientific Linux and Oracle, of course)! It is also still the case that no version of Churchill works with a RHCSL 7.x O/S (because systemd screws up network automation).

No worries, I thought to myself: tweak a few things and at least bring Churchill up-to-date-ish with version 6.8 as the new speedkey default.

No chance!

In their wisdom, the CentOS developers decided to split the main DVD release of 6.8 over two DVDs. That means Churchill’s trick of copying its own installation media onto its own hard disks (for subsequent automated network installs onto the RAC nodes to use) fails, because it can only copy one of the two install DVDs. (There is a special, 6.8-only Dual Layer DVD you can use, and that would work… but I decided not to go there for now).

This, combined with the systemd debacle, basically means that Churchill is dead in the water, and I therefore have made only one more tweak to it -whereby Scientific Linux version 6.7 is assumed to be the speedkey default- and after that, I won’t be maintaining it further.

Something new is required to automate 12c RACs on RHCSL 7.x environments… and I don’t know what that will be yet! Watch this space, I guess…