Lesson 1

Complete Oracle recovery, also known as complete media recovery, is applied to a database running in ARCHIVELOG mode. Complete recovery with archiving allows you to recover your database up to the time just prior to the occurrence of media failure. It is a more complicated procedure than recovering a NOARCHIVELOG database because you must perform recovery by applying the archived and redo logs after the damaged datafiles are restored.

In Oracle 12c, performing a complete recovery with archiving to recover your database up to the time prior to the occurrence of media failure involves a series of steps. This process assumes that the database is running in ARCHIVELOG mode, which is a prerequisite for complete recovery. Here is a detailed outline of the steps involved:

1. Identify the Extent of Damage: Begin by assessing the extent of the media failure. Determine which datafiles, control files, or redo log files are affected. This analysis will guide the recovery process.
2. Ensure Database is in the Proper State: If the database is open, it needs to be placed in MOUNT state. If it’s not open, start the Oracle instance without mounting the database using the command `STARTUP NOMOUNT`.
3. Restore Datafiles from Backup: Restore the affected datafiles from the most recent backup. This step is critical as it lays the foundation for the recovery process. Use RMAN (Recovery Manager) or other backup tools you have in place for this operation.
4. Mount the Database: If the database is not already mounted, mount it using the command `ALTER DATABASE MOUNT;`.
5. Apply Archived Redo Logs: Apply the archived redo logs to bring the database up to the point just before the media failure occurred. This is done by using the RMAN `RECOVER DATABASE` command, which applies the necessary archived logs and redo information.
6. Open the Database with RESETLOGS Option: After the recovery is complete, open the database with the `RESETLOGS` option. This is necessary because the recovery process might have included incomplete transactions that need to be reset. The command is `ALTER DATABASE OPEN RESETLOGS;`.
7. Perform a Consistency Check: Once the database is open, perform a consistency check to ensure that the database is structurally sound and that all data is accessible. This can be done using tools like DBVERIFY or by running comprehensive queries against the database.
8. Take a New Backup: After the recovery process, it's crucial to take a new backup of the database. This backup will include all the changes made during the recovery process and will serve as the new baseline for any future recovery operations.
9. Review and Update Recovery Procedures: Finally, review the recovery process to identify any gaps or areas for improvement. Update your recovery procedures and documentation accordingly to reflect any new insights or changes made during this process.

Throughout this process, meticulous attention to detail is essential. Ensure that all steps are carefully executed and documented. The success of the recovery process hinges on the accuracy of the restored data and the completeness of the applied archived redo logs. As a DBA, your expertise in navigating these complex procedures is vital to the integrity and availability of the Oracle 12c database system.

The archiver (ARCn) is an optional background process and is in charge of archiving the filled online redo log files, before they can be overwritten by new data. The archiver background process is used only if you are running your database in archivelog mode. Physical Database Structures Used in Recovering Data You need to deal with four major physical database structures during a database recovery:

1. Data files
2. Redo logs (archived and online)
3. Control files
4. Undo records

In a basic database recovery situation, you would need to first restore data files by using backups (from a past period, of course). Once the restoration of the data files is completed, you issue the recover command, which results in the database rolling forward all committed data and thus bringing the database up-to-date. The database also rolls back any uncommitted data that is recorded in the undo segments that are part of the undo tablespace. The database server automatically performs the rollback of uncommitted data by using undo records in the undo tablespace to undo all uncommitted changes that were applied to the data files from the redo logs during the recovery process. This rolling-back of uncommitted data takes place by using the information about all the changes made since the last database startup. Oracle records all changes made to the database in files called the online redo log files. Since Oracle uses a round-robin method of writing the online redo log members, it is critical that you save the filled online redo logs before they are written. The process of saving the filled redo log files is called archiving, and the saved redo log files are termed archived redo log files. A media recovery process uses both the archived redo log files and the online redo log files. The control file is essential for the Oracle instance to function, because it contains critical information concerning tablespace and data file records, checkpoints, redo log threads in the current online redo log, log sequence numbers, and so on. RMAN lets you back up all the files you need for a database recovery, including

1. data files,
2. control files,
3. the spfile, and
4. archived redo logs.

RMAN also lets you make image copies of both data files, the spfile and control files, in addition to the standard RMAN-formatted backup pieces. You should never back up online redo log files; instead, always duplex these files to protect against the loss of an online redo log.

This module investigates different methods of performing complete media recovery on an ARCHIVELOG database.
After completing this module, you will be able to:

1. Describe the implications of instance failure with an ARCHIVELOG database
2. Describe the different methods of recovery
3. Describe a complete recovery operation
4. Recover a closed database
5. Recover an open database, initially open
6. Recover an open database, initially closed

The next lesson explains the implications of instance failure with ARCHIVELOG databases.