ORACLE BACKUP STRATEGIES

ORACLE BACKUP STRATEGIES191 BACKUP PROCEDURESIt is a very important aspect of any database which should be planned carefully as recovery depends upon the back up strategy which are being followed. Backup strategy depends upon the mode of database. Different methods are adopted for the database running in archivelog mode or database running in no-archivelog mode .Criteria:[1] When database is running in archive-log mode.1.Cold backup:In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles and v$logfiles views to find the names of datafiles and redo logfiles associated with the database. Use the operating system command to take the backup of these files. * In init.ora, search for parameter log_archive_dest to find the location of archived files. Use the operating system command to take the backup of these files.Frequency: This backup has to be taken weekly.Only Oracle database related file should be backedup. It will solve two problems[a] Downtime of database will be less.[b] Retrieval from cartridge will take less time.[c] Less numbers of cartridges will be required.File System Backup : Generally its frequency should be low. It will act as a backup for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you are creating important files on server then its frequency should be increased as decided by the site incharge.2.Hot backup:In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles views to find the names of datafiles associated with the database. Use the operating system command to take the backup of these files.* In init.ora, search for parameter log_archive_dest to find the location of archived files. Use the operating system command to take the backup of these files. *Frequency: This backup has to be taken daily.3.Logical Backup:[1] Ideally Complete database export should be taken daily.It is also called base backup.[2] Take incremental export daily except on weekends.On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.On month end, take complete database export and remove previously stored cumulative export logical backups.[3] Take important user level export daily.Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]

Cartridges Strategy:If you are taking complete database export then use three different sets of cartridges ( Grandfather , Father and Son concepts .) on three different days . And rotate these cartridges again .For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cumulative database export backup .For Cumulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.Recovery:In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems.For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery).For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.[2] When database is running in no archive-log mode.

Cold backup :In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles and v$logfiles views to find the names of datafiles and redo logfiles associated with the database. Use the operating system command to take the backup of these files. Ideally this backup should be taken daily.Logical Backup:[1] Ideally Complete database export should be taken daily.It is also called base backup.[2] Take incremental export daily except on weekends.On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.On month end, take complete database export and remove previously stored cumulative export logical backups.[3] Take important user level export daily.Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]Cartridges Strategy :If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.

Recovery :It is a very important process and it should be done very carefully. In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery). For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.* Commands to be used in copying file(s) to backup device :In Unix :[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]orfind / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0][b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]ortar -cvf /Complete file system backup :In Unix :[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]or[b] To copy all files of unix to backup devicetar -cvf /In Window NT :Use backup utility to copy the necessary files.Commands to be used in restoring file(s) from backup device :In Unix :[a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]orcpio -icBv [pattern] < [/dev/rmt0.1|/dev/rmt0][b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]

Annexure 11. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN( DATABASE CAN BE IN ARCHIVELOG MODE OR NO ARCHIVELOGMODE )SCENARIO[1] Database startup fails with errors :ora 1157 can not identify datafileora 1110 give the name of datafile which is missing.REQUIREMENT[1] The script which will recreate the objects in the datafile like script which will create indexex.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE INDEXES)NON-ESSENTIAL DATAFILESDATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.SOLUTIONShutdown the database.(shutdown immediate).Take complete backup of current database.Startup mountQuery the v$recover_file view along with v$datafile with a join on file# and note down the name of filesay it is /prodebs/test/ind.dbf.Alter database datafile /prodebs/test/ind.dbf offline;(if database is in noarchivwlog mode command will beAlter database datafile /prodebs/test/ind.dbf offline drop; )Alter database open;Drop tablespace user_index including contents;Create tablespace user_indexdatafile /prodebs/test/ind.dbf size 1M;Run the script which will built indexes*.Shutdown the database and take backup if necessary.Startup.* NB : For temporary tablespace skip this step.2. MISSING MIRRORED ONLINE REDO LOG FILES (DATABASE IS UP/DOWN)SCENARIODatabase opens neatly but in alert log two error messages are logged with errors :(error from lgwr. Error is also written in lgwr trace file)ora 313 open failed for members ..ora 312 name of redo log memeber missingora 7360 OS errorora 321 Can not update logfile headerTIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN 5 MIN.SOLUTIONShutdown the database.(shutdown).Startup mountQuery v$logfile view and find which member has become invalid.Query v$log view and find which group is current and size of group members (say it is b).If the member of current log group (say it is 1) is corrupted issue the following commands :Alter system switch logfileIf you can add one more member to corrupted log group ie maximum log member is not reached add one moremember to that groupAlter database add logfile member filespec to group 1;Shutdown the databaseStartup the databaseIf you can not add one more member to corrupted log groupcreate one more log group with equal members and size of non corrupted log group.Alter database group 3 (filespec,'filespec) size b;Drop corrupted log group.alter database drop logfile group 1;Manually remove other members of this corrupted log group (ie rm in unix)Shut down the databaseStartup the database3. RECOVER A LOST DATAFILE WITH NO BACKUP AND ALL ARCHIVED LOG FILESSCENARIODatabase startup fails with errors :ora 1157 can not identify datafileora 1110 give the name of datafile which is missing.REQUIREMENTFor full recovery, database should be in archivelog mode.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 10 mins.SOLUTIONShutdown the database.(shutdown).Startup mount;Query v$recover_fileQuery v$datafile and find the name of datafile which is missing.(say it is /prodebs/test/user_odc.dbf)Now issue the following commands in the given order :alter database datafile /prodebs/test/user_odc.dbf offline;alter database create datafile /prodebs/test/user_odc.dbf as /prodebs/test/user_odc1.dbf;(removed file) (new file)alter database datafile /prodebs/test/user_odc1.dbf online;alter database recover datafile /prodebs/test/user_odc1.dbf; or recover databaseIt will generally recover the database if you have all the archived file with youalter database open;Shutdown the database and take necessary backup if required.Start the database.

4. RECOVER A LOST DATAFILE WITH BACKUP AND ALL ARCHIVED LOG FILESSCENARIODatabase startup fails with errors :ora 1157 can not identify datafileora 1110 give the name of datafile which is missing.REQUIREMENTFor full recovery, database should be in archivelog mode.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN 5 mins.SOLUTIONShutdown the database.(shutdown).Startup mount;Query v$recover_fileQuery v$datafile and find the name of datafile which is missing.(say it is /prodebs/test/user_odc.dbf)Copy the archived datafile (old one that is in backup) and give following commands :recover database;alter database open;Shutdown the database and take necessary backup if required.Start the database.5. RECOVER A LOST DATAFILE WITH BACKUP AND MISSING ARCHIVED LOG FILES.SCENARIODatabase startup fails with errors :ora 1157 can not identify datafileora 1110 give the name of datafile which is missing.REQUIREMENTFor recovery, database should be in archivelog mode.CONDITIONRecovery will be incomplete.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 10 mins.SOLUTIONShutdown the database.(shutdown).Copy all your datafiles from backup except control file.Startup mount;Copy the archived datafile (old one that is in backup) and give following commands :recover database until cancelalter database open resetlogs;Shutdown the database and take necessary backup if required.Start the database.6. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH NO LOGICAL BACKUP AND RECOVERY.SCENARIODatabase startup fails with errors : (a) on monday morning (b) on thursdayora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.There is no export and import backup (Logical backup).Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Problem has occured on (a) on monday morning (b) on thursday.Here data file (s) associated with user tablespace is (are) lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION when Problem has occured on (a) on monday morningShutdown the database.(shutdown).Take complete backup of current database.Delete all the database files, redo log files and control files.Copy the all the database files , redo log files and control files from the backup.Start the database. There is no data loss.SOLUTION when Problem has occured on (a) on thursday morning. Here data loss will occur.Shutdown the database.(shutdown).Take complete backup of current database.Delete all the database files, redo log files and control files.Copy the all the database files , redo log files and control files from the backup.Start the database. There is data loss for monday, tuesday and wednessday.Ask user to reenter the data.7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH LOGICAL BACKUP AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.There is export and import backup. (Logical backup).Strategy : So along with coldback up a complete database backup is also taken.(i) After this on everyday, an incremental backup is also taken.(ii) After this on everyday, complete database backup is also taken.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Here data file (s) associated with user tablespace is (are) lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.[i] SOLUTION when Problem has occured on thursday morning and incremental backup is taken.Shutdown the database.(shutdown).Take complete backup of current database.Delete all the database files, redo log files and control files.Copy the all the database files , redo log files and control files from the backup.Start the database.Apply incremental export using import file starting from monday to wednesday.There is no data loss.[ii] SOLUTION when Problem has occured on thursday morning and daily complete database logicalbackup is taken.Shutdown the database.(shutdown).Take complete backup of current database.Delete all the database files, redo log files and control files.Copy the all the database files , redo log files and control files from the backup.Start the database.Apply wednesday complete export backup. There is no data loss.8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Here data file (s) associated with system tablespace is (are) lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTIONShutdown the database.(shutdown).Take complete backup of current database.Copy the missing system database file(s).Startup mount exclusive;recover database;alter database open;Database is ready for use.9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Here data file (s) associated with user tablespace is (are) lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION -1 (DATABASE RECOVERY)Shutdown the database.(shutdown).Take complete backup of current database.Copy the missing database file(s).Startup mount exclusive;recover database;alter database open;Database is ready for use.SOLUTION -2 (DATAFILE RECOVERY)*Shutdown the database.(shutdown).Take complete backup of current database.Copy the missing database file(s).Startup mount exclusive;Alter database datafile offline;Alter database open;recover datafile Alter database datafile online;Database is ready for use.* If multiple datafiles are lost use parallel recovery method (from muliple terminal use the same method for different files).SOLUTION -3 (TABLESPACE RECOVERY)Shutdown the database.(shutdown).Take complete backup of current database.Copy the missing database file(s).Startup mount exclusive;Alter database datafile offline;Alter database open;Alter tablespace offline temporary;recover tablespace ;Alter tablespace online ;Database is ready for use.10. LOSS OF NON SYSTEM DATAFILE WITH ROLLBACK SEGMENTS WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Datafile(s) associated with rollback segment tablespace is (are) lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION Shutdown the database.(shutdown).Take complete backup of current database.Comment the ROLLBACK_SEGMENT parameter or assign ROLLBACK_SEGMENT=(SYSTEM)in init.ora file before startup.Copy the missing rollback segment database file(s).Startup mount exclusive;alter database datafile offline;alter database open;Alter tablespace offline temporary;recover tablespace ;Alter tablespace online;Query dba_rollback_segs (column name segment_name, status) and note down the name of segmentname having status recovery (say they are r01, r02,r03).alter rollback segment r01 online;alter rollback segment r02 online;alter rollback segment r03 online;shutdown the database.Remove comment from rollback_segment parameter or remove system value and give the name of rollbacksegments which you want to be online when database starts.Start the database.Database is ready for use.11. LOSS OF UNARCHIVED ONLINE LOG FILES WHEN THEY ARE NOT MIRRORED WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.All the online redo log files are lost.All the data files and current control files are intact.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION Shutdown the database.(shutdown).Take complete backup of current database.Copy the all the database files from latest offline or online backup.Startup mount exclusive;recover database until cancel;alter database open resetlogs;shutdown the database.Take cold backup. It is strongely advised.Start the database.Database is ready for use.12. DATABASE CRASH DURING HOT BACKUP WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIOWhile taking hot backup, database crashes.(a) When Oracle Version is 7.2 or more.(b) When Oracle Version is 7.1.TIME TAKEN IN RECOVERY[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION (Oracle Version is 7.2 or more)Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;Query the view v$backup and get file # having status active. Now from v$datafile get the name offile which is active in v$backupalter database datafile end backup;alter database open ;shutdown the database.Start the database.Database is ready for use.SOLUTION (Oracle Version is 7.1 )Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;recover database ;( This may take significant amount of time if a large number of archived logs are to be applied)alter database open ;shutdown the database.Start the database.Database is ready for use.13. LOSS OF CONTROL FILE AND WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIO[a] Loss of control file when it is mirrored.[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.[c] Loss of control file when there is no backup and it is not mirrored (total loss).NB : Loss of control file when there is a backup and it is not mirrored but it is before last reset log option.(suppose database is open on day x with alter database startup resetlogs.So your control file shouldbe before xth day )startup/recover database using backup controlfileOracle error no is ora 1190 :control file or data file 1 is from before the last RESETLOGSora 1110 :name of system datafile.[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.(suppose database is open on day x with alter database startup resetlogs.So your control file shouldbe after xth day )ORA-01122: database file 1 failed verification checkORA-01110: data file 1: /prodebs/test/sys_odc.dbfORA-01207: file is more recent than control file old control fileTIME TAKEN IN RECOVERY[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION (Loss of control file when it is mirrored)Shutdown the database.(shutdown).Copy the second control file to this disk.Rename this control file to the lost one.Start the database.Database is ready for use.SOLUTION (Loss of all the control file(s) )Shutdown the database.(shutdown).run backup of controlfile which you might have taken using the following command :alter database backup controlfile to trace;It creates a script. After editing that script it looks like this :STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 2MAXDATAFILES 30MAXINSTANCES 1MAXLOGHISTORY 100LOGFILEGROUP 1 (/prodebs/test/redo_odc11.dbf,/prodebs/test/redo_odc12.dbf) SIZE 50K,GROUP 2 (/prodebs/test/redo_odc21.dbf,/prodebs/test/redo_odc22.dbf) SIZE 50KDATAFILE/prodebs/test/sys_odc.dbf,/prodebs/test/sys_odc1.dbf,/prodebs/test/user_odc.dbf,/prodebs/test/temp_odc.dbf,/prodebs/test/rbs_odc.dbf,/prodebs/test/ind.dbf;RECOVER DATABASEALTER SYSTEM ARCHIVE LOG ALL;ALTER DATABASE OPEN;shutdown the database.Start the database.Database is ready for use.SOLUTION (Loss of control file when there is a backup and it is not mirrored)Shutdown the database.(shutdown).Copy the old control file to this disk.startup mount exclusive;If you have any tablespace which is read only, take all the datafile offiline related to this tablespace.recover database using backup controlfile;Offline datafile should bring to online status. (alter database datafile < name of datafile> online;)(for read only tablespace)alter database open resetlogs;Shutdown the database.Take cold backup. It is strongely advised.Start the database.Database is ready for use.14. DATABASE SPACE MANAGEMENT WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY (RESIZING DATAFILE).SCENARIOSpace management when :(a) Oracle Version is 7.2 or more.(b) Oracle Version is 7.1.Oracle Error is : ora 00376 file # can not be read at this time.ora 01110 name of datafile.TIME TAKEN IN RECOVERY[a] DATABASE WILL BE READY FOR USE IN MIN 5 mins.[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION (Oracle Version is 7.2 or more)Shutdown the database.(shutdown).Take complete backup of current database.Startup open;Query the view v$datafile get the name of file which you want to resize.alter database datafile resize [m/k];shutdown the database.Take backup if necessary.Start the database.Database is ready for use.SOLUTION (Oracle Version is 7.1 )[a] Restore the datafile and apply recovery. Resizing is not possible.Shutdown the database.(shutdown).Take complete backup of current database.Copy the deleted datafileStartup mount exclusive;recover database ;( This may take significant amount of time if a large number of archived logs are to be applied)alter database open ;shutdown the database.Start the database.Database is ready for use.[b] If deleted datafile is not available in backup .Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;alter database add datafile as ;recover database ;( This may take significant amount of time if a large number of archived logs are to be applied)alter database open ;shutdown the database.Start the database.Database is ready for use.[c] Rebuild tablespaceRequirement : Logical backup is there.Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;alter database datafile offline;alter database open;alter tablespace offline;drop tablespace ;create tablespace datafile size [m/k];alter tablespace online;use export / import method to recover the loss data.shutdown the database.Take backup if necessary.Start the database.Database is ready for use.15. RECOVERY THROUGH RESETLOGS .CONDITIONS :[A] When online redo logs files are deleted.[B] Loss of all control files.[C] When recovery is done through old control files.Events[1]a- Cold backup is taken.b- Loss of redo log file.and media recovery. At this moment, a backup is taken.c- Loss of data file[2]a- Cold backup is taken.b- Loss of redo log file.and media recovery. At this moment, a backup is not taken.c- Loss of data file .SOLUTION [1]Shutdown the database.(shutdown).Take complete backup of current database.Copy the most recent cold backup of datafiles.Startup mount exclusive;Recover database;alter database open;shutdown the database.Take a cold backup.Start the database.Database is ready for use.Advantage : All the data will be recovered.SOLUTION [2]-iShutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;alter database datafile offline;alter database open;Export all the data from the tablespace (all objects in missing file will be inaccessible),Drop and recreate the tablespace.Import all the data taken from tablespace.recover datafile ;alter database open;shutdown the database.Take a cold backup.Start the database.Database is ready for use.Disadvantage : All the data will be lost that was entered in datafile 5;SOLUTION [2]-iiShutdown the database.(shutdown).Take complete backup of current database.Copy the most recent cold backup of datafile and control file only. Do not copy redo log file.Startup mount exclusive;Recover database;alter database open resetlogs;shutdown the database.Take a cold backup.Start the database.Database is ready for use.Disadvantage : All the data will be lost that after event b.16. LOSS OF DATA FILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Datafile(s) associated with user tablespace is (are) permanently lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;alter database create datafile ;recover datafile ;alter database open;shutdown the database.Start the database.Database is ready for use.17. SYSTEM CLOCK CHANGE AND POINT-IN-TIME RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Datafile(s) associated with user tablespace is (are) permanently lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;recover database until time ;alter database open resetlogs;shutdown the database.Take a cold backup of database;Start the database.Database is ready for use.18. OFFLINE TABLESPACES AND RECOVERY.SCENARIODatabase startup fails with errors on thursday morning.ora 1157 can not identify datafileora 1110 give the name of datafile which is missing and found to be user data file.Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activityon sunday.Datafile(s) associated with user tablespace is (are) permanently lost.TIME TAKEN IN RECOVERYDATABASE WILL BE READY FOR USE IN MIN 30 mins.SOLUTION 1Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;recover database ;alter database open ;recover tablespace ;alter tablespace online;Shutdown the database.Start the database.Database is ready for use.SOLUTION 2 (better)Shutdown the database.(shutdown).Take complete backup of current database.Startup mount exclusive;Query the view v$datafile and note which datafile is offline say it is .alter database datafile BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;2. Cumulative Restore OverviewBecause a CUMULATIVE backup is taken each day during the week, we need to restore 2 times: 1st we have to restore the full backup and after that the last cumulative backup (for a complete restore). If differential backups are taken we have to restore all the differential backups until the database crash.3. Simulate a disk crash on Tuesday at noonNow delete a datafile,/DB1/oradata/db1/users01.dbffor instance, SHUTDOWN the database using ABORT option and restart the database.The following message will appear:

7. Restore & Recover the lost filea) connect to the RMAN:rman catalog rman/r@dbr target /b) restore the/DB1/oradata/db1/users01.dbffrom the full backup:RMAN>restore datafile ''/DB1/oradata/db1/users01.dbf';'

So, this command restores the file from the full backup.c) apply the last changes from the cumulative backup:RMAN>recover datafile ''/DB1/oradata/db1/users01.dbf';' from tag='DAY_BK_CUMULATIVE1';

How to validate a backup in an Oracle database1. Validating a logical export (taken using exp utility)First we have to take a look to the export log file. Also, we can import the data in another database to see if the import is done without errors.2. Validate a physical backup (taken using RMAN "image copy" option or by copying the files at the OS level)In this case we have to validate the .dbf files. This is done by using DBVERIFY Utility.

Total Pages Examined: Number of blocks in the fileTotal Pages Processed:Number of blocks verifiedTotal Pages Failing:Number of blocks that failed the data/ index/ segment block checkTotal Pages Marked Corrupt:Number of corrupted blocksTotal Pages Influx:Number of blocks being read and written to in parallel.

If there are no errors during the recovery ... validate, the backup is good.This command (recovery database validate) doesn't recover the database; only the check is done.b)RMAN>VALIDATE BACKUPSET

To find the backup set of the backups we can run the RMAN commandLIST BACKUP;c)using RMAN report options, likeREPORT UNRECOVERABLE DATABASE; (examine all datafiles, but not the archivelog files !!!) Oracle export / import utility (exp / imp)1. What is the Import/ Export Utility ?Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.2. Which are the Import/ Export modes ?a)Full export/export The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use thefullexport parameter for a full export.b)TablespaceUse thetablespacesexport parameter for a tablespace export.c)UserThis mode can be used to export and import all objects that belong to a user. Use the owner export parameter and thefromuserimport parameter for a user (owner) export-import.

d)TableSpecific tables (or partitions) can be exported/imported with table export mode. Use thetablesexport parameter for a table export/ import mode.3. Is it possible to exp/ imp to multiple files ?Yes, is possible. Here is an example:exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log4. How we can use exp/ imp when we have 2 different Oracle database versions? expmust be of thelower version impmust match thetarget version5. What I have to do before importing database objects ?Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.If there are any constraints on the target table, theconstraints should be disabled during the importandenabled after import.6. Is it possible to import a table in a different tablespace ? By default, NO. Because is no tablespace parameter for the import operation.However this could be done in the following manner: (re)create the table in another tablespace (the table will be empty) import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated) modify this script to create the indexes in the tablespace we want import the table using IGNORE=y option (because the table exists) recreate the indexesHere is an example of INDEXFILE:

7. In which cases imp/exp is used ? Eliminate database fragmentation Schema refresh(move the schema from one database to another) Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption) Transporting tablespaces between databases Backup database objects8. How we can improve the EXP performance ? Set the BUFFER parameter to a high value (e.g. 2M) If you run multiple export sessions, ensure they write to different physical disks.9. How we can improve the IMP performance ? Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes Store the dump file to be imported on a separate physical disk from the oracle data files If there are any constraints on the target table, theconstraints should be disabled during the importandenabled after import Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB) ) and COMMIT =y or setCOMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.) use the direct path to import the data (DIRECT=y) (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init.ora file (if possible) Set the LOG_BUFFER to a big value and restart oracle.10. Which are the EXP options ?EXP OptionDefault value Description

bufferSpecifies the size, in bytes, of the buffer (array) used to insert the data

compressNWhen Y, export will mark the table to be loaded as one extent for the import utility. If N, the current storage options defined for the table will be used. Although this option is only implemented on import, it can only be specified on export.

consistentNSpecifies theset transaction read onlystatement for export, ensuring data consistency. This option should be set to Y if activity is anticipated while theexpcommand is executing. If Y is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

constraintsYSpecifies whether table constraints should be exported with table data.

directNDetermines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance.

feedback0Determines how often feedback is displayed. A value of feedback=ndisplays a dot for everynrows processed. The display shows all tables exported not individual ones.

fileThe name of the export file. Multiple files can be listed, separated by commas. When export fills thefilesize, it will begin writing to the next file in the list.

filesizeThe maximum file size, specified in bytes.

flashback_scnThe system change number (SCN) that export uses to enable flashback.

flashback_timeExport will discover the SCN that is closest to the specified time. This SCN is used to enable flashback.

fullThe entire database is exported.

grantsYSpecifies object grants to export.

helpShows command line options for export.

indexesYDetermines whether index definitions are exported. The index data is never exported.

logThe filename used by export to write messages.

object_consistentNSpecifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent.

ownerOnly the owners objects will be exported.

parfileThe name of the file that contains the export parameter options. This file can be used instead of specifying all the options on the command line for each export.

queryAllows a subset of rows from a table to be exported, based on a SQLwhereclause.

recordlengthSpecifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. If not specified, this parameter defaults to the value specific to that platform. The highest value is 64KB.

resumableNEnables and disables resumable space allocation. When Y, the parametersresumable_nameandresumable_timeoutare utilized.

resumable_nameUser defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unlessresumable = Y.

resumable_timeout2hThe time period in which an export error must be fixed. This parameter is ignored unlessresumable = Y.

rowsYIndicates whether or not the table rows should be exported.

statisticsESTIMATEIndicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.

tablesIndicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.

tablespacesIndicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.

transport_tablespaceNEnables the export of metadata needed for transportable tablespaces.

triggersYIndicates whether triggers defined on export tables will also be exported.

tts_full_checkFALSEWhen TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.

useridSpecifies the userid/password of the user performing the export.

volsizeSpecifies the maximum number of bytes in an export file on each tape volume.

Example:expsystem/sfile=C:\emp.dmptables=scott.emplog=C:\emp.log (Windows) orexpuserid=system/sfile=C:\emp.dmptables=scott.emplog=C:\emp.log (Windows)USERID must be the first parameter on the command line.11. Which are the IMP options ?IMP OptionDefault value Description

bufferSpecifies the size, in bytes, of the buffer (array) used to insert the data

commitNSpecifies whether import should commit after each array insert.By default, import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.

compileYTells import to compile procedural objects when they are imported.

constraintsYSpecifies whether table constraints should also be imported with table data.

datafiles(only with transport_tablespace)This parameter lists data files to be transported to the database.

destroyNOverwrite tablespace data file

feedback0Determines how often feedback is displayed. A value of feedback=100 displays a dot for every 100 rows processed. This option applies to the total tables imported, not individual ones. Another way to measure the number of rows that have been processed is to execute the following query while the import is active:SELECTrows_processed FROMv$sqlarea WHEREsql_textlike'INSERT %INTO "%' ANDcommand_type=2 ANDopen_versions>0;

fileThe name of the export file to import. Multiple files can be listed, separated by commas. When export reaches the filesize it will begin writing to the next file in the list.

filesizeMaximum size of each dump file

fromuserA comma delimited list of schemas from which to import. If the export file contains many users or even the entire database, the fromuser option enables only a subset of those objects (and data) to be imported.

fullThe entire export file is imported.

grantsYSpecifies to import object grants.

helpShows command line options for importimp -help or imp help=y

ignoreNSpecifies how object creation errors should be handled. If a table already exists and ignore=y, then the rows are imported to the existing tables, otherwise errors will be reported and no rows are loaded into the table.

indexesYDetermines whether indexes are imported.

indexfileSpecifies a filename that contains index creation statements. This file can be used to build the indexes after the import has completed.

logThe filename used by import to write messages.

parfileThe name of the file that contains the import parameter options. This file can be used instead of specifying all the options on the command line.

recordlengthSpecifies the length of the file record in bytes. This parameter is only used when transferring export files between operating systems that use different default values.

resumableNWhen Y, the parameters resumable_name and resumable_timeout are utilized.

resumable_nameUser defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.

resumable_timeout2hThe time period in which an error must be fixed. This parameter is ignored unless resumable=Y.

rowsYIndicates whether or not the table rows should be imported.

showNWhen show=y, the DDL within the export file is displayed.

skip_unusable_indexesNDetermines whether import skips the building of indexes that are in an unusable state.

statisticsALWAYSDetermines the level of optimizer statistics that are generated on import. The options include ALWAYS, NONE, SAFE and RECALCULATE. ALWAYS imports statistics regardless of their validity. NONE does not import or recalculate any optimizer statistics. SAFE will import the statistics if they appear to be valid, otherwise they will be recomputed after import. RECALCULATE always generates new statistics after import.

streams_configurationYDetermines whether or not any streams metadata present in the export file will be imported.

streams_instantiationNSpecifies whether or not to import streams instantiation metadata present in the export file

tablesIndicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.

tablespacesWhen transport_tablespace=y, this parameter provides a list of tablespaces.

to_userSpecifies a list of user schemas that will be targets for imports.

transport_tablespaceNWhen Y, transportable tablespace metadata will be imported from the export file.

tts_ownersWhen transport_tablespace=Y, this parameter lists the users who own the data in the transportable tablespace set.

useridSpecifies the userid/password of the user performing the import.

Example:impsystem/managerfile=/APPS/x.dmptables=xfromuser=cstouser=cs (Unix) orimpuserid=system/managerfile=/APPS/x.dmptables=xfromuser=cstouser=cs (Unix)Note: USERID must be the first parameter on the command line.12. Which are the common IMP/EXP problems? ORA-00001: Unique constraint ... violated- Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh.. IMP-00015: Statement failed ... object already exists...- Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows. ORA-01555: Snapshot too old- Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time). ORA-01562: Failed to extend rollback segment- Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter ) while importing. RMAN incremental backup for an Oracle DB1. Incremental database backup OverviewSometimes we need to backup the database changes only from the last backup (only the last changes are backed up). This is an incremental backup. There are 2 types of incremental backup: DIFFERENTIAL (by default) & CUMULATIVE.NOTE: The incremental backups are only for the DATA files.2. Incremental DIFFERENTIAL backupDIFFERENTIALbackup= which backs up all blocks changed after the most recent incremental backup at level 1 or 0. (See Picture 1). The following RMAN command is used to take a DIFFERENTIAL database backup:RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;3. Incremental CUMULATIVE backupCUMULATIVE backup= which backs up all blocks changed after the most recent incremental backup at level 0. (See Picture 2)The following RMAN command is used to take a CUMULATIVE database backup:RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;4. The advantages of each types of incremental backupHere are the advantages of each types of incremental backup:Advantages of the DIFFERENTIALbackupAdvantages of the CUMULATIVEbackup

1. Less space disk used2. Less network traffic3. Less time to take a backup1. Less time to recover

RMAN Configuration1. Which is the default RMAN configuration ?show all;

RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/10gOHome/dbs/snapcf_db10.f'; # defaultNOTES: The parameters which are currently modified are in bold. The changes in the RMAN configuration are saved automatically in the control file/ RMAN catalog.2. How could I restore the actual configuration to the default value ?CONFIGURE RETENTION POLICYCLEAR;CONFIGURE BACKUP OPTIMIZATIONCLEAR;CONFIGURE DEFAULT DEVICE TYPECLEAR;CONFIGURE CONTROLFILE AUTOBACKUPCLEAR;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISKCLEAR;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBTCLEAR;CONFIGURE DEVICE TYPE DISKCLEAR;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISKCLEAR;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBTCLEAR;CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISKCLEAR;CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBTCLEAR;CONFIGURE CHANNEL DEVICE TYPE DISKCLEAR;CONFIGURE CHANNEL DEVICE TYPE SBTCLEAR;CONFIGURE MAXSETSIZECLEAR;CONFIGURE SNAPSHOT CONTROLFILE NAMECLEAR;3. Using substitution variablesRMAN can make use of substitution variables in creating format strings to generate UNIQUE file names. If the file names are not unique the files will be overwritten and the data will be lost.FormatDescription

%dspecifies the database name.

%uspecifies an 8-character name constituted by compressed representations of the backup set number and the time the backup set was created.

%pspecifies thebackup piece numberwithin the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.

%cSpecifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not issue the set duplex command, then this variable will be 1 for regular backup sets and 0 for proxy copies. If you issued set duplex, the variable identifies the copy number: 1, 2, 3, or 4.

%USpecifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, RMAN uses %U by default.

%tspecifies thebackup set timestamp. The combination of %s and %t can be used to form a unique name for the backup set.

%sspecifies thebackup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.

4. Configure RETENTION POLICYCONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;>> After 30 days the backup will become OBSOLETE.CONFIGURE RETENTION POLICY TO REDUNDANCY 3;>> The latest 3 backups will NOT be OBSOLETE. The others will be.When configuring a retention policy, RMAN will NOT cause backups to be automatically deleted.REPORT OBSOLETE;->List the backups (on disk) that have become obsolete with the current retention policy.DELETE OBSOLETE;->Delete the obsolete backup files.delete noprompt obsolete;NOTES: crosscheck backup; -> check if the backup filesexist physically on the disk crosscheck copy; -> check if the files of a copy operationexist physically on the disk list backup;-> To list all backup sets, backup pieces list expired backup;-> To identify those backups that were not found during a crosscheck DELETE EXPIRED BACKUP; -> To delete the information about the expired backups in the RMAN repository DELETE EXPIRED COPY; -> To delete the information about the expired copies in the RMAN repository5. Configure DEFAULT DEVICE TYPECONFIGURE DEFAULT DEVICE TYPETODISK;-->by defaultCONFIGURE DEFAULT DEVICE TYPETOSBT;This is overridden by the RUN command, or by DEVICE TYPE on the BACKUP command itself.6. Configure CONTROLFILE AUTOBACKUP(starting from 9i)CONFIGURE CONTROLFILEAUTOBACKUPON;CONFIGURE CONTROLFILEAUTOBACKUPOFF; --> by defaultRMAN writes both the CONTROLFILE and the SPFILE (if the database was started with an SPFILE) to the same backup piece.To set the location of the Control file backup:CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICETYPE DISK TO '/oracle/RMAN_backup/%F';The %F tag is essential for RMAN to be able to restore the file without a recovery catalog.RMAN incomplete recovery (in ARCHIVELOG mode)- Using the TIME parameter:run { shutdown immediate; startup mount;set until time "to_date('May 17 2008 10:20:09 pm','Mon DD YYYY HH:MI:SS am')"; restore database; recover database; alter database openresetlogs;}- Using the SCN parameter:run { shutdown immediate; startup mount;set until scn 3400; restore database; recover database; alter database openresetlogs;}- Using the SEQUENCE parameter:run { shutdown immediate; startup mount;set until sequence 12903; restore database; recover database; alter database openresetlogs;}NOTE:The incomplete recovery requires the database to be opened using theRESETLOGSoption.RMAN incomplete recovery (in NOARCHIVELOG mode)RMAN Incomplete Recovery OverviewAn Incomplete Recovery is a Recovery which is done without restoring the database up to the failure time. A typical case incomplete recovery is when the database is not in ARCHIVELOG. In this article I will show how a backup could be taken for a database in NOARCHIVELOG mode and how a database could be restored in case of failure.Take a Full Backup with RMAN(database in NOARCHIVELOG)Because the database is in NOARCHIVELOG mode, there is no need to take the archive log files and the log files in backup. To take a backup with RMAN for a database in NOARCHIVELOG, the instance must be in MOUNT state. However, if the database is OPEN state, the instance could be put in mount state for the backup, and after that the database will be opened. Here is the script (RMAN_NOARCHIVELOG_backup.sh) we can use to take an backup for a database in NOARCHIVELOG mode:#/usr/bin/kshORACLE_HOME=/DB1export ORACLE_HOMEORACLE_SID=db1export ORACLE_SIDRMAN_LOG_FILE=/home/oracle/Desktop/Backup_rman/log/hot_database_backup.`date +%y%m%d%H%M`.outecho >> $RMAN_LOG_FILEchmod 666 $RMAN_LOG_FILEecho Script $0 >> $RMAN_LOG_FILEecho ==== started on `date` ==== >> $RMAN_LOG_FILEecho >> $RMAN_LOG_FILEORACLE_USER=oracleTARGET_CONNECT_STR=sys/sRMAN=$ORACLE_HOME/bin/rmanBACKUP_TYPE="INCREMENTAL LEVEL 0"echo >> $RMAN_LOG_FILEecho "RMAN: $RMAN" >> $RMAN_LOG_FILEecho "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILEecho "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILEecho "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILEecho "BACKUP_TYPE: $BACKUP_TYPE" >> $RMAN_LOG_FILEecho >> $RMAN_LOG_FILECMD_STR=""$RMAN target $TARGET_CONNECT_STR catalog rman/r@dbr database closeddatabase dismountedOracle instance shut down

RMAN>connected to target database (not started)Oracle instance starteddatabase mounted

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytesVariable Size 268437536 bytesDatabase Buffers 645922816 bytesRedo Buffers 7163904 bytes

RMAN>RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23>allocated channel: ch00channel ch00: sid=157 devtype=DISK

allocated channel: ch01channel ch01: sid=155 devtype=DISK

allocated channel: ch02channel ch02: sid=154 devtype=DISKStarting backup at 26-MAR-08channel ch00: starting incremental level 0 datafile backupsetchannel ch00: specifying datafile(s) in backupsetchannel ch01: backup set complete, elapsed time: 00:00:01Finished backup at 26-MAR-08

released channel: ch00

released channel: ch01

released channel: ch02

allocated channel: ch00channel ch00: sid=157 devtype=DISK

Starting backup at 26-MAR-08channel ch00: starting full datafile backupsetchannel ch00: specifying datafile(s) in backupsetincluding current control file in backupsetchannel ch00: starting piece 1 at 26-MAR-08channel ch00: finished piece 1 at 26-MAR-08piece handle=/home/oracle/Desktop/Backup_rman/backup/cntrl_50_1_650415621 tag=TAG20080326T230021 comment=NONEchannel ch00: backup set complete, elapsed time: 00:00:01Finished backup at 26-MAR-08

released channel: ch00

RMAN>RMAN>database opened

RMAN>RMAN>

Recovery Manager complete.[oracle@PROD scripts]$Restore the database backupHere is the way the database could be restored if all the files (+control files) are lost:$rman target / catalog rman/r@dbr;RMAN> startup nomount;

connected to target database (not started)Oracle instance started

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytesVariable Size 272631840 bytesDatabase Buffers 641728512 bytesRedo Buffers 7163904 bytes

RMAN> restore controlfile;

Starting restore at 26-MAR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/backup/cntrl_50_1_650415621.Finished restore at 26-MAR-08

RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 26-MAR-08Starting implicit crosscheck backup at 26-MAR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISKCrosschecked 36 objectsFinished implicit crosscheck backup at 26-MAR-08..

Finished restore at 26-MAR-08

RMAN> alter database open resetlogs;

database openednew incarnation of database registered in recovery catalogstarting full resync of recovery catalogfull resync complete

RMAN> exit;NOTE: For a database which is in NOARCHIVELOG mode a cold backup by copying the file is more appropriate. This example is just to illustrate how RMAN work. RMAN Maintenance1. How to register a new database with the RMAN CatalogTo register a new database to the RMAN catalog we have to be connected to the RMAN catalog and to the new (target) database. After that we will use theREGISTER DATABASE;command. See below:

During the database registration a full resynchronization is done automatically.2. Resynchronization of the DatabaseRMAN catalog must keep all the metadata information about the target database up-to-date. And every time some RMAN commands (like BACKUP & COPY) are executed the RMAN catalog is synchronized with the database (controlfile).There are 2 types of synchronization: FULL: all the controlfile information is synchronize with the catalog PARTIAL:the physical schema information (data files, tablespaces, online files) is not synchronized; the information about the backups and new archive log is synchronized. When you runRESYNCCATALOG, RMANcreates a snapshot control filein order to obtain a read-consistent view of the control file, then updates the recovery catalog with any new information from the snapshot. TheRESYNCCATALOGcommand updates the classes or records described in the following table.Record TypeDescription

Log historyRecords that are created whenever a log switch occurs. Note that log history records describe an online log switch, not a log archival.

Archived redo logsRecords associated with archived logs that were created by archiving an online redo log, copying an existing archived redo log, or restoring backups of archived redo logs.

BackupsRecords associated with backup sets, backup pieces, proxy copies, and image copies.

Physical schema(Only in FULL SYNC)Records associated with datafiles and tablespaces. If the target database is open, then rollback segment information is also updated.

3. Resetting a DatabaseWhenALTER DATABASE OPEN RESETLOGis run a new database incarnation is created. This could be see in theV$DATABASE_INCARNATIONview of the target database.>>If this is done using RMAN, the RMAN will recognize the new database as a new one and will use this new incarnation number for the following backups/ restore.>>If this is NOT done using RMAN, the RMAN will NOT recognize the new database as a new one. To let RMAN know that this is a new incarnation we have to reset the database using the command:RESET DATABASE;If we want to restore backups of a prior incarnation of the database, we can useRESET DATABASE TO INCARNATION ;command.4. Generate Reports & ListsRMAN>list backup;-> To list all backup sets, backup piecesRMAN>list expired backup;-> To identify those backups that were not found during a CrosscheckRMAN>list backup by file; -> List Backup by FileRMAN>list archivelog all;-> List all archived log filesRMAN>list backup summary;-> Backups summaryRMAN>crosscheck backup; -> check if the backup files exist physically on the diskUse theneed backupoption to identify which datafiles need a new backup:RMAN>report need backup days = 9 database;# needs at least 9 days of logs to recoverRMAN>report need backup days = 23 tablespace system;RMAN>report need backup days = 12 datafile '/oracle/oradata/data_file_15.dbf';If complete recovery of a datafile requires more than the specified number of incremental backups, then RMAN considers it in need of a new backup.RMAN>report need backup incremental = 3 database;RMAN>report need backup incremental = 6 tablespace system;RMAN>report need backup incremental = 8 datafile '/oracle/oradata/data_file_15.dbf';Use theobsoleteoption to identify which backups are obsolete because they are no longer needed for recovery. Theredundancyparameter specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete (if you do not specify the parameter,redundancydefaults to 1).RMAN>report obsolete redundancy = 2; -> Lists backups or copies that have at least 2 more recent backups or copiesFor more information about REPORT command clickhere.For more information about RMAN maintenace clickhere.5. Unregister a Database from the CatalogTo unregister a a database from a RMAN catalog we have to use the commandUNREGISTER DATABASE; We must be connected to the RMAN Catalog and also to the target database. See bellow:

6. Backup the CatalogTo be sure that the RMAN Catalog is not lost, the RMAN database must be in ARCHIVELOG and maintained as an Oracle production database (must be backed up daily). Also, logical backups for RMAN schema could be taken with imp/exp utility.7.Delete the database filesTo test the backup, I stop the database (even if is not mandatory :) ), I delete all the database files (control files, log file, data files) and after that I restore the files using RMAN.If I want to startup the database I receive a message of error because the control files are not present:

Restore & Recover the database using RMANTo restore the database (physically the files are restored, but the files = the database) we have to connect to the target database and recovery catalog, to startup the database in nomount state, restore the control file and alter the database to be in mount state.

In the mount state we have to run:RMAN> RESTORE DATABASE;(We can run RMAN> RESTORE DATABASE VALIDATE, as well to check that all is ok and database could be opened.)RMAN> RECOVER DATABASE;And after that we can open the database (in RESETLOG mode)RMAN> ALTER DATABASE OPEN RESETLOGS;The new database will be registered with the RMAN catalog automatically:

Oracle MBR (Media Block Recovery in Oracle)1. Test that there are no data block corruption in scott.EMP table

alter system flush buffer_cache;force to get the data from the data file and not from the buffer cache.2. Generate a data block corruption for EMP table >> Find a block which keep EMP data :SQL> col HEADER_BLOCK format 99999999SQL> col SEGMENT_NAME format a12SQL> col NAME format a35SQL>selectd.name, s.header_block, s.segment_namefromdba_segments s, v$datafile_header dwheres.TABLESPACE_NAME = d.TABLESPACE_NAMEands.segment_name = 'EMP'andowner='SCOTT';

>> Write something wrong to the block 28 (the next block after the header) to corrupt the block (at the OS level):$ dd if=/dev/zero of=/DB1/oradata/db1/users01.dbf bs=8192 conv=notrunc seek=28 count=1bs= block sizeconv= convert the file ( In this case = do not truncate the output file)seek= which block will be corruptcount= how many blocks>> Verify the data block corruption:SQL>select * from emp;select * from emp*ERROR at line 1:ORA-08103: object no longer existsNOTE:Select count(*) from emp;could be used also if there are no indexes on the emp table;3. Recover the corrupted block with RMANRMAN>blockrecover datafile 4 block 28;

Starting blockrecover at 03-FEB-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: restoring block(s)handle=/home/oracle/Desktop/Backup_rman/backup/bk_34_1_645659824tag=HOT_D B_BK_LEVEL0channel ORA_DISK_1: blockrestore complete, elapsed time: 00:00:01

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /DB1/flash_recovery_a rea/DB1/archivelog/2008_02_02/o1_mf_1_3_3tb95vdp_.arcarchive log thread 1 sequence 4 is already on disk as file /DB1/flash_recovery_a rea/DB1/archivelog/2008_02_02/o1_mf_1_4_3tbbkzk3_.arcchannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1:restoring archive logarchive log thread=1 sequence=1channel ORA_DISK_1: reading from backup piece /home/oracle/Desktop/Backup_rman/b ackup/al_38_1_645659875channel ORA_DISK_1: restored backup piece 1..media recovery complete, elapsed time: 00:00:01Finished blockrecover at 03-FEB-084. Test that there are no data block corruption in scott.EMP table select EMPNO,ENAME,JOB,MGR, HIREDATE from emp;will returm the emp data:Cold Backup in Oracle (User Managed Backup)1. User Managed Backup OverviewA user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.The user-managed backups could be take at the following levels: Data file level Tablespace level Database level2. How could I take an online TABLESPACE level backup ? the database should be in ARCHIVELOG mode put the tablespace in "Begin Backup" mode (example:ALTERTABLESPACEusersBEGINBACKUP;) copy the physical files associated with this tablespace on another location using OS commands put the tablespace in "End Backup" mode (example:ALTERTABLESPACEusersENDBACKUP;) Archive the unachieved redo logs so that the redo required to recover the tablespace backups is archived (SQL>ALTERSYSTEMARCHIVELOGCURRENT;) Take a backup of all archived redo log files generated between Begin Backup and End Backup using OS commandsNOTES: Many tablespaces could be backed up in parallel. However, online redo logs can grow large if multiple users are updating these tablespaces because the redo must contain a copy of each changed data block. Oracle doesn't recommend this. When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files. When in backup mode, Oracle will write complete changed blocks to the redo log files.Normally only deltas (change vectors) are logged to the redo logs.This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups. To fix this problem, simply switch to RMAN backups. If the tablespace is in READ ONLY mode, we don't need to put the tablespace in Backup Mode.3. Which are the files which could be backed up ?SELECTnameFROMv$datafile;SELECTmemberFROMv$logfile; SELECTnameFROMv$controlfile;To view which file correspond to which tablespace you can run:SELECTt.NAME "Tablespace", f.NAME "Datafile"FROMV$TABLESPACE t, V$DATAFILE fWHERE t.TS#=f.TS#ORDERBYt.NAME;4. How could I take an online FILE level backup ? the database should be in ARCHIVELOG mode put the datafile in OFFLINE mode (example:ALTERDATABASEDATAFILE'C:\oradata\file1.dbf'OFFLINE;) copy the physical file on another location using OS commands put the datafile in ONLINE mode (example:ALTERDATABASEDATAFILE'C:\oradata\file1.dbf'ONLINE;) backup the control file as the database has gone through structural changes5. TABLESPACE RecoveryThis is done withSQL>RECOVER TABLESPACEcommand. It is possible to perform a tablespace recovery while the rest of the database is online.The prerequisites for a tablespace recovery are: The tablespace must be OFFLINE (the database could be online) Only COMPLETE recovery is possible SYSTEM tablespace never can be recovered because is online all the time.6. DATAFILE RecoveryThis is done withSQL>RECOVER DATAFILEcommand. It is possible to perform a data file recovery while the rest of the database is online or offline.The prerequisites for a data file recovery are: The data file must be OFFLINE (the database could be online) SYSTEM data files never can be recovered because SYSTEM tablespace is online all the time.7. Closed Database BackupIf the database is down (shutdown IMMEDIATE, NORMAL) was used, we have only to copy the data files, redo log files and control files to a new location. This kind of backup is used for a database in NOARCHIVELOG which is not used for a 24x7 business.8. Open Database BackupAn Open Database Backup is a backup taken when the database is up and running. This is done by putting the tablespace in Backup mode and copying the data files and control files. All the latest archived log files must be copied as well. The V$BACKUP and V$DATAFILE_HEADER should be queried after the database backup to see if all the data files are in online mode.9. Backup the CONTROL fileBackup the binary file:ALTER DATABASE BACKUP CONTROLFILETO'C:\backups\control1.bkp';Generate the script to recreate the control file:ALTER DATABASE BACKUP CONTROLFILETO TRACE AS'C:\backups\control1.txt';orALTER DATABASE BACKUP CONTROLFILETO TRACE ( the file will be created in USER_DUMP_DEST )10. Perform Cleanup after a Failed Online BackupAn Online Backup failure can occurs if: An instance failure occurs An OS failure occurs The database is accidentally shut down.The database could be stopped by usingshutdown abort. See the picture below:

When the database is brought up a media recovery is needed and the database will be in mount state:

In mount state we can query the files to see what is happening (optional):

In Oracle 9i and + we can use RECOVER DATABASE to do an automatic recovery (the database will be consistent again and the tablespace will not be in Backup mode). After this recovery a new backup could be taken.

To see the status of the files during the online user-managed backups the following select could be used: SELECTdecode(b.status, 'ACTIVE', 'BACKUP_IS_RUNNING',b.status) "Backup_Status", b.time "Backup_Start_Time", fh.status "File status", fh.tablespace_name "Tablespace_Name", fh.name "File_Name" FROMv$backup b, v$datafile_header fh WHEREb.file#=fh.file#; RMAN & SMR (Server-ManagedRecovery)1.What is RMAN?RMAN can be used to backup and restore database files, archive logs, and control files. It can also be used to perform complete or incomplete database recovery. Note that RMAN cannot be used to backup initialization files or password files.RMAN starts Oracle server processes on the database to be backed up or restored. The backup, restore, and recovery is driven through these processes hence the term server-managed recovery.Note that SMR can also be controlled from OEMs Backup Manager GUI. Thisarticle will not discuss Backup Manager.2. Terminology2.1. Backup setsA backup set is characterised by the following:- Contains one or more datafiles or archivelogs- Stored in an Oracle proprietary format- Comprises a complete set of backup pieces- Constitutes a full or incremental backup2.2. Backup piecesA backup set is comprised of a number of backup pieces. Each backup piece is a single output file. The size of a backup piece can be restricted; if the size is not restricted, the backup set will comprise one backup piece. Backup piece size should be restricted to no larger than the maximum file size that your filsystem will support.2.3. Image copiesAn image copy is a copy of a single file (datafile, archivelog, or controlfile). It is very similar to an O/S copy of the file. It is not a backupset or a backup piece. No compression is performed.2.4. Full backup setsA full backup is a backup of one or more datafiles that contains all used blocks in the datafile. Blocks that have never been used are not backed up i.e. oracle performs backup set compression.2.5. Incremental backup setsAn incremental backup is a backup of one or more datafiles that contains only those blocks that have been modified since a previous backup at the same or lower level. As with full backups, compression is performed.2.6. File multiplexingDatablocks from multiple datafiles can be multiplexed in the same backupset.2.7. Recovery catalog resyncingResyncing the recovery catalog involves synchronising the recovery catalog with the target database controlfile. Certain operations perform this implicitly. To resync manually, issue the resync catalog; command from RMAN. The catalog should be resynced frequently, especially if the target database generates many archive logs. It should also be resynced after making any structural changes to the target database. Although the target databases controlfile is automatically updated whenever new controlfile records are created (for example, creation of new archived logs or new datafiles), if the target is not resyncd and a backup controlfile is restored, the new records must be cataloged manually (catalog archivelog ;).2.8. Snapshot ControlfileWhen RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. The default name for the snapshot control file is port-specific. Use the set snapshot controlfile name to file_name command to change the name of the snapshot control file; subsequent snapshot control files that RMAN creates use the name specified in the command. The snapshot control file name can also be set to a raw device. This operation is important for OPS databases in which more than one instance in the cluster use RMAN because server sessions on each node must be able to create a snaphost control file with the same name and location.2.9. Resetlogs OperationWhenever you open the database with the RESETLOGS option, all datafiles get a new RESETLOGS SCN and timestamp. Archived redo logs also have these two values in their header. Because Oracle will not apply an archived redo log to a datafile unless the RESETLOGS SCN and timestamps match, the RESETLOGS operations prevents you from corrupting your datafiles with old archived logs.2.1.0 Database IncarnationWhenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database. The new version of the reset database is called a new incarnation. All archived redo logs generated after the point of the RESETLOGS on theold incarnation are invalid in the new incarnation.2.1.1. Resetting the Recovery CatalogBefore you can use RMAN again with a target database that you have opened with the RESETLOGS option, notify RMAN that you have reset the database incarnation. The reset database command directs RMAN to create a new database incarnation record in the recovery catalog. This new incarnation record indicates the current incarnation. RMAN associates all subsequent backups and log archiving done by the target database with the new database incarnation. If you issue the ALTERDATABASE OPEN RESETLOGS statement but do not reset the database, then RMAN cannot access the recovery catalog because it cannot distinguish between a RESETLOGS command and an accidental restore of an old control file. By resetting the database, you inform RMAN that the database has been opened with the RESETLOGS option. In the rare situation in which you wish to undo the effects of opening with the RESETLOGS option by restoring backups of a prior incarnation of the database, use the reset database to incarnation key command to change the current incarnation to an older incarnation.3. The recovery catalogThe recovery catalog is a repository of information that is used and maintained by RMAN. RMAN uses the information in the recovery catalog to determine how to execute requested backup and restore actions... The recovery catalog can be in a schema of an existing Oracle8 database. However if RMAN is being used to backup multiple databases, it is probably worth creating a dedicated recovery catalog database. THE RECOVERY CATALOG DATABASE CANNOT BE USED TO CATALOG BACKUPS OF ITSELF.To set up the recovery catalog, firstly ensure that catalog and catproc have been run, then execute the following:SVRMGR> spool create_rman.logSVRMGR> connect internalSVRMGR> create user rman identified by rmantemporary tablespace tempdefault tablespace rcvcat quota unlimited on rcvcat;SVRMGR> grant recovery_catalog_owner to rman;SVRMGR> grant connect, resource to rman;Note: Following steps only apply for an Oracle8 8.0.x catalog creation.SVRMGR> connect rman/rmanSVRMGR> @?/rdbms/admin/catrmanCheck create_rman.log for errors. The above commands assume that theTEMP and RCVCAT tablespaces have been created.In Oracle8i the catalog is created a little differently.Note: Following steps only apply to Oracle8i 8.1.5 and greater.From the UNIX shell run:% set ORACLE_SID=RCAT% rman catalog rman/rmanRMAN> create catalog;This will generate the recovery catalog schema in the default tablespace forRMAN.Also ensure that catproc has been run on the target database as SYS(do _not_ use SYSTEM); RMAN makes extensive use of RPCs.It is very important that the recovery catalog database is backed upregularly and frequently.Note: Although you are not required to use a recovery catalog with RMAN, it is recommended. Because most of the information in the recovery catalog is available via the target databases controlfile, RMAN can use this information for recovery purposes.4. Starting RMANRMAN has a command line interface, or can be run from Enterprise Manager. For the purposes of this document, only the CLI will be covered. The command line interface has following syntax:rman target [rcvcat | cmdfile |msglog | append | trace ]Argument Quoted String DescriptionTARGET A connect string containing a userid and password for the database on which Recovery Manager is to operate.rman target system/manager@targetRCVCAT A connect string that contains a userid and password for the database that contains the recovery catalog.rman rcvcat rman/rman@rcvcatCMDFILE The name of a file that contains the input commands for RMAN. If this argument is specified, RMAN operates in batch mode; otherwise, RMAN operates in interactive line mode. MSGLOG The name of a file where RMAN records commands and output Results. If not specified, RMAN outputs to the screen. APPEND This parameters causes the msglog file to be opened in append mode. If this parameter is not specified and a file with the same name as the msglog file already exists, it is overwritten.TRACE A file name where RMAN will dump a trace information.(useful feature for RMAN jobs debugging)For the purposes of the following examples, assume that- the target database is called targdb and has the same TNS alias- targdba has been granted SYSDBA privileges- the recovery catalog database is called rcat and has the same TNS alias- the schema containing the recovery catalog is rman (same password)Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment variables. Much of the RMAN LIST output is date/time related. It is often necessary to have this information displayed as accurately as possible when performing time-based recovery. Example NLS settings:NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SSFor RMAN to connect to the recovery catalog and the target database, the recovery catalog database must be OPEN, while the target instance must be at least STARTED. If not, RMAN will give an error. To perform backups with the target database open, the target MUST be in archivelog mode.4.1. Connecting to RMAN without a recovery catalogSet ORACLE_SID to be the target database, and issue the following:% rman nocatalogRMAN> connect targetor if the target database uses a password file,RMAN> connect target targdba/@targdb4.2. Connecting to RMAN with a recovery catalog% rman rcvcat rman/rman@rcatRMAN> connect targetor if the target database uses a password file,% rman rcvcat rman/rman@rcat target targdba/@targdbNote: Recovery Manager automatically requests a connection to the target database as SYSDBA. In order to connect to the target database as SYSDBA, you must either:1. Be part of the operating system DBA group with respect to the target database. This means that you have the ability to CONNECT INTERNAL to the target database without a password.-or -2. Have a password file setup. This requires the use of the orapwd command and the initialization parameter remote_login_passwordfile.Note: The connect string (for example, @targdb) should be a valid TNS alias, as specified in the local to the rman utility tnsnames.ora file .4.3. Using RMANOnce connected to the target database, you can specify RMAN commands either interactively or by using stored scripts. An example of using RMAN interactively would be:RMAN> resync catalog;An example of calling a stored script would be:RMAN> execute script alloc_1_disk;To create/replace a stored script:RMAN> replace script alloc_1_disk {2> allocate channel d1 type disk;3> }5. Register the target databaseDatabase status:Recovery catalog: openTarget: mounted or openThe target database must be registered with the recovery catalog before using RMAN against the database for the first time:RMAN> register database;6. Adding existing backups to the recovery catalogDatabase status:Recovery catalog: openTarget: mounted or openIf user-created backups existed under version 8.x prior to registering with the target database, these can be added to the recovery catalog as follows:RMAN> catalog datafilecopy /supp/ . /systargdb.dbf;To view this file in the catalog, use the following command:RMAN> list copy of database;7. Backing up in noarchivelog modeDatabase status:Recovery catalog: openTarget: database mountedRecovery catalog database is OPEN, target database is started (optionally mounted). Because the target database is not in archivelog mode, it must not be open when performing backups of datafiles. This would be equivalent of making filesystem copies of datafiles without putting tablespaces intohot backup mode. If the database is open and not in archivelog mode, RMAN will generate an error when you attempt to perform a datafile backup7.1. Example of how to back up a complete databaseRMAN> run {2> # backup the complete database to disk3> allocate channel dev1 type disk;4> backup5> full6> tag full_db_sunday_night7> format /oracle/backups/db_t%t_s%s_p%p8> (database);9> release channel dev1;10> }Line#2: Comment line (anything after the # is a comment)3&9: See section 15 Channels5: Full backup (default if full or incremental not specified)6: Meaningful string ( list backupset of database;7.2. Example of how to back up a tablespaceRMAN> run {2> allocate channel dev1 type disk;3> backup4> tag tbs_users_read_only5> format /oracle/backups/tbs_users_t%t_s%s6> (tablespace users);7> release channel dev1;10> }Line#6: Specifying only the USERS tablespace for backupTo view this tablespace backup in the catalog, use the following command:RMAN> list backupset of tablespace users;If for example the USERS tablespace is going to be put READ ONLY after being backed up, subsequent full database backups would not need to backup this tablespace. To cater for this, specify the skip readonly option in subsequent backups.Note that although this is a tablespace backup, the target database does NOT have to be open, only mounted. This is because tablespace information is stored in the controlfile in o8.7.3. Example of how to backup individual datafilesRMAN> run {2> allocate channel dev1 type SBT_TAPE;3> backup4> format %d_%u5> (datafile /oracle/dbs/sysbigdb.dbf);6> release channel dev1;7> }Line#2: Allocates a tape drive using the media manager layer (MML)Note that no tag was specified and is therefore null.To view this tablespace backup in the catalog, use the following command:RMAN> list backupset of datafile 1;7.4. Copying datafilesRMAN> run {2> allocate channel dev1 type SBT_TAPE;3> copy datafile /oracle/dbs/temp.dbf to /oracle/backups/temp.dbf;4> release channel dev1;5> }To view this file copy in the catalog, use the following command:RMAN> list copy of datafile /oracle/dbs/temp.dbf;Copying a datafile is different to backing up a datafile. A datafile copy is an image copy of the file. A backup of the file creates a backupset.7.5. Backing up the controlfileRMAN> run {2> allocate channel dev1 type SBT_TAPE;3> backup4> format cf_t%t_s%s_p%p5> tag cf_monday_night6> (current controlfile);7> release channel dev1;8> }Note that a database backup will automatically back up the controlfile.8. Backing up in archivelog modeDatabase status:Recovery catalog: openTarget: instance started, database mounted or openThe commands are identical to those in section 7 except that the target database is in archivelog mode.8.1. Backing up archived logsThe following script backs up all archive logs:RMAN> run {2> allocate channel dev1 type disk;3> backup4> format /oracle/backups/log_t%t_s%s_p%p5> (archivelog all);6> release channel dev1;7> }The following script backs up archive logs from sequence# 90 to 100:RMAN> run {2> allocate channel dev1 type disk;3> backup4> format /oracle/backups/log_t%t_s%s_p%p5> (archivelog from logseq=90 until logseq=100 thread 1);6> release channel dev1;7> }The following script backs up all archive logs generated in the past 24 hours. Furthermore it actually deletes the logs after backing them up. If the backup fails, logs will NOT be deleted:RMAN> run {2> allocate channel dev1 type disk;3> backup4> format /oracle/backups/log_t%t_s%s_p%p5> (archivelog from time sysdate-1 all delete input);6> release channel dev1;7> }To view the archive logs in the catalog, use the following command:RMAN> list backupset of archivelog all;Note that RMAN will backup specified logs if it finds them. If it cant find a log, it will report rman-6089 error: archived log %s not found or out of sync with catalog, and it will abort a backup session.8.2. Backing up the online logsOnline logs CANNOT be backed up using RMAN; they must be archived first. To do this, you can issue SQL commands from RMAN e.g.RMAN> run {2> allocate channel dev1 type disk;3> sql alter system archive log current;4> backup5> format /oracle/backups/log_t%t_s%s_p%p6> (archivelog from time sysdate-1 all delete input);7> release channel dev1;8> }The above script might be run after performing a full database open backup. It would ensure that all redo to recover the database to a consistent state would be backed up.Note, you cannot tag archive log backupsets.9. Incremental backupsA level N incremental backup backs up blocks that have changed since the most recent incremental backup at level N or less.9.1. Level 0 the basis of the incremental backup strategyRMAN> run {2> allocate channel dev1 type disk;3> backup4> incremental level 05> filesperset 46> format /oracle/backups/sunday_level0_%t7> (database);8> release channel dev1;9> }Line#4: Level 0 backup backups of level > 0 can be applied to this5: Specifies maximum files in the backupsetA list of the database backupsets will show the above backup. The typecolumn is marked Incremental; the LV column shows 0.9.2. Example backup strategy using incremental backupsA typical incremental backup cycle would be as follows:- Sun night level 0 backup performed- Mon night level 2 backup performed- Tue night level 2 backup performed- Wed night level 2 backup performed- Thu night level 1 backup performed- Fri night level 2 backup performed- Sat night level 2 backup performedIf the database suffered a failure on Sat morning and this resulted in a restore operation, RMAN could recover to the point of failure by restoring the backups from Sunday, Thursday, and Friday. This isbecause Thursdays level 1 backup contains all changes since Sunday, and Fridays level 2 backup contains all changes since Thursday. Whether the database could be completely recovered would depend on whether archive logging is enabled.10. Cumulative incremental backupsA cumulative incremental backup backs up all blocks that have changed since the the most recent incremental backup at level N-1 or less (contrast with non-cumulative incremental backups that backup blocks that have changed since the the most recent incremental backup at level N or less). This means that more work is done in performing the backup (duplication of backup effort), but time may be saved when restoring (potentially fewer backupsets to restore).RMAN> run {2> allocate channel dev01 type disk;3> backup incremental level 1 cumulative database;4> release channel dev01;5> }11. Checking backup progressTo check the backup progress run the following sql against the target database:select sid, serial#, contextround(sofar/totalwork*100,2) % Complete,substr(to_char(sysdate,yymmdd hh24: mi:ss),1,15) Time Nowfrom v$session_longopswhere compnam = dbms_backup_restore; for 8.0where substr(opname,1,4) = RMAN; for 8.1This will produce an output such as:SID SERIAL# CONTEXT % Complete Time Now- - - 12 56 980408 14:21:0712. RecoveryAs with backup, recovery is probably best explained with a few examples12.1. Database open, datafile deletedDatafile has been deleted from a running database. There are two methods of open database recovery: restore the datafile and recover either the datafile, or the tablespace. The next two examples show both methods:(a) Datafile recoveryRMAN> run {2> allocate channel dev1 type di