Category: Oracle 11g

More thoughts against Triggers

For Rob & Karl – Triggers run outside of transactions. An insert that fires a trigger may be rolled back, but the trigger rolls on.

Triggers introduce a long-term maintenance headache. You can read a stored-procedure from top to bottom and imagine you understand what it does. But unless you examine every tables it touches – you don’t. Little bits of code may be running silently which augment or even reverse some of the logic within the stored-procedure.

Triggers are used by lazy developers to ‘bolt on’ new features to applications, rather than track-down all the code that could insert/update/delete from a table and add the code (or a link to it) there.

This would be forgivable if the application code was closed or propitiatory, but never when the application is open to the application developer, who just cannot be bothered to integrate code changes properly, and cares not-a-jot about long-term maintenance headaches (slow breaths, slow breaths :))

Changing RAC to noarchivelog mode

Whilst trying to change an Oracle 11g2 database to noarchivelog mode I kept getting the error “ORA-01126: database must be mounted exclusive and not open for this operation”.

The word “exclusive” reminded me this was part of a cluster. Here’s the commands I ultimately used …

sql> select log-mode from gv$database; -- to check the modes of all the nodes.
c:> srvctl status database -d CLUSTER_NAME -- check nodes are running
c:> srvctl stop database -d CLUSTER_NAME -- stop all nodes of the database
c:> srvctl status database -d CLUSTER_NAME -- check nodes are stopped
sql> startup mount exclusive; -- mount database just on this node
sql> alter database noarchivelog; -- change the log-mode
sql> select log_mode from gv$database; - check it worked
sql> shutdown immediate; -- shutdown this node
c:> srvctl start database -d CLUSTER_NAME -- startup all nodes of the database
sql> select log_mode from gv$database; -- check modes of all nodes
c:> srvctl status database -d CLUSTER_NAME -- check nodes are running

Of-course I used the actual name of the cluster instead of “CLUSTER_NAME” above.

The Oracle SCN issue

Oracle-database uses a large number (‘SCN’) as a time-stamp for referential-integrity, backup/restores, patch levels etc etc. When instances are linked the highest SCN number overwrites all others.

Trouble is … a ‘warning limit’ is being reached (three quarters of the actual limit). And when exceeded Oracle-database assumes its corrupt and becomes unstable.

But wait! There’s a ‘fix’, a patch which lets you manually set the SCN back below the soft-limit. BUT this needs to occur on all linked instances within a short period, or they are just updated from the one with the highest number (dowh!).

And Finally … the fix is only available for the latest version of Oracle. So an obscure, forgotten, out-of-date, instance in the back-of-beyond can bring down hundreds of shiny new machines (‘patched’ or not) until upgraded.

‘Oracle SCN management’ then, not a disaster just another ongoing DBA task 🙂

SSH Authentication

To login to a customers remote Oracle/AIX server – without any passwords being exchanged – I downloaded puttygen. Entered a pass-phrase,  and generated matching public and private keys.

On my desktop I had two text-files called ‘public’ and ‘private’. I emailed the public one to the customer. And when they had authorized it, pasted the private one to a file on the remote machine (called ‘id_rsa.ppk’ if I recall).

I configured putty with the username/host (eg: oracle@127.0.0.1), and in the SSH/auth box browsed to the ppk file. I saved my settings and exited.

When I started putty again I loaded the profile for this host and checked everything had been saved correctly, then connected.

I was logged in as ‘oracle’ and asked for my original pass-phrase. And thats all there was to it :-))

Dataguard gap check

Thanks to E for this manual check that dataguard is updating a standby machine.

On Primary

alter system switch logfile;
select * from v$log;
Record the current Sequence#

On Standby

select process, sequence#, status from v$managed_standby;
Check RFS & MRP0 values match, and are related to the primary Sequence#.