Home  >  Oracle Database Administration (DBA)  >  Backup & Recovery  >  User Managed Backups

1. User Managed Backup Overview

A user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.  

The user-managed backups could be take at the following levels:

• Data file level

• Tablespace level

• Database level 

2. How could I take an online TABLESPACE level backup ?

• the database should be in ARCHIVELOG mode

• put the tablespace in "Begin Backup" mode  (example:  ALTER TABLESPACE users BEGIN BACKUP;  )

• copy the physical files associated with this tablespace on another location using OS commands

• put the tablespace in "End Backup" mode  (example:  ALTER TABLESPACE users END BACKUP;  )

• Archive the unachieved redo logs so that the redo required to recover the tablespace backups is archived                                    (  SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  )

• Take a backup of all archived redo log files generated between Begin Backup and End Backup using OS commands

NOTES: 

• Many tablespaces could be backed up in parallel. However, online redo logs can grow large if multiple users are updating these tablespaces because the redo must contain a copy of each changed data block. Oracle doesn't recommend this.

• When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files. When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups. To fix this problem, simply switch to RMAN backups.

• If the tablespace is in READ ONLY mode, we don't need to put the tablespace in Backup Mode. 

 

  3. Which are the files which could be backed up ?

 

    SELECT name FROM v$datafile;

    SELECT member FROM v$logfile;

    SELECT name FROM v$controlfile;

 

    To view which file correspond to which tablespace you can run:

 SELECT t.NAME "Tablespace", f.NAME "Datafile"

 FROM       V$TABLESPACE t,

                    V$DATAFILE f

 WHERE    t.TS# = f.TS#

 ORDER BY t.NAME; 

4. How could I take an online FILE level backup ?

• the database should be in ARCHIVELOG mode

• put the datafile in OFFLINE mode  (example:  ALTER DATABASE DATAFILE 'C:\oradata\file1.dbf' OFFLINE;  )

• copy the physical file on another location using OS commands

• put the datafile in ONLINE mode  (example:  ALTER DATABASE DATAFILE 'C:\oradata\file1.dbf' ONLINE;  )

• backup the control file as the database has gone through structural changes 

5. TABLESPACE Recovery

This is done with SQL> RECOVER TABLESPACE command. It is possible to perform a tablespace recovery while the rest of the database is online. 

The prerequisites for a tablespace recovery are:

• The tablespace must be OFFLINE (the database could be online)

• Only COMPLETE recovery is possible

• SYSTEM tablespace never can be recovered because is online all the time.

More information about  this subject ( User Managed Backups ) you can get from  www.in-oracle.com