Oracle Recovery File and Data Structures

Lesson 1

As we already discussed, an Oracle database system is made up of two major components, the 1) instance and 2) the database. We talked about some of the components of the instance. Now we will discuss some of the database file and data structures. We are most concerned with the files stored on disk when we discuss backup and recovery. In this module we will cover the various data files, where they are placed on disk, and some tips about managing data files that will make your life easier. By the end of this module, you should be familiar with the following:

1. Oracle Control files
2. Redo log files
3. Archive log files
4. Checkpoint processing
5. Some file placement considerations
6. How to determine the current state and structure of your database

Currently, the Oracle DBA certification exam does not test you on best practices or work related situations. One site may run Unix exclusively while another site may run only NT. It would be unfair to test you on situations that you would not normally encounter in most production environments. That is why the test is based solely on the content presented in the Oracle8 Backup and Recovery class. The information presented in this module will present backup and recovery concepts, but will use a particular configuration to demonstrate certain points. The disk file names in our discussion and samples are sample names for purposes of illustration only. I will try to stay true to the default naming conventions familiar to most Oracle database users.

A breakdown of the essential knowledge areas for an Oracle DBA administering Oracle 19c, focusing on the minimum baseline for effective management:
Core Database Architecture:

• Understanding Components: Memory structures (SGA, PGA), background processes (PMON, SMON, etc.), storage structures (datafiles, control files, redo logs), and their interactions.
• Multitenant Architecture (CDB/PDB): How Container Databases (CDB) and Pluggable Databases (PDB) work, and their management.

Installation and Configuration:

• System Requirements: Hardware/software needs for Oracle 19c, sizing considerations.
• Installation Processes: Using Oracle Universal Installer (OUI), Database Configuration Assistant (DBCA), or manual procedures.
• Basic Network Configuration: Oracle Net Services (Listener, TNSNAMES), connectivity fundamentals.

Database Creation and Management:

• Database Creation: Using DBCA, manual scripts, knowledge of necessary parameters.
• Storage Structures: Tablespaces, datafiles, control files; their creation, resizing, and management.
• User and Security Management: Creating users, roles, profiles, and granting/revoking privileges.

Backup and Recovery:

• Strategies: RMAN, user-managed (hot/cold) backups, recovery scenarios (data loss, instance crash).
• RMAN Basics: Taking backups (database, tablespaces, archivelogs), basic restores.
• Data Pump: Using Data Pump utilities (Export/Import) for data movement and logical backups.

Performance Monitoring and Tuning:

• Baselining: Setting performance reference points, gathering baseline metrics for health checks.
• Tools: Automatic Workload Repository (AWR), ASH reports, Statspack (older versions), using system views (like V$SESSION).
• Identifying Bottlenecks: Common performance issues (I/O, SQL execution, memory usage).

Maintenance and Troubleshooting:

• Patching: Applying security and bug fix patches.
• Database Upgrades: Processes and considerations for upgrading database versions.
• Proactive Monitoring: Setting alerts, using Enterprise Manager or custom scripts.
• Basic Problem Diagnostics: Using logs, trace files, and troubleshooting knowledge.

Additional Considerations (Beyond the bare minimum):

• Oracle ASM: For disk management, if applicable.
• Oracle Data Guard: Setting up disaster recovery solutions.
• Scripting: Skills in basic SQL and shell scripting are very helpful for automation.

Remember, being a DBA is an ongoing learning process. Start with the foundations and keep expanding your skills.