Oracle Application Express 5.1.1 on Exadata cluster

Our DBA team really likes using APEX to develop DBA applications. We are a pretty small team and we like to automate and standardize everything we do. We make a habit of using database tables to store our configuration information and we write scripts that read those DBA tables in order to support our business users.

We recently took control of a new Exadata cluster and of course we created a DBA repository database as we were creating the business databases. Since APEX 5.1.1 was just released, we decided to replace our old APEX system. That system was was a single instance running on a virtual machine and controlled by an outsourced administration team. We are a lot more comfortable having control over installation and maintenance of a system we depend on to get our work done.

The new Exadata is a four node RAC and our new DBA repository has an instance running on each node. We did not want to install software on the Exadata so that ruled out using Rest Data Services (aka APEX Listener). It also ruled out using Oracle HTTP Server with mod_plsql.

Since this APEX system is only used by our DBA team and is not exposed to outside networks we felt comfortable using the Embedded PL/SQL Gateway. However, the Oracle documentation contains this ominous note:”Oracle recommends that you do not select the Embedded PL/SQL Gateway option for Oracle RAC installations. Because the Embedded PL/SQL Gateway uses an HTTP Server built into the database instance, it does not take advantage of the Oracle RAC shared architecture.” (https://docs.oracle.com/database/apex-5.1/HTMIG/choosing-web-listener.htm#HTMIG370)

What to do? We decided to move forward with the Embedded PL/SQL Gateway and see how useable it would be. We could always spin up another virtual machine later if we needed to go the Rest Data Services route. I went through the steps in the APEX documentation to set up the Embedded PL/SQL Gateway. I could not get a response when I hit the database machine APEX URL that I had configured. I poked around on the servers and noticed that the XML db listeners were not running. I had forgotten that when I created the DBA database I intentionally did not install any options like Java and XML db. After installing XML db and restarting the database APEX started working. (https://docs.oracle.com/database/121/ADXDB/appaman.htm#ADXDB2700)

But that’s not all – here’s the real reason I wanted to blog about this. This APEX system is reachable using the scan listener! So that scary quote in the documentation should be ignored. We can connect using a URL like this: http://corpexa-scan.corp.com:8080/apex . No server names are required. So we CAN take advantage of RAC functionality.

This is just what we wanted so we are very satisfied with our new APEX system.

Advertisements

RAC and Grid Infrastructure Administration Accelerated Ed 3

This is a combination of two courses, “Oracle Grid Infrastructure 11g: Manage Clusterware” and “ASM  and Oracle Database 11g: RAC Administration”. The two courses are normally taught in five days and three days respectively.  This combination was taught in five days by extending the classroom time (8:30AM – 6:30PM). Knowledge  Services instructor Pat Frey taught the class in Columbus/Dublin OH. There is some overlap in the two courses so it’s not as bad as it sounds 🙂

There are lots of good labs in these courses and we did all of them except the overlapping parts. I was surprised that the virtual machines for our three node clusters worked so well. There were some performance issues and one student’s cluster became unresponsive on the last day.  Mine stayed up and I always finished the labs in about half the time other students took.

I couldn’t understand why it took the other students so long to work through the labs until I realized how much more experience I have with Oracle. For some of them it was their first exposure to Clusterware and RAC. I got the training materials the week before and one of the exercises actually solved an issue I was having on our Exadata RAC systems. Also, I have been using RAC since it first came out on 9i. Here’s a review of the RAC class I took back in 2003. So I think my experience and knowledge of Oracle in general made a huge difference in my ability to perform the lab work efficiently and quickly. I also asked a lot more questions for clarification than anyone else because I was able to see how the different features were relevant to production uptime and performance.

This was a very good course for me and I recommend it to anyone who already has a basic grounding in single-instance Oracle.

Exadata Database Machine Administration Workshop

I spent last week in downtown Chicago attending this class. It was a very good class and the instructor, Andy Fortunak was excellent. We had lots of labs to work through. The computers we used ran virtual database servers and virtual storage servers. This setup caused some problems, mainly slowness. My virtual database machine’s clusterware failed towards the end of the week but the virtual storage servers continued to work fine.

The main message was: invest the time needed to set up services and Database Resource Manager.  IO Resource Manager and Instance Caging depends on it.

We also discussed consolidation and using RAC one-node. We are consolidating scores of databases on Exadata. Most of them do not require multiple RAC nodes for workload but we do want high availability and load balancing. This week I tested manually moving one-node databases between nodes and it works great. The client does not get disconnected but does get an error message. Srvctl actually makes it a two node RAC and then stops the original node. I tested automatic failover of rac one-node and compared it with a full rac two-node with one instance down. They both work the way I wanted, a second instance starts up. I can’t see any technical reason for going with one-node.

Installing ASM/RAC without asmlib

I’m installing RAC (using ASM) on Red Hat Linux 6.2. Oracle has decided not to provide asmlib for Red Hat 6 and above. So I needed to figure out how to ensure correct permissions and ownership for the shared disks. Red Hat has an article on the support website which recommends using udev or multipath. I set up multipath on my VMware systems but it did not see any disks. VMware was not reporting the UUID’s of the disks to Linux. A little research on the VMware website gave me a clue, and I inserted this line into the vmx files:

disk.EnableUUID = “TRUE”

After rebooting, the multipath software could see the shared disks and automatically created the multipath devices.

RAC on AIX

I just finished the class: X0161 Oracle RAC on AIX Systems Workshop taught by Andrei Socoliuc of IBM Romania. Andrei knew a lot about Oracle RAC and AIX so it was a good class. I thought the best part was the hands on labs, especially the pre-installation preparation of the operating system. There was also a lot of good information on hardware and LPAR configuration. This is my first RAC class since 2003 when it had just been released, so the overview of RAC was a good way for me to get a refresher on RAC internals. My only complaint  is that we spent a lot of time on IBM’s shared disk solution (GPFS) and very little on Oracle’s ASM .

CRS-2800: Cannot start resource ‘ora.asm’ as it is already in the INTERMEDIATE state on server

I got this error when I was installing Oracle RAC 11.2 on Red Hat Enterprise Linux 5.6.  I was installing Clusterware using ASM on VMware shared disks.  When I created the independent persistent virtual disks, I left the “allocate all disk space now” option unselected. Oracleasm was happy on both RAC nodes. The Oracle installer was happy when it created +ASM1 on the first RAC node. But when the ASM instance started on node 2 it did not like the “virtually provisioned” disk. The +ASM2 instance was not open and was complaining about one of the shared disks being corrupt at a certain byte. When I checked the virtual disk files, I saw that Oracle was trying to read past the end of the file. I started all over with new fully allocated shared disk and that fixed the problem. Everything is up and running now.