Backup - RMAN

Backup - RMAN

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 controlfile 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;

============

Backup scripts -

rman_level_0.sh -

#!/bin/bash

date

export NLS_DATE_FORMAT="DD/MM/YYYY HH24:MI:SS"

#set the ORACLE_SID first

rman target / << EOF

run {

show all;

set command id to 'Database Backup Level 0';

backup as backupset incremental level 0 database tag 'L0_DB_FULL' plus archivelog not backed up 3 times tag 'L0_DB_BACKUP';

delete noprompt obsolete;

}

EOF

date

============

Data Recovery Advisory commands are available within RMAN: - The Data Recovery Advisor is a tool that helps you to diagnose and repair data failures and corruptions

• List Failure # lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.
• Advise Failure # presents manual and automatic repair options
• Repair Failure # automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.
• Change Failure # enables you to change the status of failures.