Oracle RMAN
Jump to navigation
Jump to search
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
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