How to take Hot backup of Oracle database.

To take hot backup of oracle database first set the oracle in archive mode.

Note: To enable archive mode please view How to enable database archive mode in oracle

Create Hot backup directory and make oracle user as owner of this directory.

[root@arzaidi /]# mkdir /oracle_backup/hotbackup
[root@arzaidi /]# chown -R oracle.oinstall oracle_backup/
[root@arzaidi /]#


Now login as sysdba to database.

At sql prompt execute following script to get path and copy script for later use of control ,data and redolog file path in windows you can use copy paste.

select 'cp ' ||name|| ' /oracle_backup/hotbackup' from v$datafile


you will get following script as output for later use.

cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/sysaux01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/users01.dbf /oracle_backup/hotbackup
cp /oracle/oradata/ardb/example01.dbf /oracle_backup/hotbackup


execute following script to view all tablespaces.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.


Note: When system tablespace is being backuped oracle uses undo tablespace and when the undo tablespace is begin backuped the it uses system tablespace so
make backup of both tablespaces trun by trun.

First set system table space for backup.

SQL> alter tablespace system begin backup;


Now open another shell and execute copy command as oracle user for system tablespace or from graphical view copy and paste file in hotbackup folder from oradata/dbdatafile folder.


[oracle@arzaidi /]$ cp /oracle/oradata/ardb/system01.dbf /oracle_backup/hotbackup


SQL> alter tablespace system end backup;

Tablespace altered.


SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

[oracle@arzaidi /]$ cp /oracle/oradata/ardb/undotbs01.dbf /oracle_backup/hotbackup

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.


execute following script to get script to set tablespace in backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' begin backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM begin backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 begin backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
--Alter tablespace TEMP begin backup ; -- hot backup do not apply to this tablespace
Alter tablespace SYSAUX begin backup ;
Alter tablespace USERS begin backup ;
Alter tablespace EXAMPLE begin backup ;
6 rows selected.



execute following script to get script to unset tablespace from backup mode.

SQL> select 'Alter tablespace '|| tablespace_name ||' end backup ;' from dba_tablespaces;

-------------------------------------------------------------
--Alter tablespace SYSTEM end backup ;  -- do not put syatem and undo tablespaces at the same time
--Alter tablespace UNDOTBS1 end backup ; -- do not put syatem and undo tablespaces at the same time
--------------------------------------------------------------
Alter tablespace SYSAUX end backup ;
Alter tablespace USERS end backup ;
Alter tablespace EXAMPLE end backup ;
6 rows selected.

alter database backup controlfile to '/oracle_backup/hotbackup/controlfilebackup.bkp';

Now the database hot backup is complete.