In a perfect world, databases would be available 24 hours a day, 7 days a week, and 365 days a year. In the real world, this is not the case. That is why a good database administrator must be aware of what can and will go wrong with a database, including situations that are beyond your control. In this module you will learn some of the considerations a good DBA must evaluate when formulating or reassessing an existing backup and recovery plan.
By the end of this module you will be familiar with the following:
- DBA responsibilities
- Business considerations
- Operational considerations
- Technical considerations
- The components of a disaster recovery plan
- Importance of testing a backup and recovery strategy
There will come a time when your database crashes and your job is on the line. You cannot plan for every situation but the better prepared you are, the more likely you will be to survive the crisis. So, let us get started.
>
When deciding between physical and logical backups in Oracle, the decision hinges on various factors, including the specific requirements of the database, the intended use of the backups, the flexibility needed in recovery operations,
and the database's size and complexity.
Physical Backups:
Physical backups consist of the files that store the database data, such as datafiles, control files, and archived redo logs. They are a byte-for-byte copy of the database and can be performed while the database is online (hot backup) or offline (cold backup).
- Use Case: Physical backups are crucial for disaster recovery scenarios. They are suitable when the requirement is to restore the entire database or to perform point-in-time recoveries.
- Advantages: Physical backups are typically faster to restore because they involve copying files directly back to their locations. They are comprehensive and include the entire database, ensuring no components are missed.
- Disadvantages: These backups can be large, as they include the entire database, and they are less flexible because they do not allow for object-level recovery.
Logical Backups:
Logical backups contain the logical data (such as tables, stored procedures, and other schema objects) extracted using tools like Oracle's Data Pump (expdp and impdp) or the older export/import utilities. These backups are in the form of SQL statements or proprietary binary format.
- Use Case: Logical backups are beneficial when migrating data between databases, between different database versions, or when you need to recover specific objects.
- Advantages: They provide the flexibility to recover individual objects and can be used to move data across different database structures or versions. Logical backups are also usually smaller in size than physical backups.
- Disadvantages: The restoration process can be slower because the data needs to be re-inserted through SQL statements. They are not suitable for complete disaster recovery, as they do not include system-related files.
Determining Factors:
- Recovery Requirements: If complete disaster recovery is the goal, physical backups are necessary. For more selective data recovery or migration, logical backups are more suitable.
- Database Downtime: Physical backups can be done while the database is up and running, whereas logical backups may sometimes require downtime, depending on the method used and the database activity.
- Database Size: For large databases, physical backups are often more practical. Logical backups can become impractical due to the time and space required to manage the data export and import.
- Flexibility and Portability: Logical backups offer more flexibility for partial recovery and are portable between different architectures and database versions.
- Backup Window: The available time for performing backups may influence the choice. Physical backups can be integrated with media management solutions for speed and efficiency.
- Resource Utilization: Logical backups are generally more resource-intensive than physical backups, as they require more CPU and I/O to export and import data.
- Data Specificity: If the backup strategy involves cloning specific sets of data for development or testing purposes, logical backups may be more appropriate.
The choice between physical and logical backups is often not mutually exclusive. Many organizations implement both as part of a comprehensive backup strategy, using physical backups for disaster recovery and logical backups for data portability and object-level recovery. The decision should be based on a thorough assessment of recovery objectives, resources, and operational constraints, aligned with the organization's overall data protection policy.