March, 2009

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.

Sybase Learning Continued

Today I came back into the office after having a week off. It is always hard to come back into the office but it is nice to get back into the swing of things as well. When I arrived I found some new Sybase books sitting on my desk. Now I know that my company is not going to send me to another class right now in these rough economic times so it was a nice surprise to see the books. Unfortunately, they are the old books of a former employee here. None the less, I plan on reading the books and trying to do as many of the labs as I can. I will be posting in the future about what I am learning here. I have some ASE Performance and Tuning as well as Advanced Administration and Troubleshooting books. I am really looking forward to the Performance and Tuning guide as I would love to make our end users experience better. I will keep you posted on my progress.

Scripting

So I have people ask me all the time how I can get so much done in a day. I have to be honest with you, I do not really do that much. This is because I write scripts to do anything and everything for me if I have to do it more than once I script it. If I think something might be useful I script it. I hate doing the same work over and over. Take something as simple as creating a new user on a domain. This is a very simple task in AD but it takes about 2 minutes per account. It is easy to find a script that will create mass accounts with some generic name. Why not take that same script and make it so it creates one account at a time or ten or whatever you need. You can make it so you type in the name of the user interactively or read names in from a list that you have. Simple things like that. Creating this account only takes 2 minutes in AD but I can do it in under 10 seconds.

When you work in the fast paced world like we do, especially in IT where everyone wants it now now now, every second counts. Saving just under 2 minutes does not seem like a lot but that is the just the beginning. If you save 5 minutes here and 10 there and 2 there then it adds up very fast so you can leave work an hour early, or you just make your boss think that you are that much better and more effective. It does not really matter if you are a Windows person or a Unix/Linux person. SCRIPT anything you can and save time.

I come from a Windows background and in this area the Unix/Linux admins are years ahead of the Windows users. I had done some automation using VBScript when I was a Windows Admin, but when I got into Unix for Database Admin, I quickly learned that scripting is the way. Now with Windows Powershell, Windows administrators can be much more effective in less time. Please learn a scripting language that is of use to you and that you understand. I prefer Powershell but VBscript is a good way to go if you like it better.

Good luck scripting and as always, if you have a question let me know by heading to http://sysadminsmith.com and click the ‘Submit a Question’ link on the right.

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

Free Space or Mount Points

I had a question about getting free space of mount points on a windows server. The user had some Exchange servers with a huge chunk of san disk attached. Then he mounted drive letters into empty folders on that disk. This gave him multiple mount points on that one volume. Now he wants to be able to report on each mount point individually. I do not have any servers that have been set up this way and I am out of the office all week. I did find this post in anther forum though.

PS C:\WINDOWS> gwmi win32_volume|where-object {$_.filesystem -match “ntfs”}|ft name,capacity,freespace

name capacity freespace

—- ——– ———

C:\ 20973137920 12781355008

D:\ 88266911744 30121046016

E:\ 36410552320 11617628160

D:\Mounts\D02\ 1924139716608 1785309392896

D:\Mounts\D03\ 1099506044928 1016254210048

D:\Data\Shared\D01\ 2186130853888 697559801856

And here is a link to the forum that I found it. I have not attempted this as I do not have any servers with this type of mount point but try it and I hope that it works. I am not sure if it will though since win32_logical disk, I do not think it will report like this. I also know that you can try win32_mountpoint and this will get you a list of all your mounts. You may be able to do some work and math using that to figure out the free space. I will try to set this up once I return to the office and see if I can work something out if this does not work.

Interview Update

I had told you that I had an interview with a company here in the valley and I just wanted to let you know what happened with that. The job description online talked about Windows and Exchange Administration. The title was Network Analyst and that sounded strange to me. The title did not fit the job description. I submitted my resume and they called me in. When I arrived they had a test for me to take. This test did have some Windows Administration questions, no Exchange questions, and a ton of Cisco Networking questions. I took the test, sure that I did not do well in the networking area as I am not a network admin and certainly not CCNA. The interview actually did go well and I think that they liked me but I did fail the test. I asked them about the description and asked why it did not say anything about Cisco since they told me they were looking for someone with CCNA. They said they consider it to be one in the same. That really shocked me, I do not think Windows and Exchange Administration have much of anything in common with CCNA Network Administration.

Oh well, I am happy to have had the opportunity to interview with them and perhaps they will call back if another opportunity arises with them. The interview did start me thinking that I perhaps should look into some networking and Cisco training. I know that companies are feeling the crunch just like everyone else right now and the more you know the better off you will be.

Customer Service Rant Cont.

Back on the 17^th I posted a rant about customer service. I was having a problem with a warranty company. I am not going to name the company or the Electronics store that sold me the warranty. After that post I continued to have problems getting the light bulb replacement for my TV. I called and called and complained and had multiple managers of at the warranty company tell me that they would take ownership of my issue. I was promised on two occasions about when my light would arrive but it did not show either time. Finally 3 full weeks after submitting my claim for this easy to replace covered part I did finally get the lamp.

Since then I have been contacted by the warranty company’s upper management and they tell me that this light is free and I can still have two more. That is great but if it is this much of a hassle again I would rather just buy my own I told them. They continued on and talked to me about what I thought should be different in order to make other cases like mine better for the customer. I gave them some thoughts and did not hold back about anything. Since that I have been contacted by two more upper management members, one was from The Office of The President of the company, and have been told that they are putting some of my suggestions into action.

That is what really made me feel that they do care. I do not mind mistakes, we all make them, but when it affects a customer in such a negative manner. I expect that the company, or person, that made the mistake to go the extra mile to make it right. I do not feel that happened immediately with this company but I do now. I had also written the Electronics store telling them that I would no longer do business with them if this is the type of company that they choose to do business with. This store also took action and contacted the warranty group to find out what was going on. That to me also is above and beyond since they do not handle the actual warranty. I am going to mention that it was Ultimate Electronics. They, from the first complaint I made to them, took the initiative to try and work it out for me. They also called me back, not only to verify that things worked out, but also to make sure that they did not lose me as a customer and they gave me a $50.00 gift certificate. This was not necessary but it certainly is appreciated and Ultimate has my business for life after this.

ITKE XBOX Contest

Everyone,

I was just informed about a great contest that ITKE is doing for some free Xbox systems. They are literally handing out three systems. Please check this out, get involved with the community here, show off your skills and get some free bling. Even if you have an Xbox or do not play games you can do so much more with these systems. Get all the details here:

http://itknowledgeexchange.techtarget.com/itke-community-blog/new-contest-a-trio-of-xboxes-for-our-top-three-users/

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.

Powershell VS VBScript

My wife works for a large company as an accountant for a company here in Phoenix and she called me one day asking if I could put a script together that would do some file manipulation and combine multiple files into one. I said no problem. You tell me what you need and I can do it in Powershell. She told me everything that she wanted it to do and provided me with some sample input files to work with. I would put the scripts on here but they are so unique to what she needs that I do not think they would help anyone else. Anyway, I got it done in Powershell and I called her and emailed her the file and said let me know. She informed me that she did not have powershell on her machine at work and that it would not work. I later went to her office and found that they are using Windows 2000 so Powershell is not even supported. I thought well I guess I could do it in VBScript.

I went back to my computer and started hammering out the code. I finished the script and found that I had over 200 lines of code. I went back to my Powershell script and found that I had just over 30 lines. To me Powershell wins hands down just because of that. With the ease of the pipe I am able to write much less code and accomplish so much more.

Let me know your thoughts on Powershell VS VBScript by leaving a comment or heading to http://sysadminsmith.com and clicking on the Submit a Question link on the right.