Use flashback database to implement flexible data rollback

Use flashback database to implement flexible data rollback

I wrote an article about using the flashback database feature to avoid repeated import of large amounts of data in the test environment, resulting in a waste of time and storage space.
The problem encountered today is more targeted and universal. In many cases, an independent environment is required for customer training and demonstration. Once the data in the environment is configured, generally, few changes are required. If you still have training or demonstration on the next day after the training, it is very difficult to get the original initialization data.
In this case, we can try to use the flashback database.
In the previous example, we shared that the time point or scn can be used to flash back to the database. In fact, there is also a common method, that is, the restore point is used for the flash back database operation.
Suppose that after the environment Initialization is complete, we can directly set a restore point. In the future, we don't have to worry too much about the time point or scn for the flash back operation.
Create restore point original_state_BASE guarantee flashback database;

After setting this restore point, we can use the following script to view the set recovery point.
Col NAME for a20
Col TIME for a35
Set lines 200
Col STORAGE_SIZE for a50
Select name, SCN, TIME, DATABASE_INCARNATION # DI, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE/1024/1024/1024
From v $ RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'yes ';
Name scn time di gua STORAGE_SIZE/1024/1024/1024
---------------------------------------------------------------------------------------------------------
ORIGINAL_STATE_BASE 4465165 17-APR-15 10.4820.1.000000000 AM 1 YES 0
ORIGINAL_STATE 4465180 17-APR-15 10.48.10.000000000 AM 1 YES. 68359375

 

After setting, it is easy to flash back for recovery.
Refer to the script below to quickly achieve the expected purpose.
Set echo on feed on
Set time on timing on
-Spoo logs/restore_flashback.log
Select name from v $ database;
SELECT current_scn FROM v $ database;
Col NAME for a20
Col TIME for a35
Set lines 100

Select name, SCN, TIME, DATABASE_INCARNATION # DI, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
From v $ RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = 'yes ';

Shutdown immediate;
Startup mount;
Flashback database to restore point ORIGINAL_STATE;
Alter database open read only;
Shutdown immediate;
Startup mount;
Alter database open resetlogs;
Spoo off

Of course, the principle of This rollback is flashback database, but it gives more practical significance in specific use cases.
Based on this, if you need to perform this rollback operation within a few days of a week based on your business needs, you can set it to crontab to run automatically, so you don't need to perform the rollback in the middle of the night.

This article permanently updates the link address: