Use flashback database to implement flexible data switching

Use flashback database to implement flexible data switching

The customer has a requirement today, because the development has encountered some difficulties in the production environment, and the problem needs to be reproduced in the test production environment. In this way, some data needs to be extracted from the production environment, the data volume may be relatively small, with a size of about 1 GB. The data needs to be loaded into the test production environment, or the diagram is clearer. We need to import some data from the production environment on the right side of the graph to the test production environment. The test production environment mentioned here is copied according to the structure of the production environment. The test environment has some test data, which may conflict with the data in the production environment.

As shown in the following figure, data conflicts may cause data loading problems.

Generally, we recommend that you back up the test production environment by using expdp, clear the data in the corresponding schema, and then import the data to reproduce the problem. After the problem persists, you can use backup to recover the data.
However, this inherent idea still has some problems.
The customer's feedback is as follows: the data volume in the corresponding schema is quite large, with a size of about 2 TB. It also takes a lot of space to back up data, export and import are both time-consuming and resource-consuming.
Owner sum (BYTES)/1024/1024
--------------------------------------------------
APP 2144666.63
This environment is borrowed for three days, so the high availability requirements of the test environment are not that high.
We can try to use the flashback database to meet this requirement.
The use of flashback database has some concerns and risks, such as the flash back time. If you do not consider it weekly, it will not be able to achieve the expected results.
By default, the database does not enable the flash back function. You need to enable the function and disable it after data recovery is completed. All these processes require the database to be stopped and the middleware needs to restart, need to coordinate with the development and testing part, whether to agree to this method.
After the database is flashed back, it is flashed back to the status before data is cleared. If you want to open the database, you need to use open resetlogs, in this case, the backup before this time point becomes invalid. Confirmation is also required to ensure that no unexpected business situations occur.
I got a reply soon. It seems that this method is also accepted by everyone. After all, it can save a lot of backup and data import and export. The operation is also relatively convenient.

Let's use the following script to simulate it. We create a table, enable the flashback database function, perform the truncate operation, import some new data, and then perform the flash back database operation to return the data before the truncate, enable the database.
Modifies the time for flash back retention. The default value is 1440 minutes, that is, 24 hours.
SQL> show parameter flashback
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_flashback_retention_target integer 1440
We have created a table with a size of about 2 GB, which can verify the growth of the flash log.
SQL> select segment_name, bytes from user_segments where segment_name = 'a ';
SEGMENT_NAME BYTES
-------------------------
AA, 2153775104

SQL> SELECT COUNT (*) FROM AA;
COUNT (*)
----------
18340352
We started to enable the flash back database function.
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 435224576 bytes
Fixed Size 1337044 bytes
Variable Size 272632108 bytes
Database Buffers 155189248 bytes
Redo Buffers 6066176 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL>
SQL> alter database open;
Database altered.
After it is enabled, a timestamp is obtained, which is used as the time point for us to complete the flash back.
Select distinct imestamp from dual;
Next we will do a cleanup job.
SQL> truncate table aa;
Table truncated.
We try to insert a part
SQL> insert into aa select * from all_objects;
71642 rows created.
SQL> commit;
Commit complete.
This data volume is much smaller than the original 2 GB.
Then we try to use the flash back database function to flash back to the status before deletion.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down

SQL> startup mount;
ORACLE instance started.
Total System Global Area 435224576 bytes
Fixed Size 1337044 bytes
Variable Size 272632108 bytes
Database Buffers 155189248 bytes
Redo Buffers 6066176 bytes
Database mounted.
SQL> Flashback database to timestamp to_timestamp ('2017-04-17 17:42:29 ', 'yyyy-mm-dd hh24: mi: ss ');
Flashback complete.
Open in read-only mode for verification to ensure that there is no problem with the flash back.
SQL> alter database open read only;
Database altered.

SQL> conn n1/n1
Connected.
SQL> select count (*) from aa;
COUNT (*)
----------
18340352

The data is back.
Check the size of the flash back log. We can see that there are still few.
Total 16040
-Rw-r ----- 1 ora11g dba 8200192 Apr 17 o1_mf_bm1oc2qt _. flb
-Rw-r ----- 1 ora11g dba 8200192 Apr 17 o1_mf_bm1ofwb7 _. flb
[Ora11g @ oel1 flashback] $ pwd
/U02/ora11g/flash_recovery_area/TEST11G/flashback
[Ora11g @ oel1 flashback] $

In addition, for the database flash function, if disabled, the flash log is automatically cleared.

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