In Oracle databases, there are three forms of database recovery: complete, incomplete, and point-in-time recovery.
A complete recovery is used to restore a database to a consistent state, typically after a catastrophic failure, such as a disk failure or a major system crash. In such situations, you would need to restore the last backup of the database and then replay all archived and redo logs generated since the backup. To perform a complete recovery, the database must be shut down and restarted with the resetlogs option.
An incomplete recovery is used to recover the database to a specific point in time between two backups, typically in the event of a minor issue such as a user error or a corrupt data block. In such situations, you would restore the last backups of the database and then apply all archived and redo logs generated between that backup and the point in time where the error occurred. Unlike with complete recovery, the database is not opened with the resetlogs option when performing an incomplete recovery.
A point-in-time recovery is a type of incomplete recovery that recovers the database to a specific point in time, rather than a specific SCN (System Change Number). This type of recovery is often used to recover from human error or application errors that occurred at a specific point in time. For example, if a user accidentally deletes a table at 3:00 PM, we could perform a point-in-time recovery to return the database to its state at 2:59 PM. This type of recovery is similar to an incomplete recovery, but instead of specifying a log sequence number (LSN) or SCN to stop recovery, we specify a timestamp to stop applying logs.
In summary, complete recovery restores a database to a consistent state after a catastrophic failure, incomplete recovery is used to recover the database to a specific point in time between two backups, and point-in-time recovery recovers the database to a specific point in time, rather than a specific SCN.