How do we backup an Oracle Database that is in archive-log mode

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

In this article we will analyze what it takes to take an online backup of an Oracle database that is in archived-log mode.

In order to take a backup in addition to the datafiles, we should also take a backup of the controlfile, spfile and the archived logs.

What is control file, spfile and archive logs?

Control File

The control file is the file that contains the name of the base, the location of the base files and the current log sequence number (lsn) or otherwise the time the base is located.

SPFILE

The spfile contains the parameters needed to run the base instance. It contains information such as the name of the base, the resources it uses and the location of the control file. To edit it we should first have made a pfile (create pfile from spfile) based on it as we can only edit the pfile and not the spfile directly.

Archived logs

When the database is in archived-log mode the information from the redo logs is stored offline through the archiving process. These saved logs are called archived logs. And they enable us to take online backup and to be able to restore to point in time.

Can we whenever we take a backup of the database take the control file together with the spfile?

Yes! We have this feature by setting to RMAN (Oracle Recovery Manager) the order CONFIGURE CONTROLFILE AUTOBACKUP ON to automatically get every time we backup the base and to control file and the spfile to the default location.

The example

In our example we will connect to RMAN (the tool provided by Oracle for backup/restore) and we will declare line by line to take a backup and its base files and the control file and the spfile and the archives.

We will define only one channel for communication to the discs with a maximum size of each file of 500mb.

We create a file named param.rman with the corresponding parameters as below.

RUN
{
	ALLOCATE CHANNEL kanali TYPE DISK MAXPIECESIZE 500M;
	BACKUP
	FORMAT  '/home/oracle/oracle/app/backup/%Y%M%D__fulldb__%U' 
	DATABASE
	CURRENT CONTROLFILE
	FORMAT  '/home/oracle/oracle/app/backup/%Y%M%D__controldb__%U'	
	SPFILE
	FORMAT  '/home/oracle/oracle/app/backup/%Y%M%D__spdb__%U'
	PLUS ARCHIVELOG
	FORMAT  '/home/oracle/oracle/app/backup/%Y%M%D___archivesdb_%U';
	RELEASE CHANNEL kanali;
} 

To run the script in RMAN we should have set the environment to ORACLE_SID and ORACLE_HOME. We can make all of the following as an executable file e.g. backup.sh.

export ORACLE_SID=db
export ORACLE_HOME=/home/oracle/oracle/app
export NLS_DATE_FORMAT='dd/mm/yyy hh24:mi'

rman target / cmdfile=/home/oracle/param.rman log=/home/oracle/backuplog`date +%Y%m%d_%H%M`.log

After giving the appropriate permissions to the file (chmod) we run it.

nohup ./backup.sh &

Sources:

• Database Backup and Recovery User's Guide