Oracle RMAN for Absolute Beginners is a gentle introduction to the use of Oracle's Recovery Manager software to make backups of an Oracle database, and to. Implementing a Cold-Backup Strategy for a Noarchivelog Mode Database. Making a Cold Backup of a Noarchivelog Mode Database. Restoring a Cold. THIS IS A SCAN COPY of The Book in PDF Form! DIGITAL version of the Oracle RMAN Pocket Reference by Scott Schulze Paperback Book The Fast Free .
|Language:||English, Spanish, Arabic|
|ePub File Size:||29.69 MB|
|PDF File Size:||20.27 MB|
|Distribution:||Free* [*Regsitration Required]|
The first € price and the £ and $ price are net prices, subject to local VAT. Prices indicated with * include VAT for books; the €(D) includes 7% for. Germany, the. Oracle RMAN for Absolute Beginners. Richardeson Maia. caite.info For your convenience Apress has placed some of the front matter material after the. Book: Pro caite.info SignalR: Real-Time Communication caite.info with SignalR ISBN: Publisher: Apress. Year:
However, they are not as desired for my use, so the first thing to do is look at the default settings, and change them. Francisco Munoz. The archive redo logs consume disk space. For example, if new storage is added to the database server, you may want to move an existing control file to the newly available location. Enabling Default Table Compression within a Tablespace When working with large databases, you may want to consider compressing the data.
Beginning Oracle Database 12c Administration: Customers who viewed this item also viewed. Page 1 of 1 Start over Page 1 of 1. From Novice to Professional. Robert G. Oracle 12c For Dummies.
Chris Ruel. About the Author Darl Kuhn is a senior database administrator working for Oracle. Read more. Product details Series: For Absolute Beginners Paperback: Apress; 1st ed. English ISBN Don't have a Kindle? Try the Kindle edition and experience these great reading features: Share your thoughts with other customers. Write a customer review.
Top Reviews Most recent Top Reviews. There was a problem filtering reviews right now. Please try again later. Kindle Edition Verified Purchase. Basic but a good reference to have handy. I'm an RMAN rookie so this book was perfect for my needs. The code examples and explanations are clear.
There is some humor in this book, so that makes it fun to read. The best line concerns a diagram. The legend reads: DBA, Appears somewhat short and bald in the diagram, which isn't far from the truth in my case. Kindle Edition. Great book. Simple and direct. Congrats to the writer. One person found this helpful. See all 3 reviews. Amazon Giveaway allows you to run promotional giveaways in order to create buzz, reward your audience, and attract new followers and customers.
Learn more about Amazon Giveaway. This item: Set up a giveaway. Customers who bought this item also bought. Francisco Munoz. Pages with related products. See and discover other items: There's a problem loading this menu right now.
Learn more about Amazon Prime. You can parameterize all aspects of the script, including data file mount points and extent sizes. This lets you avoid hard-coding a specific size in the script and instead provide the sizes at runtime.
To accomplish this, first define at the top of the script the ampersand variables to accept the values being passed in: The following example executes a script named cretbsp. For an application, separate table data from index Table and index data may have different storage data in different tablespaces. Create tablespaces as locally managed. You This provides better performance and manageability. Try to minimize the number of data files associated You have fewer data files to manage. In tablespace CREATE scripts, use ampersand This makes scripts more reusable among various variables to define aspects such as storage environments.
First, set the LONG variable to a large value: See Chapter 8 for details. Renaming a Tablespace Sometimes, you need to rename a tablespace. You may want to do this because a tablespace was initially erroneously named, or you may want the tablespace name to better conform to your database naming standards.
Controlling the Generation of Redo For some types of applications, you may know beforehand that you can easily re-create the data. In these scenarios you can turn off the generation of redo for direct path loading. MOVE 43 www. To enforce that no objects in a tablespace can be modified, you can alter the tablespace to be read-only. You should be able to restore the data files from a read-only tablespace no matter how long ago the backup was made. If you need to modify the tablespace out of read-only mode, you do so as follows: Be aware that in Oracle 11g and above, you can modify individual tables to be read-only.
By doing so, you can better determine if an application is using any objects in the tablespace. If you attempt to query a table in an offline tablespace, you receive this error: You must perform some sort of recovery to get the tablespace and its objects back.
Needless to say, be very careful when dropping a tablespace. If there are no objects in the tablespace, resize the Reducing the size of the data files to a miniscule amount of associated data files to a very small number, such as space quickly shows whether any applications are trying to 10MB. Make a backup of your database before dropping a This ensures that you have a way to recover objects that tablespace.
Take the tablespace and data files offline before you This helps determine if any applications or users are using drop the tablespace. You control OMF by setting the following initialization parameters: You can also enable OMF after your database has been created. Oracle uses the values of the initialization parameters for the locations of any newly added files.
Oracle also determines the name of the newly added file. The advantage of using OMF is that creating tablespaces is simplified. Keep in mind that you can override the default size of MB by specifying a size: If you want to add data files to a different directory, you can alter the location dynamically: Creating a Bigfile Tablespace The bigfile feature allows you to create a tablespace with a very large data file assigned to it.
The advantage of using the bigfile feature is this potential to create very large files. With an 8KB block size, you can create a data file as large as 32TB. With a 32KB block size, you can create a data file up to TB. A bigfile tablespace allows only one data file to be associated with it. You could potentially create a tablespace, not knowing it was a bigfile tablespace because you forgot it was the default or because 47 www.
Enabling Default Table Compression within a Tablespace When working with large databases, you may want to consider compressing the data. Queries reading compressed data potentially execute faster because fewer blocks are required to satisfy the result of the query. But, compression does have a cost; it requires more CPU resources, as the data are compressed and uncompressed while reading and writing.
When creating a tablespace, you can enable data compression features. Rather, any tables you create within the tablespace inherit the compression characteristics of the tablespace. You can verify the compression characteristics of a tablespace via this query: For a regular database nonpluggable , you can use the regular DBA-level views to determine space usage. The following script displays the percentage of free space left in a tablespace and data file: After you determine that a tablespace needs more space, you need to either increase the size of a data file or add a data file to the tablespace.
If the information provided isn't enough, then Google is a good second option. This example resizes the data file to 1GB: This works because only one data file can be associated with a bigfile tablespace: Increasing the size of an existing data file allows you to add space to a tablespace without adding more data files. This ensures that all modified blocks in memory that are associated with the tablespace are flushed and written to the data files.
If you attempt to take a tablespace offline while the database is mounted but not open , you receive the following error: In this scenario, Oracle initiates a checkpoint on all data files associated with the tablespace that are online. Your database must be in archivelog mode in this situation, or the following error is thrown: You must perform media recovery on the tablespace before bringing it back online.
You can specify the entire file name or provide the file number. In this example, data file 4 is taken offline: This means you need to perform media recovery on the data file before bringing it online. You can specify either the entire file name or the file number: Toggling Online?
Renaming or Relocating a Data File You may occasionally need to move or rename a data file. For example, you may need to move data files because of changes in the storage devices or because the files were created in the wrong location or with a nonstandard name. As of Oracle 12c, you have the option of renaming or moving data files, or both, while they are online.
Otherwise, you will have to take data files offline for maintenance operations. This command allows you to rename or move data files without any downtime. This once manually intensive and error-prone operation has now been simplified to a single SQL command. A data file must be online for the online move or rename to work. Here is an example of renaming an online data file: Performing Offline Data File Operations If you are using Oracle 11g or lower, before you rename or move a data file, you must take the data file offline.
There are two somewhat different approaches to moving and renaming offline data files: These two techniques are discussed in the next two sections. Use the following query to determine the names of existing data files: TO statement to update the control file with the new data file name.
Alter the data file online. The following example renames several data files in the database. The steps for this operation are as follows: Modify the trace file to display the new location of the data files.
Shut down the database. Physically move the data files, using an OS command. Start the database in nomount mode. The following example walks through the previous steps.
First, a file named mv. Normally, you know whether you want to reset the online redo logs as part of re-creating the control file. Control files are small binary files that contain information about the structure of the database. Any control files specified in the parameter file must be available in order for you to mount the database.
If a control file becomes unavailable, then your database will cease operating until you resolve the issue. I highly recommend that you configure your database with at least three control files. If one control file becomes unavailable, you can replace it with a copy of a good existing control file. If you have multiple instances connected to one database, then each instance generates its own redo thread. Each database must be created with two or more online redo log groups.
However, I highly recommend that you create your online redo log groups with two members in each group. If an online redo log has at least one member that can be written to, your database will continue to function. If all members of an online redo log group are unavailable, then your database will cease to operate.
As a DBA you must be extremely proficient in creating, adding, moving, and dropping these critical database files. Archiving is the mechanism for ensuring you have all the transactions required to recover the database. Once enabled, the archiver needs to successfully copy the online redo log after a log switch occurs.
Therefore, you need to map out carefully the amount of disk space required and how often to back up and subsequently remove these files. Tablespaces and data files are where your data is stored. These typically make up the bulk of any backup and recovery operation.
The chapters up to this point in the book have covered tasks connecting to Oracle, performing routine DBA tasks, and managing critical files. The next several chapters concentrate backup and recovery operations. Even more critical, a DBA must be able to restore and recover a database.
When media failures occur, everybody looks to the DBA to get the database up and running. There are two common, yet very different, Oracle approaches for backup and recovery: There are two types of user-managed backups: Cold backups are sometimes called offline backups because the database is shut down during the backup process.
Hot backups are also referred to as online backups because the database is available during the backup procedure. It automates and manages most aspects of backup and recovery. So, why have a chapter about user-managed backups when this approach has been gathering dust for more than a decade? Consider the following reasons for understanding user-managed backup and recovery: For these reasons, you should be familiar with user-managed backup and recovery techniques. RMAN makes much of backup and recovery automated and push-button.
However, knowledge of how to back up and recover a database manually helps you think through and troubleshoot issues with any type of backup technology. This chapter begins with cold backups.
These types of backups are viewed as the simplest form of user-managed backup because even a system administrator can implement them. Next, the chapter discusses hot backups. You also investigate several common restore-and-recovery scenarios. These examples build your base knowledge of Oracle backup and recovery internals. However, I would strongly recommend that you use RMAN to manage backup and recovery in a pluggable environment.
When connected to either the root container or a pluggable container, RMAN automatically determines which data files need to be backed up, their locations, and how to restore and recover. Implementing a Cold-Backup Strategy for a Noarchivelog Mode Database 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 database 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.
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 database in noarchivelog mode is to give you a way to restore a database back to a point in time in the past. 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.
This gives you a way to restart a performance test or a training session with the same point-in-time snapshot of the database. The example in this section shows you how to make a backup of every critical file in your database: With this type of backup, you can easily restore your database back to the point in time when the backup was made. 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. Copy the files identified in step 2 to the backup location determined in step 1. The following sections elaborate on these steps. Step 1. However, in many shops, you may not have a choice and may be told which mount points are to be used by the database.
To get a rough idea of how much space you need to store one copy of the backups, you can run this query: Make sure that the amount of disk space available at the OS is greater than the sum returned from the prior query: Identify the Locations and Names of the Database Files to Copy Run this query to list the names and paths of the files that are included in a cold backup of a noarchivelog mode database: 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 database 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.
Step 3. This mode disconnects users, rolls back incomplete transactions, and shuts down the database: Create Backup Copies of the Files 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.
Restart Your Database After all the files are copied, you can start up your database: 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. 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. Copy the Files Back from the Backup This step does the reverse of the backup: Copy the control files and data files back from the backup.
Start up the database in mount mode. Any files in the live database directory locations are overwritten when the backups are copied. Copy the Files Back from the Backup Copy the control files and data files from the backup location to the live data file locations: However, you may see this error: Oracle uses information in the control file for the placement, name, and size of the redo logs.
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. 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 script creates a file named coldback.
You place an exclamation mark! You can use this script to restore from the cold backup. The next script in this section dynamically creates a coldrest. You need to modify this script in the same manner that you modified the cold backup script i. After you run this shell script, here is a snippet of the code in the coldrest.
Therefore, unlike a backup of a noarchivelog mode database, this type of backup is not necessarily intended to be used to reset the database back to a point in time in the past from which no recovery can be applied. The purpose of a backup of an archivelog mode database is usually to restore the database and roll forward and apply transactions to fully recover the database. This has significant implications for the backups. Recall that for a noarchivelog mode database, DBAs sometimes include the online redo logs as part of the backup.
For a backup of an archivelog mode database, you should never include the online redo logs in the backup. The online redo logs contain the most currently generated redo transaction information for the database. The high-level steps for a cold backup of a database in archivelog mode are identical to those for a noarchivelog mode database: As of Oracle 10g, Oracle automatically attempts to create missing data files associated with the TEMP tablespace for locally managed temp tablespaces when the database is started.
Restoring and recovering with a cold backup of a database in archivelog mode is nearly identical to the restore and recovery from a hot backup. RMAN is a sophisticated and highly automated tool. With just a few commands, you can back up, restore, and recover your database.
A detailed knowledge of how to restore and recover from a hot backup helps you logically think your way through any RMAN scenario.
When you ride a bike, understanding how the derailleurs and gears and shifting work helps a great deal. You can usually tell when a rider knows only to push one button to go slower and another button to go faster. Riders who understand in more detail how the chain moves between gears will always be smoother at shifting gears. My editor, Jonathan Gennick, recounted the following anecdote while reading an early draft of this chapter: You should have heard the horrible noises he conjured out of my derailleurs and drivetrain.
I thought he was going to damage the bike. He was just one of those guys who knows only how to push the button, without any understanding of what goes on underneath that action. 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. Ensure that the database 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.
Copy the data files with an OS utility to the location determined in step 2. 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. Ensure That the Database Is in Archivelog Mode Run the following command to check the archivelog mode status of your database: Step 2. To get a rough idea of how much space you need, you can run this query: If you take that approach, you need to know which data files are associated with which: Note the Maximum Sequence Number of the Online Redo Logs 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: 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: This example alters all tablespaces out of backup mode at the same time: This is useful for determining the starting sequence number of the archivelog needed, in the event that the data file needs to be recovered.
Step 8. This ensures that an end-of-backup marker is written to the archive redo logs: 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: This example makes a backup of the control file and places it in the same location as the database backup files: You can do this with an OS copy command: 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: This script contains the commands for performing the hot backup. Here is a listing of the hotback. 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: 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. Understanding the Split-Block Issue 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. 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. The first half of the block is from time 1, and the latter half is copied at time 3. 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.
Oracle only records a change vector in the redo stream that specifies which block changed and how it was changed. 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. 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. Oracle uses the copy of the block it has in the redo stream as a starting point for the recovery of that block.
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. These archive redo logs were generated during the hot backup.
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. The database writer continues to write blocks to data files, regardless of the backup mode of the database. This is a widespread misconception. Use some common sense: If the transactions are being written to somewhere other than the data files, how would those data files be resynchronized after 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: Create a table that has a character field: Insert a string into that table: Force a checkpoint which ensures that all modified buffers are written to disk: From the OS, use the strings and grep commands to search for the string in the data file: Here is the output, proving that the database writer did write the data to disk: For instance, if only one data file has experienced media failure, you need to restore and recover only the damaged data file to perform a complete recovery.
Going through these steps can teach you more about backup and recovery than any documentation. The steps outlined here apply to any database backed up while in archivelog mode. The steps to restore and recover data files are the same, as long as the database was in archivelog mode during the backup. If you do this, make sure you place the data files online after the recovery is complete.
Restore the damaged data files with an OS copy utility. Alter the database open. The next several sections demonstrate some common complete restore-and-recovery scenarios. You should be able to apply these basic scenarios to diagnose and recover from any complex situation you find yourself in. Restoring and Recovering with the Database Offline This section details a simple restore-and-recovery scenario. Described next are the steps to simulate a failure and then perform a complete restore and recovery.
Try this scenario in a development database. Before you start this example, create a table, and insert some data. This table and data are selected from the end of the complete recovery process to demonstrate a successful recovery: Doing so ensures that you have to apply archive redo logs as part of the recovery: You can identify the name of this file with this query: You should also inspect the alert.
Restore the Data File from the Backup The next step is to copy from the backup the data file that corresponds to the one that has had a failure: If you attempt to open your database, Oracle throws an error stating that media recovery is required meaning that you need to apply redo to synchronize the SCN in the data file with the SCN in the control file. This indicates that redo must be applied to the data file before it can be opened for use.
Here is what happens when you try to open the database at this point: In this particular scenario, you may find it easier to remember the name of the tablespace that contains the restored data file s than to remember the data file name s.
You can view the starting log sequence number that RMAN will use to begin the recovery process via the following query: In this example, specify AUTO.
Oracle applies all redo in all archive redo log files and online redo log files to perform a complete recovery: Media recovery complete. Step 4. Alter Your Database Open After the media recovery is complete, you can open your database: For this to work, any data files being restored and recovered must be taken offline first. You may be alerted to an issue with a data file in which a user is attempting to update a table and sees an error such as this: In this example the data file associated with the USERS tablespace is taken offline and subsequently restored and recovered while the rest of the database remains online.
First, place take the data file offline: If all redo required is in the online redo logs, you see this message: These two situations are covered in the following sections.
Restoring a Damaged Control File When Multiplexed If you configure your database with more than one control file, you can shut down the database and use an OS command to copy an existing control file to the location of the missing control file. For example, from the initialization file, you know that two control files are used for this database: Oracle throws this error when querying the data dictionary: As long as you have all your data files and any required redo archive redo and online redo , you should be able to recover your database completely.
The steps for this scenario are as follows: Restore a control file from the backup. For a complete recovery, manually apply the redo contained in the online redo logs. In this example all control files for the database were accidentally deleted, and Oracle subsequently reports this error: Shut Down the Database First, shut down the database: Restore the Control File from the Backup This database was configured with just one control file, which you copy back from the backup location, as shown: Now, attempt to open the database: In this scenario the online redo logs are still intact and contain the required redo.
To apply redo contained in the online redo logs, first identify the locations and names of the online redo log files: This instructs the recovery process to apply any redo in the online redo log: Step 5.
In fact, with most incomplete scenarios, you have to restore all data files from the backup as part of the procedure. After the testing is finished, you want to reset the database back to baseline for another round of testing.
You can perform user-managed incomplete recovery three ways: You have to stop the recover process at the point of your last good archive redo log.
You may know from the alert log or from the output of LogMiner the point to which you want to restore to a certain SCN. For example, you may know that a table was dropped at a certain time and want to restore and recover the database up to the specified time.
The format for a time-based recovery is always as follows: Here is an example: Here are the steps for an incomplete recovery: Restore all the data files from the backup. Start the database in mount mode. Apply redo roll forward to the desired point, and halt the recovery process use cancel-, SCN-, or time-based recovery. If the database is open, shut it down: This example restores all data files from a hot backup.
Here is a snippet of the OS copy commands for the database being restored: This example performs a cancel- based incomplete recovery: The recovery is deemed incomplete because not all redo was applied. You may do this when recreating a control file, performing a restore and recovery with a backup control file, or performing an incomplete recovery.
Take the example of an incomplete recovery, in which the database is deliberately opened to a point in time in the past. In this situation the SCN information in the online redo logs contains transaction data that will never be recovered. Oracle requires a new incarnation so as to avoid accidentally using any old archive redo logs associated with a separate incarnation of the database , in the event that another restore and recovery is required.
Summary Some studies have indicated that airplane pilots who are over dependent on autopilot technology are less able to cope with catastrophic in-flight problems than the pilots who have spent considerable time flying without autopilot assistance. Similarly, DBAs who understand how to backup, restore, and recover a database manually, using user-managed techniques, are more proficient at troubleshooting and resolving serious backup and recovery problems than DBAs who only navigate backup and recovery technology via screens.
This is why I included this chapter in the book. Understanding what happens at each step and why the step is required is vital for complete knowledge of the Oracle backup and recovery architecture. You may find yourself employed in a shop in which old technology has been implemented and needing to restore and recover the database, troubleshoot, or assist in migrating to RMAN. In these scenarios, you must fully understand the old backup technologies.
The next several chapters examine how to configure and use RMAN for backup and recovery. The following list highlights some of the most salient qualities: Manages the deletion of obsolete backups and archive redo logs. Can use multiple processes for backup, restore, and recovery.
The goal of this chapter is to present enough information about RMAN that you can make reasonable decisions about how to implement a solid backup strategy. Refer back to this diagram when reading through this section. Target database: The database being backed up by RMAN. RMAN client: Oracle server processes: When you execute the rman client and connect to the target database, two Oracle server background processes are started. The secondary polling process occasionally updates Oracle data dictionary structures.
Channel s: Auxiliary database: Can be either a noun or a verb. The physical files backup that store the backed up files; or, the act of copying and archiving backing up files. Backups can consist of backup sets and backup pieces or image copies.
Backup set: A backup set is a logical RMAN construct that groups backup piece files. You can think of the relationship of a backup set to a backup piece as similar to the relationship between a tablespace and a data file: Backup piece file: RMAN binary backup files.
Each logical backup set consists of one or more backup piece files. These are the physical files that RMAN creates on disk or tape. A backup piece can contain blocks from many different data files.
Backup piece files are typically smaller than data files, because backup pieces only contain blocks that have been used in the data files. Image copy: A type of backup in which RMAN creates identical copies of a data file, archive redo log file, or control file. Image copies can be operated on by OS utilities such as the Linux cp and mv commands.
Image copies are used as part of incrementally updated image backups. Recovery catalog: An optional database schema that contains tables used to store metadata information regarding RMAN backup operations.
Media manager: Third-party software that allows RMAN to back up files directly to tape. An optional disk area that RMAN can use for backups. You can also use the FRA to multiplex control files and online redo logs. Snapshot control file: In these situations, RMAN first creates a temporary copy snapshot of the control file.
This allows RMAN to use a version of the control file that is guaranteed not to change while backing up the control file or synchronizing with the recovery catalog. You can make several types of backups with RMAN: Full backup: All modified blocks associated with the data file are backed up.
A full backup is not a backup of the entire database. For example, you can make a full backup of one data file. Incremental level 0 backup: Backs up the same blocks as a full backup. The only difference between a level 0 backup and a full backup is that you can use a level 0 backup with other incremental backups, but not a full backup.
Backs up only blocks that have been modified since the previous backup. Level 1 incremental backups can be either differential or cumulative. A differential level 1 backup is the default and backs up all blocks that have been modified since the last level 0 or level 1 backup. A cumulative level 1 backup backs up all blocks that have changed since the last level 0 backup.
Incrementally updated backup: First creates an image copy of the data files, after which subsequent backups are incremental backups that are merged with the image copy. This is an efficient way to use image copies for backups. Media recoveries using incrementally updated backups are fast because the image copy of the data file is used during the restore. Block change tracking: Database feature that keeps track of blocks that have changed in the database.
A record of the changed blocks is kept in a binary file. RMAN can use the contents of the binary file to improve the performance of incremental backups: Once those are in place, you can start RMAN and connect to your target database via the rman command-line utility. These are the same conditions that need to be in place before connecting to your database as a privileged user are described earlier in Chapter 1.
Using EM for backup and recovery is out of scope for this book. This book focuses on the command- line interface for its examples. This knowledge the foundation for using RMAN regardless of the interface.
This awareness is particularly useful when debugging and troubleshooting problems. You can then invoke RMAN and connect to the target database as follows: You must invoke the rman client from the OS prompt. This will help verify either that you are 97 www. Once connected to RMAN, you can issue administrative commands, such as startup, shutdown, backup, restore, and recover. For example, if you want to start and stop your database, you can do so from within RMAN as follows: For example, say you wanted to run the alter system switch logfile command.
Prior to Oracle 12c, you would have to specify that command as shown: RMAN Architectural Decisions If archiving is enabled for your database see Chapter 2 for details on archiving , you can use RMAN out of the box to run commands such as this to back up your entire target database: No, not quite.
The RMAN out-of-the-box settings may be appropriate for small development or test databases. But, for any type of business critical database, you need to consider carefully where the backups are stored, how long to store backups on disk or tape, which RMAN features are appropriate for the database, and so on.
However, each time you implement RMAN to back up a production database, you should think through each decision point and decide whether you require an attribute. Each of the decision points in the table is elaborated on in subsequent sections. The point is that you need to consider each architectural aspect and determine what makes sense for your business requirements.
Specifying the backup user Use SYS unless you have a security requirement that dictates otherwise. Using online or offline backups Depends on your business requirements. Most production databases require online backups, which means that you must enable archiving. Some shops require tape backups. Setting the autobackup Always enable autobackup of the control file. Specifying the location of the Either place it in the FRA, or configure a location.
I prefer to write the autobackup of the control file autobackup of the control file to the same location as that of the database backups. Backing up archive redo logs Depends on your business requirements.
For many environments, I back up the archive redo logs on a daily basis, with the same command I use to back up the database. Determining the location Use the default location. Using a recovery catalog Depends on your business requirements.
Oracle recommends that you do use a recovery catalog. Using a media manager This is required for backing up directly to tape. Setting the Usually, the default of 7 days is sufficient. For many backup retention policy environments, I use a backup retention redundancy of 1 or 2. Configuring the archive Depends on your database and business requirements.
Setting the Depends on the available hardware resources and business requirements. Using backup sets I prefer backup sets. Backup sets are generally smaller than image copies or image copies and easier to manage.
Using Use incremental backups for large databases when a small percentage of the incremental backups database changes between backups and when you want to conserve on disk space. Using incrementally Use this approach if you require image copies of data files. Using block change tracking Use this to improve the performance of incremental backups. Configuring Depends on your business requirements. Compressed backups consume binary compression less space but require more CPU resources and time for backup and restore operations.
Configuring encryption Depends on your business requirements. Configuring You can set many channel-related properties, such as the backup set size miscellaneous settings and backup piece size.
Configure as needed. When you run RMAN remotely, the backup files are always created on the target database server. Whenever possible, I run the rman client locally on the target server and connect, like this: If you run RMAN remotely, you need to be sure the remote rman executable is compatible with the target database.
If you run the rman client locally on the target server, there is never a compatibility issue because the rman client is always the same version as the target database. I prefer to use the SYS account directly, because when connecting to RMAN locally on the server, there is no need to specify a username and password. This means that you never have to hard-code usernames and passwords into any backup scripts or specify a username and password on the command line that can be viewed by rogue developers or managers looking over your shoulder.
Using Online or Offline Backups Most production databases have availability requirements. Therefore, your only option is online RMAN backups. Your database must be in archivelog mode for online backups. You need to consider carefully to place archive redo logs, how to format them, how often to back them up, and how long to retain them before deletion. These topics are discussed in subsequent sections.
RMAN needs the database in mount mode so that it can read from and write to the control file. When archivelog mode is enabled, Oracle writes the archive redo logs to one or more of the following locations you can configure archive redo logs to be written to the FRA as well as to several other locations that you manually set via initialization parameters: I prefer to use. For example, you can run the following command without configuring any RMAN parameters: In such situations, you need to allocate two or more channels that point to different mount points.
Using an FRA in these environments is somewhat unwieldy. Also, for performance reasons, you may want to instruct RMAN to write to multiple disk locations. I prefer to have the backups written to one directory and not separate the directories and backups by date. I find it easier to manage, maintain, and troubleshoot the backups if I use one standard directory for each database on each server. RMAN only allocates the number of channels as specified by the degree of parallelism; other configured channels are ignored.
RMAN creates as many backup pieces in the three locations as it deems necessary to create a backup of the database. If you need to unconfigure a channel, do so as follows: RMAN will open a channel for each degree of parallelism, and if the number of channels opened is greater than the number of preconfigured channels, for the unconfigured channels, RMAN will write backup files to the FRA if configured or the default location.
Use the SHOW command to display the current setting of the control file autobackup: If, for any reason, you want to disable automatic backup of the control file, you can do so as follows: Specifying the Location of the Autobackup of the Control File When you enable autobackup of the control file, RMAN creates the backup of the control file in one of the following locations: I prefer these backups to be placed in the same directory the database backups are in.
I usually like to keep on disk any archive redo logs that have been generated since the last good RMAN backup. Generally, I instruct RMAN to back up the archive redo logs while the data files are being backed up. This is a sufficient strategy in most situations. Here is the command to back up the archive redo logs along with the data files: DBAs may back up the archive redo logs two or three times a day; after the logs are backed up, the DBAs delete them to make room for more current archivelog files.
For example, if a data file has experienced media failure, you need to restore the data file from a backup and then apply any archive redo logs that were generated during and after the backup of the data file. On some occasions, you may need archive redo logs that were generated before the last backup. For instance, you may experience a media failure, attempt to restore your database from the last good backup, find corruption in that backup, and therefore need to restore from an older backup.
At that point, you need a copy of all archive redo logs that have been generated since that older backup was made. The default location of the snapshot control file is OS specific. You can display the current snapshot control file details, using the SHOW command: I recommend that you use the default setting.