Oracle RMAN

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