You perform a user-managed cold backup by copying files after the database has been shut down. This type of backup is also known as an offline backup. Your Oracle database 12C can be in either noarchivelog mode or archivelog mode when you make a cold backup.
DBAs tend to think of a cold backup as being synonymous with a backup of a database in noarchivelog mode. That isn’t correct. You can make a cold backup of a database in archivelog mode, and that’s a backup strategy that many shops employ. The differences between a cold backup with the database in noarchivelog mode and in archivelog mode are detailed in the following sections.
Making a Cold Backup of a Noarchivelog Mode Database
One main reason for making a cold backup of a Oracle database 12C in noarchivelog mode is to give you a way to restore a database back to a point in time in the past. You should use this type of backup only if you don’t need to recover transactions that occurred after the backup. This type of backup and recovery strategy is acceptable only if your business requirements allow for the loss of data and downtime. Rarely would you ever implement this type of backup and recovery solution for a production business database.
Having said that, there are some good reasons to implement this type of backup. One common use is to make a cold backup of a development/test/training database and periodically reset the database back to the baseline. This gives you a way to restart a performance test or a training session with the same point-in-time snapshot of the database.
Tip Consider using the Flashback Database feature to set your database back to a point in time in the past.
The example in this section shows you how to make a backup of every critical file in your database: all control files, data files, temporary data files, and online redo log files. With this type of backup, you can easily restore your database back to the point in time when the backup was made. The main advantages of this approach are that it’s conceptually simple and easy to implement. Here are the steps required for a cold backup of a database in noarchivelog mode:
- Determine where to copy the backup files and how much space is required.
- Identify the locations and names of the database files to copy.
- Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause.
- Copy the files (identified in step 2) to the backup location (determined in step 1).
- Restart your Oracle database 12C.
The following sections elaborate on these steps.
Ideally, the backup location should be on a set of disks separate from your live data files location. However, in many shops, you may not have a choice and may be told which mount points are to be used by the database. For this example the backup location is the directory /u01/cbackup/O12C. To get a rough idea of how much space you need to store one copy of the backups, you can run this query:
select sum(sum_bytes)/1024/1024 m_bytes from( select sum(bytes) sum_bytes from v$datafile union select sum(bytes) sum_bytes from v$tempfile union select (sum(bytes) * members) sum_bytes from v$log group by members);
You can verify how much operating disk space is available with the Linux/Unix df (disk free) command. Make sure that the amount of disk space available at the OS is greater than the sum returned from the prior query:
$ df -h
Run this query to list the names (and paths) of the files that are included in a cold backup of a noarchivelog mode database:
select name from v$datafile union select name from v$controlfile union select name from v$tempfile union select member from v$logfile;
BACKING UP ONLINE REDO LOGS (OR NOT)
Do you need to back up the online redo logs? No; you never need to back up the online redo logs as part of any type of backup. Then, why do DBAs back up the online redo logs as part of a cold backup? One reason is that it makes the restore process for the noarchivelog mode scenario slightly easier. The online redo logs are required to open the database in a normal manner.
If you back up all files (including the online redo logs), then to get your Oracle database 12C back to the state it was in at the time of the backup, you restore all files (including the online redo logs) and start up your database.
Connect to your database as the SYS (or as a SYSDBA-privileged user), and shut down your database, using IMMEDIATE, TRANSACTIONAL, or NORMAL. In almost every situation, using IMMEDIATE is the preferred method. This mode disconnects users, rolls back incomplete transactions, and shuts down the database:
$ sqlplus / as sysdba SQL> shutdown immediate;
For every file identified in step 2, use an OS utility to copy the files to a backup directory (identified in step 1). In this simple example all the data files, control files, temporary database files, and online redo logs are in the same directory. In production environments, you’ll most likely have files spread out in several different directories. This example uses the Linux/Unix cp command to copy the database files from /u01/dbfile/O12C to the /u01/cbackup/O12C directory:
$ cp /u01/dbfile/O12C/*.* /u01/cbackup/O12C
After all the files are copied, you can start up your database:
$ sqlplus / as sysdba SQL> startup;
Restoring a Cold Backup in Noarchivelog Mode with Online Redo Logs
The next example explains how to restore from a cold backup of a database in noarchivelog mode. If you included the online redo logs as part of the cold backup, you can include them when you restore the files. Here are the steps involved in this procedure:
- Shut down the instance.
- Copy the data files, online redo logs, temporary files, and control files back from the backup to the live database data file locations.
- Start up your database.
These steps are detailed in the following sections.
Shut down the instance, if it’s running. In this scenario it doesn’t matter how you shut down the Oracle database 12C, because you’re restoring back to a point in time (with no recovery of transactions). Any files in the live database directory locations are overwritten when the backup files are copied back. If your instance is running, you can abruptly abort it. As a SYSDBA-privileged user, do the following:
$ sqlplus / as sysdba SQL> shutdown abort;
This step does the reverse of the backup: you’re copying files from the backup location to the live database file locations. In this example all the backup files are located in the /u01/cbackup/O12C directory, and all files are being copied to the /u01/dbfile/O12C directory:
$ cp /u01/cbackup/O12C/*.* /u01/dbfile/O12C
Connect to your database as SYS (or a user that has SYSDBA privileges), and start up your database:
$ sqlplus / as sysdba SQL> startup;
After you finish these steps, you should have an exact copy of your database as it was when you made the cold backup. It’s as if you set your database back to the point in time when you made the backup.
Restoring a Cold Backup in Noarchivelog Mode Without Online Redo Logs
As mentioned earlier, you don’t ever need the online redo logs when restoring from a cold backup. If you made a cold backup of your database in noarchivelog mode and didn’t include the online redo logs as part of the backup, the steps to restore are nearly identical to the steps in the previous section. The main difference is that the last step requires you to open your database, using the OPEN RESETLOGS clause. Here are the steps:
- Shut down the instance.
- Copy the control files and data files back from the backup.
- Start up the database in mount mode.
- Open the database with the OPEN RESETLOGS clause.
Shut down the instance, if it’s running. In this scenario it doesn’t matter how you shut down the Oracle database 12C, because you’re restoring back to a point in time. Any files in the live database directory locations are overwritten when the backups are copied. If your instance is running, you can abruptly abort it. As a SYSDBA-privileged user, do the following:
$ sqlplus / as sysdba SQL> shutdown abort;
Copy the control files and data files from the backup location to the live data file locations:
$ cp <backup directory>/*.* <live database file directory>
Connect to your database as SYS or a user with SYSDBA privileges, and start the database in mount mode:
$ sqlplus / as sysdba SQL> startup mount
SQL> alter database open resetlogs;
If you see the Database altered message, the command was successful. However, you may see this error:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
In this case, issue the following command:
SQL> recover database until cancel;
You should see this message:
Media recovery complete.
Now, attempt to open your database with the OPEN RESETLOGS clause:
SQL> alter database open resetlogs;
This statement instructs Oracle to recreate the online redo logs. Oracle uses information in the control file for the placement, name, and size of the redo logs. If there are old online redo log files in those locations, they’re overwritten.
If you’re monitoring your alert.log throughout this process, you may see ORA-00312 and ORA-00313. This means that Oracle can’t find the online redo log files; this is okay, because these files aren’t physically available until they’re recreated by the OPEN RESETLOGS command.
It’s instructional to view how to script a cold backup. The basic idea is to dynamically query the data dictionary to determine the locations and names of the files to be backed up. This is preferable to hard-coding the directory locations and file names in a script. The dynamic generation of a script is less prone to errors and surprises (e.g., the addition of new data files to a database but not to an old, hard-coded backup script).
Note The scripts in this section aren’t meant to be production-strength backup and recovery scripts. Rather, they illustrate the basic concepts of scripting a cold backup and subsequent restore.
The first script in this section makes a cold backup of a database. Before you use the cold backup script, you need to modify these variables in the script to match your database environment:
The cbdir variable specifies the name of the backup-directory location. The script creates a file named coldback.sql, which is executed from SQL*Plus to initiate a cold backup of the database:
#!/bin/bash ORACLE_SID=O12C ORACLE_HOME=/u01/app/oracle/product/184.108.40.206/db_1 PATH=$PATH:$ORACLE_HOME/bin # sqlplus -s <<EOF / as sysdba set head off pages0 lines 132 verify off feed off trimsp on define cbdir=/u01/cbackup/O12C spo coldback.sql select 'shutdown immediate;' from dual; select '!cp ' || name || ' ' || '&&cbdir' from v\$datafile; select '!cp ' || name || ' ' || '&&cbdir' from v\$tempfile; select '!cp ' || member || ' ' || '&&cbdir' from v\$logfile; select '!cp ' || name || ' ' || '&&cbdir' from v\$controlfile; select 'startup;' from dual; spo off; @@coldback.sql EOF exit 0
This file generates commands that are to be executed from an SQL*Plus script to make a cold backup of a Oracle database. You place an exclamation mark (!) in front of the Unix cp command to instruct SQL*Plus to host out to the OS to run the cp command. You also place a backward slash (\) in front of each dollar sign ($) when referencing v$ data dictionary views; this is required in a Linux/Unix shell script. The \ escapes the $ and tells the shell script not to treat the $ as a special character (the $ normally signifies a shell variable).
After you run this script, here is a sample of the copy commands written to the coldback.sql script:
shutdown immediate; !cp /u01/dbfile/O12C/system01.dbf /u01/cbackup/O12C !cp /u01/dbfile/O12C/sysaux01.dbf /u01/cbackup/O12C !cp /u01/dbfile/O12C/undotbs01.dbf /u01/cbackup/O12C !cp /u01/dbfile/O12C/users01.dbf /u01/cbackup/O12C !cp /u01/dbfile/O12C/tools01.dbf /u01/cbackup/O12C !cp /u01/dbfile/O12C/temp01.dbf /u01/cbackup/O12C !cp /u01/oraredo/O12C/redo02a.rdo /u01/cbackup/O12C !cp /u02/oraredo/O12C/redo02b.rdo /u01/cbackup/O12C !cp /u01/oraredo/O12C/redo01a.rdo /u01/cbackup/O12C !cp /u02/oraredo/O12C/redo01b.rdo /u01/cbackup/O12C !cp /u01/oraredo/O12C/redo03a.rdo /u01/cbackup/O12C !cp /u02/oraredo/O12C/redo03b.rdo /u01/cbackup/O12C !cp /u01/dbfile/O12C/control01.ctl /u01/cbackup/O12C !cp /u01/dbfile/O12C/control02.ctl /u01/cbackup/O12C startup;
While you make a cold backup, you should also generate a script that provides the commands to copy data files, temp files, log files, and control files back to their original locations. You can use this script to restore from the cold backup. The next script in this section dynamically creates a coldrest.sql script that copies files from the backup location to the original data file locations. You need to modify this script in the same manner that you modified the cold backup script (i.e., change the ORACLE_SID, ORACLE_HOME, and cbdir variables to match your environment):
#!/bin/bash ORACLE_SID=O12C ORACLE_HOME=/u01/app/oracle/product/220.127.116.11/db_1 PATH=$PATH:$ORACLE_HOME/bin # sqlplus -s <<EOF / as sysdba set head off pages0 lines 132 verify off feed off trimsp on define cbdir=/u01/cbackup/O12C define dbname=$ORACLE_SID spo coldrest.sql select 'shutdown abort;' from dual; select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) || ' ' || name from v\$datafile; select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) || ' ' || name from v\$tempfile; select '!cp ' || '&&cbdir/' || substr(member, instr(member,'/',-1,1)+1) || ' ' || member from v\$logfile; select '!cp ' || '&&cbdir/' || substr(name, instr(name,'/',-1,1)+1) || ' ' || name from v\$controlfile; select 'startup;' from dual; spo off; EOF exit 0
This script creates a script, named coldrest.sql, that generates the copy commands to restore your data files, temp files, log files, and control files back to their original locations. After you run this shell script, here is a snippet of the code in the coldrest.sql file:
shutdown abort; !cp /u01/cbackup/O12C/system01.dbf /u01/dbfile/O12C/system01.dbf !cp /u01/cbackup/O12C/sysaux01.dbf /u01/dbfile/O12C/sysaux01.dbf !cp /u01/cbackup/O12C/undotbs01.dbf /u01/dbfile/O12C/undotbs01.dbf !cp /u01/cbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf !cp /u01/cbackup/O12C/tools01.dbf /u01/dbfile/O12C/tools01.dbf ... !cp /u01/cbackup/O12C/redo03b.rdo /u02/oraredo/O12C/redo03b.rdo !cp /u01/cbackup/O12C/control01.ctl /u01/dbfile/O12C/control01.ctl !cp /u01/cbackup/O12C/control02.ctl /u01/dbfile/O12C/control02.ctl startup;
If you need to restore from a cold backup using this script, log in to SQL*Plus as SYS, and execute the script:
$ sqlplus / as sysdba SQL> @coldrest.sql