Oracle at Work

No time...

2009-09-10T13:24:00.000-07:00

Hello there!

It's been quite a while since I post anything...It's been hard to find the time and energy that this blog deserves...but I should post something during the next weekend.

I have a couple of important issues that deserve a well written post..and hopefully will help the readers on similar issues.

Until now the "FTP and SFTP on the Database..." post has generated a lot of email requests and I'm sorry if sometimes I take a of lot of time responding...but well...free time is so hard to find! But until now, more later or more sooner, I've replied all the emails...and keep'em coming :)

Well...look out for a new post with some fresh Oracle knowledge in the next few days...

Until then...

Database Migration From Windows to Linux Using RMAN

2009-02-10T16:30:00.000-08:00

This task was on the "todo" pile for a awhile now and I had already tried it once. We have an old database on a Windows XP pretty close to kernel death and we wanted desperaly to deploy it on a Enterprise Linux without losing any data and if possible not have to create the schemas.
Due to time limits I had little success on the first attempt, although I now see that I was not that far off!

My second attempt was mainly trigged by a this post on Alejandro Vargas' Blog. His guide is based on the RMAN command CONVERT DATABASE .

It worked out great for my case, but you can find alternatives (Metalink ID: 733205.1):

Export / Import to include the use of Datapump facilities. all versions support Export/Import but for Datapump 10.1.0.2 or higher is required
Transportable Tablespaces 10G or Later
RMAN Convert Database functions. 10G or Later

The third has the advantage of being multi-schema, that it is to say, that you don't need to create the users on the new database, which was a task requirement on my case.

Alejandro Vargas' post is pretty good and straigh forward. RMAN allows you to generated converted datafiles, along with a new control file and a transport script. These files can be then sent to the the new machine and with some editing on the script and control file, the new instance is created with the all the data from the previous one.

The most touble you may find is edting the transport script. In my case I couldn't understand why the script wouldn't upload the users datafile, along with the users. I later understood: Oracle was pretty much foling Oracle!!

Any way, Alejandro's receipe is a great help, and based on the Metalinks, it gave me the extra information I just need to complete this important task.

Any issue feel free to email me.

Enabling SSL for Oracle HTTP Server

2008-12-15T09:27:00.000-08:00

Enabling SSL for the HTTP server is a relative simple task, and the essential steps are described next.

1) Edit the "opmn.xml" file (don't forget to make a backup copy). You can edit this file via the OEM or manually (via shell).
Access the Oracle Entreprise Manager (http://your_server:1810) and at the bottom of the page, click the link that reads "Process Management".
In the "opmn.xml" file, change the value for the "HTTP_Server" "start-mode" to "ssl-enabled" and click Apply.

2)Edit the "ssl.conf" file.
This file is accessible via the OEM on the HTTP_Server > Administration > Advanced Server Properties > ssl.conf

Just above the closing tag, enter the following directives:

RewriteEngine on

RewriteOptions inherit

In this same file find the line that reads "#SSLVerifyClient require", and uncomment it (delete the "#").

Finally change the "SSLWallet file" value to $ORACLE_HOME/oca/wallet/ssl and click the Apply button after finishing your edit.

3) Reset the OCA SSL Wallet password.

Run the following command on the linux shell:

$ORACLE_HOME/oca/bin/./ocactl setpasswd -type CASSL

Enter the OCA administrators password.

Enter the new wallet password.

Confirm the new wallet password.

4) Edit the Wallet.

Now that you have the wallet's password open it using the Oracle Wallet Manager and insert as a trusted certificate the CA certificate you created.

5) Import your user certificate to Firefox/IE.
You can now access your website through a secure channel and using user authentication via digital certificate.

Note: this post assumes that you have successfully create a CA certificate and a user certificate signed by the CA. I recommend using OpenSSL. In the next few days I'll post on OpenSSL focusing on this issue. Feedback is more than welcomed!

FTP and SFTP on the Database...

2008-12-07T04:06:00.000-08:00

When designing your application with an Oracle database, you may find more productive to trigger FTP jobs from inside the database itself.

If you rely on simple FTP protocol, you can use two main approaches:

Build a Java procedure to run shell scripts (with a PL/SQL wrapper), like the one available here, and invoke a third party FTP software. This option has the advantage of less code to manage, but of course, you will have to depend on third party software, which depending on your application's context may be an unwanted feature.
Develop a pure PL/SQL package to send and receive files. Using the UTL_TCP and UTL_FILE packages, you can easily implemente a FTP API. Even easier is using an already implemented API, like the one available on Oracle-Base and the XUTL_FTP package. You may find more examples throughout the Oracle Bloggers or on the OTN network.

Now, if your application is evolving, and you now wish to use SFTP directly from the database, the PL/SQL native packages don't allow you to implemente a SFTP API, like the FTP APIs shown above.

First of all, do not confuse SFTP with FTPS. SFTP is based on the SSH protocol, and is called SFTP simply merely to "marketing techniques" by the SSH group. FTPS is the secure version of the original FTP protocol.

In my case I was called to implement a SFTP API for the database. Well, the lack of PL/SQL infrastructure to do so I turned to JAVA! Using the ChannelSFTP class of the JSch package, I easily implemented a simple API to send and receive files via SFTP.
Finally with the .jar deploy I loaded my package to the Oracle database (after loading the JSch jar and the Jzlib jar) and build the PL/SQL wrappers.

The first battery of tests demonstrated: success! SFTP from the database!
In conclusion, Oracle's architecture allows multiple solutions to a problem, and with Java there's always a way out!
If you want the code for the Java SFTP API or/and PL/SQL wrappers fell free to email me.

Using the UTL_DBWS Package

2008-12-07T04:03:00.001-08:00

In my previous post, I mentioned the UTL_DBWS package, that provides a ready to use method to consume web services from the database itself, using pure PL/SQL.

Although you only need PL/SQL to consume, the package itself is based on a couple of java classes.

If you’re using a 10.1 or 10.2 database, the PL/SQL code is already installed, but the Java package isn’t! This will cause an error when using the package to establish a connection to the web services, complaying that a particular Java class does not exist

To correct this, get the dbws callout utility. You can download it here.

Then, apply it to the database using the following commands:

loadjava -u user/userpass -r -v -f -s -grant public -noverify -genmissing dbwsa.jar

loadjava -u user/userpass -r -v -f -s -grant public -noverify -genmissing dbwsclient.jar

After this, the PL/SQL package should now function correctly.

Here are a couple of web services to test your code, if do not have any one available:

Consuming Web Services using PL/SQL

2008-12-07T04:02:00.001-08:00

For the last couple of days..I’ve been studying how to consume web services within the Oracle database using PL/SQL..

A quick glance at my personal library (Google!)…led me to conclude that there are two major ways to do this:

Build your own SOAP API using the UTL_HTTP package for the HTTP requests (mandatory for anyone using 9i or older) ;
Use the UTL_DBWS package available in 10g that provides all the essential methods to consume web services

In my particular case I’m building my own API, solely because I couldn’t consume the desired web service via HTTPS.

I lost a lot of time searching how to consume a web service over HTTPS using the UTL_DBWS package, with little success, and frustrated I decided to go with the first solution.

Using the UTL_HTTP package with a Oracle Wallet, I successfully consumed web service over HTTPS with little effort. This was achieved using the following procedure, before the HTTP request:

utl_http.SET_WALLET(your_wallet_path, your_wallet_pass);

Pretty simple and straight forward…

I’ll post more related code tomorrow from my PC at work..

Meanwhile I started working on FTP and SFTP (not confuse with FTPS) from the database, which I’ll post some examples in the next days!

Any doubts or feedback be sure to comment or send me an e-mail!

1 Comment

Starting....

2008-12-07T04:01:00.000-08:00

Hi there…

For the last few months, I’ve working as ORACLE developer and doing some DBA related tasks….

Since I’ve come across some interesting issues…often not exposed on the web as they should…simply not concise enough…or perhaps to serve as reference to future developers…since I have found personal blogs about Oracle to be very useful…

So…I’m gonna start my own blog…to keep as a referrence to myself…and hopefully to help others…