RMAN effective use

my forum
    
Backup datafile needed after adding a datafile? 
http://forums.oracle.com/forums/thread.jspa?forumID=74&threadID=496950
Adding a datafile is registered in archivelogs, no need to back up the datafile. 
Controlfile autobackup is recommended when noarchiveolg

nohup rman target "/" msglog "restore_clt_file.log" rcvcat "rman/rman@reposit.fadalti.com" cmdfile "myfile.cmd" &
nohup rman msglog "restore_clt_file.log" cmdfile "myfile.cmd" &
    
rman>connect target sys/pwd@al11.fadalti.com
rman>connect catalog rman/rman@reposit.fadalti.com

run {
  allocate channel t1 type 'SBT_TAPE' ;
  backup current controlfile;
  release channel t1;
}

Backup database
connect target /
connect catalog rmanbfvpro/rmanbfvpro@reporman
run {
  set command id to 'backup_db';
  #exclude a tablespace
  CONFIGURE EXCLUDE FOR TABLESPACE cwmlite;
  #advanced Legato settings
  allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_BINDIR=/usr/sbin,NSR_CLIENT=db801bfv,NSR_SERVER=bk801bck,NSR_DATA_VOLUME_POOL=DATABASE)';
  #allocate channel t1 type DISK format '/opt/oracle/rmanbck/%d_%s_%p_%U_DB';
  #'compressed' is supported in Standard Edition
  backup as compressed backupset filesperset 8 database
  #plus archivelog
  #skip readonly
  #skip offline
  ;
  delete noprompt force obsolete;
  release channel t1;
}

Check if backup is ok
RESTORE DATABASE VALIDATE;
10g
RESTORE DATABASE PREVIEW;

run{
  set command id to 'backup_arch';
  allocate channel t1 type 'SBT_TAPE' ;
  #allocate channel t1 type disk format '/tmp/%d_%s_%p_%U_ARCH';
  #optional
  delete noprompt expired archivelog all completed before 'SYSDATE-15';
  change archivelog all crosscheck;
  sql "alter system archive log current";
  #DO NOT SPECIFY FORMAT IF USING DISK AND FORMAT IS SPECIFIED ON DISK
  #'compressed' is supported in Standard Edition
  #backup as compressed backupset filesperset 8 archivelog from logseq 3 until logseq 4;
  #backup as compressed backupset filesperset 8 archivelog all format "%d_%s_%p_%U_ARCH";
  backup as compressed backupset filesperset 8 archivelog  all delete all input format "%d_%s_%p_%U_ARCH";
  #backup as compressed backupset filesperset 8 archivelog from time='2000-05-09:19:43:00' format "%d_%s_%p_%U_ARCH";
  
  release channel t1;
}

Delete the archivelogs only from a single destination
run {
allocate channel ch1 type 'sbt_tape'
sql "alter system archive log current";
setlimit channel ch1 maxopenfiles 8;
backup filesperset = 32 ( archivelog  until time = 'sysdate-0'  like '/data04/oraarch/ITALYP/backup_colt_arch/%' );
delete noprompt archivelog  until time = 'sysdate-0'  like '/data04/oraarch/ITALYP/backup_colt_arch/%'  ;
}

#backup all the archived log from any destination
  backup as compressed backupset ARCHIVELOG ALL NOT BACKED UP 1 TIMES filesperset 8;
  #try to backup againg from the destination that we want to delete, almost no files will be backed up but the deleteion will execute
  backup as compressed backupset ARCHIVELOG like '/data03/oradata/MPD/DB1/ITADRT/archStdby/%' NOT BACKED UP 1 TIMES filesperset 8 delete input;
  

Back up archive logs by scn
run{
  set command id to 'arcvodcms';
  allocate channel t1 type 'SBT_TAPE' ;
  allocate channel t2 type 'SBT_TAPE' ;
  #'compressed' is supported in Standard Edition
  backup as compressed backupset filesperset 8
  #OS environment variable:
  #export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
  #time '06-Oct-00 11:07:14' until time '06-Oct-00 11:14:08'
  archivelog from scn 21565882777 until scn 21565888811 format "%d_%s_%p_%U_ARCH";
  release channel t1;
  release channel t2;
}

Specify destination, ONLY to override default:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/9.2.0/dbs/snapcf_COMSYPRO.f';

Back it up:
backup current controlfile;

Backup archivelogs from a custom path (not a log_archive_dest)
Use:
#'compressed' is supported in Standard Edition
backup as compressed backupset archivelog like '%/xyz/%'; 

!This solution has not been tested yet!

Backup incremental
#Level often range from 0 to 2
run{
  #'compressed' is supported in Standard Edition
  backup as compressed backupset incremental level 0 check logical filesperset 8
  tag IncrLev0OnlineBackup format '%d_%s_%p_%U_INCR_DB'
  database include current controlfile;
}

RUN {
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
RESTORE CONTROLFILE FROM AUTOBACKUP;
#if no set until time, then the most up to date is taken
#SET UNTIL SEQUENCE 124 THREAD 1;
#set until time "to_date('10-18-2006 10:00:00','MM-DD-YYYY HH24:MI:SS')";
ALTER DATABASE MOUNT;
RESTORE DATABASE;
SWITCH DATAFILE ALL; # Update the control file with new location of the datafiles.
RECOVER DATABASE
#NOREDO (if lost redos)
;
release channel t1;
release channel t2;
}
ALTER DATABASE OPEN RESETLOGS;
--Same as
--ALTER DATABASE CLEAR UNARCHIVED LOGFILE

#db_name in init.ora must equals the one we are restoring from
#look for controlfile position in init.ora
run{
allocate channel t1 type 'SBT_TAPE';
allocate channel t2 type 'SBT_TAPE';
set until scn 506141;
#set until logseq 55 thread 1;
#set until time "to_date('Aug-29-2005 10:00:00','Mon-DD-YYYY HH24:MI:SS')";
#RESTORE CONTROLFILE FROM AUTOBACKUP;
restore controlfile to '/controlfile1/ctrl1.ctl';
restore controlfile to '/controlfile2/ctrl2.ctl';
release channel t1;
release channel t2;
}

The restore command ONLY restore the datafiles
        
#db_name in init.ora must equals the one we are restoring from
#set dbid to DB_ID column of DB table in rman repository
set dbid 1234566
run{
allocate channel t0 type 'SBT_TAPE';
allocate channel t1 type 'SBT_TAPE';
set until scn 506141;
#set until time "to_date('Aug-29-2005 10:00:00','Mon-DD-YYYY HH24:MI:SS')";
#set newname for datafile 45 to '/ora02_nis/NIS_PRO/undo/undotbs05.dbf';
restore database;
#restore datafile '/oradata_netdb/NETDB/data/prova01.dbf';
release channel t0;
release channel t1;
}

run{
allocate channel t1 type disk;
allocate channel t2 type 'SBT_TAPE';
#restore archivelog from logseq=102336  until logseq=102340 thread=1;
restore archivelog from scn=460779 until scn =506141 thread=1;
release channel t1;
release channel t2;
}

Restore a tablespace (after human error)
    
# take the tablespace with the missing datafile offline
SQL "ALTER TABLESPACE history OFFLINE IMMEDIATE";
# restore the tablespace even though you have no backup
RESTORE TABLESPACE history;
# recover tablespace
RECOVER TABLESPACE history;
# bring the recovered tablespace back online
SQL "ALTER TABLESPACE history ONLINE";

run {
  set until scn 506141;
  recover database;
}

sql>alter database open;
rman>reset database; #create a new incarnation

Not using RMAN

(Does not create a new incarnation on RMAN repository catalog)    

#!/bin/ksh
export ORACLE_SID=AL12
. $HOME/.oraenv
. /u01/app/oracle/admin/AL12/RMAN/your_set_env.ksh
export NLS_DATE_FORMAT='yyyy-mm-dd:hh24:mi:ss'
export NLS_LANG=american_america.WE8MSWIN1252
#
sqlplus "/ as sysdba" << EOF >> /u01/app/oracle/admin/AL12/RMAN/recover.log
set autorecovery on
#OS environment variable:
#export NLS_DATE_FORMAT='yyyy-mm-dd HH24:MI:SS'
recover database until time '2005-03-14 04:00:01' using backup controlfile;
exit
EOF

Rename database
See note 15390.1

All
run {
  resync catalog;
  change archivelog all crosscheck;
  delete force noprompt obsolete;
  delete force noprompt expired backup;
  delete force noprompt expired archivelog all;
  delete force noprompt expired backupset;
}

delete backuppiece '/app/oracle/product/9.2.0/dbs/arch_TSTARCH_09hksifn';
or
run {
  ALLOCATE CHANNEL t1 DEVICE TYPE 'SBT_TAPE';
  delete noprompt backupset 119, 120;
  release channel t1;
}

# !! ALLOCATE CHANNEL FOR MAINTENANCE cannot be used from within a run block !!
    
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
delete noprompt backupset completed before "sysdate";

create/replace script aabbcc {
...
}

RUN { EXECUTE SCRIPT aabbcc ; }

#!!Only backup sets that were created on device type DISK can be backed up!!

#This command would typically be run in a scenario where the user wants more
#recent backups to exist on disk and older backups to exist on tape, but does
#not need backups to exist on both disk and tape at the same time.
#metalink note 151123.1
run {
  allocate channel t2 type 'SBT_TAPE';
  BACKUP BACKUPSET completed BEFORE "to_date('05/06/2006 19:00', 'dd/mm/yyyy hh24:mi')" delete all input;  
  #BACKUP BACKUPSET completed BEFORE 'sysdate-7' delete all input;
  release channel t2;
}

Usefull functions

See current SCN
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual

list backup device type disk;
list backup device type 'SBT_TAPE' completed before "sysdate";
list backup of archivelog sequence 62753;
list backup of archivelog all;
list backup of archivelog all device type 'SBT_TAPE' completed before "sysdate";

All back up of a single datafile
list backup of datafile '/dev/vx/rdsk/l_oradg_I3/rawdev_1024M_036';

%d = database name
%F = Controlfile autobackup defined with CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/NAS/test_saved_archive/%d_%s_%p_%U';
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
#Reset to default
CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR; 
CONFIGURE RETENTION POLICY CLEAR;

select program, status, 
(select disk_reads from v$sql where v$sql.address = v$session.sql_address and v$sql.hash_value = v$session.sql_hash_value) disk_reads,
(select cpu_time from v$sql where v$sql.address = v$session.sql_address and v$sql.hash_value = v$session.sql_hash_value) cpu_time, 
(select elapsed_time from v$sql where v$sql.address = v$session.sql_address and v$sql.hash_value = v$session.sql_hash_value) elapsed_time, 
(select buffer_gets from v$sql where v$sql.address = v$session.sql_address and v$sql.hash_value = v$session.sql_hash_value) buffer_gets,
--(select last_load_time from v$sql where v$sql.address = v$session.sql_address and v$sql.hash_value = v$session.sql_hash_value) last_load_time,
(select spid from v$process where addr = paddr) spid    
from v$session
where program like '%rman%'

Not verified solutions

Restore a datafile from copy

-If your database up then take the tablespace offline.
-Then restore the file at new location from previous backup.
-Then recover the datafile using archive log.
-then take the tablespace online.