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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: