Bob Mycroft's Oracle Blog

I discovered something truly marvellous today while doing a little revision – something I missed in 10g! I had seen restore points in various documentation sets and thought “Oh – its just a tag to flashback to when running flashback-database – Ok fine”. Then when I see references to  “Guaranteed restore points” I made an assumption that it just meant that oracle wouldn’t delete the flashback logs it needed to honour the restore point.…

I was kinda correct...However, the interesting part is that to run with guaranteed restore points you don’t actually need to be running in flashback database mode with all the (possible) pain of having flashback logs filling your Flash Recovery Area.  Basically as long as you are at least running in archivelog mode, and have a flash_recovery_area defined then you can use guaranteed restore points to flash the database back even without having flashback database (FBD) enabled.

So – how does this work? When FBD is enabled, the before image of a block is written away to the flashback logs at the point a change is made to that block. The flashback logs are analogous to redo logs only the record the “undoing” not the “doing”. As long as we have flashback logs that go back in time to the point at which we want to flash back to, all is good; We can flash back to any time in that timefield. A normal restore point in this scenario is in effect just a tag that identifies a time.

If we create a guaranteed restore point and we DON’T have FBD enabled how can we still flashback? After all, no flashback logs are produced right?   Well… oracle does indeed create flashback logs for us when we create the restore point. The difference is that only the first change to a block is written away to the flashback logs – we aren’t saying we want to flashback to any time in this case, just to the guaranteed restore point. Hence any additional changes to a block after the first isn’t needed or recorded. We don’t need all the before-images for every change – just the first “before” image of the blocks that subsequently got changed.

Note that this only works with guaranteed restore points; If you try it with a plain restore point without being in FBD mode you get ;

ORA-38782 cannot flashback database to non-guaranteed restore point "string"

Ok - so a quick walkthrough;

Found a useful thing today that I wanted to share. It used to be a right royal pain clearing up or removing an old repository; It wasnt enough just to drop the SYSMAN schema unfortunately.

Im playing with EMGRID 11g at the moment and needed to clean out a dbconsole repository so I could install the EMGRID repository. I found that theres a tool now that can do this for us! The RepManager binary can be found under the RDBMS ORACLE_HOME, as below;

Job Done!

-Bob

10.2.0.3 64 bit on SLES

Fixing a corrupt OCRfile in 10g

One of the tasks that has been on my to-do is fixing the oracle Cluster for one of our of our systems. We are not running RAC on this system but are clustering a single instance of oracle through Oracle Clusterware’s High Availability Framework in much the same way that you can run Oracle RAC One Node in 11.2.  

In essence one of the OCR files had corrupted.  The servers are at our hosted environment and unfortunately it would appear the company is not contracted to monitor the Clusterware components. As a result although the databases were up and running fine, we were  not sent any alerts for the corrupt OCR and only spotted it when going on to do some remote maintenance.

Because the system was up and running, we didn’t want to bring it down unless we had to.

The symptoms;

• ocrcheck gives error output.

•   OCR backups are from a long time ago

Ok so no problem – we saw the ocrmirror is bust while the other is fine so the inherent redundancy has kept it up and running.   We don’t want to bring down a running system.

First  step; We want to receate the ocrmirror using the following syntax.; Unfortunately this throws internal errors

Step 2 – Ok…lets try creating it into a new separate file.

Now we talk to Oracle Support and they suggest creatuing an empty file with dd before creating the ocr_mirror. Ive done something similar with creating voting disks …but haven’t a lot of faith…

Oracles advice – try creating it into a file we have created using dd. We did a lot of interesting stuff like run ocrconfig with strace to see what the problem was…

Oracles response is now “Its 10.2.0.3 – you probably needs to reinstall CRS and upgrade to 10.2.0.4”.

Unfortunately the client is very rigid about what versions of software are allowed. So although we would like to put 10.2.0.4 in place, company standards and bureaucracy prevents us.

So the last step is that we have to bring the cluster down so we decided to restore one of the old OCR backups – the logic is that the backups are good because Oracle cannot/will not back up a corrupt OCR. Even though the backups are ancient, they should stil be good as no config has changed in the cluster in a long time.

If we continue to get the Internal errors when doing the OCR restore, then its reinstall time as per Oracle Supports advice….

We have deliberately left the restore until last simply as this involves taking an otherwise-working system down – all kinds of fun booking downtime etc.

All good clean fun

-B

Ok so today was the worst day at work I’ve had in a very very long time.

One of my tasks today was to clean out and populate a test database from an export of Production. Its something I’ve done a thousand times and have it all nicely scripted up, but today I was rushing due to an earlier problem impinging on a deadline and let a typo really make a mess of my week.

The test and the production database names I was working with differ by one character – one database name ends in "pr", the other in "pf". The killer was that at the point I ran the scripts, both test and prod had the same password for the system account.

In essence I accidentally ran the "truncate all the tables".sql script against production rather than against the test database. This wasn’t good. In fact this was very bad. This was very very bad. And no rollback as it was "truncate" not "delete"....

Quickly checking the backup situation it looked ok - but then there was the dawning realisation that I would have to restore and recover more than just this one database, indeed more than just this one system.

The database I broke was one of 3 that are loosely tied together. One stores the application data, one is the database component of a 3rd party workflow system that sends messages between the other two, and the third database is a document archival & retrieval system.

Because the 3 databases are tied together, restoring one back to just before the incident also meant rolling back the other two. To compound the issue another application entirely shares the document-archival system & database so rolling that database back meant rolling the other system back as well.

Suddenly a cock-up in one database in one application has meant restoring back and rolling forwards four databases across two distinct applications.

Ok so I did a point in time recovery for each database, to a point 3 minutes before when I had run the truncate script. So far so good.. ?

Now it gets nasty - although the databases were back within a short time period, the document archival system has a "Lucene Index" which is built from data in the database and from the documents stored therein and this file-based index is stored in a filesystem.

This Lucene index is now out of sync with the data in the database and has to be rebuilt.......

             ...this takes about 12 hours and while the rebuild is running, all the applications have to stay down. This re-index is running as we speak.

The bottom line here is that although the databases were back within 60 minutes, the applications as a whole will not be available for another 12 hours.

The lessons to learn here

1) Don’t have the same passwords in Prod and Test for anything - captain cock-up will come visiting...eventually.

2) Even if you have valid database backups and have Flashback database switched on in 10g onwards, the time required to get your application back is not necessarily just the time it takes to get the databases back. When you do your Disaster Recovery planning, take this into account. Often the fact that you might be using a standby database might give you a warm feeling that you are safe but in reality there could be monsters like the above lurking and waiting .

I have not had a good day today; I cannot remember ever having made a cock-up on this scale ever before and I’m so annoyed with myself. Tomorrow morning we will get the system back and then have to do some smoke testing to check it all looks good before handing over to the business to check out.

Hopefully tomorrow will be more positive.

At least I have stopped feeling like I’m going to throw up now. Well, nearly anyway.

Not a happy bunny.

A little while back I was thinking about getting my own CSI code so that when I am between contracts I can continue to access Metalink. Whatever you think about the new interface to "My Oracle Support" these days, the actual contents are invaluable (IMHO) when you get stuck and want to look at potential solutions, or for that matter just learn something new.  Initially I was looking at the Standard Edition Database license for a single named used, which came in at about £210 give or take the cost of a pizza. Not too bad but could I get it cheaper.....? At the time yes, but it looks like Oracle have closed the loophole...

It boils down to a question of "Whats the cheapest thing for which I can buy support?" and the answer was Orace Access Manager. Picking "Non-Employee User External" in the drop down list and the price is a handsome £3.66 plus 80 pence for annual support. Add in VAT and it was about a fiver. At this point the minimum named users was just 1 - unfortunately it looks like Oracle have spotted this and the minimum is now 5000 users.

I managed to get my CSI code and the like before they changed the minimum number of users, but its a shame that they changed this

-  Bob