Skip to Main Content
  • Questions
  • Why we use alter database open resetlogs after flashback database to guaranteed restore point

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ashish.

Asked: February 13, 2017 - 6:51 am UTC

Last updated: February 13, 2017 - 8:34 am UTC

Version: oracle database 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Team,

Below how i created a guaranteed restore point.

=========================================================================================

SQL> select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE FLASHBACK_ON LOG_MODE
--------- ---------------- -------------------- ------------------ ------------
ASHISH PRIMARY READ WRITE YES ARCHIVELOG

SQL>
SQL> CREATE RESTORE POINT TEST_02112017 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>
SQL> SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name FROM v$restore_point;


SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
---------- --------------------- --- ------------ -------------------------------- ------------------------------
1.4407E+13 3 YES 589299712 11-FEB-17 10.48.06.000000000 AM LOAD_TEST_02112017

################## After testing completes #######################################

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to restore point TEST_02112017;

Flashback complete

SQL> alter database open resetlogs;

=====================================================================================================================


Could you please help me understand? Why we need to open database with resetlogs after we flashback database to a guaranteed restore point.

Is there a way to open database without resetting the incarnation and logfile sequence?

Since 10g onwards all previous backup will remain valid after resetlogs. How this works?

Thank You ,
Ashish Francis

with LiveSQL Test Case:

and Connor said...

We *have* to reset the logs because the logs are not "rewound" as part of flashback.

Lets say you took a restore point at 9am.

At 11am, when you want to run a flashback:

- the datafiles are at 11am
- the redo logs are at 11am

After the flashback processing, the datafiles are now at 9am, and redo logs are now of no use - they no longer reflect the state of the database. Hence the need to reset them.

Since 10g, we started taking into the account the database incarnation. Before this, when you did a resetlogs recover, we couldnt really digest enough information from historical archivelogs to know if they were applicable to the database or not. The incarnation information lets us do that now.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.