Total Recall

One of the most interesting new features from my point of view in 11g is the new Total Recall option with it's Flashback Data Archive. Now it's possible to track data changes in the past in a very efficient manner both in terms of performance and disk consumption. Here's a quick example of setting up Flashback Data Archive:

CREATE FLASHBACK ARCHIVE DEFAULT FDA_1_YEAR TABLESPACE FDA_1_YEAR
QUOTA 100M RETENTION 1 YEAR;

alter table scott.emp flashback archive fda_1_year;

In the example above we created a flashback data archive (FDA)and then set the emp table to be included in the FDA and we wished to retain the flashback information for one year.

Having done this it's now possible to look backwards in time for the values of emp table using the 'as of' flashback sql-structure. An example of this would be:

select ename, sal from scott.emp as of timestamp to_timestamp('2007-14-08 15:30:00','YYYY-DD-MM HH24:MI:SS');

We can see from dba_flashback_archive_tables that Oracle stores the flashback information in a separate table which is actually range partioned and compressed to maximize performance and minimize space comsumption:

select table_name, archive_table_name from dba_flashback_archive_tables;

TABLENAME ARCHIVE_TABLE_NAME
------------------------------ -----------------------------------------------------
EMP SYS_FBA_HIST_69515

SQL> select compression, compress_for from dba_tab_partitions where table_name='SYS_FBA_HIST_69515';

COMPRESS COMPRESS_FOR
-------- ------------------
ENABLED FOR ALL OPERATIONS <-- This is new in Oracle 11g and means that compression will be continuous for this table.

FDA will be the way to go if you have the need to retain historical data changes for your database.

Oracle 11g is here!!

The latest release of Oracle database for Linux x86 can be downloaded here:

http://www.oracle.com/technology/software/products/database/index.html

The installation was once again a smooth experience and I will be discussing some really cool new features soon, including Real Application Testing, security enhancements, new partitioning schemes etc.

"su functionality" with sqlplus

Now this sounds a bit unbelievable at first but with 10g R2 "su functionality as we know it from Unixes" can be accomplished.

SQL> create user tester identified by tester;
User created.

SQL> grant connect, resource to tester;
Grant succeeded.

SQL> create user datauser identified by datauser;
User created.

SQL> grant connect, resource to datauser;
Grant succeeded.

SQL> connect datauser/datauser
Connected.

SQL> create table sample(a number);
Table created.

SQL> insert into sample values (99999);
1 row created.

SQL> commit;
Commit complete.

SQL> connect system
Enter password: ******
Connected.

SQL> alter user datauser grant connect through tester;
User altered.

SQL> connect tester[datauser]/tester
Connected.

SQL> show user
USER is "DATAUSER"

SQL> select user from dual;

USER
------------------------------
DATAUSER

SQL> desc sample
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> select sys_context('userenv','session_user') "Sess. User",
2 sys_context('userenv','proxy_user') "Prox.User",
3 sys_context('userenv','authentication_type') "Auth.type" from dual;

Sess. User Prox.User Auth.type
-------------------- -------------------- ----------
DATAUSER TESTER PROXY

Restrict DBA's data access

Preventing the DBA and other powerful users from seeing company confidential data has not been an easy task. With Oracle 10g R2 this can be easily accomplished via the use of Database Vault. DV allows you to define realms around your application data to block DBA's from seeing the data they're not supposed to see. DV allows you to define realms to protect your data through an easy to use web interface or through PL/SQL API's. Here are the basic steps to protect a schema with a realm:

1. Create the Realm:

Exec dbms_macadm.create_realm('Protected_Scott','Scott schema DV protected','YES',0);

2. Define the Schema and objects to be protected, this protects all objects of Scott's schema:

Exec dbms_macadm.add_object_to_realm(’Protected_Scott',’scott','%','%');

3. Give access to appropriate users for accessing the schema protected data:

Exec dbms_macadm.add_auth_to_realm(realm_name => ’Protected_Scott', grantee => ’scott', auth_options => 1);

And that's basically it. Now the schema scott is protected and scott is made the owner of the realm. From now on users such as sys and system will not be able to select data from scott's tables.

Taking the Database Vault configuration a step further enables features such as: multifactor authorization, Oracle Label Security integration etc. Utilizing these features one can for example create an environment where database access from certain IP-address at certain time of day restricts data visibility to certain OLS level.

Verifying your data hasn't been tampered

I was recently asked how one can verify that some data in a table hasn't been changed.
A solution that comes to mind is to use the hash procedure of DBMS_CRYPTO package.
Here's a quick example of how the procedure works:

SQL> select dbms_crypto.hash(utl_raw.cast_to_raw('Some Data..'),3) from dual;

DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('SOMEDATA..'),3)
--------------------------------------------------------------------------------
7C0266521736A069E4D057CB452A034F7C850C31

Here I'm asking for a hash using the number three as input parameter for the procedure that gives us the SHA-1 version hash. The procedure returns raw values (automatically converted to hex for us) . Valid input values are either raw or lob values, that's why the example uses the UTL_RAW.CAST_TO_RAW function to first convert the varchar2 value into a raw value. Comparing the generated hash value with the original hash value (stored somewhere) one can easily see if the data has been changed.

By the way if you don't have the documentation on hand you could always ask the database for the information about those supplied packages. For example I didn't remember the parameter for SHA-1 on the previous example so I used this query to find out what it was (DBMS_CRYPTO also supports md-4 and md-5 hashes):
select text from dba_source where name like'%DBMS_CRYPTO%';
...
-- PACKAGE NOTES
--
-- DBMS_CRYPTO contains basic cryptographic functions and
-- procedures. To use correctly and securely, a general level of
-- security expertise is assumed.
--
-- VARCHAR2 datatype is not supported. Cryptographic operations
-- on this type should be prefaced with conversions to a uniform
-- character set (AL32UTF8) and conversion to RAW type.
--
-- Prior to encryption, hashing or keyed hashing, CLOB datatype is
-- converted to AL32UTF8. This allows cryptographic data to be
-- transferred and understood between databases with different
-- character sets, across character set changes and between
-- separate processes (for example, Java programs).
--
---------------------------------------------------------------------------


-------------------------- ALGORITHM CONSTANTS ----------------------------
-- The following constants refer to various types of cryptographic
-- functions available from this package. Some of the constants
-- represent modifiers to these algorithms.
---------------------------------------------------------------------------

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;
...

there I have the information needed :)

Raw Devices and RHEL4

If you're using raw devices with RHEL4 you might not know that their permissions are not retained after reboot.

Solution for this is to simply edit the file:
/etc/udev/permissions.d/50-udev.permissions

If your raw disks are used for RAC voting disks or OCR for example
you could change a line containing "raw/*:root:disk:0660" to something like: "raw/*:oracle:dba:0660" and that should do the trick.

A small invaluable utility

If you're like me and prefer command line tools for some operations then you need to familiarize yourself with a tool called rlwrap. This handy little tool will give you a command line history for various command line tools. I use it with sqlplus, rman etc..
Here is how I call it from .bash_profile:

alias dba='rlwrap sqlplus / as sysdba'
alias rman='rlwrap rman'
alias sqlplus='rlwrap sqlplus'

After setting these aliases I have a command line history for sqlplus and rman. Rlwrap can even be configured to include word completion lists to enable fast typing of command sequences. You should find .rpm for rlwrap easily with google.

Posting starts..Hello World!

Having had this in my mind for a while I finally decided to start blogging. This blog will have mainly technical topics and most of it will be around various Oracle technologies.
I think this could be a great way to share ideas with my friends and collegues. Also having a blog could act as a sort of a "personal technical diary", I think it might be nice to see afterwards what topics were "hot" at a given point in the past. Any comments will be appreciated.