By: Juan Carlos Olamendy Turruellas

 

Introduction

 

This is the second article in a series where we’re being learning about the principles, concepts and real world scripts for doing backups to Oracle database.

 

In the first article, I’ve talked about the most important terms related to backups in Oracle databases. In this second article, I’ll talk about doing low-level manual backups in order to illustrate the principles and concepts of the first article. In the last articles, I’ll talk about a tool that automates and simplifies the backup process (no more low level tasks) named Recovery Manager (also know as RMAN).

 

Regarding to the concepts learned in the first article, we could recall that we have basically two backup strategies:

 

  1. Offline/cold/closed backup,
  2. Online/hot/open backup.

 

The examples below will be divided on these two strategies.

 

Offline/cold/closed backup

 

This type of backup has the following features:

 

  • It’s a whole database backup

  • It produces a consistent backup

  • The database can be restore from the last backup without performing the update step of the recovery process

  • It can be used with either archivelog or noarchivelog mode

    • In archivelog mode, we can take additional recovery steps to complete a backup to a point-of-last-committed-transaction

  • The database instance must be shut down normally (not due to an instance failure)

  • It uses the OS commands to backup the database files. They are:

    • All database files

    • All control files

    • All online redo log files

    • Optional: The initialization parameter file (init.ora)

  • Backups performed using OS commands, while the Oracle instance is running or crashed, are not valid. If not possible to shut down the instance, we need to execute a hot backup.

 

In order to execute this type of backup, we need to follow the steps:

 

1 - Obtain a list of files to backup

 

Use SQL*PLUS and query the v$datafile view to obtain the list of database files as show below in the listing 01.

 

SQL> SELECT name FROM v$datafile;

Listing 01

 

Use SQL*PLUS and query the v$controlfile view to obtain the list of control files as shown in the listing 02.

 

SQL> SELECT name FROM v$controlfile;

Listing 02

 

Use SQL*PLUS and query the v$logfile view to obtain the list of online redo log files as shown in the listing 03.

 

SQL> SELECT member FROM v$logfile;

Listing 03

 

2 - Copy the files to a backup directory

 

Now we need to shutdown the Oracle instance, and using the cp Unix command, let’s copy every file to a backup directory as shown in the listing 04.

 

$ cp -a database_file_path /u05/oradata/DBTEST/backup/

Listing 04

 

3- Do a backup archive

 

And finally, using the tar Unix command, I’ll archive the backup directory to a tape drive as shown below in the listing 05.

 

$ tar –cvf /dev/rmt/0hc /u05/oradata/DBTEST/backup/

Listing 05

 

We can automate this process using a shell script to generate the list of files to backup. At the end of the day, the backup process boils down to copying all the necessary files using the operating system copy utilities; and always remember to shutdown the instance before executing this type of backup. The script is shown below in the listing 06.

 

#!/bin/bash

BACKUP_DIR=/u05/oradata/DBTEST/backup/

OUTPUT_SCRIPT=cold_backup.sh

 

sqlplus -s /nolog <<EOF

CONNECT / AS SYSDBA

SET HEAD OFF FEED OFF ECHO OFF TRIMSPOOL ON LINESIZE 200

SPOOL $OUTPUT_SCRIPT

SELECT 'cp ' || name || ' ${BACKUP_DIR}' FROM v\$datafile;

SELECT 'cp ' || name || ' ${BACKUP_DIR}' FROM v\$controlfile;

SELECT 'cp ' || member || ' ${BACKUP_DIR}' FROM v\$logfile;

SPOOL OFF;

EXIT;

EOF

Listing 06

Online/hot/open backup

 

This type of backup has the following features:

 

  • The Oracle instance is running – we don't have to shut it down for doing this kind of backup

  • There are transactions changing data while we’re backing up files, so we need another mechanisms to perform the backups. So, it can give a read-consistent copy of the database, but it will not backup active transactions

  • The Oracle instance must be running in archivelog mode

  • It uses the OS commands to backup the database files. They are:

    • Database files

    • Control files

    • Online/archived redo log files

    • Optional: The initialization parameter file (init.ora)

 

Now let’s see the steps required to execute correctly an online/hot/open backup.

 

1 – Start the Oracle instance in archivelog mode

 

By default, when we create a database is in noarchivelog mode. So, we need to change to archivelog mode.

The steps to switch the database from noarchivelog into archivelog are:

 

  • Shutdown the instance

  • Start the instance and mount the database without opening

  • Change the database to archivelog mode and then open the database

 

It’s remarkable to say that changing the database into archivelog mode update the control files, so if we shutdown and restart the database, then the database starts in archivelog mode.

The listing 07 illustrates the steps.

 

-- Shutdown the instance

SQL> SHUTDOWN IMMEDIATE

 

-- Start the instance in mount mode (no open mode)

SQL> STARTUP MOUNT

 

-- Change the database archiving mode

-- Then open the database for normal operations

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

Listing 07

 

We need to modify the init.ora initialization parameter file to set the location and format of log archives as shown in the listing 08.

 

#Collect all of the archive log files in this directory

log_archive_dest=/u05/oradata/DBTEST/arch

 

#Specify a particular format for the archived log file

#%T refers to the thread number,

#%S refers to the sequence number

log_archive_format="%s_%t_%r.arc"

Listing 08

 

In order to check that everything is working correctly, we need to run the following command as shown in the listing 09.

 


 

Listing 09

 

We can force an Oracle instance to switch the current log file and to archive it using the command shown in the listing 10.

 

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Listing 10

 

2 – Backup database files

 

We can setup a tablespace into a backup mode as shown below in the listing 11.

 

SQL> ALTER TABLESPACE data BEGIN BACKUP;

Listing 11

 

Next step is to backup the underlying database files using the OS commands. In order to get a list of the database files associated to a tablespace, we need to run the statement shown in the listing 12.

 

SQL> SELECT FILE_NAME

SQL> FROM DBA_DATA_FILES

SQL> WHERE TABLESPACE_NAME='DATA';

Listing 12

 

From the former step output, we can copy the database files into a backup directory using the cp Unix command as shown in the listing 13.

 

$ cp -a database_file_path /u05/oradata/DBTEST/backup/

Listing 13

 

When the copy is done, we need to indicate the tablespace to turn into a normal state as shown in the listing 14.

 

SQL> ALTER TABLESPACE data END BACKUP;

Listing 14

 

3 – Backup the archived log files

 

After completing an inconsistent backup, we need to backup all archived redo logs produced since the backup began; otherwise, we cannot recover from the backup.

We can delete the original archived logs from the storage after this backup step.

 

4 – Backup the control files

 

While running in archivelog mode, we need to backup the control files. During the update step in the recovery process, we must use the backup of the control file. We can backup a control file to a physical backup file as shown below in the listing 15. The REUSE clause indicates to overwrite any current backup that exists.

 

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u05/oradata/DBTEST/backup/control.ctl.bak' REUSE;

Listing 15

 

It’s remarkable to say that we can make several tablespace backups by putting the database on backup mode as shown in the listing 16.

 

SQL> ALTER DATABASE BEGIN BACKUP;

 

SQL> ALTER DATABASE END BACKUP;

Listing 16

 

Conclusion

 

In this second part, I've illustrated the key principles and concepts related to backup in Oracle databases using real world examples. Now you can adapt these scripts to your own backup scenarios.