Oracle RMAN

Duplicate Database

Si può partire con un spfile creato opportunamente. O si può restorare l'spfile del db originale e modificarlo tramite duplicate (dalla 11 in poi) (preferibile se si duplica su un server diverso)

10gR2

Creato spfile dell'istanza AUXILIARY, e creato passwordfile, modificata entry nel listener.ora e tnsnames.ora per consentire connessione via listener alla AUXILIARY.

ORACLE_SID=TEMP
export ORACLE_SID
rm -f ${ORACLE_HOME}/dbs/orapwTEMP
orapwd file=${ORACLE_HOME}/dbs/orapwTEMP password=temppassword
sqlplus '/ as sysdba' << EOF
 shutdown abort;
 create spfile from pfile='/home/oracle/pfileTEMP.ora';
 startup nomount;
EOF

impostato con . oraenv la connessione al target (database da duplicare). Il database duplicato verrà generato nell'istanza AUXILIARY.

rman target / auxiliary sys/temppassword@TEMP nocatalog trace duplicate.log << EOF
run
{
  allocate auxiliary channel dd0 type sbt;
  allocate auxiliary channel dd1 type sbt;
  DUPLICATE TARGET DATABASE TO TEMP
   DB_FILE_NAME_CONVERT ('/oradata/DBNAME/','/oradata2/TEMP/')
   UNTIL LOGSEQ 1011237 THREAD 1
   LOGFILE
     '/oradata2/TEMP/onlinelog/log01.dbf' size 512M,
     '/oradata2/TEMP/onlinelog/log02.dbf' size 512M,
     '/oradata2/TEMP/onlinelog/log03.dbf' size 512M
  ;
}
EOF

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;