The Blog from the DBA Classroom

By: Joel Goodman

Archive for April, 2009

Recovered Yet??

Posted by Joel Goodman on 20/04/2009

In my post on Shared Sessions and Shared Servers I mentioned the new “Bits and Pieces” format at the UKOUG Technical SIGs which invited SIG delegates to speak spontaneously for as little as five minutes to share useful tips and experiences with their colleagues.

This was a nice addition to the SIG and consistant with my DBA classroom where delegates are invited to conribute if they can on my courses to everyone’s benefit.

I spoke about a few topics on that day each for a short time and the one that generated the most lively debate and discussion centred on “Backup and Recovery” strategy for distributed systems.

It began when I asked “who used DB links” which most delegates did. The next point was to distinguish between “Remote Transactions” and “Distributed Transactions” in order to see who might need to revisit their strategy.

In a “Remote Transaction” the local session updates one or more objects in a remote database over a DB link. When a “Commit” is issued by the local Database Instance, it causes a “Single Phase Commit” request to be passed over the DB link to be processed in the remote database which either commits or rolls back depending on its own internal processing requirements and which returns control to the calling instance.

In “Distributed Transactions”, two or more databases have objects updated necessitating a “Two Phased Commit” which is controlled from the Database instance where the client or middle tier is connected, also known as the “Global Coordinator”. “Two Phased” commit protocol guarantees that all participating instances will either commit or rollback and not have a mixed outcome. It is not my intention to cover the details of Oracle Database Two Phased Commit here as this is a large topic in itself which I plan to deliver later this year in a MasterClass format.

The “Backup and Recovery” strategy issue relates to situations where one of the participating databases has undergone a Database Point in Time Recovery (DBPITR) for any reason. This may have been caused by corruption, by incorrect input to a batch run, by loss of redo logs discovered during normal recovery; it does not matter really. If DBPITR is done to one of the databases, then the other databases that participate in the distributed transactions must be recovered back to the same global SCN in order for the entire distributed environment to once again be consistant.

I also told the group of a delegate on a course I delivered about four or five years ago whose jaw dropped when I mentioned this Distributed Systems Backup and recovery situation. She was a DBA for a large bank which four months prior, had performed  DBPITR  to one of three databases involved in an international distributed system but not to the other two databases. She spent much of the breaks and lunch for the next two or three days on her mobile in meetings to decide what should be done.

I asked the User Group how many of the 70 or so people using DB Links and distributed transactions had performed DBPITR in the past year. Only a couple of DBAs claimed to have done so. I then asked how many know how to perform this recovery and most were not sure or did not reply (it is an SCN based DBPITR for the other participating databases).

Furthermore one of the DBAs had a related issue where the database did not use links but stored some data on another system using AQ but where the AQ Data was not backed off when the Database had a DBPITR performed.

So the discussion then turned to listing those configurations and scenarios that we as a group could think of where coordinated Recovery is needed after a Database has DBPITR performed on it.

Here is the list we created of situations involving database transactions which include at least some updates done in some other environment outside the database instance to which the client connects:

1. Distributed Transactions Using DB Links

2. Systems updating data using UTL_FILE

3. Systems using AQ or Streams Queuing

4. Systems creating Data Pump External Tables

5. Systems making “extproc” callouts to programmes that update files

6. Systems using Bfile Lobs

7. Systems using XA for open Distributed Transactions

To me the best part of this 10 minutes was not my original idea of warning about recovery when using DB links but rather that the group as a whole then built upon this idea making an interesting discussion and expanding the original idea. But it certainly convinced many DBAs that they ought to revisit their “Backup and Recovery”  strategy. If you have any of these configurations, then  you might wish to do so as well.

Please comment if you have other scenarios to add to the list or if you have has a problem caused by the lack of a comprehensive recovery strategy.


Posted in Oracle | 2 Comments »

Oracle DBA Certification Exam Development Process

Posted by Joel Goodman on 17/04/2009

I have posted details in the past on preparing for Oracle DBA Certification Tests but if you are UK based and a member of the UKOUG then you may be interested in my presentation at the UKOUG UNIX Special Interest Group on May 20th, 2009. Here are the details.

Posted in Oracle | 3 Comments »

Virtual Oracle DBA 2.0

Posted by Joel Goodman on 01/04/2009

You may remember the paper that I co-wrote last year with Harald van Breederode on DBA 2.0 skills. Following on from this success we introduce The Virtual DBA 2.0. Virtual DBAs are like  virtual machines, which are hosted on a real machine possibly along with one or more other virtual machines running the same or different operating systems at the same or different times on the same hardware. The DBA variety permits one person to perform a much wider range of tasks and to have a much wider range of skills that a conventianal DBA 2.0. This includes doing all DBA jobs

1. Serially

2. In Parallel

3. In any combination of Language or Country

Since Virtual DBAs look like any other DBA it may be useful to know how to recognise  them. They typically:

1.  Know details of All Operating Systems Supporting Oracle

2.  Know details of all Storage Systems supported by Oracle

3. Know the details of all Network Systems and protocols used by Oracle

4. Seem to lack consistant concentration as if they are only there some of the time. But they dont realise that they ar only there some of the time.

5. Seem to leave the room whenever another person suspected of being a virtual DBA enters. This can be a serious problem if many virtual DBAs work in the same place and requires a hardware assist.

6. Seem to think that virtual machines are real but that other virtual DBAs are not. A virtual phone network will permit these Virtual DBAs to communicate.

7. Are very sensitive to criticism from others when proposing new ideas especially when people say “That will never work! Get Real!” If the Virtual DBA does get real it may have serious consequences for other Virtual DBAs but has no affect on real DBAs.

8. Freezes in a catatonic state when the words “virtually finished” are uttered so this expression must be removed from everyday speech.

By avoiding some unfortunate phrases and by exploiting a full range of Hardware, storage and network options plus some extra network options one may get maximal benefits from the virtual Oracle DBA 2.0.

You are virtually guaranteed that this will be a success.

Posted in Oracle | 1 Comment »