Thursday, September 10, 2009
No time...
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...
Tuesday, February 10, 2009
Database Migration From Windows to Linux Using RMAN
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
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.
Monday, December 15, 2008
Enabling SSL for Oracle HTTP Server
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!
Sunday, December 7, 2008
FTP and SFTP on the Database...
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.
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
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
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!
Starting....
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…