Oracle RMAN

Duplicate Database

Duplicate Database

Restore

Punto più recente disponibile

Scenario: perdita completa del server, ripristino su un nuovo server In caso che allo startup dia errore di memoria, si può forzare rman ad avviare l'istanza senza spfile con un più sga con questa variabile:

ORA_RMAN_SGA_TARGET=1024

da rman:

set dbid XXXXXXXXXX;
startup nomount force;
run {
allocate channel c1 type disk;
restore spfile from autobackup;
}

Per modificare tutti i percorsi che sul nuovo server sono diversi, si modifica l'spfile appena ripristinato, serve valorizzare il parametro spfile in modo che l'istanza sappia dove scrivere le modifiche.

Se da sqlplus:

alter system set spfile='/u01/oracle/product/11.2.0.4/home1/dbs/spfileSID.ora' scope=memory;
alter system set db_create_file_dest='/u01/oradata' scope=spfile sid='*';
shutdown immediate
startup mount

se da rman:

sql "alter system set spfile=''/u01/oracle/product/11.2.0.4/home1/dbs/spfileSID.ora'' scope=memory";
sql "alter system set db_create_file_dest=''/u01/oradata'' scope=spfile sid=''*''";
sql "alter system reset control_files scope=spfile sid=''*''";
shutdown immediate;
startup nomount;

Si prosegue con il restore da rman:

set dbid XXXXXXXXXX;
run {
allocate channel c1 type disk;
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;
}

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

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

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
/