Oracle Oracle Certifications 1Z0-067 Questions & Answers

• Question 141:

  You want to move your existing recovery catalog to another database.

  Examine the steps:

  1) Export the catalog data by using the Data Pump Export utility in the source database.

  2) Create a recovery catalog user and grant the necessary privileges in the target database.

  3) Create a recovery catalog by using the create catalog command.

  4) Import the catalog data into the new recovery catalog by using the Data Pump Import utility in the target

  database.

  5) Import the source recovery catalog schema by using the import catalog command.

  6) Connect to the destination database.

  7) Connect as catalog to the destination recovery catalog schema.

  Identify the option with the correct sequence for moving the recovery catalog.

  A. 1, 6, 4

  B. 2, 3, 7, 5

  C. 1, 2, 6, 4

  D. 1, 2, 3, 6, 5

  Correct Answer: B

• Question 142:

  Your database supports an online transaction processing (OLTP) workload in which one of the applications creates a temporary table for a session and performs transactions on it. This consumes a lot of undo tablespace and is affecting undo retention.

  Which two actions would you take to solve this problem?

  A. Enable temporary undo for the database.

  B. Enable undo retention guarantee.

  C. Increase the size of the redo log buffer.

  D. Enable Automatic Memory Management (AMM).

  E. Increase the size of the temporary tablespace.

  Correct Answer: AE

  Reference:http://docs.oracle.com/database/121/ADMIN/undo.htm#CEGJGIAE

• Question 143:

  RMAN is connected to the target database PROD1 and an auxiliary instance in NOMOUNT state. Examine the command to create a duplicate database:

  RMAN> DUPLICATE TARGET DATABASE TO dup1 FROM ACTIVE DATABASE NOFILENAMECHECK PASSWORD FILE SPFILE;

  Which two statements are true about the execution of the DUPLICATE command?

  A. All archive redo log files are automatically copied to the duplicate database.

  B. The duplicate database has the same directory structure as the source database.

  C. The duplicate database is created by using the backups created during the execution of the DUPLICATE command.

  D. The password file and SPFILE for the duplicate database DUP1 are created in their respective default locations.

  E. The duplicate database is created without using RMAN backups and PROD1 is allowed to remain open during duplication.

  Correct Answer: AE

• Question 144:

  A user issues a query on the sales table and receives the following error:

  ERROR at line 1:

  ORA-01565: error in identifying file '/u0l/app/oracle/oradata/ORCL/temp01.dbf'

  ORA-27037: unable to obtain file status

  Which two actions would you take to recover the temporary tablespace?

  A. Drop the tenp01.dbf file, and then re-create the temp file.

  B. Add a new temp file to the temporary tablespace and drop the temp01.dbf file.

  C. Shut down the database instance, start up the database instance in mount state, create a new temporary tablespace, and then open the database.

  D. Take the temporary tablespace offline, recover the missing temp file, and then bring the temporary tablespace online.

  E. Create a new temporary tablespace and assign it as the default to the user.

  Correct Answer: BE

• Question 145:

  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.

  Correct Answer: ACD

  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 146:

  Which three statements are true about Oracle Restart?

  A. It can be configured to automatically attempt to restart various components after a hardware or software failure.

  B. While starting any components, it automatically attempts to start all dependencies first and in proper order.

  C. It can be configured to automatically restart a database in case of normal shutdown of the database instance.

  D. It can be used to only start Oracle components.

  E. It runs periodic check operations to monitor the health of Oracle components.

  Correct Answer: ABE

  A is correct: Oracle Restart improves the availability of your Oracle database. When you install Oracle Restart, various Oracle components can be automatically restarted after a hardware or software failure or whenever your database host computer restarts E is correct: Oracle Restart runs periodic check operations to monitor the health of these components. If a check operation fails for a component, the component is shut down and restarted. B is also true About Startup Dependencies Oracle Restart ensures that Oracle components are started in the proper order, in accordance with component dependencies. For example, if database files are stored in Oracle ASM disk groups, then before starting the database instance, Oracle Restart ensures that the Oracle ASM instance is started and the required disk groups are mounted. Likewise, if a component must be shut down, Oracle Restart ensures that dependent components are cleanly shut down first. Oracle Restart also manages the weak dependency between database instances and the Oracle Net listener (the listener): When a database instance is started, Oracle Restart attempts to start the listener. If the listener startup fails, then the database is still started. If the listener later fails, Oracle Restart does not shut down and restart any database instances. References: http://docs.oracle.com/cd/E18283_01/server.112/e17120/restart001.htm

• Question 147:

  user_data is a nonencryptedtablespace containing tables with data.

  You must encrypt ail data in this tablespace.

  Which three methods can do this?

  A. Use Data Pump.

  B. Use ALTERTABLE. . .MOVE

  C. Use CREATE TABLE AS SELECT

  D. Use alter tablespace to encrypt the tablespace after enabling row movement on all its

  E. Use altertablespace to encrypt the tablespace.

  Correct Answer: ABC

  Finally, note that you can only create encrypted tablespaces; you cannot modify existing tablespaces to encrypt them. So, when you need existing data in encrypted tablespaces, the best solution is to first create encrypted tablespaces and then move the objects from the unencrypted tablespaces to them. Reference: http://www.oracle.com/technetwork/testcontent/o19tte-086996.html

• Question 148:

  Which two statements are true about a common user?

  A. A common user connected to a pluggable database (PDB) can exercise privileges across other PDBs.

  B. A common user with the create user privilege can create other common users, as well as local users.

  C. A common user can be granted only a common role.

  D. A common user can have a local schema in a PDB.

  E. A common user always uses the global temporary tablespace that is defined at the CDB level as the default temporary tablespace.

  Correct Answer: BD

  Reference: http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64ocp12c-2349447.html

• Question 149:

  You are administering a database that supports a data warehousing workload and is running in noarchivelog mode. You use RMAN to perform a level 0 backup on Sundays and level 1 incremental backups on all the other days of the week.

  One of the data files is corrupted and the current online redo log file is lost because of a media failure.

  Which action must you take for recovery?

  A. Restore the data file, recover it by using the recover datafilenoredo command, and use the resetlogs option to open the database.

  B. Restore the control file and all the data files, recover them by using the recover database noredo command, and use the resetlogs option to open the database.

  C. Restore all the data files, recover them by using the recover database command, and open the database.

  D. Restore all the data files, recover them by using the recover database noredo command, and use the resetlogs option to open the database.

  Correct Answer: B

  See Example 3-6 Recovering a NOARCHIVELOG Database STARTUP FORCE NOMOUNT; RESTORE CONTROLFILE; # restore control file from consistent backup ALTER DATAB ASE MOUNT; RESTORE DATABASE; # restore data files from consistent backup RECOVER DATABASE NOREDO; # specify NOREDO because online redo logs are lost ALTER DATABASE OPEN RESETLOGS; Reference: https://docs.oracle.com/database/121/RCMRF/rcmsynta2001.htm#RCMRF140

• Question 150:

  For which two requirements would you use the Database Resource Manager?

  A. limiting the CPU used per database call

  B. specifying the maximum number of concurrent sessions allowed for a user

  C. specifying the amount of private space a session can allocate in the shared pool of the SGA

  D. limiting the degree of parallelism of operations performed by a user or group of users

  E. specifying an idle time limit that applies to sessions that are idle and blocking other sessions

  Correct Answer: DE

  Limit the degree of parallelism of any operation performed by members of a group of users. Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.