User Manged Hotbackup for 10g

Perform a Oracle Hot backup

Simple Steps for a hot backup in Oracle 10g;

         1.    Check the database for if it is in the Archive mode.

         2.    SQL>Alter database begin backup;

         3.    Go to the OS level copy the datafiles to the backup location.

         4.    SQL>Alter database end backup;

===============================================================================

Hot back is also known as inconsistent backup since it requires database recovery to bring back the database up.

Note * Hot backup requires  your database to be in Archive log mode.

Step:1  : Find out if the your database is in Archive log mode first through this command:

SQL>select log_mode from v$database;

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE

-----------------

ARCHIVELOG

You are good to proceed if you find it to be on Archive log mode.

Step 2: Find out the files that you may require to backup – The backup would consists of (datafiles, online redo logfiles , control files, spfile,

SQL>SELECT NAME “Files To 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’;

File To Backup
——————————————————————————–
/oradata/data1/dbase/system01.dbf
/oradata/data1/dbase/undotbs01.dbf
/oradata/data1/dbase/sysaux01.dbf
/oradata/data1/dbase/users01.dbf
/oradata/data.dbf
/oradata/data1/data02.dbf
/oradata/6.dbf
/oradata/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata/data_test.dbf
/oradata/data1/dbase/redo03.log
/oradata/data1/dbase/redo02.log
/oradata/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.

The Above query  indicates a backup is needed for 13 files.

Note * The read only tablespace and the offline tablespace need not be placed into backup mode as there is changes made to these files during database operation.

Use this Query to check the status of the tablespaces and datafiles in backup mode.

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;

You may use the below script to place the online tablespace in the begin backup mode

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;

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 UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;

6 rows selected.

Note*: - Since Oracle 10g you have an additional feature that enables you to put the whole database in the backup mode and the then copy all the data files to a common location.

You may use the below command  to check if the backup mode is still active or not for the tablespaces.

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’;

Script to determine the status of the tablespaces if they are in the backup mode

SQL>SELECT ‘host scp ‘|| d.name ||’ &backup_location’ FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS=’ACTIVE’;

      

Once the copying is over put the tablespace or the database in end backup mode.

SQL>ALTER DATABASE END BACKUP;

OR

SQL>ALTER TABLESPACE END BACKUP;