Oracle Database full recovery and Incomplete recovery, and perform user management for multiple generations recovery, oracle user management

Oracle Database full recovery and Incomplete recovery, and perform user management for multiple generations recovery, oracle user management

Comparison between full recovery and Incomplete recovery:

1. Full recovery: restores the database to the latest status, including all submitted data changes until the request is restored.

2. Incomplete recovery: restores the database to the past time point specified before the request recovery operation.

 

1. Complete recovery process
The following steps describe the actions to be taken during full recovery:

1. Restore corrupted or lost files through backup.

2. Apply Incremental backup, archive redo log files, and online redo log files as needed. Apply the redo log changes to the data file until the current online log is reached, and re-enter the latest transaction processing. The restored block is generated throughout the process. This is called roll-forward or cache restoration.

3. At this time, the restored data file contains submitted and uncommitted changes.

4. Restore blocks are used to roll back any uncommitted changes. It is also called transaction recovery.

5. At this time, the data file is in the restored State and consistent with other data files in the database.

Ii. Incomplete recovery process:
 

Incomplete recovery or database restoration at a time point use backup to generate a database of a non-current version. That is to say, no redo records generated after the latest backup will be applied. This type of recovery is performed only when absolutely necessary. To perform Incomplete recovery, You need:

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

All archived logs from the backup time to the specified recovery time
The incomplete recovery process is listed below:

1. Restore data files from backup: If the recovery point objective is not very new, the backup used may not be the latest. This requires the OS command or the RMAN RESTORE command to copy files.
2. Use the RECOVER command: redo the log file application from the archive, and include as much data as possible to restore the point objective.

3. restored status: At this time, the data file contains some submitted transactions and uncommitted transactions, because redo can contain uncommitted data.

4. Run the alter database open command: the DATABASE is opened before the application restores the block. This is to provide higher availability.

5. Restore data: When an application is redone, it also supports restoring data files. In this way, restoration can be applied to data files to restore any uncommitted transactions. This is the next step.

6. process completed: At this time, the data file has been restored to the selected time point.

If recovery is required and archive logs containing transaction processing are lost, the transaction processing occurs between the backup creation time used for restoration and the target SCN, the time point recovery is the only option. If no log is lost, there is no record for updating the data files during this period. The only option is to restore the database from the backup time point until the time point allowed by the intact archive log series, and then use the RESETLOGS option to open the database. All or subsequent changes in the lost redo log file will be lost.

 

Backup and recovery types:

Database backup and recovery types include:

User-managed: RMAN is not used.
-- Use OS commands to move files -- DBAs need to manually maintain backup activity records

For Server Management: RMAN

1. Perform User-managed database backup
You can use the OS command to create a copy of the data file to back up the database. The operation process depends on whether the database is in ARCHIVELOG mode. If yes, you can place each tablespace in backup mode before copying the tablespace data file to make the database open and available. Otherwise, you must close the database before copying data files.

1) manually back up the NOARCHIVELOG database:

By disabling the database and copying all data files and control files to the backup directory, you can back up the NOARCHIVELOG database as a whole. Because the file copy operation is completed 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 file 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 back up the ARCHIVELOG Database

If the database is in ARCHIVELOG mode, you do not have to disable the database before copying files. Eventually, an inconsistent Backup will be obtained, but the application will restore the data to the consistent state after redoing the data.

Start the backup mode:
Before copying a data file, you must place each data file in backup mode. Use the begin backup clause of the alter tablespace and alter database commands to perform this operation. The syntax for each command is as follows:

Alter tablespace <tablespace> begin backup;
-- The alter tablespace command only affects the data files that belong to the TABLESPACE.

Alter database begin backup;
-- Alter database affects all data files in the DATABASE.

End backup mode:

It is important to remove the data file from the backup mode. When the database is shut down, no data files are in backup mode. If you try to shut down the database in this status, you will receive an error. In addition, the backup mode will generate additional redo data, resulting in additional load on the system. If you do not manually back up data files, there is no reason to keep any data files in backup mode.

Specific operations:

Back up data files:
SQL> select file_name, tablespace_name from dba_data_files;
-- View all data file locations and tablespaces

SQL> ALTER TABLESPACE users BEGIN BACKUP;
-- Place the tablespace in backup mode

$ Cp $ ORACLE_HOME/oradata/orcl/users *. dbf/u02/backup/datafile
-- Copy the data file of the tablespace to the backup location

SQL> ALTER TABLESPACE users END BACKUP;
-- Exit the tablespace from the backup mode.

Backup control files can be stored in the following ways:

(1). Create an image copy and write it to the file with the specified name:

SQL> ALTER DATABASE BACKUP CONTROLFILE
'/U01/backup/controlfile. Bak ';
 

Database altered.

(2) generate a script to recreate the control file and write it to the tracking file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 

Database altered.
-- Re-create the Control File Script in $ ORACLE_BASE/diag/rdbms/prod/PROD/trace

View the generated script file and you will find two ways to create a control file:

Noresetlogs archivelog indicates that the log files are in good condition, and you do not need to resetlog to restart the database;

Resetlogs archivelog; log files are damaged and lost, and cannot be completely restored. The log group number is cleared;

2. Execute user-managed database recovery

1) execute user-managed database full recovery

Full recovery of user-managed databases:

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

V $ RECOVER_FILE: check which files need to be restored.

V $ RECOVERY_LOG: view the archive logs required for recovery.

In some cases, the instance is automatically closed when the file that belongs to the SYSTEM tablespace is damaged. When some data files have problems, even if the Instance remains running, you can determine that it makes no sense to keep the database running; there are too many affected database objects. In this case, close the database for restoration.

If the database is still open, you can query the V $ RECOVER_FILE view to find out which data files need to be restored, and query V $ RECOVERY_LOG to find out which archive logs are needed. This indicates which files (if any) need to be restored from the backup ).
Close the database. Investigate medium faults and determine the causes. Fix the problem so that files can be restored from the backup.

For example, you may need to change the disk drive.
Now, you can use the RECOVER command to perform recovery. Limit the recovery scope to the required range, such as data files or tablespaces. You can restore the entire database if needed. Then, open the database.

SQL> SELECT file #, error FROM v $ recover_file;
-- Determine the data file to be restored

SQL> SELECT archive_name FROM v $ recovery_log;
-- Determine the archive log files required for recovery

Determine recovery-Related Files

If the database is still open, follow the instructions below to query the file. Otherwise, start the instance and load the database for query.

To determine which data files need to be restored, query the V $ RECOVER_FILE view. The ERROR column specifies the reason for file recovery. If this column has any value other than offline normal, it needs to be restored.

To view the overview of all affected data files and tablespaces, you can add V $ DATAFILE and V $ TABLESPACE to this query.
The following 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 for recovery. If the list shows that some files have been removed from the default archiving log location, you must restore them to a specific location and then restore them.
Close the database after record the results of these queries.

Restore recovery-Related Files

After determining which data files and archive log files are required, restore them to the corresponding disk location. By copying a 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 you need to restore any archived logs, check whether these logs are still on the default disk of the archived logs. These logs may not be in the default location,
For example, you have moved them to a tape or another disk drive. If logs have been moved, you need to restore them to the default location or temporary location for archiving logs. If there is sufficient space in the default location (specified by the LOG_ARCHIVE_DEST_1 initialization parameter), restore the log at this location. Otherwise, the log can be placed in another disk location. During restoration, you can specify the backup location to find the archived log file.
To move a data file, you must record this situation in the control file. You can perform this operation 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'
'/Newdisk/ORCL/datafile/survey01.dbf ';
-- You must first start the instance and load the DATABASE before executing the alter database rename file command.

Load the database and bring all data files online (if this operation has not been performed ). You can check the status of each data file by querying the V $ DATAFILE view. Use the following command to bring data files online:
 
SQL> ALTER DATABASE DATAFILE '/newdisk/ORCL/datafile/survey01.dbf' ONLINE;
 

Application redo data

At this time, the data file has been restored to a time point in the past. The archived log file has also been restored to its default location or another location (for this recovery only ). You are ready to perform real recovery steps, which means that the application has been redone and the data file has been restored to the latest SCN.
Run the SQL * Plus RECOVER command to perform this operation.
If the AUTOMATIC option is not specified, the system prompts you to specify each redo log file to be applied. This improves the control of the recovery process. Generally, 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. Provides a directory for storing these files. The restoration process searches for files in this directory.
Finally, open the database. At this time, it is completely restored.

SQL> RECOVER AUTOMATIC FROM '/u01/arch_temp' DATABASE;
-- Use the RECOVER command to redo data

SQL> ALTER DATABASE OPEN; -- OPEN the DATABASE

Perform full recovery on the opened database

If a media fault occurs when the database is opened, 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 will not cause them to go offline, but an error will be returned to the user who sends the query.
Similar to restoring a closed database, you must first query the files to be recovered and archive logs. Then, take all tablespaces that contain damaged data files offline. Run the following command to complete the operation:
SQL> ALTER TABLESPACE survey OFFLINE TEMPORARY;
Using the TEMPORARY option causes Oracle to perform checkpoint operations on all online data files belonging to the tablespace. Data files that have undergone checkpoint operations do not need to be restored after they are brought online, because they are up-to-date for the latest SCN that may affect the processing of any transactions. Although the data file must be available when the command is run, this option is more suitable. The problem may be temporary. You can bring the tablespace online without any errors.
Check the media to identify the cause of the problem. You can use the DBVERIFY utility to perform this operation. If the file is permanently damaged, restore and restore as described in restoring and disabling the database. After the restoration and recovery steps are completed, all tablespaces should be brought online again.

2. Incomplete recovery of user management

In the following cases, you can restore the database to the past time point:

You want the database to be In the status before a user error or management error occurs.

The database contains damaged blocks.

The database cannot be fully restored because some redo log files are missing.

You want to create a test database that has been in the past for some time.

Data Files and one or more Unarchived and redo log files are lost.

You can specify when to stop the application from redoing data by using the following methods:

1). Specify the stop time
SQL> RECOVER DATABASE
TIME '2017-12-14: 12: 10: 03 ';

2 ).
Specify the stopped SCN
SQL> recover database until change1487389;

3). Issue the CANCEL command during recovery.
SQL> RECOVER DATABASE UNTIL CANCEL;

Incomplete recovery of execution user management

Run the following command to perform Incomplete recovery:

RECOVER [AUTOMATIC] DATABASE option

The meanings of the options are as follows:

AUTOMATIC: automatically apply archiving 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

The incomplete recovery based on cancellation is very similar to the full recovery performed to shut down the database. The difference lies in the method of executing the RECOVER command; specify the until cancel clause. This clause prompts you to confirm the recommended name of each redo log file to be applied during the recovery process. Therefore, during the recovery process, the system prompts you to confirm the archived or online redo log file name, And you can accept or change it for each file name. Enter CANCEL instead of accepting the file name when the stop recovery time is reached. This will stop the recovery.
After you complete this operation, you must use the RESETLOGS option to open the database. Because the database is currently performing another instantiation operation, you need to reset the redo log serial number.
Open the database and check the messages in the warning log. In this way, you can know whether the restoration is successful. To automatically apply the redo log file during the recovery process, run 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 changes

The incomplete recovery based on time and change is similar to the cancel-based recovery. The difference is that different standards are used to specify the stop recovery time. Time-based recovery uses the time specified in the RECOVER command line to know when to stop. Change-based recovery uses the SCN specified in the command line.
As with all incomplete recovery, you must use the RESETLOGS option to open the database.
 

Incomplete recovery of user management: Step

1. Shut down the database.

2. Restore the data file.

3. Load the database.

4. Restore the database.

5. Use the RESETLOGS option to open the database. Resetlogs forcibly sets the SCN Number of the log file to 1;

Execute alter database open resetlogs to OPEN the DATABASE.

 

 

Bytes -----------------------------------------------------------------------------------------------------

 

 

 

 

 

 

What is the status of the oracle database when it is not completely restored?

Mout status.

Incomplete Database recovery in oracle

I haven't played it for a long time, and I don't know if it's right. Talk about your understanding and learn from each other.

1. Why do I need to restore the tablespace by only using all the data files for backup files of all control files?
The following is a hypothetical example.
Nine o'clock A.M.. The database is normal. Back up the database.
Ten o'clock A.M., an error occurred while deleting a tablespace.
Eleven o'clock A.M.. The error is deleted and needs to be restored.

At, the control file records the information of each of your data files.
At, The tablespace is accidentally deleted, causing one or more data files to be deleted. At the same time, the control file does not record the file information.
At, to restore the accidentally deleted tablespace, you must locate the control file. At, the control file records the number of data files and the name of each tablespace.
If you only have a bunch of data files, Oracle cannot automatically identify these files. Which tablespace does it belong.

2. In this example, if the database is not completely recovered, it is a time before it is restored to the database.
The SCN of the complex control file and data file goes forward. Is it true that the log file and the system's changed SCN are moving forward? SCN?
You can proceed. Or I have not understood about incomplete database recovery. Please help me solve it !!

SCN = SYSTEM CHANGE NUMBER
That is, after the database COMMIT, the SCN will continuously increase from small to large.
Or the previous example

At nine o'clock A.M., the database is normal. Back up the database if the database SCN = 500 at this time.
At ten o'clock A.M., a tablespace is deleted incorrectly. Assume that the database SCN = 600 at this time.
At eleven o'clock A.M., an error was found to have been deleted. You need to restore the database if SCN = 700 at this time.

In this case, you need to restore the database to the first SCN before deleting the tablespace, that is, the SCN = 599.