Category: Oracle

Oracle Change Tables – An Example

Here is an example of how to create and use Oracle Change tables. This is part of what Oracle refers to as “change data capture” or CDC.

Oracle change tables allow you to capture what has changed in a specific table over a period of time. This can be useful if you are attempting to keep data updated with a source. Typically CDC is used as part of the “extract” process in an extract-transform-load procedure for loading a data warehouse.

There are many sties and articles on this topic that go into much more depth than I will here, however I have never found a fully functional demonstration of an entire CDC cycle.

Change table example code

For more information on change data capture I recommend Oracle’s Data Warehousing Guide. More on data warehousing in general can be found at Mark Rittman’s Oracle Weblog.

A new feature in Oracle 10g called ‘streams’ offers an alternative to this type of CDC, however I (and I’m sure many others) are still dealing with change tables on a regular basis.

Technorati tags: , , , , , ,

Oracle Password Hacks

As a DBA it is often useful to change a user’s database password for testing, but what if you don’t know the user’s original password so you can change it back when you’re done?

There is no easy way to decipher the encrypted password, but you can view it. What you can do is copy the user’s encrypted password, change the password to a known value for testing with the normal alter user command, then replace the original password with a special alter user command.

In my case I am actually using this to synchronize passwords between two databases to assure database links work properly. This will only work if the usernames are identical.

Here is an example of how I am using this technique to synchronize passwords:

First we want to set up a user with a known password

In the original database:

SYS:TEST> alter user jemmons identified by copyme;

User altered.

SYS:TEST> select username, password from dba_users where username='JEMMONS';

USERNAME PASSWORD
------------------------------ ------------------------------
JEMMONS EAEC44107194EBC6

Now we connect up to the database we want to clone the password to. Note the first attempt to connect as jemmons fails as that is not the assigned password.

In the database you want to copy the password to:

nolog> conn jemmons/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

nolog> conn / as sysdba
Connected.
nolog> alter user jemmons identified by values 'EAEC44107194EBC6';

User altered.

Elapsed: 00:00:00.01
nolog> conn jemmons/copyme;
Connected.

Now we see that this only works if the usernames are identical. This is because the hashed password is based on a combination of the password provided and the username.


nolog> conn / as sysdba
Connected.
nolog> alter user ken identified by values 'EAEC44107194EBC6';

User altered.

nolog> conn ken/copyme;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

If you want to know more on this, check out article from red-database-security.com

Note: This was done on a 9i database. This may or may not work across versions.

Technorati tags: , , , ,

Oracle Optimal Flexible Architecture

Originally written in 1990, the Optimal Flexible Architecture (OFA) whitepaper still stands as the best-practices for oracle databases. In my time as an Oracle database administrator I have often seen DBAs using these standards, having learned them from senior DBAs, who did not realize the OFA standard exists.

Cary V. Millsap of Oracle Corp. offers this description in this distribution of the whitepaper:

The OFA Standard is a set of configuration guidelines that will give you faster, more reliable Oracle databases that require less work to maintain. The OFA Standard is written by the founder of the Oracle team responsible for installing, tuning, and upgrading several hundreds of sites worldwide since 1990—this paper is based on the best practices of those hundreds of sites. Today the “Optimal Flexible Architecture’’ described in the OFA Standard is built into the Oracle configuration tools and documentation on all open systems ports.

The benifits of the OFA standard go beyond performance and stability. When I started at Plymouth State University 18 months ago I quickly recognized the signs of an OFA environment (mostly partitions named /u01 and /u02.) Having identified that, I immediately knew where to find nearly all data and configuration files.

The guidelines that OFA provides can be easily adapted to other modern multi-user applications including web servers, application servers and other breeds of database. I offer the OFA whitepaper here as I have found it increasingly difficult to find on the web.

The OFA Standard-Oracle for Open Systems

oracle, sql, dba, database administration, database development, database security, database, oracle security

Oracle Web Resources

Below are some of my favorite Oracle related web resources. Most have information on databases and Oracle Application Server. There are, of course, countless sites out there on the topic. These are just a few of the ones I prefer.

tahiti.oracle.com – This quick URL gives you direct access to the current Oracle documentation (with a free OTN account.) It’s a good quick back-door way into the oracle docs.

asktom.oracle.com – I don’t know the whole story on this guy, but he seems to work for (or closely with) Oracle answering user submitted questions. There are thousands of questions and answers on all things Oracle.

otn.oracle.com – Oracle’s Technology Network is the official source for tech news from Oracle. It is to the techies what www.oracle.com is to the business folks.

orafaq.com – More Oracle question and answer listings. Also a very useful and comprehensive glossary.

rittman.net – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual.

Technorati tags: , , , ,