Oracle Cold||Offline||Consistence backup and restoration

To take the cold backup we need to shutdown the database and take CRD(Controfile/Datafile/Redologfiles) and Pfile or Spfile

Cold backup is consistent backup, No need archivelog backup.

How to take oracle database cold backup:

Follow the steps mentioned below for cold backup and restoration.


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 name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
XYZ       READ WRITE

SQL> set linesize 400 pages 4000
SQL> col file_name for a35

FILE_NAME
-----------------------------------
/u01/datafiles/USERS01.DBF
/u01/datafiles/UNDOTBS01.DBF
/u01/datafiles/SYSAUX01.DBF
/u01/datafiles/SYSTEM01.DBF

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

SQL> col member for a30
SQL> select member from v$logfile;

MEMBER
--------------------------
/u01/datafiles/REDO2A.LOG
/u01/datafiles/REDO2B.LOG
/u01/datafiles/REDO1A.LOG
/u01/datafiles/REDO1B.LOG
/u01/datafiles/REDO3A.LOG
/u01/datafiles/REDO3B.LOG

6 rows selected.

3. List down all control files and its location by following:

SQL> select name from v$controlfile;
SQL?col name for a30
------------------------------
/u01/datafiles/CONTROL01.CTL
/u01/datafiles/CONTROL02.CTL
/u01/datafiles/CONTROL03.CTL


4. List down all temp files and its location by following:

SQL> select file_name from dba_temp_files;

FILE_NAME
-----------------------------------
/u01/datafiles/TEMP01.DBF

SQL>

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


5. Create directory on your system, In this case I am using “OFFLINEBKP” for backup location, as you know this is my test environment.

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

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

6.Grace fully shutdown your database with following command:

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

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

[oracle@PR ~]$ cd /u01/datafiles/
[oracle@PR ~]$ pwd
/u01/datafiles/
[oracle@PR ~]$ cp /u01/datafiles/USERS01.DBF  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/UNDOTBS01.DBF  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/SYSAUX01.DBF  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/SYSTEM01.DBF  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO2A.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO2B.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO1A.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO1B.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO3A.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/REDO3B.LOG  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/CONTROL01.CTL  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/CONTROL02.CTL  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/CONTROL03.CTL  /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp /u01/datafiles/TEMP01.DBF  /u01/bkup/OFFLINEBKP

Copy pfile or spfile from ORACLE_HOME location to backup directory "/u01/bkup/OFFLINEBKP"
[oracle@PR ~]$ cd /u01/app/oracle/product/12.1.0.2/home_1
[oracle@PR ~]$ pwd
/u01/app/oracle/product/12.1.0.2/home_1
[oracle@PR ~]$ cp INITXYZ.ora /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp SPFILEXYZ.ora /u01/bkup/OFFLINEBKP


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

[oracle@PR ~]$ cd /u01/bkup/OFFLINEBKP
[oracle@PR OFFLINEBKP]$ ls -lrt
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/USERS01.DBF  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/UNDOTBS01.DBF  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/SYSAUX01.DBF  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/SYSTEM01.DBF  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO2A.LOG  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO2B.LOG  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO1A.LOG  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO1B.LOG  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO3A.LOG  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/REDO3B.LOG  
-rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02/u01/datafiles/CONTROL01.CTL  
-rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02/u01/datafiles/CONTROL02.CTL  
-rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02/u01/datafiles/CONTROL03.CTL  
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/TEMP01.DBF 
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/INITXYZ.ora 
-rw-r----- 1 oracle oinstall 34273392 Nov 15 23:02/u01/datafiles/SPFILEXYZ.ora  

There is no need to backup temporary datafile those are associated with temporary tablespace

Cold backup Restoration:

1. Restore or copy spfile or pfile from /u01/bkup/OFFLINEBKP to ORACLE_HOME /u01/app/oracle/product/12.1.0.2/home_1/dbs

[oracle@PR ~]$ cd /u01/bkup/OFFLINEBKP
[oracle@PR ~]$ cp INITXYZ.ora /u01/app/oracle/product/12.1.0.2/home_1/dbs
[oracle@PR ~]$ cp SPFILEXYZ.ora /u01/app/oracle/product/12.1.0.2/home_1/dbs

2. Copy or restore  CRD and Tempfiles to thier respective directory structure.

[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/USERS01.DBF  /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/UNDOTBS01.DBF /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/SYSAUX01.DBF /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/SYSTEM01.DBF /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO2A.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO2B.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO1A.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO1B.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO3A.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/REDO3B.LOG /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/CONTROL01.CTL  /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/CONTROL02.CTL  /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/CONTROL03.CTL  /u01/datafiles/
[oracle@PR ~]$ cp /u01/bkup/OFFLINEBKP/TEMP01.DBF /u01/datafiles/

3. Start your database with pfile or spfile:

 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.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
XYZ       READ WRITE

########################################Good Luck ########################################