Oracle Oracle Certifications 1Z0-062 Questions & Answers

• Question 261:

  Examine the following commands for redefining a table with Virtual Private Database (VPD) policies: Which two statements are true about redefining the table? (Choose two.)

  

  A. All the triggers for the table are disabled without changing any of the column names or column types in the table.

  B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.

  C. VPD policies are copied from the original table to the new table during online redefinition.

  D. You must copy the VPD policies manually from the original table to the new table during online redefinition.

  Correct Answer: BC

  C (not D): CONS_VPD_AUTO Used to indicate to copy VPD policies automatically

  *

  DBMS_RLS.ADD_POLICY / The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD).DBMS_RLS is available with the Enterprise Edition only.

  Note:

  *

  CONS_USE_PK and CONS_USE_ROWID are constants used as input to the "options_flag" parameter in both the START_REDEF_TABLE Procedure and CAN_REDEF_TABLE Procedure. CONS_USE_ROWID is used to indicate that the redefinition should be done using rowids while CONS_USE_PK implies that the redefinition should be done using primary keys or pseudo-primary keys (which are unique keys with all component columns having NOT NULL constraints).

  *

  DBMS_REDEFINITION.START_REDEF_TABLE To achieve online redefinition, incrementally maintainable local materialized views are used. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization.

  *

  START_REDEF_TABLE Procedure Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be redefined) with the desired attributes of the post-redefinition table, and then call this

  procedure to initiate the redefinition.

• Question 262:

  You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, and delete operations are performed. At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions.

  After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs.

  How do you accomplish this?

  A. Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.

  B. Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data from the trace file.

  C. Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR view.

  D. Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.

  Correct Answer: D

  * Monitoring database operations Real-Time Database Operations Monitoring enables you to monitor long running database tasks such as batch jobs, scheduler jobs, and Extraction, Transformation, and Loading (ETL) jobs as a composite business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the business operation being monitored. As a DBA or developer, you can define business operations for monitoring by explicitly specifying the start and end of the operation or implicitly with tags that identify the operation.

• Question 263:

  Identify three benefits of Unified Auditing.

  A. Decreased use of storage to store audit trail rows in the database.

  B. It improves overall auditing performance.

  C. It guarantees zero-loss auditing.

  D. The audit trail cannot be easily modified because it is read-only.

  E. It automatically audits Recovery Manager (RMAN) events.

  Correct Answer: ABE

  A: Starting with 12c, Oracle has unified all of the auditing types into one single unit called Unified auditing. You don't have to turn on or off all of the different auidting types individually and as a matter of fact auditing is enabled by default right out of the box. The AUD$ and FGA$ tables have been replaced with one single audit trail table. All of the audit data is now stored in Secure Files table thus improving the overall management aspects of audit data itself.

  B: Further the audit data can also be buffered solving most of the common performance related problems seen on busy environments.

  E: Unified Auditing is able to collect audit data for Fine Grained Audit, RMAN, Data Pump, Label Security, Database Vault and Real Application Security operations.

  Note:

  * Benefits of the Unified Audit Trail

  The benefits of a unified audit trail are many: / (B) Overall auditing performance is greatly improved. The default mode that unified audit works is Queued Write mode. In this mode, the audit records are batched in SGA queue and is persisted in a periodic way. Because the audit records are written to SGA queue, there is a significant performance improvement.

  / The unified auditing functionality is always enabled and does not depend on the initialization parameters that were used in previous releases

  / (A) The audit records, including records from the SYS audit trail, for all the audited components of your Oracle Database installation are placed in one location and in one format, rather than your having to look in different places to find audit trails in varying formats. This consolidated view enables auditors to co-relate audit information from different components. For example, if an error occurred during an INSERT statement, standard auditing can indicate the error number and the SQL that was executed. Oracle Database Vault-specific information can indicate whether this error happened because of a command rule violation or realm violation. Note that there will be two audit records with a distinct AUDIT_TYPE. With this unification in place, SYS audit records appear with AUDIT_TYPE set to Standard Audit.

  / The management and security of the audit trail is also improved by having it in single audit trail.

  / You can create named audit policies that enable you to audit the supported components listed at the beginning of this section, as well as SYS administrative users. Furthermore, you can build conditions and exclusions into your policies.

  *

  Oracle Database 12c Unified Auditing enables selective and effective auditing inside the Oracle database using policies and conditions. The new policy based syntax simplifies management of auditing within the database and provides the ability to accelerate auditing based on conditions.

  *

  The new architecture unifies the existing audit trails into a single audit trail, enabling simplified management and increasing the security of audit data generated by the database.

• Question 264:

  A database is stored in an Automatic Storage Management (ASM) disk group, disk group, DGROUP1 with SQL:

  

  There is enough free space in the disk group for mirroring to be done.

  What happens if the CONTROLLER1 failure group becomes unavailable due to error of for maintenance?

  A. Transactions and queries accessing database objects contained in any tablespace stored in DGROUP1 will fall.

  B. Mirroring of allocation units will be done to ASM disks in the CONTROLLER2 failure group until the CONTROLLER1 for failure group is brought back online.

  C. The data in the CONTROLLER1 failure group is copied to the controller2 failure group and rebalancing is initiated.

  D. ASM does not mirror any data until the controller failure group is brought back online, and newly allocated primary allocation units (AU) are stored in the controller2 failure group, without mirroring.

  E. Transactions accessing database objects contained in any tablespace stored in DGROUP1 will fail but queries will succeed.

  Correct Answer: D

• Question 265:

  On your Oracle 12c database, you Issue the following commands to create indexes

  SQL > CREATE INDEX oe.ord_customer_ix1 ON oe.orders (customers_id, sales_rep_id) INVISIBLE;

  SQL> CREATE BITMAP INDEX oe.ord_customer_ix2 ON oe.orders (customers_id, sales_rep_id);

  Which two statements are correct? (Choose two.)

  A. Both the indexes are created; however, only the ORD_COSTOMER index is visible.

  B. The optimizer evaluates index access from both the Indexes before deciding on which index to use for query execution plan.

  C. Only the ORD_CUSTOMER_IX1 index is created.

  D. Only the ORD_CUSTOMER_IX2 index is created.

  E. Both the indexes are updated when a new row is inserted, updated, or deleted In the orders table.

  Correct Answer: AE

  11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default. Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.

• Question 266:

  Your multitenant container database has three pluggable databases (PDBs): PDB1, PDB2, and PDB3.

  Which two RMAN commands may be; used to back up only the PDB1 pluggable database? (Choose two.)

  A. BACKUP PLUGGABLE DATABASE PDB1 while connected to the root container

  B. BACKUP PLUGGABLE DATABASE PDB1 while connected to the PDB1 container

  C. BACKUP DATABASE while connected to the PDB1 container

  D. BACKUP DATABASE while connected to the boot container

  E. BACKUP PLUGGABLE database PDB1 while connected to PDB2

  Correct Answer: AC

  To perform operations on a single PDB, you can connect as target either to the root or directly to the PDB.

  *

  (A) If you connect to the root, you must use the PLUGGABLE DATABASE syntax in your RMAN commands. For example, to back up a PDB, you use the BACKUP PLUGGABLE DATABASE command.

  *

  (C)If instead you connect directly to a PDB, you can use the same commands that you would use when connecting to a non-CDB. For example, to back up a PDB, you would use the BACKUP DATABASE command.

• Question 267:

  Which three statements are true about Flashback Database? (Choose three.)

  A. Flashback logs are written sequentially, and are archived.

  B. Flashback Database uses a restored control file to recover a database.

  C. The Oracle database automatically creates, deletes, and resides flashback logs in the Fast Recovery Area.

  D. Flashback Database can recover a database to the state that it was in before a reset logs operation.

  E. Flashback Database can recover a data file that was dropped during the span of time of the flashback.

  F. Flashback logs are used to restore to the blocks before images, and then the redo data may be used to roll forward to the desired flashback time.

  Correct Answer: CDF

• Question 268:

  Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

  A. By default, EM express is available for a database after database creation.

  B. You can use EM express to manage multiple databases running on the same server.

  C. You can perform basic administrative tasks for pluggable databases by using the EM express interface.

  D. You cannot start up or shut down a database Instance by using EM express.

  E. You can create and configure pluggable databases by using EM express.

  Correct Answer: D

  References: http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html

• Question 269:

  Examine the following command;

  ALTER SYSTEM SET enable_ddl_logging = TRUE;

  Which statement is true?

  A. Only the data definition language (DDL) commands that resulted in errors are logged in the alert log file.

  B. All DDL commands are logged in the alert log file.

  C. All DDL commands are logged in a different log file that contains DDL statements and their execution dates.

  D. Only DDL commands that resulted in the creation of new segments are logged.

  E. All DDL commands are logged in XML format in the alert directory under the Automatic Diagnostic Repository (ADR) home.

  Correct Answer: E

• Question 270:

  In which two scenarios do you use SQL* Loader to load data? (Choose two.)

  A. Transform the data while it is being loaded into the database.

  B. Use transparent parallel processing without having to split the external data first.

  C. Load data into multiple tables during the same load statement.

  D. Generate unique sequential key values in specified columns.

  Correct Answer: CD