Executing offline cold backups in oracle is bit easy, Offline cold backup is nothing but bring your oracle database to shutdown and copy(backup) all database physical files to backup location manually. like data file, control file, log file, etc

Note: This article assumes you don’t use any tablespaces with ASM instance. For ASM instance, I strongly recommend to kindly perform your database backup with RMAN utility. RMAN: Recovery Manager.

For manual offline backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

List down the oracle data files which you have to backup, following SQL command will help you to determine file name and its location:

SQL> select file_name from dba_data_files;
 FILE_NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/users01.dbf
 /u01/app/oracle/oradata/RTS/undotbs01.dbf
 /u01/app/oracle/oradata/RTS/sysaux01.dbf
 /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

List down all online redologs and its location with following SQL command:

 SQL> select member from v$logfile;
 MEMBER
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/redo03.log
 /u01/app/oracle/oradata/RTS/redo02.log
 /u01/app/oracle/oradata/RTS/redo01.log
 3 rows selected.

Step 3>>

List down all control files and its location by following:

SQL> select name from v$controlfile;
 NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/control01.ctl
 /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

We have gathered all the physical file list that we’ll need for backup.

Step 4>>

Create directory on your system, In this case I am using “manual_offline_cold” for backup location, as you know this is my test environment.
Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ pwd
 /u01/bkup/manual_offline_cold

Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

Step 5>>

Grace fully shutdown your database with following command:

 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

Step 6>>

Now copy all the physical file that you have found in step number 1,2 and 3.

 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/users01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/undotbs01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo03.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo02.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo01.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/flash_recovery_area/RTS/control02.ctl /u01/bkup/manual_offline_cold

Once copy process complete, kindly verify the backup directory with all copied files.

 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ ll
 total 1376468
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control01.ctl
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control02.ctl
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo01.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo02.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:01 redo03.log
 -rw-r----- 1 oracle oinstall 492838912 Nov 15 22:59 sysaux01.dbf
 -rw-r----- 1 oracle oinstall 702554112 Nov 15 23:00 system01.dbf
 -rw-r----- 1 oracle oinstall 31465472 Nov 15 22:59 undotbs01.dbf
 -rw-r----- 1 oracle oinstall 5251072 Nov 15 22:58 users01.dbf

There is no need to backup temporary datafile those are associated with temporary tablespace, because technically we don’t use to restore database.

You can compress backup files with any compression utility to manage your disk space well for log period of retention.

Step 7>>
Start your database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 1469792256 bytes
 Fixed Size 2213456 bytes
 Variable Size 1040189872 bytes
 Database Buffers 419430400 bytes
 Redo Buffers 7958528 bytes
 Database mounted.
 Database opened.

Congratulations!! Manual offline backup successfully completed.

Stay Tune. 🙂