Backuop & Recovery Scenarios

Backup modes: 1. What strategies are available for backing-up an Oracle database? The following methods are valid for backing-up an Oracle database:

• • • •

Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details. Cold or Off-line Backups - Shut the database down and backup up ALL data, log, and control files. Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files. RMAN Backups - While the database is off-line or on-line, use the "rman" utility to backup the database.

It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry. Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files. 2. What is the difference between on-line and off-line backups? A hot backup is a backup performed while the database is on-line and available for read/write. Except for Oracle exports, one can only do on-line backups when running in ARCHIVELOG mode. A cold backup is a backup performed while the database is off-line and unavailable to its users.\ 3. Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-intime (before the disaster occurred), or roll-forward until the last transaction recorded in the log files. sql> connect SYS as SYSDBA sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE 3. How does one do off-line database backups? Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database. Do the following queries to get a list of all files that needs to be backed up: select name from sys.v_$datafile; select member from sys.v_$logfile; select name from sys.v_$controlfile; 4. How does one do on-line database backups? Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example. ALTER TABLESPACE xyz BEGIN BACKUP; ! cp xyfFile1 /backupDir/ ALTER TABLESPACE xyz END BACKUP; It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example: ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf'; NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes. 5. How does one backup a database using RMAN? The biggest advantage of RMAN is that it will only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example. rman target sys/*** nocatalog run { allocate channel t1 type disk; backup format '/app/oracle/db_backup/%d_t%t_s%s_p%p' ( database ); release channel t1; } Example RMAN restore: rman target sys/*** nocatalog run { allocate channel t1 type disk; # set until time 'Aug 07 2000 :51'; restore tablespace users; recover tablespace users; release channel t1; } The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup an RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.

Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

ARCHIVELOG mode process: 1) How does one put a database into ARCHIVELOG mode? The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database: SQLPLUS> connect sys as sysdba SQLPLUS> startup mount exclusive; SQLPLUS> alter database archivelog; SQLPLUS> archive log start; SQLPLUS> alter database open; NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up. NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups. NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=... NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories 2)I've lost my REDOLOG files, how can I get my DB back? The following INIT.ORA parameter may be required if your current redologs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it. _allow_resetlogs_corruption = true. 3) I've lost some Rollback Segments, how can I get my DB back? Re -start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch. Caution is advised when enabling this parameter as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it. 4) How does one create an RMAN recovery catalog? Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example: sqlplus sys SQL> create user rman identified by rman; SQL> alter user rman default tablespace tools temporary tablespace temp; SQL> alter user rman quota unlimited on tools; SQL> grant connect, resource, recovery_catalog_owner to rman; SQL> exit; Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman catalog rman/rman RMAN> create catalog tablespace tools; RMAN> exit; You can now continue by registering your databases in the catalog. Look at this example: rman catalog rman/rman target backdba/backdba RMAN> register database. 5) What are the common RMAN errors (with solutions)? Some of the common RMAN errors are: RMAN-20242: Specification does not match any archivelog in the recovery catalog. Add to RMAN script: sql 'alter system archive log current'; RMAN-06089: archived log xyz not found or out of sync with catalog Execute from RMAN: change archivelog all validate; 6) My database is down and I cannot restore. What now? Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an off-line database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!! 7) My database was terminated while in BACKUP MODE, do I need to recover? If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle7.2, you can simply take the individual datafiles out of backup mode and restart the database. ALTER DATABASE DATAFILE '/path/filename' END BACKUP; One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the script section of this FAQ. From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode: ALTER DATABASE END BACKUP; The above command needs to be issued when the database is mounted.

WHAT INFORMATION SHOULD BE BACKED UP? A database contains a wide variety of types of data. When developing backup strategy, DBAs must decide what information they want to copy. The basic backup types include:

• • • • • • • •

Online Database Backup Offline Database Backup Whole Database Tablespace Datafile Control File Archived Redo Log Configuration Files

In deciding what to back up, the basic principle is to prioritize data depending on its importance and the degree to which it changes. Archive logs do not change, for example, but they are crucial for recovering the database, so multiple copies should be maintained, if possible. Expense account tables, however, are constantly updated by users. Therefore, this tablespace should be backed up frequently to prevent having to apply as much redo data during recovery. Backups can be combined in a variety of ways. For example, a DBA can decide to take weekly whole database backups, to ensure a relatively current copy of original database information, but take daily backups of the most accessed tablespaces. The DBA can also multiplex the all important control file and archived redo log as an additional safeguard. Online Database Backup: An online backup or also known as an open backup, is a backup in which all read-write datafiles and control files have not been checkpointed with respect to the same SCN. For example, one read-write datafile header may contain an SCN of 100 while other read-write datafile headers contain an SCN of 95 or 90. Oracle cannot open the database until all of these header SCNs are consistent, that is, until all changes recorded in the online redo logs have been saved to the datafiles on disk. If the database must be up and running 24 hours a day, 7 days a week, then you have no choice but to perform online backups of a whole database which is in ARCHIVELOG mode. Offline Database Backup: In this backup, all datafiles and control files are consistent to the same point in time - consistent with respect to the same SCN, for example. The only tablespaces in a consistent backup that are allowed to have older SCNs are read-only and offline-normal tablespaces, which are consistent with the other datafiles in the backup. This type of backup allows the user to open the set of files created by the backup without applying redo logs, since the data is already consistent. The only way to perform this type of backup is to shut down the database cleanly and make the backup while the database is closed. A consistent whole database backup is the only valid backup option for databases running in NOARCHIVELOG mode. Whole Database Backup: The most common type of backup, a whole database backup contains the control file along with all database files that belong to a database. If operating in ARCHIVELOG mode, the DBA also has the option of backing up different parts of the database over a period of time, thereby constructing a whole database backup piece by piece. Tablespace Backups: A tablespace backup is a subset of the database. Tablespace backups are only valid if the database is operating in ARCHIVELOG mode. The only time a tablespace backup is valid for a database running in NOARCHIVELOG mode is when that tablespace is read-only or offline-normal. Datafile Backups: A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups and are only valid if the database is run in ARCHIVELOG mode. The only time a datafile backup is valid for a database running in NOARCHIVELOG mode is if that datafile is the only file in a tablespace. For example, the backup is a tablespace backup, but the tablespace only contains one file and is read-only or offline-normal. Control File Backups: A control file backup is a backup of a database's control file. If a database is open, the user can create a valid backup by issuing the following SQL statement: ALTER DATABASE BACKUP CONTROLFILE to 'location'; or use Recovery Manager (RMAN). Archived Redo Log Backups: Archived redo logs are the key to successful media recovery. Depending on the disk space available and the number of transactions executed on the database, you want to keep as many days of archive logs on disk and you want to back them up regularly to ensure a more complete recovery. Configuration Files: Configuration files may consist of spfile or init.ora, password file, tnsnames.ora, and sqlnet.ora. Since these files do not change often, then they require a less frequent backup schedule. If you lost a configuration file it can be easily recreated manually. When restore time is a premium, it will be faster to restore a backup of the configuration file then manually creating a file with a specific format. Making Recovery Manager Backups: Recovery Manager (RMAN) is a powerful and versatile program that allows users to make an RMAN backup or image copy of their data. When the user specifies files or archived logs using the RMAN BACKUP command, By default RMAN creates a backup set as output. A backup set is a file or files in a proprietary-specific format that requires the use of the RMAN RESTORE command for recovery operations. In contrast, when the BACKUP AS COPY command is used to create an image copy of a file, it is in an instance-usable format - the user does not need to invoke Recovery Manager to restore or recover it. When a RMAN command is issued, such as backup or restore, Recovery Manager establishes a connection to an Oracle server process. The server process then back up the specified datafile, control file, or archived log from the target database. The recovery catalog is a central repository containing a variety of information useful for backup and recovery. RMAN automatically establishes the names and locations of all the files needed to back up. Recovery Manager also supports incremental backups backups of only those blocks that have changed since a previous backup. In traditional backup methods, all the datablocks ever used in a datafile must be backed up.

Automatic Disk-Based Backup and Recovery The components that creates different backup and recovery-related files have no knowledge of each other or of the size of the file systems where they store their data. With Automatic Disk-Based Backup and Recovery, you can create a flash recovery area, which automates management of backup-related files. Choose a location on disk and an upper bound for storage space, and set a retention policy that governs how long backup files are needed for recovery, and the database manages the storage used for backups, archived redo logs, and other recovery-related files for your database within that space. Files no longer needed are eligible for deletion when RMAN needs to reclaim space for new files. If you do not use a flash recovery area, you must manually manage disk space for your backup-related files and balance the use of space among the different types of files. Oracle Corporation recommends that you enable a flash recovery area to simplify your backup management.