Full and incomplete recovery of Oracle database and execution of user management generational recovery

Compare full and incomplete restores:

A. Full recovery: Restores the database to its current state, including all committed data changes until the request is resumed

Second, incomplete recovery: Restore the database to the last point of time specified before the request recovery operation

I. Complete recovery process
The following steps describe the actions to take during a full recovery:

1. Restore damaged or missing files by backup.

2. Apply the changes in the incremental backup, archive redo log files, and online redo log files as needed. The redo log changes are applied to the data file until the current online log is reached and the latest transaction is re-entered. A restore block is generated throughout the process. This is known as roll-forward or cache recovery.

3. At this point, the restored data file contains the committed and uncommitted changes.

4. The restore block is used to fallback any uncommitted changes. Sometimes also referred to as transactional recovery.

5. At this point, the data file is in a restored state and is consistent with other data files in the database.

Second, incomplete recovery process:

Incomplete recovery or database point-in-time recovery uses a backup to build a database that is not the current version. That is, all redo records generated after the most recent backup are not applied. This type of recovery is performed only when absolutely necessary. To perform an incomplete restore, you need:

Valid offline or online backup of all data files generated before the recovery point

All archive logs from backup time to specified recovery time
The following is a list of the procedures for performing incomplete restores:

1. Restore a data file from a backup: If the restore point target is not very new, the backup used may not be up-to-date. This requires using the OS command or the RMAN RESTORE command to copy the files.
2. Use the RECOVER command: Apply the redo from the archive redo log file and include as much data as necessary to achieve the restore point objective.

3. Recovery status: At this point, the data file contains some committed transactions and uncommitted transactions, because the redo can contain uncommitted data.

4. Use the ALTER Database Open command: The databases are open before you apply the restore block. This is to provide a higher level of availability.

5. Apply restore data: When the redo is applied, the redo that supports the restore data file is also applied. This allows the restore to be applied to the data file in order to restore any uncommitted transactions. This is the next action to complete.

6. Process complete: At this point, the data file has been restored to the selected point in time.

Point-in-time recovery is the only option if recovery must be performed and the transaction is found to be missing from the archive log that contains the transaction processing that occurs between the creation time of the backup used for the restore and the target recovery SCN. There is no missing log, there is no record of the data file being updated in that period. The only option is to restore the database from the point in time that the backup was restored, until the time allowed by the undamaged archive log series, and then open the database using the Resetlogs option. Any changes in or after the lost redo log file are lost.

Types of use for backup and recovery:

The types of database backup and recovery include:

User-managed: Do not use RMAN.
--Using OS commands to move files--DBA requires manual maintenance of backup activity records

Server-managed: using RMAN

1. Perform user-managed database backups
You can back up a database by using an OS command to create a copy of the data file. The operation process depends on whether the database is in ARCHIVELOG mode. If so, you can leave the database open and available by placing each tablespace in Backup mode before copying the data files for the tablespace. Otherwise, you must close the database before copying the data file.

1) manually back up the Noarchivelog database:

By shutting down the database and copying all the data files and control files to the backup directory, you can make a consistent overall database backup of the Noarchivelog database. Because the file copy operation is done using the OS command, you must first close the database. This puts the database in a consistent state.

Sql> SHUTDOWN IMMEDIATE
$ CP $ORACLE _base/orcl/datafile/*.dbf/u02/backup/datafile
--Copy the data files to the backup location

$ CP $ORACLE _base/orcl/controlfile/*.ctl/u02/backup/controlfile--Copy the control file to the backup location

Sql> STARTUP
2) Manually backing up the ARCHIVELOG database

If the database is in ARCHIVELOG mode, you may not have to close the database before copying the file. The result is an inconsistent backup, but the app will revert to a consistent state after the data has been re-made.

To start the backup mode:
Before you can copy a data file, you must put each data file in Backup mode. Use the BEGIN BACKUP clause of the ALTER TABLESPACE and ALTER DATABASE commands to perform this operation. The following is the syntax for each command:

ALTER tablespace <tablespace> BEGIN BACKUP;
The--alter tablespace command affects only those data files that are part of the tablespace.

ALTER DATABASE BEGIN BACKUP;
--alter database affects all data files in the databases.

To end the backup mode:

It is important to leave the data file out of backup mode. When you close a database, you cannot have any data files in Backup mode. If you try to close a database that is in that state, you receive an error. In addition, because the backup mode causes additional redo data to be generated, it brings additional load to the system. If you are not actively backing up your data files, there is no reason to keep any data files in Backup mode.

Specific operation:

To back up a data file:
Sql> Select file_name, tablespace_name from Dba_data_files;
--View all data file locations and table spaces

Sql> ALTER tablespace users BEGIN BACKUP;
--place tablespace in Backup mode

$ CP $ORACLE _home/oradata/orcl/users*.dbf/u02/backup/datafile
--Copy the tablespace's data file to the backup location

Sql> ALTER tablespace users END BACKUP;
--Make the table space exit Backup mode

The ways to back up your control files include:

(1). Create a file that is written to the specified name as a copy of the image:

sql> ALTER DATABASE BACKUP controlfile to
'/u01/backup/controlfile.bak ';

Database altered.

(2). Generate a script that re-creates the control file and write to the trace file:

sql> ALTER DATABASE BACKUP controlfile to TRACE;

Database altered.
--The script for rebuilding the control file is in $oracle_base/diag/rdbms/prod/prod/trace

Looking at the newly generated script file, you will find that there are two ways to create a control file:

Noresetlogs ARCHIVELOG, indicating that the log files, such as intact, restart the database is not resetlog;

Resetlogs ARCHIVELOG; Log file corruption is lost, can not perform a full recovery, the log group number is cleared 0;

2. Perform user-managed database recovery

1) Perform user-managed database Full recovery

User-managed Database Full recovery:

Restore the database to the latest SCN
You can process the entire database at one time, or you can process one data file or tablespace at a time requiring backup of all files that need to be recovered for the current control file to all archive logs so far

V$recover_file: See which files require media recovery

V$recovery_log: See which archive logs are required to perform recovery

In some cases, when files that are part of the SYSTEM tablespace are damaged, the instance is automatically closed. When something is wrong with some data files, you can decide that keeping the database running is meaningless, even if the instance remains running, and there are too many affected database objects. In this case, shut down the database to perform the recovery.

If the database is still open, you can query the V$recover_file view to see which data files need to be recovered and query V$recovery_log to find out which archive logs are required. This indicates which files, if any, you need to restore from the backup.
Then close the database. Investigate the media failure and determine the cause of the problem. Fix the problem so that you can restore the files from the backup.

For example, you may need to replace the disk drive.
You can now use the RECOVER command to perform the recovery. Limit the recovery scope to the desired range, such as a data file or table space. You can restore the entire database if needed. Then, open the database.

sql> SELECT file#, error from V$recover_file;
--Identify the data files that need to be recovered

Sql> SELECT archive_name from V$recovery_log;
--Determine which archive log files are required to complete the recovery

Identify recovery-related files

If the database is still open, follow the instructions below to query the file. Otherwise, try to launch the instance and mount the database to issue the query.

In order to determine which data files need to be recovered, query the V$recover_file view. The ERROR column indicates why the file needs to be recovered. If this column has any value other than OFFLINE NORMAL, recovery is required.

To see an overview of all affected data files and tablespaces, you can include V$datafile and v$tablespace in this query.
Here is an example:

SELECT r.file#, D.name df_name, T.name tbsp_name, D.status, R.error, r.change#, R.time

From V$recover_file R, V$datafile D, V$tablespace t

WHERE t.ts# = d.ts#
and d.file# = r.file#;

This can indicate the extent of the damage and help you determine the object of the RECOVER command.

The V$recovery_log view displays the archive log files required to perform the recovery. If the list shows that some files have been moved out of the default archive log location, you must restore them to a location before performing the recovery.
After the results of these queries are logged, close the database.

Restore files related to recovery

After you have determined which data files and archived log files are required, restore them to the appropriate disk location. By copying the data file from the backup location, you can restore the data file, as shown in the following example:

$ cp/disk2/backup/datafile/survey01.dbf $ORACLE _base/oradata/orcl/datafile/survey01.dbf

If recovery requires any archived logs, check that the logs are still in the default disk location of the archive log. These logs may not be in the default location,
For example, they have been moved to a tape or other disk drive. If the logs have been moved, you need to restore them to the default archive log location or to a temporary location. If there is enough free space in the default location (specified by the log_archive_dest_1 initialization parameter), restore the log at that location. Otherwise, you can place the log on some other disk location. When you restore, you can specify the alternate location to locate the archive log file.
If you need to move data files, you must record this in a control file. You can do this by executing the ALTER DATABASE RENAME FILE command, as shown in the following example:

sql> ALTER DATABASE RENAME FILE
'/U01/APP/ORACLE/ORADATA/ORCL/DATAFILE/SURVEY01.DBF ' to
'/NEWDISK/ORCL/DATAFILE/SURVEY01.DBF ';
--You must start the instance and mount the database before you can execute the ALTER DATABASE RENAME FILE command.

Download the database and bring all the data files online (if you have not already done so). By querying the V$datafile view, you can check the status of each data file. You can bring the data file online using the following command:

sql> ALTER DATABASE datafile '/newdisk/orcl/datafile/survey01.dbf ' ONLINE;

Apply Redo Data

At this point, the data file has been restored to a point in time in the past. The archive log file has also been restored to its default location or to a different location (for this recovery only). is ready to perform a true recovery step, which means that the redo has been applied and the data file has been restored to the latest SCN.
Use the Sql*plus RECOVER command to perform this operation.
If you do not specify the AUTOMATIC option, you are prompted to specify each redo log file that you want to apply. This improves the control of the recovery process. Typically, AUTOMATIC is used for full recovery.
If the archived log file has been restored to a disk location other than the default location of the database, you must specify the FROM clause. Provide the directory where these files are stored, and the recovery process will look for files in that directory.
Finally, open the database. This is fully restored.

Sql> RECOVER AUTOMATIC from '/u01/arch_temp ' DATABASE;
--Use the RECOVER command to apply redo data

sql> ALTER DATABASE OPEN; --Open Database

Perform a full recovery on an open database

If a media failure occurs while the database is open, the database continues to run. When you try to write data to a data file, the data file is automatically taken offline. Querying these data files does not cause them to be taken offline, but it returns an error to the user who issued the query.
Similar to recovering a closed database, you first need to query the files and archive logs that need to be recovered. Then, make all the table spaces that contain the corrupted data file offline. You can do this using the following command:
sql> ALTER tablespace survey OFFLINE temporary;
Using the temporary option causes Oracle to perform checkpoint operations on all online data files that are part of the tablespace. Data files that have been inspected for checkpoints do not need to be recovered after they are brought back online because they are up to date with the latest SCN for any transaction that might affect them. Although the data file must be available while this command is running, this option is more consistent with your needs. The problem may be temporary, and you can bring the tablespace online without generating an error.
Check the media to determine the cause of the problem. You can use the Dbverify utility to perform this operation. If the file is permanently damaged, restore and restore the contents as described in the previous restore shutdown database. After the restore and restore steps are complete, you should bring all tablespaces back online.

2. Perform incomplete recovery of user management

You can restore a database to a previous point in time in the following situations:

You want the database to be in a state before a user error or administrative error occurs.

The database contains corrupted blocks.

A full recovery of the database cannot be performed because some redo log files are missing.

You want to create a test database that is in the state of a certain time in the past.

Lost data file and one or more archived redo log files

You can indicate when to stop applying the redo data in the following ways:

1). Specify the time to stop
sql> RECOVER DATABASE UNTIL
Time ' 2005-12-14:12:10:03 ';

2).
Specify a stopped SCN
sql> recover database until change1487389;

3). Issue a CANCEL command when performing a restore
sql> RECOVER DATABASE UNTIL CANCEL;

Perform incomplete recovery of user management

Perform an incomplete recovery using the following command:

RECOVER [AUTOMATIC] DATABASE option

Here's what the options mean:

AUTOMATIC: Automatically apply archive and redo log files

Option:until time ' Yyyy-mm-dd:hh24:mi:ss '

UNTIL CANCEL

UNTIL Change <integer>

USING BACKUP Controlfile

Incomplete recovery based on cancellation

A cancel-based incomplete recovery is very similar to a full restore of the shutdown database execution. The difference is in the way the RECOVER command is executed; Specify the UNTIL CANCEL clause. This clause causes the recovery process to prompt you to confirm the suggested name of each redo log file that you want to apply. Therefore, during the recovery process, you are prompted to confirm the file name of the redo log that was archived or online, and you can either accept it or change it for each file name. When you reach the point in time for the recovery to stop, enter CANCEL instead of the file name. This will stop the recovery.
After you do this, you must open the database by using the Resetlogs option. The redo log sequence number needs to be reset because the database is currently performing another instantiation operation.
After you open the database, examine the messages in the alert log. In this way, you can learn whether the recovery was successful. To automatically apply the Redo log file during the recovery process, you can use the Sql*plus SET autorecovery on command, enter AUTO at the recovery prompt, or use the RECOVER AUTOMATIC command.

Incomplete recovery based on time and change

An incomplete recovery based on time and change is similar to a cancellation-based recovery, except that different criteria are used to specify the time to stop recovery. Time-based recovery uses the time specified in the command line of the RECOVER command to learn when to stop. Change-based recovery uses the SCN specified on the command line.
As with all incomplete recoveries, you must then use the Resetlogs option to open the database.

Perform incomplete recovery of user management: Steps

1. Close the database.

2. Restore the data file.

3. Load the database.

4. Restore the database.

5. Open the database using the Resetlogs option. Resetlogs force the SCN number of the log file to 1;

Execute the ALTER database open resetlogs.

-----------------------------------------------------------------------------------------------------

Full and incomplete recovery of Oracle database and execution of user management generational recovery