Tom Kyte's DBA Resolutions for 2009

Read Tom Kyte's DBA Resolutions for 2009. I liked it and you will too. It's available at http://www.oracle.com/newsletters/information-indepth/database-insider/jan-09/kyte.html?msgid=7295775

Configuring Oracle APEX on Port 80 (with Embedded PL/SQL Gateway) in XE database

You can use the following instructions to configure Oracle APEX on Port 80 in Oracle XE database. The default port is port 8080 but if you want it change to port 80 instead use the following steps.

Port numbers less than 1024 are reserved for use by privileged processes on many operating systems. To enable the XML DB HTTP listener on a port less than 1024 such as port 80 your DBA must do the following:

1. (UNIX only) Use this shell command to ensure that the owner and group of executable file tnslsnr are root:

$ chown root:root $ORACLE_HOME/bin/tnslsnr

2. (UNIX only) Add the following entry to the listener file, LISTENER.ora, where hostname is your host name.

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 80))
(PROTOCOL_STACK = (PRESENTATION = HTTP) (SESSION = RAW))
)

3. (UNIX only) Stop, then restart the listener, using the following shell commands, where user_id and group_id are your UNIX user and group identifiers, respectively:

$ lsnrctl stop
$ tnslsnr LISTENER -user user_id -group group_id &

Use the ampersand (&), to execute the second command in the background. Do not use lsnrctl start to start the listener.

4. Use PL/SQL procedure DBMS_XDB.sethtpport with SYS as SYSDBA to setthe HTTP port number to 80 in the Oracle XML DB configurationfile /xdbconfig.xml.

SQL> exec DBMS_XDB.setHTTPPort(80);

5. Force the database to reregister with the listener, using this SQL statement:

SQL> ALTER SYSTEM REGISTER;

6. Check that the listener is correctly configured:

$ lsnrctl status

6. Access Oracle APEX by giving the following URL. You do not need to specify the port number explicitly as port 80 is the default port.

 

http://hostname/apex

 

 

Reference: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369.pdf Page 795

 

 

Step-by-step guide to install Oracle Application Express on Oracle XE Database on Windows

Oracle Application Express - one of the hidden jewels of Oracle is a rapid web application development tool. APEX uses only a web browser to develop, deploy and administer professional applications that are fast and secure. APEX combines the qualities of a personal database, productivity, ease of use and flexibility with qualities of an enterprise database, security, integrity, scalability, availability, and built for the web. 

APEX lives completely within Oracle database and is also available with Oracle Express Edition database which is a free product from Oracle.With Oracle XE & 11g APEX has no footprint at the operating system level and that makes it an extremely simple architecture to use. Using APEX applications can be deployed on a desktop to start with and can be scaled up very easily. 

Try Oracle Application Express - It's easy to develop, easy to deploy & easy to administer. Check out this step-by-step guide to install Oracle Application Express on Oracle XE database on Windows platform.

Step-by-step guide to install Oracle APEX on XE on Windows - Upload a Document to Scribd

Read this document on Scribd: Step-by-step guide to install Oracle APEX on XE on Windows

Automate DBAs automate !

One of the most time-consuming (and can I say mundane, boring, de-moralizing ) tasks of a DBA is to implement change requests i.e. running scripts, copying files, promoting changes etc. in various databases (test, uat, production). There are organizations where many DBAs are dedicated just for doing such tasks.

However the good news is with little bit of time & effort majority of such tasks can be automated.

Let me give you a case study.

When I was working for Panasonic, we had a team of 3-4 DBAs looking after 50 Oracle Databases & 4 Oracle Applications 11i systems (where each 11i system further had its own 3-4 environments - Production, Test, UAT, CRP, Development etc.). Developers were given full control to development environment and anything beyond that was restricted except for DBAs because of security reasons. Which in other words meant that If any change was to be done to other environments it was DBAs' responsibility. Developers used to raise change requests (for production environments) or send us e-mail (for non-production environments).

The kind of requests that we DBAs used to receive were as follows

a. Running of SQL scripts files
b. FTP files to and from server (forms, reports, sqls) among different environments.
c. Provide the latest procedure/function/package codes from database
d. Spool the table data and provide output

The volume of such requests required one full time DBA.

Most of these requests (except for some) needed no DBA expertise and was kind of operator work. E.g. ftp files from one location to another location or provide the latest source code from database, or provide the current forms (fmb) or report file (rdf). And apart from the fact that these tasks needed no DBA expertise, there was a lot of dependency on the DBAs, and waiting time for DBAs to do the work.

Seeing no value addition in such tasks, we decided to automate the whole process and came up with an e-mail workflow system. For example the new system was such that whenever developers needed to ftp files to and from server, they will send the mail directly to server (for test instance) and will send mail to their supervisors (for UAT and Production instance) in a pre-defined format. For UAT & Production instance If supervisor is okay with the requests, he will forward the mail to the server and will add 'APPROVED' keyword in the subject. A cronjob was setup on the server to check the mails every few minutes. Once there was a new mail, a shell script used to analyze the mails contents and then take appropriate action. Care was taken to ensure that only valid requests from authorized persons are entertained (E.g. the mail should be from specific Ids only or that the subject of the mail contains the keyword 'APPROVED' ). Once all the conditions were satisfied, the server used to execute the task and send confirmation mail back to the developers, their approvers and DBAs. Here is a flowchart for this






Like this we automated couple of other routine tasks also.

With this simple automation which probably did not take more than 10 man days to develop we were able to save the work of one fulltime DBA and divert our attention to more meaningful tasks where we could add value. For developers & end-users this also reduced the waiting-time & dependency on DBAs.

One simple automation greatly enhanced the efficiency and morale of our team.

I really feel sorry when I see DBAs doing such kind of manual work which can very easily be automated. Automating does need time and effort in the beginning but the rewards are simply great. Then there are tools like Oracle Application Express (APEX) a web-based rapid application development tool which can be used to do such simple automations. And guess what - Oracle APEX is free.

So DBAs don't work like an operator. Be a DBA in the true sense and do justice - to yourself and your organization.

Happy automating !!!
Ashish Agarwal

Build your own Oracle test lab

For any IT professional continuous learning is must as the technology always changes so fast.

I started my career as an Oracle DBA / Oracle Apps DBA in 1998 and fell in love with Oracle RDBMS and other Oracle technology products. Trying out new product features, new releases, doing all kind of R&D has always been one of my favourite pastime activity.

This blog is just to give you an idea on how can you have your own Oracle test lab.

You need the following to build your own test lab

1. A good speed PC (my test lab consists of a laptop with 2.2 Ghz dual core processor & 2 GB RAM running Windows Vista). 2 GB will give you a decent performance if you intend to use Real Application Clusters (RAC).

2. An external USB Hard Disk (Recommended) (I got an portable external Maxtor 80GB Hard disk).

3. VMWare Workstation & Player (Can be downloaded from Internet. There is 30 days evaluation copy for VMWare Workstation. VMWare Player is free).

4. Enterprise Linux (Can be downloaded from Internet).

5. Oracle products that you are interested in (Can be downloaded from Internet)

(Links to all the downloads are given at the bottom of this article.)

Install VMWare Workstation on your PC and create the virtual machine. If you have got an external hard drive, specify the location of virtual machine to be your external hard drive. The advantage of storing all the virtual machines on a separate external hard disk (other than your PC's main hard disk) is that virtual machines are very heavy I/O bound (Other than virtual machine files VMWare also creates memory files/paging files in the same location). If you store these virtual machines on the main disk of your PC, it may slow down the entire PC because of I/O.

(For those who are not aware of virtual machine concept, a very common definition of virtual machine is "turning hardware into software". The virtual machines are stored as files on your PC's main operating system.)

Start the virtual machine and install Linux operating system. Once linux is installed, shutdown the virtual machine and make a copy of the files. This copy can later be used to create more virtual machines there by saving you from the work of installing Linux again.

(As the virtual machines are just operating system files on your main operating system, removing these machines is as simple as deleting those files - meaning it does not mess with windows registry and leave any traces whatsoever.)

Once Linux is installed, starting this virtual machine will start linux and will take you to the Linux logon screen. From this point onwards it is as good as you are working on a linux machine.

Just to give you an example of how useful it can be, in my case I created the following virtual machines in my test lab

1. dbsrv :- Where I installed the following Oracle products
-> Oracle Database 10g
-> Oracle Application Express
-> Oracle Grid Control
-> Oracle Internet Directory
-> Oracle Database 11g
-> Oracle XE
2. rac1 :- Oracle RDBMS 10g with ASM & RAC Node 1
3. rac2 :- Oracle RDBMS 10g with ASM & RAC Node 2
4. bkpsrv :- For Standby Database (Physical & Logical), Recovery catalog, RMAN Testing

Depending on what I intend to work on, I can start only the required virtual machine.

Having these machines on separate disk has given me a very decent performance even when I use Real Application Clusters or Oracle Grid Control.

Here are some useful links which will help you in building your own Oracle test lab

1. To see a demo on understanding virtualization
http://download3.vmware.com/media/infrastructure_optimization/VMware_IO.html

2. To download VMWare Workstation
http://www.vmware.com/download/ws/eval.html
You'll need to register to get 30 days evaluation license. Once you have created your virtual machine and after the expiry of your 30 days evaluation license you can continue using the virtual machines using VMWare Player which is free.

3. To download Linux
http://edelivery.oracle.com/linux

4. To download Oracle Products
http://otn.oracle.com

Do post your comments and/or let me know if you need more information.

Happy testing !
Ashish Agarwal - PMP, OCM & OCP

Preparing for Oracle Certified Master (OCM) Practicum

I have been receiving lots of queries on how to go about doing Oracle Certified Master (OCM) and so I just thought about blogging it here. Though I won't be able to disclose or write explicitly about the exam (as that might result into my OCM credentials being revoked), I would like to give you my personal suggestions on how to go about it. (Do note that I took OCM in Oracle 9i. Please refer to the URL http://education.oracle.com/ -> Certification -> Select Database Administrator Certified Master under the Oracle 9i or 10 to validate that the information mentioned below is still valid)

To become an OCM you need to satisfy the following

1. You need to be an OCP

2. You need to do 2 advanced courses from Oracle University. Refer to URL http://education.oracle.com/ -> Certification -> Select Database Administrator Certified Master under the Oracle 9i or 10 to get the list of advanced courses.)

Once you have cleared the above two criteria, the following suggestions will be helpful in preparing.

1. Refer to the OCM Practicum syllabus thoroughly. All the scenarios in the exam will follow the syllabus in sequence.

2. Oracle's Documentation CD is enough for the preparation. The whole documentation set will be available to you during the exam also, but then you will hardly have time to refer to those documents. So have a fair idea on how to browse through Oracle's documentation, and where to find the relevant information.

3. Have the OCM Practicum environment on your PC. Practice each and every scenario as per syllabus at least couple of times. E.g. if standby databases is one of the topic, create standby databases, validate that the standby database is working properly, do switchover & failover. And do it multiple times to be able to do it right the first time in the exam.

4. During the exam you will be hard pressed for time. There will be 100 scenarios and you will have time to complete 50 only.

5. Do practice backup & recovery thoroughly. Simulate all the crash scenarios and recover your databases. In OCM practicum you are treated like a DBA managing a critical production instance and you should be able to recover from every imaginable disaster scenarios.

6. As far as possible use "Oracle Enterprise Manager" to do things. You can use OEM during the exam except for a couple of things. It will be faster than typing commands and also less error-prone.

You can also refer to the following sites for more information

1. URL "http://www.oracle.com/technology/oramag/oracle/04-mar/o24tech_ocp.html" gives you a good idea on how to go about taking OCM

2. URL http://education.oracle.com/ -> Certification -> Select Database Administrator Certified Master under the Oracle 9i or 10

All the best !!!

Ashish Agarwal - PMP, OCM & OCP