Wednesday, September 28, 2011

Backup - RMAN..

    * Create a catalog
    * Register a database
    * Un-register a database
    * Reset the catalog after a restlogs on the target
    * Resync the catalog with the target controlfile
    * Delete a backup
    * Backup a database
    * Restore/recover a database
    * Show the controlfile backup record
    * Misc commands

Create a catalog
create a tablespace
create a user
grant connect, resource, recovery_catalog_owner to user

rman catalog user/pass@db
create catalog tablespace "<tablespace_name>";
 
Note. <tablespace_name> is case sensitive (i.e. it must be uppercase)
Note. If you get the error 'rman: can't open catalog', make sure that oracle's rman is being run (which rman). X11 also has a command called rman. Rename it if necessary.
Register a database
Note. ensure the target db has a password file
rman catalog user/pass@rmandb target user/pass@db
register database;
Un-register a database
sqlplus user/pass@rmandb
select * from rc_database;
select db_key, db_id from db;

execute dbms_rcvcat.unregisterdatabase(<db_key>, <db_id>);
Reset the catalog after a restlogs on the target
reset database;
Resync the catalog with the target controlfile
resync catalog;
Delete a backup
allocate channel... delete backuppiece <number>; 
release channel; 
Backup a database
backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';

run {
allocate channel t1 type disk;
backup current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
backup database format '/u01/ora_backup/rman/%d_%u_%s';
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}
 
Cold backup (archivelog or noarchivelog mode)
run {
allocate channel t1 type disk;
shutdown immediate;
startup mount;
backup database include current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
alter database open;
}

run {
allocate channel t1 type disk;
backup archivelog all delete input;
}
Restore/recover a database
Full restore and recovery
startup nomount; 
run { allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore controlfile; 
restore archivelog all; 
alter database mount; 
restore database; 
recover database; 

sql 'alter database open resetlogs';

Restore and roll forward to a point in time
startup nomount; 
run { set until time ="to_date('30/08/2006 12:00','dd/mm/yyyy hh24:mi')"; 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore controlfile; 
restore archivelog all; 
alter database mount; 
restore database; 
recover database; 
sql 'alter database open resetlogs';

If the archive logs are already in place:
startup mount; 
run { set until time ="to_date('08/02/2007 14:00','dd/mm/yyyy hh24:mi')"; 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore database; 
recover database; 
sql 'alter database open resetlogs';

startup mount; 
run { allocate channel t1 type disk; 
recover database; 
}
Show the control file backup record
set pages 999 lines 100 
col name format a60 
break on set_stamp skip 1 

select set_stamp 
, to_char(ba.completion_time, 'HH24:MI DD/MM/YY') finish_time 
, df.name 
from v$datafile df 
, v$backup_datafile ba 
where df.file# = ba.file# 
and ba.file# != 0 
order by set_stamp, ba.file# 
/
Misc commands
list backupset; 
list backup of database; 
list backup of archivelog all; 
report obsolete; 
report obsolete redundancy = 2; 
delete obsolete; - remove unneeded backups 
restore database validate; - check the backup 
report unrecoverable; 
report schema; - show current db files 
crosscheck backup; - make sure the backups in the catalog still physically exist 
delete expired backup; - delete epired backups found by crosscheck 

rman target sys/*****@scr10 catalog rman/rman@dbarep 
LIST BACKUPSET OF DATABASE; 
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; 
DELETE OBSOLETE REDUNDANCY = 4 device type disk; 
delete obsolete REDUNDANCY = 2 device type disk;

Delete archive log older than...
DELETE NOPROMPT ARCHIVELOG UNTIL TIME "SYSDATE-5" 

Crosscheck the available archivelogs (fixes RMAN-06059)
change archivelog all crosscheck;


Sukhwinder Singh
Apps DBA

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

There was an error in this gadget