User managed backup means taking backup of the database without making use of any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup refers to taking the backup of database whenever it is in open state.
To use hot backups, the database should operate in ARCHIVELOG mode.
1. Check whether the database is in ARCHIVELOG mode or not
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
——————-
ARCHIVELOG
If not, place the database in archive log mode ( assuming your database is up and running ) :
SQL> alter database close;
SQLPLUS>alter database archivelog;
SQL>alter database open;
Display online log sequence:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:oracleproduct10.1.0db_2RDBMS
Oldest online log sequence 169
Next log sequence to archive 171
Current log sequence 171
2. Find files required to backup using the following query:
SQL> SELECT NAME “FILES TO BACKUP” FROM V$DATAFILE
2 UNION ALL
3 SELECT NAME FROM V$CONTROLFILE
4 UNION ALL
5 SELECT MEMBER FROM V$LOGFILE
6 UNION ALL
7 SELECT VALUE FROM V$PARAMETER WHERE NAME=’SPFILE’;
FILES TO BACKUP
——————————————————-
D:CRMDATASYSTEM.DBF
D:CRMDATASYSAUX.DBF
D:CRMDATAUNDOTBS.DBF
D:CRMDATACRM.DBF
D:CRMCONTROLCRM_CTL1.CTL
D:CRMCONTROLCRM_CTL2.CTL
D:CRMCONTROLCRM_CTL3.CTL
D:CRMREDOR1.LOG
D:CRMREDOR2.LOG
D:CRMREDOR3.LOG
10 rows selected.
For online tablespace, it must be taken to backup mode. Check status of tablespace :
SQL> SELECT S.STATUS, S.TABLESPACE_NAME “TABLESPACE”, D.FILE_NAME “DATAFILE”
2 FROM DBA_TABLESPACES S, DBA_DATA_FILES D
3 WHERE S.TABLESPACE_NAME = D.TABLESPACE_NAME;
STATUS TABLESPACE DATAFILE
————– ———————– —————–
ONLINE SYSTEM D:CRMDATASYSTEM.DBF
ONLINE SYSAUX D:CRMDATASYSAUX.DBF
ONLINE UNDOTBS D:CRMDATAUNDOTBS.DBF
ONLINE CRM D:CRMDATACRM.DBF
Determine datafile status for online tablespace backups:
To check whether a datafile is part of a current online tablespace backup, query the V$BACKUP view. This view is useful only for user-managed online tablespace backups, not offline tablespace backups or RMAN backups.
The V$BACKUP view is most useful when the database is open. It is also useful immediately after an instance failure because it shows the backup status of the files at the time of the failure. Use this information to determine whether you have left any tablespaces in backup mode.
V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$BACKUP accurately. Also, if you have restored a backup of a file, this file’s STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view can contain misleading data about restored files.
SQL> SELECT * FROM V$BACKUP;
FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 1772260 30-SEP-11
2 NOT ACTIVE 1772260 30-SEP-11
3 NOT ACTIVE 1772260 30-SEP-11
4 NOT ACTIVE 1772260 30-SEP-11
SQL> SELECT ‘ALTER TABLESPACE ‘ ||TABLESPACE_NAME || ‘ BEGIN BACKUP; ‘ “SCRIPT”
FROM DBA_TABLESPACES WHERE STATUS NOT IN (‘READ ONLY’,’OFFLINE’);
SCRIPT
—————————————————————————–
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE CRM BEGIN BACKUP;
SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;
Tablespace altered.
SQL> SELECT T.NAME AS “TABLESPACE”, D.NAME AS “DATAFILE”, S.STATUS
2 FROM V$DATAFILE D, V$TABLESPACE T, V$BACKUP S
3 WHERE D.TS#=T.TS# AND S.FILE#=D.FILE#
4 AND S.STATUS=’ACTIVE’;
TABLESPACE DATAFILE STATUS
———————– —————— —————-
SYSTEM D:CRMDATASYSTEM.DBF ACTIVE
In the STATUS column, NOT ACTIVE indicates that the file is not currently in backup mode (that is, ALTER TABLESPACE … BEGIN BACKUP), whereas ACTIVE indicates that the file is currently in backup mode.
Copy the physical files associated with this tablespace to desired backup directory using OS commands / graphical utility.
C:>COPY D:CRMDATASYSTEM.DBF E:BACKUP_CRM2011_OCTOBER_11
1 file(s) copied.
SQL> ALTER TABLESPACE SYSTEM END BACKUP;
Tablespace altered.
Alternatively, put all tablespaces in backup mode at the same time using ‘ALTER DATABASE BEGIN BACKUP’ :
Archive the unachieved redo logs so that the redo required to recover the tablespace backups is archived.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
Backup archive files to backup folder.
SQL>ALTER DATABASE BEGIN BACKUP;
Database altered.
Use OS commands / graphical utility / run script to take the backup of all the datafiles.
SQL>ALTER DATABASE END BACKUP;
Database altered.
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
Backup archive files, control files and spfile to backup folder.
It is a bad idea to put all tablespaces in backup mode, as it is better to do it one by one in order to minimize the supplemental redo logging overhead.
Oracle introduces this ‘shortcut’ for one reason only: when doing backup with a mirror split (BCV, Flashcopy, etc), the copy gets all the datafiles at the same time, and the copy lasts only few seconds. In that case, it is easier to use that command to put all tablespaces in backup mode during the operation.
When BEGIN BACKUP is issued:
The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy.
This is to manage the backup consistency issue when the copy will be used for a recovery.
A checkpoint is done for the tablespace, so that no dirty buffer remains from modifications done before that point.
Begin backup command completes only when checkpoint is done.
During backup:
The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup.
Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs.
This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a ‘backup’ SCN)
Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behaviour that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.
When END BACKUP is issued:
A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
The hot backup flag in the datafile headers is unset.
The header SCN is written with the current one.
Force a log switch, so that Oracle will create an archived log file
SQL> ALTER SYSTEM SWITCH LOGFILE;
Backup control file
SQL> ALTER DATABASE BACKUP CONTROLFILE TO filespec;