Sybase ASE 12.5 to 15 Upgradation Steps -II

Upgrade Steps:

Before proceeding with any further steps , please make sure preupgrade step is clean  and you have backup of all databases, tables etc.

1. Make sure you are in ASe 15 enviorment, ( by executing .profile_15)

2. cd to sample resource files directory.

3. copy the sql server resource file with new name before modifying original file.

4. Edit the resource file for the old sybase home dir (ASE 12.5),for reserver word check yes and update the resource file as per your enviorment.

5. No need to shutdown your ASE 12.5 server, sqlupgradres will take care all the things.

6.  Only execute this command once you have done with all above steps and you have taken all backup. (MOST IMPORTANT)

sqlupgraderes again perform preupgrade, before actual upgrade.( you can modify its behaviour in resource file).

sqlupgraderes will copy automatically the interface file, cfg file , run server file in ase 15x.

Execute the following command with resource file: sqlupgraderes -r <resource_file_name>

Copying interfaces file entry to new directory...

The interfaces file entry has been copied to the new directory.

Running preupgrade program...

The preupgrade program has completed successfully.

Checking reserved words in each database...

No reserved word conflicts were found.

Checkpointing all databases...

All databases have been checkpointed.

Copying configuration file to new directory...

The configuration file has been copied to the new directory.

Updating RUN_SERVER file...

The RUN_SERVER file has been updated.

Doing pre-upgrade modifications...

Pre-upgrade modifications succeeded.

Restarting Adaptive Server....

Adaptive Server has been restarted.

Running upgrade program...

The upgrade program has completed successfully.

Restarting Adaptive Server....

Adaptive Server has been restarted.

Running installmaster script...

installmaster: 10% complete.

installmaster: 20% complete.

installmaster: 30% complete.

installmaster: 40% complete.

installmaster: 50% complete.

installmaster: 60% complete.

installmaster: 70% complete.

installmaster: 80% complete.

installmaster: 90% complete.

installmaster: 100% complete.

The installmaster script has been successfully installed.

Running installcommit script...

The installcommit script has been successfully installed.

Installing common character sets (Code Page 437, Code Page 850, ISO Latin-1,

Macintosh and HP Roman-8)...

Character sets installed.

Restarting Adaptive Server....

Adaptive Server has been restarted.

Done

6. Log in the server, check the current version with @@version, @@boottime, database/device status.

Post-upgrade tasks:

1. Installing 15x stored procedures after upgrade depending which function is being used on the server.

installsecurity – Run this script if the Adaptive Server Auditing functionality was enabled in your earlier installation.

installhasvss – Run this script if the HA functionality is enabled and the HA feature is currently in use in the upgraded installation of        Adaptive Server.

Installmodel

instmsgs.ebf

Check the installation script version using sp_version by logging in ASE.

2. Compare sp_configure output to see if there is any difference. Verify procedure cache allocation after upgrading. Verify data cache has no change.

3. Make sure license file is used.

save the license files with a .lic file name extension to the SYSAM-2_0/licenses directory of the license server installation.

Modify the variable in .profile for any instances on the same box.

For example:

LM_LICENSE_FILE=servername.lic

If you not having lic file, it will run under grace period of 1 months, you will get same warning in errorlog.

4. Reboot the instance

5. Reenabling auditing if needed.

Sybase Community Forum...

Hi All,

After long time, new posting...:(

Just sharing..may be some folks aware with it...Recently came across the Sybase's own  community forum, supported by Team Sybase....

Its having very good technical discussion, troubleshooting...tips fo ASE, IQ, Replication and many more...

http://www.sybase.com/detail_list?id=11507

Dataserver Health Check Script

Hi Folks,

This is going to be first post of month march, was busy due to relocation.  Now new topic health check...

In our production support environment we need do the health check on our data server on time to time basis, such as after the any long activity, in the production monitoring, after the server restart, and there are lot of many more cases.

I have tried to compile the Hc script v 1.0 as below , I am still trying to improve it , your suggestions and thoughts most welcome...

/***************************************************************

## Dataserver Health Check - Version 1.0
## Intial Drafted by - sybanva for SYBASETEAM.COM
## Date - 19th Feb 2010
****************************************************************/

print "#########################################################"
set nocount on
go
print "--------------------------------------------------------"
print "         DataServer Checks"
print "--------------------------------------------------------"
go
select @@version "DATASERVER VERSION"
go
select @@servername "Instance Name", @@maxpagesize " Page Size of Instance"
go
select getdate() "Current Date", @@boottime "Instance Startup date"
go
print "--------------------------------------------------------"
print "         Active Trace Flags"
print "--------------------------------------------------------"
dbcc traceon(3604)
go
dbcc traceflags
go
print "--------------------------------------------------------"
print "         Process Status"
print "--------------------------------------------------------"
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID"  from sysprocesses where physical_io>0 or cpu >0
go
print "--------------------------------------------------------"
print "          Long Running Process Status"
print "--------------------------------------------------------"
If exists (select count(1) from syslogshold where spid <> 0)
print "<<<<<<<<  NO LONG RUNNING TRANS AT THIS MOMENT >>>>>>>>>"
else
select dbid "Database ID",spid SPID,starttime "START TIME",name NAME from syslogshold where spid <> 0
go
print "--------------------------------------------------------"
print "           Blocking Process Status"
print "--------------------------------------------------------"
go
If not exists (select count(1) from sysprocesses where blocked > 0)
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID"  from sysprocesses where blocked > 0
else
print "<<<<<<<<  NO BLOCKING PROCCESS AT THIS MOMENT >>>>>>>>>"
go
print "--------------------------------------------------------"
print "           Checks for Zombie SPIDs"
print "--------------------------------------------------------"
go
if exists(select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses))
select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses)
else
print "<<<<<<<<  NO ZOMBIE SPIDS AT THIS MOMENT >>>>>>>>>"
go
print "--------------------------------------------------------"
print "           Check for Engines"
print "--------------------------------------------------------"
go
sp_configure 'number of engines'
go
select engine "Engine No",osprocid "OC Proc ID",status "STATUS",starttime "START TIME" from sysengines
go
print "--------------------------------------------------------"
print "           Checks for Database Status"
print "--------------------------------------------------------"
go
select  name "DB NAME",dbid "DBID",status "STATUS",crdate "CRATION DATE",dumptrdate "LAST DUMP TRAN DATE" from sysdatabases
go
print "--------------------------------------------------------"
print "           Checks for Suspect/OFFLINE  Database Status"
print "--------------------------------------------------------"
go
if exists(select name from sysdatabases where status in (-32768,64,256,32))
select name, status from sysdatabases where status in (-32768,64,256,32)
else
print "<<<<<<<<  ALL DBS STATUS IS NORMAL >>>>>>>>>"
go
print "--------------------------------------------------------"
print "           Checks for Servers"
print "--------------------------------------------------------"
select srvname "SERVER NAME",srvnetname "SERVER N/W NAME" from sysservers
go
print "--------------------------------------------------------"
print "           Checks for Backup Server"
print "--------------------------------------------------------"
go
SYB_BACKUP...sp_who
go
print "--------------------------------------------------------"
print "           Checks for Monitor config"
print "--------------------------------------------------------"
go
sp_monitorconfig "max memory"
go
sp_monitorconfig "number of locks"
go
sp_monitorconfig "number of open indexes"
go
sp_monitorconfig "number of open objects"
go
sp_monitorconfig "number of user connection"
go
sp_monitorconfig "procedure cache size"
go
Same Thread @ http://www.sybaseteam.com/dataserver-health-checkout-qa-script-t-684-3.html

Cleaning Shared Memory Segment and semaphore, for the ASE startup..

Panic dataserver, stopping it manually, and on restart showing issues with memory , meanwhile business people wants updates in every 5 mins...The situation is really worst for a DBA.

This posting is related to shared memory problem, for cleaning the shared memory:

When we shutdown the Sybase dataserver abruptly, by using the kill -9 or kill -12(or due to any reason automatically).

During the restart, sometime it gives the problem unable to create shared memory region, as below:

os_create_region: shmget (0x%x): %s

os_create_region: Shared memory segment %d is in the way

os_create_region: uninitialized shared memory descriptor

os_create_region: shmat (%d): %

Killing Process may left behind the System V semaphore or shared memory left behind instead of being cleaned up automatically. To eliminate unneeded semaphores or shared memory segments, we need to release semaphore or shared memory segment manually.

For the Semaphores:

Run the command ipcs -sa, it will give all semaphores active in the UNIX box, look for the value "0" in the column NSEMS. 0 values indicate that unused semaphore, find all semaphore for the Sybase user and remove them using command ipcrm -s

testinghostname:(/sybase)=>ipcs -sa
IPC status from as of Thu Sep 17 08:50:11 EST 2007
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 201326671 0xc103d804 --ra-ra-ra- patrol dba patrol dba 2 8:45:15 0:00:08
s 201326668 0xc103d80e --ra-ra-ra- sybase dba sybase dba 0 8:50:08 0:00:00

testinghostname:(/sybase)=>ipcrm -s 201326668

For the Shared Memory:

To cleanup the Shared memory segment, which are assigned at OS level but not in use, run ipcs -ma command.

testinghostname:(/sybase)=>ipcs -ma
IPC status from as of Thu Sep 17 08:50:24 EST 2007
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 654311446 0x4103d80e --rw-rw-rw- patrol dba patrol dba 0 2304 1766 1766 8:50:18 8:50:18 0:00:08
m 671088657 0x4103d80d --rw-rw-rw- sybase dba sybase dba 0 1792 1766 1762 8:45:15 8:45:15 0:00:08
m 671088650 0x4103d80b --rw-rw-rw- root root root root 0 768 1766 1766 8:50:18 8:50:18 0:00:08
m 671088648 0x4103d80a --rw-rw-rw- sybase dba sybase dba 1 1280 1766 1766 8:50:18 8:50:18 0:00:08
m 654311548 0x4103d809 --rw-rw-rw- sybase dba sybase dba 0 256 1766 1766 8:50:08 8:50:08 0:00:08

Now look in the NATTCH field for 0, if it 0 zero for Sybase user , it is unneeded, but still assigned at OS level. Remove it ipcrm -m by providing the id.

testinghostname:(/sybase)=>ipcrm -m 671088657 -m 654311548

Now Start the dataserver....

Same case with backup server, if u get any error with shared memory..!

Thanks.

Source : Sybooks and UNIX Manuals on wwww.
Same Thread @ http://www.sybaseteam.com/cleaning-shared-memory-segment-semaphore-for-ase-startup-t-715.html

Cross Database intgerity constraints and dbid mismatch

Hi Folks,

I came across the interesting issue on ASE Server.(Still not sure, need to check with sybase tech suport)

Suppose in our prod env, we have cross database referential integrity constraints, these referential integrity constraints stores in sysreferences table by relating the dbids of the two dbs(primary db and referenced db).

When we perform the refresh from one server to another (Suppose PROD - > UAT).These constraints come as it is in refreshed database. In refreshed dataserver, all dbs may not be created in same order as source dataserver, which results in dbid mismatch from source server.

Still our constraints are same as source db with source dbids.

When we run the dbcc checks on refreshed db, it starts flaging for the wrong dbid in sysreferences table.

Interesting ! Isn't it?

In sybase manual and sybooks, I didn't get any article related to db id mismatch, for cross db integrity constraints.

Will update u soon, if any.


Anva

Syssrvroles dropped

Hey Guys!
In one case, I dropped out the syssrvroles accidently. Now, no one having any roles, even sa is not having its roles..I hav backup of all system tables and dump of master db. But We dont hav sso_role for any login we can't do the bcp in , we can't insert any row in any system table. Even we can't change the allow update on system table, requires sso_role. Anyway. We have backup of master..so we can recover it..by loading the dump of master , but still need suggestions....