• 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. Oracle Database – Measuring Network Capacity using oratcptest
  2. Oracle Software Group Accounts OSDBA, OSOPER, Oracle Inventory group
  3. Oracle SQL Script to Report Tablespace Free and Fragmentation
  4. New Oracle Net features in version 12c
  5. Oracle Database 12c New Feature: SYSRAC administrative privilege
  6. How to Delete ASM Disk on Multipath Device in CentOS/RHEL
  7. How to find Cluster Name and Grid Version in Oracle RAC
  8. Beginners Guide to Oracle Temporary Tablespaces
  9. Database Crashed With ORA-19815, ORA-19809, ORA-16038
  10. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace

You May Also Like

Primary Sidebar

Recent Posts

  • “az storage blob” Command Examples (Manage blob storage containers and objects in Azure)
  • “az storage account” Command Examples (Manage storage accounts in Azure)
  • “az sshkey” Command Examples (Manage ssh public keys with virtual machines)
  • “az redis” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright