IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Une stratégie de sauvegarde automatisée avec Sybase ASE

Avancé

Sauvegarde et restauration à une heure précise d'une base Sybase Apdaptive Server Enterprise… En théorie fort simple, ceci peut s'avérer complexe à mettre à jour.
Voici un petit outil permettant de le faire. ♪

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

La plupart des bases de données gèrent leurs transactions de manière à ce qu'il soit toujours possible de remonter une base de données dans un état consistant. Il s'agit parfois de remonter la base en l'état de la dernière sauvegarde pleine, mais il est parfois nécessaire de la remonter à une heure précise (PTR = Point in time recovery).

Le fer de lance de cette stratégie de sauvegarde adéquate est l'accès et l'utilisation des bons fichiers, et surtout la sécurisation du système permettant de maintenir un niveau de transactions sain.

DB2-UDB et Oracle offrent la possibilité de travailler soit en log tournants (les journaux de transactions s'écrasent au fur et à mesure), soit en log non tournant (les journaux de transactions doivent être extraits / copiés sur bande afin d'être disponible en cas de restauration.

Sybase ASE et Microsoft SQL Server gèrent un journal de transaction interne à la base (la table syslogs) qui peut se purger automatiquement ou s'extraire.

Voici un résumé de ces différentes méthodes :

SGBDR

Type de stratégie

Description

Récupération Point in time

Oracle

noarchivelog

Les redo logs s'écrasent au fur et à mesure de leur réutilisation. Pas de possibilité de sauvegarder la base à chaud.

Non

Oracle

archivelog

Avant de se faire écraser, les redo logs sont copiés par l'archiver dans un répertoire distinct. Ces fichiers sont ensuite sauvegardés via rman

Oui

DB2-UDB

Log cyclique

Les logs s'écrasent au fur et à mesure de leur réutilisation. Pas de possibilité de sauvegarder la base à chaud.

Non

DB2-UDB

Log non cyclique

Les logs se stockent de manière séquentielle sur le disque. Seul le nettoyage manuel ou semi-automatique permet de libérer cet espace.

Oui

Microsoft SQL Server

Full

Toutes les transactions sont stockées dans la table syslogs (le fichier .ldb). Rien n'est effacé, hormis lors du passage d'une commande backup transaction. À ce moment, les transactions mortes (déjà commitées ou déjà rollbackées) sont purgées de la table syslogs

Oui

Microsoft SQL Server

Simple

Les transactions ne sont pas sauvegardées. À chaque checkpoint, toute transaction commitée ou rollbackéed disparait du journal de transactions.

Non

Sybase ASE

trunc log on checkpoint=on

Toutes les transactions sont stockées dans la table syslogs (les segments dont le segmap est à 4). Rien n'est effacé, hormis lors du passage d'une commande dump transaction. À ce moment, les transactions mortes (déjà commitées ou déjà rollbackées, et en cas de réplication, seulement si le 2e point de troncature (Log Truncation Point) les a déjà traitées) sont purgées de la table syslogs

Non

Sybase ASE

trunc log on checkpoint=off

Les transactions ne sont pas sauvegardées. À chaque checkpoint, toute transaction commitée ou rollbackéed disparait du journal de transactions.

Oui

II. Mise en place

L'idée de cette procédure est de tracker les diverses sauvegardes faites afin que le système soit en mesure le plus aisément possible de remonter les sauvegardes utiles. Il suffirait alors de donner la date et l'heure exacte (Point in Time Recovery) de la restauration pour que le système, grâce à une table d'historisation, remonte les différentes sauvegardes nécessaires dans l'ordre adéquat.

On crée donc une table TrackDump qui servira à cette historisation.

 
Sélectionnez
-- Author : Fabien Celaia
-- Date   : 27.6.1999
-- Desc.  : table trackDump, to manage the dumps made by sp__dump 

if exists(select 1 from sysobjects where name = "trackDump" and type = "U")
    begin
    print "Dropping old trackDump table"
    drop table trackDump
    end
go

print "Creating new trackDump table"
create table trackDump 
    (
    dbid     int not null,
    file    varchar(255) not null,
    type     char(1) not null,
    error     int,
    start    datetime not null,
    stop     datetime not null
    )
go
print "Creating primary key to trackDump"
create unique clustered index trackDump_unc on trackDump( start, dbid)
go
print "Granting table trackDump"
grant delete on trackDump to oper_role
go

Création de la procédure stockée qui permettra d'exécuter des sauvegardes et d'en garder un historique. Il sera dès lors important que toute sauvegarde passe par cette transaction afin de ne pas couper la chaîne des journaux.

 
Sélectionnez
if exists(select * from sysobjects where name = "sp__dump" and type ="P")
    begin
    print "Dropping old sp__dump stored procedure"
    drop proc sp__dump
    end
go

print "Creating new sp__dump stored procedure"
go
create proc sp__dump (@dbname varchar(30), @method char(1)=NULL, @pathtodump varchar(200)=NULL) 
as
-- Author : Fabien Celaia
-- Date   : 27.6.1999
-- Syntax : sp__dump @dbname, {@method}, {@pathtodump}
-- Inputs : @dbname (mandatory) : name of the database to save
--          @method (optional)  : NULL => dump database
--                                "T" => dump transaction log
--          @pathtodump (optional) : Null => c:\temp (Windows) or /tmp (Unix)
--                                 : else saved in the given directory
-- Outputs:  0 => succeed
--          -1 => DB doesn't exist
--          -2 => DB needs to be saved before it's log
--            -3 => BS is down
--          -4 => Corrupted database : try to save the last log

    begin
    declare @fichier varchar(255), @debut datetime, @fin datetime, 
      declare @err int, @delcmd char(3), @numstripe int, @cpt int
    
    set nocount on

    if exists (select @@version where @@version like "%NT%")
        /* Unix */
        begin
        if @pathtodump is null
            select @pathtodump = "/tmp" 
        select @delcmd = "rm"
        end
    else
        /* Dos */
        begin
        if @pathtodump is null
            select @pathtodump = "C:\temp\" 
        select @delcmd = "del"
        end

    if not exists(select name from master..sysdatabases where name = @dbname)
        begin
        print "The database %1! does not exist", @dbname
        return -1
        end
    else
        begin
        -- Creation du fichier
        select @debut = getdate()
        select @fichier= @pathtodump+@dbname + "_" + substring(convert(char, @debut, 112), 1,8) + 
                               "_" + substring(convert(char,@debut,8),1,2) + substring(convert(char,@debut,8),4,2)+ substring(convert(char,@debut,8),7,2)

        -- Spécifie des stripe devices par bloc de 2Gb 
        select @numStripe = sum(size/512)/2048, @cpt=1
        from master..sysusages 
        where dbid=db_id(@db_name)

        while @cpt < @numStripe
            begin
            select @fichier = @fichier+ " stripe on " +@fichier+ convert(char(3),@cpt) 
            select @cpt=@cpt+1
            end


        -- test si base corrompue : si tel est le cas, sauvegarde du dernier log
        if exists (select * 
            from master..sysdatabases 
            where name = @dbname
            and status2 = status2|256)
            begin
                print "The database %1!is corrupt : trying to save the last transaction log.", @dbname
                dump tran @dbname to @fichier with no_truncate
                return (-4)
            end
        
        if @method is not null
            -- si sauvegarde du journal de transaction
            begin
            -- test si base deja sauvegardee
            if not exists(select * from DBA..trackDump where dbid = db_id(@dbname) and type ="D")
                begin
                print "Save the full database before the transaction log"
                return -2
                end
            else
                begin
                select @fichier = @fichier +".tran"
                dump tran @dbname to @fichier
                select @err = @@error
                if @err = 7205
                    print "Dump failed : BS probably is down." 
                if @err = 0 
                    print "Backup succeed"
                    insert into DBA..trackDump (dbid, start, stop, file, type, error) 
                    values (db_id(@dbname), @debut, getdate(), @fichier, "T", @err)
                end
            end
        else
            -- sinon sauvegarde de la base de donnees
            begin
            select @fichier = @fichier+".dmp"
            
            -- Si la base est configuree pour utiliser dbcc checkstorage, l'executer !
            dbcc checkstorage(@dbname)
            select @err=@@error
            if @err <> 0
                begin
                print "DBCC checkstorage failed for DB %1"`, @dbname
                insert into DBA..trackDump (dbid, start, stop, file, type, error) 
                values (db_id(@dbname), @debut, getdate(), NULL, "C", @err)
                end                
                

            dump database @dbname to @fichier            
            select @err = @@error
            if @err = 7205
                begin
                print "Dump failed : BS probably is down." 
                return -3
                end
            if @err = 0 
                print "Backup succeed"
                insert into DBA..trackDump (dbid, start, stop, file, type, error) 
                values (db_id(@dbname), @debut, getdate(), @fichier, "D", @@error)
            end
    
        end
    return @err
    end
go

Procédure permettant de contrôler l'état des sauvegardes d'une base donnée

 
Sélectionnez
if exists(select * from sysobjects where name = "sp__dump_fault" and type ="P")
    begin
    print "Dropping old sp__dump_fault stored procedure"
    drop proc sp__dump_fault
    end
go

print "Creating new sp__dump_fault stored procedure"
go
create proc sp__dump_fault @dbname varchar(30) = Null 
as
-- Author : Fabien Celaia
-- Date   : 27.6.1999
-- Syntax : sp__dump_fault @dbname
-- Inputs : @dbname (optional) : name of the database to check
-- Outputs:  0 => succeed

    begin
    set nocount on
    if @dbname is Null
        select db_name(t.dbid) DBName, t.start,t.error , m.description 
        from DBA..trackDump t, master..sysmessages m
        where t.error <> 0
        and t.error = m.error
        group by dbid
        order by start
    else
        select db_name(t.dbid) DBName, t.start,t.error, m.description 
        from DBA..trackDump t, master..sysmessages m
        where t.error <> 0
        and t.error = m.error
        and t.dbid = db_id(@dbname)    
        order by start
    end
go

Procédure permettant de nettoyer un historique de sauvegardes. Il va sans dire qu'il serait judicieux de garder tout l'historique

 
Sélectionnez
if exists(select * from sysobjects where name = "sp__dump_clean" and type ="P")
    begin
    print "Dropping old sp__dump_clean stored procedure"
    drop proc sp__dump_clean
    end
go

print "Creating new sp__dump_clean stored procedure"
go
create proc sp__dump_clean (@dbname varchar(30)) 
as
-- Date   : 30.6.1999
-- Syntax : sp__dump_clean @dbname, @time
-- Inputs : @dbname (mandatory) : name of the database to clean
-- Outputs:  0 => succeed
--          -1 => DB doesn't exist

    begin
    declare @limite datetime, @fich varchar(80), @xpval int, @delcmd varchar(31)    

    set nocount on

    if not exists(select name from master..sysdatabases where name = @dbname)
        begin
        print "The database %1! does not exist", @dbname
        return -1
        end

    -- test si la configuration permet d'envoyer une commande en ligne
    select @xpval = cur.value 
    from master..sysconfigures cfg, master..syscurconfigs cur
    where cfg.config= cur.config
    and cfg.name like "%xp_cmdshell%"

    exec sp_configure "xp_cmdshell", 0

    select @limite=max(start) 
    from DBA..trackDump
    where dbid = db_id(@dbname)
    and type="D"
    and error=0

    declare track_cur cursor 
        for select file 
        from DBA..trackDump 
        where dbid=db_id(@dbname)
        and start < @limite    
        for update

    open track_cur

    fetch track_cur into @fich

    while @@sqlstatus != 2    
        begin
        select @fich = @delcmd +" "+ @fich
        exec xp_cmdshell @fich
        fetch track_cur into @fich
        end  

    close track_cur
    deallocate cursor track_cur

    select file "Deleted Files" from DBA..trackDump
    where dbid=db_id(@dbname)
    and start < @limite    

    delete DBA..trackDump
    where dbid=db_id(@dbname)
    and start < @limite    

    exec sp_configure "xp_cmdshell", @xpval    
    end
go

Procédure permettant d'afficher l'état des sauvegardes d'une base spécifique

 
Sélectionnez
if exists(select 1 from sysobjects where name = "sp__dump_help" and type ="P")
    begin
    print "Dropping old sp__dump_help stored procedure"
    drop proc sp__dump_help
    end
go

print "Creating new sp__dump_help stored procedure"
go
create proc sp__dump_help (@dbname varchar(30)=null)
as
-- Date   : 8.8.2000
-- Syntax : sp__dump_help : display the list of the interessting dumps and the parameters
-- Inputs : -
-- Outputs: 
begin
declare @def varchar(255), @del varchar(30)

if exists(select @@version where @@version like "%NT%")
    /* NT */
    select @del="del", @def="C:\temp"
else
    /* Unix */
    select @del="rm", @def="/tmp"

print ""
print "Default Dump device : %1! ", @def
print "OS Command deleting a file : %1! ", @del
print ""
print ""
print "Available dumps"
print "==============="
print ""
select db_name(dbid), start Date,case type when "T" then "Transaction" when "C" then "Checkstorage" else "Database" end "Type",file from trackDump 
where error = 0
order by 1,2
end
go
grant execute on sp__dump_help to public
go

Procédure permettant de restaurer une base à une date et une heure précise

 
Sélectionnez
if exists(select 1 from sysobjects where name = "sp__dump_recover" and type ="P")
    begin
    print "Dropping old sp__dump_recover stored procedure"
    drop proc sp__dump_recover
    end
go

print "Creating new sp__dump_recover stored procedure"
go

create proc sp__dump_recover (@dbname varchar(30), @time datetime=NULL) 
as
-- Author : Fabien Celaia
-- Date   : 27.6.1999
-- Syntax : sp__dump_recover @dbname, @time
-- Inputs : @dbname (mandatory) : name of the database to reload
--          @time (optional) : recover till the given time (default = current datetime)
-- Outputs:  0 => succeed
--          -1 => Corrupted database
--          -2 => No valide database dumps exist
--          -3 => BS is down
--          -4 => Database in use

    begin
    declare @file varchar(255), @err int

    set nocount on

    if db_name() = @dbname
        begin
        print "You must exit of the database %1! before loading it. Please, use master."
        return (-4)
        end

    if @time is null
        select @time = getdate()

    declare dump_cur cursor for
    select file
    from DBA..trackDump
    where stop < @time 
    and error = 0
    and dbid = db_id(@dbname)
    and start > (    select max(start) 
            from DBA..trackDump 
            where error = 0 
            and dbid = db_id(@dbname)
            and type ="D")
    order by start

    select @err= count(uid) 
    from master..sysprocesses
    where dbid = db_id(@dbname)
    and suid <>0

    if @err > 0 
        begin
        select @file= convert(varchar(8), @err)
        print "%1! users are currently using %2!. Kill them before executing the load !",@file, @dbname
        select u.uid UserID, l.name Login, u.name UserName 
        from master..sysprocesses p, sysusers u, master..syslogins l
        where p.dbid = db_id(@dbname)
        and p.suid <>0
        and p.suid = l.suid
        and u.suid = l.suid
        return -4
        end
    
    /*cas de la base corrompue*/
    if exists (select * 
        from master..sysdatabases 
        where name = @dbname
        and status2 = status2|256)
        begin
            print "The database %1!is corrupt : check the errorlog to make it stable the problem before uploading !", @dbname
            return (-1)
        end
    else
        begin
        select @file = file 
        from DBA..trackDump 
        where error = 0 
        and dbid = db_id(@dbname)
        and type ="D"
        and start = (select max(start) 
                from DBA..trackDump 
                where error = 0 
                and dbid = db_id(@dbname)
                and type ="D")
        if @file is null 
            begin
            select @file = convert(char(8), @time, 4)+ " at " +convert(char(8), @time, 8)
            print "The database %1! has no valid load before %2!", @dbname, @file
            return (-2)
            end
        else
            begin
            load database @dbname from @file
            select @err = @@error
            if @err = 7205
                begin
                print "Load failed : BS probably is down." 
                return -3
                end
            if @err = 0 
                print "Load succeed"

            open dump_cur 
            fetch dump_cur into @file

            while @@sqlstatus <> 2
                begin
                load tran @dbname from @file
                select @err = @@error
                if @err = 7205
                    begin
                    print "Load failed : BS probably is down." 
                    return -3
                    end
                if @err = 0 
                    print "Load succeed"
                fetch dump_cur into @file
                end    
            deallocate cursor dump_cur
            end
        online database @dbname
        end
    return @err
    end
go

print "Granting sp__dump... stored procedures"
grant execute on sp__dump_recover to oper_role 
grant execute on sp__dump to oper_role 
grant execute on sp__dump_clean to oper_role 
go

Procédure permettant de tuer les sessions présentes sur une base spécifique, ceci afin de pouvoir effectuer une restauration en mode utilisateur unique.

 
Sélectionnez
print "Creating new sp__kill stored procedure"
go

create proc sp__kill (@dbname varchar(30)) 
as
begin
    set nocount on
    
    declare @snum  varchar(5)
    declare ckill cursor for select convert(char(5),spid) from master..sysprocesses where dbid=db_id(@dbname)
    
    open ckill
    fetch ckill into @snum
    while @@sqlstatus <> 2
        begin
        print "Killing process %1!",@snum
        exec ("kill " + @snum)
        fetch ckill into @snum
        end
end
go
grant execute on sp__kill to oper_role , sa_role, sso_role
go

Procédure de seuil standard permettant de lancer automatiquement une sauvegarde du journal de transaction au moment où un seuil critique est atteint.

 
Sélectionnez
use sybsystemprocs
go
if exists(select name from sysobjects where name = "sp_thresholdaction" and type = "P")
    begin
    declare @old varchar(30), @debut datetime
    select @debut = getdate()
    select @old = "sp_threshold_"+substring(convert(char, @debut, 112), 1,8) + 
                  "_" + substring(convert(char,@debut,8),1,2) + substring(convert(char,@debut,8),4,2)

    print "Renaming old sp_thresholdaction to %1!", @old
    exec sp_rename sp_thresholdaction, @old
    end
go

print "Creating new sp_thresholdaction stored procedure"
go

create procedure sp_thresholdaction
    @dbname        varchar(30),
    @segmentname    varchar(30),
    @space_left        int,
    @status            int
as
    begin
    declare @taille_avant    decimal(7,2),
        @taille_apres    decimal(7,2),
            @err        int,
        @ficname        varchar(128),
        @repname        varchar(128),
        @dumpname        varchar(255),
        @datet        datetime

    /* Tronque les decimales superflus */
    set arithabort numeric_truncation off

    /* journal de transactions (debut) */
    if @segmentname = ( select name from syssegments where segment = 2 )
    begin
        
        /* Recupere l'espace occupe dans le journal avant la sauvegarde */
        select @taille_avant = (reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))/1024.0
        from sysindexes
        where name = "syslogs"

        /* Effectue la sauvegarde du journal + purge */
        exec DBA..sp__dump @dbname, "T"

        /* Controle d'erreur */
        select @err = @@error

        /* Si Erreur sur dump tran to file */
        if @err != 0
        begin
          print "Impossible de sauvegarder le journal de la base
                 '%1!'. (Error Nr '%2!')", @dbname, @err
          return
        end

        /* Recupere l'espace occupe dans le journal apres la sauvegarde */
        select @taille_apres = (reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))/1024.0
        from sysindexes
        where name = "syslogs"

    end
    else
        begin
        print "BE CAREFULL: THE DATABASE %1! IS FILLING UP !!!", @dbname
        print "Available : %1! (2Kb)", @space_left
        end
    end
go
print "Granting sp_thresholdaction"
grant execute on sp_thresholdaction to public
go

III. Utilisation

Batch Windows permettant de démarrer les sauvegardes de base de données.

 
Sélectionnez
rem Auteur     : Fabien Celaia
rem Date     : 16.8.00
rem Description    : sauvegardes de bases

set CHEMIN=C:\temp

@echo off
echo use DBA > %CHEMIN%\bck_db.sql
echo go >> %CHEMIN%\bck_db.sql
echo exec sp__dump VotreBaseUtlisateur >> %CHEMIN%\bck_db.sql
echo exec sp__dump master >> %CHEMIN%\bck_db.sql
echo go >> %CHEMIN%\bck_db.sql
%sybase%\%sybase_ocs%\bin\isql -Ubatch -P****** -i %CHEMIN%\bck_db.sql -S%DSQUERY% >> %CHEMIN%\dump_db.log
del %CHEMIN%\bck_db.sql

Equivalence sous Unix

 
Sélectionnez
#!/bin/ksh
# Auteur         : Fabien Celaia
# Date          : 16.8.00
# Description    : sauvegardes de bases

CHEMIN=/usr/sybase/log

$SYBASE/$SYBASE_OCS/bin/isql -Ubatch -P****** -i $CHEMIN/bck_db.sql -S $DSQUERY -o $CHEMIN/dump_db.log -d DBA << EOF
exec sp__dump VotreBaseUtlisateur 
exec sp__dump master 
echo go 
EOF
rm $CHEMIN/bck_db.sql

Même appel pour les sauvegardes des journaux de transaction (sous Windows)

 
Sélectionnez
rem Auteur     : Fabien Celaia
rem Date     : 16.8.00
rem Description    : sauvegardes chronique du journal de transactions 

@echo off
echo use DBA > c:\temp\bck_tran.sql
echo go >> c:\temp\bck_tran.sql
echo exec sp__dump VotreBaseUtlisateur, 'T' >> c:\temp\bck_tran.sql
echo go >> c:\temp\bck_tran.sql
%sybase%\bin\isql -Ubatch -P****** -i c:\temp\bck_tran.sql -S%DSQUERY% >> g:\sybase\syblog\dump_db.log
del c:\temp\bck_tran.sql

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2008 Fadace. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.