Taking Cold Backup of Oracle Database

Introduction

Cold backup is consistent backup, as the backup is run when the database is shutdown. Its exact image copy of the database that can be used to clone database on other server. We can take Oracle database cold backup in two ways

1. Cold backup can be taken using OS command such as cp
2. Using RMAN utility also we can take cold backups

Taking Cold Backup of Oracle Database - Manual

Step 1:- Check the list of data files, control files and redolog files in the database

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/GGDB01/users01.dbf

/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

/u01/app/oracle/oradata/GGDB01/system01.dbf

/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/GGDB01/redo03.log

/u01/app/oracle/oradata/GGDB01/redo02.log

/u01/app/oracle/oradata/GGDB01/redo01.log

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/GGDB01/control01.ctl

/u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl

Step 2:- Shutdown the database using graceful shutdown

[oracle@prim backups]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 22 19:35:45 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

Step 3:- Now manually copy all the data files, control files and redolog files

$ cp /u01/app/oracle/oradata/GGDB01/users01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/undotbs01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/system01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/sysaux01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/ggdata01.dbf /u01/app/backups/
--------------------------------------------------------------------------------

$ cp /u01/app/oracle/oradata/GGDB01/redo01.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo02.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo03.log /u01/app/backups/

--------------------------------------------------------------------------------
$ cp /u01/app/oracle/oradata/GGDB01/control01.ctl /u01/app/backups/

$ cp /u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl /u01/app/backups/

Step 4:- Go to backup location and list the files. This step is to verify all the files are present 

[oracle@prim backups]$ ls -ltr

total 1898144

-rw-r----- 1 oracle oinstall   5251072 Aug 22 19:36 users01.dbf

-rw-r----- 1 oracle oinstall  31465472 Aug 22 19:36 undotbs01.dbf

-rw-r----- 1 oracle oinstall 702554112 Aug 22 19:37 system01.dbf

-rw-r----- 1 oracle oinstall 503324672 Aug 22 19:38 sysaux01.dbf

-rw-r----- 1 oracle oinstall 524296192 Aug 22 19:38 ggdata01.dbf

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo03.log

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control01.ctl

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control02.ctl

[oracle@prim backups]$

Now we have completed the cold backup for Oracle database :)

Step 5:- Once the backup is complete, start the database

Taking Cold Backup of Oracle Database using RMAN

Here we will demonstrate the procedure of taking RMAN cold backup for Oracle database

Step 1:- Shutdown the database and bring it to mount state

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2217992 bytes

Variable Size             532678648 bytes

Database Buffers          301989888 bytes

Redo Buffers                2396160 bytes

Database mounted.

SQL> 

Step 2:- Connect to RMAN then run full database backup and control file backup

[oracle@prim ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 22 22:52:34 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GGDB01 (DBID=1637703886, not open)

RMAN> backup database;

Starting backup at 22-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/GGDB01/system01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/GGDB01/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_nnndf_TAG20170822T225245_dsrt35w5_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncsnf_TAG20170822T225245_dsrt5lvt_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN> backup current controlfile;

Starting backup at 22-AUG-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncnnf_TAG20170822T225457_dsrt7boz_.bkp tag=TAG20170822T225457 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN>

Once the database is complete, open the database

SQL> alter database open;

Now we can successfully completed full :)