• 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. CentOS / RHEL : Installing and Configuring ASMLib
  2. Oracle ASM 11gR2 instance is unable to start due to missing ASM spfile
  3. How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/UNIX
  4. Patching and Upgrading Databases in Oracle Data Guard Configuration
  5. New Connections to the Database lead to ORA-12518 or TNS-12518
  6. Oracle Interview Questions : Using srvctl V/s sqlplus and pfile V/s spfile in RAC
  7. ORA-16713: The Oracle Data Guard broker command timed out When Changing LogXptMode
  8. How to drop and recreate TEMP Tablespace in Oracle
  9. ORA-01666: control file is for a standby database – failover over standby as primary
  10. Oracle SQL script to Show current Users and SQL being Executed

You May Also Like

Primary Sidebar

Recent Posts

  • ncat Command Examples in Linux
  • ncat: command not found
  • nautilus Command Examples in Linux
  • namei: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright