Oracloid Blog

A few month ago I posted about my indignation regarding the inability to change my email address on OTN. Now, I’m not only able to change my email address, but also the screen name (I don’t think I do that before either).

After a closer look, I saw that this applies only to my notification email address. However, I don’t see any other addresses in my profile, so I suppose it’s become an invisible primary key, which wouldn’t bother me in any way, to be honest.

In the end, it took Oracle just 2 months and 5 days to follow up on my post. Not too bad, considering that OTN forums were full of complaints for years! ;-)

You have probably read it already on my Pythain group blog but I would like to repeat it here to make sure you, my dear reader, don’t get a chance to miss it.

I would like to announce that last weekend the BAAG party was born. I have finally managed to introduce Joing BAAG Form and the list of BAAG members which is tiny so far. I’m very excited about it and looking forward to this project.

If you are tired of observing troubleshooting by guessing day by day, by day, by day, by … — join the forces of BAAG party. We can make a difference together! See you there.

Guesswork - just say no!

Greetings everyone. I would like to announce that last weekend the BAAG party was born. I have finally managed to introduce Joing BAAG Form and the list of BAAG members which is tiny so far. I’m very excited about it and looking forward to this project.

If you are tired of observing troubleshooting by guessing day by day, by day, by day, by … — join the forces of BAAG party. We can make a difference together! See you there.

Guesswork - just say no!

Here are a few nice typos that had quite disastrous consequences.

After having fought some network problems to get a distribution of Oracle installation binaries from OTN to a Linux box, a colleague found a revolutionary way to unpack a cpio archive:

$ cpio -idmv > ship.db.lnx32.cpio

We saved few gigs of space, by the way, but the installer didn’t work — ksh: ./runInstaller: not found. Weird.

Another interesting case involves changing permissions on a directory tree starting from current working directory:

/opt (root)# chown -R oracle:dba .*

Hm… it turned out that it wasn’t necessary — the whole OS was owned by oracle anyway.

Now more serious one. The best way to tar your database files:

/oracle/opt/oradata/ORCL> ls
control01.ctl     logmnr_tbs01.dbf  system01.dbf
control02.ctl     redo01.log        temp01.dbf
control03.ctl     redo02.log        temp02.dbf
dbmd01.dbf        redo03.log        tools01.dbf
dbmd_idx01.dbf    strmtbs01.dbf     undotbs01.dbf
dbvb01.dbf        sysaux01.dbf      users01.dbf
/oracle/opt/oradata/ORCL> tar cvf * ../ORCL.tar

Right, a controlfile is good but a “stuffed” controlfile is much better! Now you know why controlfile multiplexing is good even within the same directory.

Alex Fatkulin has already mentioned this on our blog a while ago and reminded me once again yesterday. I think some other bloggers have pointed it out too, but I want to emphasize it here again, since Oracle 10.2 release is getting more and more popular in production settings. It’s especially useful to look on it vis-a-vis upgrades. In addition, there is a small gotcha that might cause performance problems in certain extreme cases.

After an upgrade to 10gR2, you might observe that an Oracle instance uses more UNDO space. Apparently, this is the result of a change in behavior when AUTOEXTEND for UNDO datafiles is disabled. For some, this change caused a bit of confusion — was this a bug with non-autoextensible UNDO tablespaces or some kind of special optimization for autoextensible UNDO tablespaces?

(more…)

I’ve never really liked the idea of REMOTE_LOGIN_PASSWORDFILE=SHARED, probably just because I haven’t seen much use for it. As a result, I’ve never paid any attention to it. If you don’t recall the difference between EXCLUSIVE and SHARED settings for 9i, here is the quote from the documentation:

SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.

EXCLUSIVE
The password file can be used by only one database and the password file can contain names other than SYS.

As I said, I could never imagine the use case for a shared password file. If you have better ideas of a situation that is a good fit for shared password file, please share.

Today, I was going through a migration strategy with a client, and we were reviewing init.ora parameters. I noticed that they used SHARED settings for their password file and was curious why. Apparently, there was no clear explanation for SHARED setting and it was used more or less as exclusive — one file per instance. Alex Fatkulin seemed to be curious too and did the RTFM part for me (good boy!) and, surprise! the EXCLUSIVE setting is not used in 10g anymore.

It turned out that Oracle merged the SHARED and EXCLUSIVE password file features — now we can used shared password file and store passwords for users other than SYS. REMOTE_LOGIN_PASSWORDFILE=SHARED is used for that. EXCLUSIVE still works for backwards compatibility but now it behaves just like SHARED.

Here is the new reference from the docs:

NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

SHARED
One or more databases can use the password file. The password file can contain SYS as well as non-SYS users.

Note:
The value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED.

There are quite a few non-obvious behavior changes that are not well known but are in fact documented, so thorough RTFM-ing seems to be a good idea with every new release. For our part, we will try to post some of them here so stay tuned!

Some time ago, one of our customers contacted us to help them recover from a situation where one employee departing the company left behind quite a bit of hidden damage. (That there weren’t proper security and auditing policies in place is another story.)

What application-support discovered was that there were no problems with data deleted or changed, but rather something was changed in the Oracle schemas. After more investigation, they suspected PL/SQL procedures and packages. We had an option to restore the database to a certain point in time and try to capture previous versions. Using LogMiner was another idea and, as we later saw, a superior one.

We knew the time-frame when harmful changes could have been made, so we made sure that all archivelogs for that period were available. The next step was to start LogMiner. This was a 10g database so it was simple:

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> begin
  2  dbms_logmnr.start_logmnr(
  3  startTime => '2007-04-16 00:00:00',
  4  endTime => '2007-04-20 00:00:00',
  5  options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG + dbms_logmnr.CONTINUOUS_MINE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

(more…)

This was posted yesterday on Oracle-L by Li Li. I feel I should blog about it to spread the word, especially since not everyone in this world performs test-restores.

Li was executing a test-restore and hit a problem at the end of the point-in-time recovery phase:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: 'H:\xxx\xxx\xxx.dbf'
ORA-01206: file is not part of this database - wrong database id

Datafile 9 was a read-only tablespace, and the source database was actually created with RMAN DUPLICATE. Datafile 9 was read-only during that duplicate operation, and the status hadn’t changed since then. As you can imagine, the read-only datafiles were not changed and their headers still contained the DBID of the database that was the source of the RMAN DUPLICATE. A similar situation could probably happen if tablespaces were imported using transportable tablespaces feature, and left read only.

The fix in this case is to make tablespaces read-write for a moment, and then change back to read-only. The read-write operation will write new datafile headers and, consequently, put there the “right” DBID. IMPORTANT — this has to be done before backup and not after a disaster strikes. This case just emphasizes again the most important rule of any backup/recovery strategy is to do regular test-restores.

If it’s too late and something hit the fan — well, you probably have a chance to offline drop those tablespaces and, hopefully, be able to import them back, if those are transportable tablespaces, and the metadata dump file is still available.

Another idea would be to offline datafiles and then online them after OPEN RESETLOGS. Should someone try that — let us know if it works.

Today I’ve seen a question on OTN forum about ORA-1652 in alert.log in RAC environment. I immediately recalled the bug and thought that I should mention that here. I quickly found Metalink Note 258941.1 and was surprised to see that there are actually two bugs on that and I only was aware of one so I definitely have to blog about it now.

The issue that I hit in the past is that ORA-1652 can be dumped in alert.log without actually any queries failing. This was very confusing to find out as there was automated monitoring on alert.log errors and we had a lot of false positives resulting in escalations. In a nutshell, the problem is in the following. Every instance on Oracle RAC cluster maintains its own pool of free temporary extents and if there are no more free extents available in the current instance, ORA-1652 is generated but query doesn’t fail. Instead, RAC instance requests extents from another node and continues successfully. Bug 2858082 was filled for this issue. Oracle versions 9.2 and 10.1 are affected. Fixed in 9.2.0.7 and 10.1.0.4 and finally made it to 10.2.0.1 release.

Another bug (2934117) is related to DEFAULT TEMPORARY TABLESPACE feature. The situation is similar but in this case the query does fail with ORA-1652 as RAC instance is unable to acquire free temporary extents from another instance. There is a workaround and it’s fixed in 9.2.0.5 (you shouldn’t run a lower release these days) and 10.1.

I find the first bug very annoying as it causes problems for automated monitoring and proposed workaround (ignoring errors) is not feasible as the real issue can be missed.