Welcome to the Blog of Mike Ross

September 20, 2011

Dewey and 11g Update

The other day I got a bit of time and decided to create the DAD necessary to test PL/SQL from the 11g test database on Dewey. This required getting up early and restarting the webserver, but all went well and now I've got a Dewey DAD to use for testing PL/SQL.

So far, it does appear that the fine grained access control is not active on the 11g upgraded database. This is really good news as it allows for "only" the upgrade to be done at one time, then we can apply the fine grained access control at a separate time. The upgrade process itself will require most of a night to complete.

We need further testing on the FGA control, then I need to get my notes together and document all the things that require special attention before and after the upgrade. Things such as .dbf file location, Mike Huber's "special" items in the listener - and in the old Oracle Home, backup folder locations, database links, directory objects, and so on. The upgrade process tends to leave a lot of things in the 10g Oracle Home, so I want them all moved over under the 11g Oracle Home to keep it all nice and neat.

Posted by rossm at 3:17 PM | Comments (0)

August 24, 2011

Solaris Parameters Set, More Juice for Aero

Last night Dave and I increased the RAM on our Solaris LDOM which serves Aero to 32G. Dave had previously upped the CPUs to 32 as well. We configured a project to allow the Oracle user no more than 20G of ram. At this level we were able to open the database with a max SGA setting of 12G and 250 processes. Anything higher would result in a memory error as Oracle was trying to grab more than its allotted 20G.

All in all a successful upgrade of LDOM resources and increase of Aero's SGA.

Posted by rossm at 11:38 AM | Comments (0)

August 17, 2011

A Couple Oracle Things: Tnsnames.ora rewrite, Solaris OS

A couple Oracle things came up this week. From somewhere, a massively ugly tnsnames.ora file was put into place on ts4. After an hour or so of fiddling around we got all the connection issues fixed, but that file needed some attention. Michael asked me to clean it up, and I enlisted Mike Soukup to help with the Banner connections. There are quite a few outdated connection entries, and there were a ton of connections for other campuses - from the new data warehouse models, I think. Anyway, these files are so touchy - I could see not one thing wrong with the ora.wou.edu entry, but it would not work. I cut and pasted it in again and it worked. Such has been my experience with the tnsnames entries in the past. Once they work, then you are golden, but they can sometimes be a real pain.

A second Oracle item is Dave and I are working on fixing the Solaris setup on ora. Somehow it isn't set up quite right, and is causing problems under heavy loading. We are working on testing Dewey's setup, then we'll put a good fix onto ora. Also, Dave is going to give us some more RAM so that we can increase the SGA on Aero. We'll do everything at once.

Posted by rossm at 3:10 PM | Comments (0)

August 12, 2011

Database 11g Update and a Few Other Things

The 11g updated version of our production database is running on our test server now. I did discover one interesting thing - the fine grained access control does not kick in upon upgrade. By this I mean, it is something that can be implemented after the upgrade process. This is pretty significant - it is always better to divide a large project into as many smaller steps as possible. I need to test this further, but if this is indeed the case, we are pretty close to upgrading the production db. However, I'm waiting until next week when Dave is back before rocking the boat - just in case! When he gets back I'm going to install the 11g binaries on our production server and get that all patched up and ready. Oh yes, first we have to fix the OS kernel parameters and make sure that box is ready for any load we can throw at it.

A couple other items - Michael has suggested we look at an Oracle install on the "unbreakable" unix platform - rather than the SPARC Solaris platform. I think there are a lot of good reasons to move in this direction, so am very excited to pursue this option. The Solaris platform is good, but it feels like it is losing ground to the vanilla unix OS'es, and who knows what plans Oracle has for Solaris in the future, if any.

The other database issue is that Aero is once again calling for increased SGA size. Currently, it is set at 8 GB, which is quite a bit larger than it was on our previous server. OEM is recommending an increase to 14 G, which would require the addition of some more RAM on the LDOM to make this work. Not sure why it's wanting so darn much more RAM, over our previous production db. We do run a lot of large, repeating jobs pretty much all day long - way more than we used to. But I don't think that alone accounts for the need for more SGA space.

Posted by rossm at 10:49 AM | Comments (0)

August 2, 2011

OEM Restarted on Aero

Well, last week was not fun in the database world of WOU. At first great progress was being made, and a running copy of Aero was actually upgraded to 11gR2. That part was good. However, after doing the upgrade to the Aero copy a lot of "fixing" had to be done. So, of course I started up OEM to help me see how the database was doing. It was here where disaster struck. I shut down the database using OEM on the copy. Unfortunately, the real database got shut down as well. This is bad, but not terrible. I logged on to the server, opened sqlplus, and restarted the production database. It started just fine, so after some testing and watching, I left for lunch - no problem, right? Wrong! Michael called me at lunch - the production server was "hung". After much searching and grunting we concluded that the production server could not handle the backlog caused from the earlier shutdown. All of the OS processes were being used, then the server would hang, then it would work fine for 20 minutes, then it would hang again. After trying everything we could think of Dave added some more processors to the LDOM and the problem went away.

It now looks like the underlying problem is incorrectly set OS parameters. Dave is on vacation until the 12th, so when he gets back we will see if those parameters can be improved upon.

Not a happy situation. However, I just now restarted OEM on the production db and that is working again (too chicken to try it earlier!) - so much easier than monitoring by hand!

Posted by rossm at 3:40 PM | Comments (0)

July 22, 2011

Test Instance of 11gR2 Up and Running

This week I had the opportunity to work on our Oracle production test instance, which is a copy of our production database. I was able to open the test instance, which is version 10gR2. Then an 11g Oracle home was created and the 11gR2 binaries installed. At this point, I was ready to prepare for the upgrade.

There are quite a few steps involved getting ready for the 11g upgrade - I chose the DBUA (Database Upgrade Assistant) path, Oracle article id 870814.1. Oracle strongly suggests that you remove all invalid objects from the db before upgrade. How nice. They obviously haven't seen our production db! The real point here is that there are no invalid objects due to incorrect db setup. Practically all of our invalid objects are abandoned procedures and packages, but there are lots of them scattered throughout the db. There are no invalid objects due to incorrect db setup issues, so I deemed this one "good enough", although I did run utlrp.sql in an attempt to recompile as many invalid objects as possible.

Next issue is TimeZone versions. This is a very confusing area as the main DBUA article says you "MUST" upgrade the TZ files following an upgrade if you have less than version 11 (we have version 4 so you'd think we better be on top of this one). Alas, no. An entirely different Oracle article focusing strictly on time zone versions says that the 11gR2 db comes with about 10 different versions, and will just use the newest one. So, it says, "DO NOT" modify the TimeZone files. I did not. However, the "test" is this select statement: SELECT version FROM v$timezone_file; which still returns version 4 on the test instance. Looks like the DBUA instructions might be the ones to follow here....further study required.

Two more items must be attended to right before running the upgrade. Here are the commands:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PURGE DBA_RECYCLEBIN;

First, you want to gather stats on all the objects before the upgrade. This saves down time after the upgrade (both of these commands are done with the db open). Second, right before upgrade you need to purge the recycle bin - the upgrade can't deal with any objects being in here.

This next item is where I got stumped upgrading to 11g - trying to move servers and upgrade all at once just is not a good idea. Way too much room for errors, then you would have a heck of a time figuring out what went wrong. Anyway, the 11g move to network acls is one thing I'm having trouble getting going. There are about 45 schemas on our production db that will have issues with these network acls. The pre-upgrade tool I ran identifies the schemas, but not individual objects, so it is unknown exactly how many objects (mostly packages) will break under this new regimen. It is also unknown, as of today anyway, exactly how to fix them. Obviously this must be figured out before attempting the actual upgrade.

Since this is just a test instance, at this point I fired off the DBUA. Other than taking about 5 hours to complete, this part was pretty much "click and wait". It really had no issues to deal with at all during the actual upgrade.

Following the upgrade, upon opening the sqlplus from the 11g directory, voila - 11g db up and running. There are a bunch of post upgrade issues that need to be dealt with. This blog is getting too long - I'll write about those later.

Posted by rossm at 10:14 AM | Comments (0)

July 13, 2011

Oracle Database Security

Today I had an opportunity to check out some of the Oracle database security products. There are quite a few of them, but these are some of the main applications:

Data Vault: This allows for fine grained control of data access in the database. It's primary function is to protect sensitive data from even high level database administrators with DBA privileges - even SYS and SYSMAN. The idea is that the management of the database is separated from direct access to the sensitive data in the database. Sometimes there have been problems where the high level database managers have abused their position, and accessed sensitive data for their own reasons. This would be a bit of a pain to implement.

Advanced Security - Database Encryption: Oracle 11g database encryption is a definite step up. You can use Enterprise Manager to encrypt table columns, an entire tablespace, and even such things as your daily backups. This protects you from direct OS file stealing, theft of backup tapes, and similar threats. It uses a two layer encryption algorythm which I had not seen before. There is an encryption key stored outside the database (in an Oracle wallet for example), and a second encryption key made randomly by you for each table, which is a series of up to 70 random characters. To decrypt the information, you need both keys. I thought encrypting a whole tablespace might be too much overhead, but that encrypting certain columns containing sensitive data made a lot of sense. Oh, also, you keep one encryption key outside the database so it is not included in any of your daily backups.

Data Masking: This is not too useful for anything I could think of in our situation, but the idea is that sensitive data is converted to irretrievable gibberish. This is used in situations where you might give your data to third parties but they don't need the real values of the sensitive data to do their work.

We will be adding Advanced Security at least to our production databases.

Posted by rossm at 3:36 PM | Comments (0)

April 14, 2011

Dev10g Move

Dev10g is our local Oracle database that is maintained to run two applications: Computrition and Viking. These applications can not live on our main database for various reasons, so an older 10.1 version is maintained just for them. The server upon which dev10g sits has aged, so this db must be moved. Dave has created a new ldom for this purpose, and the plan is to move dev10g as soon as possible.

After much searching and cursing, the binaries for 10.1.0.2 were located along with the patch up to 10.1.0.4. A test install was made onto the new server. The install completed, but threw a bunch of errors right at the end. There is something about the Oracle user on the new server that throws permissions errors not seen on other servers. Unfortunately, Dave, guru of everything Unix, has been out this week, so this piece is stuck for now.

Early this morning, the real dev10g was shut down and necessary copies were extracted to the new server. This will permit a test startup once the permissions issue is solved and the db can be reinstalled.

Long term plan is to successfully start up the test instance on the new server and note all the (inevitable) problems which arise. Fix those problems, keeping a log of how/what happened. Remove this test instance, then do the real move.

The webserver will need some config modification, and a restart as well.

That should do it!

Posted by rossm at 9:04 AM | Comments (0)

December 9, 2010

Movin' On

Well, it's time to bid farewell to Sundown, our workhorse Sun server that houses the production Oracle database and many other critical services for campus. I kind of hate to see it go - it has been an extremely reliable platform with plenty of horsepower and a couple of humongous storage arrays hooked to it. But, as everyone knows, nothing stays put for long in the computing world. So, Sundown is going to be replaced with a Solaris LDOM (much like a Windows VM) - actually, two LDOM's.

Michael Ellis and I have been work, work, working on Aero to get it ready to move. This included such things as cleaning up old schemas, removing unused tablespaces, and general db housekeeping. It is now in pretty good shape.

The process for moving an Oracle db from one server to another is a pretty good challenge. Luckily for us, we are maintaining the same OS (Solaris 10, 64 bit), and we have configured the new LDOM to mirror the existing file structure. I installed and patched the same software version onto the LDOM, so now we just need to move the database.

I have gained a better appreciation for the difference between the "binaries" of an Oracle install, and the "database". The binaries includes all of the software (Explained to me as the Car. The database is the Gas.) So we have this Car sitting there on the LDOM, which for all intents and purposes is a mirror of our existing production database. Here's how we fill 'er up, then start the engine.

First, shutdown the existing db with shutdown immediate (not abort). Then copy the database files, control files, and redo files over to the LDOM. Tweak the initaero.ora, listener config, shell (environment variables), tnsnames.ora, and probably one or two more files to reflect the new server's name.

Once all this is in place, we "startup mount pfile=path/initaero.ora" and see what happens. In practice we have always forgotten a parameter or two, or something, but the db will tell you why it can't mount. You fix the problem, then try again. Pretty soon, the db will mount. Then, it's "alter database open". With a little luck, what you now have is the production db sitting on your new server.

This is one of those situations in life where you can describe the process pretty easily. However, actually making *everything* work upon the move is way more complicated. The production db has tons of bells and whistles, special cases, and add-ons that have been configured over time. Each of these needs special attention.

Stay tuned for the movement of the HTTP Server (thanks Ron), and the process for moving the dictionary objects to a new file system.

Posted by rossm at 10:37 AM | Comments (0)

Moving Objects To New Tablespace

Today I took a bit of time to clean up a messed up tablespace on Aero. A while back Dale and I reorganized a tablespace to make it locally managed with automatic (as opposed to manual) segment management.

This is an automated process, and the database renames the tablespace from myspace to myspace_reorg0. Well, we forgot to modify the schema owner's properties to default to the new tablespace. End result = all new objects went right back in to the original tablespace.

My plan is to now fix the schema properties to default to the auto tablespace, then move all objects into the new tablespace. This is a pretty straightforward process for "normal" objects such as tables and indexes. A command such as this does the trick, and takes less than a second for most reasonably sized objects:

ALTER TABLE [TABLENAME] MOVE TABLESPACE [TABLESPACE NAME];

or

ALTER INDEX [INDEX NAME] REBUILD TABLESPACE [NEW TABLESPACE NAME];

Well, that's fine, but this particular tablespace also contains a bunch of these:

MYSPACE.SYS_LOB0944542804C48302 - LOBSEGMENT

Ya, dang blobs and clobs everywhere - all segments stored in the wrong tablespace. Well, this can also be fixed, but first you have to know the tables to which these lobsegments belong. I haven't yet figured out a good way to identify the table-lobsegment relationship out of the data dictionary. Instead, I just looked through the schema for tables with blob or clob columns in them. Once located, this is the syntax for moving the lobsegment to the correct tablespace:

ALTER TABLE myspace.test_lobtable
MOVE LOB(blobclobcolumnnamehere) STORE AS (
TABLESPACE myspace_reorg0
)

The second complication is that the tablespace contains a bunch of these, too:

MYSPACE.BIN$bp8vY4FWOlfgRAADunpu8Q==$0 - TABLE

Unless someone is a naming putz, this is a flashback/recycle bin object. You can get rid of these (theoretically) with a command like this:

PURGE TABLESPACE MYSPACE;

Unfortunately, this didn't remove any of these objects - they are apparently no longer recognized by the database - I'm really not sure why it doesn't remove them. It just doesn't.

Once everything except these crazy-named objects are all moved, the next step is to take the original myspace tablespace offline, and see what happens/breaks, if anything.
Assuming nothing breaks, then the myspace tablespace can be dropped, and the myspace_reorg0 with the correct segment management can be used.

Posted by rossm at 10:14 AM | Comments (0)

May 14, 2010

Cleaning Up

Well, it was finally long enough since the last time I tried cleaning up users/schemas and tablespaces that I forgot what a miserable experience it is. So, all smiley and happy, I tore into some old and long-forgotten schemas and tablespaces on AERO - a real Spring cleaning. Ring,ring (it's the phone). "Do you know anything about the kiosks in the libary?" - um, no, why? "They quit working." oh,oh. Now I remember why clean up is a dirty word!

Well, after a couple hours, much help from Michael, and a few lucky breaks, the dropped user was replaced, the missing package was replaced, and the kiosks worked once again. The package has been moved to a more suitable schema, but now I'm gun shy and have quit deleting stuff!

Posted by rossm at 2:34 PM | Comments (0)

September 30, 2009

Oracle Database Gets Confused, Then Fixed

On September 9th Sundown crashed and was rapidly repaired by our highly competent staff. However, about a week later I received a call from John Rushing - he could not get his (previously just fine) file i/o application to work. The error he received was "illegal file operation" - not too helpful. The next thing I know Mike Soukup is on the phone. His file i/o application also quit working - "illegal file operation". Well, obviously something had gone terribly bad with the file system when the server crashed. It was possible to log in to the server as the Oracle user and view all directories and files - even write to the files. Dave said that something was corrupted on the Oracle database, but obviously it was something corrupted on the file system! After all, I had even bounced the db without so much as a whimper or an alert log. Well...... no. After several days worth of moving files, creating new directory objects and a host of other "tweaks", I noticed that the alert_aero.log had some recurring errors in it. Researching that error (it was arcane) resulted in advice from Oracle to rebuild the entire SYS schema on AERO due to probable corruption of the schema and/or the data dictionary. Oh.

So, one night Michael Ellis, Ron and I set about rebuilding the entire SYS schema and, sure enough, the file i/o problems disappeared. Let that be a lesson to you, uh, to me - listen to Dave! By the way, I had opened an SR with Oracle for this issue, but this time they were unhelpful. Sometimes an SR is so very helpful, and sometimes it is a waste of computer ink.

I did actually learn quite a bit about creating dictionary objects on the database. The old way (which is what I was still using) was to create a long list of dictionary paths in an initialization parameter called utl_file_dir. Each change or addition required a change to this parameter and bouncing the database to activate the change. Not such a great plan bringing the database down just for that. Well, Oracle must have thought so too, as they implemented directory objects that can be created and activated while the database is running - much better!

Anyway, that's a recent lesson learned about Oracle and crashing servers.

Posted by rossm at 11:08 AM | Comments (0)

July 23, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver - Part Two

In Part One of this two-part mini-series we reviewed the setup required to connect to a remote SQL Server database using a unix/Solaris 9 free odbs driver. Now comes the tricky part - making this connection useful. The goal is to be able to call this remote data directly from sql/code within our Oracle 10gR2 database. A noble goal it is....

From the first article, we know we have a working odbc connection. If you don't have this, go back and make sure that much is working before proceeding. This is one of those times when you need to go step by step or you will have very little chance of making the whole thing work. Such things as useful error messages and/or log files aren't available.

In order to complete this project, we need the following:

• Database Link
• listener.ora
• tnsnames.ora
• HS (Heterogeneous Services)

We can start with the HS setup. Oracle has two "things" that connect to odbc drivers - HS (which has the advantage of being free) and Oracle Gateway. I only know about HS, but there is a lot of documentation on Oracle Gateway as well. The Gateway provides a more robust connection environment. In order to get HS going, we go to the hs admin folder which is located at ORACLE_HOME/hs/admin.

Create an initclmd.ora file in the hs/admin directory. You create this config file to match the name you gave to your connection when we first started. For me it is "clmd", so the config file is named initclmd.ora - you get the idea.

initclmd.ora -

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = clmd
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /[your path]/odbc/lib/libodbc.so
HS_FDS_TRACE_FILE_NAME = /[your path]/odbc/tmp/freetds.trc
#
# ODBC specific environment variables
#
set ODBCINI=/[your path]/odbc/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
#set =

Fill in your connection name, which you should now know by heart. trace_level - 4, I don't know, it was suggested in the documentation and works.
shareable_name = this is the path to your odbc libraries. Be sure to look in there and verify that you have the libodbc.so file or you are just wasting your time.
trace_file_name = make something up.
ODBCINI = path and name to your odbc.ini file
Save it, you are done configuring HS. Now it knows where to go to find your odbc driver.

Here's the part that always gives me trouble - SQL Net. Here, we need to configure the tnsnames.ora and listener.ora files to get a listener for our requests to the remote database. I wound up stopping and starting the listener quite a few times before getting this part right, but maybe that's just me. I hope that these copies of my config files will help you.

tnsnames.ora - add in a section like this

CLM_Pay = (DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)
(HOST=[name of server with Oracle database on it])(PORT=1521))
(CONNECT_DATA = (SID=clmd)
)
(HS=OK)
)

The name of this tnsnames entry isn't too significant, you just use that same name when configuring the database link. The HOST and PORT should mirror the host name and port number your Oracle database listener uses. Port 1521 is the standard port, but your system might be using a different port. Under the CONNECT_DATA use SID, not SERVICE_NAME. You recognize the now familiar odbc connection name. The HS=OK entry tells Oracle to go look in the initclmd.ora for the connection information. Done here.

back up your listener.ora file (just in case - I needed the original several times...)

listener.ora - putting it all into action

# listener.ora Network Configuration File: /[your OH]/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =[your OH])
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=clmd)
(ORACLE_HOME=[your OH])
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH=/[your OH]/lib32:/[your path]/odbc/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = [local server])(PORT = 1521))

This is by far the most complicated config file. There are a lot of unwritten rules. What we need to do is to add an item to the sid_list_listener= sid_list. I'm showing you the PLSExtProc entry so that you can see the syntax for adding in another "sid_list" item. PLSExtProc has nothing to do with the odbc connection. It is just another listener item we happen to have on our database. So we add a SID_DESC item to the SID_LIST.
SID_NAME = if you don't know it by now, it's hopeless ;-)
ORACLE_HOME= copy paste from your other entries
PROGRAM = hsodbc That, you just need to know (now you do).
ENVS=LD_LIBRARY_PATH= ORACLE_HOME/lib32: /[your path]/odbc/lib
OK, this is important. I have not seen this in any documentation, and would not have gotten this right without Oracle Support's help. First is the syntax for adding two library paths. Just copy mine - it's right. Second, notice the first part of the library path - what is that? 32 bit libraries! This is where you convert your 64 bit database to 32 bit - Oracle has already provided the 32 bit libraries for you, you just have to point to them. Everything else - odbc, freetds, use 32 bit libraries to match the remote database (NOT 64 bit libraries to match your OS/database!). Dave reminded me that the 64 bit OS can read 32 bit libraries (but not vice versa). So all the confusion about 64 - 32 bit libraries comes down to right here. Just have your 64 bit Oracle database call its own 32 bit libraries and use 32 bit libraries everywhere else.

One more thing before leaving this part - only two items should be aligned on the leftmost column of the listener.ora file. The SID_LIST_LISTENER =, and the LISTENER = are the only items that go against the left margin. Everything else must be indented (it's hard to see on my example because they aren't indented very much). Learned that the hard way....

stop and restart the listener. If it restarts without error, you should see your new listener item displayed in the output. Once you get this piece going, you're nearly done.

Create a database link:

create public database link [any name you choose]
connect to useridforremotedatabase identified by remotepassword
using 'CLM_Pay';

This is standard stuff - the using 'xxxxxxxx' part must match whatever you put in the tnsnames.ora entry. Here's the final product:

From your favorite SQL spot:
select * from school_codes@clmd;

RESULT SET:

1 1 WOU 00320900 WO Western Oregon University wou.jpg Dona Vasas N N

Gee, it looks so simple! Well, you know better, but that's often what we work for - making something that's pretty darn complex look easy!

By the way, the second connection you create takes a whole lot less time! We added a connection to a MySQL database in a matter of a few hours. I hope this article will help you get your Oracle database talking to other data sources.

Posted by rossm at 8:17 AM | Comments (0)

July 22, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver

Well, a new twist has been added to our production Oracle database. It became necessary to connect our Oracle 10gR2 database up to a Microsoft SQL Server database. The goal was to be able to create a database link in our Oracle database and directly fetch data from the SQL Server database. As we worked through this process we learned that connecting to a "remote" server (that is, each database is on a different server) is a complicating factor. Other complications included: Oracle database is on Solaris 9 - 64 bit. SQL Server database is on a 32 bit Windows OS. The final complicating factor - we needed to use free/open source software - no budget for buying a commercial solution.

Here are the pieces you need to make this work. (Finding this out required a lot of time!)

• 64 bit Oracle database 10gR2
• Oracle database link
• 32 bit libraries for said Oracle database (comes with Enterprise Edition)
• Listener - properly configured (BIG caveat)
• tnsnames.ora - properly configured
• Oracle Heterogeneous Services (also in Enterprise Edition)
• unix/solaris ODBC driver (obtained from unixodbc.org)
• Free TDS driver (obtained from freetds.com)
• Windows ODBC driver
• SQL Server database

What we found was that a lot of people have tried to make these connections, and that the documentation was either incomplete or very confusing. Much of the confusion came from understanding when to use 64 bit libraries and when to use 32 bit libraries. Jose at Oracle helped a lot (SR). He modified the listener.ora file to tell the Oracle database to use 32 bit libraries. Once this was in place, then everything else used 32 bit libraries even though it was installed on a 64 bit OS. The 32 bit libraries work just fine on the 64 bit Solaris 9 OS we were using.

I can't say too much about the Windows ODBC setup, except to say it was done pretty quickly on the Windows server, and there was a good amount of supporting documentation that was accurate enough to get that job done. This was set up by Mike Huber and Brian Berkley (our Windows sysadmin). All of our challenges came on the unix/Solaris side.

After installing the odbc driver manager obtained from unixodbc.org, the next step was to configure this to connect with the windows ODBC driver. This should all be using 32 bit libraries (I know this now...). We found that adding freeTDS into the unixodbc driver was necessary to actually make our systems talk to each other. It may be that the freetds portion is not always necessary, but it was fairly easy to install and configure, and we could not get any connectivity without it. Dave Diemer, our unix sysadmin, installed the odbcdriver manager, and freetds for us - this must be done as root. These things want to install in the /usr/bin path, but Dave forced them into a different path in our Oracle area. Dave used a command like this:

• ./configure -with-tdsver=8.0 --prefix=/[your Oracle path]/odbc --with-unixodbc=/[oracle path]/odbc

It was quite helpful keeping all of this within our Oracle area.

The unixodbc driver uses two config files - odbc.ini and odbcinst.ini
Here's what our files look like:

odbc.ini -

[clmd]
Driver = TDS
Description = clm development
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/clmd.log
#Servername = winchester.wou.edu
Server = ip.address.of.your.server
Database = [name of database you want to connect to]
Port = 1433

Things to note about this file:
[clmd] - clmd is a common name we used throughout the setup. At first we had CLM_Development. Don't use such a long name, it can cause problems. Just pick something simple. We had to eventually go back through all of our setup and change to the simpler name.
Server = Use the ip address, at least initially. Even though the unixodbc.org instructions say to use "Servername" don't do it! It must say "Server = "
Database = This is the name of the database you want to connect to.
Port = 1433 is the standard port for this sort of thing.

odbcinst.ini -

[TDS]
Description = FreeTDS driver
Driver = /[oracle path]/odbc/lib/libtdsodbc.so
Setup = /[oracle path]/odbc/lib/libtdsodbc.so
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/freetds.log
FileUsage = 1

Our driver and setup entries refer to the freetds libraries. If you are not using freetds, then these entries point to the odbc libraries for your situation. The libraries come in pairs, for example the MySQL libraries are named libmyodbc3.so and libmyodbc3S.so (The Setup library has the extra "S", the other library is the Driver.)

freetds.conf -

# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[clmd]
host = your.server.ip.address
port = 1433
tds version = 8.0

The last section is really the only thing we had to configure in this file.

At this point, you should try connecting to the remote database using the odbc drivers you just configured and installed. There is a nifty utility for doing this called "isql". Don't let the name throw you, it is just a utility that comes with the unixodbc install. It is located in the /[oracle path]/odbc/bin folder. You call it like this:

isql -v tds(our situation, use your odbc.ini name) useridtodatabase password;

A successful connection returns a box, praises you for your success and opens a sql session with the remote database. You can then do an appropriate select and it will display the data in a cryptic, command line sort of way. Here's an example:

isql -v clmd sqlserveruserid password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from school_codes;
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| SCHOOL_LINK_ID| SCHOOL_CODE| OPEID | CAMPUS| NAME | BANNER_IMAGE | MULTIPLE_ADDRESSES| MAIN_CONTACT | TITLE | ARCHIVE_FLAG| BUTTON_IMAGE | BILL_TEXT | CLOSE_AMT | LONG_AMT | INCLUDE_IN_CLEARINGHOUSE|
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| 1 | WOU | 00320900 | WO | Western Oregon University | wou.jpg | | Dona Vasas | | N | | | | | N |
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

At this point you know you have the unix odbc driver and everything on your Windows box configured properly. This is a long process, so I'm going to quit right here. Next time, we'll look at the remaining part - getting the Oracle database to make this connection directly.

One final comment. To make this work, it took quite a number of people from the UCS Department here at WOU. Mike Huber, who is a Banner programmer that needs this functionality. Mike helped in many ways including finding unixodbc.org, freetds, and providing details in the various configuration files. Brian Berkley helped set up the Windows box and database to accept a remote connection from our Oracle database. Dave Diemer did the actual installs and provided the unix guru advice to put the pieces together. Oracle support - Jose Franco. Took our setup, analyzed it, pointed out where we had gone wrong, and actually made it work. Opening an SR (tar) for this was definitely the way to go! There are a lot of pieces that need to fit together just right in order for this to work.

Posted by rossm at 1:09 PM | Comments (0)

April 15, 2009

New Database

Well, Dave has set me up a virtual box, and we are going to install an Oracle 10g database on it just for fun. We're looking for a new landing place for some of our Oracle databases, plus since Maverick died on us we need a spot for the CBORD upgrade to land.

So far I've been able to copy over the downloads from Oracle, which I had already saved, and unzipped them.

New day: This morning Dave and I got together and tried the Oracle 10gR2 install into the container. We kept getting an "out of memory" error when Oracle tried configuring the database. We did successfully install the db onto the root system (much more ram and disk space), but haven't yet gotten a successful install into the container.

The out of memory error appears to be connected to a bug in the way that the new "project" method for setting OS parameters is implemented. The project parameters may not be allocated to a particular Oracle session. Dave is looking at this problem.

Well, we've proceeded to get a db installed into a Solaris container. Dave made it happen, not sure what was required.

Next step is to set up some test queries to compare the processing times between the db on the main hardware and the db in the container.

Posted by rossm at 4:22 PM | Comments (0)

August 20, 2008

Oracle Database Recovery

I am still waiting on some root level file structure modifications and some file copying on Maverick in order to try a recovery. Brad has been gone and busy on other stuff, so hasn't gotten to this one yet. Dale and I are pretty much stuck until we can get our server set up to mimmick the Aero configuration. Once that gets done, we hope to make some gains in our knowledge of database backup and recovery. More on this later.

Posted by rossm at 9:06 AM | Comments (0)

August 6, 2008

New 10gR2 Instance

Hello again, blogging world. I've been out enjoying the Summer and taking vacation, but am now back for a few weeks to move things forward. The latest news on Oracle databases is that I installed a brand new instance of 10gR2 on our standy/backup server. I've been reading up on RMAN and am just about ready to try to load this database from the backups of our production instance. Brad is going to copy over the backup folders for me and do a little work recreating the correct directory structure, then we'll be ready to give it a try. This is really the first "restore" we've tried around here for quite a while. It is the first step in creating a more robust backup strategy, and also the first step in getting us trained and ready to perform backups if it becomes necessary.

The two production databases have been pretty boring lately. They are getting light usage this time of year. The smaller one has a few issues with the server it's on, but there is a light at the end of the tunnel. We may very well have a VM (virtual machine) to put it on in the near future. Everyone is ready for that to happen. Dale and I continue to monitor these databases using EM, and have fixed some minor problems. We are both impressed with the improvements in Version2 over Version1.

Posted by rossm at 9:01 AM | Comments (0)

July 9, 2008

Is Everything Broken?

I don't want to sound like some old grouch, but this past week has just not been rosy. It seems like everything decided to break all at once, probably because the weather was nice outside. Yesterday Dale and I spent most of the day putting the Oracle Enterprise Managers for Aero and Slider back on line. Yup, they both decided to go down, although for different reasons. We did finally have to delete and rebuild the entire EM repository on each database. On Aero, we started getting a java error, which resulted in the loss of all of our statistics including CPU usage, SQL performance, and user data. Slider was working perfectly until I tried to re-run an RMAN backup, then poof, it started spitting out errors mighty fast, then just crashed.

The backups running from EM on Slider continue to fail. The RMAN job just isn't using the correct paramaters so it fills the flashback recovery area up then fails. The backups aren't supposed to go into the flashback area. If we run RMAN by hand, it works flawlessly, using the correct backup disk destination. The next step is to remove the EM backup job and recreate it.

Yesterday we had the weirdest experience on Astra. Kathy Hill called me asking why the NS building wasn't showing up for scheduling of classes. Gee, I don't know, why not? Then, a few minutes later, Nan Lehto called me wanting to know why she couldn't schedule any SOAR events into NS. Gee, I don't know, why not? Well, in looking over Astra I discover that the entire NS building has been marked as "offline" meaning nothing can be scheduled in it. A couple hours later, poof, that designation goes away, and it's back to normal. Or should I say paranormal? Anyway, I spent a good hour removing permissions from all but the core Astra users that allow for the changing of building and room information.

No, that's not all. Last Friday I got a call from Kris Adams - the Bookstore's new printer won't print PO's correctly. Oh, no problem, it's the Forms Path software probably not set correctly for the new printer - a call to MBS hardware support should fix it pronto. Well, after about 8 phone calls, and umpteen hours on the phone with MBS tech support, we're finally back to where the new printer will print some stuff, but still no forms such as PO's. We have re-installed Forms Path, and now we're waiting for further help from hardware. The Forms Path seems to be working, but can't get the print jobs onto the print queue (for reasons currently unknown).

Well, I'm due for a vacation in two weeks!

Posted by rossm at 10:00 AM | Comments (0)

July 2, 2008

All Quiet on the Western Front

The databases are running smoothly, with just a few minor things such as jobs not running and so forth. Aero has been quite stable since the hardware replacement on the server. Dale and I still want to get in and do some more tuning on application SQL. Also, there are some permissions issues that need to be fixed. Slider has been serving it's two application users well. The server for Slider does double duty as our backup server, but seems to handle both loads pretty well. WOU has some additional server hardware which is being set up now. This could very well mean that the production db's will get some new homes. Exciting!

I spent about an hour trying to start up our old 10g install on Maverick, but it is pretty unhappy. Some of the sys datafiles seem to be missing. Dale and I will be setting up a 10g test version on Maverick, but it looks like a complete new install is going to be required. That's OK, though, as I would like to get the latest patch of 10g and see how that works, plus we should also get busy looking at version 11. There's always something going on when it comes to db's - I like it!

Posted by rossm at 8:33 AM | Comments (0)

June 18, 2008

RMAN Studies Begin

This week I began my in depth studies of the Oracle backup and recovery manager (RMAN). The UCS department purchased a great reference book on RMAN and I've begun reading it. Yes, it is a rather dry subject, but this book is a wealth of information. Here are some things I've learned so far:
- RMAN is installed as part of the Enterprise database, it exists in every db we have.
- RMAN connects internally to the db as sysdba.
- The control file contains information about all data files (.dbf) and RMAN stores backup information about each data file right in the control file. This is why it is not good to lose all copies of your control file.
- RMAN copies data files at the block level. No other backup strategy can do this. This results in a lot of benefits such as the ability to delete "null" blocks and blocks containing no data during backup.
- RMAN can backup the db while it's running (hot) without interfering with the use of the db. It does this by cloning the control file and making a backup consistent with the cloned control file. The db continues to work using the real control file.
- RMAN copies the blocks directly into one big file, in chunks, often using more than one process, and only RMAN can ever get them back to their rightful places again.

There's more, but you can get the idea of the types of things that I'm learning about RMAN. I still need to learn about coordinating our RMAN backups (all db's have current RMAN backups) with a good tape backup strategy, then deleting unnecessary backups. Also, the interaction between RMAN and the Flashback area needs more study.

Oh, by the way, the db's are running strong. I managed to screw up the Helpdesk app a little doing some tuning on it, but other than that I've kept under the radar.

Posted by rossm at 9:28 AM | Comments (0)

June 11, 2008

What's Happening With Oracle These Days?

Inquiring minds want to know! Well, the productiond db AERO has really settled down. It's fine tuned, mostly set up well, and Sundown has been reliable since the hardware problem was fixed. My new goal is to just keep the thing running continuously for awhile and see how it does. The one item that still needs more polish is the RMAN backups. We're taking RMAN backups every day, but we need to coordinate the disk backups with the tape backups, then remove those backups we no longer need from the disks. I'm on Chapter 2 of my new sleep inducing - RMAN Backup book. It is a really good book, but you have to admit it's a pretty dry subject!

Our 10gR1 database is having some issues - mainly, the Computrition users are going crazy because their application is sooooo slooooow. We've been looking over the db for bottlenecks, but nothing too definitive has come up yet. A couple of tablespaces were pretty full, so I gave them some more room to work. Also, it looks like our server is a bit short of RAM, so we're looking at throwing some hardware at the problem, too. As of yesterday, we're getting good RMAN backups now.

All in all, I'm pretty happy with the way things are set up right now. This Summer I hope to get 10gR2 running on Maverick, which we'll use for a development instance and also for testing our backups. Dale and I continue to find more and more things the new Enterprise Manager can help us with, so we'll be exploring that some more, too.

Posted by rossm at 11:05 AM | Comments (0)

More Parameters R Us

Sometimes things go right! SWAMP was giving me more fits because of the 2000 parameter limit imposed by the newer version of the Apache web server. Of course, we were trying to pass about 2400 parameters - just a few over the limit. That got me interested in this parameter problem again so I started looking around for some way to increase the limit. We had tried before to increase the limit, but then the web server wouldn't start. Well, to make a long story short, the config file requires the limit to be set in a particular place. So, I put the new directive in the right place, Ron bounced the web server, and voila - a new limit of 15000 parameters. Woohoo!

I know what you're thinking - "Who passes that many parameters around?" But, remember that each piece of data counts against the limit, whether it's in an array or not. So, 15 or 20 data items per record, 2-300 records or so, bang, you're over the limit. It's actually pretty easy!

Posted by rossm at 10:55 AM | Comments (0)

June 4, 2008

Oracle Databases

Things have settled down quite a bit on the Oracle database scene. The hardware glitch on Sundown has been fixed, so the goal now is to keep AERO and the web server up except for necessary scheduled maintenance. We had a nice meeting with the systems people regarding the backup process. I ordered a reference book on RMAN backups, and will be refining our backup strategy as the next db project. We currently maintain RMAN backups for AERO, but there is a need to coordinate these backups with the tape backup process.

The 10gR1 instance now has the food service application and Viking running on it. The Viking users were quite anxious to get moved to 10g to take advantage of some of Viking's newer features, but Viking will only run on 10gR1 with a specific character set (not the default set). By coincidence, that character set is the one our existing 10gR1 instance uses. Michael Ellis moved Viking over last week. So, Viking got upgraded and also that frees up a server for us to use for a test 10gR2 instance. Dale and I will be working on that in the near future. We need to remove the 9i instance and install a 10gR2 (or 11??) instance there. Well, we don't ever have a shortage of interesting things going on with the Oracle databases!

Posted by rossm at 7:36 AM | Comments (0)

May 21, 2008

AERO Flying Straight

Well, AERO is really shaping up nicely. I can't overstate how much help the web based Enterprise Manager is in helping to keep the database in tip-top shape. Every week I find something new that helps with the database management.

A couple weeks ago I implemented RMAN backups on AERO, using a standard backup schedule. I got a little wild and created a flashback area that is 50 GB huge. However, at this size I have nearly two weeks of entire database flashback time. I've never tried to recover using the flashback area, but hope to do that soon. One thing that still needs to be done is to move the RMAN backups to tape and delete the backups once they're on tape. We have a meeting coming up soon with the Unix gurus to work on this. Also, Michael Ellis and I finally got all the pieces put together to move Viking onto a "special" 10gR1 database. Once this is done, we will have a server to put our development instance on. That will allow us to do some testing of our backups, and develop our recovery skills.

One sour note is that our server that is never supposed to go down crashed again - second time in about a month. Of course, the database crashed right along with the server, but I understand it came right back up (I was gone when this occurred).

Posted by rossm at 8:38 AM | Comments (0)

May 7, 2008

10gR1 Instance - Clogged Files and Enterprise Manager

We have a second Oracle database instance, which is a 10gR1 version. It is used to run a dining application, and we are hoping to put Viking on it. So, we have been spending some time making it ready for Viking. It has some definite maintenance issues. Clayton just returned from vacation and was a big help in identifying and getting rid of a 31GB alert log file that had jammed up the works. This db has not been managed up to this point, and of course things don't tend to get better over time especially with regard to the ever growing log files.

We did finally get the HTTP server from our production instance to connect to this instance. Looking back, the setup was pretty straight forward - add a DAD that points to the 10gR1 instance, add in connect information to the tnsnames file, and presto! Since we weren't sure how to set everything up, it did require a lot of trial and error to get it all synched up. But it works now! That is sweet! We only need to have one Apache style web server this way.

The EM console would really be a great help for managing this instance, but what a mess that is. I've gotten as far as getting a repository created and the dbconsole running, but could not connect to the db. So, back to the drawing board, drop the repository and start all over. Unfortunately, this requires the db to be in "quiesce" mode, which messes with the users. Fortunately, there aren't a lot of users on this db. I'm hopeful that one more round of dropping and recreating the EM repository will get EM going to help mange this instance. In the mean time, it is being managed by hand which isn't nearly as good. I am a big fan of the new EM console, but it is a bear to get it set up correctly.

Posted by rossm at 9:38 AM | Comments (0)

AERO Doing Well

The AERO database survived this month's maintenance window, which was last Thursday night, despite my best efforts. Dale and I went through many of the tablespaces and reorganized them to have auto segment management. We reorganized all of the tablespaces that we could - some had data types or dropped tables in the flashback area so weren't able to be reorganized. This will make tablespace management much easier. It did have one drawback - wow, we generated a ton of redo logs! These will soon pass through the system, and will not be a long term problem.

The other thing we did during the maintenance window was to multiplex our redo logs. This simply means we make two redo logs instead of one, so we have redo logs on more than one disk - just in case. Our previous setup was three redo log groups, with one member log file per group. We still have three redo log groups, but now there are two members per group. Now AERO enjoys multiplexed redo logs, control files, and archive log files. I'm thinking of turning off the duplication of the archive logs and relying on our tape backups, but for now we're creating two sets.

In addition to all the multiplexing we're now doing on the production db, we also have a huge (possibly too huge - 50 GB) flashback area that is fully functional. Dale is running a test on how long of a time we have to flashback dropped tables. He dropped some tables last week, and can still flashback all of them. At some point they will age out of the flashback area, but so far they haven't. We have also implemented RMAN backups, which use the flashback area, too.

All in all, we've really ramped up our db stability. All the multiplexing really will help in the event of a disaster. The flashback area can be used to restore dropped tables (and many other dropped objects), and also can flashback individual tablespaces or even the entire database in case of corruption. Add to these improvements the tuning we've been able to do, and AERO is really in tiptop shape!

Posted by rossm at 9:13 AM | Comments (0)

April 30, 2008

AERO Gets A Friend

First of all, let me update you on AERO. It has survived it's upgrade to 10gR2, and we've had some time to shake the bugs out of it now. Enterprise Manger (EM) is running, and Dale and I are learning how to take advantage of the many features of EM. Let me give you one example. When AERO was first started up I set the baseline on SQL Response - it was right at 16 seconds. I'm not exactly sure what this means, because the average wait time for SQL queries certainly was not 16 seconds, but anyway that's the baseline. Using a feature called ADDM, I was able to identify about 6 of our worst SQL tuning offenders and fix them right from EM - usually by adding an index. The baseline is now less than 2 seconds after performing these tuning tasks. Also, we now have RMAN backups going and Thursday night we will have multiplexed redo logs. This database is as stable and well tuned as any we've had on campus, thanks in large part to the new EM.

OK, so AERO is doing well. We have a sibling db running on a separate server at version 10gR1. This db just happens to have the correct character set for the Viking application - what a stroke of luck that was. Michael Ellis and I have been testing the migration of Viking to this 10g platform. Right now we're working on creating a DAD which will use AERO's web server to connect Viking users to the 10gR1 instance. It should be doable, but is of course a challenge. Yesterday I learned about tnsping, which can be run from a Solaris command line and can be used to test the connect string for the DAD. The one we had wasn't working, so we are on the right track to get that going. The actual migration of Viking might require the expertise of the Viking techs because it still uses long raw data types, which don't make the export/import jump to hyperspace. As luck would have it, the 10gR1 EM is totally hosed, so that's another project to keep me off the streets!

Posted by rossm at 9:40 AM | Comments (0)

April 16, 2008

10gR2 - We Love EM!

Yes, we love Enterprise Manager! Dale and I have continued our quest to whip our production database into shape. This week we did a lot of minor tweaking such as adding indexes based upon recommendations from the ADDM (whatever that stands for) section. It will identify sql queries that use up a lot of cpu or database time and offer suggestions for improving the execution. In three separate cases a new index was recommended, and in one of these cases adding the index reduced the execution cost from over 5,000,000 to about 8,100 (this index saved multiple full table scans, which is a costly operation on a big table). You can create the index right from EM.

Another important improvement completed this week was activation of the flashback area. The neatest part of this is that we can now recover a table that was accidentally dropped in about a minute. Without flashback, this takes about a week! The bigger, but not as obvious, advantage is that we can flash back the entire database for about 48 hours if something major happens. In other words, the whole database can be recovered without resorting to the archive log files, at least for a short time.

The last piece I'll write about here is setting up a good backup. This requires the flashback area to be in place. I put a job together that ran last night, but it failed because I'm not too good at math, I guess - tried to put 40 plus GB of files into a 5 GB space. Guess what, it doesn't fit. So, about 10% of the backup got done before it totaly filled up the space. Anyway, the backup will work once we get the appropriate amount of disk space set aside for it. Clayton is working on that for me.

Using Enterprise Manager in the 10gR2 environment is really helpful. EM tells you about many, many items in the database that are really hard to see using a command line. Some examples: identifying tablespaces that are running out of disk space, tablespaces that need to be shrunk, take tablespaces off line or put them back online, sql statements that need tweaking, important alert log entries, and on and on.

Posted by rossm at 9:12 AM | Comments (0)

April 9, 2008

10gR2 Gets Annoying, Possibly Fixed

So often, upgrades drive you crazy while you chase down annoying problems that crop up. You really appreciate the new features, but it sure comes at a cost. Unfortunately, this upgrade has been no exception. Right after the upgrade, the Enterprise Manager wouldn't start up. Turns out the installer puts different time zone files in for the EM and the Java virtual machine. Must bounce the db to fix, which we did when Sundown was down. Next annoyance: approximately every two days or so the Listener hangs. At first, it appeared as though the database itself had expired, but no, the listener just hung. Bill figured out that somehow two listener processes had gotten started - cause unknown - but, killing those processes and restarting the listener put you right back in business. Of course, this got old the third or fourth time it happened. This morning, I put in an SR to Oracle and they got right back to me suggesting I put this command into the listener config file: SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF. Well, we had tried this before and the listener wouldn't start, but I had received better directions on where to place this command in the config file. Sure enough, Bill stopped and re-started the listener and it started right back up with this line in the config file. So, hopefully, this annoyance will also be behind us.

I've got one more thing to fix right away, and that is the flashback area. It had been set to start up with the last restart of the database, but it didn't. This requires a bounce of the database to start, and will have to be done soon. The flashback area is very similar to the Windows Recycle Bin. If an object is mistakenly dropped from the database you can just go in there and put it back. Very slick, but of course it doesn't work unless configured.

So, aside from me complaining about these annoyances, the 10gR2 version of the database is a real step forward. Once these few items shake themselves out, then I'll get busy doing some fine tuning and tweaking to really make it hum. That will be a lot more interesting!

Posted by rossm at 9:37 AM | Comments (0)

April 2, 2008

10gR2 Has a Wild Week

Well, our server that never crashes did just that late last week - last day of Spring break, good timing. Of course that brought the database, listener, the whole ball of wax, down. But, while the database was down I copied over those two time zone files I got from Oracle Metalink support and by golly, Enterprise Manager is now functional. What a relief that is! At least now I know what all needs attention. Dale and I moved a bunch of objects out of the System tablespace. Somehow, we got a number of users with System as their default tablespace, which make absolutely no sense! Anyway, we just altered those users and moved out all of the offending objects.

We got to learn the syntax for moving an Index to another tablespace, which is:
alter index [index name] rebuild tablespace [new tablespace name].
The syntax for moving a table is:
alter table [table name] move tablespace [tablespace name].

On Thursday night, I'll be doing another 9:00 pm to whenever shift to make some modifications to the 10gR2 production instance. When I started it up during the upgrade, a wrong version of the initaero.ora file was used, which resulted in our control files and archive files staying in their old location. One of the main things I want to do this month is to move those over to the proper location. Also, we have maxed out our number of data files at 200, so that needs to be increased. I want to increase the processes parameter to 500, too. When we lose connection with OUS databases, our processes spiral upward and it locks up the database. Hopefully, this will help with that problem.

While I have the database down, I also want to do some maintenance on the Apache webserver logs, and take care of an ons.log problem from the upgrade.

It will be a full night of maintenance, but not nearly as demanding or stressful as the upgrade, I hope!

So far, the 10gR2 database has proven to be a nice, solid Oracle database for our production environment. The Enterprise Manager upgrade was worth it alone, it is really slick. In addition, I have installed and implemented Oracle's "Software Configuration Manager" which is a part of the Metalink support. It sends information about the database to Metalink, and Oracle makes recommendations about items that need attention on the database. It is actually very helpful.

Much going on with the database still, and plenty to keep a guy like me happily busy for some time. I am anxious to get these fixes in place Thursday night, it should be fun.

Posted by rossm at 9:47 AM | Comments (0)

March 26, 2008

Some Random Database Stuff

There are a few random things to add to the database log. Most of these came up from preparation for next week's maintenance window.

First item - how to move a table from one tablespace to another. This is occassionally done when a table gets put in the system tablespace by mistake, or if a new tablespace is created, and you want to move some existing objects (usually tables) into it:
ALTER TABLE [tablename] MOVE TABLESPACE [new tablespace name];

Second item - Brad and Clayton showed me how to use a unix application called RCS. This is a way to log changes to a particular file. You use commands to check the file out and in, and to lock it while you are modifying it. Then you enter a description of the changes you made to the file. In the end, you have a nice log of all changes that have been made to the file. You can also roll back to any version of the file. We are going to use RCS to log all changes in our configuration files for the database. That will help us keep some good information on what's going on in those files, and who to contact for a particular change. This will be implemented right after our next maintenance window.

Third item - Log files that drive me crazy. With each upgrade of Oracle, it seems like another log file or two pops up and causes trouble. The Apache ssl_request log is one such creature. You can't delete the log file or Apache goes haywire, and the thing grows by many MB's per day. Pretty soon it is a huge mess. It does actually have some useful information in it, but you are hard pressed to find it among every single SSL request that's made. There's not much to do with this one but clean it out while the db is down anyway. A new item from Oracle is opmn - this is now used to stop/start the webserver instead of apachectl. Of course, it comes with it's own set of logs in addition to the Apache logs. The upgrade also has some sort of conflict and the ons log also grows like a weed - it is just full of error messages from the conflict. Hopefully, this one will get fixed during the next maintenance window. There are more of these, but this is enough to get the picture.

I guess that about covers it for this week. Time to add a bit more to my web log.....

Posted by rossm at 10:22 AM | Comments (0)

March 19, 2008

10gR2 - Up and Running

Well a week has passed and our 10gR2 database has done a darn good job of shouldering the load. Nobody has been phoning me to say how much faster everything is, but they haven't been phoning to tell me X isn't working, either! I have identified 6 items that need attention at our next schedulied maintenance window. I am amazed at the screwy logging schemes that seem to pop up everywhere on a production Oracle database. We've got somewhere in the neighborhood of 100 MB of connection log entries on the HTTP server in this short time period. The ons.log grows like a weed and Dale has deleted it off both Sundown and Maverick several times already! I am hopeful that some day we can create circular logging schemes for all of these various logs so that they don't need constant babysitting.

Read on to see more detail regarding the upcoming maintenance items.

The first maintenance item is an effort to try and keep the ons.log from running wild. Oracle documentation recommends adding this line to the configuration:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

However, I had added the line in, and Bill could not get the web server to start with it in there, so some tweaking of this is obviously necessary. This is just an annoyance item, not a threat in any sense.

The second maintenance item is possible applying Patch 5337014. This is the patchset that moves the db from 10.2.0.1 to 10.2.0.3. Michael Ellis needs a 10.2.0.3 db version for his CBORD install, however it looks like that install might get moved to this Summer. If so, we will not apply this patch until later, because of the next item...

The third item on the maintenance list is Patch 5752339. This is a patch for Solaris 64 Bit using Veritas, which is us, and must be applied with the other patch or else the database can't communicate with the disks. Now you see why I'm willing to wait awhile to apply these patches! We don't need this kind of problem, and perhaps a more comprehensive patch will be available by this Summer that avoids this kind of issue altogether.

The fourth maintenance item is to shut down the Apache web server and clear out the connection log, possibly putting something more user friendly in place.

The fifth item is to make a number of modifications to, and restart the db using an upgraded parameter file, then make a new SP file for future db restarts.

This is the list of modified parameters:
control_files ='/newpath/control/control01.ctl',
'/newpath/control/control02.ctl',
'/newpath/control/control03.ctl')

log_archive_des_1=newpath/arch'

07_DICTIONARY_ACCESSABILITY=false - Moved from true to false.

PROCESSES - reset the following parameter:
processes=500 (up from 180)

When all nice and working well:

create spfile from pfile='path and init filename;
shutdown db
startup; //this command should work if the spfile is created OK.
//this is the way we want to start the db.

These are just a few "cleanup" items from the upgrade.

The final item for the maintenance window, and by far the most aggravating of them all, is to overwrite two simple time zone files, which have prevented us from using Enterprise Manager since the upgrade. Somehow, the installer puts in two different time zone files, one for the db and one for Java, and since they don't match EM does not work. Aaaack! It is a bit like flying blind trying to figure out what's going on in the database when your A number 1 tool is on the fritz for such a goofy reason.

Oh, well..... On the positive side, the database is running, it is manageable, and it cheerfully does a ton of really key work each and every day.

Posted by rossm at 8:51 AM | Comments (0)

March 12, 2008

10gR2 Upgrade is Done!

Yes. We are now running on Oracle 10gR2 in our production database. The upgrade was very difficult to perform, and there is no way I could have done it without the help of about 20 other people including Bill, Travis, Ron, Michael, Brian, Dale, Brad, Clayton, and some others I'm sure I forgot. We started the upgrade at 9:00 p.m. on Thursday to coincide with our monthly maintenance window. We finished at 9:00 a.m. on Friday.

As of today, there are still a few loose ends out there. The archive logs and control files are still being written to the old Oracle Home, there is a 2000 parameter limit on items passed through the web server, Enterprise Manager is not working (open TAR on that one), and the ability of the database to write onto the file system seems to not be working right. Out of a list of about 10,000 details, not bad at all - although I do hate that the Enterprise Manager isn't working yet.

Due to limits with the Viking application, it now resides by itself where our test database will eventually live. We had to make a complete 9i instance for them, and that was a bit rough. It seems to be working well now. This Summer we are going to quit using that application, so at that time we can reclaim our server and reinstall the test database.

All in all, we got a very good result. I am glad we don't do that every day, though!

Posted by rossm at 10:10 AM | Comments (0)

March 5, 2008

Ora10gR2 - Thursday Night!

Finally. We are getting ourselves ready for upgrading the Aero 9i production database to 10gR2! It is pretty exciting. A lot of folks in UCS have been helping to prepare various pieces of the database for upgrade, and this Thursday we are going for it. We will start around 9:00 pm, and the production database will be offline for several hours minimum. But, come Friday morning, we will have moved to nearly current with our database.

One complication we ran into is that the Viking application is unable to run in Oracle 10g unless the character set is US7ASCII. This is an unacceptable character set for our production database, so we are creating a separate 9i database solely for this application. The bad news is, it will wipe out our test instance. The good news is, we are only using this application for a few more months. Then we will put our 10g test instance back in place, and upgrade it to version 11.

Some of the improvements we should see with the 10g version are many more sessions!, much improved Enterprise Manager for database management, Oracle Wallet will hopefully begin to work, we will be using the standard Java virtual machine, DAD creation and management is much easier, metalink support automated, and a lot more! Next week, I'll give you the blow by blow and how it works out. Sometimes my job is just too much fun.....

Posted by rossm at 10:29 AM | Comments (0)

February 20, 2008

10gR2 Production Upgrade Scheduled

Friday, March 7th, beginning at 5:30 p.m., several of the UCS staff will be having a fun time upgrading our production database from the 9i version to 10gR2. We have created a 10gR2 database on our production server together with an HTTP server. These will be used as part of the upgrade. Our plan of attack is to shut down the database, make a copy just in case, run the upgrade, move all the datafiles to their new Oracle Home, recreate all configuration files to point to the new Oracle Home, then restart the upgraded database. Ron will move our SSL certificate, and Alex will be responsible for all the Unix heavy lifting - file copies and such.

We have learned that we can create our DAD's with names that mimic our existing structure, so that we should not have to go to each package and procedure to enter the new paths. This will save us a bunch of time. This upgrade is long overdue, so it will be nice to get the 10g version into production. It has many features that will resolve some long term issues, such as Oracle Wallet for our website, increased number of sessions, and a much better way to do upgrades and patches. It's supposed to be more efficient, too, but let me tell you it is one very busy application! I am constantly amazed at the wide variety of things that use the Oracle database. The improved performance may not be all that noticable at the user level, primarily because our current database is already running at such high speed. It's like going from 100 mph to 120 mph - one is surely faster, but both speeds are pretty fast.

With any luck at all, we'll have a sweet 10gR2 production database up and running by March 8th. Then I can work on the upgrade to 11i - ah, job security!

Posted by rossm at 8:35 AM | Comments (0)

February 13, 2008

10gR2 Update

Thanks to Alex and Travis, we are moving quickly ahead toward upgrading our production instance to 10gR2. The OS parameters have been updated, and 10gR2 has been installed on the production server. The next step is to install the HTTP server, then run the upgrade script to determine what issues we have for the upgrade. Fix those issues, prepare the configuration files, then go for the upgrade. We will have to bring the database down one evening to accomplish the upgrade, and I'll bet you will be amazed at how many things quit working when we do that! The database will be down for at least several hours to allow for a complete backup (just in case!) and to let the upgrade manager run.

Many thanks to our Unix/Solaris support - Alex and Travis.

Posted by rossm at 10:17 AM | Comments (0)

February 6, 2008

10gR2 Upgrade Gets a Lift Tonight

Tonight, Travis and Alex will be doing the monthly maintenance on the server where our production database resides. One of their tasks is to make a slight modification to some Solaris parameters so that 10g can run on the server - requires a reboot of the server to activate the parameters. After that we should be getting the new Oracle Homes set up and pushing forward with the installs. Dale has been reviewing the web-based Enterprise Manager and we are continually impressed with the number of administration tasks that can be accomplished through EM.

Posted by rossm at 3:15 PM | Comments (0)

January 30, 2008

Moving AERO to 10gR2

Those of you familiar with the UCS department know that two key employees have moved on to different jobs in the past month - Joe Crowe and Travis Knabe. It's hard to see old friends leave, but just a part of life, I guess. For those of us remaining, we will replace the positions, meet new people, and move on. I mention this because Travis was a key player in making the upgrade of our Oracle database happen. It's kind of ironic, we were getting pretty close to doing an upgrade about a year ago, maybe more, when Troy Knabe left (Travis' brother), putting this project back to square one - Troy was "the" key person back then. Thankfully, Travis is still available to help on a reduced time basis, and we are mighty close to getting this upgrade completed.

We have a running 10g database on our backup server (not production) that has a functioning web server and a web interface to the Oracle Enterprise Manager. This was our practice upgrade of a copy of our production database, and with a few wrinkles, went quite well.

The basic steps for completing the production upgrade are to modify the OS etc/system parameters on Sundown slightly, create Oracle Homes for the new database and HTTP server, install db and HTTP server, run the upgrade script to identify issues for the upgrade, perform the upgrade, re-configure and clean up old directories and legacy code.

Stay tuned for more blogs with upgrade details as we move forward.

Posted by rossm at 9:53 AM | Comments (0)

March 23, 2007

Performing Tablespace Recovery on 10gR2

Yesterday and today I've been working on the intricacies of restoring a tablespace. This is an interesting concept in that it should be possible to recover a messed up tablespace without bringing the database down. In other words, someone could seriously mess up their own area (tablespace) and we could recover it for them without disturbing anyone else. This would be a key skill for effective management of our new production database.

Like most things Oracle, this isn't as easy as it sounds. Every time I would take a tablespace offline and try a recovery on it, the datafile for that tablespace would also require recovery. I'm not yet sure why this is happening, but once the datafile was recovered, then sure enough, I could recover and open the tablespace to the current time.

Another thing, I had to recover dropped tables using flashback. They didn't come back with the tablespace recovery to a prior point in time. This must be tied to the required recovery of the datafile.

Bottom line is that I was able to recover just one tablespace. Using flashback, I could replace any dropped tables as well. This is yet another benefit of going to 10gR2 as our production database.

Posted by rossm at 10:53 AM | Comments (0)

March 7, 2007

Update on 10gR2

Well, I did come back from Hawaii, although it sure was a fun trip. Snorkeling was really fun, and we saw lots of fish and turtles. Very nice!

No moss has grown on me, though, since my return. I've torn right in to the 10gR2 upgrade again. Travis created a new snapshot of our "TENG" test database. I dropped a couple tables, then Travis replaced the existing db with his snapshot. To make a long story short, the database came back up with the dropped tables restored.

I ran in to one interesting problem, and a lot of uninteresting ones, too. The db was throwing an ORA-01102 error on startup. It wouldn't mount the db. An excellent article in MetaLink (Note:1034037.6) explained how to look for existing memory segments and semaphores. I followed the directions for removing the old segments and semaphores and the db started right up.

What this all means is that we are in a position to restore TENG using one of Travis' snapshots. The db will just open once the snapshot has been applied. So, if we destroy the db in testing, it can be replaced in a matter of a few minutes - no need to completely reinstall. This is not a complete restore and recovery process - yet - because we will lose all data from the time of the snapshot. OK for the test db, not good enough for production. The next thing to do is to begin work on a good backup and recovery strategy. Also, we need to get some version of 9i-AERO on our test box.

Posted by rossm at 2:07 PM | Comments (0)

February 23, 2007

10gR2 Moves Forward

Yesterday Michael Ellis, Dale, and I worked some more on the 10gR2 upgrade. We were able to log in with the Thin Client in less than 5 minutes - a huge improvement. We actually shut down the AERO instance of 10g, saved the folders we needed, and deleted the rest of the install. We then reinstalled 10gR2 with a SID of TENG. It is our collective opinion that two db's on the same server with the same SID just was not going to work.

Dale handled some maintenance, creating users and importing tables.

Travis made a snapshot of TENG, so our plan is to shut it down and try a restore using the snapshot. We still feel that this is the key thing we must be able to do before putting 10gR2 into production, that is, we must be able to restore the db with the most recent snapshot, then recover it to real time. And, we must be able to do this post haste.

Anyway, we are pushing forward on this project.

Posted by rossm at 11:05 AM | Comments (0)

February 16, 2007

Playing With 10g

This Thursday, Dale and I met for our Thursday morning staff development time. You know we were anxious to tear into the 10g database and see what we could do. The Oracle Enterprise Manager (OEM) for 10g is web based, and quite comprehensive. For years I have been trying to get the 9i OEM to run as more than a stand alone, with no luck, and here we have a really nice OEM interface right out of the box.

So far, Dale and I have performed a number of DBA management tasks on the 10g database. We have created several LMT's (locally managed tablespaces), created a bunch of new users, imported some fairly decent sized tables, and dropped and recovered a couple tables using Flashback - another 10g improvement. Starting from scratch it took us less than 5 minutes to restore a dropped table. Very nice!

One other thing we looked at on Thursday was the Metrics section. There is a huge amount of data kept on the database, and it is somewhat confusing, however, we did manage to reset one metric limit to keep it from throwing an alert every two minutes. The metrics area will be a lot more useful once we have a production instance going.

Posted by rossm at 2:44 PM | Comments (0)

February 14, 2007

AERO Crashes

Last Tuesday afternoon our production Oracle database crashed. Persons attempting to log in received an error message, and those already logged in lost all functionality. The error message we received was that the maximum number of sessions had been exceeded. However, the database did a core dump and created several trace files at the same time. So, I opened a Service Request with Oracle and sent them the files for review.

It turns out that our database is experiencing a known bug that affects the shared pool. Newer versions of the database don't suffer from this bug. There is a temporary bug fix available, however, the fix could be worse than the bug. At this time, we have decided against applying the bug fix, and are hoping to resolve the issue by simply upgrading to 10g.

Posted by rossm at 3:21 PM | Comments (0)

Oracle 10g Release 2

We are well underway in our efforts to upgrade the WOU production database to Oracle 10g Release 2. Travis has set up a server for us, and we installed the database on it last Friday afternoon. The database is up and running and about four UCS techs have DBA privileges on it.

We have been impressed with the web-based Oracle Enterprise Manager (OEM). You can perform virtually all database administration and management tasks from the web interface.

The next steps for us are to install a copy of our production 9i database on Maverick, test and perfect the upgrade process on Maverick, then do the production upgrade.

Posted by rossm at 3:15 PM | Comments (0)

November 7, 2006

It's Lights Out for WILB

When I first began delving into the depths of Oracle databases, I had the opportunity to work on upgrading our Oracle Enterprise database version from 8i to 9i. Doesn't sound like much, but it was quite a project involving the installation of the new database and a migration of the 8i data to the 9i platform. That 9i database was named WILB (after the server it was on - Wilbur as in Orville and Wilbur Wright). It served as the production database for WOU for several years.

Time passes and WILB was replaced by our current production database named AERO. AERO was essentially the same Oracle database, just moved to "bigger and better" hardware. Well, moving all of the applications and data from WILB to AERO was an enormous job. I think everyone in UCS had a hand in it one way or another. As just one small example, Summer had to rewrite the entire Admissions application (these are all web applications - so the application that allows prospective students to apply online) as there were just too many changes needed to move it.

Well, to make a long story short, I shut down WILB earlier this week. A couple users noticed it because there were some outdated links on our web page, so they could no longer log in as they were pointed to WILB and not AERO as they should have been. Ron fixed those links straight away, and now WILB is just a memory.

Just so you don't get the idea that we're now finished with anything, the current plan calls for an OS upgrade to Solaris 10 on the Wilbur server, then an installation of Oracle 10g (Oracle's latest-greatest enterprise level database). The new WILB database will be coupled with an upgrade of AERO (also to 10g) then the new WILB will be used as a development database so that AERO will have only production level applications running on it.

Anyway, it's farewell to WILB, but it will be back in a new and better way in the future. Stay tuned....

Posted by rossm at 10:09 AM | Comments (0)

April 12, 2006

Oracle 10g Development Takes a Hit

I found out this week that Troy is leaving WOU for greener pastures. I wish him the best, and it's easy to understand his reasons for leaving - such things as higher pay and cutting about 45 minutes off his daily commute, each way. Still, it's important to know that this is a significant hit to our development of Oracle (this isn't the only thing that will be impacted). Troy has developed some great skills in setting up servers to run Oracle, installing databases, and administering databases. We will miss you, Troy. I wish you good luck, smooth sailing, and following seas.

Posted by rossm at 10:07 AM | Comments (0)

March 29, 2006

Checking Out 10g

Troy has set up a very nice test instance of the 10g database for us to play with. This week we've spent several hours exploring such things as "flashback" and complete database restores using Oracle Enterprise Manager. The flashback feature of 10g is worth the upgrade all by itself. It's kind of like the Windows recycle bin. If a table is accidentally dropped or the data is somehow lost, it is a fairly simple and quick way to restore the lost object. The table can be restored in literally a minute or two. Thinking this was so nice, we also tried dropping an entire tablespace. This didn't work near so nice, as Troy wound up eventually having to reinstall the database the first time we tried to restore the tablespace. We have now managed to recover the database after a tablespace was dropped, but haven't yet been able to replace the missing tablespace. Also, we looked at recreating a dropped package. This is different from other objects in that a compiled version of the package is stored in the database in binary form, and the text of the package is stored in the SYS tablespace. We looked up the table that stores the package text, and Mike Ellis is going to work on a job that backs up this table each night, then write a routine that will recreate any dropped package from the saved table. This means you would lose at most one day's work on a dropped package. (I recommend keeping a complete, current set of your packages on one of the network drives, but can see that not everyone is going to keep those up.)

I'm looking forward to getting 10g up and running as our production database this Summer. It has some very handy features especially in the area of recovery and restore.

Posted by rossm at 8:11 AM | Comments (0)

December 23, 2005

Safety Schema Moves

After a lot of tweaking, importing-exporting, testing and coordination with Banner Dale, Mike S. and I managed to move the safety/parking schema over to Aero. This was one of the thornier applications to move because it not only has links to Banner, but also links from Banner back to the application. Brian W., Mike S. and Mike Staats all performed the Banner related tasks. Dale placed the redirect in the login package and also put some email monitors throughout the Wilb app in case we missed something. I did an export/import by hand, using a parameter file that looks like this:

GRANTS=N
IGNORE=Y
INDEXES=N
LOG=safimp.log
TABLES=*

It's pretty straightforward except the IGNORE. This entry means "Yes, ignore object already exists error." In this way, I had earlier set up all the tables, grants, indexes, constraints on Aero the way I wanted them and tested it all out. Then, I just truncated the tables, did the import of the table data into the already existing tables, and did not mess up the framework. It worked very nicely.

Another item that was interesting was resetting the sequence numbers. This step was overlooked when we moved the schema over for testing and it soon was spawning the incorrect numbers and messing up such things as primary key constraints, etc. It was easy to look at the Wilb sequences, note the next number they were going to give out, then set up the Aero sequences to pass out that same number. This also prevented us from skipping a bunch of numbers for parking permit numbers (which relate to real world parking stickers).

The final (I hope) hurdle was solving this problem: A user reported that some of the values in one of the drop down boxes were missing. Hmmm....A quick check of the tables showed they were exactly the same - no missing records. So, the exp/imp did not drop any data. It also showed that the tables DID have the missing elements in them. What the heck?? So, by passing the mouse over the link I could see the package that was being called to display a new window with the problematic drop down box. The chase was on, because this package called off to the webdb.wwv_usr_static_lov$ table. Well, that table was missing the newer records on Aero, so a quick import of the Wilb counterpart table solved that problem. Easy fix, but a nightmare to find the trouble spot.

Let me just say a word here about the importance of commenting your code. Please, do it! This schema is an excellent example of the pain you cause by not commenting your code. This was written by Mike Lorengo, who did a really nice job of coding it, EXCEPT there isn't one comment in it anywhere. Well, Mike is gone and there is no one here who knows the first thing about how this app is put together. It is no fun wading through all the packages, all the procedures, all the functions, all the tables, all the views, etc. and thousands of lines of code trying to figure out what goes where! A comment that, hey, such and such a table contains all the LOV values would have really helped.

Posted by rossm at 8:51 AM | Comments (1)

August 25, 2005

Movin' Along from Wilb to Aero

The move for our friends in public safety is in its last testing stages. Will, Mike Soukup and I have fixed the errors found by Nancy and Cheri so far, and it looks like we have nearly 100% functionality. Once we are more confident with the stability of the application on Aero, Will and I will move the table data and complete the move. By the way, we are going to try a new method of moving the table data - truncating the Aero test tables (not dropping them) and using a control file for the exp/imp functions which will append the data onto the existing tables. "Why?", you ask. Because these particular tables are a bear to move! In the first place, they need to be moved to a new tablespace on Wilb. Also, we've got the table permissions, data constraints, primary keys, and indexes set up very well, so we don't want to destroy that work by dropping the tables. We'll see if this method helps or is just more work.

Will also discovered errors with the four sequences - not bringing over the correct "next number", which fouls things up when new vehicles are added, or new permits are issued. We are going to just monitor those by hand, and set the "next number" manually since there are only four of them.

One last piece to work out is coordination between public safety's users, and the Banner users in administration. Several Banner packages will need to be modified to point to Aero's tables. Thanks to Brian W. and Summer for handling this portion of the move.

Posted by rossm at 9:09 AM | Comments (0)

August 12, 2005

Wilbur Applications "Moving" Along

Will and I tackled the move of a couple of the toughest schemas from Wilb over to Aero. We're about 90% done! These schemas proved to be quite a challenge as they directly interact with Banner data. The processes for connecting to Banner data are quite different on the two databases. I did manage to "move" a schema that someone else had already moved, thereby stomping all over their work, and breaking a perfectly good application. Brian was actually pretty nice when he called me considering I had just blown away a bit of his handiwork! (I'm now thinking of writing a small app that gives us a bit of information about the myriad applications UCS has written to prevent this kind of error in the future.)

The good news is that Will and I were able to put the added infrastructure in place to enable these schemas to get at the data they need to work. One application is being tested by its users to see if any hidden bugs are lurking there, and we will tackle the final schema next week.

Once all applications are safely moved to Aero, we will be in a position to shut down the Wilb database, and move forward with our ultimate goal of providing Oracle 10g to the campus community.

Posted by rossm at 11:34 AM | Comments (2)

10G Install Successful

Earlier this week Troy and I installed Oracle 10g on a Sun server that Troy had set up. It went pretty easily once we replaced some kernel parameters that the Oracle documentation said were outdated. The Oracle installer erred out when they weren't present, though. We did discover that the Apache web server is no longer installed as part of the Enterprise DB install. No, Apache is now contained on a separate installation CD and requires its own Oracle Home and SID. We're not quite sure why or what the benefit is of this setup, but we will see!

Unlike Oracle installations of old, this install was pretty EASY. Once we got the kernel paramaters straightened out, the DB pretty much installed itself.

Stay tuned for more information on 10g and its benefits! Our goal is to eventually install 10g on the "cluster" and migrate AERO over as our production DB. Sweet!

Posted by rossm at 11:21 AM | Comments (0)

August 5, 2005

We're Moving...........

Will Drury and practically every other programmer in UCS have been working to move all our web applications from Wilbur to Aero. This is a long process as many of these applications are used a lot! Also, some of them were written by students and UCS staff who don't work here any more. That makes moving them more of a challenge. What does this all mean? Why bother?

Well, for one thing Aero is on the Sun cluster and Wilbur is a stand alone server, so all the advantages of a cluster will be available to every application once moved to Aero. Aero has superior horsepower, RAM memory, and storage capabilities as well. It will need them in order to keep up with all these applications!

There is a standing joke among programmers that a particular project is "90%" done. When they report that a project is 90% done, in reality, this often means a project is about half done, but that a host of unforeseen problems haven't cropped up yet. So you can now appreciate that the move from Wilbur to Aero is about 90% done! By the way, once this move is accomplished all will be set for the jump to Oracle 10g (well, that and actually getting the database installed, of course). Stay tuned as we work to keep WOU on the cutting edge of technology for everyone's benefit!

Posted by rossm at 11:44 AM | Comments (0)

WOU - Powered by Oracle

You probably didn't know this, but almost every facet of student life on campus is impacted by our Oracle database (we actually have more than one). A quick count shows about 112 different local applications that use the database. Examples of these applications include:
SWAMP - physical plant's purchasing,accounting, and postage systems
WEBDB - the university's web page
TIMESHEET - tracks student work hours
SAFETY - parking permits and, yes, citations
REDWOLF - residence hall network and internet monitor
LIBRARY - many library services such as reservations, periodicals
KEYS - key checkout data for the entire campus
JUKEBOX - a huge music collection for the entire campus
CAMERAS - security cameras
BOOKSTORE - a portion of the bookstore's web store
BANNER - student grades, financial records and a lot more

Oracle databases are not new to Western, we have used them here for years. They are widely considered to be the best databases available for reliability and speed. When installed on Unix servers, you get truly awesome performance. WOU currently runs Oracle version 9i housed on a Sun/Solaris cluster. Plenty of horepower!

Oracle databases are also packed with features, such as the ability to output HTML. This means you can use the database for web-based applications such as those listed above, and the database can give you data in the form of a web page. A great example of this is the student/faculty/staff search page on the WOU website. Type in a name or part of a name and you are performing a search of data on an Oracle database, reading the results in the form of a web page! Cool!

Stay tuned as University Computing is not letting any grass grow under our feet! Oracle has a new product known as 10g (for grid computing), and we will be making it available for use to the campus!

Posted by rossm at 8:08 AM | Comments (0)