Author: bitbach

Undo retention, flashback query and ora-01555 snapshot too old


Setting up a fault aware database environment is in charge of regarding possible physical and logical error scenarios. On the physical side of the medal, you got real-application-cluster or dataguard at your disposal. The logical one usually comprises backup and replication. Recently, oracle and others introduced features that enables a database to recover from logical errors without or with less remote systems and data. Namely flashback is a powerful technology to look back into (query), back out (transaction) or fully restore (database) the history of data. Flashback of course takes accompanying (history) (meta)data to do its job, namely again, undo-before-images, archived-redo-logs and flashback-logs. So, essentially, the convenience of your course into history depends on the amount of history metadata that is available to the database at the time of a logical error, say an inadvertant delete from.

You may already have learned or heard about or even hardly experienced that flashback-logs may be purged from the recovery-area in favour of archived-redo-logs, rendering your configured flashback-retention-time (DB_FLASHBACK_RETENTION_TARGET) a value of theory. So far, sizing the file-system recovery-area is a task to be performed in a clear-sighted manner. The same is true then for undo-before-images (compare to logs) und the undo-tablespace (compare to area) to accomodate enough data to meet your configured undo-retention-time (UNDO_RETENTION) in UNDO_MANAGEMENT=AUTO mode, while in addition still support long running workload queries (not to overwrite old undo). This article discusses the usability of the (enterprise-manager) undo-advisor in sizing your undo-tablespace to always foster successful flashback queries up to the configured undo-retention-time (as opposed to the nasty surprise of getting an ora-01555, see below, which is also misleading here, imho, in suggesting that the undo-tablespace is to small now, as we know it from workload queries – nope, it has been to small before now, in the past, as regarded to flashback queries – also see : ora-01555 snapshot too old when running flashback query).

(more…)

XMLType.schemaValidate is your only true friend for xmlschema validation


When it comes to introducing xml-data into your database storage, you might, as all sincere developers do, at first attempt to take care of proper data integrity checking on import. Since xml is a really powerful but a somewhat complex document-alike data representation, such integrity checking must incorporate proving the document strucuture, as sort of integrated data types, iff you like, as well as the document data, in terms of facets of given actual values. According to the xml standard, xmlschema is the means of choice here, offering another xml-spelled specification layer to achive the two beforementioned goals. Eventually, a given xml-instance will have to be thrown against the xmlschema provided, to assure its integrity (and being well-formed too, btw), the earlier the better, at best on data import already.

The paragraph above is actually not very oracle specific. Any implementation of the xml standard, as oracle’s xml db does, proposes this course of action. However, since some xml operations may become quite costly when the xml-instances get large and the whole dom tree has to be set up in memory, oracle, as others, dabbles at dodging and shifting pricey work to the most reasonable extend possible. You may load xml into the database, claiming any xml-instances are just fine, validate xml-instances only to be well-formed, manage the validation status of xml-instances at your own responsibility and so forth. There is nothing wrong about that whatsoever. The point is, though, it might be a stony walkway to learn to distinguish the maybe from the certain, to establish a reliable check to safeguard your xml data integrity, just as simple as ALTER TABLE mytab ADD(CONSTRAINT mytab_CHK CHECK(INSERT_TS is not null) NOT DEFERRABLE ENABLE);.

Jep, what next? I’m going to briefly discuss the xmlschema / xmlinstance used below and then show what I experienced will happen using the various ways of “validating” the instance against the schema. The post is somewhat lengthy, but do not get frightened, this is for the c&p examples all along the way.

(more…)

Some irritation due to extended output of systemd / systemctl status for sysv services


Bad is an unpleasant word, right? “On” or “off” implies some final statement, or “failed” may signal something wrong but at least terminated. But “bad”… uuuhh, blameworthy, guilty, unaccountable, still being around. Ok, before diving into linguistic depression, the change eventually turned out simple and was actually in good faith but, however, produced remarkable irritation. You know, systemctl status {service} will show an overview of some systemd unit definition with load state, current activity and so on. The load state, in particular, details, in parantheses, into the path of the unit file, the enablement and the vendor enablement preset, respectively. Original systemd units may give a load state as follows:

Loaded: loaded (/usr/lib/systemd/system/atop.service; enabled; vendor preset: disabled)

However, systemd units, that have just been derived from systemv init scripts, only printed the init script path since lately:

Loaded: loaded (/etc/rc.d/init.d/sysv-thing)

The new irritating factor now is an extension for those derived systemv init scripts, to also state the enablement, but show up as “bad” for the running enablement for whatever weird reason:

Loaded: loaded (/etc/rc.d/init.d/sysv-thing; bad; vendor preset: disabled)

(more…)

Scheduling / descheduling linux host reboots via shutdown


Scheduling and/or descheduling linux host reboots is possible with the shutdown -r command using the time parameter (the reboot command, that I usually prefer in favour of clarity, does’nt feature the time parameter, so shutdown -r is the only choice here). Aside from discussing the quite straightforward man page of shutdown, there is two points here to register in your knowledge cells.
First, a (scheduled) shutdown -r hh24:mi execution will hangup itself into background, no need to use job-tools or an &. shutdown -r hh24:mi actually puts systemd-shutdownd in charge of serving the party, this is what you”ll want to expect to see in your running process list, looking for some command effect. Also, a running scheduled shutdown may be cancelled using shutdown -c any time before hh24:mi. Note however, that from around five minutes before hh24:mi, you’ll you’ll no longer be allowed to login the machine, essentially impeding any further control from your side.

(more…)

Understanding “occurrences before alerts” in oracle enterprise manager 13c


The Incident Manager within Oracle Enterprise Manager 13c is a powerful tool for monitoring a wealth of target types such as databases, hosts, middleware or just services in general from a bird’s eye view or right down into the manifold details of a dedicated application. As with all powerful means around, where there is a lot of power, there also usually is a lot to do wrong (or even employ counterproductive). This is why it is important to understand the Incident Manager concepts from bottom up and be able to identify the knobs and wheels to poke with to meet a certain requirement.

Basically, Incident Manager is a professional toolset to facilitate the management of non-critical and critical system alerts against metric values (registered as problems and incidents, see About Incidents and Problems) in a larger quantity and time scale along with alert management templating and alert assigment and so forth.

(more…)