CHAPTER 12
Backups
As to our Conduct in the Affair of Extinguishing Fires, tho’ we do not want Hands or Good-Will, yet we seem to want Order and Method, and therefore I believe I cannot do better than to offer for our Imitation, the Example of a City in a Neighbouring Province. There is, as I am well inform’d, a Club or Society of active Men belonging to each Fire Engine; whose Business is to attend all Fires with it whenever they happen; and to work it once a Quarter, and see it kept in order.
—Benjamin Franklin, in an anonymous letter to the Pennsylvania Gazette (of which he was the editor) following a disastrous fire in Philadelphia in the eighteenth century
American national hero Benjamin Franklin often wrote anonymous letters to the Pennsylvania Gazette, a prominent newspaper that he owned and edited. In one such letter, he coined the famous phrase “an ounce of prevention is worth a pound of cure”; and, in addition to making several suggestions for the prevention of fires, he proposed that Philadelphia imitate his native Boston in establishing fire stations and employing firefighters. Not only should all efforts be made to prevent fires, but the city should be adequately prepared to handle the next inevitable fire.
Backups are to a database what fire stations and fire fighters are to a city. You may protect the database against damage the best you can, but you must be prepared if the database is ever damaged, through user or operator error or hardware failure, and needs to be repaired. A backup is a snapshot of a database or a part of a database; if the database is damaged, the damaged parts can be repaired using the backups. Archived logs can be used in conjunction with a backup to replay transactions that changed data after the backup was performed. This chapter describes how to create various kinds of backups; the next chapter describes how to use them to repair databases. If you are impatient, note that you can create a backup with two simple words: BACKUP DATABASE. But as the leading mind of the European renaissance, Leonardo da Vinci, said: “Those who are in love with practice without knowledge are like the sailor who gets into a ship without rudder or compass and who never can be certain whether he is going. Practice must always be founded on sound theory.”
Why Do You Need Backups?
Oracle guru Tom Kyte likes to say that “Why is probably the right answer.” “Why do you need backups?” is a wise question because consideration of the answer will dictate your backup strategy; it might even determine whether you need backups at all. Backups consume resources: disk space, tapes, CPU cycles, IO bandwidth, network bandwidth, tape-drive bandwidth, operator time, and so on. None of those are free. The necessary hardware and software are not free, either. Consider, for example, the following scenarios:
- A new version of the application is to be deployed, and many objects in the database will be modified. The developers have requested that a backup be created just before the new version is deployed. However, you might be able to recover the database using previous backups and archived redo logs, and a new backup may not be necessary. Also, you can use Oracle flashback technology to recover the database without using backups. If the database is very large but only a small portion will be affected by the new deployment, an alternative might be to duplicate the tables that will be affected, using CREATE TABLE AS commands—the original data can be restored from the duplicate tables if the deployment is unsuccessful.
- Consider a reporting database that contains only materialized views1 that reflect the data in other databases. If the materialized views become damaged, they can be completely refreshed using the original data. The entire database can be re-created from scripts. In this scenario, you do not need good backups; you just need good scripts.
- I once managed a database named Flatline that was used to store archived data that had been deleted from the transactional databases but needed to be retained for an extended period for legal reasons. New data was loaded into the database only once a month. Obviously, it needed backups only once a month.
- Oracle provides read-only tablespaces for archived data. These don’t need as many backups as the rest of the database.
- Development and testing databases may not need backups if they can be cloned from production databases or rebuilt using scripts and seed data.
- Standby databases are databases that are continuously synchronizing themselves with the primary database. They have many uses: they can be used for reporting purposes; they provide switchover capability in the event of a planned outage of the primary database; they provide failover capability in the case of an unplanned ouage; and they can be backed up in lieu of backing up the production database so as to offload the CPU and I/O overhead from the production database to the standby database. Standby databases don’t need to be backed up unless they are being backed up in lieu of backing up the production database.
An appropriate backup strategy is one that meets the needs of the business while remaining cost-effective. A different strategy might be needed for each database in the enterprise, the goal being to “provide cost-effective stewardship of the IT assets and resources used in providing IT services.”2 Mission-critical databases such as those used in e-commerce have the most demanding requirements. The requirement for a mission-critical e-commerce database might be to “make backups without impacting database performance and quickly recover from failures.” Such a requirement typically dictates the use of advanced hardware and software, with all the associated cost. For example, advanced options such as parallel backup, parallel recovery, single-block recovery, and database flashback are available only with the Enterprise Edition of Oracle Database.
Types of Backup
It bears repeating that an appropriate backup strategy is one that meets the needs of the business while remaining cost-effective. You have many choices to make; I describe some of them here.
Tape Backups vs. Disk Backups
The two main storage choices are tape and disk. Each choice has advantages and disadvantages. It is generally accepted that disk backups are not a substitute for tape backups. A common practice is to make backup copies on disk when possible and have them copied to tape in a separate operation.
Advantages and Disadvantages of Tape Backups
On the plus side, tapes are relatively cheap compared to disks, which means multiple backups can be retained without too much expense; multiple backups obviously offer extra protection. Tapes are also more reliable than disks; disks are electromechanical devices and therefore more prone to failure. Also, tapes are serial-access devices and can achieve very high sustained reading and writing speeds.
On the minus side, the process of retrieving a single file from tapes is slower than with disks because the serial nature of tapes requires that the entire preceding portions be read first. Also, tapes usually are not kept online; they are typically ejected from the tape drive and may even be sent offsite for storage in secure fire-proof facilities—for this reason, they may not be readily available in an emergency. A DBA working remotely might not be able to initiate database recovery without the assistance of “remote hands” to perform actions like inserting tapes into a tape drive. Also, tape backup management is itself a specialized IT activity; the DBA may need assistance from IT personnel with the required knowledge.
In general, tapes are faster in serial-access situations, such as copying large numbers of files to a blank tape. They are slower in random-access situations, such as retrieving one file from tape.
Advantages and Disadvantages of Disk Backups
On the plus side, backup and recovery can be initiated by the DBA without “remote hands” assistance and without requiring specialized knowledge of the tape backup technology that is being used.
On the minus side, disks are relatively expensive compared to tape, which means the available capacity is usually much more limited and there may not be enough space available to create a backup copy of the database, let alone multiple backups. Another minus is that disks—unlike tapes—cannot be sent offsite for safekeeping.
Full Backups vs. Partial Backups
Backups require the use of computer resources such as storage disks and storage tapes as well as memory, CPU cycles, and network bandwidth. They must therefore be scheduled so as not to interfere with normal database operations; typically, they are scheduled for nights and weekends. If the database is very large, you may not have the time and resources to create a backup of the entire database in a single operation; instead, you might spread the activity over the course of a week and create a backup of just a portion of the database every night. Also, some portions of the database may be designated as read-only, and it is not necessary to create multiple backups of this data.
Level 0 Backups vs. Level 1 Backups
A full backup is also called a level 0 backup. This is a backup containing every data block. A level 1 backup is one that contains only those data blocks that have changed since the level 0 backup. A level 1 backup is also called an incremental backup. A level 0 backup might be created on weekends when there is plenty of time available, and a level 1 backup might be created every night when there is less time available. A feature called block change tracking can be used to avoid having to examine every block; the changed blocks are listed in the block change tracking file.
Physical Backups vs. Logical Backups
The term physical backup refers to exact copies of data blocks and data files produced by a tool such as Recovery Manager (RMAN). The term logical backup refers to a structured copy of the data in the tables such as is produced by Data Pump Export or a CREATE TABLE AS SELECT command.
A logical backup can be much smaller than the corresponding physical backup because there is typically much unused space in the data blocks and the data files and because the database contains index data in addition to table data. However, logical backups cannot be used to restore the database; they can only be used to re-create the data in an otherwise functional database.
If the database (or part of it) is restored from physical backups, then the redo logs can be used to recover all modifications to the data made since the physical backup was initiated. If a logical backup is used to re-create data, any modifications to the data made after the logical backup was initiated are lost.
Consistent Backups vs. Inconsistent Backups
Many databases are used around the clock. If the data in the database is being modified while the backup is being created, the backup may contain internal consistencies because each data block in the database is visited just once during a backup operation, and any subsequent changes to the block will not be captured. The only way to guarantee a consistent backup is to make the database unavailable and prevent changes during the backup operation. However, inconsistent physical backups are very useful because the information contained in the redo log files can be used to fix any inconsistencies in such backups.
Hot vs. Cold Backups
Hot backups (also called online backups) are backups that are created while the database (or relevant portion thereof) is accessible by users and can be modified while the backup is underway. Cold backups (also called offline backups) are backups that are created while the database (or relevant portion thereof) is in a state in which it cannot be modified while the backup is underway.
Hot backup and online backup are generally considered synonymous with inconsistent backup, whereas cold backup and offline backup are considered synonymous with consistent backup. However, if the database was not shut down gracefully, a cold backup also may contain inconsistencies. And it is possible to make a consistent logical backup of the database while the database is online. Finally note that an online backup of a portion of the database that cannot be modified (for example, a read-only tablespace) is guaranteed to be consistent.
Oracle-Managed Backups vs. User-Managed Backups
Backups created using RMAN are called Oracle-managed backups, and backups created by other methods are called user-managed backups. For example, Network Appliance provides an advanced technology that can be used to create snapshots of the largest databases in seconds or minutes by recording only the addresses of the blocks in the database instead of the data contained in the blocks—a copy of a block is made only if the data in one of these blocks is subsequently changed.
Advantages of Oracle-Managed Backups
Backups created using RMAN have many advantages:
- Arguably, the biggest advantage of RMAN is its ease of use. The entire database can be backed up with the simple words BACKUP DATABASE.
- Another great advantage of RMAN is that it stores history data. The history data is needed during recovery operations, but it can be queried by the DBA at any time; a typical use is to verify that the database has been backed up.
- RMAN offers features that are not available anywhere else. Examples are incremental backups, detection of corrupted blocks, and recovery of single blocks.
Advantages of User-Managed Backups
Some types of user-managed backups offer great advantages. For example, snapshot technology has the advantage of lightning speed of both backup and recovery. Making a backup of a file is lightning fast because it only requires that the addresses of blocks be recorded, not the data contained in the blocks—recovery of a file is also lightning fast because it only requires that one list of block addresses be switched with another. The best of both worlds is achieved when snapshots are registered in the RMAN repository—this allows RMAN to use the snapshots for database recovery.
Practical Demonstration: Physical Backups
Here is a practical demonstration of the use of RMAN to create a backup copy of the database. As you will see, this can be accomplished with a few short commands. Please follow along in your VM; you will learn best by doing. For this exercise, restore your VM to the snapshot that you took prior to installing a second ORACLE_HOME in Chapter 5; you won’t use it in this and future chapters. However, you can always restore your VM to the snapshot containing the second ORACLE_HOME if you so choose.
Hot backups are desirable because the database remains available during the backup operation. But at this point, RMAN cannot perform a hot backup because the database is in NOARCHIVELOG mode. Remember that in ARCHIVELOG mode, archive copies of the online redo logs are made before they are allowed to be overwritten. If the database ever needs to be restored from a backup, you will need the archive copies of any redo logs that were active during the time of the backup. Let’s go ahead and put the database into ARCHIVELOG mode.
First, unset the TWO_TASK variable that tries to connect you to the pdb1 pluggable database. Then connect to the CDB using the SYS account with SYSDBA privilege.
[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 3 20:58:16 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Shut down the CDB, and then reopen it in MOUNT mode.
CDB$ROOT@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
CDB$ROOT@ORCL> startup mount;
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2289016 bytes
Variable Size 335544968 bytes
Database Buffers 71303168 bytes
Redo Buffers 4235264 bytes
Database mounted.
You can now switch the database into ARCHIVELOG mode. As a separate step, you also need to start the archive process; it is the process responsible for automatically making archive copies of the online redo logs as they fill up. Finally, you need to ensure that the archiver is automatically started whenever the database is started. This involves setting the LOG_ARCHIVE_START database-initialization parameter to TRUE:
CDB$ROOT@ORCL> alter database archivelog;
Database altered.
CDB$ROOT@ORCL> alter system archive log start;
System altered.
CDB$ROOT@ORCL> alter system set log_archive_start=true scope=spfile;
System altered.
Next, open the database and test your work by manually closing an online redo log and checking that an archive copy is automatically made by the archiver process:
CDB$ROOT@ORCL> alter database open;
Database altered.
CDB$ROOT@ORCL> alter system switch logfile;
System altered.
CDB$ROOT@ORCL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_11_03/o1_mf_1_129_b5jgj5
m7_.arc
CDB$ROOT@ORCL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
You are now ready to use RMAN to perform an online backup. Let’s change the value of the NLS_DATE_FORMAT environment variable—it affects the format in which dates are displayed. The default setting does not include minutes and seconds values, which you would like to have during this session. This is an optional step—display settings have no impact on the integrity of backups:
[oracle@localhost ~]$ export NLS_DATE_FORMAT=’YYYY/MM/DD HH24:MI’
When you invoke RMAN, it displays version information and gives you a command prompt:
[oracle@localhost ~]$ rman
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Nov 3 09:25:22 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN>
Next you establish a command session with the database using the command CONNECT TARGET. RMAN displays the database name (ORCL in this case) and the DBID, a unique numeric identifier used to distinguish between databases with the same name:
RMAN> connect target
connected to target database: ORCL (DBID=1365223133)
All that remains is to instruct RMAN to make a backup copy of the database. The command is as simple as backup database. RMAN first tells you that information about the backup will be stored in the control file of the database instead of a separate recovery catalog, which is a separate Oracle database dedicated to storing information about database backups:
RMAN> backup database;
Starting backup at 2014/11/03 21:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
RMAN then makes a backup of the files in the CDB:
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf
channel ORA_DISK_1: starting piece 1 at 2014/11/03 21:31
channel ORA_DISK_1: finished piece 1 at 2014/11/03 21:37
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jgncy1_.bkp tag=TAG20141103T213136 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:06:11
RMAN then makes a backup of the files in the pdb1 database:
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_sysaux_9fxvnjdl_.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_system_9fxvnjdq_.dbf
input datafile file number=00014 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_apex_226_9gfgd96o_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_users_9fxvoh6n_.dbf
channel ORA_DISK_1: starting piece 1 at 2014/11/03 21:37
channel ORA_DISK_1: finished piece 1 at 2014/11/03 21:40
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/F0832BAF14721281E045000000000001/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jgzymm_.bkp tag=TAG20141103T213136 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
There is still another database left to process. This is the seed database, which is used only to create a new pluggable database:
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxn22p3_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxn22po_.dbf
channel ORA_DISK_1: starting piece 1 at 2014/11/03 21:40
channel ORA_DISK_1: finished piece 1 at 2014/11/03 21:41
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/F081641BB43F0F7DE045000000000001/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jh49xl_.bkp tag=TAG20141103T213136 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 2014/11/03 21:41
Finally, RMAN backs up up the control file and the spfile:
Starting Control File and SPFILE Autobackup at 2014/11/03 21:41
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_11_03/o1_mf_s_862695669_b5jh693f_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2014/11/03 21:41
You can ask RMAN to summarize the results of the backup operation with the LIST BACKUP command. Three backup sets correspond to the three databases that were processed—the CDB, the pluggable database pdb1, and the seed database:
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ----------------
1 Full 1.47G DISK 00:06:06 2014/11/03 21:37
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141103T213136
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jgncy1_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------------- ----
1 Full 3470428 2014/11/03 21:31 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
3 Full 3470428 2014/11/03 21:31 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
4 Full 3470428 2014/11/03 21:31 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
6 Full 3470428 2014/11/03 21:31 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ----------------
2 Full 813.77M DISK 00:02:12 2014/11/03 21:40
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141103T213136
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/F0832BAF14721281E045000000000001/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jgzymm_.bkp
List of Datafiles in backup set 2
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------------- ----
11 Full 3471051 2014/11/03 21:37 /u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_system_9fxvnjdq_.dbf
12 Full 3471051 2014/11/03 21:37 /u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_sysaux_9fxvnjdl_.dbf
13 Full 3471051 2014/11/03 21:37 /u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_users_9fxvoh6n_.dbf
14 Full 3471051 2014/11/03 21:37 /u01/app/oracle/oradata/ORCL/F0832BAF14721281E045000000000001/datafile/o1_mf_apex_226_9gfgd96o_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ----------------
3 Full 553.52M DISK 00:00:54 2014/11/03 21:41
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141103T213136
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/F081641BB43F0F7DE045000000000001/backupset/2014_11_03/o1_mf_nnndf_TAG20141103T213136_b5jh49xl_.bkp
List of Datafiles in backup set 3
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------------- ----
5 Full 2654251 2014/01/27 15:35 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxn22po_.dbf
7 Full 2654251 2014/01/27 15:35 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxn22p3_.dbf
RMAN> exit;
Recovery Manager complete.
Practical Demonstration: Logical Backups
The Data Pump Export tool is very powerful and flexible. It has features such as parallel unloading and the ability to select precise subsets of data.
First you have to create a directory object and map it to a directory on disk. This allows the Data Pump PL/SQL routines to write to disk:
[oracle@localhost ~]$ sqlplus sys/oracle@pdb1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Nov 12 19:59:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
PDB1@ORCL> create directory PDB1_DATA_PUMP_DIR as ’/u01/app/oracle/admin/orcl/dpdump/’;
Directory created.
PDB1@ORCL> grant all on directory PDB1_DATA_PUMP_DIR to system;
Grant succeeded.
PDB1@ORCL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Let’s export the data from the HR schema, one of the sample schemas included in your VM. Notice the use of the flashback_time qualifier, which ensures that any changes made to the data while the export is in progress are not copied:
[oracle@localhost ~]$ expdp userid=system/oracle@pdb1 schemas=hr directory=pdb1_data_pump_dir flashback_time=sysdate
Export: Release 12.1.0.1.0 - Production on Thu Nov 13 11:11:43 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": userid=system/********@pdb1 schemas=hr directory=pdb1_data_pump_dir flashback_time=sysdate
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "HR"."COUNTRIES" 6.437 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows
. . exported "HR"."EMPLOYEES" 17.05 KB 107 rows
. . exported "HR"."JOBS" 7.078 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows
. . exported "HR"."LOCATIONS" 8.414 KB 23 rows
. . exported "HR"."REGIONS" 5.523 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/orcl/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Nov 13 11:23:12 2014 elapsed 0 00:10:19
On checking the size of the dump file, you find that it is significantly smaller than the physical backup you performed earlier:
[oracle@localhost ~]$ ls -l /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
-rw-r-----. 1 oracle oracle 573440 Nov 13 11:23 /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
You can obtain a concise list of Data Pump Export options using the command expdp help=y. Some useful options are COMPRESSION, CONTENT, DIRECTORY, DUMPFILE, FILESIZE, FLASHBACK_TIME, FULL, PARALLEL, PARFILE, SCHEMAS, TABLES, and TABLESPACES.
Common RMAN Commands
The RMAN commands that you will most frequently encounter (in addition to BACKUP) are LIST, REPORT, CROSSCHECK, DELETE, SHOW, and CONFIGURE; Table 12-1 provides some examples. For a complete description of these and other commands, please refer to the Oracle Database 12c reference manuals.
Table 12-1. Common RMAN Commands
Command | Purpose |
|---|---|
LIST BACKUP OF DATABASE SUMMARY | Produces a summary of all backups recorded in the control file (and recovery catalog if one is being used). |
LIST BACKUP OF DATABASE COMPLETED AFTER ’SYSDATE - 1’ | Produces a detailed listing of all backups completed over the last 24 hours. |
CROSSCHECK BACKUP | Checks whether all the pieces of the backups recorded in the control file are still on disk and have not been inadvertently removed. Backup pieces that can no longer be located are designated as expired. |
DELETE EXPIRED BACKUP | Removes information about a backup from the control file (or recovery catalog if one is being used) if the backup pieces cannot be found on disk. |
REPORT OBSOLETE | Produces a list of backups that are older than the retention policy that is in effect. You can use the SHOW RETENTION POLICY command to determine which retention policy is in effect. |
DELETE FORCE NOPROMPT OBSOLETE | Removes information about a backup from the control file (or recovery catalog if one is being used) if the backup is older than the retention policy in effect. The backup pieces are also removed from disk. The FORCE qualifier instructs RMAN not to complain about missing backup pieces. The NOPROMPT qualifier instructs RMAN not to prompt for confirmation before beginning the delete operation. |
DELETE ARCHIVELOG ALL COMPLETED BEFORE ’SYSDATE - 7’ | Deletes archived redo logs older than seven days. |
SHOW ALL | Lists all the RMAN configuration parameters. |
CONFIGURE CONTROLFILE AUTOBACKUP ON | Instructs Oracle to make a backup copy of the control file automatically whenever a new data file is added to the database. This is the default mode of behavior; I have included this command only to illustrate how to change backup options. |
Listing 12-1 demonstrates the use of these commands. You put all the commands into a command file called rman.rcv. You also set the NLS_DATE_FORMAT environment variable to force RMAN to print the time of each operation, not just the date.
Horror Stories
All of the following stories are true and are based on personal experience—mine.
A deployment of a new version of a business application was unsuccessful, and the DBA was asked to undo the changes made to the database by recovering the database from the previous night’s backups and the redo logs. He discovered that the backup script that was scheduled to run every night had been failing for three months; its defect was that it did not send an alert to the DBA when it failed in its task. Luckily the redo logs were being copied to a file server and none of the archived redo logs had been deleted. The DBA restored the database from the last good backup and started applying the redo logs to recover the missing transactions. Even the tiniest damage to any of the archived redo logs would have abruptly terminated the recovery operation, but, luckily, none of the redo logs were damaged.
Tip Implement a script that checks whether the database has been successfully backed up. This script should be separate from the backup script. Also consider daily and weekly reports listing backup successes and failures for all databases in the enterprise—such reports are also useful for tracking how data volumes and backup times are growing.
A new DBA was creating a database and was told by a system administrator that the system administration team handled all aspects of backups. The DBA took him at his word, but, unfortunately, this particular server had been built using a new standard: the database administration team was responsible for backups, not the system administration team. This mission-critical database was used for a whole year before and then decommissioned. It had never been backed up.
Tip Backups should be tested periodically. A recovery test should be conducted before a database is put to use.
A DBA had disabled an automated backup script one night because it would conflict with a deployment of a new version of a business application. The deployment failed and the database in question was successfully restored to its previous state using the previous night’s backup and redo logs. However, the DBA forgot to re-enable the automated backup script. One month later, there was another application deployment, which also failed. This time, the database could not be restored, and the data had to be manually re-created.
It happened to me—it could happen to you!
Summary
Here is a short summary of the concepts touched on in this chapter:
- A backup is a snapshot of a database or a part of a database; if the database is damaged, the damaged parts can be repaired using the backups. Archived logs can be used in conjunction with backups to replay transactions that changed data after the backup was performed.
- An appropriate backup strategy is one that meets the needs of the business while remaining cost-effective.
- A common practice is to make backup copies on disk when possible and have them copied to tape in a separate operation.
- Backups require a lot of computer resources and are therefore typically scheduled at nights and on weekends when other activity is at a minimum. If the database is very large, you may not have the time and resources to create a backup of the entire database in a single operation.
- Incremental backups can be used to conserve computer resources. You copy only those data blocks which have changed since the last backup.
- Backups that are performed while the database is being used may contain inconsistencies. The information in the redo log files can be used to eliminate the inconsistencies.
- Physical backups are copies of whole database blocks. Logical backups are copies of the data records themselves.
- Recovery Manager (RMAN) is usually the tool of choice for creating physical backups. Data Pump Export can be used for creating logical backups.
Exercises
- Automate RMAN backups using the Oracle-suggested backup strategy in Chapter 9 of Oracle Database 2-Day DBA.
Footnotes
1A materialized view is an SQL statement that has been precomputed, or materialized. These views are typically used to pre-join tables in the interest of computational efficiency. Their contents typically are not as accurate as the contents of the tables referenced in the SQL expression, but they are useful in situations such as month-end reporting where the most recent data is not required. Another typical use is to store a local copy of data from a remote database.
2This is the goal of financial management, as stated in the IT Service Management (ITSM) literature. Chapter 15 discusses ITSM.