Oracle OCP 9i to 11g Using Flashback and Logminer

Card Set Information

Author:
Tralala
ID:
190438
Filename:
Oracle OCP 9i to 11g Using Flashback and Logminer
Updated:
2014-05-30 03:18:33
Tags:
Oracle OCP 9i 11g Flashback
Folders:

Description:
Oracle OCP 9i to 11g Using Flashback and Logminer
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. Flashback Recovery Area contains what structures?
    • Archivelog files
    • RMAN files
    • Flashback files
    • Flashback logs
    • Other backup files
  2. Which view contains stats on the flashback area?
    V$FLASHBACK_DATABASE_LOG
  3. Default db_flashback_retention_target?
    1440 mins
  4. When can Flashback Database NOT be performed?
    • Controlfile was recreated or restored during the duration of the flashback
    • If the datafile has been resized with RESIZE command (does not apply to autoextend)
    • If a tablespace or datafile was dropped during the the duration of the flashback
    • If an incomplete recovery was performed and RESETLOGS during the the duration of the flashback.
  5. What are the FLASHBACK DATABASE options?
    • FLASHBACK DATABASE TO TIME= time format
    • FLASHBACK DATABASE TO SCN=n
    • FLASHBACK DATABASE TO SEQUENCE=m THREAD=n
    • FLASHBACK DATABASE TO TIMESTAMP
  6. What are the steps to flashback a database?
    • RMAN>shutdown immediate
    • RMAN>startup mount
    • RMAN>FLASHBACK DATABASE TO...
    • RMAN>ALTER DATABASE OPEN RESETLOGS
  7. What happens when ALTER DATABASE FLASHBACK OFF is executed.
    The flashback logs are deleted.
  8. Which view can be used to monitor the flashback space for the last 24 hours?
    • V$FLASHBACK_DATABASE_STAT
    • One row for each hour
  9. What can V$FLASHBACK_DATABASE_LOG  tell us?
    • Oldest Flashback SCN
    • Oldest Flashback Time
    • Retention Target
    • Flashback Size
    • Esimated Flashback Size based on Retention Target
  10. How long will object stay in RECYCLE_BIN?
    • The tablespace containing the dropped object cannot contain more data
    • The datafile extends automatically
    • Users quota for the tablespace is met
    • User/DBA purges the recycle bin
  11. What command will drop a table bypassing the RECYCLE BIN?
    DROP tablename PURGE
  12. Which view shows the contents of the RECYCLE BIN?
    • USER_RECYCLEBIN or RECYCLEBIN
    • DBA_RECYCLEBIN
  13. How do you delete objects from the RECYCLE BIN for a particular tablespace?
    PURGE TABLESPACE tablespace_name;
  14. How do you delete objects from the RECYCLE BIN for a particular tablespace for a specific user?
    PURGE TABLESPACE tablespace_name USER username;
  15. How can we guarantee how long an object will stay in the RECYCLE BIN?
    We can't
  16. What command does user invoke to delete all objects in RECYCLE BIN?
    PURGE RECYCLEBIN
  17. If a table is dropped how can it be recovered without a database restore? How can this table be renamed?
    FLASHBACK TABLE tablename TO BEFORE DROP [RENAME TO newname]
  18. Which view will tell us information about a dropped table?
    • USER_RECYCLEBIN/RECYCLEBIN
    • DBA_RECYCLEBIN
  19. What happens to a tablespace's recycle bin if the tablespace is dropped?
    The contents are purged
  20. If DROP USER...CASCADE is executed, what happens to the RECYCLE BIN?
    It is purged of the users objects.
  21. Which clause will allow you to retreive data from a table between two past time events?
    VERSIONS BETWEEN
  22. What two parameters have to be set for VERSIONS BETWEEN to work?
    • UNDO_TABLESPACE
    • UNDO_RETENTION
  23. What rows are returned using VERSIONS BETWEEN?
    Committed transactions, excluding the current transaction
  24. What privileges are required for VERSIONS BETWEEN?
    FLASHBACK and SELECT
  25. What privileges are required to flashback ANY table?
    FLASHBACK ANY TABLE
  26. What is the syntax for VERSIONS BETWEEN?
    • SELECT column1, column2
    • FROM table
    • VERSIONS BETWEEN 
    • [SCN minval AND maxval] OR [TIMESTAMP timestamp1 AND timestamp2]
    • [WHERE condition....]
  27. When can VERSIONS BETWEEN not be used?
    • if structural changes have occurred
    • against a view
  28. When can VERSIONS BETWEEN be used in
    A. Views
    B. Subqueries
    • A. Yes
    • B. Yes
  29. What must be set for Flashback Table?
    • UNDO_TABLESPACE
    • UNDO_RETENTION
    • ALTER TABLE table_name ENABLE ROW MOVEMENT
  30. What feature can a user implement to recover a table without DBA involvment?
    Flashback Table
  31. What is the syntax for Flashback Table?
    • FLASHBACK TABLE
    • schema.tablename
    • TO [SCN] [TIMESTAMP] n;
  32. What does Flashback Transaction Query do?
    Allows viewing of changes made to the database at transaction level
  33. Which view contains information for Flashback Transaction
    flashback_transaction_query
  34. How is UNDO_SQL be generated?
    • SELECT VERSIONS_XID, *
    • FROM table
    • VERSIONS BETWEEN SCN scn1 AND scn2
    • [WHERE condition];

    • SELECT OPERATION, UNDO_SQL, TABLE_NAME
    • FROM flashback_transaction_query
    • WHERE xid='n';
  35. What Oracle Features will guarantee a table can be flashed back a given time?
    Flashback Data Archive
  36. What command creates a Flashback Data Archive?
    • CREATE FLASHBACK ARCHIVE [DEFAULT] <flashback_archive_name>
    • TABLESPACE <tablespace_name>
    • [QUOTA <integer_value <M | G | T | P>]
    • RETENTION <retention_value> <YEAR | MONTH | DAY>;
  37. Which view shows tablespaces containing a Flashback Archive?
    DBA_FLASHBACK_ARCHIVE_TS
  38. Which view shows Flashback Archive Information?
    DBA_FLASHBACK_ARCHIVE
  39. How can we purge data from a Flashback archive for specific period?
    ALTER FLASHBACK ARCHIVE <ARCHIVE> PURGE BEFORE SCN <SCN> | TIMESTAMP <TIMESTAMP>
  40. How can we purge all data from a Flashback archive?
    ALTER FLASHBACK ARCHIVE <ARCHIVE> PURGE ALL;
  41. How do we drop a flashback archive?
    DROP FLASHBACK ARCHIVE <ARCHIVE>
  42. Which objects are created when creating a Flashback Archive?
    • SYS_FBA_HIST_<OBJECT_ID>
    • SYS_FBA_TCRV_<OBJECT_ID>
    • SYS_FBA_DDL_COLMAP_<OBJECT_ID>
  43. How can we alter a retention period for a Flashback Archive?
    • ALTER FLASHBACK ARCHIVE <ARCHIVE>
    • MODIFY RETENTION n DAY|WEEK|MONTH|YEAR;
  44. What are the requirements for flashback?
    • Database must be in ARCHIVELOG mode
    • Flash recovery area must be enabled
  45. What are steps for enabling Flashback Database?
    • SHUTDOWN IMMEDIATE
    • STARTUP MOUNT
    • ALTER DATABASE FLASHBACK ON
  46. Which init parameter sets the period for which a database can be flashed back?
    DB_FLASHBACK_RETENTION_TARGET
  47. What is the default value of DB_FLASHBACK_RETENTION_TARGET?
    One day in mins
  48. For which objects are flashback logs generated by default?
    All permanent tablespaces
  49. How is flashback logging disabled for specific tablespaces?
    ALTER TABLESPACE mytbs FLASHBACK OFF
  50. How is flashback logging disabled for the entire database?
    ALTER DATABASE FLASHBACK OFF
  51. Where are the flashback logs stored?
    FLASH RECOVERY AREA
  52. When a table is dropped, what happens?
    The table is renamed and placed in the recycle bin
  53. What command is used to restore a table?
    FLASHBACK TABLE
  54. How do objects in the recyclebin affect a users space quotas?
    They are counted towards it.
  55. If a tablespace is dropped, how does the recyclebin come into play?
    It is not used and objects for the dropped tablespace in the recyclebin are purged.
  56. If the recyclebin is disabled, what happens when an object is dropped?
    It is just dropped.
  57. How is a table recover from the recyclebin?
    FLASHBACK TABLE .... TO BEFORE DROP [RENAME TO]
  58. Which tablename is used when using FLASHBACK TABLE
    • The original name
    • The recyclebin name
  59. Which Oracle feature is used to retrieve metadata and historical data for a specific interval?
    Flashback Versions Query
  60. In Flashback Versions Query what is used to define the intervals?
    • Timestamps
    • SCNs
  61. What is the metadata returned by Flashback Versions Query?
    • The start and end time
    • The type of DML
    • The identity of the transaction that created each row version
  62. Which clause is used to generate Flashback Version Query in as SELECT statement?
    VERSIONS BETWEEN
  63. Which Oracle feature is used to retrieve metadata and historical data from transactions in a supplied interval?
    Flashback Transaction Query
  64. Which column does Flashback Transaction Query create?
    UNDO_SQL
  65. What is stored in UNDO_SQL?
    The logical opposite SQL of excited DML
  66. Which Oracle feature allows a table to be recovered to a point in time?
    Flashback Table
  67. What privileges are required to Flashback Table?
    • FLASHBACK ANY TABLE or FLASHBACK privilege on the table
    • SELECT, INSERT, DELETE, ALTER on the table
    • SELECT ANY DICTONARY or SYSTEM_CATALOG_ROLE
  68. For an object to be flashed back, what are the prerequites?
    • Not part of cluster, MV, system table etc etc
    • The structure must not have changed
    • Row movement must be enabled
    • The undo must be sufficient to go back to the desired time
  69. How is a flashback table operation performed?
    • 1. SELECT current_scn FROM V$database
    • 2. SELECT SCN or time you wish to return to
    • 3. Ensure there is sufficient UNDO
    • 4. Make sure row movement is enabled
    • 5. Check for table dependencies
    • 6. Execute FLASHBACK TABLE
  70. What is the syntax for Flashback table?
    FLASHBACK TABLE TO [SCN scn_no | TIMESTAMP timestamp ]
  71. Using which Oracle feature allows a transaction to be backed out of?
    Flashback Transaction
  72. Which procedure is used in Flashback Transaction?
    DBMS_FLASHBACK.TRANSACTION_BACKOUT
  73. What is required to allow Flashback Transaction to function?
    • Database must be in ARCHIVELOG mode
    • Supplemental logging must be enabled
  74. How is Supplemental logging must enabled?
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  75. For Flashback Query, what privileges are required?
    • FLASHBACK and SELECT on affected objects
    • OR
    • FLASHBACK ANY TABLE
  76. For Flashback Transaction Query, what privileges are required?
    • SELECT ANY TRANSACTION
    • SELECT, UPDATE, DELETE, INSERT on affected tables
    • EXECUTE ON DBMS_FLASHBACK
  77. What are the four options for TRANSACTION_BACKOUT, which is default?
    • CASCADE
    • NOCASCADE (Default)
    • NOCASCADE_FORCE
    • NONCONFLICT_ONLY
  78. In TRANSACTION_BACKOUT, what is a Dependent Transaction?
    One where there is a write-after-write relationship. Same data changed twice or same primary key reinserted after deletion.
  79. What does TRANSACTION_BACKOUT....CASCADE achieve?
    Backs out all specified transaction and all dependant transaction is reverse order.
  80. What does TRANSACTION_BACKOUT....NOCASCADE achieve?
    Assumes there are no dependent transactions, if dependent transaction exist, an error is generated
  81. What does TRANSACTION_BACKOUT....NOCASCADE_FORCE achieve?
    Assumes there are no dependent transactions, id there are, they are ignored.
  82. What does TRANSACTION_BACKOUT....NONCONFLICT_ONLY achieve?
    Backout changes to non-conflicting rows of the specified transactions
  83. Which column in V$ARCHIVED_LOG shows if an archived log is in the Flash Recovery Area?
    IS_RECOVERY_DEST_FILE
  84. At what thresholds are Flash Recovery Area Warnings generated?
    • Warning 85%
    • Critical 97%
  85. Which dictionary table can be used to determine if flashback database is enabled?
    V$DATABASE
  86. Which view provides detailed information about the flash recovery area?
    V$RECOVERY_FILE_DEST
  87. What happens to flashback archive information if the default flashback archive is dropped?
    The historical data is lost
  88. If a table is flashed back, what happens to the tables indexes?
    They are maintained
  89. If a table is flashed back, is the DBA informed?
    Yes, via a message in the alert log
  90. What happens if constraints are violated in a flashback table?
    The flashback fails
  91. Can you flashback a table multiple times?
    Yes
  92. What happens to indexes on a FLASHBACK TABLE...TO BEFORE DROP?
    They are recovered
  93. What happens on a FLASHBACK TABLE if the table has been purged?
    The flashback fails
  94. What happens to triggers on a FLASHBACK TABLE...TO BEFORE DROP?
    They are recovered
  95. How are ARCHIVELOG and Flashback Data Archive related?
    The are not, Flashback Data Archive does NOT require ARCHIVELOG
  96. What are the versions Pseudocolumns for a table?
    • versions_startscn
    • versions_starttime
    • versions_endscn
    • versions_endtime
    • versions_xidv
    • versions_operation
  97. When are versions pseudocolumns for a table used?
    With VERSIONS_BETWEEN clause

What would you like to do?

Home > Flashcards > Print Preview