User Managed hot backup of oracle database  

Posted by Mawahid in


Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.

To take full database backup follow the following steps.

1) Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown.


2) Determine the files that you need to take backup.

Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.

In order to decide which files you need to backup issue the following query.


SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';


3) Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.

You can check the status, tablespace_name and it's associated data file name with the following query


SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile" FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;


Take the tablespace in backup mode rather than offline and read-only tablespace.

You can easily make a script of taking the online tablespace in backup mode by following query.


SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');
 

Alternatively, you can issue

SQL>ALTER DATABASE BEGIN BACKUP;


4) Copy the datafile to backup location.

After making a tablespace in backup mode take backup/copy of the associated datafiles.
 

For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,

SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';


Here you can also make a script in order to copy datafiles to another location. 

SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
 

5) Whenever your copy is finished make the tablespace out of backup mode.

SQL>ALTER DATABASE END BACKUP; 
 

This entry was posted on Tuesday, January 11, 2011 at 8:31 AM and is filed under . You can follow any responses to this entry through the comments feed .

0 comments

Post a Comment