User Managed Online Backups (Hot Backup)

While Recovery Manager (RMAN) is highly recommended for database backup and recovery operations your organization may require you to perform backups using a custom process or you might have an interest in knowing what is required to perform a user managed backup.

This document will detail the steps to perform a user managed online database backup commonly referred to as a hot backup. The database needs to be in archive log mode in order to perform an online backup.

To perform a user managed online back you need to copy all of the data files including undo files, the control files, the SPFILE and any archive logs. You do not need to copy the online redo logs or the temporary files.

The database will be open and possibly processing transactions during the backup so we cannot just simply copy the files as they can be changed during the copy process. In order to facilitate copying Oracle data files Oracle provides the ability to freeze the data file header. This can be done at the tablespace level and the database level.

Tablespace Level

ALTER TABLESPACE BEGIN BACKUP
ALTER TABLESPACE END BACKUP;

Database Level

ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP:

While in backup mode the data file header reflects the checkpoint SCN at the time of BEGIN BACKUP. While in backup mode redo is increased because whole data blocks are written to the redo stream when changed in addition to the normal change vector behavior. Because of the increased redo you will want to ensure that the END BACKUP is issued after the backup is complete. Depending on the size of the database it might make sense to limit the scope to the tablespace level.

Finding the names and locations of the files can be found querying the database. The steps performed in this document were done on an Oracle Enterprise Linux system and the same on Windows or other UNIX variants.

Determine if the database is in archive log mode and the archive log location.

While logged into the database with SYSDBA privileges issue archive log list.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/oradata/odlin11g/arch
Oldest online log sequence     72
Current log sequence           74
SQL>

The archive log mode along with the destination in which archive logs are written is displayed.

Determine the location of the SPFILE.

Find the location of the SPFILE by querying V$PARAMETER.

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileodlin11g.ora

SQL>

Determine the location of the control files.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/odlin11g/control01.ctl
/u01/app/oracle/oradata/odlin11g/control02.ctl

SQL>

Determine the location of the data files

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/odlin11g/system01.dbf
/u01/app/oracle/oradata/odlin11g/sysaux01.dbf
/u01/app/oracle/oradata/odlin11g/undotbs01.dbf
/u01/app/oracle/oradata/odlin11g/users01.dbf

SQL>

Build a script to copy the files

The backup script will be built to follow the following task flow.

Backup archive logs
Put the database in backup mode
Backup data files
Backup archive logs
Backup control files and spfiles
Take the database out of backup mode

The reason for backing up archive logs both before and after backing up the database is because we want to be able to capture any archive logs that might have been written during the backup of the data files. This process is similar to the RMAN command BACKUP DATABSE PLUS ARCHIVE LOG.

Below is an SQL script that can be used to build a script to perform the steps listed above to perform a user managed online backup. The script spools the results to a file called backupscript.sql.

set echo off
set heading off
set feedback off
set linesize 130
set termout off
set pagesize 0

spool backupscript.sql

select 'alter system archive log current;'
from dual;

select 'host cp /u01/app/oracle/oradata/odlin11g/arch/* /u01/app/oracle/oradata/odlin11g/backup2'
from dual;

select 'alter database begin backup;'
from dual;

select 'host cp '||name||' /u01/app/oracle/oradata/odlin11g/backup2'
from v$datafile;

select 'alter database end backup;'
from dual;

select 'alter system archive log current;'
from dual;

select 'host cp -u /u01/app/oracle/oradata/odlin11g/arch/* /u01/app/oracle/oradata/odlin11g/backup2'
from dual;

select 'host cp '||name||' /u01/app/oracle/oradata/odlin11g/backup2'
from v$controlfile
union
select 'host cp '||value||' /u01/app/oracle/oradata/odlin11g/backup2'
from v$parameter
where name = 'spfile';

spool off
set echo on
set heading on
set feedback on
set termout on

The output below is from the execution of the script detailed above. The script was stored in the file bkup.sql.

SQL> @bkup.sql
SQL> set termout off
SQL> @backupscript.sql
SQL> alter system archive log current;

System altered.

SQL> host cp /u01/app/oracle/oradata/odlin11g/arch/* /u01/app/oracle/oradata/odlin11g/backup2

SQL> alter database begin backup;

Database altered.

SQL> host cp /u01/app/oracle/oradata/odlin11g/system01.dbf /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/sysaux01.dbf /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/undotbs01.dbf /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/users01.dbf /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/example01.dbf /u01/app/oracle/oradata/odlin11g/backup2

SQL> alter database end backup;

Database altered.

SQL> alter system archive log current;

System altered.

SQL> host cp -u /u01/app/oracle/oradata/odlin11g/arch/* /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/control01.ctl /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/oradata/odlin11g/control02.ctl /u01/app/oracle/oradata/odlin11g/backup2

SQL> host cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileodlin11g.ora /u01/app/oracle/oradata/odlin11g/backup2

SQL> 

While effectively demonstrating a process to perform an online backup this script would need much more work to use in a production environment. For example the script does not perform any maintenance on the archive logs after they have been backed up nor has any consideration been given to address filename collisions.