Database

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.

Sybase Instance Scare

Monday morning my company was putting in a new UPS at our DR facility. This is great but it means that all database instances that are running at that location are coming down as power to the entire site was down for 2 hours. No big deal right, I got a list of the instances and shut them all down properly before the host machines were shutdown. A couple hours later I get a call saying that all the power is back on and that all my hosts are ready as well. I login and I start to run my startup scripts for all my Sybase instances. I run into two servers that are not starting up and this is what I find in the log files.
The configuration area in master device appears to be corrupt.
NOT COOL… I know that I have backups so that is OK but Master really in two instances… I dig around a bit more and I find that the two instances that are having issues run on the same physical host but are in Solaris Containers that use Veritas Disk mounts. I google the error to see if it may be disk related and not corruption in Master. I found it.

I called my storage guy and he looked into it. Called me about 30 minutes later and asked me to try again. WOW everything came up and I was a happy camper. Sure made for a long morning though and also made me realize the benefit to practicing DR situations. I think I will work with my team to have outages on out Test instances just so we can rebuild Master. It will be good to know and be practiced up on if it is ever not the disk.

Add a AD Group to MSSQL Server and add to a role using Powershell

In SQL Server 2005 the Builtin Administrators group is a login, by default, to your MSSQL Server Instance and is also a member of the sysadmin role. Well this has been changed in SQL Server 2008 with good reason. I decided that I would like to add a group for my DBA Group to every SQL Server in the company and also add this group to the sysadmin role. This way I do not need to have the builtin administrators group be a member of the sysadmin group or even a login to the server. Here is how I did it.

$servers = Import-Csv “csv listing of all servers”
#########################################################
foreach ($entry in $servers)
{
    $torp = $entry.TorP
    $mon = $entry.monitor
    $machine = $entry.server
    $errorlog = $entry.errorlog
    $os = $entry.os2000
    $iname = $entry.Instance
    if ($iname -eq “Null”)
    {
        $instance = “$machine”
    }
    else
    {
        $instance = “$machine\$iname”
    }
    if ($torp -eq “Prod”)
    {
        $ServerType = “Production”
    }
    else
    {
        $ServerType = “Test”
    }
    $instance = $instance.toupper()
#########################################################

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null   
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance

$logins = $s.Logins
$query = “CREATE LOGIN $sqladmin FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq $sqladmin}
#$dba
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq $sqladmin}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}
if ($dba -ne $null)
{
$sroles = $s.Roles
foreach ($srole in $sroles | where{$_.name -eq “sysadmin”})
    {
    $srole.AddMember($sqladmin)

}

This script walks through the list of all my instances and adds the login using sqlcmd and then also adds that new group to the sysadmin role. Let me know if you have any questions or need anything at all.

Another New Language

My team is going to be developing a new application that will handle all of our monitoring. Currently that includes the monitoring of Sybase Servers, Oracle Instances, and MSSQL Servers. We will also be adding another Database Type that will house all of the Monitoring data. We will be using a MYSQL backend for this application. This application will be developed to run on a unix host so I need to be able to connect to my MSSQL servers from the unix host and insert rows of data back into the MYSQL database. To do this I think that we have decided on Perl for the language of choice. So I now get to go learn Perl. I am excited to learn another language as I am of the befleif that the more tools I have in my box the better off I will be. I will be starting my educational journey of Perl at

 http://www.perl.com/pub/a/2000/10/begper…

I am looking forward to this project and I will keep you up to date on the progress and features.

Update sysindexes table in Sybase 12.5.4

I am moving a Sybase instance from one host to another. I have done all the installation and patching and I have loaded all the databases with little to no issues. I ran into a big issue when attempting to change to indexes in sysservers to allow duplicate rows. I had to go and find out what the binary value for the setting that I wanted was and then find out what column held that value. I determined, from looking at my current instance, that I needed the Status colunm to be changed to 64. I then verified this by looking here at the definitions for the table.

 http://infocenter.sybase.com/help/index….

I attempted droping the indexes so that I could just recreate them but you are not able to use Drop Index when working with system tables. That is when I decided to find the value and the column to update. So then I ran a query:

Select name, status from sysindexes

where name = ‘csysservers’

and status = 146

go

This returned on row for me and I thought now I can run the update

update sysindexes

set status = 40

where name = ‘csysservers’

and status = 146

go

Now I get the following error:

Cannot update more than 1 sysindexes row at a time.

Well this did not make sense at all. When I run the select I only get one row returned. But I took the advice of the error and modified my where clause to include more columns and then I was able to make the updates that I needed.

MR. Denny has a webcast

I just saw this and I am looking forward to attending. MR. Denny is a great SQL Resource and I am sure that I will learn new things during this webcast. Here is a link to his blog entry about the webcast.

 http://itknowledgeexchange.techtarget.co…

I suggest you check it out and learn something new from a knowledgable SQL Server DBA.

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.

Powershell Ping (Cont)

Earlier this month I posted about SQL Ping Servers Script that I was working on to notify different groups if a server was down or just SQL Server was down or just the agent was down. Well here is the next part. This is the Ping_Interface Function of the script. This goes out and does a normal dos ping and looks at the return value. Sends only 1 packet and checks for 0% loss to be in the return value. Anything but that and it fails. If it fails it checks the ping_failure variable. If that is 0 then it knows to send the notification out and it puts the touch file on the filesystem so that on the next run it knows that it sent the notification out on the last run. Windows team did not want to be overtly notified that a server was down. Not my idea. So here is the function and if you have any questions head to http://sysadminsmith.com and click the ‘Submit a Question’ link.

##Ping_Interface

function
Ping_Interface

{

echo
“nt users are $ntusers”

$pingresult
=
ping
$machine
-n 1

if ($pingresult
-like
“*(0% loss*”)

{

## Server Interface is responsive

## Remove Ping Failure file from monitoring folder if ping is successful and file exists.

$pingfail
= 0

if (test-path
“i:\$folder\ping_Failure.txt”)

{

del
“i:\$folder\ping_Failure.txt”

}

echo
“Ping Interface is succseful for $machine” >> “i:\OUT\ping_with_service_check.txt”

Check_Services
## Check to see if the SQL Server and the SQLAgent are running via WMI

}

else

{

## Server Interface is non-responsive

$pingfail
= 1

if (!(test-path
“i:\$folder\ping_Failure.txt”))

{

New-Item
-force
-itemType
file
-path
“i:\$folder\ping_Failure.txt”

}

## Check if this is first time and if it has been more than 6 hours since notification was sent.

if (($pingfail
-eq 1) -and ($pingnoemail
-eq 0))

{

$users
=
get-content
-path
“i:\$folder\email”
|
Sort-Object
|
Get-Unique

$users
=
$users
+
$NTusers

$Subject
=
“Unable to Ping $machine”

$body
=
“$machine is not responding to Ping. $pingresult.”

echo
“Ping interface failed for $machine, Sending notification to Systems Team and DBA Team” >> “i:\OUT\ping_with_service_check.txt”

Notify

}

else

{

echo
“Ping interface failed for $machine, notification has already been sent.” >> “i:\OUT\ping_with_service_check.txt”

}

}

}

Error while installing SQL Server 2005 SP 3

I have installed SP3 on a few SQL Servers with no issue at all. The other day I go to install it on a production instance and it fails with the following error:

A Recently Applied Update, KB955706, failed to install.

The update failed with that error for the following services:

Database Services

Notification Services

Integration Services

Client Components

I then went and looked into the log files for each that failed and I noticed some interesting things. Here are the highlights:

Warning: Local cached package ‘C:\WINDOWS\Installer\8510ea.msi’ is missing.

Couldn’t find local patch ‘C:\WINDOWS\Installer\549bc1bf.msp’. Looking for it at its source.

MainEngineThread is returning 1635

This patch package could not be opened. Verify that the patch package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer patch package.

D:\SQL Installer\Disk 1\Setup\SqlRun_SQL.msi

So I started googleing like crazy and I was just about ready to call MS to open a case when I found the best writeup ever on this problem.

http://blogs.msdn.com/sqlserverfaq/archive/2009/01/30/part-1-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx

I followed the instructions here on a clone of the server and everything worked perfect. Now I just need to make sure that no one ever touches the c:\Windows\Installer directory when trying to free up disk space.