Recently in Oracle DBA Projects Category

Mike and I have spent considerable time working through the many details needed in order to prepare for our upgrade of the AERO database to Oracle 10gR2. Mike and I, as well as several others within UCS, will come in during the late evening on Thursday, March 6 (hey, that's tomorrow) and may work through the night if needed, in order to complete the upgrade.

As I said, Mike and I have been working through many different tasks in order to prepare for this upgrade. This morning, he and I worked on a text file to be used as a script to move the data files (.DBF) to their new location for the upgraded database. Yesterday I worked at a command prompt to gather statistics for all of the current schemas within the 9i database. It was best to do this before, rather than during, the actual upgrade.

Mike will give me several tasks to complete during the actual upgrade project. It should be fun!

Mike and I spent considerable time on Friday of last week working through more of the details in preparation for the upgrade/migration to Oracle 10gR2. We spent some time editing some of the configuration files that I had backed up earlier on Maverick. We will eventually copy these updated files into the 10g production database on Sundown. We also successfully copied a tablespace from the production Oracle 9i database to the test 10g database and were able to view a page within that tablespace from a web browser. In our preparation work thus far, everything has been very smooth; here's hoping this trend will continue when we go for the Gold and upgrade the database for real!

Mike and I have spent time over the last few weeks, when he hasn't been involved with the Astra project, working with the migration of AERO to Oracle 10g. I have helped research (Google) errors as well as other issues that have surfaced. I've gained a greater familiarity with Oracle's web-based OEM interface, as well as more experience working in the UNIX- and SQL Plus-based environments. We're currently working on the configuration of the development database and will soon migrate the production database. It will be nice to get this project completed, as we've had other projects compete for our attention over the past several months.

Mike and I spent more time this week working with the setup for Oracle 10g. We decided to spend some time "cleaning up" the installation and making sure that the configuration options are pointing to the actual 10g installation, rather than the old 9i.

Specifically, we configured the redo log groups to point to the Oracle 10g directory structure on Maverick. As this was previously pointed to the 9i directory location, this was a significant configuration change. Once we created the new redo logs within the 10g environment, we successfully removed those pointing to 9i.

We were successful in our endeavors, as the database remained running and stable the whole time. I know, as we continue working with the 10g upgrade project, that we will find more settings to change accordingly. Unfortunately, the Oracle documentation in general isn't that great.

This morning I decided to experiment with the flashback/recovery capabilities that we have configured on the Oracle 10g instance of AERO running on Maverick. I logged in to SQLPLUS (the command line) and dropped two tables from she same schema. Here are the steps I followed to recover them.

● Login to Oracle 10g Enterprise Manager (OEM).
● From the tabs (links) near the top of the page, click Maintenance.
● Under the Backup/Recovery section, click Perform Recovery.
● In the Object Level Recovery section, select Tables for the Object Type.
● Operation Type: click Flashback Dropped Tables.
● Host Credentials: enter the Operating System username and password.
● Click the Perform Object Level Recovery button.
● On the next page, under the Search section, enter the schema name where the table(s) are to be recovered and click the blue "Flashlight" icon immediately to its right.
● In the popup that displays next, select the schema name and click the Select button.
● Now, in the Results section, select the table(s) you need to recover and click the Next button near the far-right side of the page.
● On the next page, again click the Next button near the far-right; it isn't necessary to provide a new name for the recovered table(s).
● In the next page, the Impact Analysis window will show a summary of the recovery operation about to be completed; just click the Submit button near the far-right side of the page.
● Next, a confirmation page should display, indicating that the table(s) have been flashed back from the recycle bin; click the OK button near the far-right side of the page.

The tables were recovered successfully. Mike and I will need to spend more time testing this functionality, but at least we know the feature works.

Mike and I spent most of the morning working on some "clean-up" of the AERO Oracle 10gR2 database running on Maverick.

The first major task we tried to complete was to change the location of the default tablespace. Apparently the default tablespace, as part of a standard 10g installation, is assigned to SYSTEM. Mike wanted to reassign this to DEFAULT_AERO, a new tablespace that we created. After doing some research, Mike had me run the command

alter database default tablespace default_aero;

In troubleshooting the resulting error message, we discovered that many of the 10g settings were actually pointing back to the 9i version of AERO (an instance we have installed on Maverick, used as part of our upgrade project). We ended up shutting down and restarting the 10g database many times throughout the morning, as we needed to work on getting the various settings pointed to the new 10g installation.

A major discovery for us was learning how to properly make changes to the SPFILE. Apparently, although you can edit the SPFILE using Pico, in reality this will corrupt that file. We discovered that we really needed to edit the PFILE, then issue the command below from within SQLPLUS

create spfile from pfile;

Of course, all of this involved shutting down and restarting the database several times as we made several changes and verified that they were in place.

Once we had the SPFILE configured properly, we were able to change the default tablespace as we had originally planned.

We also wanted to enable archivelog mode within the 10g database. To determine whether or nor this had been enabled, I found this command:

archive log list;

To enable archivelog mode, we issued this command:

alter database archive log;

We believe we're now ready to try to start the 10gR2 instance of AERO with the 9i instance "deleted", as we think we've changed all of the settings to point to 10g. In order to cover our tracks however, we'll have Travis simply rename the directory containing the 9i instance for now. In case we can't start 10g, we'll be able to get the 9i instance back to a known state.

In working with these settings, we both learned a great deal.

Mike and Travis have had several meetings this week to work on the installation of Oracle 10g onto Maverick. As they have 10g up and running, Mike and I have been working on the upgrade from 9i to 10g the last couple of days. The upgrade instructions consist of an 18-page document, of which we have worked through about 11 pages. I have helped Mike do research. What would we ever do without Google and, of course Oracle's Metalink resource? Putting our two heads together, we have been able to resolve most errors and challenges as they surface. As of now, however, when starting the actual upgrade of the 9i database to 10g, we've hit a significant roadblock when we issue the actual upgrade command (following the instructions from Oracle). Mike submitted a service request to Oracle; hopefully we'll get some new information in order to proceed next week. With this project, I have gained experience working with the OEM interface of Oracle 10g as well as the command-line interfaces (Sun Solaris and SQLPLUS). Working with OEM in 10g, I successfully changed the tablespace for my own login. Through the process of trial-and-error, I had to delete then re-create my own login to get the tablespace configured properly. This has been a fun project, and I've learned a lot. I look forward to working more with the Oracle 10g database in the future.

Mike Ross and I have had some good discussions about upcoming DBA-related projects. Of course we're anticipating the Banner Consolidation Project and the work we'll need to do to make sure that everything within AERO is working properly. We may need to recompile any "broken" packages and views that contain links to Banner. Mike has also been working with Travis this week on the installation of Oracle 10gR2. Mike and I downloaded the software earlier in the week; he and Travis have been working on the actual installation of that software and the preparations to migrate the Oracle 9i database to Oracle 10gR2. We anticipate that the installation of the Astra calendar application will require an Oracle 10g database, so this has actually been a great time to move forward with 10g.

Yesterday Mike Ross and I spent time going through some of the Oracle trace files and Apache logs on the Sundown server. We investigated some of the RDBMS alert logs and UDUMP trace files and did some clean-up (i.e., deleting the older trace files that are no longer useful). Within the UDUMP trace files, we could see bad SQL statements from PL/SQL packages and their resulting errors within the database. Specifically, we found a deadlock within the registration schema and the application that tracks the seats available within a course. For whatever reason, in coding several of the UPDATE SQL statements, the developer neglected the appropriate "COMMIT" statements; in an attempt to resolve the deadlock, Mike added these to the code. It would be nice if this quick fix was all that is needed to resolve this long-standing (and annoying) deadlock. We'll continue to monitor things to see if we were successful.

Mike Ross and I met on Thursday morning to take a look at some of the logs created by the Apache web server running on Sundown. Mike had me do much of the work, logging into the UNIX box, going out to the Apache directory and deleting the log files that are no longer needed. As my UNIX experience is very limited, this work served as an excellent review. We look forward to many more opportunities to work on similar tasks, hopefully improving the performance of the Oracle database and web server as well as allowing me to improve my UNIX knowledge and skills.

It's been several weeks since Mike Ross and I have met to work on DBA projects. Yesterday he gave me a demo of the COED application, and the progress that he and Summer have made over the last few months. Although the application is still in development, they have made many improvements so that the interface will be more user-friendly and will run more efficiently within the Oracle database. Within COED, they have implemented some of the kinds of security and authentication through LDAP that I have in place within the Scheduler Application.

This morning Mike Ross and I met to do some troubleshooting of the database running on AERO. Mike has noticed that recently the overall performance of the database has degraded to a point where we need to try to figure out what's going on.

At first, we took a minute to review the BIND_VAR_HISTORY table that we set up several months ago. While there were a couple of entries from this morning with higher numbers than we really wanted, Mike wanted to spend some time working on tuning of the database using STATSPACK and submitting this to the Burleson statspackanalyzer.org site for analysis.

Mike and I each wrote a blog back in November 2006 about this topic.

�? Click here to read Mike's blog.
�? click here to read my blog.

Here's an article that contains detailed steps for generating a STATSPACK report:

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm

Here's a brief summary of the steps we followed:

�? Login to the database as perfstat
�? execute statspack.snap; (including the semicolon) -- we took two snapshots, 15 minutes apart.
�? Issue this command after creating the snaps: e?/rdbms/admin/spreport

I'm sure we'll continue with this project next week.

Mike Ross and I met this morning to continue our recent programming projects. As it's been a couple of weeks since we last met, he had a lot to show me. He has completely redesigned the large form that we've been working with, and the user interface is now much simpler. As the form is being used to display live data, as well as to allow for updating of the Oracle tables, the PL/SQL code is somewhat involved. We worked on the code to display and update just one field, and it took some time to get it to work the way Mike had intended; he thought it would be good to have "two heads" involved in the project. I learned something new this morning as well: another way to loop through a cursor, without using the actual "fetch" keyword.

Mike Ross and I continued our work with Javascript as a validation tool for form data. Within the large form we've been working on lately, we are making progress on the validation of the many fields.

We discussed how we might use arrays in PL/SQL to efficiently display and capture specific test scores; for one test with a possibility of data being entered for up to nine actual exam attempts, we'd like to devise a method to only display the fields for attempt 1 by default, then have the form display fields for attempt 2 only when data for attempt 1 has been entered, and display subsequent attempts similarly.

We also talked about how we could present the form to "compress" sections that are not actively being edited by the user. At the time a section is "compressed", data contained within that section would be updated within the database. When the user decides to go back to a prior section, it would "expand", showing the current fields from the database.

We'll continue working with this project next week and will decide how to pursue these goals.

Mike Ross and I have been working to resolve some bugs within a large form used to submit and update student information to a database. We've worked through some intense Javascript that validates the form data at the time the user clicks the Submit button, just before the data is actually inserted into the tables. For invalid or incomplete fields, a message box indicates the specific error and the form remains loaded in the browser. We've spent considerable time working through just the radio buttons, determining whether or not the user has clicked a choice. Between the two of us, we now have the radio buttons working properly.

Mike Ross and I spent much of today working on some Javascript code and passing parameters to a procedure that will submit a long form and insert and/or update records in several Oracle tables. After accepting many of the fields as input through Javascript, we're passing them on to the next procedure in the form of hidden input tags. We're working through a somewhat long list of parameters and using the resulting PL/SQL error messages to verify the values of each field. Yes, even the lowly error message can be a useful tool!

During our weekly meeting yesterday, Mike Ross and I did some work with some tables within an application that we'll be modifying in the coming weeks. We made copies of a few of the existing tables and created some sequences to be used to recreate key fields. We even wrote a little PL/SQL procedure to automate most of this. I'm sure we'll continue with this phase of the project next week.

We also took a few minutes to check the tablespace usage within AERO. It had been some time since we had checked this, so we were expecting to make some major adjustments. As it turns out, however, the usage seemed to be acceptable, and we only made a few minor changes.

In our meeting yesterday, Mike Ross and I were interested in finding a way to parse a string, delimited by the carret (^) character. Searching Google and Wiki, we found some code that we were quickly able to run against a test string. We'll need to parse such a string within an upcoming project, so this little exercise helped us to conclude that it can, in fact, be done.

Yesterday Mike and I worked with the backup and recovery procedures within Oracle 10g. In the end, we successfully backed up and did a point-in-time recovery of the entire database, but not without a few technical difficulties along the way.

We discovered that the database didn't have a PFILE specified. In working through the steps to establish this, we had to experiment with the commands, and finally figured out that we weren't providing the needed path. Once we gave the correct path, we were able to complete this configuration. With the PFILE setting now properly configured, the backup and recovery went well.

I experimented this morning by doing another point-in-time recovery. I was eventually successful, but had to overcome some problems authenticating to the database and the host. I concluded that I had tried to refresh the OEM web page too quickly after shutting down the database in the beginning.

Mike and I need to spend more time working with backup and recovery procedures. I'm still not completely clear, for example, with how the database completes the point-in-time recovery.

This morning Mike Ross and I met to import new graduation data into the Viking application, used by University Advancement. Mike guided me through the entire process which, for the most part, seems relatively straightforward. As my login doesn't have all of the required permissions to copy the files, he used his login instead. I'm not an advanced UNIX user, but I had enough prior knowledge to understand what we were doing and I had Mike clarify some of the specific commands we entered. This was a good learning opportunity!

In our meeting yesterday, Mike and I discovered that our test database, TENG, was running in NOARCHIVELOG mode. We need the database to run in ARCHIVELOG mode, so we decided to try and fix this. We had great difficulty attempting to make the necessary configuration changes within the OEM interface. Through further research, Mike found some command-line entries to turn ARCHIVELOG mode on. We learned that it is best not to manually edit the SP file or the INITTENG file.

Mike Ross, Michael Ellis and I met yesterday to delete our first installation of Oracle 10gR2 and reinstall the software once again on Maverick. We wanted to establish a new SID for the database, TENG (pronounced "ten G"). Since we plan to do a recovery of the Oracle 9i AERO database onto Maverick, we felt it best that the two databases running on the same UNIX server not have the same SID.

Mike Ross assigned several tasks to me, which I completed later in the day. I created a user and tablespace for Alex, I created a user and tablespace for SWAMP, and I imported a few tables that Mike had exported from the production 9i AERO last week for testing.

Creating the users and tablespaces using the web-based OEM was a good review, as I had done this before. I tried to figure out how to import the tables using OEM, but didn't get very far. Mike suggested that I use PL/SQL Developer instead and login as the SWAMP user. I have imported tables before using PL/SQL Developer, so I knew this would work. When Mike exported the tables last week, he saved them to a .DMP file. This file format was incompatible with my version of PL/SQL Developer (I'll upgrade my local copy in the next day or so), so I decided to login to the terminal server and use that copy. Once we updated the TNSNAMES file to include an entry for TENG on Maverick (thanks Joe), I was able to successfully import the tables.

For our meeting today, Mike and I began exploring the web-based Oracle Enterprise Manager (OEM), which is part of the Oracle 10gR2 installation that he and Michael Ellis completed last Friday. We used OEM to work through some very basic DBA tasks. We were both very impressed with the interface as a whole, and the ease with which we were able to handle tasks. Mike guided me through several of the tasks we completed, and I learned a great deal through his experience and expertise.

Here's what we did:

► Created two tablespaces, ROSSM and SWAMP.
► Created two users, ROSSM and SWAMP.
► Created a simple table under Mike's ROSSM schema, added some test data to that table, then dropped the table.
► Used OEM to "flashback" the deleted table to restore it and the data.
► Imported a couple of tables from the Oracle 9i SWAMP schema to the new 10g SWAMP schema.

We were both very impressed with the capability to restore the deleted table, as we were able to do this in less than five minutes. It'll be interesting to work through similar exercises with a test instance/copy of our production database.

During our weekly meeting this morning, Mike Ross and I read through an Oracle Metalink document (316889.1, Complete checklist for manual upgrades to 10gR2) that he found which describes the detailed steps for upgrading any version of Oracle to 10gR2. Having skimmed these instructions this morning helped us to feel more confident about the project. I plan to spend some time in the next day or so reading the instructions in more detail.

Several of us met yesterday to begin planning for our upgrade and database migration to Oracle 10gR2. We will all be involved with most stages of the project, and hope to have everything in production by July 1. We had a good discussion about many necessary technical details that will all need to fall into place, involving everyone's expertise -- from OS and Oracle installation details to the migration of the database -- all will have ample opportunity to contribute to this project.