Solved

Moving the sybase databases to a different server and dbs are in suspect state

Posted on 2004-08-27
23
3,556 Views
Last Modified: 2012-05-05
I moved the sybases from one server to another, when I moved the databases and started it on the other server ,some of the databases are in suspect mode.
Also even when I issue ,
> sp_who
> go
I get the result:
1> sp_who
2> go
Msg 926, Level 14, State 1:
Line 1:
Database 'sybsystemprocs' cannot be opened. An earlier attempt at recovery
marked it 'suspect'. Check the SQL Server errorlog for information as to the
cause.
Msg 2812, Level 16, State 4:
Line 1:
Stored procedure 'sp_who' not found. Specify owner.objectname or use sp_help to
check whether the object exists (sp_help may produce lots of output).

This is what I get in the eror log file:
00:04/08/27 22:47:35.35 server  Database 'custtest' cannot be opened. An earlier attempt at recovery marked it 'suspect'. Check the SQL Server errorlog for information as to the cause.
00:04/08/27 22:47:35.35 server  Unable to proceed with the recovery of dbid <6> because of previous errors.  Continuing with the next database.
00:04/08/27 22:47:35.35 server  Error: 926, Severity: 14, State: 1
00:04/08/27 22:47:35.35 server  Database 'customer' cannot be opened. An earlier attempt at recovery marked it 'suspect'. Check the SQL Server errorlog for information as to the cause.
00:04/08/27 22:47:35.36 server  Unable to proceed with the recovery of dbid <8> because of previous errors.  Continuing with the next database.
00:04/08/27 22:47:35.36 server  Error: 926, Severity: 14, State: 1
00:04/08/27 22:47:35.36 server  Database 'report_data' cannot be opened. An earlier attempt at recovery marked it 'suspect'. Check the SQL Server errorlog for information as to the cause.
00:04/08/27 22:47:35.36 server  Unable to proceed with the recovery of dbid <9> because of previous errors.  Continuing with the next database.
00:04/08/27 22:47:35.36 server  Recovery complete.
00:04/08/27 22:47:35.36 server  SQL Server's default sort order is:
00:04/08/27 22:47:35.36 server          'bin_iso_1' (ID = 50)
00:04/08/27 22:47:35.36 server  on top of default character set:
00:04/08/27 22:47:35.36 server          'iso_1' (ID = 1).

I WOULD REALLY APPRECIATE IF ANYONE CAN HELP WITH THIS ISSUE<

thank you
Vni



0
Comment
Question by:vijadon
  • 12
  • 10
23 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11919367
How did you move them? If you copied the .mdf, .ndf, and .ldf files, did you detach the databases first? If you did backups on the first server, copied the backup files, and restored them on the second server, check the log files during the period of time the restores were performed.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 11920784
I assume you are at the point where the ONLINE DATABASE command won't work ?

Were you databases on raw or file devices ?
0
 
LVL 1

Author Comment

by:vijadon
ID: 11930168
This command does not work.
THE databases are on raw devices.
1> ONLINE DATABASE INDEX_MAP
2> GO
Msg 911, Level 11, State 2:
Line 1:
Attempt to locate entry in sysdatabases for database 'INDEX_MAP' by name failed
- no entry found under that name. Make sure that name is entered properly.
1> online database index_map
2> go
Msg 926, Level 14, State 1:
Line 1:
Database 'index_map' cannot be opened. An earlier attempt at recovery marked it
'suspect'. Check the SQL Server errorlog for information as to the cause.
Your help is greatly appreciated.


thank you
Vin
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 11930543
were the raw devices DD'd accurately ?

are all device names / sizes identical on both servers (use the sp_helpdevice command)
0
 
LVL 1

Author Comment

by:vijadon
ID: 11931396
the device names were not identical.
The new server is on NAS SYSTEM , SO THE NAMING CONVENTION WAS DIFFERENT, wE CORRECTED THEM.
1> sp_helpdevice
2> go
Msg 926, Level 14, State 1:
Line 1:
Database 'sybsystemprocs' cannot be opened. An earlier attempt at recovery
marked it 'suspect'. Check the SQL Server errorlog for information as to the
cause.
Msg 2812, Level 16, State 4:
Line 1:
Stored procedure 'sp_helpdevice' not found. Specify owner.objectname or use
sp_help to check whether the object exists (sp_help may produce lots of output).
1> select * from sysdevices
2> go
 low         high        status cntrltype name
         phyname                                                                
         mirrorname                                                            
 ----------- ----------- ------ --------- ------------------------------
        -------------------------------------------------------------------------------------------------------------------------------
        -------------------------------------------------------------------------------------------------------------------------------
           0           0     16         2 customer_account_dmp
         /backups/sybcps11/customer_account.dmp                                
         NULL                                                                  
           0           0     16         2 customer_dmp
         /backups/sybcps11/customer.dmp                                        
         NULL                                                                  
           0           0     16         2 customer_dmp01
         /backups01/sybcps11/customer.dmp                                      
         NULL                                                                  
           0           0     16         2 customer_log_dmp
         /backups/sybcps11/customer_log.dmp                                    
         NULL                                                                  
           0           0     16         2 customer_log_dmp01
         /backups01/sybcps11/customer_log.dmp                                  
         NULL                                                                  
           0           0     16         2 custtest_dmp
         /backups/sybcps11/custtest.dmp                                        
         NULL                                                                  
           0           0     16         2 custtest_dmp01
         /backups01/sybcps11/custtest.dmp                                      
         NULL                                                                  
    33554432    34578431      2         0 data1
         /dev/md/rdsk/d21                                                      
         NULL                                                                  
    50331648    51355647      2         0 data2
         /dev/md/rdsk/d23                                                      
         NULL                                                                  
    67108864    68132863      2         0 data3
         /dev/md/rdsk/d94                                                      
         NULL                                                                  
   134217728   135241727      2         0 data4
         /dev/md/rdsk/d97                                                      
         NULL                                                                  
   100663296   101687295      2         0 data5
         /dev/md/rdsk/d34                                                      
         NULL                                                                  
           0           0     16         2 index_map_1_dmp
         /backups/backupcps5/index_map1.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_1_dmp01
         /backups01/sybcps11/index_map1.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_2_dmp
         /backups/backupcps5/index_map2.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_2_dmp01
         /backups01/sybcps11/index_map2.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_3_dmp
         /backups/backupcps5/index_map3.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_3_dmp01
         /backups01/sybcps11/index_map3.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_4_dmp01
         /backups01/sybcps11/index_map4.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_dmp
         /backups/sybcps11/index_map.dmp                                        
         NULL                                                                  
           0           0     16         2 index_map_log_dmp
         /backups/sybcps11/index_map_log.dmp                                    
         NULL                                                                  
           0           0     16         2 index_map_log_dmp01
         /backups01/sybcps11/index_map_log.dmp                                  
         NULL                                                                  
    83886080    84910079      2         0 log1
         /dev/md/rdsk/d33                                                      
         NULL                                                                  
           0       25046      2         0 master
         d_master                                                              
         NULL                                                                  
           0           0     16         2 master_dmp
         /backups/sybcps11/master.dmp                                          
         NULL                                                                  
           0           0     16         2 master_dmp01
         /backups01/sybcps11/master.dmp                                        
         NULL                                                                  
           0           0     16         2 report_data_dmp
         /backups/sybcps11/report_data.dmp                                      
         NULL                                                                  
           0           0     16         2 report_data_dmp01
         /backups01/sybcps11/report_data.dmp                                    
         NULL                                                                  
           0           0     16         2 report_data_log_dmp
         /backups/sybcps11/report_data_log.dmp                                  
         NULL                                                                  
           0           0     16         2 report_data_log_dmp01
         /backups01/sybcps11/report_data_log.dmp                                
         NULL                                                                  
           0           0     16         2 sybsystemprocs_dmp
         /backups/sybcps11/sybsystemprocs.dmp                                  
         NULL                                                                  
           0           0     16         2 sybsystemprocs_dmp01
         /backups01/sybcps11/sybsystemprocs.dmp                                
         NULL                                                                  
    16777216    16803400      2         0 sysprocsdev
         /dev/md/rdsk/d36                                                      
         NULL                                                                  
           0       20000     16         3 tapedump1
         /dev/rmt4                                                              
         NULL                                                                  
           0       20000     16         2 tapedump2
         /dev/rst0                                                              
         NULL                                                                  
           0           0     16         2 test_dmp
         /backups/sybcps11/test.dmp                                            
         NULL                                                                  
           0           0     16         2 test_dmp01
         /backups01/sybcps11/test.dmp                                          
         NULL                                                                  

(37 rows affected)
 I would really appreciate your help onthis one, please.

Vin
0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 500 total points
ID: 11931580
>The new server is on NAS SYSTEM , SO THE NAMING CONVENTION WAS DIFFERENT, wE CORRECTED THEM.
did you also correct the high/low addresses

You have to be EXTREMELY CAREFUL, otherwise don't DD your devices / server / databases.

Your system seems small enough (only 5 data devices), why not follow the safe, tried and tested approach:
1. Build a new server on the new unix box
    (a) bring accross syslogins from the old server (bcp)
    (b) compare you sp_configure output on both servers and change accordingly
2. Init your new devices
3. Create the new databases on the new devices
4. Dump your databases from the old server
5. Load then into your new server
0
 
LVL 1

Author Comment

by:vijadon
ID: 11934729
Since I  have not done Sybase before,
I COPY OVER THE SYBASE SOFTWARE,

THEN HOW DO I BRING THE SYSLOGINS FROM THE OLD SERVER.

i AM GOING ONE STEP AT A TIME.

THANK YOU
Vin
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 11941764
you can copy the logins using the bcp command

bcp master..syslogins out syslogins.bcp -Soldserver -Usa -c
bcp master..syslogins in syslogins.bcp -Snewserver -Usa -c

you will need to turn on "allow updates to system tables" option on the new server BEFORE you bcp the file in
sp_configure "allow updates", 1

and turn it off again when done:
sp_configure "allow updates", 0
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 11941821
since you have said (in another thread)
>I am an Oracle DBA

"bcp" is the equivelant to "SQL*Loader"
0
 
LVL 1

Author Comment

by:vijadon
ID: 11966235
When I start up the database, master and model come online but then it hangs.

This is where it hangs ,nothing is there in the log either:
-----------------------------------------------------------------
sybdyt-msw-01% ./START_SYBASE
Backup Server/11.0.2/P/Sun_svr4/OS 5.4/1/OPT/Tue Sep 10 14:15:29 PDT 1996
Confidential property of Sybase, Inc.
(c) Copyright Sybase, Inc.  1987, 1996
All rights reserved.

Use, duplication or disclosure by the U.S. Government is subject to
restrictions set forth in FAR subparagraphs 52.227-19(a)-(d) for
civilian agency contracts and DFARS 252.227-7013(c)(1)(ii) for
Department of Defense contracts.  Sybase reserves all unpublished
rights under the copyright laws of the United States.
Sybase, Inc. 6475 Christie Avenue, Emeryville, CA 94608 USA.

00:04/09/01 11:54:40.94 kernel  Using config area from primary master device.
00:04/09/01 11:54:40.95 kernel  Warning: Using default file '/home/sybase/sybcps11.cfg' since a configuration file was not specified. Specify a configuration file name in the RUNSERVER file to avoid this message.
Logging Backup Server messages in file '/home/sybase/install/backuperrorlog'
00:04/09/01 11:54:41.33 kernel  os_create_keyfile: Removed old segment /home/sybase/sybcps11.krg
00:04/09/01 11:54:43.17 kernel  Using 1024 file descriptors.
00:04/09/01 11:54:43.17 kernel  SQL Server/11.0.2/P/Sun_svr4/OS 5.4/1/OPT/Tue Sep 10 14:03:23 PDT 1996
00:04/09/01 11:54:43.17 kernel  Confidential property of Sybase, Inc.
00:04/09/01 11:54:43.17 kernel  (c) Copyright Sybase Inc., 1987, 1996.
00:04/09/01 11:54:43.17 kernel  All rights reserved.
00:04/09/01 11:54:43.17 kernel
00:04/09/01 11:54:43.17 kernel  Use, duplication, or disclosure by the United States Government
00:04/09/01 11:54:43.17 kernel  is subject to restrictions as set forth in FAR subparagraphs
00:04/09/01 11:54:43.17 kernel  52.227-19(a)-(d) for civilian agency contracts and DFARS
00:04/09/01 11:54:43.17 kernel  252.227-7013(c) (1) (ii) for Department of Defense contracts.
00:04/09/01 11:54:43.18 kernel  Sybase reserves all unpublished rights under the copyright
00:04/09/01 11:54:43.18 kernel  laws of the United States.
00:04/09/01 11:54:43.18 kernel  Sybase, Inc. 6475 Christie Avenue, Emeryville, CA 94608 USA.
00:04/09/01 11:54:43.18 kernel  Using '/home/sybase/sybcps11.cfg' for configuration information.
00:04/09/01 11:54:43.18 kernel  Logging SQL Server messages in file '/home/sybase/install/errorlog_sybcps11'
.
00:04/09/01 11:54:43.18 kernel  Network and device connection limit is 1014.
00:04/09/01 11:54:43.20 server  Number of proc buffers allocated: 49595.
00:04/09/01 11:54:43.23 server  Number of blocks left for proc headers: 49665.
00:04/09/01 11:54:43.23 server  Memory allocated for the default data cache cache: 377012 Kb
00:04/09/01 11:54:43.36 server  Size of the 2K memory pool: 377012 Kb
00:04/09/01 11:54:43.37 kernel  Initializing virtual device 0, '/dev/vx/rdsk/sybcps11/data1'
00:04/09/01 11:54:43.37 kernel  Virtual device 0 started using standard unix i/o.
00:04/09/01 11:54:43.37 server  Opening Master Database ...
00:04/09/01 11:54:43.50 server  Loading SQL Server's default sort order and character set
00:04/09/01 11:54:43.51 kernel  ninit:0: listener type: master
00:04/09/01 11:54:43.51 kernel  ninit:0: listener endpoint: /dev/tcp
00:04/09/01 11:54:43.51 kernel  ninit:0: listener raw address: \x00020bb80a6417140000000000000000
00:04/09/01 11:54:43.52 kernel  ninit:0: transport provider: T_COTS_ORD
00:04/09/01 11:54:43.53 server  Recovering database 'master'
00:04/09/01 11:54:43.53 server  Recovery dbid 1 ckpt (1494,17)
00:04/09/01 11:54:43.53 server  Recovery no active transactions before ckpt.
00:04/09/01 11:54:43.58 server  Database 'master' is now online.
00:04/09/01 11:54:43.58 server  The transaction log in the database 'master' will use I/O size of 2 Kb.
00:04/09/01 11:54:43.74 server  server is unnamed
00:04/09/01 11:54:43.92 server  Recovering database 'model'.
00:04/09/01 11:54:43.92 server  Recovery dbid 3 ckpt (414,7)
00:04/09/01 11:54:43.92 server  Recovery no active transactions before ckpt.
00:04/09/01 11:54:43.96 server  The transaction log in the database 'model' will use I/O size of 2 Kb.
00:04/09/01 11:54:44.11 server  Database 'model' is now online.
00:04/09/01 11:54:44.11 server  Clearing temp db
00:04/09/01 11:54:44.86 server  Recovery complete.
00:04/09/01 11:54:44.86 server  SQL Server's default sort order is:
00:04/09/01 11:54:44.86 server          'bin_iso_1' (ID = 50)
00:04/09/01 11:54:44.86 server  on top of default character set:
00:04/09/01 11:54:44.87 server          'iso_1' (ID = 1).

I would really appreciate your help.


thank you
Vin
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 11968700
please explain what you have changed.

should be:  build new server, bcp in the syslogins, have you done anything else at this point ?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:vijadon
ID: 12000384
I have not done anything else yet, no change.
I dont know why this is hanging.


thanks
Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12001971
well I just ran a similar job last weekend
- I built a new server (linux)
- bcp'd the syslogins out of the old server
- removed the 2 system entries (as they will duplicate) using vi
- bcp'd the syslogins into the new server

then I went on:
- used sp_configure to update the server configuration
- used DISK INIT to build some new devices
- used CREATE DATABASE ... FOR LOAD to build the database shells
- used LOAD DATABASE to load the database dumps from the old server
- ONLINE DATABASE's

worked like a charm

I can only suggest you start again
0
 
LVL 1

Author Comment

by:vijadon
ID: 12012720
Sure you are Sybase guru,
I  would appreciate exact syntax, once again I am not a Sybase person.
I would really really appreciate if you can do this for me .

thank you and please.

Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12012751
syntax for what command ?

I keep getting the feeling you are not going back to the point of building a NEW server, ie not upgrading the one that is already there (and corrupted).

Are you being prompted for things like the size  and path of the master device ?
0
 
LVL 1

Author Comment

by:vijadon
ID: 12015993
Yes, currently I cannot even login and start up the database fully. I have already given the log file message.
Where the message ends it hangs there , does not exit successfully.

when I start up the database I get this error:

00:04/09/09 09:13:30.17 kernel  basis_dlock: file '/dev/vx/rdsk/sybcps11/data1' already in use by a SQL Server
00:04/09/09 09:13:30.17 kernel  kdconfig: unable to read primary master device
00:04/09/09 09:13:30.17 kernel  kiconfig: read of config block failed
Logging Backup Server messages in file '/home/sybase/install/backuperrorlog'
and then it hangs.

To what step should I regress back.

thanks
Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12016865
as I keep saying "build a new server", if you have to, totally remove your sybase directory and reinstall sybase (do NOT just restore a tape.

All the devices that you had the unix sys admin build are going to be (most likely) wrong for this server and should be removed and rebuilt with "disk init" after the new server has been built
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12054902
did you get anywhere with this ?
0
 
LVL 1

Author Comment

by:vijadon
ID: 12055648
I am still woring on it.
0
 
LVL 1

Author Comment

by:vijadon
ID: 12068077
Currently I have started working on it .

thank you for your help,

Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12070116
let me know how you are getting on
0
 
LVL 1

Author Comment

by:vijadon
ID: 12123656
I will accept your answer ,as this is on hold for a month.
I will open another case ,if I need help.

thank you

Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12124763
good luck
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now