Jephe Wu - http://linuxtechres.blogspot.com
Objective: understanding Oracle rman backup and best practice for doing it
Environment: Oracle database 11.2 64bit
Configuration:
Firstly, check control_file_record_keep_time paramteter, make sure it's greater than your retention policy, otherwise, the rman backup information will be removed from controlfile. it's 7 days by default.
to check parameter value:
sql> show parameter control_file_record_keep_time;
use 'show all' in rman to list current configuration
and use something like 'CONFIGURE CHANNEL DEVICE TYPE DISK clear' to reset to default settings.
Rman backup script best practise:
$ cat rmanbackup.sh
# export variables
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export ORACLE_HOME
ORACLE_SID=LIVEDB1
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH
#or
export PATH=/usr/bin:/usr/local/bin:/usr/ccs/bin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
export ORACLE_SID=PROD
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
# other variables
week=`date +%w`
LOGFILE=/u01/app/oracle/admin/LIVEDB/backup/backup.log
export TIMESTAMP=`date +%Y-%m-%d-%T`
export WEEK=`date +%w`
export TRACE=/u01/app/backup/controlfile.backup.${week}.sql
export PFILE=/u01/app/backup/spfile.backup
export EMAIL="jephe@domain.com"
# create lock file
PRG=`basename $0`
LOCKFILE=/tmp/$PRG.lock
if [ -f $LOCKFILE
]; then
echo "lock
file exists, exiting..."
exit 1
else
echo "DO NOT REMOVE,
$LOCKFILE" > $LOCKFILE
fi
rman target / <<ERMAN > $LOGFILE
set echo on;
# controlfile autobackup
CONFIGURE CONTROLFILE AUTOBACKUP ON;
or
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dir/cf%F';
# The %F element of the format string combines the DBID, day, month, year, and sequence number to # generate a unique filename. %F must be included in any control file autobackup format.
#retention policy
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
or
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
show all;
report unrecoverable;
# actual backup
backup database plus archivelog;
refer to http://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup003.htm below
4.3.7.2 Backing Up Logs with BACKUP ... PLUS ARCHIVELOG
You can add archived redo logs to a backup of other files by using the
BACKUP ... PLUS ARCHIVELOG clause. Adding BACKUP ... PLUS ARCHIVELOG causes RMAN to do the following:- Runs the
ALTERSYSTEMARCHIVELOGCURRENTcommand. - Runs
BACKUPARCHIVELOGALL. Note that if backup optimization is enabled, then RMAN skips logs that it has already backed up to the specified device. - Backs up the rest of the files specified in
BACKUPcommand. - Runs the
ALTERSYSTEMARCHIVELOGCURRENTcommand. - Backs up any remaining archived logs generated during the backup.
This guarantees that datafile backups taken during the command are recoverable to a consistent state.
# delete obsolete;
# report obsolete;
delete force noprompt obsolete;
# controlfile/pfile backup
sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE';
sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''/u01/app/backup/controlfile.backup.${week}.sql'' reuse";
sql "CREATE PFILE=''/u01/app/backup/spfile.backup'' FROM SPFILE";
# crosscheck
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
crosscheck copy;
delete noprompt expired copy;
# list
# list backup by file;
# list backup of database by backup;
# list expired backup;
# list expired copy;
list backup;
list backup summary;
list backup recoverable;
# report
# report need backup database;
# report obsolete;
# report schema;
report unrecoverable;
# test restore
restore database validate;
restore spfile validate;
restore archivelog from time 'sysdate-1' validate [check logical]
restore controlfile to '/path/to/' validate;
# restore
tablespace users validate;
# restore archivelog from time ‘sysdate-1′
validate;
Note: The RESTORE DATABASE VALIDATE command will check for the last level 0 or FULL tape or disk based backup, but the RESTORE ARCHIVELOG ALL command will check for all the archivelog files catalogued based on the retention policy
exit
ERMAN
mailx -s "controlfile trace backup for PROD" $EMAIL < $TRACE
mailx -s "pfile backup for PROD" $EMAIL < $PFILE
use RMAN to check unrecoverable
# check which datafile has unrecoverable objects:
SELECT df.name data_file_name, df.unrecoverable_time FROM v$datafile df, v$backup bk WHERE (df.file# = bk.file#) and df.unrecoverable_change# != 0 and df.unrecoverable_time > (select max(end_time) from v$rman_backup_job_details where INPUT_TYPE in ('DB FULL' ,'DB INCR'));
# check objects inside datafile
select distinct dbo.owner,dbo.object_name, dbo.object_type, dfs.tablespace_name, dbt.logging table_level_logging, ts.logging
tablespace_level_logging from v$segstat ss, dba_tablespaces ts, dba_objects dbo, dba_tables dbt, v$datafile df, dba_data_files dfs, v$tablespace
vts where ss.statistic_name ='physical writes direct' and dbo.object_id = ss.obj# and vts.ts# = ss.ts# and ts.tablespace_name = vts.name and
ss.value != 0 and df.unrecoverable_change# != 0 and dfs.file_name = df.name and ts.tablespace_name = dfs.tablespace_name and dbt.owner = dbo.owner
and dbt.table_name = dbo.object_name;
use RMAN to check block corruption if required.
RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
or backup validate check logical datafile 3;
SQL> select * from v$database_block_corruption;
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.
RMAN> blockrecover corruption list;
Starting recover at 05-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-AUG-12
RMAN>run {
allocate channel d1 type disk;
blockrecover corruption list;
release channel d1;
}
V$BACKUP_CORRUPTION displays information about corrupt block ranges in data file backups
from the control file.
V$DATABASE_BLOCK_CORRUPTION displays information about database blocks
that were corrupted after the last backup.
SQL> select * from v$backup_corruption;
no rows selected
The V$BACKUP_CORRUPTION view shows corrupted blocks discovered during an RMAN backup. But once the blocks have been fixed this view is not updated.
SQL> select * from v$copy_corruption;
no rows selected
References:
How to identify all the Corrupted Objects in the Database with RMAN [ID 472231.1]
New Rman Blockrecover command in 11g (Recover corruption list) [ID 1390759.1]
Rman database restore: - using current control file
rman target /
Rman> startup force mount;
Rman> restore database;
Rman> recover database;
Rman> alter database open;
Rman database restore: - using backup control file
rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
Rman backup in non-archivelog mode:
rman target /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP FORCE DBA;
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE;
or
RMAN> BACKUP AS COPY DATABASE;
RMAN> ALTER DATABASE OPEN;
Other rman backup: (individual file or tablespace)
RMAN> BACKUP ARCHIVELOG from time 'sysdate-2';
RMAN> BACKUP TABLESPACE system, users, tools;
RMAN> BACKUP AS BACKUPSET DATAFILE
'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trg/tools01.dbf';
RMAN> BACKUP DATAFILE 1,3,5;
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/curr_cf.copy';
RMAN> BACKUP SPFILE;
RMAN> BACKUP BACKUPSET ALL;
rman> Backup archivelog;
rman> Backup archivelog all;
You can also specify a range of archived redo logs by time, SCN, or log sequence number, as in the following example:
rman> BACKUP ARCHIVELOG
FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';
Rman recover tablespace and data block:
Tablespace:
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
rman> RESTORE TABLESPACE users;
rman> RECOVER TABLESPACE users;
rman> SQL 'ALTER TABLESPACE users ONLINE;
Individual data block:
rman> BLOCKRECOVER CORRUPTION LIST;
rman> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;
Managing rman repository:
RMAN repository metadata is always recorded in the control file of the target database.
CONTROL_FILE_RECORD_KEEP_TIME = 7 is the default setting
You can configure a retention policy to be used by RMAN to determine which backups are considered obsolete. This policy can be based on a recovery window (the maximum number of days into the past for which you can recover) or redundancy (how many copies of each backed-up file to keep).
e.g.
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN does not automatically delete backups rendered obsolete by the retention policy. RMAN deletes obsolete files if you run the DELETE OBSOLETE command.
report obsolete;
delete obsolete;
or
delete [force] [noprompt] obsolete;
if encounter problem like this:
ORA-27027: sbtremove2 returned error
ORA-19511: Error received from media manager layer, error text:
Rman crosscheck and delete backups:
Crosscheck is needed when an archivelog file or backup is manually removed, i.e., not deleted by RMAN. This command ensures that data about backups in the recovery catalog or control file is synchronized with corresponding data on disk or in the media management catalog.
The CROSSCHECK command operates only on files that are recorded in the recovery catalog or the control file.
The CROSSCHECK command does not delete any files that it is unable to find, but updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to remove the repository records for all expired files as well as any existing physical files whose records show the status EXPIRED.
If some backup pieces or copies were erroneously marked as EXPIRED, for example, because the media manager was misconfigured, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
Rman FAQ:
1. Move backup to new location:
You can move backup to their original location, then catalog them again
rman> catalog archivelog '/path/to/xxxz.arc';
or
rman> catalog star with '/path/to/';
MOS: How to backup archivelog files moved to different location [ID 293495.1]
2. remove archivelog backed up once:
Rman target /
Rman> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO Disk;
or to free up disk space quickly
rm -f *.arc
rman> crosscheck archivelog all;
note: if not crosschecked, you will encounter backup issue below:
RMAN-06059 (RMAN-6059)
Text: expected archived log not found, lost of archived log compromises
recoverability
MOS: RMAN-6059 expected archived log not found, lost of archived log compromi [ID 291415.1]
MOS: Rman backup retention policy [ID 462978.1]
3. check tablespace usage
Tablespace usage
select
tablespace_name,
used_percent
from
dba_tablespace_usage_metrics
where
used_percent > 50;
4. you cannot delete a obsolete file in rman, uncatalog it
rman> report obsolete;
rman> change copy of controlfile uncatalog;
rman> report obsolete;
now file is not listed
rman> delete obsolete
References:
1. http://docs.oracle.com/cd/E11882_01/backup.112/e10642.pdf # rman 11.2 doc
2. Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]
3. http://gavinsoorma.com/2009/11/rman-restore-validate-examples/
Posts
