Incremental backups of data files capture data changes on a block-by-block basis, rather than requiring the backup of all used blocks in a data file. With a complete set of redo logs and an older copy of a data file, Oracle can reapply the changes recorded in the redo logs to re-create the database at any point between the backup time and the end of the last redo log. This can be upward of 100's of terabytes. Loss of the control file makes recovery from a data loss much more difficult. A data warehouse often has lower availability requirements than an OLTP system. Oracle Database Backup and Recovery Users Guide for more information about configuring multisection backups. Oracle Database provides the ability to transport tablespaces across platforms.

SQL*Loader loads data from external flat files into tables of Oracle Database. This section contains the following topics: Physical Database Structures Used in Recovering Data. Incremental backups, like conventional backups, must not be run concurrently with NOLOGGING operations. Redo logs record all changes made to a database's data files. One approach is to take regular database backups and also store the necessary data files to re-create the ETL process for that entire week. For example, to keep guaranteed restore points for 2 days and you expect 100 GB of the database to change, then plan for 100 GB for the flashback logs. Moreover, in some data warehouses, there may be tablespaces dedicated to scratch space for users to store temporary tables and incremental results. Consequently, your recovery time may be longer. In a typical enterprise, hundreds or thousands of users may rely on the data warehouse to provide the information to help them understand their business and make better decisions. It is accessible in a running condition to millions of users at home and office i.e. In this case you have two RTOs. While this is a simplistic approach to database backup, it is easy to implement and provides more flexibility in backing up large amounts of data. External tables can also be used with the Data Pump driver to export data from a database, using CREATE TABLE AS SELECT * FROM, and then import data into Oracle Database. RMAN can fully use, in parallel, all available tape devices to maximize backup and recovery performance. In a typical data warehouse, data is generally active for a period ranging anywhere from 30 days to one year. While the most recent year of data may still be subject to modifications (due to returns, restatements, and so on), the last four years of data may be entirely static. Before looking at the backup and recovery techniques in detail, it is important to discuss specific techniques for backup and recovery of a data warehouse. However, just as there are many reasons to leverage ARCHIVELOG mode, there is a similarly compelling list of reasons to adopt RMAN. The external tables feature is a complement to existing SQL*Loader functionality.

Oracle Database requires at least two online redo log groups.

Currently, Oracle supports read-only tablespaces rather than read-only partitions or tables. Logical backups contain logical data (for example, tables or stored procedures) extracted from a database with Oracle Data Pump (export/import) utilities. These components include the files and other structures that constitute data for an Oracle data store and safeguard the data store against possible failures. Backup is a critical factor. However, a data warehouse may not require all of the data to be recovered from a backup, or for a complete failure, restoring the entire database before user access can commence. The data is stored in a binary file that can be imported into Oracle Database. The 100 GB refers to the subset of the database changed after the guaranteed restore points are created and not the frequency of changes. Running the database in ARCHIVELOG mode has the following benefits: The database can be recovered from both instance and media failure. In a data warehouse, there may be times when the database is not being fully used. Hot database backup means backing up the data warehouse with databases and related files while they are being updated. Moreover, subsequent operations to the data upon which a NOLOGGING operation has occurred also cannot be recovered even if those operations were not using NOLOGGING mode. A data warehouse contains historical information, and often, significant portions of the older data in a data warehouse are static. On the most basic level, temporary tablespaces never need to be backed up (a rule which RMAN enforces). RMAN takes care of all underlying database procedures before and after backup or recovery, freeing dependency on operating system and SQL*Plus scripts. However, the tradeoff is that a NOLOGGING operation cannot be recovered using conventional recovery mechanisms, because the necessary data to support the recovery was never written to the log file. This chapter contains the following sections: A data warehouse is a system that is designed to support analysis and decision-making. Each time data is changed in Oracle Database, that change is recorded in the online redo log first, before it is applied to the data files. This technology is the basis for the Oracle Data Pump Export and Data Pump Import utilities.

The resulting backup sets are generally smaller and more efficient than full data file backups, unless every block in the data file is changed. The advantage of static data is that it does not need to be backed up frequently. Logical backups store information about the schema objects created for a database. There are two approaches to backup and recovery in the presence of NOLOGGING operations: ETL or incremental backups.

When they are accessed by users online, they go onto online storage. During recovery, RMAN may point you to multiple different storage devices to perform the restore operation.

One important consideration in improving backup performance is minimizing the amount of data to be backed up. If you do not want to use Recovery Manager, you can use operating system commands, such as the UNIX dd or tar commands, to make backups. Document the backup and recovery plan. While the simplest backup and recovery scenario is to treat every tablespace in the database the same, Oracle Database provides the flexibility for a DBA to devise a backup and recovery scenario for each tablespace as needed. Oracle Data Pump enables high-speed movement of data and metadata from one database to another. It is important to design a backup plan to minimize database interruptions. The overall backup time for large data files can be dramatically reduced. Not only must the data warehouse provide good query performance for online users, but the data warehouse must also be efficient during the extract, transform, and load (ETL) process so that large amounts of data can be loaded in the shortest amount of time. You can also use flashback logs and guaranteed restore points to flashback your database to a previous point in time. In particular, one legitimate question might be: Should a data warehouse backup and recovery strategy be just like that of every other database system? RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with leading tape and storage media products with the supplied Media Management Library (MML) API. Several types of information stored in the control file are related to backup and recovery: Database information required to recover from failures or to perform media recovery, Database structure information, such as data file details. Logical backups are a useful supplement to physical backups in many circumstances but are not sufficient protection against data loss without physical backups. Backup and recovery is a crucial and important job for a DBA to protect business data. This utility makes logical backups by writing data from Oracle Database to operating system files. Some organizations may determine that in the unlikely event of a failure requiring the recovery of a significant portion of the data warehouse, they may tolerate an outage of a day or more if they can save significant expenditures in backup hardware and storage. A more automated backup and recovery strategy in the presence of NOLOGGING operations uses RMAN's incremental backup capability. This section contains several best practices that can be implemented to ease the administration of backup and recovery. Backup operations can also be automated by writing scripts. Each tool gives you a choice of several basic methods for making backups. To flash back to a time after the nologging batch job finishes, then create the guaranteed restore points at least one hour away from the end of the batch job. If the overall business strategy requires little or no downtime, then the backup strategy should implement an online backup.

When using BACKUP DURATION, you can choose between running the backup to completion as quickly as possible and running it more slowly to minimize the load the backup may impose on your database. One common optimization used by data warehouses is to execute bulk-data operations using the NOLOGGING mode. For this scenario, guaranteed restore points can be created without enabling flashback logging. When you have hundreds of terabytes of data that must be protected and recovered for a failure, the strategy can be very complex. Oracle Database does not archive the filled online redo log files before reusing them in the cycle. Hierarchical Storage Management (HSM) has this ability of online and offline storage. Data warehouse recovery is similar to that of an OLTP system.

While this window of time may be several contiguous hours, it is not enough to back up the entire database. If you configure a tape system so that it can back up the read-write portions of a data warehouse in 4 hours, the corollary is that a tape system might take 20 hours to recover the database if a complete recovery is necessary when 80% of the database is read-only. on Foxconn Entering European Data Center Services Market, on FOR THE DDOS ATTACK, ANNOUNCEMENT MADE FOR LAUNCHING SECURITY PLATFORM BY LEASEWEB. Oracle Recovery Manager (RMAN), a command-line is the Oracle-preferred method for efficiently backing up and recovering Oracle Database. Essentially, the data warehouse administrator is gaining better performance in the ETL process with NOLOGGING operations, at a price of slightly more complex and a less automated recovery process. Archived redo logs are crucial for recovery when no data can be lost because they constitute a record of changes to the database. These logs track the original block images when they are updated. The control file contains a crucial record of the physical structures of the database and their status. Assuming a fixed allowed downtime, a large OLTP system requires more hardware resources than a small OLTP system. A Recovery Point Objective, or RPO, is the maximum amount of data that can be lost before causing detrimental harm to the organization.

A zero RPO means that no committed data should be lost when media loss occurs, while a 24 hour RPO can tolerate a day's worth of data loss. The backup and recovery may take 100 times longer or require 100 times more storage. One downside to this approach is that the burden is on the data warehouse administrator to track all of the relevant changes that have occurred in the data warehouse. For example, in some data warehouses, users may create their own tables and data structures. It is the most efficient way to move bulk data between databases. Devising a backup and recovery strategy can be a complicated and challenging task. In a data warehouse, you should identify critical data that must be recovered in the n days after an outage. There are several key differences between data warehouses and OLTP systems that have significant impacts on backup and recovery. Oracle Database backups can be made while the database is open or closed. RMAN is efficient, supporting file multiplexing and parallel streaming, and verifies blocks for physical and (optionally) logical corruptions, on backup and restore. To create a user-managed online backup, the database must manually be placed into hot backup mode. This Veritas software is useful for keeping updated backup copies on a local or remote site thus making it readily available and instantaneous form of backup, particularly for smaller data warehouses. Your backup and recovery plan should be designed to meet RTOs your company chooses for its data warehouse. It is able to backup huge number of files, links, databases, data and others. Sign up now to receive DCT's News and Updates. Backup and recovery is literally means implementing various strategies, methods and procedures to protect databases, data center, data mining against loss and risks, and to recover it or to reconstruct it after failure. You may want to consider breaking up the database backup over several days. The database is backed up manually by executing commands specific to your operating system. Backups can be performed while the database is open and available for use. A data warehouse is typically updated through a controlled process called the ETL (Extract, Transform, Load) process, unlike in OLTP systems where users are modifying data themselves. This chapter discusses one key aspect of data warehouse availability: the recovery of data after a data loss. A data warehouse is typically much larger than an OLTP system. The database never needs to be taken down for a backup. When the guaranteed restore points are created, flashback logs are maintained just to satisfy Flashback Database to the guaranteed restore points and no other point in time, thus saving space. To restore a data file or control file from backup is to retrieve the file from the backup location on tape, disk, or other media, and make it available to Oracle Database. The next several sections help you to identify what data should be backed up and guide you to the method and tools that enable you to recover critical data in the shortest amount of time. However, today's tape storage continues to evolve to accommodate the amount of data that must be offloaded to tape (for example, advent of Virtual Tape Libraries which use disks internally with the standard tape access interface). A sample implementation of this approach is to make a backup of the data warehouse every weekend, and then store the necessary files to support the ETL process each night. By taking advantage of partitioning, users can make the static portions of their data read-only. The ETL process uses several Oracle features and a combination of methods to load (re-load) data into a data warehouse.

In each group, there is at least one online redo log member, an individual redo log file where the changes are recorded. More recovery options are available, such as the ability to perform tablespace point-in-time recovery (TSPITR). Very large databases are unique in that they are large and data may come from many resources. It enables you to access data in external sources as if it were in a table in the database. Design: Transform the recovery requirements into backup and recovery strategies. However, there may be a requirement to create a specific point-in-time snapshot (for example, right before a nightly batch job) for logical errors during the batch run. This copy can include important parts of a database such as the control file, archived redo logs, and data files. A backup is just another copy of original data. Flashback Database is a fast, continuous point-in-time recovery method to repair widespread logical errors. Oracle Database Data Warehousing Guide for more information about data warehouses.

Three basic components are required for the recovery of Oracle Database: Oracle Database consists of one or more logical storage units called tablespaces. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. There is a cold and hot database backup which do the backing up of databases, related files and links within the whole data warehouse. Data Warehouses are built by the corporate and leading companies, to accommodate and safeguard abundance of data, for successful running of their business. Transportable tablespaces allow users to quickly move a tablespace across Oracle Databases. You can make a backup of the entire database immediately, or back up individual tablespaces, data files, control files, or archived logs. These tablespaces are not explicit temporary tablespaces but are essentially functioning as temporary tablespaces. It means that companies will lose millions of data which could lead to loss of projects and business, thus loss of revenues and probably users. This data loss is often measured in terms of time, for example, 5 hours or 2 days worth of data loss.

The first principle to remember is, do not make a backup when a NOLOGGING operation is occurring. Backup strategies often involve copying the archived redo logs to disk or tape for longer-term storage. Typically, the only media recovery option is to restore the whole database to the point-in-time in which the full or incremental backups were made, which can result in the loss of recent transactions. If the source platform and the target platform are of different endianness, then RMAN converts the tablespace being transported to the target format. Maintaining backup copies save data from application or processing error and are like a bodyguard against data loss. Backup and recovery windows can be adjusted to fit any business requirements, given adequate hardware resources.

A Recovery Time Objective (RTO) is the time duration in which you want to be able to recover your data. For data warehouses, this can be extremely helpful if the database typically undergoes a low to medium percentage of changes. During this period, the historical data can still be updated and changed (for example, a retailer may accept returns up to 30 days beyond the date of purchase, so that sales data records could change during this period). While data warehouses are critical to businesses, there is also a significant cost associated with the ability to recover multiple terabytes in a few hours compared to recovering in a day. Given the size of a data warehouse (and consequently the amount of time to back up a data warehouse), it is generally not viable to make an offline backup of a data warehouse, which would be necessitated if one were using NOARCHIVELOG mode. Incremental backups provide the capability to back up only the changed blocks since the previous backup. Preserving the archived redo log is a major part of your backup strategy, as it contains a record of all updates to data files. These four characteristics are key considerations when devising a backup and recovery strategy that is optimized for data warehouses. This approach does not capture changes that fall outside of the ETL process. b. It requires a high-end backup product, for example Oracle 7 or 8 databases, which has hot backup capability and recovery system. Oracle Data Pump loads data and metadata into a set of operating system files that can be imported on the same system or moved to another system and imported there. Depending on the business, some enterprises can afford downtime. Not all of the tablespaces in a data warehouse are equally significant from a backup and recovery perspective.

Build and integrate: Deploy and integrate the solution into your environment to back up and recover your data. Although the NOLOGGING operations were not captured in the archive logs, the data from the NOLOGGING operations is present in the incremental backups. In the event where a recovery is necessary, the data warehouse could be recovered from the most recent backup. stub since the big part of this file has moved onto secondary storage while being offline. Before you begin to think seriously about a backup and recovery strategy, the physical data structures relevant for backup and recovery operations must be identified. It backup databases and non-databases files to cover up the whole data warehouse and continues to store increasing backup of files and data without scanning them.