In today’s article, we will be cover about how to Make Flashback Database Monitor in Oracle.
We get the necessary information about flashback from 3 views.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[Primary-1] SQL> select table_name from dictionary where lower(table_name) like '%flashback%'; TABLE_NAME ------------------------------ DBA_FLASHBACK_ARCHIVE DBA_FLASHBACK_ARCHIVE_TABLES DBA_FLASHBACK_ARCHIVE_TS DBA_FLASHBACK_TXN_REPORT DBA_FLASHBACK_TXN_STATE USER_FLASHBACK_ARCHIVE USER_FLASHBACK_ARCHIVE_TABLES USER_FLASHBACK_TXN_REPORT USER_FLASHBACK_TXN_STATE GV$FLASHBACK_DATABASE_LOG GV$FLASHBACK_DATABASE_LOGFILE TABLE_NAME ------------------------------ GV$FLASHBACK_DATABASE_STAT V$FLASHBACK_DATABASE_LOG V$FLASHBACK_DATABASE_LOGFILE V$FLASHBACK_DATABASE_STAT V$FLASHBACK_TXN_GRAPH V$FLASHBACK_TXN_MODS 17 rows selected. |
|
1 2 3 |
[Primary-1] SQL> desc GV$FLASHBACK_DATABASE_LOG; Name Null? Type ----------------------------------------- -------- ---------------------------- |
INST_ID(NUMBER): Provides instance information.
OLDEST_FLASHBACK_SCN(NUMBER): It gives the oldest SCN that the database can receive with flashback.
OLDEST_FLASHBACK_TIME(DATE): It gives the oldest time the database can receive with flashback.
RETENTION_TARGET(NUMBER): Returns the value of DB_FLASHBACK_RETENTION_TARGET.
FLASHBACK_SIZE(NUMBER): Gives the instantaneous value of the flashback data.
ESTIMATED_FLASHBACK_SIZE(NUMBER): Provides information on how many estimated Flashback Logs in FRA are needed to meet DB_FLASHBACK_RETENTION_TARGET.
|
1 2 3 |
[Primary-1] SQL> desc GV$FLASHBACK_DATABASE_STAT Name Null? Type ----------------------------------------- -------- ---------------------------- |
BEGIN_TIME(DATE): Gives the begin time of the Flashback Log.
END_TIME(DATE): Returns the end time of the Flashback Log.
FLASHBACK_DATA(NUMBER): Gives the size of Flashback Data in the time period.
DB_DATA(NUMBER): Gives the size of the read and written data block in the time period.
REDO_DATA(NUMBER): Returns the size of Redo Data in the time period.
ESTIMATED_FLASHBACK_SIZE(NUMBER):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[Primary-1] SQL> desc GV$FLASHBACK_DATABASE_LOGFILE Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER NAME VARCHAR2(513) LOG# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER FIRST_CHANGE# NUMBER FIRST_TIME DATE TYPE VARCHAR2(9) |
The sum of the BYTES here gives the size of the flashback logs in FRA.
Finally, information about the FRA field:
|
1 2 3 |
[Primary-1] SQL> desc v$RECOVERY_FILE_DEST Name Null? Type ----------------------------------------- -------- ---------------------------- |
NAME(VARCHAR2(513)): Provides the name of the FRA field.
SPACE_LIMIT(NUMBER): Returns the value specified in DB_RECOVERY_FILE_DEST_SIZE.
SPACE_USED(NUMBER): Returns the space used in the FRA area.
SPACE_RECLAIMABLE(NUMBER): Returns the area that is reclaim (delete obselete, redundant, low-priority files).
NUMBER_OF_FILES(NUMBER): Returns the number of files.
