Author: dheltzel

Synonyms are well understood and commonly used objects that get downright “quirky” when dealing with editions, so I wanted to devote an entire post just to them. We like to think of a synonym like an alias, shortcut or symbolic link (to borrow terminology from various OS’s). In principle, they do operate like that, but in order to properly track dependencies on the synonym and the object it resolves to, Oracle does a great deal more under the covers.

If owned by a schema that is edition-enabled, the synonym automatically belongs to the edition in which it is created. Internally, that means it is tagged with that edition name (as are all editioned objects). A basic rule for editioning is that only another editioned object can have a dependency on an editioned object. Non-editioned objects can exist in the dependency tree for an editioned object, but only as far up as the first editioned object. It makes sense if you consider that a non-editioned object cannot identify with an edition and so multiple copies of a parent object could all be considered equally valid. There is no way to determine for sure which edition of the object to use. One could assume the default edition, but that could change with a single command and cause all the non-editioned objects to resolve to a (potentially) different object version. Rather than try to deal with that messy situation, Oracle made a rule that no non-editioned object can point to an editioned object.

Several issues with synonyms result from this. First, since the PUBLIC user is not a real schema and can’t be edition-enabled, all public synonyms for editioned objects are invalid and unusable. So you must drop them and replace them with private synonyms. That leads to the next issue. Those private synonyms resolve to objects in their edition (or the nearest parent edition they are instantiated into) when created. So far so good, all is normal. But now imagine creating a new edition that is a child of the current edition and compiling some objects there that use the synonyms in what is now the parent edition. If the synonym in the parent edition resolves to an object with a version in both editions, which one does the calling object get the dependency on? You would want, and expect, it to be the parent object in the edition that the dependent object belongs to, since that preserves our understood assumption that synonyms act like an alias. I have not found that to be consistently true. The synonyms seem to respect the object id rather than the name and can resolve to different edition-version of an object than you would get if you were using a fully qualified name instead of the synonym.

It took me a while to sort out that this was happening and figure out how to deal with it. My basic rule now it to never trust the inheritance of dependencies across editions. Perhaps it will be change to work correctly at some point, but in the mean time, this will cause you much grief if you simply follow Oracle’s examples. Here are my recommendations:

1) Use the fully qualified name of the object if feasible to avoid using synonyms at all

2) If you must use the private synonyms, compile them in each edition in which a version of the object they resolve to exists

Imagine you have a generic database that is just storing data in tables. No compiled code or views, just bare tables. Now you add 2 web servers that both get their data from this shared database. Your normal reasoning for doing this is to scale your app and add some redundancy to the middle tier, but this has an additional benefit. When it comes time to upgrade the code in your app, you can remove one web server from the load balancer, forcing all the traffic to go to the remaining WS. While the first WS is not taking any traffic, you are free to upgrade and test the app. Since no one sees the site as down, you may take your time with this and do all the testing and tweaking you want. Only when you are confident in the newly upgraded WS do you place it back in service. For overall consistency, you probably now want to remove the 2nd WS and watch that your newly upgraded server is acting exactly as you expect. Once you have a high level of confidence that the new version of your app is what you want it to be, and little chance you will want to revert to the old version, you simply run the same upgrade on the 2nd WS and place it back into service as well. Upgrade done safely and efficiently.

Obvious and old news really. You have virtualized your code into sets that run independently, in this case on web servers. Now let’s extend that to imagine that we can virtualize our code inside the database. Just as a load balancer makes decisions about which web servers to send connections to based on network addresses (name or IP), now our middle tier needs to make a decision about which virtual code set to use within the database. Once we connect to that code set, everything else looks the same, but at a high level we have achieved with database code that we did earlier with web server code, and now we can upgrade our database app with the same process (at least conceptually).

The tag that identifies a virtual code set in Oracle is called an “edition”. If you are running an 11gR2 database, you are already using a virtual code set, possibly without even knowing it existed, it’s an edition called ORA$BASE.

Also called EBR, or just editioning, this is a new feature in Oracle 11gR2. A fair amount has already been written about the “how”, and a little bit about the “why”. I’m not going to go over that, but instead present a series of posts about my experiences implementing editioning in a production environment.

First of all, I prefer to call this editioning, but often shorten it to editions, though that could cause some confusion with the whole standard vs enterprise edition thing. In this context I will expect you to understand editions to refer to editioning within the db.

This is a very complex new feature, but Oracle does not charge extra for using it, and I think I know why. At a high level all that editions do is to correct a decades old design flaw in the way Oracle runs code inside the db. In the modern world we expect always up systems, but that is fairly new concept (except for mainframes, which everyone mostly ignores). Back when Oracle was building the engines that run code, hard downtime was acceptable and worth taking to ensure a single point of consistent code execution. This was when there was a movement to put all business logic inside the database. The need for scalability proved that to be ill-advised in many cases, and the rise of a middle tier made it possible to scale out horizontally, and as a side-effect, to run different code versions at the same time — so long as the db portion of the code didn’t require a corresponding change. If it did, you were just out of luck, until the use of editions corrected that deficiency. So Oracle could not charge for what is really a design fix. If they tried to, few would pay for it, preferring to spend that money to move the rest of the business logic out of the db.