[pvvsnaveen]

Hi,
Is there any way to recover my database when server crashed?
i have dump tran for every 15 mins.
my server crased at 12:40 ihave dump trans upto 12:30.
One of my colleague said it can be recoverable for that 10 mins also.

As my knowledge, If it is not crashed then we can take dump tran with no_truncate log.

[pdreyer]

What was the reason for the crash? Yes you should be able to recover that 10 min as well provided it is not the log disk that went bad and caused the crash. Make a copy of your log disk before you try anything - just in case.

[pvvsnaveen]

My logdevice is safe. CAn you please tell me the steps to recover the system.
i have loaded upto 12:30.

[pdreyer]

shutdown ASE
copy the saved log over the current log file
restart ASE

[trvishi]

Originally Posted by pvvsnaveen

My logdevice is safe. CAn you please tell me the steps to recover the system.
i have loaded upto 12:30.

Hm if your server just crashed, all you had to do is bring it up and the ASE will do automatic recovery. You need not have loaded the transaction logs. Now I dont think you can get the 10 mins back. Or am I missing something here.

[pdreyer]

Lets create some dumps to play with

1> disk init name='pd_d1',physname='/tmp/pd_d1.dat',size='10M'
2> go
1> disk init name='pd_l1',physname='/tmp/pd_l1.dat',size='5M'
2> go
1> create database pd on pd_d1=10 log on pd_l1=5
2> go
CREATE DATABASE: allocating 2560 logical pages (10.0 megabytes) on disk 'pd_d1'.
CREATE DATABASE: allocating 1280 logical pages (5.0 megabytes) on disk 'pd_l1'.
Database 'pd' is now online.
1> use pd
2> go
1> create table t1 (id int identity, name varchar(255))
2> go
1> dump database pd to 'compress::/tmp/pd.dgz'
2> go
...
Backup Server: 3.42.1.1: DUMP is complete (database pd).
1> insert into t1 select top 5 name from sysobjects
2> GO
(5 rows affected)
1> select max(id) from t1
2> go
             
 ----------- 
           5 

(1 row affected)
1> dump tran pd to 'compress::/tmp/pdlog_5rows.ldgz' with standby_access
2> go
Backup Server: 3.42.1.1: DUMP is complete (database pd).
1> insert into t1 select top 5 name from sysobjects                               
2> go
(5 rows affected)
1> select max(id) from t1
2> go
             
 ----------- 
          10 

(1 row affected)
1> !!ps -ef >/tmp/pd_d1.dat #### Clobber data file (10 rows committed)
1> insert into t1 select top 5 name from sysobjects
2> go
(5 rows affected)
1> select max(id) from t1   ---- No failure yet
2> go
             
 ----------- 
          15 

(1 row affected)
1> select * from t1         ---- Read from disk cause failure
2> go
00:00000:00000:2007/12/24 09:10:31.85 kernel  sddone: read error on virtual disk 5 block 656:00:00000:00000:2007/12/24 09:10:31.86 kernel  sddone: 4096 bytes passed, 0 returned on read for virt disk 5 block 656
00:00000:00023:2007/12/24 09:10:31.88 server  Error: 823, Severity: 24, State: 1
00:00000:00023:2007/12/24 09:10:31.88 server  I/O error detected during read...
1> exit
$ isql -Dpd
1> select max(id) from t1   ---- Sybase still OK with last tran
2> go
             
 ----------- 
          15 

(1 row affected)
--##### Lets save the log
1> dump tran pd to 'compress::/tmp/pdlog_15rows.ldgz' with standby_access
2> go
...
Backup Server: 3.42.1.1: DUMP is complete (database pd).
1> insert into t1 select top 5 name from sysobjects --- more to log still ok
2> go
(5 rows affected)
1> select max(id) from t1
2> go
             
 ----------- 
          20 

(1 row affected)

Let's assume 16 to 20 occurred in the last 10 min i.e. no log dump
Now the recovery

1> shutdown -- Now lets restart ASE without another log dump
2> go

$ cp -p /tmp/pd_l1.dat /tmp/pd_l1.dat_20rows ### Save the log file
$ startserver -f RUN_ppd15
...
$ isql -Dpd                                                       
Msg 926, Level 14, State 1:
Server 'ppd15', Line 1:
Database 'pd' cannot be opened. An earlier attempt at recovery marked it 'suspect'. Check the ASE errorlog for information as to the cause.
1> dbcc dbrepair (pd,dropdb)
2> go
1> sp_dropdevice pd_d1 -- Now recreate the bad device
2> go
00:00000:00022:2007/12/24 09:47:18.82 kernel  Deactivating virtual device 5, '/tmp/pd_d1.dat'.
Device dropped.
(return status = 0)
1> !!rm /tmp/pd_d1.dat
[sh:rm /tmp/pd_d1.dat]
1> disk init name='pd_d1',physname='/tmp/pd_d1.dat',size='10M'
2> go
1> create database pd on pd_d1=10 log on pd_l1=5
2> go
1> load database pd from 'compress::/tmp/pd.dgz'
2> go
Backup Server: 3.42.1.1: LOAD is complete (database pd).
1> online database pd for standby_access
2> go
Database 'pd' is now online.
1> select max(id) from pd..t1 -- Check emty OK
2> go
             
 ----------- 
        NULL 

(1 row affected)
1> load tran pd from 'compress::/tmp/pdlog_5rows.ldgz' 
2> go
Backup Server: 3.42.1.1: LOAD is complete (database pd).
1> online database pd for standby_access
2> go
Database 'pd' is now online.
1> select max(id) from pd..t1 -- Check 5 rows OK
2> go
             
 ----------- 
           5 

(1 row affected)
1> load tran pd from 'compress::/tmp/pdlog_15rows.ldgz'
2> go
Backup Server: 3.42.1.1: LOAD is complete (database pd).
1> online database pd for standby_access
2> go
Database 'pd' is now online.
1> select max(id) from pd..t1 -- Check 15 rows OK
2> go
             
 ----------- 
          15 

(1 row affected)

1> shutdown
2> go
$ cp -p /tmp/pd_l1.dat_20rows /tmp/pd_l1.dat ## to get all 20
$ startserver -f RUN_ppd15
$ isql -Dpd
1> select max(id) from pd..t1  -- check all 20 OK
2> go
             
 ----------- 
          20 

(1 row affected)
1> select * from t1 -- Check OK, no data loss