Backup related technical terms
Main category of doing database backup
- Whole database backups: a backup of every datafile in the database, plus the control file. Whole database backups are the most common type of backup.
- Partial database backups: backup of either tablespaces ,datafiles, control files or Archived Redo Log but not the whole database.
Backup Type
- Full backup : every time the backup has been doing it will create a whole database.
- Incremental backup : backup only the updated data, there must be an old backup file for comparison purpose.
Backup Mode
- Offline backup/consistent backup: doing backup while the database is offline. The files in the backup contain all the data taken from a same point in time. This type of backup does not require recovery after it is restored.
- Online backup/inconsistent backup/hot backup : doing backup while the database is online.
Backup Format
- Image Copies: this type of backup is copying physical file, this kind of format the original file size would be the same as the copy file size. Which would take space.
- Backup set: this format is the backup default and only used for Oracle database. The difference between Image Copied and Set Backup is Set Backup would do the backup only the block with data and we could configure the set level and the media type to keep the data on i.e. disk or tape
Recovery Manager (RMAN)RMAN is an Oracle utility which come with Oracle Database from installation process. The main functionality of this utility is backup, restoring and recovering database.
You can also use operating system commands for backups and SQL*Plus for recovery. This method, also called user-managed backup and recovery, is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.
RMAN related technical terms
- Target Database: the database server we want to do the backup.
- Channel Process: the channel which RMAN would access the target database.
- RMAN Repository: Path that keep RMAN’s information, which will hold backup information of each instance. The default path is configured in control file of each instance. We could create a new database for the use of this purpose. With this new database we could keep the backup’ information of every instances we would do the backup
- Flash Recovery Area: The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area. RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape
- Recovery catalog: The database the store recovery catalog schema, which contain metadata that RMAN use for backup and recovery
RMAN Configuration Parameters
- DB_RECOVERY_FILE_DEST_SIZE: set how much flash_recovery size could go.
- DB_RECOVERY_FILE_DEST: set the path that keep backup set.
RMAN Related View List
- V$RECOVERY_FILE_DEST: This view shows the position of flash recovery.
- V$FLASH_RECOVERY_AREA_USAGE : this view shows how much the space had been taken for each file.
Physical File which RMAN could do the backup
- Database file = { data file, control file, spfile }
- Archived log file
Note: password file, network configure file (tnsname.ora, listener.ora, sqlnet.ora) we need to do manual backup.
Basic RMAN commandStart & Exit RMAN
# RMAN target /[@] [nocatalog | catalogname]
# RMAN
RMAN> EXITrun script RMAN from file
# RMAN target / @file1
RMAN> @file1
command to validate RMAN syntax
# RMAN CHECKSYNTAX
# RMAN CHECKSYNTAX @file1command to display parameters
RMAN> show ;
RMAN> SHOW ALL;
RMAN> CONFIGURE CLEAR;Media management command
Specify the media
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO ;Specify to compress the backup
RMAN> CONFIGURE DEVICE TYPE BACKUP TYPE TO ;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;
some of media managers need to configure through parameter.
RMAN> CONFIGURE CHANNEL DEVICE TYPE SBT PARMS=’ENV=mml_env_settings’;backup data file command
RMAN> BACKUP DATABASE;backup tablespaces
RMAN> BACKUP TABLESPACE system, users, tools;
RMAN> BACKUP DEVICE TYPE sbt MAXSIZE=10M TABLESPACE user, tool;Backup data file
RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY ‘/tmp/system.dbf’;
RMAN> BACKUP DEVICE TYPE sbt TABLESPACE user INCLUDE CURRENT CONTROLFILE;
RMAN> BACKUP AS BACKUPSET DATAFILE
’ORACLE_HOME/oradata/trgt/users01.dbf’,
’ORACLE_HOME/oradata/trgt/tools01.dbf’;to be able to set the incremental backup, which is level 1 we need to do level 0 first. In other word, we need to do the first backup with level 0. There is one caution to be made, that is when we do the incremental backup, the backup file level 0 และ 1 need to be together so we could completely do the restoration and recovery.
RMAN> BACKUP INCREMENTAL LEVEL [0 | 1] CUMULATIVE DATABASE;backup control file
Set the configuration so that it could do the auto backup control file and parameter file when there were some changes and when there is some disasters RMAN would recover for that particular instance.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ;change the autobackup default name
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE to ‘?/oradata/cf_%F’;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE clear;to override autobackup value for any particular session (use run block on RMAN prompt)
RMAN> SET CONFIGURE AUTOBACKUP FORMAT
Backup current control file
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO ’/backup/curr_cf.copy’;Backup control file
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT ‘/tmp/control01.ctl’;
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY ‘/tmp/control01.ctl’;
backup parameter file
RMAN> BACKUP SPFILE;
RMAN> BACKUP DEVICE TYPE sbt SPFILE;backup archived log file
RMAN> BACKUP ARCHIVELOG …;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN ’SYSDATE-31’ AND ’SYSDATE-7’;restore command
RMAN> RESTORE DATABASE;
RMAN> RESTORE TABLESPACE users;
RMAN> RESTORE DATAFILE <ชื่อ datafile>;
RMAN> RESTORE DATAFILE 7;
recovery command
RMAN> RECOVER DATABASE;
RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER DATAFILE ;
RMAN> RECOVER DATAFILE 7;backup sample
Backup Database including Archivelog file plus delete archive log file from backup
# RMAN TARGET backup_admin/backup_admin NOCATALOG
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;Backup Database including Control file plus delete un-updated backup.
RMAN> backup incremental level 0 cumulative device type disk tag '%TAG' database include current controlfile;
RMAN> allocate channel for maintenance type disk;
RMAN> delete noprompt obsolete device type disk;
RMAN> release channel;
restore & recovery sample
Recovering whole database
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;Recovering tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
Recovering Datafiles 7
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';Backup Control File to Trace
To do trace backup control file for recovery when control file is corrupted
for OEM go to “Controlfiles” menu then “Backup To Trace”;
or you could use command line as below.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; The result would be kept as configured parameter USER_DUMP_DEST
the default name is _ora_pid.trc
and default path is
/admin//udump
the trace file would contain the sql command that could recreate the control file .
