{"id":1977,"date":"2020-10-22T07:00:00","date_gmt":"2020-10-22T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1977"},"modified":"2024-06-05T20:42:20","modified_gmt":"2024-06-05T17:42:20","slug":"pos-kanoyme-flashback-mia-vasi-dedomenon-tis-oracle","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-flashback-mia-vasi-dedomenon-tis-oracle\/","title":{"rendered":"How do we return an Oracle database to the previous state of a restore point (Flashback Database to guaranteed restore point)"},"content":{"rendered":"

In the article we will analyze how we can make use of the function flashback database <\/strong>in guaranteed restore point<\/strong> in its database Oracle<\/strong>.<\/p>\n\n\n\n

The flashback database<\/strong> it belongs to the category of data protection and provides the ability to freeze the image of the database at a point in time so that we can quickly return to the image that the database had at that point.<\/p>\n\n\n\n

<\/p>\n\n\n\n

But why would we want to flashback a database?<\/h2>\n\n\n\n

With its operation flashback database<\/strong> we can keep one guarantee restore point<\/strong> before performing a major upgrade to the base. This action takes effect in case something goes wrong and is needed rollback <\/strong>not need to be done restore <\/strong>the entire base.<\/p>\n\n\n\n

<\/p>\n\n\n\n

How do we create a guarantee restore point?<\/h2>\n\n\n\n

To begin with, we should define the size and the location we want to have flash recovery area<\/strong>. From its activation flashback database <\/strong>and then they will be created recovery files<\/strong>. As changes are made to the database, the size of these files will increase according to the size of the data that has changed.<\/p>\n\n\n\n

To set the size in gigabytes<\/h5>\n\n\n\n
alter system set DB_RECOVERY_FILE_DEST_SIZE ='megethos'G scope=both; <\/pre>\n\n\n\n
To define where the recovery files will be saved<\/h5>\n\n\n\n
alter system set db_recovery_file_dest='path';<\/pre>\n\n\n\n
At this point we have to check that the database works in archived log mode, that is, that the logs are kept as archived.<\/p>\n\n\n\n
select log_mode,flashback_on from v$database;<\/pre>\n\n\n\n
Enabling flashback mode is optional. If it is not activated, however, to restore the restore point, the base should be mounted and not open.<\/em><\/p>\n\n\n\n
To activate the flashback<\/h5>\n\n\n\n
alter database flashback on;<\/pre>\n\n\n\n
To create the restore point<\/h5>\n\n\n\n
create restore point ONOMA guarantee flashback database;<\/pre>\n\n\n\n
To see where it is and how much space is still free in the flash recovery area<\/h5>\n\n\n\n
select name,space_used\/1024\/1024\/1024 from v$recovery_file_dest;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How to restore the base to the restore point<\/h2>\n\n\n\n
We should look at the restore points we have and find its name.<\/p>\n\n\n\n
select name,scn,time from v$restore_point;<\/pre>\n\n\n\n
In case of single instance<\/h5>\n\n\n\n
shutdown immediate;\nstartup mount;\nflashback database to restore point ONOMA ;                 \nalter database open resetlogs;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
In case of RAC (Real Application Cluster)<\/h5>\n\n\n\n
In case we have multiple instances for database (RAC) <\/strong>the base should go down and only go up an instance<\/strong> her. Accordingly after the flashback <\/strong>the instance should be downloaded and the database with all its instances should be uploaded:<\/p>\n\n\n\n
srvctl stop  database -db db1 -stopoption immediate\nsrvctl start instance -db db1 -instance instance1 -startoption mount\n\nflashback database to restore point ONOMA;  \nalter database open resetlogs;\n\nsrvctl stop  database -db db1 -stopoption immediate\nsrvctl start database -db db1<\/pre>\n\n\n\n
If we have many services they should be uploaded:<\/p>\n\n\n\n
srvctl start service -d db1 -s service_tade<\/pre>\n\n\n\n
After completing the restore we can delete the restore point:<\/p>\n\n\n\n
drop restore point ONOMA;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
In case of Data Guard (with or without Fast Start Failover)<\/h5>\n\n\n\n
In case we have infrastructure Data Guard<\/strong> we should follow the below steps.<\/p>\n\n\n\n
We check on all nodes that the restore point has been created, when creating it on the primary it also creates it on all secondary nodes:<\/p>\n\n\n\n
select name,space_used\/1024\/1024\/1024 from v$recovery_file_dest;\nselect name,scn,time from v$restore_point;<\/pre>\n\n\n\n
In case we have it enabled fast_start failover<\/code> we should disable it:<\/p>\n\n\n\n
dgmgrl \/\ndisable fast_start failover force;<\/pre>\n\n\n\n
In the primary node <\/strong>we run the following to do flashback <\/strong>at the restore point while the database is in mount state:<\/p>\n\n\n\n
sqlplus \/ as sysdba\nshutdown immediate;\nstartup mount;\nflashback database to restore point NAME;\nalter database open resetlogs;\n\n<\/pre>\n\n\n\n
After completing the restore we can delete the restore point:<\/p>\n\n\n\n
drop restore point NAME;<\/pre>\n\n\n\n
Finally we activate it again fast_start failover<\/code> if we had it enabled before the actions:<\/p>\n\n\n\n
dgmgrl \/\nenable fast_start failover;<\/pre>\n\n\n\n
Caution!!!<\/strong><\/p>\n\n\n\n
In case the standby database is open to read only mode<\/strong> a manual restart should be done:<\/p>\n\n\n\n
sqlplus \/ as sysdba\nshutdown immediate;\nstartup mount;<\/pre>\n\n\n\n
And after synchronizing in a second time, open again in read only mode:<\/p>\n\n\n\n
alter database open read only;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
In the next article we will see how we can flashback a database at a specific point in time without using a guarantee restore point.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n