ORACLE DATABASE RESTORE POINT
Description :
In this blog, we are going to see the Oracle database restore point and its types with the demo.
Normal Restore Point:
- A normal restore point enables you to flash the database back to a restore point within the time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
- A normal restore point can be dropped explicitly.
- The control files stores name of the restore point and the SCN.
Guaranteed restore point:
- A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
- Guaranteed restore point must be dropped explicitly by the user using the DROP RESTORE POINT command.
- Guaranteed restore point never ages out.
Normal Restore Point Demo:
Must DB in archive log mode.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Check available restore point:
SQL> select name from v$restore_point;
no rows selected
Create a normal restore point:
SQL> create restore point res_test;
Restore point created.
View the created restore point:
SQL> select name from v$restore_point;
NAME
——————–
RES_TEST
After the creation of the restore point creates a table.
SQL> create table test as select * from dba_users;
Table created.
Check the RVWR process status.
In normal restore point no background process are started.
[oracle@primary ~]$ ps -ef |grep -i rvwr
oracle 16948 14963 0 09:40 pts/2 00:00:00 grep –color=auto -i rvwr
Flashback the restore point:
SQL> flashback database to restore point res_test;
Flashback complete.
Guaranteed Restore Point Demo:
Create guarantee restore point:
SQL> create restore point res_test1 guarantee flashback database;
Restore point created.
RVWR back ground process can be started automatically.
[oracle@primary ~]$ ps -ef |grep -i rvwr
oracle 17014 1 0 09:41 ? 00:00:00 ora_rvwr_orcl
oracle 17060 14963 0 09:42 pts/2 00:00:00 grep –color=auto -i rvwr
[oracle@primary ~]$
Check the flashback status:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY
Automatically create flashback logs:
[oracle@primary ORCL]$ cd flashback/
[oracle@primary flashback]$ ls
o1_mf_jtotn2c3_.flb o1_mf_jtotn8d4_.flb
Drop restore point the logs will be removed.
SQL> drop restore point res_test1;
Restore point dropped.
[oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL/flashback
[oracle@primary flashback]$ ls
[oracle@primary flashback]$
Bounce database and Flashback database to restore point:
SQL> flashback database to restore point res_test2;
Flashback complete.
Reset the logs:
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from test1;
COUNT(*)
———-
45
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn:
https://www.linkedin.com/in/ramkumardba/
LinkedIn Group:
https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page:
https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter:
https://twitter.com/ramkuma02877110
Ramkumar’s Telegram:
https://t.me/oracleageant
Ramkumar’s Facebook:
https://www.facebook.com/ramkumarram8Like this:
Like Loading...
