Auditing Nuances

I had posted it earlier on my other blog - Oracle Security Musings (http://www.proligence.com/blog/index.html). I reproduce here.

This came up in the Oracle-L maiilng list, which I used to frequent and I responded to this thread.

In regular Oracle auditing (via the AUDIT command), what happens when the statement that causes the audit trail is rolled back? Does the trail disapper, too?

The answer may startle you. It doesn't! Audit entry generation is not part of the transaction; it might have been implemented through autonomous transactions or some other means; but it is outside the scope of the tranasaction. Here is a little test to prove it.

Make sure that audit_trail is set to DB.

create table atest1 (col1 number, col2 number, col3 number, col4 number);
insert into atest1 values (1,1,1,1);
audit update on atest1 by access;
update atest1 set col1 = 2;

Do NOT commit.

From another session as user SYS,

select action_name, obj_name, ses_actions, returncode from dba_audit_trail;

ACTION_NAME OBJ_NAME SES_ACTIONS RETURNCODE
----------- -------- ----------- -----------------
UPDATE ATEST1 0

The entry is there even if the transaction is not committed.

Now rollback the update and check the audit trail; it will be there.

If the auditing option were BY SESSION, instead of action, the ACTION_NAME would have been SESSION REC and the column SES_ACTIONS would've been"----------S-----". Beware of this side-effect of the audit functionality. It may not be what you wanted to develop!

A recent picture

someone observed that my picture on OTN appears much younger than what I really look. Well, the OTN picture was taken some 4 years ago. Here is one, just days ago.

I hope I appear "old" enough!

RAT in 11g Replacing QA Team?

We are in the process of moving to a different hardware and cluster technology and the quesiton on everyone's mind is: will it perform.

Good question. The QA team has been helping us in definign some QA. Then, one day, the manager of the QA team sees my interview on eWeek: and sends me a quizzical email: "so, you think my team has outlived its useful life?" or words to that effect. and he also wants to know how RAT really is.

Here is my response:

It’s really, really cool! It does not replace what you are doing for [the current buisness project] or for that matter any future development efforts. It does however help in situations like the data center migrations as we are doing now where we can capture the workload from [primary site] and replay every piece in [new site] to see if the hardware and design keeps up or crumbles. Currently we don’t do that for a variety of reasons – the enormous time requirement to create synthetic transactions for [third party tool] being the primary one. RAT will alleviate that and make that testing possible and realistic.

So, your team’s job is more value added; not replaced.

He may have accepted it with a grumble.

Standby Database Using RMAN

I learn something about Oracle everyday and in the last few days the learnings have been rather painful. I have been trying to create a physical standby database on version 10.1.0.4 on HP-UX. The target is a two node RAC and the standby is a single instance database. the reason for the standby was to move to an ASM storage on a differerent server.

I will try to produce a whole cookbook on the effort; but in the meantime let me explain the pain points.

Like everyone else, I scanned the books, articles, MetaLink, the internet, the socks drawer and broom closet; but couldn't find a good write up on how to perform this task in real life.

For instance, most of the recipes suggested taking a cold backup, something that was impossible since the target is a production database.

Others recipes suggested taking a special backup of the database using RMAN with a special clause - WITH CONTROLFILECOPY AS STANDBY (or something like that; I forgot th exact syntax). Well, duh! the backup is already taken and I can't re-exeute this "special" backup. So down with Option 2.

There was no clear document on how to do it. So, here was my first attempt in a nutshell:

(1) Mount the RMAN backup filesystems on the standby server using NFS
(2) On the standby server, create the pfile
(3) start the instance (nomount)
(4) add the service to the listener
(5) add the service to the tnsnames.ora
(6) on the primary, add the tns entry for the standby
(7) On the primary issue the command

RMAN> backup current controlfile for standby format '/backup/c.ctl';

(8) create the rman file:

connect target /
connect auxiliary sys/manager@odssby
connect catalog rman/rman@catdb
run {
set until scn = 71196399221;
duplicate target database for standby
dorecover;
}

(9) and now comes the golden moment. I run it:

$ rman @r1.rman

and it fails after some time with the error:

RMAN 6024 no backup or copy of the controlfile found to restore

Well, this is wierd. The controlfile has been created on the primary as a standby controlfile. This is also recorded in the catalog. So, what's the deal with the "no backup of controlfile"?

Some Metalink articles even suggested something like

run {
restore controfile from '/backup/c.ctl';
replicate controlfile from ....
}

this could not have worked for me, since I am not creating a new database; I'm creating a standby one.

So, I delvedinto some research. I will spae you the details and cut straight to the chase. The issue was with the time when the controlfile backup was taken. In my case, here is what I did:

Time -> ----+----T1---------T2----------
Arc Log Controlfile
Backup Backup
Taken Created

This cuased the recovery to start at a point earlier to T1, where there was no controlfile backup for standby. Naturally the restore failed.

The solution: simple. Just took another backup of archived logs after time T2. Once the backups were succefully created on the disk, I ran the rman script again and voila! everything works.

The real issue is how RMAN displays messages. Instead of producing a relevant message that points to the issue, it produces a rather cryptic message that gives the impression that some controlfile backup is not present. Well, perhaps in 12g, becuase I know that is not available in 11g.

Resuscitating the Comatose

Well, my foray into world of bloging didn't have a great fanfare but I didn't expect it to die down so soon. Here is my attempt at Blogging version 2 and I intend to keep it up.