SCRIPTS - BACKUP & RECOVER BANCO DE DADOS ORACLE

Área de scripts destinados à backup e recover do banco de dados.

[+] Script para verificar os backups realizados desde o dia anteriror

set lines 190

select operation as "OPERACAO",
 object_type as "TIPO",
 status,
 output_device_type as "MEDIA",
 to_char(end_time,'DD-MM-RRRR HH24:MI:SS') as "DATA",
 round(MBYTES_PROCESSED/1024,2) as "TAMANHO(MB)"
from
 v$rman_status
where
 operation <> 'CATALOG'
 and trunc(end_time)>=trunc(sysdate-1)
order by end_time;

[+] Script para verificar os backups do spfile

SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , sp.spfile_included                                     spfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v$backup_set                                           bs
  , v$backup_piece                                         bp
  ,  (select distinct
          set_stamp
        , set_count
        , 'YES'     spfile_included
      from v$backup_spfile)                                sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.set_stamp = sp.set_stamp
  AND bs.set_count = sp.set_count
ORDER BY
    bs.recid
  , piece#;

[+] Script para verificar os backups do controlfile

SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , DECODE(   bs.controlfile_included
            , 'NO', '-'
            , bs.controlfile_included)                     controlfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v$backup_set    bs
  , v$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.controlfile_included != 'NO'
ORDER BY
    bs.recid
  , piece#;

[+] Script para realizar um hot backup full + archives (com compressão) - RMAN

run {
allocate channel d1 type disk maxpiecesize 5000M;
 backup as compressed backupset format '[DESTINO]/full_%d_%s_%p_%t.bkp' tag 'BackupDatabaseFullDiario' database;
 sql 'alter system archive log current';
 backup as compressed backupset format '[DESTINO]/arch_%d_%s_%p_%t.bkp' tag 'BackupArchivelogDiario' archivelog all delete input;
 backup as compressed backupset format '[DESTINO]/ctrl_%d_%s_%p_%t.bkp' tag 'BackupCurrentControlfile' current controlfile;
 backup as compressed backupset format '[DESTINO]/spfile_%d_%s_%p_%t.bkp' tag 'BackupCurrentControlfile' spfile;
release channel d1;
}

[+] Script para realizar um cold backup simples - RMAN

run {
 shutdown immediate;
 startup mount;
 backup spfile;
 backup database;
 alter database open;
 delete noprompt obsolete;
}

[+] Script para realizar o restore e recover completo dos datafiles - RMAN

STARTUP NOMOUNT
RESTORE CONTROLFILE FROM '[LOCAL_BKP_CTRL]/[ARQUIVO_BKP_CTRL]';
SHUTDOWN ABORT
STARTUP MOUNT

RUN {
ALLOCATE CHANNEL D1 TYPE DISK;
 CATALOG START WITH '[LOCAL_ARQUIVOS_BKP]';
 RESTORE DATABASE;
 RECOVER DATABASE;
RELEASE CHANNEL D1;
}

[+] Script para verificar os datafiles em begin backup

set lines 190
col file_name for a60
col tablespace_name for a30

select df.file_name,
       tablespace_name,
       b.change#,
       to_char(b.time, 'dd/mm/yyyy hh24:mi') TIME,
       b.status
  from v$backup b, dba_data_files df
 where b.status <> 'NOT ACTIVE'
   and b.file# = df.file_id;

[+] Script para monitorar a execução dos backups - RMAN

--POR CANAL
select sid, 
 serial#, 
 context, 
 sofar, 
 totalwork, 
 round(sofar/totalwork*100,2) as "%_complete" 
from
 v$session_longops 
where 
 opname like 'RMAN%' 
 and opname not like '%aggregate%' 
 and totalwork != 0 
 and sofar <> totalwork;

Caso você queira compartilhar algum script que não está na lista, entre em contato pelo e-mail anderson.graf87@gmail.com ou deixe um comentário.