Oracle 1Z0-067 Online Practice Questions and Exam Preparation

Oracle 1Z0-067 Online Questions & Answers

• Question 231:

  Which two are prerequisites for setting up Flashback Data Archive?

  A. Fast Recovery Area should be defined.
  B. Undo retention guarantee should be enabled.
  C. Supplemental logging should be enabled.
  D. Automatic Undo Management should be enabled.
  E. All users using Flashback Data Archive should have unlimited quota on the Flashback Data Archive tablespace.
  F. The tablespace in which the Flashback Data Archive is created should have Automatic Segment Space Management (ASSM) enabled.
  D. Automatic Undo Management should be enabled.
  F. The tablespace in which the Flashback Data Archive is created should have Automatic Segment Space Management (ASSM) enabled.

  ---

  There are a number of restrictions for flashback archives: The tablespaces used for a flashback archive must use local extent management and automatic segment space management. The database must use automatic undo management.

  Reference:

  http://www.dba-oracle.com/t_11g_new_enabling_fdba.htm

• Question 232:

  Examine the parameters for your database instance:

  NAME TYPE VALUE

  optimizer_adaptive_reporting_onlyboolean FALSE optimizer_capture_sql_plan_baselinesboolean FALSE optimizer_dynamic_samplinginteger 2 optimizer_features_enablestring 12.1.0.1

  Which three statements are true about the process of automatic optimization by using statistics feedback?

  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  B. The optimizer can re optimize a query only once using cardinality feedback.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
  E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
  A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
  C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
  D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.

  ---

  C: During the first execution of a SQL statement, an execution plan is generated as usual.

  D: if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback. (not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions.

  optimizer_dynamic_sampling optimizer_features_enable

  Dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates.

  Note:

  * OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. Range of values. 0 to 10

  Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

• Question 233:

  Your database is running in archivelog mode. You are taking a backup of your database by using RMAN with a recovery catalog. Because of a media failure, one of the data files and all the control files are lost.

  Examine the steps to recover the database:

  1.

  Restore the control files by using the RMAN restore controlfile command.

  2.

  Mount the database.

  3.

  Restore the data files by using the RMAN restore database command.

  4.

  Open the database with the resetlogs option.

  5.

  Recover the data files by using the RMAN recover using backup controlfile command.

  6.

  Start the database instance in nomount state.

  7.

  Connect to the target database by using a recovery catalog.

  8.

  Open the database.

  9.

  Restore the data file.

  10.

  Recover the data file.

  Identify the required steps in the correct order.

  A. 7, 6, 1, 2, 3, 5, 4
  B. 7, 2, 1, 3, 5, 8
  C. 7, 6, 1, 2, 9, 10, 8
  D. 7, 6, 1, 2, 9, 10, 4
  D. 7, 6, 1, 2, 9, 10, 4

  ---

  "If all copies of the current control file are lost or damaged, then you must restore and mount a backup control file. You must then run the RECOVER command, even if no datafiles have been restored, and open the database with the

  RESETLOGS option."

  References:

  https://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmadvre.htm#i1006245

• Question 234:

  Which four actions are possible during an Online Datafile Move operation?

  A. Creating and dropping tables in the datafile being moved
  B. Performing file shrink of the data file being moved
  C. Querying tables in the datafile being moved
  D. Performing Block Media Recovery for a data block in the datafile being moved
  E. Flashing back the database
  F. Executing DML statements on objects stored in the datafile being moved
  A. Creating and dropping tables in the datafile being moved
  C. Querying tables in the datafile being moved
  E. Flashing back the database
  F. Executing DML statements on objects stored in the datafile being moved

  ---

  "The good news is that Oracle 12cR1 now offers the ability to move entire datafiles between different storage locations without ever having to take the datafiles offline. The datafiles being moved remain completely accessible to applications in almost all situations, including querying against or performing DML and DDL operations against existing objects, creating new objects, and even rebuilding indexes online. Online Move Datafile (OMD) also makes it possible to migrate a datafile between non-ASM and ASM storage (or vice-versa) while maintaining transparent application access to that datafile's underlying database objects. OMD is completely compatible with online block media recovery, the automatic extension of a datafile, the modification of a tablespace between READ WRITE and READ ONLY mode, and it even permits backup operations to continue against any datafiles that are being moved via this feature." Reference: http://muhammad-asif-dba.blogspot.com.br/2013/11/oracle-12c-database-online-move-datafile.html

• Question 235:

  Your database is running on the host OUSERVER. You back up your database regularly using RMAN and the backups are cataloged in a recovery catalog. For testing purposes, you want to replicate your database to another host, OUSERVER1, with the same directory structure. So, you copy the backups to the new host. What must you do to make the database operational in OUSERVER1?

  A. Restore the data files by using the NOCATALOG option and use the SET NEWNAME command to change the location
  B. Restore the control file from the backup by using the CATALOG options, restore the data files by using the SET NEWNAME command, and recover the data files
  C. Restore the data files from the backup by using the recovery catalog, use the SWITCH command to change the location, and recover the data files
  D. Restore the control file from the backup by using the NOCATALOG option, and then restore and recover the data files
  D. Restore the control file from the backup by using the NOCATALOG option, and then restore and recover the data files

• Question 236:

  Examine the RMAN commands:

  RMAN> CONNECT TARGET "sbu@prod AS SYSBACKUP";

  RMAN> CONNECT AUXILIARY "sbu@dup_db AS SYSBACKUP";

  RMAN> DUPLICATE TARGET DATABASE TO dup_db

  FROM ACTIVE DATABASE

  PASSWORD FILE

  SECTION SIZE 400M;

  Which statement is true about the DUPLICATE command?

  A. It succeeds and creates multisection backup sets that are used for active database duplication
  B. It fails because there is no connection to a recovery catalog
  C. It succeeds only if the target database is in MOUNT state
  D. It fails because no parallel channels are allocated for the auxiliary database
  A. It succeeds and creates multisection backup sets that are used for active database duplication

• Question 237:

  Before a Flashback Table operation, you execute the following command:

  ALTER TABLE employees ENABLE ROW MOVEMENT;

  Why would you need this to be executed?

  A. Because row IDs may change during the flashback operation
  B. Because the object number changes after the flashback operation
  C. Because the rows are retrieved from the recycle bin during the flashback operation
  D. Because the table is moved forward and back to a temporary during the flashback operation
  A. Because row IDs may change during the flashback operation

• Question 238:

  You are administering a multitenant container database (CDB) cdb1 that has multiple pluggable databases (PDBs). As the sys user on cdb$root, you execute the commands:

  SQL> CREATE USER C##ADMIN IDENTIFIED BY orc1123;

  SQL> GRANT CREATE SESSION to C##ADMIN CONTAINER=ALL;

  SQL> GRANT CREATE USER TO C##ADMIN CONTAINER=ALL;

  Which two statements are true about the c##admin user that is created in all PDBs?

  A. It can create only local users in all the PDBs.
  B. It has a common schema for all the PDBs.
  C. It can create common users only when it is logged in to the CDB.
  D. It can create only local users in the CDB.
  E. It can be granted only common roles in the PDBs.
  B. It has a common schema for all the PDBs.
  C. It can create common users only when it is logged in to the CDB.

• Question 239:

  In your database, the user SCOTT wants to retrieve the dropped HR.EMPLOYEES table by using the command:

  SQL> FLASHBACK TABLE HR.EMPLOYEES TO BEFORE DROP;

  What are three prerequisites for this command to succeed? (Choose three.)

  A. The recycle bin should be enabled.
  B. No duplicates of the HR.EMPLOYEES table should exist in the recyclebin of the HR schema.
  C. SCOTT should have the SELECT privilege on the HR schema and its recyclebin.
  D. SCOTT should have the FLASHBACK ANY TABLE privilege.
  E. No other table with the same name should exist in the HR schema.
  F. Undo retention guarantee must be enabled.
  A. The recycle bin should be enabled.
  C. SCOTT should have the SELECT privilege on the HR schema and its recyclebin.
  D. SCOTT should have the FLASHBACK ANY TABLE privilege.

• Question 240:

  For your database, an incremental level 1 backup is taken every week day. On Tuesday, before the backup is performed, you add a new tablespace.

  You execute the command:

  RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

  Which statement is true about the execution of the command?

  A. It returns an error because there is no level 0 backup available for new data files.
  B. It performs an image copy backup of new data files, and a level 1 incremental backup of all other data files.
  C. It performs a level-0 backup of all data files including those that belong to the new tablespace.
  D. It performs an image copy backup of all data files including those that belong to the new tablespace.
  E. It performs a backup as a backup set of all data files including those that belong to the new tablespace.
  B. It performs an image copy backup of new data files, and a level 1 incremental backup of all other data files.

  ---

  References: https://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup004.htm