Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 341:

  You are working as a DBA on the decision support system.

  There is a business requirement to track and store all transactions for at least three years for a few tables in the database.

  Automatic undo management is enabled in the database.

  Which configuration should you use to accomplish this task?

  A. Enable Flashback Data Archive for the tables.

  B. Specify undo retention guarantee for the database.

  C. Enable supplemental logging for the database.

  D. Query V$UNDOSTAT to determine the amount of undo that will be generated and create an undo tablespace for that size.

  E. Create Flashback Data Archive on the tablespace on which the tables are stored.

  Correct Answer: A

• Question 342:

  You want to track and store all transactional changes to a table over its lifetime. To accomplish this task, you enabled Flashback Data Archive with the retention of 5 years. After some time, the business requirement changed and you were

  asked to change the retention from 5 years to 3 years.

  To accomplish this, you issued the following command:

  ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 3 YEAR;

  What is the implication of this command?

  A. The command produces an error because the retention time cannot be reduced.

  B. All historical data is retained but the subsequent flashback data archives are maintained for only three years.

  C. All historical data is purged and the new flashback data archives are maintained for three years.

  D. All historical data older than three years is purged from the flashback archive FLA1.

  Correct Answer: D

• Question 343:

  You have just performed a FLASHBACK TABLE operation using the following command:

  flashback table employees to scn 123456;

  The employees table has triggers associated with it.

  Which of the following statements is true regarding the state of the triggers during the Flashback Table operation?

  A. All the triggers are disabled.

  B. All the triggers are enabled by default.

  C. Enabled triggers remain enabled and disabled triggers remain disabled.

  D. Triggers are deleted when a Flashback Table operation is performed.

  Correct Answer: A

• Question 344:

  Flashback Database relies on which technologies to recover to a point in time?

  A. Flashback Data Archive

  B. Flashback logs in the flash recovery area

  C. FlashbUndo tablespace

  D. RMAN command line

  E. None of the above

  Correct Answer: B

• Question 345:

  Which of these are valid Flashback Database recovery point parameters? (Choose all that apply.)

  A. SCN

  B. Timestamp

  C. Named recovery point

  D. Transaction ID

  E. Session ID

  Correct Answer: ABC

• Question 346:

  When setting up the Flashback Data Archive, which of these key parameters are required? (Choose all that apply.)

  A. Tablespace name

  B. Storage quota

  C. Retention

  D. Table name

  E. Create a default archive

  Correct Answer: AC

• Question 347:

  To clean up old records that are in a Flashback Data Archive and are past the retention period, what must the DBA do?

  A. TRUNCATE the archive table.

  B. DROP the Flashback Data Archive.

  C. Nothing; expired rows are automatically removed.

  D. Nothing; expired rows are moved to an archive table.

  E. Delete entries from the archive where the metadata date retained is greater than the retention period.

  Correct Answer: C

• Question 348:

  You execute the following FLASHBACK TABLE command:

  SQL> FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP('2008-01-04 11:00:00','YYYY-MMDD HH24:MI:SS');

  Which two statements are correct? (Choose two.)

  A. The emp table that was dropped by mistake earlier is restored.

  B. The FLASHBACK TABLE statement is executed as a single transaction.

  C. The FLASHBACK TABLE statement does not maintain existing indexes on the emp table.

  D. The changes made to the emp table since the specified time are undone if no constraint is violated during flashback.

  Correct Answer: BD

  FLASHBACK TABLE (Link)

  Purpose

  Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also,

  Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

  Semantics

  During an Oracle Flashback Table operation, Oracle Database acquires exclusive DML locks on all the tables specified in the Flashback list. These locks prevent any operations on the tables while they are reverting to their earlier state.

  The Flashback Table operation is executed in a single transaction, regardless of the number of tables specified in the Flashback list. Either all of the tables revert to the earlier state or none of them do. If the Flashback Table operation fails on

  any table, then the entire statement fails. At the completion of the Flashback Table operation, the data in table is consistent with table at the earlier time.

  However, FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints. Oracle Database does not revert statistics associated with table to

  their earlier form. Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point. If the index exists now but did not yet exist at the Flashback point, then the database updates the index to reflect the

  state of the table at the Flashback point. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored.

  Logical Flashback Features Useful in Backup and Recovery (Link) The remaining flashback features operate at the logical level. The logical features documented in this chapter are as follows:

  Flashback Table

  You can recover a table or set of tables to a specified point in time in the past without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.

  Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring you to find and restore application-specific properties. "Rewinding a Table with

  Flashback Table" explains how to use this feature.

  Flashback Drop

  You can reverse the effects of a DROP TABLE statement. "Rewinding a DROP TABLE Operation with Flashback Drop" explains how to use this feature.

  Note:

  Because the logical flashback features have uses not specific to backup and recovery, some documentation for them is located elsewhere in the documentation set. All logical flashback features except Flashback Drop rely on undo data.

  Used primarily for providing read consistency for SQL queries and rolling back transactions, undo records contain the information required to reconstruct data as it existed at a past time and examine the record of changes since that past time.

• Question 349:

  You enabled Flashback Data Archive on the INVENTORY table.

  Which DDL operation is supported on the table after enabling Flashback Data Archive?

  A. Drop the table.

  B. Partition the table

  C. Truncate the table.

  D. Add a column to the table.

  E. Rename a column in the table.

  Correct Answer: D

  Flashback Data Archive supports only these DDL statements:

  ALTER TABLE statement that does any of the following: Adds, drops, renames, or modifies a column Adds, drops, or renames a constraint Drops or truncates a partition or subpartition operation TRUNCATE TABLE statement RENAME statement that renames a table Flashback Data Archive does not support DDL statements that

  move, split, merge, or coalesce partitions or subpartitions, move tables, or convert LONG columns to LOB columns.

• Question 350:

  Which of the following statements is true regarding implementing a Flashback Table recovery?

  A. An SCN is never used to perform a Flashback Table recovery.

  B. If a significant number of changes have been made to the table, row movement must be enabled.

  C. The tablespace must be offline before performing a Flashback Table recovery.

  D. Flashback Table recovery is completely dependent on the availability of undo data in the undo tablespace.

  Correct Answer: D