IT professionals should strive to check and answer the following :
• If access to a database server is lost, what would the cost to a company be (monetary and reputation) ?
• What is the maximum duration that a loss of access can be tolerated ?
• If server X crashes, how long would it take to fully recover ? (Business continuity planning, working out the time to recover by carrying out a recovery procedure)
• Are backups being taken and checked regularly ?
• Verifying that the backups are working and are periodically tested
• If the RAC1 server is offline, how quickly can it be put back online ?
• What to do in a situation where RAC2 goes offline while RAC1 is offline ?
• How soon can a RACx server be put back online ?
• It is strongly advised to keep a step-by-step recovery guide ready, taking precautions is considered vital.
(For example: Is there backup media ready and waiting to be removed immediately if there was a disaster such as a fire at a workplace ?)
• Prepare documents that are easy to understand and could guide anyone to restore backups, in case of an emergency where you are unavailable and people couldn’t contact you.
In this article we try to explain the subject of Oracle backups and in a separate article we will discuss restoring/recovery.
RMAN (Recovery Manager)
This a built-in tool that comes with Oracle. It can be used to take backups of a database while it is open and can perform incremental backups.
In addition to being able to perform block media recoveries, RMAN has many other features.
Terminology
Target Database: The db to be backed up and restored.
RMAN Repository: The metadata collection about dbs used for backup, maintenance and recovery. This information is stored in control file records.
Recovery Catalog schema: The schema that holds the backup metadata, held in the recovery catalog database.
RMAN Client: A command line program used by client computers to connect to a database server, using specific connections and requires authentication, like SQL*Plus.
Backup Piece: The physical files that together form RMAN backup sets. Each piece file is in a proprietary binary RMAN format.
Backup Set: A number of files, including control files, datafiles, archived redo log files. There are one or more binary files per backup set.
Image Copy: An identical copy of single datafiles, archived redo log files or control files. They can be used to perform recovery without needing any modification.
|
1 |
configure device type disk backup type to copy ; |
RMAN backup information can be stored in 2 locations:
1. In a controlfile
2. In a recovery catalog
If there are 1 or 2 Oracle servers in your company then you can use a controlfile. In scenarios where you have numerous databases, it would be better to store all their backup information in separate locations from a recovery standpoint, rather than in a single recovery catalog.
A recovery catalog itself is an RMAN set of tables and views where repository data about one or more databases are kept.
Additionally the catalog can store more metadata history information than a control file. RMAN scripts can also be kept in a recovery catalog that can be used to automate RMAN tasks.
Perhaps the best feature of recovery catalogs are that it is possible to use them even if control files are damaged (using CONFIGURE CONTROLFILE AUTOBACKUP ON), however the recovery process takes longer using a recovery catalog instead of a control file. This becomes more apparent in the recovery/restore scenario articles.
NOTE: After every backup of target databases, a copy should be made of recovery catalog databases as they would contain the latest data.
Some Recovery Catalog recommendations:
The RC server should be in archive mode and the backup copies in a seperate location.
A retention policy of higher than 1.
Activating automatic backup of the control file.
Not changing CONTROL_FILE_RECORD_KEEP_TIME values so that they are higher than the default values.
To take online or hot backups using RMAN, the database needs to be in archive mode.
Archivelog Mode: Redo logs are stored as archives in order to return the database to a certain time. This is similar to a Windows restore point.
For example: If a full backup was made on a Sunday and the system failed on a Wednesday. The database is first restored to its state as it was on Sunday, using a full backup, and archivelogs are then used to restore the database to its state between Sunday and Wednesday.
Please refer to the article on placing a database into ArchiveLog Mode.
NoArchiveLog Mode: Redo logs can’t be archived in this mode.
You can restore back to the point where a full backup was taken, which was Sunday in the example above, however it isn’t possible to access the data from then on until system failure.
Logical Backups: Backups made using the exp/imp and exdp/imdp tools. (Questions about logical backups appear in Oracle Certified Professional exams (version 9i)).
Physical Backups: A backup of the physical files, including data files, archive logs and control files.
Having the target database and recovery catalog in separate instances/servers is strongly recommended.
Levels of backup
• Backup of all database files (data file, control file, archivelog)
• Backups made at the tablespace level (all of the datafiles in the tablespace)
• A backup of the tablespace’s datafile can be made if the database is in archivelog mode.
The configuration of the machines running the examples in this articles are as follows:
| Machine A | Machine B |
| Oracle 10G 10.2.0.5 | Oracle 10G |
| RHEL 5.5 | Oracle Solaris 10 |
| i386 CPU, 1284MB RAM | |
| 5 disk | |
| Disk 1: OS | |
| Disks 2,3,4,5: Included in the data and recovery asm (automatic storage management) group |
Creating a Recovery Catalog
1. Creating a tablespace
2. Creating a user and granting privileges
|
1 2 3 4 5 6 7 8 |
CREATE TABLESPACE rec_cat DATAFILE '+DATA/orcl/datafile/rec_cat01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1024M LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; |
|
1 2 3 4 |
CREATE USER cat IDENTIFIED BY cat DEFAULT TABLESPACE rec_cat TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON rec_cat; |
|
1 2 3 |
GRANT CONNECT,RESOURCE TO cat; GRANT "RECOVERY_CATALOG_OWNER" TO CAT; ALTER USER CAT DEFAULT ROLE "RECOVERY_CATALOG_OWNER"; |
|
1 2 |
rman catalog cat/cat create catalog; |
Procedures to run on the server where the Recovery Catalog is to be stored. (Backup information will be stored on a catalog server)
As this is a test environment the recovery catalog server and the server where backup information is stored are the same.
However in a production environment it is recommended having both sets of data on separate servers.
|
1 2 |
$rman target / RMAN> connect catalog cat/cat@orcl<br< a=""> />RMAN> register database; |
The previous syntax shows that RMAN backups will now be stored in the recovery catalog.
Connecting with RMAN:
|
1 2 3 4 5 6 7 |
$rman connect target / alternatively $rman target / Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 16 14:15:13 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1257644776) |
|
1 2 3 4 |
$rman target sys/sys@orcl Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 16 14:16:59 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1257644776) |
It is now possible to connect to the target database.
To connect to the remote machine the rman client needs to be compatible with the server. If they are not compatible:
|
1 |
RMAN-06429: TARGET database is not compatible with this version of RMAN « error message |
Connecting to the target database and catalog:
|
1 2 3 4 5 |
rman TARGET sys/sys@orcl CATALOG cat/cat@orcl « recovery database is the same as the target database Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 16 14:14:34 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1257644776) connected to recovery catalog database |
Connecting to the target database without a recovery catalog:
|
1 2 3 4 5 |
$ rman TARGET sys/sys@orcl NOCATALOG Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 16 14:13:18 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1257644776) using target database control file instead of recovery catalog |
Running the parameter file (RMAN commands can be run after entering them into a parameter file)
|
1 2 |
$ rman @/home/oracle/Batches/rman.txt $ rman sys/sys@orcl catalog cat/cat@orcl CMDFILE RmanParameter.txt log Rman.log |
Connecting to the target db, creating a log and trace
|
1 |
$ rman TARGET sys/password NOCATALOG debug trace=rman.trc log=rman.log |
Connecting to the target db, creating a log
|
1 |
$ rman TARGET sys/password log=rman.log APPEND |
Backup information can be queried from the tables and views in the CAT schema.
Example Scenario: The Oracle database is not connected to the recovery catalog. If backups taken using RMAN were later saved on the recovery catalog, they might later need to be manually synchronized.
The BACKUP and COPY commands to be used on the target database get automatically synchronized with the recovery catalog. A situations where changes made to the physical structure and there a high number of log switches might also need to be manually synchronized.
|
1 2 3 4 5 |
[oracle@asm ~]$ rman TARGET sys/sys@orcl CATALOG cat/cat@orcl Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 16 14:21:22 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORCL (DBID=1257644776) connected to recovery catalog database |
|
1 2 3 |
RMAN> RESYNC CATALOG; starting full resync of recovery catalog full resync complete |
Scripting
RMAN scripts can be copied to the recovery catalog or a text file.
If using a Recovery Catalog, you could run a RC script directly.
If you’re not using a recovery catalog then use the RMAN by entering this command: @/path/rman.txt
|
1 2 3 |
CREATE SCRIPT COMPRESSED_FULL_BACKUP { ALLOCATE CHANNEL C1 TYPE DISK ; BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/Rman/%u' ;} |
|
1 2 3 |
CREATE SCRIPT COMPRESSED_FULL_BACKUP_ARC { backup as compressed backupset database format '/u01/Rman/dbf_%d_%t_%s.rman' Tag='DBF_Manual_%U' plus archivelog format '/u01/Rman/arc_%d_%t_%s.rman' tag='Arc_Manual_%U' ;} |
|
1 2 |
CREATE SCRIPT TBS_USERS { backup as compressed backupset tablespace users format '/u01/Rman/users-tbs.rman' Tag='users-tbs_Manuel_16062011' ;} |
Running the scripts
|
1 2 3 4 5 6 7 8 9 10 11 |
RUN<strong> </strong>{ ALLOCATE CHANNEL C1 TYPE SBT ; ALLOCATE CHANNEL C2 TYPE SBT ; BACKUP FORMAT 'FULL BACKUP_u%' FILEPERSET 15 DATABASE; RELEASE C1; RELEASE C2; } |
Checking the scripts
|
1 2 3 4 5 6 7 |
[oracle@asm ~]$ rman checksyntax @/home/oracle/Scripts/COMPRESSED_BACKUP Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jun 17 14:53:04 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. backup as compressed backupset database format '/u01/Rman/dbf_%d_%t_%s.rman' Tag='DBF_Manual_16062011' plus archivelog format '/u01/Rman/arc_%d_%t_%s.rman' tag='Arc_Manual_16062011' ; The cmdfile has no syntax errors Recovery Manager complete. |
Saving the script to a text file
|
1 2 |
print script COMPRESSED_FULL_BACKUP_ARC to file 'backupscript.txt' ; script COMPRESSED_FULL_BACKUP_ARC written to file backupscript.txt |
The file should be in the same directory where RMAN was run.
By creating global scripts it will be possible to use them in all databases that were saved to the recovery catalog.
|
1 2 3 4 5 |
create global script glb_full_backup { backup database plus archivelog; delete obsolete; } |
To view the contents of the global scripts
|
1 |
print global script global_full_backup ; |
Listing the scripts
|
1 2 3 |
LIST SCRIPT NAMES; LIST GLOBAL SCRIPT NAMES; LIST ALL SCRIPT NAMES; |
|
1 2 |
SELECT script_name FROM cat.rc_stored_script; --recovery catalog un sahibi SELECT script_name, line, text FROM cat.rc_stored_script_line |
Deleting the scripts
|
1 2 |
delete script 'script_ismi' ; delete global script 'script_ismi' ; |
Running scripts on RMAN startup
|
1 |
rman TARGET sys/sys@orcl CATALOG cat/cat@orcl SCRIPT '/home/oracle/Scripts/backup.sh' |
Configuring the backup channel and tool
|
1 2 |
configure default device type to sbt ; --tape configure default device type to disk ; --disk |
The device selected by default can be changed for a pending backup
|
1 2 3 4 |
run { backup database ; } |
Tagging the backups
|
1 2 |
backup tag 'daily_full_backup' database ; backup format='DBF_%d_%t_%s_%p' archivelog like '%Arc_dest%' ; |
Copying the RMAN backups
|
1 2 3 4 5 |
run { BACKUP DEVICE TYPE DISK COPIES 2 DATAFILE 5 FORMAT '/u01/y1/dbf5_%U', '/u01/y2/dbf5_%U' ; } |
The syntax below shows how backups can be taken from an operating system to a separate location, the catalog command could be included in RMAN when required. There is an example scenario where this is shown.
Copying a backup to a different disk or tape
|
1 2 3 |
backup device type disk as backupset database plus archivelog format '/u01/y2/yy_%U' ; backup device type sbt as backupset database plus archivelog « from disk to tape backup device type sbt as backupset ALL |
Backup commands
|
1 2 3 4 5 6 7 |
backup database ; backup tablespace tablespace_name ; backup datafile '+DATA/orcl/datafile/examples' ; « Retrieve the datafile information from dba_data_files backup as copy database ; backup as copy tablespace examples ; backup as copy datafile '+DATA/orcl/datafile/examples' ; backup tablespace examples TAG = 'Daily_Backup' ; |
Incremental Backup
Differential Incremental: The differences or changes since the last backup.
Incremental level 0 = Full Backup (a backup of every used block, this is the base of the incremental backup)
Listed below is an example of a weekly plan backup plan:
Sunday level 0 « Take a full backup
Monday incremental level 2 « No previous level 2 backup, so a backup of the difference since level 0
Tuesday incremental level 2 « A previous level 2 backup, so the differences since the level 2 backup
Wednesday incremental level 2 « A previous level 2 backup, so the differences since the level 2 backup
Thursday incremental level 1 « No previous level 1 backup, if there was, a backup would have been made of the difference. Instead a backup is made from 0, this is a difference from a full backup from the middle of the week.
Checking to see if any backups were made at the current level or at a more base level, if backups were made at a more base level (lower number) then a backup is made of the difference.
|
1 2 |
sun mon tues wed thurs fri sat sun 0 3 3 2 |
A full backup is taken on Sunday
On Monday, the difference since Sunday is what gets taken as a backup.
On Tuesday, a backup is made of the difference in the database state since Monday.
On Wednesday, has a backup been made at the same level ? If not, a backup is made since the last closest level of backup, in this case a level 2 which was on Sunday.
Logically it would be quicker to take a daily incremental backup after the full backup but taking a backup in this way is suprisingly slow, at least until the “block_change_tracking” feature has been activate.
|
1 2 3 4 |
set linesize 121 col filename format a60 SELECT filename, status, bytes FROM v$block_change_tracking; |
|
1 2 |
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/oracle/flash_recovery_area/ORATR/bctf01.log' ; |
|
1 2 |
SELECT filename, status, bytes FROM v$block_change_tracking; |
|
1 |
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; |
|
1 2 |
SELECT filename, status, bytes FROM v$block_change_tracking; |
|
1 2 |
backup incremental level 0 database plus archivelog; backup incremental level 1 database plus archivelog; |
First backup example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--Sunday backup incremental level 0 database plus archivelog; --Monday backup incremental level 1 database ; --Tuesday backup incremental level 1 database ; --Wednesday backup incremental level 1 database ; --Thursday backup incremental level 1 database ; --Friday backup incremental level 1 database ; --Saturday backup incremental level 1 database ; --Sunday backup incremental level 0 database plus archivelog; |
Commands
The ALLOCATE CHANNEL and SWITCH parameters need to be placed in curly bracket. <>.
|
1 |
run backup datafile 1; |
|
1 |
backup as copy datafile 5 ; |
Deleting backups
If RMAN suggests deleting backups from the operating system, even though there is a solution for dealing with deleted backups it is recommended to not delete them.
|
1 2 |
delete archivelog all backed up 2 times to device type disk « Deletes archivelogs that have been backup up at least twice BACKUP ARCHIVELOG ALL DELETE INPUT; « All archivelogs that have been backed up get deleted. |
Deleting backups that aren’t required (in accordance with the retention policy, more details ahead).
|
1 |
delete obsolete ; |
Crosschecking whether Rman backups have been deleted by the operating system. Backups that can’t be found are marked as expired and erased using “delete expired”.
|
1 2 3 |
crosscheck archivelog all crosscheck backupset crosscheck backup |
Deleting backups that have been marked as expired.
|
1 2 |
delete expired ; delete noprompt expired ; « Deletes the backups that are marked expired without prompting |
Occasionally Rman scripts stops responding and without being able to control a script the archive logs start to get filled up. This could be followed by running out of diskspace and losing access. The only remaining option is to regain access at a local level.
To create space as quickly as possible, in this situation, the oldest of the archive logs should be moved to another location or in the worst case should be deleted, however this is not recommended as it could cause problems.
If the archive logs have been deleted by the operating system or moved to another disk, at this point it is possible to access the system by entering commands.
|
1 2 3 |
crosscheck archivelog all delete noprompt expired archivelog all alter system switch logfile; |
Report commands
|
1 |
report schema ; « Lists the data files in the target database |
report obsolete ; « Lists the backups marked as obsolete (unused or unnecessary) according to the database’s retention policy. These backups can be deleted using the “delete obsolete” command. If the flash recovery area is used as the backup path, as long as there is available space in the path obsolete backups would continue being stored there, otherwise they get deleted. If backups are stored in a different area then they would need to be deleted manually (Scripts generally get deleted by writing into them, examples such as these will be shown in the scenario section inshALLAH)
|
1 2 3 4 5 |
report need backup ; « Files that require backups get listed according to the retention policy setting. REPORT NEED BACKUP DEVICE TYPE sbt; REPORT NEED BACKUP DEVICE TYPE disk; REPORT NEED BACKUP TABLESPACE users DEVICE TYPE sbt; REPORT UNRECOVERABLE ; « Shows database files that need to be urgently backed up due to nologging operations such as Direct-Path INSERT (As they haven't created redo logs. (As they haven't created redo logs, files that need to be backed up are listed). |
Backup files that are unknown to Rman (not in its records) are used to inform Rman after the following has taken place:
• The process of restoring the control file
• Re-creating the control file
• Changes to the db_recovery_file_dest parameters
• Needing to restore backup files from a disk, separate to the one where the backups have been taken.
|
1 2 3 |
catalog datafilecopy '/u01/y1/accounts01.dbf' ; catalog backuppiece '/u01/Rman/ORCL_DB_1hmfeu60_49_1' ; catalog start with '/u02/Rman/' ; |
If there are inconsistencies between the physical backups on the disk and the backup records in Rman, they may get deleted. (There should be a backup of the backup before any deletion takes place. If there are any problems you could get support from Oracle and do a restore.)
|
1 |
delete force archivelog sequence 23 ; |
Listing the backups
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
LIST BACKUP; LIST BACKUP SUMMARY; LIST COPY; LIST ARCHIVELOG ALL; LIST BACKUP OF DATABASE; LIST INCARNATION; LIST BACKUP BY FILE; LIST COPY OF DATABASE ARCHIVELOG ALL; LIST COPY OF DATAFILE 1, 2, 3; LIST BACKUP OF DATAFILE 1 SUMMARY; LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1453; LIST CONTROLFILECOPY "/u02/backup/CNTRLFILE.CP"; LIST BACKUPSET OF DATAFILE 1; LIST SCRIPT NAMES; LIST GLOBAL SCRIPT NAMES; |
Checking the usability of the backups.
|
1 |
validate backupset 5; |
|
1 2 3 4 5 6 |
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of validate command at 06/22/2011 13:09:06 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20215: backup set not found RMAN-06159: error while looking up backup set |
|
1 |
validate backupset 879; |
|
1 2 3 4 5 6 |
using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile backupset channel ORA_DISK_1: reading from backup piece /u01/Rman/19mfe9nl channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/Rman/19mfe9nl tag=TAG20110621T100748 channel ORA_DISK_1: validation complete, elapsed time: 00:00:46 |
RMAN settings
|
1 |
show all; « Shows all available settings |
The “# default” at the end of a sentence indicates that the values haven’t been changed at that they are still at their default settings.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; « Just stores a single backup, a value of 4 would mean setting it to store 4 backups. CONFIGURE BACKUP OPTIMIZATION ON; « If the tablespaces are read-only & if there are backups of data that hasn't been changed, another backup is made. CONFIGURE DEFAULT DEVICE TYPE TO DISK; « The default backup device could be set as a disk or tape. CONFIGURE CONTROLFILE AUTOBACKUP ON; « Ensures that the control file and spfiles are backed up when the structure of the database changes (adding or deleting dbf or redolog) and backups are made CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; « Should be in %F format, it may be difficult to restore if changed. CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; CONFIGURE DEVICE TYPE DISK PARALLELISM 1; « Can be increased according to the number of processors in order to speed up the backup process. This then increases the number of files that are created. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; « Indicates the number of copies of the Datafile backups. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; « Indicates the number of copies of the Archivelog files. CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/Rman/%d_DB_%u_%s_%p'; « The backup device, path and format. CONFIGURE MAXSETSIZE TO UNLIMITED; --CONFIGURE MAXSETSIZE TO 10G; « To set restrictions such as the backup files not being over 10Gb or restrictions on the hardware or the operating system. CONFIGURE ENCRYPTION FOR DATABASE OFF; « Encrypting the backups is vital for security purposes. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; « The setting for the encryption method or algorithm. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; « If this is set to 2, backup Np 1. would be marked as obsolete and backup No. 2, i.e. the latest backup, would be set as the backup to be restored from. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/y1/snapcf_orcl.f' « The location where a snapshot of the control file is kept, used to synchronize with the catalog database. configure retention policy to recovery window of 7 days; « Retains all of the backups that have been taken for a period of 7 days, would not mark them as obsolete. Either one of the "windows" or "redundancy" keywords may be used but not both at the same tiöe Typing clear at the end of the statements would reset the setiting back to its defaults. CONFIGURE CONTROLFILE AUTOBACKUP clear ; configure backup optimization clear; |
Installing Oracle 10G...
12 March 2019