This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.
A handy feature that I’ve just found out about is that there is a link from Metalink document 884522.1 to the Oracle Pre-Upgrade Utility. You can get the utility without downloading the entire installation set and unpacking it.
For me, the easiest way to use the utility is to invoke the non-XML version from SQL Plus. For example:
SQL> conn sys/<password>@<tns_entry> as sysdba SQL> spool utlu112_prereqs.out SQL> @@utlu112i .... SQL> spool off
The utility checks some interesting things:
If you did unpack the entire installation set, the utility script is in rdbms/admin.2 Comments | Filed in Oracle | Tags: 11g, upgrade
Starting with Oracle Database 11g Release 2, an event-based Scheduler job can be started based on the arrival of a file in a directory on the server. The file arrival event is raised by a new Scheduler object called a file watcher.
As per the docs: A file watcher defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.
Ittichai has an excellent example of using the file watcher. The following is similar example, but uses BLOBs instead of CLOBs. In this example, we want a Scheduler job to be triggered as soon as an image file is deposited into a directory on the database server. We want the job to be triggered once for each file. The job reads the image file and saves it into a BLOB column in a table. We will then use Oracle SQL Developer to retrieve the image from the table and view it in the built-in viewer.9 Comments | Filed in Oracle | Tags: 11g, dbms_scheduler
Just a quick note about the Oracle Database 11g Interactive Quick Reference That was released today to the public. This Interactive Quick Reference, created by Oracle University and the Server Technology Curriculum group, is your Essential Guide to Oracle Database 11g Release 2. This interactive quick reference includes an architecture diagram, a comprehensive list of background processes and an extensive list of DBA views organized by product feature areas.
Nicely done! Wish the columns in the DBA views had comments though.Comments Off on Oracle Database 11g Interactive Quick Reference | Filed in Oracle | Tags: 11g, reference
VirtualBox, Virtual PC, VMware Workstation and a few other software packages help you create and run multiple virtual machines on your desktop or laptop enabling you to run multiple operating systems simultaneously on a single computer.
My personal favorite is VirtualBox (recently acquired by Sun Microsystems), not only because it is freely available as Open Source Software and runs on Windows, Linux and Macintosh but also because it has great features and supports a large number of guest operating systems.
Last Saturday I used VirtualBox to create a new virtual machine (VM) running Windows XP Pro and Oracle Database 11gR1. First, I created a “base” VM with only Windows XP pro SP3 installed. I then detached the virtual disk file (VDI) from the VM. I ended up with a VDI file that I can clone as many times as I want eliminating the need to install a new operating system every time I create a new VM.
Cloning a virtual disk in VirtualBox is done using the command line. For example, to clone WindowsXP.vdi as a new virtual disk called WindowsXPProOraDB11gR1.vdi, you would issue this command:
VBoxManage clonevdi WindowsXP.vdi WindowsXPProOraDB11gR1.vdi
You would then assign WindowsXPProOraDB11gR1.vdi as the virtual hard drive of a new virtual machine.
The installation of Oracle Database 11gR1 on a new Windows XP VirtualBox VM on my laptop went very smoothly, but I had to troubleshoot a couple of things on the VM: A missing loopback adapter and a 100% CPU usage.
My laptop and the newly created VM do not have a static IP address. Dynamic Host Configuration Protocol (DHCP) is used to assign dynamic IP addresses on the network. According to the documentation, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local static IP address to that computer. I followed the instructions found in the Oracle Database Pre-installation Requirements document to install the loopback adapter. After that, the database installed without any issues.
Now that my WinXP VM and 11g Database are up and running, I noticed that the oracle.exe process was consuming 100% of the CPU. I waited a few minutes, but the CPU usage did not go down, it stayed at 100%. After a quick search, I found these two forum posts: post 1 and post 2. Since this is a test database, I did not hesitate to follow the instructions in post 2 and was able to bring the CPU usage down to a normal level.
Virtual machines are a great way to learn and try new software and applications without the need to buy new hardware or messing up your existing computer. You may even have fun embarking on some virtual adventures.
2 Comments | Filed in Oracle, Technology, Tips | Tags: 11g, virtual-pc, virtualbox, virual machine
Oracle Database 11g introduced a new feature called PL/Scope. A while back, I wrote about Dan Morgan’s experience when he compiled the package STANDARD for PL/Scope. I also wrote about Oracle’s answer, in which they said “…the reason to compile STANDARD would be to make its identifiers available in the new DBA_Identifiers view family (see PL/Scope). Our script to do this sadly missed the release. We’re about to post it on OTN.”
In fact, the PL/Scope documentation mentions a utlirplscope.sql script:
A database that has been upgraded from a release of Oracle Database that did not yet support PL/Scope will have no PL/Scope metadata—nor will a new Oracle Database 11g environment that has been ordinarily created. The DBA can rectify this by running the utlirplscope.sql script.
In an email, Bryn Llewellyn, Oracle’s PL/SQL Product Manager, was kind enough to point me to this page on OTN:
It turns out that a script is not needed after all:
Instead of simply providing a script to recompile STANDARD and DBMS_STANDARD for PL/Scope, we would first like to clear up some misconceptions about the usefulness of such a script and make sure you really do need to perform such an operation.
I suggest you read this OTN piece if you want to know the answers to the following questions:
Important notes to take from this:
In conclusion, I agree with Bryn that PL/Scope is a fine feature. Just get your DBA to read the OTN reference above if your use case would benefit from having STANDARD and DBMS_STANDARD identifier data.Comments Off on PL/Scope in Oracle Database 11g – Revisited | Filed in Oracle | Tags: 11g, pl/sql, plscope