Sowacz blog, DBA and GNU/Linux Admin notes : ORACLE RMAN

for more read documentation

symbols:

%s	set nr
%p	piece of set nr
%c	copy nr for multi copy
%d	database name
%I	database id
%t	time index in sec
%D	day of month
%M	month
%Y	year
%T	year,month,day YYYYMMDD
%u	eight digit name consist of set nr and time
%U	uniq file name (necessary for many channel), consist of database id,day nr,month,year,file nr
%F	for controlfile c-[db_ID]-[YYYYMMDD]-[hex sequence from 00 to ff - max 256 different values]

connect to database without catalog using controlfile:

rman> connect target /
rman> connect target [sys_user]/pass@[sid_target]

connect to catalog, and next to database

(in one catalog may be register many databases):
rman> connect catalog [rman_user]/[pass]@[sid_rman_base]
rman> connect target [sys_user]/pass@[sid_target]
or;
rman catalog [rman_user]//[pass]@[sid_rman_base] target [sys_user]/pass@[sid_target]

settings:

rman> show all;
rman> configure retention policy to redundancy [number_of_files];
rman> configure retention policy to recovery window of [number od days] days;
rman> configure device type disk backup type to compressed backupset;
rman> configure device type disk backup type to [copy|backupset];
rman> configure [parametr] clear; #clear configuration for present parameter

default settings:

CONFIGURE RETENTION POLICY TO REDUNDANCY ;
CONFIGURE BACKUP OPTIMIZATION OFF; #default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; #default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/rmandata/%F.rbk';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oracle/rmandata/data-%I-%T-%s-%p-%U.rbk';
CONFIGURE MAXSETSIZE TO UNLIMITED; #default
CONFIGURE ENCRYPTION FOR DATABASE OFF; #default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; #default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/db_1/dbs/snapcf_sow01.f'; #default

backup script example:

run {
backup full tag "FULL"
filesperset 20
database;
sql 'alter system archive log current';
backup format '/oracle/rmandata/arc-%I-%T-%s-%p-%U.rbk'
archivelog all delete input;
}

! if you set CONFIGURE CONTROLFILE AUTOBACKUP ON - for any command "BACKUP" automaticaly will be backuped controlfile, thus we get two entries for one backup in command "list backup",

in an example above we get four entries
!setting tags is not neccessary because default setting is easy to find TAGYYYYMMDDTHH24MISS

check backup progress:

SELECT status,operation,object_type,row_type,to_char(start_time,'YYYY-MM-DD HH24:MI:SS)') FROM v$rman_status ORDER BY start_time;

last 1 day:
SELECT rs.status,rs.operation,rs.object_type,rs.row_type,to_char(rs.start_time,'YYYY-MM-DD HH24:MI:SS'),ro.output FROM v$rman_status rs,v$rman_output ro WHERE rs.start_time >= trunc(sysdate-1) AND rs.stamp=ro.stamp ORDER BY rs.start_time,ro.recid;

last 10 minutes:
SELECT rs.status,rs.operation,rs.object_type,rs.row_type,to_char(rs.start_time,'YYYY-MM-DD HH24:MI:SS'),ro.output FROM v$rman_status rs,v$rman_output ro WHERE rs.start_time >= sysdate-10/(24*60) AND rs.stamp=ro.stamp ORDER BY rs.start_time,ro.recid;

last 1 day (short version):
SELECT rs.stamp,rs.status,rs.object_type,rs.operation,rs.row_type,trunc(rs.input_bytes/1073741824,2)||' GB ' as input ,trunc(rs.output_bytes/1073741824,2) || ' GB ' as output , to_char(rs.start_time,'MM-DD HH24:MI:SS') as starttime,to_char(rs.end_time,'MM-DD HH24:MI:SS') as endtime FROM v$rman_status rs WHERE rs.start_time >= trunc(sysdate-1) ORDER BY rs.start_time;

last 1 day (long version):
SELECT rs.stamp, rs.status, rs.object_type, rs.operation, rs.row_type, trunc(rs.input_bytes/1073741824,2) || ' GB ' as bytes_input, trunc(rs.output_bytes/1073741824,2) || ' GB ' as bytes_output, to_char(rs.start_time,'YYYY-MM-DD HH24:MI:SS') as starttime, to_char(rs.end_time,'YYYY-MM-DD HH24:MI:SS') as endtime, ro.output FROM v$rman_status rs, v$rman_output ro WHERE rs.start_time >= trunc(sysdate-1) AND rs.stamp=ro.stamp ORDER BY rs.start_time,ro.recid;

list:

rman> list all script names;
rman> list copy of database;
rman> list backup [summary];
rman> list backup [summary] [completed after "04-AUG-11"]; rman> list backup by file;

rman> list backup of database [summary];
rman> list backup of database [summary] [completed after "04-AUG-11"];
rman> list backup of database by backup tag [tag_name];
rman> list backup of tablespace;
rman> list backup of controlfile;
rman> list backup of spfile;

rman> list backup of database archivelog all;
rman> list backup of archivelog all [summary];

rman> list backup of datafile '[path]' | [nr] [summary];

rman> list backupset [nr],[nr],[nr];
rman> list archivelog all; #list current archivelog

crosscheck:

rman> crosscheck backup | archivelog | copy #check if backup files are still exist and they obtain status AVAILABLE or EXPIRED, (you can test it by move backup files to other location and then bring it back)
rman> list expired backupset; #backups that were not found during a crosscheck, crosscheck must be first
rman> list expired backup of archivelog summary;
rman> list expired copy of archivelog all;
rman> list incarnation of database;

report:

rman> report schema;
rman> report need backup; #what is neccessary to backup according present backup policy
rman> report need backup days 7;
rman> report need backup redundancy 7;
rman> report need recovery window of 7 days;
rman> report obsolete; #old files according present policy, (if you change policy, report will be change also)
rman> report obsolete redundancy 7;
rman> report obsolete recovery window of 7 days;

delete:

rman> delete obsolete; #remove expired according present backup policy
rman> delete expired archivelog all;

backup:

rman> backup as copy database #copy files in format which is as same as datafiles
rman> backup as backupset database - copy at set?
rman> backup database;

other:

sql> select obsolete,backup_type,file_type,fname from v$backup_files;
sql> select * from v$rman_status;
sql> select * from v$rman_output;
rman> run {execute script [script_name];}
rman> run {sql '[sql_command'}
rman> run {host '[os_command]'}
rman> delete script [script_script];
rman> create script {[data_script];}

create catalog database:

sql> create tablespace [ts_rman] datafile '[file]' size [size]m autoextend on next [size]m;
sql> create user [rman_user] identified by [password] default tablespace [ts];
sql> grant resource,connect,recovery_catalog_owner to [rman_user];

create structure of catalog database with schema [rman_user]):

rman catalog [rman_user]/[pass]@[sid_rman_base]
or
rman> connect catalog [rman_user]/[pass]@[sid_rman_base]
rman> create catalog

register database in catalog:

(new entries on table [rman_user].db)
rman catalog [rman_user]/[pass] target [sys_user]/[pass]@[sid]
or
rman> connect catalog [rman_user]/[pass]@[sid_rman_base]
rman> connect target [sys_user]/[pass]@[sid]
you get output:
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog
rman> register database;

description of tables in schema [rman_user]:

DB #inf about register databases
DBINC #inf about databases and DBINC_KEY - database incarnation key
DF,DFATT #inf about database files
SCR,SCRL #scripts
ORL #inf about redologs
TS,TSATT #inf about tablespaces
TF,TFATT #inf about temp tablespaces

views:

V$BACKUP_CORRUPTION #damaged blocks of backupset
V$COPY_CORRUPTION #damaged blocks of copy files
[rman_user].RC_STORED_SCRIPT
[rman_user].RC_STORED_SCRIPT_LINE

backupsets:

FULL BACKUP #full backup all blocks, can not be used with incremental backup
default is incremental backupset
levels can be 0,1,2,3,4
backupset at level 0 #all blocks like FULL BACKUP
backupset at level 1 #contain blocks which were changed from parent backup (base on SCN which is wrote inside each block, if child block SCN > parent block SCN than it is copied)
INCREMENTAL BACKUP SET #backupset of level n, which contain blocks which were modified from backupset of level n or n-1
INCREMENTAL CUMULATIVE #backupset of level n, which contain blocks which were modified from backupset of level n-1 or lower

options:

BACKUP INCREMENTAL LEVEL=0 DATABASE
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE
BACKUP (tablespace [ts_name1,ts_name2)
BACKUP ARCHIVELOG FROM TIME '[time]' | FROM LOG SEQ=[nr_seq]
BACKUP ARCHIVELOG UNTIL TIME '[time]' | UNTIL LOG SEQ=[nr_seq]
BACKUP ARCHIVELOG ALL DELETE INPUT #backup with remove
COPIES [nr] FORMAT '[1st_copy]','[2nd_copy] ' #a number of copies (up to 4)

recovery all:

sql> STARTUP MOUNT;

rman> RESTORE DATABASE; #or
rman> RESTORE DATABASE UNTIL TIME "to_date('2009/08/18 16:00','YYYY/MM/DD HH24:MI')"; #or
rman> RESTORE DATABASE FROM TAG="[tag_name]"; #check TAG from command "list backup"
rman> RECOVER DATABASE; #get alertlogs from backup
rman> ALTER DATABASE OPEN;

! MAKE FULL BACKUP

recovery tablespace:

rman> SQL 'ALTER TABLESPACE [ts_name] OFFLINE';
rman> RESTORE TABLESPACE [ts_name];
rman> RECOVER TABLESPACE [ts_name];
rman> SQL 'ALTER TABLESPACE [ts_name] ONLINE';

recovery datafile:

rman> SQL 'ALTER DATABASE DATAFILE [file_nr] OFFLINE';
rman> RESTORE DATAFILE [file_nr];
rman> RECOVER DATAFILE [file_nr];
rman> SQL 'ALTER DATABASE DATAFILE [file_nr] ONLINE';

backup without alertlog from disk:

rman> SHUTDOWN IMMEDIATE;
rman> STARTUP NOMOUNT;
rman> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
rman> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/[backup_datafile_path]/ora_df%t_s%s_s%p' MAXPIECESIZE 4000M;
rman> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
rman> CONFIGURE CONTROLFILE AUTOBACKUP ON;
rman> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/[backup_controlfile_path]/%F';

rman> BACKUP DATABASE;
rman> ALTER DATABASE OPEN;

recovery cold backup without alertlog:

remove all datafiles etc.
rman> set DBID=[dbid]
rman> set controlfile autobackup format for device type disk to '[backup_controlfile_path]/%F';
rman> restore controlfile from autobackup;
rman> alter database mount;
rman> recover database; (!get error RMAN-03002, RMAN-06054)
rman> recover database noredo;
rman> alter database open resetlogs;

recover till timestamp

set variable:
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

rman> run {
set until time "to_date('2009-09-25:18:18','yyyy-mm-dd:hh24:mi')";
restore database;
recover database;
alter database open resetlogs; }

! all above commands must be included at one run block, otherwise rman recover to current state and to point of time

! if we've recovered database till timestamp and we've opened in RESETLOGS option before and we recognize that database data are not correct and we try to recover from other timestamp, we get an error:
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
so what we have to do is:
rman> list incarnation of database; #we get list of all incarnations
if we try to open database we get:
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

so go on:
run database at mount mode:
rman> reset database to incarnation [Inc Key];
rman> run {
set until time "to_date('2009-09-26 23:32','yyyy-mm-dd hh24:mi')";
restore controlfile;
alter database mount;
restore database;
recover database;}
rman> alter database open resetlogs;

change localization of source files

rman> catalog start with '[backup_datafile_path]'

example scripts:

rman> create script [script_name] {
rman> allocate channel [channel_name] type disk;
rman> set limit channel [channel_name] kbytes=10000; --limit of backup piece
rman> backup
rman> filesperset 20 --a number of files in one backupset (default 4 datafiles and,16 archivelogs),
-- ! one backupset include many files but one files could not be in many backupsets
rman> format '[dir_path/file_%s_%p_%t]' -- %s-backupset nr, %p-backup piece nr, %t-4byte timestamp
(archivelog all);
rman> release channel [channel_name];
}
rman> create script [script_name] {
rman> allocate channel [channel_name] type disk;
rman> copy
rman> datafile '[file_path.dbf]' to '[file_path.bck]',
rman> datafile '[file_path.dbf]' to  '[file_path.bck]',
rman> current controlfile to '[controlfile_path.bck]'
rman> tag='[tag_name]';
rman> release channel [channel_name];
}
create script [script_name] {
rman> allocate channel [channel_name] type disk;
rman> copy controlfile to '[path_control.bck]';
rman> release channel [channel_name];
}
create script [script_name] {
rman> allocate channel [channel_name] type disk;
rman> backup
rman> filesperset 3
rman> tag='[tag_name]'
rman> format '[dir_path/fileincrement_%s_%p_%d]'
(database include current controlfile);
release channel [channel_name];
}
rman> create script [script_name] {
rman> allocate channel [channel1_name] type disk;
rman> allocate channel [channel2_name] type disk;
rman> set limit channel [channel1_name] kbytes 10000;
rman> set limit channel [channel2_name] kbytes 10000;
rman> backup full
rman> filesperset 3
rman> tag='[tag_name]'
rman> format '[dir_path/fileincrement_%s_%p_%d]'
(datafile 1,2,3 channel [channel1_name)
(datafile 4,5,6 channel [channel1_name);
rman> release channel [channel1_name];
rman> release channel [channel2_name];
}
rman> create script [scirpt_name]{
rman> allocate channel [channel_name1] type disk;
rman> allocate channel [channel_name2] type disk;

rman> backup
rman> format '/[backup_dir]/controlfile_t%t_s%s_p%p'
rman> (current controlfile);
rman> backup
rman> incremental level 0
rman> tag db_intr_0
rman> filesperset 5
rman> format '/[backup_dir]/datafile_t%t_s%s_p%p'
rman> (database);

rman> sql 'alter system archive log current';

rman> backup
rman> filesperset 20
rman> format '/[backup_dir]/archivelog_t%t_s%s_p%p'
rman> (archivelog all delete input);

rman> release channel [channel_name1];
rman> release channel [channel_name2];
}

Sowacz blog, DBA and GNU/Linux Admin notes

2008-10-03

ORACLE RMAN