Cloned databases and RMAN (the NID utility)

I ran into an issue with RMAN recently when trying to setup backups for one of our production databases. It kept erroring, saying that it effectively couldn’t reconcile the database name against the DBID. It turned out this was because our test database was in the same recovery catalog and, since it was cloned from production, had the same DBID.

So just in case you weren’t aware, if you’re going to be using RMAN (with a recovery catalog) to backup cloned databases, make sure you renew the DBID of the clone immediately after cloning using the nid utility.

Shutdown the database, startup mount and execute:

nid target=sys/

Cheers,

Ian

9i auditing via trigger

A need arose to do some column-level auditing in one of our 9i databases the other day. An example of what was implemented is as follows:

CREATE TABLE t (t1 NUMBER, t2 VARCHAR2(10));

INSERT INTO t VALUES (1, 'IAN');
COMMIT;

CREATE TABLE audit_t
(op_date DATE,
op_user VARCHAR2(255),
old_t2 VARCHAR2(10),
new_t2 VARCHAR2(10));

CREATE OR REPLACE TRIGGER audit_t_trig
BEFORE DELETE OR INSERT OR UPDATE ON t
FOR EACH ROW
BEGIN
INSERT INTO audit_t
VALUES
(sysdate,
user,
:old.t2,
:new.t2);
END;
/

So when the T2 column in table T is changed (UPDATEd, INSERTed, DELETEd) a record is written to AUDIT_T detailing the datestamp, the user, what the old value was, and what the new value is.

Cheers,

Ian