Restore Point and flash back database

Restore Point and flash back database

Flash back database and restore origin are two types of data recovery-related functions, they are used to replace restoration by time point to Cancel Changes to the database. Flashback allows you to roll back the entire database and use a specified time window to undo changes to the database. This is similar to database restoration by time point.

Restore Point provides the ability to flash back to the database. It is another recovery operation. The protected Restore Point provides a complete function to flash back to the database under specific circumstances, allowing you to select an SCN and forcibly require the database to flash back to be available for this SCN.

Recovery points and flash back databases can be used separately or together. In both cases, the flashback database command or flashback database Statement of RMAN actually restores the database to the state corresponding to a specified SCN. For example:

Flashback database to restore point 'before _ upgrade ';
Flashback databasae to scn 202381;

Flash back database
Flash back to the database, which can be completed through the flashback database command of RMAN and the flashback database Statement of SQL * Plus. The ability to quickly restore the database from logical data errors or user errors.

It is similar to the traditional restoration by time point, and can restore the database to a certain time point in the past. Flash back database recovery is much faster than by time point, because flash back database does not need to restore data files from the backup and apply the corresponding changes from the archived redo log files.

The flashback database can be used to cancel unnecessary changes to the database. The data file is intact. This includes restoring the database to the status corresponding to the previous database avatar. This will cancel the open resetlogs operation.

The flashback database uses its own log mechanism, which creates a flashback log in the flashback area. You can use the flashback database only when the flashback log is available. Therefore, if you want to use this function, you must set the database so that it can create the relevant flash log.

In order to enable the flashback database, you need to set the flashback zone and the flashback retention policy target to specify the ability to restore the database to a certain time point in the past.

Starting from enabling the flashback database, the database will copy the changed data blocks in each data file to the flashback log at regular intervals. These data block images can be used to reconstruct the data file content later.

When the database uses a flash back database to restore the database to some of the past target time points, each data block that changes since the restoration time point will copy the data block corresponding to the target time point from the flash back log. Then, redo logs starting from the time when the data block is copied to the Flash log are applied.

Note: The redo logs corresponding to the Flash log must be available throughout the time span.

Flash back database window
There is enough flashback log data between the SCN within a certain range to support the flashback database command. This is called the flashback to the database window. If the space in the flash back area is too small, you may delete expired flash back logs by setting a retention policy to allocate space for new flash back log files. Based on the size of the flash back area, other backup files that must be retained and the required flash back log data may cause a shorter time to flash back to the Database window than the flash back retention target.

Note: The flashback retention goal is a goal, not an absolutely protected target that can be used to perform the flashback database.

If the size of the flash back area cannot store the flash back logs and other files required to meet the retention policy, such as archiving and redo logs and other backups. In order to store the flash back logs of other files starting from the earliest SCN, they may be deleted due to space problems.

In the flash back database window, the earliest SCN in the available flash back log cannot be expanded forward. Flash back logs cannot be backed up to directories outside the flash back area. Therefore, in order to add available flash back logs to meet the requirements of the flash back data window, the maximum storage space available in the flash back area is required.

There are also some operations, such as deleting tablespaces or shrinking data files, which cannot be undone by flashing back data. After these operations are performed, the flashback window will immediately re-calculate from these operations.

If the flash-back database window does not meet the requirements of the flashback database, you can restore the database by time point to perform similar tasks to the maximum extent.

Using Protected restore points is the only method that ensures that you can use the flash back database to restore the database to a specified time point or flash back window.

Restore Origin
You can specify a time point or SCN as the restore point name to create a restore point. This label or alias is used to specify the SCN when executing a command with a restore point clause.

If you may cancel any operations you perform, you can create a normal restore point. The Restore Point name and SCN are recorded in the control file. If you need to use a flashback database, a flashback table, or a restoration by time point, you can use a Restore Point name to indicate the target time to replace a time expression or SCN. Define a normal restore point before executing a possible Undo operation to eliminate the need to manually record SCN, or find the correct SCN when executing a flash query.

Normal recovery is very lightweight. The control file can contain thousands of normal recovery points without affecting the database performance. Normal restore points are eventually removed from the control file rather than manually deleted, so they do not need to be maintained.

Restore the origin
In the following context, the Restore point is used to specify the target SCN:
1. RMAN recover database and flashback database commands

2. flashback database statement in SQL * Plus

Note: Generally, protected restore points can be used as the SCN alias in any operation that uses normal restore points. Unless otherwise specified, the use of normal restore points also applies to protected restore points.

Protected Restore Point
Like normal restoration, protected restoration points can also be used as the SCN alias in restoration operations. However, they also provide specific features for the flash back database.

Creating a protected restore point in a specific SCN can force the database to be restored to the state of the SCN by executing the flash back database, even if the database does not enable the flash back log. If the flashback log is enabled, the policy of creating a protected restore point to force the flashback Log retention policy can meet any time point after the database is flashed back to the protected Restore Point.

Protected recovery points can be used to restore the entire database to a known good time point, provided that there is sufficient space in the flash back area to store the flash back log. When a database is flashed back, direct path insertion that is affected by nologging can also be restored using protected restore points.

Protected restore points replace storage snapshots
In practice, protected restore points can effectively replace storage snapshots. They are usually used to protect databases before some dangerous operations, such as large-scale updates and application patching, or upgrade. Compared with creating a snapshot or copying a database to perform these operations, you can create a protected restore point and then perform these dangerous operations, as long as only necessary flash logs are retained.

Logs that flash back to the database and are protected for recovery
Logs that flash back to the database and are protected for restore are based on the data block image captured before the application is changed, therefore, these images can be used to restore the data file to the status corresponding to the previous time point when the flashback database command is executed.

The main difference between a normal flash log and a protected restore point is whether the log is deleted due to the pressure on the available space in the flash back area. These differences affect log space usage and database performance.

Whether or not to enable logs for the flash back database and protected restore points depends on your recovery objectives, as well as the impact on performance and the space usage of these functions.

Use of protected recovery points and flash back space
When creating a protected restore point, you must monitor the status of the available space in the flash back area, whether or not the full flashback database log is enabled. If a file is required to meet the protected restore point, the deletion from the flash back area is not met. Therefore, the retained policy of the flash back log is the same as that of other files that are required to meet the protected requirements, which may completely remove the flash back zone.

Note: When the reserved policy cannot delete any files and the space is used up, the database is hang in many cases.

Protected Restore Point logs when flash back logs are disabled
If a protected restore point is created when the database log is disabled, the data block of the data file is changed for the first time after the protected restore point is created, the image of the data block is stored in the Flash log before the change. The Flash log can therefore restore the modified data block to the status when the protected recovery point is created. However, subsequent modifications to the same data block will not be recorded in the log, unless another protected restore point is created before these modifications.

This log method has the following important effects:
1. available block images can restore the data file content to the status corresponding to the protected restore point when using the flashback database, but cannot use the flashback database to restore the database to the protected Restore Point and the current A time point between time. Only when logs are enabled for the flash back database. If you need to restore the database to an intermediate time point, you can only use the database to restore by time point.

2. Because the change of each data block is recorded only once, the space used by logs with protected recovery points when the flash log is disabled is smaller than that used by normal flash logs. You can maintain protected recovery points by day or week without having to pay attention to the growth of flashback logs after the database is enabled. The performance of logs that record protected recovery points is very small when flash back database logs are disabled.

If you mainly restore the database to the time point when the protected restore point is created, it is more effective to disable the flash back database log to only use the protected Restore Point. For example, if you plan to upgrade the application on the production database server and create a protected restore point before the upgrade starts, you can use flashback database to cancel the change if the upgrade fails, instead of restoring from the backup.

Use protected Restore Point flash back database logs
If the flashback database is enabled and one or more protected restore points are defined, the normal flashback log execution of the database may affect the performance. The flash back area may generate a large amount of space Pressure Based on the working mode of the database. However, unlike normal flashback database logs, the flashback zone always needs to retain the flashback logs required to restore the database to the oldest protected restoration point using the flashback database. Flash back logs cannot be deleted due to space pressure if they meet the protected restore point requirements.

In this case, the flashback database can restore the database to any time point in the Flash window and the status corresponding to a specific protected Restore Point, however, you must monitor the space usage in the flash back area.

Normal use and protected Restore Point

Requirements for using protected restore points
To support the use of protected restore points, the database must meet the following requirements:
1. The compatible parameter must be set to Version 10.2 or later.

2. The database must run in archive mode. The flashback database operation restores the database to a protected restoration point. Archive redo logs that have been used until the Restore point are restored.

3. You must set a flash back area. The protected Restore Point uses a mechanism similar to the flash back log. Oracle must store the logs to be used in the flash back area.

4. if flash back is not enabled, the database must be in the mount state when the first protected restoration snack (or if all previously created protected restore points have been deleted) is created, it cannot be open.

Note: there are no special requirements for normal restore.

Create normal and protected restore points
To create a normal or protected restore point, use the create restore point statement, provide a name for the restore point, and specify whether the restore point is protected or normal (default ).

When creating a restoration point, the database can be in the open or mount state. If it is in the mount status, it must be completely closed (except for the physical standby database ).

The following command is used to create a normal restore point
SQL> create restore point before_truncate;

Restore point created.

The following command is used to create a protected Restore Point
SQL> create restore point before_truncate guarantee flashback database;
Create restore point before_truncate guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'before _ TRUNCATE '.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.

According to the error message, we can see that when the flashback database is not enabled in the database, the protected restore point can only be created in the mount state.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> create restore point before_truncate guarantee flashback database;

Restore point created.

SQL> col name for a20
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
2 GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
3 from v $ RESTORE_POINT;

Name scn time DATABASE_INCARNATION # GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
------------------------------------------------------------------------------------------------------------------------------
BEFORE_TRUNCATE 2849315 27-JAN-15 02.40.56.000000000 PM 2 YES 8192000

 

Show restore Origin
To view the currently defined restore point, you can use v $ restore_point to control the File View:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
From v $ RESTORE_POINT;

SQL> col name for a20
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
2 GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
3 from v $ RESTORE_POINT;

Name scn time DATABASE_INCARNATION # GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
------------------------------------------------------------------------------------------------------------------------------
BEFORE_TRUNCATE 2849315 27-JAN-15 02.40.56.000000000 PM 2 YES 8192000

 

You can see the name of each Restore Point, SCN, time, and the serial number of the database corresponding to the Avatar when the Restore point is created. Is it a normal restore point or a protected Restore Point, and the space used by the restoration point in the flash back area to provide sufficient information to support the flash back database operation.

You can query only the protected Restore Point Information:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
From v $ RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'yes ';

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
2 GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
3 from v $ RESTORE_POINT
4 WHERE GUARANTEE_FLASHBACK_DATABASE = 'yes ';

Name scn time DATABASE_INCARNATION # GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEFORE_TRUNCATE 2849315 27-JAN-15 02.40.56.000000000 PM 2 YES 8192000

 

For normal restore points, storage_size is 0. The protected Restore Point storage_size indicates the space in the flash back area consumed by logs to ensure that flashback database operations can be performed on the Restore Point.

The storage_size of the following normal restore points is indeed 0:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION #,
2 GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
3 from v $ RESTORE_POINT
4 WHERE GUARANTEE_FLASHBACK_DATABASE = 'yes ';

Name scn time DATABASE_INCARNATION # GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEFORE_TRUNCATE 2849315 27-JAN-15 02.40.56.000000000 PM 2 YES 8192000

 

SQL> select count (*) from tt;

COUNT (*)
----------
51281

SQL> truncate table tt;

Table truncated.

SQL> select count (*) from tt;

COUNT (*)
----------
0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 1273516 bytes
Variable Size 138412372 bytes
Database Buffers 184549376 bytes
Redo Buffers 2920448 bytes
Database mounted.
SQL> select open_mode from v $ database;

OPEN_MODE
----------
MOUNTED

SQL> select flashback_on from v $ database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> flashback database to restore point before_truncate;

Flashback complete.

 

SQL> alter database open;
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select count (*) from tt;

COUNT (*)
----------
51281

Oracle 11g flashback Data Archive (flash back Data archiving)

Oracle flashback flash back Mechanism

Flashback table quick recovery of accidentally deleted data

Oracle backup recovery: flashback flash back

[Oracle] use of the flashback Function

 

For more details, please continue to read the highlights on the next page:

1 2 Next Page