Tracking configuration in Oracle databases
Or how I learned to stop worrying and love my auditor
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.