Tracking configuration in Oracle databases

Or how I learned to stop worrying and love my auditor

  • alert
  • submit to reddit

Workshop Databases are never static. The data in them changes, of course, but so does the meta-information about that database. Oracle holds information about each user's database assets in separate schemas (and the system also has its own). This information will change as new developments happen over time. It's the database administrator (DBAs) job to keep track of it. How can they do that effectively, without putting all of their time into change tracking?

There are several reasons to track the configuration of your database after it is first set up. The first two are focused on your technical responsibility to deliver a functional database to the business. The second two focus more on business-based responsibilities.

Performance Undocumented configuration changes can lead to performance issues. Even in the smallest databases, one innocuous change can have an impact on how the database functions. Being able to see how a configuration was affected over time makes it easier to backtrack and find the cause of any performance problems

Availability Performance is one thing; dead databases are quite another. Incorrectly configured systems can lay a database low. Documenting - and ideally approving - changes made to a system can help to avoid disaster later on.

Compliance In the US especially but also in Europe, legislation introduced over the last 15 years has highlighted the need for better internal controls, in some more heavily regulated industries, but also across the board.

Forensics In some less secure environments, DBAs aren't the only ones who get to push changes through to the production database. Having a solid record of configuration alterations going back over a long period will prepare DBAs for if the auditors ever come knocking, wanting to know if a disgruntled employee may have made an alteration to a schema somewhere, for destructive malice, or fraud.

Starting off with configuration

In the course of your life as a DBA, you'll probably set up more than your fair share of databases. Do yourself a favour and develop templates for your configurations from the start. The configurations will change over the database's lifetime, of course, but at least you'll start with a standard gold build, and that's a good first step. Oracle's Database Configuration Assistant (DBCA) can help you with this.

All database templates include database options, initialization parameters, and storage attributes. You can use either one of two template types: a seed, which includes the datafiles for a database, and a non-seed, which doesn't. The latter lets you change the names and sizes of your datafile, along with other parameters, giving you more flexibility.

Some approaches to change tracking

One way to track changes in a database is via database triggers. A trigger is an action that fires in response to a specific database event, and they're written in Oracle's PL/SQL language. You can use triggers to write entries into an audit log whenever specific actions are carried out on a table, for example - from simple UPDATEs and INSERTs, through to whole tableDROPs.

One of the problems with triggers is that they can impact performance, if they're used on frequently occurring events. The other is that they can be changed by privileged users.

Another way to tackle change management is to mine the REDO logs, which are the logs that Oracle automatically writes whenever a new operation is conducted against the database. But again, privileged users can alter these, making it tricky if you’re doing this for compliance purposes. This is also a reactive policy; you're tracking changes after they've occurred, rather than documenting them before they happen.

Another option is the Flashback Data Archive feature, inside Total Recall in Oracle 11g. This is a new dictionary object in Oracle 11g, and it's a container for historical information about a given table or set of tables. When data manipulation commands are passed through the buffer cache, they are saved to this container via a background process. Like triggers and REDO logs, FDA can be used to track any change to database data. It can also be used to track changes to the schema, such as the addition or deletion of columns, for example.

Structures for change management

Having a template makes it even easier to document the initial configuration of your database, but you'll need a system for documenting those changes over time.

The savvy DBA will set up at least one non-production server (development) and preferably another (testing/staging) server, where configuration changes can be thrashed out before being applied to the production system. Some DBAs may even separate the testing/staging process into two servers.

Orchestration is documentation

One apparently simple way to work out what changes have been made on the non-production system is to run a difference analysis on the development and production databases whenever you're ready to push changes from the former to the latter. That may sound simple, but likely to produce a list of every single insignificant change that was made, often even if they were changed back later.

Another, more effective way to do it is to document the configuration changes as they are first being written and applied on a development system. For this, you can use a separate source control system.

The source control system will be a place to store the SQL or PL/SQL code (Oracle's language for creating stored procedures), which is what you'll be using to make those changes to the database. These scripts will be written elsewhere and applied to these non-production systems over time.

There are several ways to handle this source control, using different tools, but Toad for Oracle includes features for version control, and for managing which developers or administrators can change files.

Toad's source management system integrates with a variety of version control systems, including Perforce and CVS. Developers can write their PL/SQL statements (including data definition language statements), and have them committed to the group workspace and versioned.

The version control system integration will only enable developers to check in and out code stored outside of the database, but developers may want to retain control of their code inside the database, too. For this, use Toad for Oracle's Team Coding function, which can be implemented alongside the version control integration. This allows you to control who accesses and changes your source once it is inside database objects.

The other part of this configuration change puzzle is to set your team users' privileges so that they can't push changes to the production database; only you should be able to do that.

Change management and documenting configuration is an important part of the Oracle DBA's job. Get it right, and you'll save yourself headaches further down the road. Not only does it create good governance for managing future database changes, but it also enables you to look back over past changes and find out where they came from, and understand why. ®

The Register is running a series of Oracle DBA workshop articles in association with Dell Software. Interested in learning more? Check out these Dell whitepapers.

More from The Register

next story
New 'Cosmos' browser surfs the net by TXT alone
No data plan? No WiFi? No worries ... except sluggish download speed
'Windows 9' LEAK: Microsoft's playing catchup with Linux
Multiple desktops and live tiles in restored Start button star in new vids
iOS 8 release: WebGL now runs everywhere. Hurrah for 3D graphics!
HTML 5's pretty neat ... when your browser supports it
'People have forgotten just how late the first iPhone arrived ...'
Plus: 'Google's IDEALISM is an injudicious justification for inappropriate biz practices'
Mathematica hits the Web
Wolfram embraces the cloud, promies private cloud cut of its number-cruncher
Mozilla shutters Labs, tells nobody it's been dead for five months
Staffer's blog reveals all as projects languish on GitHub
SUSE Linux owner Attachmate gobbled by Micro Focus for $2.3bn
Merger will lead to mainframe and COBOL powerhouse
iOS 8 Healthkit gets a bug SO Apple KILLS it. That's real healthcare!
Not fit for purpose on day of launch, says Cupertino
prev story


Secure remote control for conventional and virtual desktops
Balancing user privacy and privileged access, in accordance with compliance frameworks and legislation. Evaluating any potential remote control choice.
WIN a very cool portable ZX Spectrum
Win a one-off portable Spectrum built by legendary hardware hacker Ben Heck
Storage capacity and performance optimization at Mizuno USA
Mizuno USA turn to Tegile storage technology to solve both their SAN and backup issues.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
The next step in data security
With recent increased privacy concerns and computers becoming more powerful, the chance of hackers being able to crack smaller-sized RSA keys increases.