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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to determine the required archivelog files needed for a guaranteed restore point before running flashback database

by admin

Question: How can you determine the required archivelog files needed for a guaranteed restore point before running the flashback database?

The following query should give this information:

SELECT DISTINCT al.thread#, al.sequence#, al.resetlogs_change#, al.resetlogs_time
FROM v$archived_log al,
     (select grsp.rspfscn               from_scn,
             grsp.rspscn                to_scn,
             dbinc.resetlogs_change#    resetlogs_change#,
             dbinc.resetlogs_time       resetlogs_time
        from x$kccrsp grsp,  v$database_incarnation dbinc
       where grsp.rspincarn = dbinc.incarnation#
         and bitand(grsp.rspflags, 2) != 0
         and bitand(grsp.rspflags, 1) = 1 -- guaranteed
         and grsp.rspfscn <= grsp.rspscn -- filter clean grp
         and grsp.rspfscn != 0
     ) grsp
  WHERE al.next_change#   >= grsp.from_scn
      AND al.first_change#    <= (grsp.to_scn + 1)
      AND al.resetlogs_change# = grsp.resetlogs_change#
      AND al.resetlogs_time       = grsp.resetlogs_time
      AND al.archived = 'YES';

Filed Under: oracle

Some more articles you might also be interested in …

  1. ORA-01031 When Compiling A Synonym
  2. CentOS / RHEL 7 : How to set udev rules for ASM on multipath disks
  3. Oracle Tablespace Transport for a Single Partition
  4. Beginners Guide to Monitoring Oracle Database Restore/Recovery Progress
  5. How to upgrade RMAN catalog SCHEMA from 11g to 12.1.0.2 without upgrading the catalog database
  6. How to add or drop redo log groups in Oracle RAC
  7. PL/SQL: Palindrome Program
  8. How to load SELinux Module For Oracleasm
  9. Steps to relink Oracle Libraries
  10. Oracle Database : Understanding Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright