You may think the title of this article refers to “Learning Oracle is as easy as one, two, three.” Actually, the numbers I have in mind are eight, nine and ten (and soon to be eleven). Although Oracle obviously increments the version number from release to release, the versions you work with from job to job may not, that is, you may be working with 10g in your current job, but in the new one (where you’ve just accepted a job offer), you will be taking not just one, but two steps back version-wise and find yourself having to deal with Oracle 8.1.7.
If your DBA career started with an older version and you’ve migrated upwards as newer versions were released, then going back to an older version as part of a new job (or for other reasons) is relatively easy. On the other hand, if your career started under a newer version, how can you win the interview contest without ever having had any workplace/professional experience on an older version of Oracle? It’s hard enough as it is just being able to almost master Oracle 10g let alone trying to devote time to learn the nuances of an older version. What follows is some advice on how you can overcome a step or two backwards with respect to Oracle.
Migration is Job Number One
Take advantage of your familiarity with the newer version(s) you’ve been working with on a day to day basis. If your new employer has been looking to migrate from 8i to 10g (something you can ascertain during an interview when it’s your turn to ask questions), then making that migration take place after you’re hired should, if at all possible, be mission number one for you. With respect to migrating databases from one version to another, the fact of the matter is that quite a few DBAs have never performed a migration. For many, migration is a rare event in terms of frequency (but shouldn’t be in terms of having had practiced it on a test box before doing it in a live setting). Your selling point in this regard is that you can offer to develop a migration plan. Your trade off is the difference between learning “old” technology versus learning how to orchestrate a single event (the migration).
Understand What is Basically Unchanged
If you frame the differences between 8i and 10g in the context of what the Oracle8i exam areas covered (SQL, Architecture & Administration, Performance Tuning, Backup & Recovery, and Networking), you’ll see that SQL, tuning, and networking are essentially the same. Are there differences? Most definitely, but for the most part, none of them are big selling points for upgrading or having to know more about an older version. SQL is basically the same, but if you’ve grown accustomed to REGEXP, say goodbye to that. Tuning in 8i was more about ratios and counts as opposed to wait event analysis. The good news is that you can take some wait event analysis skills and apply them to an older version. Many third party tools support several older versions (See Quest Software’s Toad for Oracle), so if you can bring Toad along, tools such as query analyzer can help make tuning jobs easier to perform. Finally, chances are very good that the use of TNSNAMES you see in an 8i environment is exactly the same in your 10g system.
Understand What is Significantly Changed or Different
In my opinion, there are at least seven major differences out of what literally seems like hundreds of changes or improvements now found in 10g when compared to 8i (and it too, had numerous changes over version 7). The “new feature explosion” observed in recent versions of Oracle is like witnessing the Big Bang cosmological model firsthand, that is, the universe called Oracle keeps expanding as time goes on. You can overcome a lack of hands-on experience in most of these areas by being able to apply concepts and first principles (i.e., a starting place from which everything else can be derived). Here is a short list of seven major differences.
1. Tablespace Management
If the system tablespace is locally managed (but not in 8i), then so are the others. If dictionary managed, then others can be one or the other. Given that 8i allows tablespaces to be locally managed, and knowing that the default (and soon to be the only way) is LMT in 10g, you can make 8i tablespaces more like 10g by using LMT. Why should you use LMTs?
Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:
The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace. |
Think “all space is created equal” and if that is the case, say goodbye to having to deal with managing extents.
2. Rollback Segments
RBS is my personal favorite “feature” to dislike. You’re stuck with rollback segments in 8i, but that doesn’t mean you can’t make them like an UNDO tablespace. You size UNDO space by making it big enough to handle a time or retention policy. Size your rollback segments large enough to accomplish the same thing. In both undo management schemes, you are not immune to snapshot too old errors. How are they avoided? By being able to keep the starting read consistent view of a transaction.
3. Oracle Managed Files
Create an OMF file in 10g and its name may not mean anything to you. You don’t have to use Oracle managed files in 10g, and there are some valid reasons not to. Personally, I like being able to “read” a file name and be able to ascertain which tablespace it belongs to and what it is used for (tables or indexes), but on the other hand, it is nice to be able to check “Add Oracle Managed File” in Toad or just write “add datafile size whatever” in SQL. The main concept here is that you can (and should) establish and implement a structured file naming management system in an 8i environment (if not already done for you). Closely related to OMF is OFA (which was mentioned in 8i). The first principle here is that you can demonstrate an understanding of how and where files are maintained.
4. Flashback
Someone drops a table in your 8i archivelog mode database (or even in a 10g database). Without using flashback, simply because it was not present in 8i, how do you recover the table? This is by far and away the number one task or skill you need to be able to rattle off in your sleep, and there is no reason you can’t practice this in a 10g environment. The first principle being applied here is how to perform recovery. You stand a much better chance of surviving a temporarily poorly performing database than you do a permanent loss of data. Flashback and the recycle bin are without doubt extremely convenient mechanisms, but they are not absolute safeguards in terms of preventing permanent loss of data. What if the table was dropped past what your retention policy is, or what if the recycle bin was flushed? You’re essentially in the same boat as just described in the 8i environment.
5. Recovery Manager
Okay, RMAN was available in 8i, but it has improved a good deal since. RMAN was relatively slow to be adopted because of its somewhat odd syntax and also because it was competing with tried and true backup scripts. The difference really being pointed out here has to do with user managed versus server managed recovery, where recovery is made possible by the type of backup being performed. You may walk into an 8i environment where backups are executed by a series of “alter tablespace whatever begin backup” (followed by what command when done?), so you need to know the ancient Jedi master/first principle way of taking a backup. Once you are driving the database, you can implement RMAN as your backup mechanism, but until then, you have to know the pre-RMAN method of performing a backup.
6. Built-in Packages
There are hundreds of built-in packages in Oracle. The best advice here is to take the ones you find yourself using on a regular basis in 10g and find their counterparts in 8i. One example has to do with statistics. Do you compute or gather statistics via one of several packages, or by using an “analyze table” command?
7. Rule-Based Optimizer
In 8i, the Cost-Based Optimizer is much less robust than what you’re used to in 10g, except for one area it seems. Oracle8i documentation says:
The CBO chooses the most efficient join order based on cost after permuting the possible join graphs. Hence, there is no need, or benefit, to ordering the
FROMclause under the CBO.
However, in 10g, the CBO must have regressed because documentation there says:
Choose the best join order, driving to the best unused filters earliest.
The bottom line is that you may see code in 8i -based applications that baffles you as to why anyone would ever append a space to a column name or add zero to a value. In 8i, those are means of disabling an index. The first principle here is that good performance is largely dependent on good SQL. A lot of what applies in 10g tuning also applies in 8i, but with 8i, you also have the benefit (arguably good, depending on the situation) of using the RBO.
In Closing
If you see a job that looks appealing to you, but the job description mentions an older version of Oracle, don’t be dissuaded by a lack of hands-on experience in that older version. Prudent application of first principles (and what you know in a newer version) is just as applicable – for the most part – in older versions of Oracle. A solid grounding in the basics can help you in landing a new (or even first) job.
