Automated Solutions For Backup And Recovery Of Oracle Databases
- Published on October 30, 2007
The allure of client/server architecture is very strong for those who want to downsize from a mainframe environment. It promises, among other things, local ownership of local data and distributed processing. Unfortunately, the client/server model introduces complexities in data administration and protection.
Mission critical data must be protected, whether it is stored locally or centrally. Unlike the mainframe environment with its centralized operations staff, sophisticated tools and standardized procedures, client/server applications reside in an open, often fluid, environment. The very aspects that make it so attractive (heterogeneous environments, smaller IS staff, distributed computers) can make administering and securing data a nightmare.
Even in a centralized IS environment, client/server databases typically lack the sophisticated backup and recovery tools taken for granted on the mainframe system. Wherever backups are inadequate or not consistently performed, valuable data is at risk. In the case of Oracle, a leading relational database management system (RDBMS), existing backup and recovery procedures are complex to learn, deploy and maintain, leaving core applications vulnerable to failures or long outages.
With the growing number of client/server applications maturing and supporting vital corporate functions, ever increasing amounts of critical data is being put at risk.
Oracle Backup/Recovery Today
Current backup and recovery methods for production Oracle databases not only take a great deal of time, they also demand a high level of expertise from database administrators (DBAs).
To carry out successful backup and recovery using traditional methods, DBAs first need a comprehensive understanding of the structure of the database and the relationships between the files. They need to write, or have written, robust scripts for backing up all of the necessary database components. They need to maintain these scripts consistently as database files are added or changed.
Once the system is in place and running, DBAs need to perform and carefully track database backups and exports, which requires a good system for identifying and locating the actual backup tapes and files. In practice, this tracking system may be maintained by the DBA, the system administrator (SA) or some combination of the two.
Database recovery relies even more heavily on the DBA not only correctly retrieving the files, but understanding the nature of the failure, identifying the correct files to retrieve and performing the appropriate actions to recover the database. The heavy reliance on the DBA's presence and expertise leaves some institutions vulnerable to failure because they cannot reach the right person, or because they are experiencing turnover in the DBA group.
The risks to data can be classified as five key areas in the development and implementation of a backup/recovery strategy:
- establishing the backup procedure
- maintaining the backup procedure
- recovering data
- adjusting for growth
- cross-system interaction
The three mainstays of a comprehensive backup and recovery plan are:
- physical backups (usually a combination of cold and hot backups)
- archivelog management
- regular database exports
If one of these is missing, it may not be possible to recover from some failures. The human component is of such importance here that obtaining the services of a well qualified DBA is often critical. However, as the range of Oracle applications continues to expand, good DBAs are harder to find.
Organizations with experienced DBAs are always at risk of losing them; turnover rates are high. Isolated departments in organizations that are beginning to hold mission-critical data may not have the expertise locally to set up or adhere to standard backup procedures - they may not even recognize that they need to do this.
Most Oracle DBAs have heard the story of the DBA who added a data file but forgot to modify the backup script and found after a failure that there were no backups. It may just be a DBA legend, but it does highlight a basic flaw in the process - ongoing vigilance is required to keep the backups up to date.
Also, it is often the case that the people who write the backup scripts are not involved in the maintenance process; they may even work for a different company. As one manager at a major financial institution told us, "We're okay as long as those consultants who set it up are still around."
Setting up the backup procedure is often the task of the consultants or developers who build the application and who move on to other projects, departments or companies when the project is complete.
If the scripts themselves are not easily maintainable, the DBA responsible for the database may ultimately have to create a new backup procedure from scratch.
Like the backup process, Oracle database recovery requires a detailed understanding of the nature of the failure, exact information about what data is required to be recovered and a comprehensive understanding of the Oracle database structure and recovery processes.The inevitability of failure occurring at a supremely inconvenient time simply adds to the pressure under which DBAs must work.
It is possible to have all the right pieces available for a recovery, and still not put them together correctly for a consistent database recovery.
In some sites, a system administrator is responsible for writing to tape the actual files that comprise the database, and for recovering them from tape. In this case, the DBA cannot perform a recovery alone.
It now depends not only on the DBA's expertise and time, but also the system administrator's expedience in finding and recovering specific files. Inefficient communication between the two will delay a complete recovery and may result in a key application being unavailable for a prolonged period.
Adjusting for Growth
Even with a devoted and knowledgeable DBA, flawless scripts, fast tape drives and happy users, there is no assurance that a current backup system will continue to work as the database grows.A backup system that's fine for a 2G database may be unacceptable for one of 20G and impossible for one of 200G.
Many DBAs anticipate significant growth in both database size and usage requirements for the applications they maintain. Very few have determined how they will work with those growth requirements.
Most are optimistically counting on the database vendors supplying something before the problem becomes critical.
Database vendors often provide no tools or guidance on backup and recovery issues, and any administrative tools they will supply are for their own databases. Sites with more than one vendor's database in-house must either maintain separate, different procedures for each type of database, or look for an integrated solution that uses common procedures for all mission-critical databases.
The problems, therefore, are manifold. They occur with staffing, with the supply of tools, and the complexities of creating an efficient backup system and of recovering from a failure.
The likelihood that databases will continue to increase in size simply compounds the problems outlined above. There is clearly a requirement for tools that are specifically designed to manage large databases, though it is often beyond the scope of database vendors to develop such software.
The future lies in backup and recovery tools that look at the complete scope of backup and recovery tasks that DBAs face in the Oracle environment, including maintaining backup configurations and performing complex recoveries. These tools should address all five areas described earlier in which problems occur to be truly successful solutions for complex client/server environments.
Anne Janzer is a Product Marketing Manager for DataTools, Inc.