Oracle

Oracle Dataguard Replication Scare

I had to bring down the standby server for the power outage in our DR location a few nights agon. No big deal, shut off replication from the primary, go to the standby and do a shutdown imediate, since you can not do a shutdown on the standby server. When things cam up I go to the secondary and do a startup nomount; and then alter database mount standby database; Well I thougt that was all I needed. About 10 minutes later I get some pages about the primary not being able to connect to the standby… DUH I forgot to start the listener on the standby. No Problem. I run lsnrctl start on the standby host and I am in business now. No more pages all is good. Well I thought so until after the cold backup that we run overnight from the standby database. Now I get a page saying that recover managed standby database cancel can not execute since this is not a managed standby database. I think that is odd and I check to see if archive logs are being applied and they are not and have not been for over 13 huors now. I find that I forgot to run alter database recover managed standby database; I run that command and look at that. Now my Archive logs are being applied. It took about 4 hours to get caught up but it did. So when bringing up the standby just do the following:

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANGED STANDBY DATABASE;

If you have enabled the primary to send archive logs then you are all set.

Oracle Upgrade Final Test this weekend

The Oracle 9 to 10GR2 upgrade that I completed last week will be getting its final test on Sunday. We will be failing over to our standby server and see what happens. I am not worried about this as all the files that are associated with the upgrade are on disk that is shared between the two cluster nodes. The only exception is in /var/opt/oracle and I have modified those files to be what they need to be. Keep your fingers crossed for me but I think I am in good shape.

Oracle Upgrade Complete

Last Sunday morning I came in to work at just after 2:00 AM and did the upgrade on our Production Oracle system. Since I had done this a few times on our test system I was ready for anything to happen. I had already installed the new version of Oracle and so I brought the DB Down changed my $ORACLE_HOME and went to work. Launched the DBUA and saw no instances available for upgrade. I closed DBUA and went to my oratab file and I saw that my current 9 version entry was in the file. Also had some commented out instances. I tried again and had the same result. Back to oratab and I deleted all the commented entries out. Now I can see my instance in DBUA.

Let DBUA do its thing and I was done in less than 2 hours. Since I was prepared for anything, almost nothing went wrong. We have been live with Oracel 10.2.0.4 since then and have had no issue.

Oracle Upgrade Complete

I was able to finish up the Oracle Upgrade that I wanted to do today. First I removed the previous installation of Oracle 10 on the server. I then installed the software and all patches that I needed. I then changed my variables to reflect the new Oracle Home Directory that I wanted to use. After that I launched the dbua and walked thru each step, I did not move the files during the upgrade so it was an in place upgrade. I did have a good backup that I could fail back to if needed. This is very important. After a couple hours the upgrade had finished. I still had to copy over the tns folder and change the listener.ora file also to reflect the new Oracle Home. I also needed to change some parameters in the spfile.ora. The application that uses this DB has provided a tool to edit the spfile directly. I edited the file and I started the DB with no issue. This took me just under 4 hours and that was my target time. I think that I am ready to upgrade our production servers and feel confidant that I will not have any issues that I can not overcome.

Oracle Upgrade Testing Once Again

Today I will again embark on the journey that is Upgrading Oracle. This will be the third and final time that I do this on our test system in preparation to upgrade our Production Cluster that also has a DataGaurd Backup solution. I expect this process to take a about 4 hours including the removal of the current Oracle 10 software that I have installed on the server. I am running through this from the very beginning and expect to hit no road bumps this time around. If all does go well this time then I will start to plan how I am going to handle the production environment and how much downtime I am going to require. I will require downtime since we are doing an in place upgrade. It would be nice to have enough disk to install 10 on one Cluster node and upgrade the Database files while moving them to a new disk location and then failing over the cluster once that is complete. I, however, will have to upgrade the files in place and use only the DR server as a backup. That should not be a problem though. I think I will upgrade one node bring the DB up and then install Oracle on the second node. Once that is complete the Cluster should be done. Then in a day I will upgrade the DataGaurd server as well and re-enable the replication.

Perfstat Cron Jobs

Here is the update on changing the perfstat statspack job that I spoke of earlier. The question was how can I get statspacks to run every 15 minutes during OLTP and every 2 hours during batch. My thought is a Cron Job that will run the SQL for you. Just make a connection to the DB using SQLPLUS and call the SQL file with the appropriate sql to change the job. Since the user asking the question had the SQL done here is a sample of how I would write the script to run in Cron.

#!/bin/sh
sqlplus -S User/Password@$instance @/full/path/to/file/filename.sql

That is it. Pretty straightforward really. Hope that helps. If you have any questions about this or anything else just head over to http://sysadminsmith.com and click the ‘Submit a Question’ link and I will get back to you.

Changing Perfstat Job

I got a question about how I would change the Prefstat Statspack job in Oracle to run at 15 minute increments during the day when OLTP transactions are occurring and then change it to run only onve every hour during off hours while batch is running. Since the user already had the PL\SQL to change the job my answer is to build that SQL into a shell script that is scheduled in CRON. Have two jobs. One that changes the time to once an hour and one that changes back to every fifteen minutes. In the script just make a connection to the DB, run the SQL, Log the changes and perhaps build in some logic to alert you if the changes were not made. Sounds pretty basic and I will try to work out the shell scripts and have them posted tomorrow.

If you have a question please head over to http://sysadminsmith.com and click the ’submit a question’ link to the right. Thanks

Oracle Upgrade Cont.

I have completed the Oracle Upgrade on our test system and then rolled back to Oracle 9 and upgraded the DB again to 10.2.0.4.

This process was pretty straightforward. The company whose application uses this Database has some useful tools to utilize during the upgrade process. One gives you the ability to modify the SPFILE directly without causing any issue. I would not normally do this but the tool is very nice. I used the DBUA to complete the upgrade and I upgraded the Database in place. That is to say that I did not have to copy the Data files to a separate location before doing the upgrade.

I ran into two snags on the second run and both were easily resolved. First is the Listener, I was not able to start or stop it. It kept telling me that the password was not recognized. Now I am not sure why this listener has a password on it as this is not the standard setup for us. I could see that the password was in the listener.ora file and not encrypted. I tried to change the password, and I tried to pass the password to lsnrctl but it kept telling me that it was wrong. To resolve this I finally ps –ef | grep lsnrctl and found the listener process. I killed it. I then was able to do a lsnrctl start and the listener started. I am now able to do anything I need to the listener. Just a strange and frustrating issue.

The second is that the DBUA put the ARCHIVE_MODE into a NO ARCHINVE state. This caused an issue when doing a hot backup. The Hot backup script is custom made by the application developers and requires that the ARCHIVE_MODE be in ARCHIVE. I had to bring the DB down and do a startup mount. Then I was able to change this and restart the DB again. Now everything seems to be working and I will be starting the upgrade on our production cluster and failover standby database next week.

If you have any questions let me know by leaving a comment or head over to http://sysadminsmith.com and click on the submit a question link on the right.

Oracle Upgrade

I did my Oracle upgraded yesterday and overall I think that it went pretty well. We are still testing but so far so good. The process was not too bad. First you need to create a new $ORACLE_HOME as two Oracle installations can not be in the same $ORACLE_HOME. Then install the media. I did run into one snag here, it did not want to install on the Solaris 10 OS 64 Bit. After some looking we found this to be a bug and we found a resolution. Then I installed items from the companion CD. This is was tricky as well. I selected the companion cd products and I could not install them into the same $ORACLE_HOME. I kept fooling around with it and found that if you choose Oracle 10g products then it installs just fine and does install the items from the companion cd. All I wanted from the companion cd was JAccelerator but it forces you to install all 20 products. LAME. After that I did all my patching and I did not install a new Database. This is because I want to upgrade an existing DB. After that I ran the Database Upgrade Assistant and that is really all there was to it. So if anyone is upgrading and has any questions please let me know by going to http://sysadminsmith.com and clicking the Submit A Question link on the right.

Oracle Upgrade

It looks like I might get the chance to upgrade one of our Test oracle systems today. I will be moving from 9.2.0 to 10.2. This should be interesting as I am still pretty new to Unix and to Oracle. I am looking forward to this and it really looks pretty straight forward. Looks like I will have to migrate to a Locally Managed Temp space from Dictionary managed and then do the actual upgrade. I will be using the Oracle upgrade assistant and I do not think that I should have any issues. I am doing this on a Solaris 64 SPARK server. I will keep you updated as I work through this task.