11g New Feature - External table compression and encryption

HAPPY NEW YEAR... well, almost. I thought that I'd share an Oracle Database 11g New Feature with you now that the year is almost done. We will talk about external table encryption and compression! Both of these features require either a license for the compression option or a license for the advanced security option. Sorry, I don't determine what is licensed and what isn't, I just shell out information on the new features! :-)

External Table Compression

Oracle Data Pump supports compression of dumpfiles in Oracle Database 11g. As a side-effect, you can also compress external tables dump files when creating them. Simple add the compression enabled syntax to the access parameters section as seen in this example:

Drop table ext_new_emp;
host del c:\oracle\admin\orcl\dpdump\new_emp.dmp

create table ext_new_emp
organization external
(
type oracle_datapump
default directory DATA_PUMP_DIR
access parameters
(logfile DATA_PUMP_DIR compression enabled)
location ('new_emp.dmp')
)
as select * from new_emp;

External Table Encryption

Oracle Database 11g offers TDE, Transparent Data Encryption. The Oracle Data Pump driver supports encryption in Oracle Database 11g. Use the encryption enabled keyword when creating the external table to encrypt the resulting dump file as seen in this example:

Drop table ext_new_emp;
host del c:\oracle\admin\orcl\dpdump\new_emp.dmp
create table ext_new_emp
organization external
(
type oracle_datapump
default directory DATA_PUMP_DIR
access parameters
(logfile DATA_PUMP_DIR encryption enabled)
location ('new_emp.dmp')
)
as select * from new_emp;

Merry Christmas and an Oracle Database 11g New Feature - Password Changes

MERRY CHRISTMAS!!!! I hope you are enjoying this holiday!!

There are some new features in Oracle Database 11g with respect to user passwords you might want to know about.

1. DBA_USERS no longer displays the password hash at all. The column is there, but it's NULL.
2. You can still use user$ to find the password hash.
3. Common passwords are salted, thus the hashing is different as seen here.

The robert and robert1 account both have a password set to robert in this example:

SQL> l
1 select name, password from user$
2* where name like '%ROBERT%'
SQL> /

NAME PASSWORD
------------------------------ -----------------
ROBERT F26C10F60B4EFB98
ROBERT1 F3042C3EBB6E134F

Note that the hashes are different even though the passwords are the same. This is because the passwords are "salted".

4. Salting does not impact the ability to use the "using values" parameter with the SAME ACCOUNT as seen here:

SQL> /

NAME PASSWORD
------------------------------ ------------------------------
ROBERT F26C10F60B4EFB98
ROBERT1 F3042C3EBB6E134F

SQL> grant dba to robert, robert1
2 ;

Grant succeeded.

SQL> alter user robert1 identified by dodo;

User altered.

SQL> connect robert1/dodo
Connected.
SQL> alter user robert1 identified by values 'F3042C3EBB6E134F';

User altered.

However, if you use the same hash value with a different account, it will not work. So here, we changed the robert1
account to use the hash password for the robert account (which is still the password robert). Thus, the hash is dependent on the user account (which provides the "salting" key)...

SQL> connect robert1/robert
Connected.
SQL> alter user robert1 identified by values 'F26C10F60B4EFB98';

User altered.

SQL> connect robert1/robert
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

My office view...

I just thought I'd share the view from my office window this afternoon. The snow on the mountains looks beautiful. For those of you not acquainted with Salt Lake City... The temple is probably pretty easy to recognize. It's nestled between the Church Office Building (COB) on the left and the Joseph Smith Building (JSMB) to the right. You can see the conference center to the far left of the picture too. Note the flag on the JSMB. The wind is blowing pretty hard today, pretty much out of the south.

Not a bad view if I say so myself. Have a great day and more 11g Features are coming soon!

11g New Feature - DBMS_COMPARISON!!

I thought I'd share an 11g New Feature with you today! This is in the form of the dbms_comparison package that is new in Oracle Database 11g.

With dbms_compare you can compare objects/schemas/data between two different databases or schema's. This is handy, say, in replicated environments when things get out of wack. You can use dbms_compare to help determine if there is some data divergence between two tables. If data divergence is found you can bring the objects to a consistent state.

For example, if I have two schemas in my database (scott and Robert in this case) I can compare the EMP tables in both schemas. First I create the comparison:

exec dbms_comparison.create_comparison('robert','robert','emp',null,null,null,'scott','emp');

This tells Oracle that I'm getting ready to compare these two objects. You can do this over a DBLink too by just defining the name of the dblink. Even more, while the local compare side must be Oracle Database 11g, the remote site can be Oracle Database 10g release one or later!!

Now I can do the actual compare as seen here:

declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return :=
dbms_comparison.compare (
comparison_name=>'robert',
scan_info=>compare_info,
perform_row_dif=>TRUE);

if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is data divergence.');
dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
end if;
end;
/

Here is the result in my case:

Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the
differences for scan_id:13

As we can see here, the results send us to dba_comparison and dba_comparison_scan_summary where we find these results:

select a.owner, a.comparison_name, a.schema_name, a.object_name,
z.current_dif_count difference
from dba_comparison a, dba_comparison_scan_summary z
where a.comparison_name=z.comparison_name
and a.owner=z.owner
and z.scan_id=13;

OWNER COMPARISON_NAME
------------------------------ ------------------------------
SCHEMA_NAME OBJECT_NAME DIFFERENCE
------------------------------ ------------------------------ ----------
ROBERT ROBERT
ROBERT EMP 16

There are actually a lot of views you can use here including:

• DBA_COMPARISON

• USER_COMPARISON
  

• DBA_COMPARISON_COLUMNS

• USER_COMPARISON_COLUMNS

• DBA_COMPARISON_SCAN

• USER_COMPARISON_SCAN

• DBA_COMPARISON_SCAN_SUMMARY

• USER_COMPARISON_SCAN_SUMMARY

• DBA_COMPARISON_SCAN_VALUES

• USER_COMPARISON_SCAN_VALUES

• DBA_COMPARISON_ROW_DIF

• USER_COMPARISON_ROW_DIF

You can also use the dbms_comparison.converge function to "fix" the data, as it were. This procedure will converge our objects, taking care of the data divergence. With this procedure you can either say that the remote or local table is to be the "master" table. Divergences will be sourced from that object. So, in our case we can update the robert.emp table so that it's no longer divergent with the scott.emp table. Here is an example:

declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge (
comparison_name=>'robert',
scan_id=>13,
scan_info=>compare_info,
converge_options=>dbms_comparison.cmp_converge_remote_wins);

dbms_output.put_line('--- Results ---');
dbms_output.put_line('Local rows Merged by process: '||compare_info.loc_rows_merged);
dbms_output.put_line('Remote rows Merged by process: '||compare_info.rmt_rows_merged);
dbms_output.put_line('Local rows Deleted by process: '||compare_info.loc_rows_deleted);
dbms_output.put_line('Remote rows Deleted by process: '||compare_info.rmt_rows_deleted);
end;
/

--- Results ---
Local rows Merged by process: 16
Remote rows Merged by process: 0
Local rows Deleted by process: 0
Remote rows Deleted by process: 0

Note that if you run this more than once, you are in no danger of duplicating your data as seen in this subsequent output from a second run:

--- Results ---
Local rows Merged by process: 0
Remote rows Merged by process: 0
Local rows Deleted by process: 0
Remote rows Deleted by process: 0

Something else to be aware of. If the data changes in either table, it can impact the compare/converge operations. Thus for best results, you should quiesce all activity on the tables being compared.

There are some other requirements with this feature that you will want to review (as always), but it is a very powerful new feature in Oracle Database 11g!

Fun fun 11g!!