Bill Ennis (Serverside Technology Solutons)

Open Source Data Profilers

2009-11-10T10:59:00.000-08:00

I've been looking into Open Source Data Profilers of late.
These allow you to get some basic info on your data quickly.
The 2 I like the most so far are the Talend Open Profiler
and another called Data Cleaner. Talend is more graphical and
Data Cleaner is a bit more intuitive so they both have their merits.

http://www.talend.com
http://datacleaner.eobjects.org

Talend also sells an "Enterprise" version of the product

I also came across an Open Source Data Modeling tool that has some
basic profiling capabilities named Power Architect.

http://www.sqlpower.ca

wwv_flow_epg_include_mod_local

2009-08-18T08:50:00.000-07:00

At some point between Apex 2 and 3 Oracle decided tighten the security on calling stored procedures. I had a procedure that was being used to download documents from the database to the browser. This worked fine in Apex 2, but was broken after the upgrade. Upon clicking on the document link I was receiving a "Forbidden" message. This indicated a security problem. After some searching I came across others talking about a function that effectively allows you to "register" procedures that you would like allow to be called via the PL/SQL gateway. This procedure is in the APEX schema and is named wwv_flow_epg_include_mod_local.
I needed to comment out the return false at the beginning of the function and add the name of the procedure I wanted to allow calling to the list.
i.e.

if upper(procedure_name) in (
'QUANT.APEX_DOWNLOAD_NOTEBOOK_ENTRY') then
return TRUE;
else
return FALSE;
end if;

RMAN switch to copy

2009-08-13T14:41:00.000-07:00

Today I successfully used RMAN switch to copy to swap out my 500GB drives for a 1.5TB drives with minimal downtime. I have a simple setup where data resides on an E: drive and backups on the F: drive. The process was relatively simple.

1) Within RMAN execute backup as copy database
2) Shutdown the database
3) startup mount
4) Within RMAN execute switch database to copy
5) Within RMAN execute recover database
6) alter database open

At this point I had the database running from the F: drive with the exeption of the redo and control files. In my case these reside on C: which was unaffected by this execise, but something you should be aware of. Also, the Temp tablespace files was not moved from this. There may be an option to include the TEMP tablespace, however it was easy enough for me to just create a new default tempory TEMP tablespace (named FTEMP) and drop the old one.

Now the move back. This really was just the same exercise in the other direction. In order to make it work I needed to first change the db_recovery_file_dest to point back to the E: drive

1) alter system set db_recovery_file_dest=E:\oradata
2) Within RMAN execute backup as copy database
3) Shutdown the database
4) startup mount
5) Within RMAN execute switch database to copy
6) Within RMAN execute recover database
7) alter database open
8) Create a new default TEMP tablespace on E:
9) alter system set db_recovery_file_dest=F:\flash_recovery_area scope=both

At this point the database is back on the E: drive. However, we are vulnerable at this point so I immediately kicked off a database backup to get things back to a suitable state.

One side effect of this execrise was that my datafiles were renamed. Before the move I had a naming convention for datafiles using the tablespace_name_.DBF. For example,
for the QUANT tablespace QUANT_01.DBF, QUANT_02,DBF, etc... I don't know of a way to name datafiles according to this format using backup as copy database. You can use a custom format at the individual copy datafile level, but that seemed error prone since I would need to type in the file names by hand and run alot more switch commands.

Save Outlook attachements to disk

2009-07-17T14:17:00.000-07:00

This handy bit of Powerscript code allows you to save attachements in your Outlook inbox to disk:

You need to pass a file name, not just a destination directory to SaveAsFile.The attachment has a FileName property.
This code groups attachments with same name and saves the first attachment in each group, if the attachment is corrupt or if it can't save it the Trap handles the exception; in this sample it just writes a message.You could also iterate through each group's attachments and append a different number to the name in order to save all.

$inbox=6
$outlook = new-object -com Outlook.Application
$inbox = $outlook.Session.GetDefaultFolder($inbox)
foreach ($group in $inbox.items % {$_.attachments} group filename) {
trap {
Write-Host There was a problem saving $fName
continue}
if ($group.Name.startswith("SECTSPDR")) {
$fName = "C:\TEMP\$($group.Name)"
$group.Group[0].saveasfile($fName)
if ($?) {Write-Host $fName was saved succesfuly.}
}
}

Backup options on your home network

2009-07-17T11:07:00.000-07:00

I think I have settled on a good backup option for my desktop at home. I have an external hard drive connected to my desktop. Previously, I had installed rsync via cygwin which I have successfully used in the past. However, every few days I would find the process hung and had to reboot the machine. It appeared that this was not a common problem and it's not very easy finding support for free tools like cygwin (understandably)

Recently I cam across a tool similar to rsync from Microsoft named robocopy. This utility is included in the Windows resource tookit. It seems to be working well and have not yet found it hung like rsync was doing.

I also added an email to the job to mail the results of the run. For this I installed blat. I also had to hook into an SMTP server (using Gmail for this). Since GMail required SSL I needed to install and configure stunnel to communicate via SSL.

Scientific Notation and SQL Loader

2008-06-19T12:31:00.000-07:00

Sometimes you receive data in scientific notation. The way to handle this when using sql loader is to utilize the FLOAT EXTERNAL directive in the sql loader controlfile.

ex
STD_DEV FLOAT EXTERNAL

11G upgrade Heterogeneous Services quirk

2008-05-28T06:33:00.000-07:00

Last week I went through an upgrade from 10G to 11G and had a problem with some dblinks I had setup to use the generic ODBC gateway. The links stopped working. The fix was to check the Enable Quoted Identifiers option in the DSN Advanced tab.

Also, as I was searching I found some suggestions to add the following line to the hs/admin/init.ora file(s)

HS_FDS_SUPPORT_STATISTICS=FALSE

Oracle on Windows nuances

2008-05-13T10:16:00.000-07:00

Over the past year I have begun to support Oracle in a Windows environment. This experience hasn't been as horrifying as expected, however there still have been some nuances. here's my list:

Windows password changes suddenly prevent EM jobs from running. This is something you need to dicover as soon as possible
Restoring a database on Windows with RMAN requires there be a Windows service running for the database.
Removing software and Oracle Home assignments have always been tough on Windows (even for a client only install which I have worked with quite a bit). This is due to the registry mess and convoluted ways to manage environment settings in Windows.
"Logon as Batch' is required for Enterprise Manager jobs that will be running. This is assigned Via Control Panel Administrative Tools Local Security Policy Local Policies User Rights Assignment

RMAN switch to COPY

2008-05-07T13:19:00.000-07:00

RMAN has a neat feature that allows you to switch the database to run on a backup copy (after recoverying it)

Setup:
-------
In order to set this up you need a datafile copy backup setup in RMAN.
example:
BACKUP FOR RECOVER OF COPY DATAFILECOPY FORMAT='T:\ImageCopies\ORCL\%d_%I_%N_%f' WITH TAG '%d_image_copy' DATABASE;

Make sure T:\ is independent from your current datafile(s) location

SWITCH example (USERS tablespace)
----------------------------------------
Now let's assume we had an i/o error on the file(s) associated with the USERS tablespace and we need to restore ASAP.

SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
SWITCH TABLESPACE users TO COPY;
RECOVER TABLESPACE users;
SQL "ALTER TABLESPACE users ONLINE";

The above will redirect to the datafile copy, recover it, and bring it online so that the database operates with it.

Now, you probably want to redirect back to the original datafile location at some point. to do so, you would need to perform a new datafile copy backup in RMAN. Here, you would direct the copy to the ORIGINAL datafile location.

Here's an exmaple on how this would be done:
backup as copy datafile 'T:\ImageCopies\ORCL\ORCL_2575401446_USERS_8' format 'D:\oradata/ORCL\USERS_01.dbf';

After that run the same procedure outlined abovve to SWITCH the datafile. Oh, and assuming that all goes well, - take another backup!

Very busy TNSLSNR process

2008-02-26T08:15:00.000-08:00

I encountered a problem with a listener running on a windows machine that had a 10G Oracle database and companion installed in separate ORACLE_HOMES. The ons.log file was HUGE under the companion install. And the listener was consuming the CPU. I found some posts on OTN compaining of the same. It turned out that there was a config problem with the opmn process. Changing the ports in the ons.cnfig and in opmn.xml files solved the problem.

The errrs in the ons.log file were of the form:
Date Time Local connection 0,127.0.0.1,6100 missing form factor.

Database Managed Services - 2008 Strategy for ServerSide

2008-01-15T05:17:00.000-08:00

Over the coming year I am planning on extending my consulting services company into the area of managed services and support. More and more I am seeing companies that would like to bring these services on board to either supplement their existing staff or to bring these skills to the table for the first time. Let's face it - deep skills in database technology are both hard to come by AND expensive. Many small, medium, and even large companies would love to buy these services without having to commit to a large staff or a long term outsourcing agreement. I am in the process of drafting initial agreements for several of my current clients now and hope to extend the offer to new clients over the coming year.

If you have interest let me know.

paste

2007-12-20T03:44:00.000-08:00

Learned something new recently while having the task of manipulating large files from a data provider. You see, I needed to gather several years of financial information for several thousand companies (at the daily level). There were about 30 attributes so this process needed to be run about 30 times with 30 resulting files. After it was done it all needed to be loaded into a database. I could have loaded the files individually, but in the end all the data needed to be joined. I actually tried loading it all into the database and let the database doe the join, however these were such large data sets that the memory required to do such a join was larger than I had. I had a "wouldn't it be great" moment wondering if there was a way to join the files together in a streaming fashion. The order of the lineswas such that line 1 of each file could be joined together in a consistent way (the all belonged to the same security and the same date)

I was able to run the follwing command after placing all cvs files in a directory:

paste -d ',' *csv > all_data.csv

After that I had one monter file ready to load to the database - pre-joined and all!

A Real Post

2006-12-26T11:37:00.000-08:00

Thought I'd finally get a real post up here since it's almost been 6 months since the Blog was created! Just completed Christmas and back to work for the short (or is it the long) week.

I also had the honor today of submitting my estimated taxes. I'm currently working as an independent consultant under contract so I hadn't paid taxes yet this year. Letting the tax due accumulate like that let's it build to a point where you realize just how much you pay Uncle Sam over the course of the year. I just hope they put all that cash to good use. I still need to send in my Illinois taxes.