As discussed previously, RMAN should be your tool of choice for any type of Oracle database 12C backup (either online or offline). RMAN is more efficient than user-managed backups and automates most tasks. Having said that, one of the best ways to gain an understanding of Oracle backup and recovery internals is to make a hot backup and then use that backup to restore and recover your database. Manually issuing the commands involved in a hot backup, followed by a restore and recovery, helps you understand the role of each type of file (control files, data files, archive redo logs, online redo logs) in a restore-and-recovery scenario.
The following sections begin by showing you how to implement a hot backup. They also provide basic scripts that you can use to automate the hot backup process. Later sections explain some of the internal mechanics of a hot backup and clarify why you must put tablespaces in backup mode before the hot backup takes place.
Making a Hot Backup
Here are the steps required for a hot backup:
- Ensure that the Oracle database 12C is in archivelog mode.
- Determine where to copy the backup files.
- Identify which files need to be backed up.
- Note the maximum sequence number of the online redo logs.
- Alter the database/tablespace into backup mode.
- Copy the data files with an OS utility to the location determined in step 2.
- Alter the database/tablespace out of backup mode.
- Archive the current online redo log, and note the maximum sequence number of the online redo logs.
- Back up the control file.
- Back up any archive redo logs generated during the backup.
These steps are covered in detail in the following sections.
SQL> archive log list;
The output shows that this Oracle database is in archivelog mode:
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oraarch/O12C
If you’re not sure how to enable archiving, see this blog for details.
SQL> select sum(bytes) from dba_data_files;
Ideally, the backup location should be on a set of disks separate from your live data files. But, in practice, many times you’re given a slice of space on a SAN and have no idea about the underlying disk layout. In these situations, you rely on redundancy’s being built into the SAN hardware (RAID disks, multiple controllers, and so on) to ensure high availability and recoverability.
SQL> select name from v$datafile;
When you get to step 5, you may want to consider altering tablespaces one at a time into backup mode. If you take that approach, you need to know which data files are associated with which: tablespace:
select tablespace_name, file_name from dba_data_files order by 1,2;
To successfully recover using a hot backup, you require, at minimum, all the archive redo logs that were generated during the backup. For this reason, you need to note the archivelog sequence before starting the hot backup:
select thread#, max(sequence#) from v$log group by thread# order by thread#;
SQL> alter database begin backup;
If it’s an active OLTP database, doing this can greatly degrade performance. This is because when a tablespace is in backup mode, Oracle copies a full image of any block (when it’s first modified) to the redo stream (see the section "Understanding the Split-Block Issue" later in this article, for more details).
The alternative is to alter only one tablespace at a time into backup mode. After the tablespace has been altered into backup mode, you can copy the associated data files (step 6) and then alter the tablespace out of backup mode (step 7). You have to do this for each tablespace:
SQL> alter tablespace <tablespace_name> begin backup;
$ cp /u01/dbfile/O12C/*.dbf /u01/hbackup/O12C
After you’re finished copying all your data files to the backup directory, you need to alter the tablespaces out of backup mode. This example alters all tablespaces out of backup mode at the same time:
SQL> alter database end backup;
If you’re altering your tablespaces into backup mode one at a time, you need to alter each tablespace out of backup mode after its data files have been copied:
SQL> alter tablespace <tablespace_name> end backup;
If you don’t take the tablespaces out of backup mode, you can seriously degrade performance and compromise the ability to recover your Oracle database 12C. You can verify that no data files have an ACTIVE status with the following query:
SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS'; SQL> select * from v$backup where status='ACTIVE';
Note Setting the NLS_DATE_FORMAT parameter appropriately will allow you to see the exact date/time when the data file was placed into backup mode. This is useful for determining the starting sequence number of the archivelog needed, in the event that the data file needs to be recovered.
SQL> alter system archive log current;
Also, note the maximum online redo log sequence number. If a failure occurs immediately after the hot backup, you need any archive redo logs generated during the hot backup to fully recover your database:
select thread#, max(sequence#) from v$log group by thread# order by thread#;
For a hot backup, you can’t use an OS copy command to make a backup of the control file. Oracle’s hot backup procedure specifies that you must use the ALTER DATABASE BACKUP CONTROLFILE statement. This example makes a backup of the control file and places it in the same location as the database backup files:
SQL> alter database backup controlfile to '/u01/hbackup/O12C/controlbk.ctl' reuse;
The REUSE clause instructs Oracle to overwrite the file if it already exists in the backup location.
Back up the archive redo logs that were generated during the hot backup. You can do this with an OS copy command:
$ cp <archive redo logs generated during backup> <backup directory>
This procedure guarantees that you have the logs, even if a failure should occur soon after the hot backup finishes. Be sure you don’t back up an archive redo log that is currently being written to by the archiver process—doing so results in an incomplete copy of that file. Sometimes, DBAs script this process by checking the maximum SEQUENCE# with the maximum RESETLOGS_ID in the V$ARCHIVED_LOG view. Oracle updates that view when it’s finished copying the archive redo log to disk. Therefore, any archive redo log file that appears in the V$ARCHIVED_LOG view should be safe to copy.
Scripting Hot Backups
The script in this section covers the minimal tasks associated with a hot backup. For a production environment a hot backup script can be quite complex. The script given here provides you with a baseline of what you should include in a hot backup script. You need to modify these variables in the script for it to work in your environment:
The ORACLE_SID OS variable defines your database name. The ORACLE_HOME OS varriable defines where you installed the Oracle software. The SQL*Plus hbdir variable points to the directory for the hot backups.
#!/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 hbdir=/u01/hbackup/O12C spo hotback.sql select 'spo &&hbdir/hotlog.txt' from dual; select 'select max(sequence#) from v\$log;' from dual; select 'alter database begin backup;' from dual; select '!cp ' || name || ' ' || '&&hbdir' from v\$datafile; select 'alter database end backup;' from dual; select 'alter database backup controlfile to ' || '''' || '&&hbdir' || '/controlbk.ctl' || '''' || ' reuse;' from dual; select 'alter system archive log current;' from dual; select 'select max(sequence#) from v\$log;' from dual; select 'select member from v\$logfile;' from dual; select 'spo off;' from dual; spo off; @@hotback.sql EOF
The script generates a hotback.sql script. This script contains the commands for performing the hot backup. Here is a listing of the hotback.sql script for a test database:
spo /u01/hbackup/O12C/hotlog.txt select max(sequence#) from v$log; alter database begin backup; !cp /u01/dbfile/O12C/system01.dbf /u01/hbackup/O12C !cp /u01/dbfile/O12C/sysaux01.dbf /u01/hbackup/O12C !cp /u01/dbfile/O12C/undotbs01.dbf /u01/hbackup/O12C !cp /u01/dbfile/O12C/users01.dbf /u01/hbackup/O12C !cp /u01/dbfile/O12C/tools01.dbf /u01/hbackup/O12C alter database end backup; alter database backup controlfile to '/u01/hbackup/O12C/controlbk.ctl' reuse; alter system archive log current; select max(sequence#) from v$log; select member from v$logfile; spo off;
You can run this script manually from SQL*Plus, like this:
Caution If the previous script fails on a statement before ALTER DATABASE END BACKUP is executed, you must take your database (tablespaces) out of backup mode by manually running ALTER DATABASE END BACKUP from SQL*Plus (as the SYS user).
While you generate the hot backup script, it’s prudent to generate a script that you can use to copy the data files from a backup directory. You have to modify the hbdir variable in this script to match the location of the hot backups for your environment. Here is a script that generates the copy commands:
#!/bin/bash ORACLE_SID=O12C ORACLE_HOME=/u01/app/oracle/product/18.104.22.168/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 hbdir=/u01/hbackup/O12C/ define dbname=$ORACLE_SID spo hotrest.sql select '!cp ' || '&&hbdir' || substr(name,instr(name,'/',-1,1)+1) || ' ' || name from v\$datafile; spo off; EOF # exit 0
For my environment here is the code generated that can be executed from SQL*Plus to copy the data files back from the backup directory, if a failure should occur:
!cp /u01/hbackup/O12C/system01.dbf /u01/dbfile/O12C/system01.dbf !cp /u01/hbackup/O12C/sysaux01.dbf /u01/dbfile/O12C/sysaux01.dbf !cp /u01/hbackup/O12C/undotbs01.dbf /u01/dbfile/O12C/undotbs01.dbf !cp /u01/hbackup/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf !cp /u01/hbackup/O12C/tools01.dbf /u01/dbfile/O12C/tools01.dbf
In this output, you can remove the exclamation point (!) from each line if you prefer to run the commands from the OS prompt. The main idea is that these commands are available in the event of a failure, so you know which files have been backed up to which location and how to copy them back.
Tip Don’t use user-managed hot backup technology for online backups; use RMAN. RMAN doesn’t need to place tablespaces in backup mode and automates nearly everything related to backup and recovery.
To perform a hot backup, one critical step is to alter a tablespace into backup mode before you copy any of the data files associated with the tablespace, using an OS utility. To understand why you have to alter a tablespace into backup mode, you must be familiar with what is sometimes called the split- (or fractured-) block issue.
Recall that the size of a database block is often different from that of an OS block. For instance, a database block may be sized at 8KB, whereas the OS block size is 4KB. As part of the hot backup, you use an OS utility to copy the live data files. While the OS utility is copying the data files, the possibility exists that database writers are writing to a block simultaneously. Because the Oracle block and the OS block are different sizes, the following may happen:
- The OS utility copies part of the Oracle block.
- A moment later, a database writer updates the entire block.
- A split second later, the OS utility copies the latter half of the Oracle block.
This can result in the OS copy of the block’s being inconsistent with what Oracle wrote to the OS. Figure 1 illustrates this concept.
Looking at Figure 1, the block copied to disk at time 3 is corrupt, as far as Oracle is concerned. The first half of the block is from time 1, and the latter half is copied at time 3. When you make a hot backup, you’re guaranteeing block-level corruption in the backups of the data files.
To understand how Oracle resolves the split-block issue, first consider a database operating in its normal mode (not in backup mode). The redo information that is written to the online redo logs is only what Oracle needs, to reapply transactions. The redo stream doesn’t contain entire blocks of data. Oracle only records a change vector in the redo stream that specifies which block changed and how it was changed. Figure 2 shows Oracle operating under normal conditions.
Now, consider what happens during a hot backup. For a hot backup, before you copy the data files associated with a tablespace, you must first alter the tablespace into backup mode. While in this mode, before Oracle modifies a block, the entire block is copied to the redo stream. Any subsequent changes to the block only require that the normal redo-change vectors be written to the redo stream. This is illustrated in Figure 3.
To understand why Oracle logs the entire block to the redo stream, consider what happens during a restore and recovery. First, the backup files from the hot backup are restored. As explained earlier, these backup files contain corrupt blocks, owing to the split-block issue. But, it doesn’t matter, because once Oracle recovers the data files, for any block that was modified during the hot backup, Oracle has an image copy of the block as it was before it was modified. Oracle uses the copy of the block it has in the redo stream as a starting point for the recovery (of that block). This process is illustrated in Figure 3-4.
In this way, it doesn’t matter if there are corrupt blocks in the hot backup files. Oracle always starts the recovery process for a block from a copy of the block (as it was before it was modified) in the redo stream.
Understanding the Need for Redo Generated During Backup
What happens if you experience a failure soon after you make a hot backup? Oracle knows when a tablespace was put in backup mode (begin backup system SCN written to the redo stream), and Oracle knows when the tablespace was taken out of backup mode (end-of-backup marker written to the redo stream). Oracle requires every archive redo log generated during that time frame to successfully recover the data files.
Figure 5 shows that, at minimum, the archive redo logs from sequence numbers 100 to 102 are required to recover the tablespace. These archive redo logs were generated during the hot backup.
If you attempt to stop the recovery process before all redo between the begin and end markers has been applied to the data file, Oracle throws this error:
ORA-01195: online backup of file 1 needs more recovery to be consistent
All redo generated during the hot backup of a tablespace must be applied to the data files before they can be opened. Oracle, at a minimum, needs to apply everything between the begin-backup SCN marker and the end-backup marker, to account for every block modified while the tablespace was in backup mode. This redo is in the archive redo log files; or, if the failure happened right after the backup ended, some of the redo may not have been archived and may be in the online redo logs. Therefore, you have to instruct Oracle to apply what’s in the online redo logs.
Understanding That Data Files Are Updated
Note that, in Figures 2 and 3, the behavior of the database writer is, for the most part, unchanged throughout the backup procedure. The database writer continues to write blocks to data files, regardless of the backup mode of the database. The database writer doesn’t care if a hot backup is taking place; its job is to write blocks from the buffer cache to the data files.
Every once in a while, you run into a DBA who states that the database writer doesn’t write to data files during user-managed hot backups. This is a widespread misconception. Use some common sense: if the database writer isn’t writing to the data files during a hot backup, then where are the changes being written? If the transactions are being written to somewhere other than the data files, how would those data files be resynchronized after the backup? It doesn’t make any sense.
Some DBAs say, “The data file header is frozen, which means no changes to the data file.” Oracle does freeze the SCN to indicate the start of the hot backup in the data file header and doesn’t update that SCN until the tablespace is taken out of backup mode. This “frozen SCN” doesn’t mean that blocks aren’t being written to data files during the backup. You can easily demonstrate that a data file is written to during backup mode by doing this:
- Put a tablespace in backup mode:
SQL> alter tablespace users begin backup;
- Create a table that has a character field:
SQL> create table cc(cc varchar2(20)) tablespace users;
- Insert a string into that table:
SQL> insert into cc values('DBWR does write');
- Force a checkpoint (which ensures that all modified buffers are written to disk):
SQL> alter system checkpoint;
- From the OS, use the strings and grep commands to search for the string in the data file:
$ strings /u01/dbfile/O12C/users01.dbf | grep "DBWR does write"
- Here is the output, proving that the Oracle database writer did write the data to disk:
DBWR does write
- Don’t forget to take the tablespace out of backup mode:
SQL> alter tablespace users end backup;