• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » how to check restore point in oracle

how to check restore point in oracle

July 10, 2022 by techgoeasy Leave a Comment

In this post, we will discuss, what is restore point in an oracle, how to check restore point in oracle

Table of Contents

  • What is a Restore Point in Oracle?
  • Guaranteed restore points
  • Normal Restore Points
  • how to check restore point in oracle
  • How to create restore point in Oracle
  • How to create guaranteed restore point in Oracle
  • How to drop restore point

What is a Restore Point in Oracle?

  • A restore point is a name assigned to a system change number (SCN) in Oracle corresponding to the time of the creation of the restore point.
  • A restore point can be used to flash a table or the database back to the time of the creation of the restore point without the need to determine the SCN or timestamp.
  • The restore point information is stored in a control file
  • There are two types of restore points in the Oracle Database: a normal restore point and a guaranteed restore point.

Guaranteed restore points

  • It let you revert to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
  • They used a good amount of space in the flash recovery area as flashback logs are not deleted and can get accumulated over time. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration
  • Guaranteed restore points never get deleted automatically, as normal restore points do. You have to drop them explicitly
  • It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

Normal Restore Points

  • A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter
  • The database automatically drops them when the maximum number reached out
  • You can explicitly drop them using the drop restore point command

how to check restore point in oracle

You can check the restore point in the oracle database using the below query

sqlplus / as sysdba
select * from v$restore_point;

How to create restore point in Oracle

Before creating the restore point, you should have the following

  1. The database is an archive log mode
  2. Flashback mode is ON
  3. FRA must be set for the Database

You can create using the below query

sqlplus / as sysdba
create restore point <restore point name>;

How to create guaranteed restore point in Oracle

Before creating the restore point, you should have the following

  • The database is an archive log mode
  • Flashback mode is ON. It is not mandatory. For Guaranteed restore point, it will put the database automatically in flashback mode
  • FRA must be set for the Database

You can create using the below query

sqlplus / as sysdba
create restore point <restore point name> guarantee flashback database;

How to drop restore point

You can drop the restore point using the below query. This will be valid for both Normal and guaranteed restore point

sqlplus / as sysdba
drop restore point <restore point name>

When you are dropping a guaranteed restore point, it might take time as it has to delete all the accumulated flashback logs

Related Articles

Oracle Flashback Database: Oracle Flashback Database is a new feature from 10g onwards which uses past images to back out changes .it is enabled using alter database flashback on
Flashback Database queries: Check out this post to find the top useful oracle Flashback Database queries, how to enable flashback, how to perform flashback
recover table using flashback standby database: Detailed steps to recover table using flashback standby database. Accidental delete,drop, truncate can be recovered using this technique

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us