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

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



LVL 1
vijadonAsked:
Who is Participating?
 
ChrisKingConnect With a Mentor Commented:
>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
 
jdlambert1Commented:
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
 
ChrisKingCommented:
I assume you are at the point where the ONLINE DATABASE command won't work ?

Were you databases on raw or file devices ?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
were the raw devices DD'd accurately ?

are all device names / sizes identical on both servers (use the sp_helpdevice command)
0
 
vijadonAuthor Commented:
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
 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
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
 
ChrisKingCommented:
since you have said (in another thread)
>I am an Oracle DBA

"bcp" is the equivelant to "SQL*Loader"
0
 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
please explain what you have changed.

should be:  build new server, bcp in the syslogins, have you done anything else at this point ?
0
 
vijadonAuthor Commented:
I have not done anything else yet, no change.
I dont know why this is hanging.


thanks
Vinai
0
 
ChrisKingCommented:
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
 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
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
 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
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
 
ChrisKingCommented:
did you get anywhere with this ?
0
 
vijadonAuthor Commented:
I am still woring on it.
0
 
vijadonAuthor Commented:
Currently I have started working on it .

thank you for your help,

Vinai
0
 
ChrisKingCommented:
let me know how you are getting on
0
 
vijadonAuthor Commented:
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
 
ChrisKingCommented:
good luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.