Results 1 to 6 of 6

Thread: Full recovery

  1. #1
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Unanswered: Full recovery

    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.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2007
    Location
    India
    Posts
    56
    My logdevice is safe. CAn you please tell me the steps to recover the system.
    i have loaded upto 12:30.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    shutdown ASE
    copy the saved log over the current log file
    restart ASE
    Last edited by pdreyer; 12-24-07 at 05:31.

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote 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.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    Example

    Lets create some dumps to play with
    Code:
    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
    Code:
    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  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •