Saturday, August 16, 2008

RMAN examples

# cold backup
rman
connect target /
connect catalog rcowner/password@rman
configure controlfile autobackup on;
startup mount;
run {
allocate channel disk1 device type disk format '/backup/rman_%d_%T_%s_%p';
backup as compressed backupset database;
}

# compressed backup
connect target /
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/rman_%d_%T_%s_%p';
BACKUP as compressed backupset database plus archivelog;
}

# point-in-time restore
# restore controlfile
RMAN
CONNECT TARGET /
connect catalog rcowner/password@rman
set dbid 3298136637
RUN
{
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';
SET UNTIL TIME '2008-06-03 13:30:00';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/rman_%d_%T_%s_%p';
RESTORE CONTROLFILE;
}

# restore database
# exit rman and login again (to avoid ORA-01460)
RMAN
CONNECT TARGET /
connect catalog rcowner/password@rman
RUN
{
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';
SET UNTIL TIME '2008-06-03 13:30:00';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/backup/rman_%d_%T_%s_%p';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
}

# recover database
RUN
{
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';
SET UNTIL TIME '2008-06-03 14:00:00';
RECOVER DATABASE;
}

# open database
sqlplus '/ as sysdba'
ALTER DATABASE OPEN RESETLOGS;


# backup archivelogs for a specific day
backup archivelog from time 'SYSDATE-38' until time 'SYSDATE-37' filesperset 50 format '/arch1_%d_%u/';

# backup recovery area
BACKUP RECOVERY AREA;

# Incremental backups

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/oracle/admin/test/bct.dbf';

SELECT * FROM V$BLOCK_CHANGE_TRACKING;

BACKUP DEVICE TYPE SBT INCREMENTAL FROM SCN 750923 DATABASE;
BACKUP INCREMENTAL FROM SCN 750923 DATABASE;
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 750983 DATABASE FORMAT '/tmp/incr_standby_%U';

BACKUP AS COMPRESSED INCREMENTAL LEVEL 1 TAG = WEEKLY DATABASE PLUS ARCHIVELOG;

# rolling incremental backups

RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}

# Rename datafile

select 'set newname for datafile '||file_id||' to '''||file_name||''';' from dba_data_files;