Using RMAN

• Some useful commands

backup incremental level 0 database ;

restore database datafile 9 from tag='TAG20101201T170101';

crosscheck archivelog all;

backup archivelog all not backed up 1 times;

delete noprompt archivelog until time 'sysdate - 1' backed up 1 times to device type sbt;
backup archivelog all not backed up delete input;

restore archivelog sequence between 932 and 935 thread=1;
restore archivelog SCN BETWEEN 6055449295951 AND 6055451858904;

---- controlfile

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0.4/dbs/snapcf_cis.f';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02mars/ORADATA/cis/autobackup/%F';

restore controlfile to '/oradata/ctvfile.bak' from autobackup;
restore controlfile to '/oradata/ctvfile.bak' from 'c-4119285822-20100302-0a'
restore spfile from autobackup
create spfile from MEMORY


CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;


--- duplicate --
run {
allocate channel nbu_sbt0 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin
/libobk.so, ENV=(NB_ORA_SERV=mynetback,NB_ORA_CLIENT=myserv)';
allocate channel nbu_sbt1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin
/libobk.so, ENV=(NB_ORA_SERV=mynetback,NB_ORA_CLIENT=myserv)';
allocate auxiliary channel nbu_sbt2 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup
/bin/libobk.so, ENV=(NB_ORA_SERV=mynetbackNB_ORA_CLIENT=myserv)';
allocate auxiliary channel nbu_sbt3 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup
/bin/libobk.so, ENV=(NB_ORA_SERV=mynetback,NB_ORA_CLIENT=myserv)';
DUPLICATE TARGET DATABASE TO MYDBDEV until sequence=27757 thread=1 NOFILENAMECHECK;
}
DUPLICATE TARGET DATABASE TO MYDBDEV until time="to_date('30/06/2011 20:00:00', 'DD/MM/YYYY HH24:MI:SS')" NOFILENAMECHECK;

export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
DUPLICATE TARGET DATABASE TO MYDBDEV until time '04/03/2010 04:00:00' NOFILENAMECHECK;

-- delete
delete noprompt obsolete;
delete noprompt archivelog until time 'sysdate - 8/24' backed up 1 times to device type sbt;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;

crosscheck archivelog all;
DELETE NOPROMPT EXPIRED archivelog all completed before 'sysdate -40' ;

crosscheck backup;
DELETE NOPROMPT EXPIRED backup completed before 'sysdate -40';

-- list
list copy of archivelog from time='sysdate - 2';
        list backupset by backup summary; Listing backupset by summary
spool log to list.txt
        list backupset by file;
list backup of database;
list backup of tablespace tbs_tab_users;
list incarnation of database;
-- report
       report schema; Display all datafiles in database including temporary datafile
       report need backup days 1;
       report obsolete;
       report need backup;
       report unrecoverable;
REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

 validate database;
 select * from V$DATABASE_BLOCK_CORRUPTION

-- catalog
create user catrman identified by catrman
default tablespace tbs_tab_repos temporary tablespace temp;
grant CONNECT , RESOURCE, RECOVERY_CATALOG_OWNER to catuser;
RMAN>CONNECT CATALOG catuser/catuser ;
RMAN>CREATE CATALOG TABLESPACE tbs_tab_repos
rman target / target catalog catuser/catuser@catmran;
register database;
unregister database;

• Dealing controlfile enqueue issue

SELECT s.sid, username AS "User", program, module, action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid = s.sid and l.type = 'CF' AND l.id1 = 0 and l.id2 = 2;

• Restore database skeeping tbs or renaming datafiles

col redondance format 999,999

col fichier format a50

set pages 800

set lines 100

set feedback off

set heading off

spool switch.txt

define REP_CIBLE=/u01/ORADATA/myndb/

select 'switch datafile '||file_id||' to datafilecopy ''&&REP_CIBLE'||file_id||fichier||''';'

from

(

select substr(fichier,instr(fichier,'/',-1)+1) fichier,file_id

from

( select file_name fichier,file_id from dba_data_files where tablespace_name not in

('TBS1','TBS2')

) FIC) T

/

Generation switch datafiles script

for restore database with skeeped tablespaces

col redondance format 999,999

col fichier format a50

set pages 800

set lines 100

set feedback off

set heading off

spool newname.txt

define REP_CIBLE=/u01/ORADATA/myndb/

select 'set newname for datafile '||file_id||' to ''&&REP_CIBLE'||file_id||fichier||

''';'

from

(

select substr(fichier,instr(fichier,'/',-1)+1) fichier,file_id

from

( select file_name fichier,file_id from dba_data_files where tablespace_name not in

('TBS1','TBS2')

) FIC) T

/

Generating setnew name script for

restore database with skeeped tablespaces

run {

allocate channel nbu_sbt0 type 'SBT_TAPE' PARMS

'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so, ENV=(NB_ORA_SERV=frnbumaster,NB_ORA_ CLIENT=mynclient)';

allocate channel nbu_sbt1 type 'SBT_TAPE' PARMS

'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so, ENV=(NB_ORA_SERV=frnbumaster,NB_ORA_ CLIENT=mynclient)';

allocate channel nbu_sbt2 type 'SBT_TAPE' PARMS

'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so, ENV=(NB_ORA_SERV=frnbumaster,NB_ORA_ CLIENT=mynclient)';

allocate channel nbu_sbt3 type 'SBT_TAPE' PARMS

'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so, ENV=(NB_ORA_SERV=frnbumaster,NB_ORA_ CLIENT=mynclient)';

set newname for datafile 1 to '/u01/ORADATA/myndb/1system01.dbf';

set newname for datafile 2 to '/u01/ORADATA/myndb/2undotbs01.dbf';

set newname for datafile 3 to '/u01/ORADATA/myndb/3sysaux01.dbf';

set newname for datafile 10 to '/u01/ORADATA/myndb/10tbs_tab_iris_01.dbf';

set newname for datafile 11 to '/u01/ORADATA/myndb/11tbs_ind_iris_01.dbf';

restore database skip forever tablespace TBS1,TBS2;

switch datafile 1 to datafilecopy '/u01/ORADATA/myndb/1system01.dbf';

switch datafile 2 to datafilecopy '/u01/ORADATA/myndb/2undotbs01.dbf';

switch datafile 3 to datafilecopy '/u01/ORADATA/myndb/3sysaux01.dbf';

switch datafile 10 to datafilecopy '/u01/ORADATA/myndb/10tbs_tab_iris_01.dbf ';

switch datafile 11 to datafilecopy '/u01/ORADATA/myndb/11tbs_ind_iris_01.dbf ';

}