{"id":152,"date":"2008-10-03T22:56:00","date_gmt":"2008-10-03T20:56:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2008\/10\/03\/oracle-rman\/"},"modified":"2010-08-24T22:44:13","modified_gmt":"2010-08-24T20:44:13","slug":"oracle-rman","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2008\/10\/03\/oracle-rman\/","title":{"rendered":"ORACLE RMAN"},"content":{"rendered":"

for more read documentation<\/a>
format signs:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
%s<\/td>\nset nr<\/td>\n<\/tr>\n
%p<\/td>\npiece of set nr<\/td>\n<\/tr>\n
%c<\/td>\ncopy nr for multi copy<\/td>\n<\/tr>\n
%d<\/td>\ndatabase name<\/td>\n<\/tr>\n
%I<\/td>\ndatabase id<\/td>\n<\/tr>\n
%t<\/td>\ntime index in sec<\/td>\n<\/tr>\n
%D<\/td>\nday of month<\/td>\n<\/tr>\n
%M<\/td>\nmonth<\/td>\n<\/tr>\n
%Y<\/td>\nyear<\/td>\n<\/tr>\n
%T<\/td>\nyear,month,day YYYYMMDD<\/td>\n<\/tr>\n
%u<\/td>\neight digit name consist of set nr and time<\/td>\n<\/tr>\n
%U<\/td>\nuniq file name (necessary for many channel), consist of database id,day nr,month,year,file nr<\/td>\n<\/tr>\n
%F<\/td>\nfor controlfile c-[db_ID]-[YYYYMMDD]-[hex sequence from 00 to ff – max 256 different values]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

connect to database without catalog using controlfile:<\/span>
rman> connect target \/
rman> connect target [sys_user]\/pass@[sid_target]<\/p>\n

connect to catalog, and next to database (in one catalog may be register many databases):<\/span>
rman> connect catalog [rman_user]\/[pass]@[sid_rman_base]
rman> connect target [sys_user]\/pass@[sid_target] lub<\/p>\n

rman catalog [rman_user]\/\/[pass]@[sid_rman_base] target [sys_user]\/pass@[sid_target]
settings:<\/span><\/div>\n
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];<\/div>\n
rman> configure [parametr] clear; # czyszczenie konfiguracji dla danego parametru
example of default settings:<\/span>
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
example of backup script:<\/span>
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”,<\/span>
in example of backup script above we get four entries<\/span><\/p>\n

!setting tags is not neccessary because in default you get nice format TAGYYYYMMDDTHH24MISS<\/span> <\/span><\/p>\n

list commands: <\/span>
<\/span>
rman> list all script names; <\/span>
rman> list copy of database;
rman> list backup [summary];
rman> list backup by file;
rman> list backup of database [summary];
rman> list backup of database archivelog all;
rman> list backup of archivelog all [summary];<\/p>\n

rman> list backup of datafile '[path]’ | [nr] [summary];<\/p>\n

rman> list backupset [nr],[nr],[nr];
rman> list archivelog all; #list current archivelog<\/p>\n

rman> crosscheck backup | archivelog | copy #check if backup files are still exist and they obtain status AVAILABLE or EXPIRED, (you can test move backup files to other location and then bring it back)<\/span>
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;<\/p>\n

report commands:<\/b> <\/b>
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;<\/p>\n

rman> report obsolete; #old files according present policy, (if you change policy, report will be change olso)<\/span> <\/span>
rman> report obsolete redundancy 7;
rman> report obsolete recovery window of 7 days; <\/b><\/p>\n

delete commands:<\/b><\/p>\n

rman> delete obsolete; #remove expired according present backup policy
rman> delete expired archivelog all;<\/p>\n

backup commands:<\/b><\/p>\n

rman> backup as copy database – copy at files which format is simmilar as datafiles
rman> backup as backupset database – copy at set?
rman> backup database;<\/p>\n

other commands:<\/b> <\/p>\n

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]’}<\/p>\n

rman> delete script [script_script];
rman> create script {[data_script];}<\/p>\n<\/div>\n

create database for catalog:<\/span>
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 on schema [rman_user]):<\/span>
rman catalog [rman_user]\/[pass]@[sid_rman_base] lub
rman> connect catalog [rman_user]\/[pass]@[sid_rman_base]
rman> create catalog
register database in catalog (in table [rman_user].db occur new entry):<\/span>
rman catalog [rman_user]\/[pass] target [sys_user]\/[pass]@[sid] lub
rman> connect catalog [rman_user]\/[pass]@[sid_rman_base]
rman> connect target [sys_user]\/[pass]@[sid]
you get:
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog<\/span>
rman> register database;<\/p>\n

base tables of schema [rman_user]:
<\/span>DB – inf about register databases<\/div>\n

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<\/p>\n
views:<\/span>
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<\/p>\n

backupsets:
<\/span>FULL BACKUP – full backup
\n all blocks, can not be used with incremental backup
<\/span>in default is incremental backupset
levels can be 0,1,2,3,4
backupset on level 0 – all blocks like FULL BACKUP
backupset on 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<\/p>\n

options:<\/span>
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)<\/p>\n

example scripts:<\/span>
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<\/em>
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];
}<\/p>\n

\n

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];
}<\/p>\n

\n

create script [script_name] {
rman> allocate channel [channel_name] type disk;
rman> copy controlfile to '[path_control.bck]’;
rman> release channel [channel_name];
}<\/p>\n

\n

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];
}<\/p>\n

\n

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];
}<\/p>\n

\n

rman> create script [scirpt_name]{
rman> allocate channel [channel_name1] type disk;
rman> allocate channel [channel_name2] type disk;<\/p>\n

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);<\/p>\n

rman> sql 'alter system archive log current’;<\/p>\n

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

rman> release channel [channel_name1];
rman> release channel [channel_name2];
}<\/p>\n

recovery:<\/span>
all db:<\/span>
sql> STARTUP MOUNT;<\/p>\n

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;<\/p>\n

! MAKE FULL BACKUP<\/p>\n

tablespace:<\/strong><\/span>
rman> SQL 'ALTER TABLESPACE [ts_name] OFFLINE’;
rman> RESTORE TABLESPACE [ts_name];
rman> RECOVER TABLESPACE [ts_name];
rman> SQL 'ALTER TABLESPACE [ts_name] ONLINE’;
datafile:<\/strong>
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’;<\/p>\n

backup without alertlog from disk:<\/span><\/p>\n

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’;<\/p>\n

rman> BACKUP DATABASE;
rman> ALTER DATABASE OPEN;<\/p>\n

recovery cold backup without alertlog:<\/span>
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<\/span>)
rman> recover database noredo;
rman> alter database open resetlogs;<\/p>\n

odtwarzanie do momentu w czasie:<\/span>
nale\u017cy ustawi\u0107 zmienn\u0105:
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT=’yyyy-mm-dd hh24:mi:ss’<\/p>\n

rman> run {
set until time „to_date(’2009-09-25:18:18′,’yyyy-mm-dd:hh24:mi’)”;
restore database;
recover database;
}
rman> alter database open resetlogs
! wszystko musi si\u0119 znale\u017a\u0107 w jednym bloku run poniewa\u017c inaczej b\u0119dzie odtwarza\u0142 do stanu bie\u017c\u0105cego a nie do punktu w czasie, z uwagi na fakt \u017ce set b\u0119dzie si\u0119 odnosi\u0142 tylko do instrukcji RESTORE a ju\u017c nie do RECOVER<\/span><\/p>\n

! jezeli ju\u017c raz odtwarzali\u015bmy do momentu w czasie i otworzyli\u015bmy baz\u0119 z opcj\u0105 RESETLOGS, to w przypadku gdy okaza\u0142o si\u0119 \u017ce nie trafili\u015bmy, a chcieliby\u015bmy odtworzy\u0107 inny moment w czasie to dostaniemy komunikat:<\/span>
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time<\/span><\/p>\n

nale\u017cy wtedy wykona\u0107 zapytanie:
rman> list incarnation of database;<\/p>\n

otrzymamy list\u0119 wszystkich tzw. inkarnacji, poniewa\u017c w momencie odtwarzania do punktu w czasie i otwierania bazy w trybie RESETLOGS otrzymujemy komunikat:
database opened<\/span>
new incarnation of database registered in recovery catalog<\/span>
starting full resync of recovery catalog<\/span>
full resync complete<\/span><\/p>\n

tak wi\u0119c lec\u0105c dalej wykonujemy (na niezamontowanej bazie):
rman> reset database to incarnation [nr inkarnacji];
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 dat
\nabase open resetlogs;<\/p>\n

!Je\u017celi chcemy poda\u0107 inn\u0105 lokalizacj\u0119 plik\u00f3w \u017ar\u00f3d\u0142owych:
rman> catalog start with '[backup_datafile_path]’<\/p>\n


<\/span><\/div>\n<\/div>\n

<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"

for more read documentationformat signs: %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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[80],"tags":[70],"class_list":["post-152","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-rman"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=152"}],"version-history":[{"count":1,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/152\/revisions"}],"predecessor-version":[{"id":208,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/152\/revisions\/208"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}