Recovering from a Non-Master Device offline Failure

Symptoms :

• 840 error on recovery
• Database marked as not recovered (status bit 64) and "suspect" (bit 256)
• Recovery for that database fails

Reasons :

• Adaptive server cannot activate a device during startup recovery of a database
• Possible cause :

1. Device offline , busy , damaged , removed
2. Network problems
3. file permission problem

Dignostics :

• check ASE  error log & OS error log to find type of device problem
• login to ASE server and check databse affected & its status "not recovered" or "suspect".

Cure :

• If the suspect device is still usable and data remain on device :-

1. Dignose and fix the device problem if necessary.
2. reset sysdatabases.status by manually turning off bit 256 , use below command
3.  sp_configure "allow update to system tables",1
4. go
5. begin tran
6. update sysdatabases set status = status &~ 256
7. where dbid =db_id('sales')
8. -----check sysdbabases
9. commit tran
10. sp_configure "allow update to system tables",0
11. go
12. sysdatabases.status bit 64(offline) will be reset automatically during recovery.

• If data device or log device are unusable , it is necessary to go to backups.

Master Device curruption

SCENARIO :1: Master Device curruption without a backup .

1) create new mastre device with dataserver utility .
2)Edit the  ASE run file (-d parameter) to point to the new master device.
dataserver -d /var/sybase/masterdb.dat -b100M -sMASTER2K3)start up ASE in single user mode (-m) option .
4)Alter master databse to original size .
5) issue disk reinit command to restore sysdevices entries.
6) issue disk refit command to restore sysdatabases & sysusages entries.
disk refit
go
7)Execute the installmaster T-SQL script .
8)Execute the installmodel T-SQL script .
9)Recreate logins .
10)Restart ASE in multiuser mode .
11)Other step to consider :-
     i) new passwd for all login including sa..
     ii)Mirror the new master device & turn off the default status.
     iii)Run consiustency ( dbcc ) check in critical db.
     iv) Add entries in master..sysservers for the local ASE & all remote servers ,
          including Backup server.
     v) Alter and customize the model database
     vi) disk reinit  :- This step is critical , if you dont have original command disk init you cannot
                               proceed for master restoration.

SCENARIO :2: Master Device curruption with a backup .

1) create new mastre device with dataserver utility .
2)Edit the  ASE run file (-d parameter) to point to the new master device.
dataserver -d /var/sybase/masterdb.dat -b100M -sMASTER2K3)start up ASE in single user mode (-m) option .
4)Alter master databse to original size .
*5) Manually update sysservers for correct Backup server entry.
update sysservers set srvnetname= 'YODA_BACKUP'
where srvnetname='SYB_BACKUP'
*6) Load master database from backup.

7)Execute the installmodel T-SQL script .
8)Restart ASE in multiuser mode .

Manually Dropping a Corrupt Table and its Related Objects

Steps for Dropping a Table

---

Note: The following steps include an undocumented and unsupported dbcc option, extentzap. Use it at your own risk. Using it requires both sa_role and sybase_ts_role permissions.

---

Before you begin, make sure the table is not in use. Then follow these steps:

1. Turn on support for making changes to tables:
   
   sp_configure "allow updates to system tables", 1
2. Use the database that contains the corrupt table:
   
   use database-name
3. Run the following commands and write down the ID numbers; you will need these later:
   
   For the database ID:
   
   select db_id('database-name')
   For the ID of the corrupt table:
   
   select id from sysobjects where name = 'bad-table-name'
   For the table's index IDs:
   
   select indid from sysindexes where id = bad-table-id
4. The following step is optional but highly recommended. Mark the start of a user-defined transaction:
   
   begin tran
   
5. Delete all system catalog information for the object, including any object and procedure dependencies by creating and using all of this short script:
   

   declare @obj int
   select @obj = id from sysobjects where name = bad-table-namedelete syscolumns where id = @obj
   delete sysindexes where id = @obj
   delete sysobjects where id in (select constrid from sysconstraints where tableid  = @obj)
   delete sysdepends where depid = @obj
   delete syskeys where id = @obj
   delete syskeys where depid = @obj
   delete sysprotects where id = @obj
   delete sysconstraints where tableid = @obj
   delete sysreferences where tableid = @obj
   delete sysattributes where object = @obj
   delete syspartitions where id = @obj
   delete sysstatistics where id = @obj
   delete systabstats where id = @obj
   delete syscomments where id in (select id from sysobjects where deltrig = @obj)
   delete syscomments where id in (select id from sysobjects where instrig = @obj)
   delete syscomments where id in (select id from sysobjects where updtrig = @obj)
   delete sysprocedures where id in (select id from sysobjects where deltrig = @obj)
   delete sysprocedures where id in (select id from sysobjects where instrig = @obj)
   delete sysobjects where deltrig = @obj
   delete sysobjects where instrig = @obj
   delete sysobjects where updtrig = @obj
   delete sysobjects where id = @obj 
   
   /* If you are using Adaptive Server version 15.0 or newer, */
   /* you will need to add 3 more system tables to the script */
   delete sysstatistics where id = @obj
   delete systabstats where id = @obj
   delete syspartitionkeys where id = @obj

6. Note: If you make a mistake, cancel the transaction using the rollback command; and then correct and submit the script again.
7. Mark the end of the transaction:
   

   commit tran

8. Prepare to run dbcc, using the undocumented and unsupported option extentzap. Make the database read only by submitting each of the following commands:

   use master
   sp_dboption database-name, 'read only', true
   use database-name
   checkpoint

   ---

   WARNING: When you execute dbcc extentzap, it clears all extents for a given object ID and indid. The only way to recover the data is to use a database backup.
   

   ---

9. Assuming that you have the required sa_role and sybase_ts_role permissions, run dbcc extentzap twice for each index - once with a final parameter of "0" and again with a final parameter of "1". If the table uses and ALLPAGES lock scheme and has a clustered index, you also need to delete extents on index 0, even though that indid has no sysindexes entry. Use the following syntax, being very careful to use the correct object ID, that is, the object ID of the bad table:
   

   dbcc traceon(3604)
   
    /* to see the errors */
    
   dbcc extentzap (database-id, object-id, index-id,  0)dbcc extentzap (database-id, object-id, index-id,  1)    

10. Clean up using the following commands:
    

    use mastersp_configure "allow updates to system tables", 0sp_dboption database-name, 'read only', falseuse database-namecheckpoint