The flashcards below were created by user
on FreezingBlue Flashcards.
Flashback Recovery Area contains what structures?
- Archivelog files
- RMAN files
- Flashback files
- Flashback logs
- Other backup files
Which view contains stats on the flashback area?
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.
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
What are the steps to flashback a database?
- RMAN>shutdown immediate
- RMAN>startup mount
- RMAN>FLASHBACK DATABASE TO...
- RMAN>ALTER DATABASE OPEN RESETLOGS
What happens when ALTER DATABASE FLASHBACK OFF is executed.
The flashback logs are deleted.
Which view can be used to monitor the flashback space for the last 24 hours?
- One row for each hour
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
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
What command will drop a table bypassing the RECYCLE BIN?
DROP tablename PURGE
Which view shows the contents of the RECYCLE BIN?
- USER_RECYCLEBIN or RECYCLEBIN
How do you delete objects from the RECYCLE BIN for a particular tablespace?
PURGE TABLESPACE tablespace_name;
How do you delete objects from the RECYCLE BIN for a particular tablespace for a specific user?
PURGE TABLESPACE tablespace_name USER username;
How can we guarantee how long an object will stay in the RECYCLE BIN?
What command does user invoke to delete all objects in RECYCLE BIN?
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]
Which view will tell us information about a dropped table?
What happens to a tablespace's recycle bin if the tablespace is dropped?
The contents are purged
If DROP USER...CASCADE is executed, what happens to the RECYCLE BIN?
It is purged of the users objects.
Which clause will allow you to retreive data from a table between two past time events?
What two parameters have to be set for VERSIONS BETWEEN to work?
What rows are returned using VERSIONS BETWEEN?
Committed transactions, excluding the current transaction
What privileges are required for VERSIONS BETWEEN?
FLASHBACK and SELECT
What privileges are required to flashback ANY table?
FLASHBACK ANY TABLE
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....]
When can VERSIONS BETWEEN not be used?
- if structural changes have occurred
- against a view
When can VERSIONS BETWEEN be used in
What must be set for Flashback Table?
- ALTER TABLE table_name ENABLE ROW MOVEMENT
What feature can a user implement to recover a table without DBA involvment?
What is the syntax for Flashback Table?
- FLASHBACK TABLE
- TO [SCN] [TIMESTAMP] n;
What does Flashback Transaction Query do?
Allows viewing of changes made to the database at transaction level
Which view contains information for Flashback Transaction
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';
What Oracle Features will guarantee a table can be flashed back a given time?
Flashback Data Archive
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>;
Which view shows tablespaces containing a Flashback Archive?
Which view shows Flashback Archive Information?
How can we purge data from a Flashback archive for specific period?
ALTER FLASHBACK ARCHIVE <ARCHIVE> PURGE BEFORE SCN <SCN> | TIMESTAMP <TIMESTAMP>
How can we purge all data from a Flashback archive?
ALTER FLASHBACK ARCHIVE <ARCHIVE> PURGE ALL;
How do we drop a flashback archive?
DROP FLASHBACK ARCHIVE <ARCHIVE>
Which objects are created when creating a Flashback Archive?
How can we alter a retention period for a Flashback Archive?
- ALTER FLASHBACK ARCHIVE <ARCHIVE>
- MODIFY RETENTION n DAY|WEEK|MONTH|YEAR;
What are the requirements for flashback?
- Database must be in ARCHIVELOG mode
- Flash recovery area must be enabled
What are steps for enabling Flashback Database?
- SHUTDOWN IMMEDIATE
- STARTUP MOUNT
- ALTER DATABASE FLASHBACK ON
Which init parameter sets the period for which a database can be flashed back?
What is the default value of DB_FLASHBACK_RETENTION_TARGET?
One day in mins
For which objects are flashback logs generated by default?
All permanent tablespaces
How is flashback logging disabled for specific tablespaces?
ALTER TABLESPACE mytbs FLASHBACK OFF
How is flashback logging disabled for the entire database?
ALTER DATABASE FLASHBACK OFF
Where are the flashback logs stored?
FLASH RECOVERY AREA
When a table is dropped, what happens?
The table is renamed and placed in the recycle bin
What command is used to restore a table?
How do objects in the recyclebin affect a users space quotas?
They are counted towards it.
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.
If the recyclebin is disabled, what happens when an object is dropped?
It is just dropped.
How is a table recover from the recyclebin?
FLASHBACK TABLE .... TO BEFORE DROP [RENAME TO]
Which tablename is used when using FLASHBACK TABLE
- The original name
- The recyclebin name
Which Oracle feature is used to retrieve metadata and historical data for a specific interval?
Flashback Versions Query
In Flashback Versions Query what is used to define the intervals?
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
Which clause is used to generate Flashback Version Query in as SELECT statement?
Which Oracle feature is used to retrieve metadata and historical data from transactions in a supplied interval?
Flashback Transaction Query
Which column does Flashback Transaction Query create?
What is stored in UNDO_SQL?
The logical opposite SQL of excited DML
Which Oracle feature allows a table to be recovered to a point in time?
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
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
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
What is the syntax for Flashback table?
FLASHBACK TABLE TO [SCN scn_no | TIMESTAMP timestamp ]
Using which Oracle feature allows a transaction to be backed out of?
Which procedure is used in Flashback Transaction?
What is required to allow Flashback Transaction to function?
- Database must be in ARCHIVELOG mode
- Supplemental logging must be enabled
How is Supplemental logging must enabled?
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
For Flashback Query, what privileges are required?
- FLASHBACK and SELECT on affected objects
- FLASHBACK ANY TABLE
For Flashback Transaction Query, what privileges are required?
- SELECT ANY TRANSACTION
- SELECT, UPDATE, DELETE, INSERT on affected tables
- EXECUTE ON DBMS_FLASHBACK
What are the four options for TRANSACTION_BACKOUT, which is default?
- NOCASCADE (Default)
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.
What does TRANSACTION_BACKOUT....CASCADE achieve?
Backs out all specified transaction and all dependant transaction is reverse order.
What does TRANSACTION_BACKOUT....NOCASCADE achieve?
Assumes there are no dependent transactions, if dependent transaction exist, an error is generated
What does TRANSACTION_BACKOUT....NOCASCADE_FORCE achieve?
Assumes there are no dependent transactions, id there are, they are ignored.
What does TRANSACTION_BACKOUT....NONCONFLICT_ONLY achieve?
Backout changes to non-conflicting rows of the specified transactions
Which column in V$ARCHIVED_LOG shows if an archived log is in the Flash Recovery Area?
At what thresholds are Flash Recovery Area Warnings generated?
Which dictionary table can be used to determine if flashback database is enabled?
Which view provides detailed information about the flash recovery area?
What happens to flashback archive information if the default flashback archive is dropped?
The historical data is lost
If a table is flashed back, what happens to the tables indexes?
They are maintained
If a table is flashed back, is the DBA informed?
Yes, via a message in the alert log
What happens if constraints are violated in a flashback table?
The flashback fails
Can you flashback a table multiple times?
What happens to indexes on a FLASHBACK TABLE...TO BEFORE DROP?
They are recovered
What happens on a FLASHBACK TABLE if the table has been purged?
The flashback fails
What happens to triggers on a FLASHBACK TABLE...TO BEFORE DROP?
They are recovered
How are ARCHIVELOG and Flashback Data Archive related?
The are not, Flashback Data Archive does NOT require ARCHIVELOG
What are the versions Pseudocolumns for a table?
When are versions pseudocolumns for a table used?
With VERSIONS_BETWEEN clause