Archive for the ‘User Managed Backup Recovery’ Category

Backup Concepts

Database Backup

 

A whole database backup includes all data files and at least one control file.

Full backups make a copy of every data block within the files being backed up that contains data.

Partial database backups may include zero or more tablespaces, zero or more data files, and may or may not include a control file.

 

Incremental backups make a copy of all data blocks that have changed since some previous backup. Oracle Database 10g supports two levels of incremental backup (0-1). A level 0 or baseline backup is equivalent to a full backup and contains all data blocks. A level 1 incremental backs up all database blocks changed since the level 0 backup. To restore using incremental backups, the baseline backup must first be restored, and then the incremental.

Offline backups or Cold backups(also known as consistent backups) are taken while the database is not open. They are consistent because at the time of the backup, the SCN data file headers matches the SCN in the control files.

Online backups (also known as hot or inconsistent backups) are taken while the database is open. The backups are inconsistent because with the database open there is no guarantee that the data files are synchronized with the control files. Inconsistent backups require recovery in order to be used. For online backup, ARCHIVELOG mode should be enabled.

 

Modes of Backups

i. ARCHIVELOG

If the database is in noarchive log mode, online redo logs can be overwritten without making sure they are saved. This implies that a database cannot be recovered fully (it can be rolled forward to the last transaction) even if backups were made.

ii. NOARCHIVELOG

If the database is in log archive mode, the database makes sure that online redo logs are not overwritten before they have been archived. Database can be recovered to the last

Types of Backups

i. Physical Backup

Physical backups are backups of the physical files used in storing and recovering your database, such as datafiles, control files, and archived redo logs.

ii. Logical Backup

Logical backups contain logical data (tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

 

Backup Types based on Physical Backup

For performing backup and recovery based on physical backups, you have two solutions available:

  • Recovery Manager (RMAN), a tool (with command-line client and Enterprise Manager GUI interfaces) that integrates with sessions running on the Oracle server to perform a range of backup and recovery activities, as well as maintaining a repository of historical data about your backups
  • The traditional user-managed backup and recovery, where you directly manage the files that make up your database with a mixture of host operating system commands and SQL*Plus backup and recovery-related capabilities

Recovery Manager isthe preferred solution for database backup and recovery. It can perform the same types of backup and recovery available through user-managed methods more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.

 

Image Copies and Backup Sets

 

Image copies are duplicates of data or archived log files (similar to simply copying the files using OS commands).

Backup sets are copies of one or more data or archived log files. With backup sets, empty data locks are not stored, thereby causing backup sets to use less space on disk or tape. Backup sets an be compressed to further reduce the space requirements of the backup.

Image copies must be backed up to disk whereas Backup sets can be sent to disk or directly to tape.

Advertisements

User Managed Backup

User-managed backups can be either logical or physical. You can use the Export utility to make backups of logical objects such as tables, views, and stored procedures, and use the Import utility to restore these objects.

  • Files are backed up using operating system commands.
  • Backups are restored using operating system commands.
  • Recovery is accomplished using SQL and SQL*PLUS commands.

Basic Backup Methodology

The basic method for taking user-managed backups of the whole database is as follows:

  1. Identify the datafiles, control files, and archived redo logs to be backed up by querying dynamic performance views or data dictionary tables (refer to “Querying V$ Views to Obtain Backup Information” for procedures).
  2. Use an operating system command such as the UNIX cp command to back up datafiles and archived redo logs (refer to “Making User-Managed Backups of the Whole Database” for procedures).
  3. Use a SQL statement to back up the control file (refer to “Making User-Managed Backups of the Control File” for procedures).
  4. Use an operating system command such as the UNIX cp command to back up configuration files (refer to“Making User-Managed Backups of Miscellaneous Oracle Files” for procedures).

WHOLE DATABASE BACKUP

CONSISTENT DATABASE BACKUP

To make a consistent whole database backup:

  1. SQL> SHUTDOWN IMMEDIATE (can use shutdown normal , transactional as well)
  2. Use an operating system utility to make backups of all datafiles as well as all control files specified by the CONTROL_FILES parameter of the initialization parameter file. Also, back up the initialization parameter file and other Oracle product initialization files.
    cp /disk1/oracle/dbs/*.dbf /disk2/backup
  3. Restart Database
      Startup;
INCONSISTENT DATABASE BACKUP

Database should be in archivelog mode.

1. ALTER DATABASE BEGIN BACKUP;
2. Copy database files
3. ALTER DATABASE END BACKUP;   –to exit from the backup mode.

TABLESPACE BACKUPS

OFFLINE TABLESPACE BACKUPS

1. Identify the tablespace’s datafiles by querying the DBA_DATA_FILES view.

SELECT TABLESPACE_NAME, FILE_NAME
  FROM SYS.DBA_DATA_FILES
  order by tablespace_name;

2. ALTER TABLESPACE users OFFLINE NORMAL; (NORMAL bring TS online without recovery)
3. Back up the offline datafiles.
4. ALTER TABLESPACE users ONLINE;
5. ALTER SYSTEM ARCHIVE LOG CURRENT; (archive the unarchived redo logs )

ONLINE TABLESPACE BACKUPS

1. Identify the tablespace’s datafiles by querying the DBA_DATA_FILES view.

SELECT TABLESPACE_NAME, FILE_NAME
  FROM SYS.DBA_DATA_FILES
  order by tablespace_name;

2. ALTER TABLESPACE users begin backup;
3. Back up the offline datafiles.
4. ALTER TABLESPACE users end backup;
5. ALTER SYSTEM ARCHIVE LOG CURRENT;

 

CONTROLFILE BACKUPS

Back up the database’s control file, specifying a filename for the output binary file. The following SQL statement backs up a database’s control file to /oracle/backup/cf.bak:

ALTER DATABASE BACKUP CONTROLFILE TO ‘/oracle/backup/cf.bak’ REUSE;

 

To back up the control file to a trace file, mount or open the database and issue the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the output is a trace file containing a CREATE CONTROLFILE ... NORESETLOGS statement. As in the case of binary control file backups, tempfile entries are not included in the trace output.