Oracle RMAN

Query per job di backup

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     v$BACKUP_SET_DETAILS d
                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from v$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/
 

Duplicate Database

Duplicate Database

Restore Point in time

Restore point in time utilizando il catalogo, spfile presente, valorizzato db_create_file_dest diverso dal database sorgente:

startup nomount force;
run {
set dbid XXXXXXXXXX;
allocate channel c1 type disk;
set until time "to_date('2021-06-15:01:00:00','YYYY-MM-DD:hh24:mi:ss')";
restore controlfile from autobackup;
sql 'alter database mount';
sql 'alter database disable block change tracking';
set newname for database to new;
restore database;
switch datafile all;
recover database;
}

Recupero schema da backup

Istanza avviata con init o spfile:

processes=200
sga_target=10G
pga_aggregate_target=2G
db_block_size=8192
compatible="12.2.0"
log_archive_dest_1="location=+DG_ARCH"
db_files=1000
db_create_file_dest="+DG_DATA"
_system_trig_enabled=false
db_name="DBORA01"
db_unique_name="RECO01"
diagnostic_dest="/u01/app/oracle"
control_files="+DG_DATA/RECO01/controlfile/current.786.1075850995"

Da rman:

sql 'alter database mount clone database';
run {
 set until time "to_date('2021-06-20:03:20:00','YYYY-MM-DD:HH24:MI:SS')";
 set newname for database to new; 
 restore database skip forever tablespace USERS,TBS01,TBS02 ;
 switch datafile all;
 recover database skip forever tablespace USERS,TBS01,TBS02 ;
}

backup archive

per salvare gli ultimi archive generati, ma evitare salvataggi doppi e cancellazione degli archive dopo 1 giorno dalle generazione:

Sintassi testata su rman 10.2

backup archivelog all not backed up 1 times to destination "sbt_tape";
backup as backupset device type sbt archivelog all not backed up 1 times;
delete noprompt archivelog all backed up 1 times to device type sbt;
delete noprompt archivelog all completed before 'sysdate-1' backed up 1 times to device type sbt;

Rman script windows

Template per multi retention

@echo off

for /f "skip=2" %%x in ('wmic Path Win32_LocalTime get Day^,DayOfWeek /Format:List') do set %%x >NUL

rem echo %Day% %DayOfWeek%

rem Sovrascrivi per prove
rem set Day=1
rem set DayOfWeek=1

rem echo %Day% %DayOfWeek%

set BCK=ORD
if %DayOfWeek% EQU 0 set BCK=LT180
if %DayOfWeek% EQU 0 if %Day% GTR 7 set BCK=LT35

echo %BCK%
(
echo spool log to %BCK%.log
echo list expired backup;
echo spool log off;
) > %BCK%.rman

set ORACLE_SID=XXXXXXX
set NLS_LANG=ITALIAN_ITALY.WE8MSWIN1252
set NLS_DATE_FORMAT=DD-MM-YYYY HH24:MI:SS

rman target / nocatalog cmdfile %BCK%.rman

Rman script linux

Versione multi retention

#!/bin/sh
# versione 12/09/2014 EG

DAYW=$( date +%w )
case $DAYW in
0)
        LEVEL=" incremental level 0 "
	DAY=$( date +%d )
	if [ $DAY -gt 7 ]
	then 
	  TAG="LT35LEV0"
	  KEEP=" keep until time 'sysdate+35' "
	else
	  TAG="LT180LEV0"
	  KEEP=" keep until time 'sysdate+180' "
	fi
        ;;
*)
        LEVEL=" incremental level 1 cumulative "
	TAG="LEV1"
	KEEP=""
        ;;
esac

NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
NLS_LANG='ITALIAN_ITALY.WE8ISO8859P15' ; export NLS_LANG

rman target / nocatalog log /tmp/rman_${ORACLE_SID}.log << EOF
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_${ORACLE_SID}.f';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/asigra/libdsc_ora_sbt_Linux_x86_64.so,ENV=(CONNECT_INFO=/asigra/parms.txt)';
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 22 DAYS;

crosscheck archivelog all;
crosscheck backup device type sbt;
backup as compressed backupset 
 archivelog all not backed up 1 times
   format 'ora.%d.%s.%p.%t.%u.arc.rman'
   filesperset 20 delete all input;

delete noprompt archivelog all backed up 1 times to device type sbt;

backup as compressed backupset
 ${LEVEL} tag ${TAG} ${KEEP}
 database
   format 'ora.%d.%s.%p.%t.%u.rman'
   filesperset 20
 plus archivelog not backed up 1 times
   format 'ora.%d.%s.%p.%t.%u.arc.rman'
   filesperset 20 delete all input;

#delete noprompt archivelog until time 'sysdate -1' backed up 1 times to device type sbt;

delete noprompt obsolete device type sbt;
delete noprompt expired backup device type sbt;

ALLOCATE CHANNEL FOR MAINTENANCE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE DISK;
#CROSSCHECK DATAFILECOPY ALL;
#DELETE NOPROMPT EXPIRED DATAFILECOPY ALL;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
RELEASE CHANNEL;


EOF

Versione semplice con recovery window

#!/bin/bash
echo $PATH

PATH=$PATH:/usr/local/bin
export PATH

#(( LEV = $( date +%w )  > 0 ? 1 : 0 ))

DAYW=$( date +%w )
DAY=$( date +%d )
H=$( date +%H )
if [ $DAYW = 0 -a $H -le 3 ]
then
  LEV=0
else
  LEV=1
fi
NLS_LANG=ITALIAN_ITALY.UTF8 ; export NLS_LANG
NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

DST=/tmp
LEVEL="incremental level ${LEV}"
TAG='tag TAG00'

ORACLE_SID=$1 ; export ORACLE_SID
ORAENV_ASK=NO ; export ORAENV_ASK
. oraenv
unset ORAENV_ASK

## 6 ore 600 * 72

if lockfile -60 -r 72 /tmp/backup_rman_${ORACLE_SID}.LCK
then

rman target / nocatalog trace ${DST}/${ORACLE_SID}_rman_${DAY}_${H}.log << EOF
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${DST}/snapcf_${ORACLE_SID}.f';
CONFIGURE DEFAULT DEVICE TYPE TO SBT;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/asigra/libdsc_ora_sbt_Linux_x86_64.so,ENV=(CONNECT_INFO=/asigra/parms_${ORACLE_SID}.txt)';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 21 DAYS;

crosscheck archivelog all;
crosscheck backup device type sbt;
backup as compressed backupset device type sbt
 ${LEVEL} ${TAG}
 database
   format 'ora.%d.%s.%p.%t.%u.db.rman'
   filesperset 20
   include current controlfile
 spfile
   format 'ora.%d.%s.%p.%t.%u.spfile.rman'
 plus archivelog not backed up 1 times
   format 'ora.%d.%s.%p.%t.%u.arc.rman'
   filesperset 20 delete all input;

delete noprompt archivelog all backed up 1 times to device type sbt;

delete noprompt expired backup;
delete noprompt obsolete;

EOF

rm -f /tmp/backup_rman_${ORACLE_SID}.LCK
fi