Q1. Your database supports an online transaction processing (OLTP) application. The application is undergoing some major schema changes, such as addition of new indexes and materialized views. You want to check the impact of these changes on workload performance.

What should you use to achieve this?

A. Database replay

B. SQL Tuning Advisor

C. SQL Access Advisor

D. SQL Performance Analyzer

E. Automatic Workload Repository compare reports

View Answer

Q2. Your multitenant container (CDB) contains two pluggable databases (PDB), HR_PDB and ACCOUNTS_PDB, both of which use the CDB tablespace. The temp file is called temp01.tmp.

A user issues a query on a table on one of the PDBs and receives the following error: ERROR at line 1:

ORA-01565: error in identifying file ‘/u01/app/oracle/oradata/CDB1/temp01.tmp’ 

ORA-27037: unable to obtain file status

Identify two ways to rectify the error.

A. Add a new temp file to the temporary tablespace and drop the temp file that that produced the error.

B. Shut down the database instance, restore the temp01.tmp file from the backup, and then restart the database.

C. Take the temporary tablespace offline, recover the missing temp file by applying redo logs, and then bring the temporary tablespace online.

D. Shutdown the database instance, restore and recover the temp file from the backup, and then open the database with RESETLOGS.

E. Shut down the database instance and then restart the CDB and PDBs.

View Answer

Q3. You are connected to a pluggable database (PDB) as a common user with DBA privileges. The STATISTICS_LEVEL parameter is PDB_MODIFIABLE. You execute the following:

SQL > ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = ‘*’ SCOPE = SPFILE;

Which is true about the result of this command?

A. The STATISTICS_LEVEL parameter is set to all whenever this PDB is re-opened. 

B. The STATISTICS_LEVEL parameter is set to ALL whenever any PDB is reopened. 

C. The STATISTICS_LEVEL parameter is set to all whenever the multitenant container database (CDB) is restarted. 

D. Nothing happens; because there is no SPFILE for each PDB, the statement is ignored. 

View Answer

Q4. Which three actions are possible during an Online Data file Move operation?

A. Creating and dropping tables in the data file being moved

B. Performing file shrink of the data file being moved

C. Querying tables in the data file being moved

D. Performing Block Media Recovery for a data block in the data file being moved

E. Flashing back the database

F. Executing DML statements on objects stored in the data file being moved

View Answer

Q5. You use a recovery catalog for maintaining your database backups.

You execute the following command:

$rman TARGET / CATALOG rman / cat@catdb

RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Which two statements are true?

A. Corrupted blocks, if any, are repaired. 

B. Checks are performed for physical corruptions.

C. Checks are performed for logical corruptions.

D. Checks are performed to confirm whether all database files exist in correct locations

E. Backup sets containing both data files and archive logs are created. 

View Answer

Q6. You are administering a database stored in Automatic Storage Management (ASM). You use RMAN to back up the database and the MD_BACKUP command to back up the ASM metadata regularly. You lost an ASM disk group DG1 due to hardware failure.

In which three ways can you re-create the lost disk group and restore the data?

A. Use the MD_RESTORE command to restore metadata for an existing disk group by passing the existing disk group name as an input parameter and use RMAN to restore the data. 

B. Use the MKDG command to restore the disk group with the same configuration as the backed-up disk group and data on the disk group.

C. Use the MD_RESTORE command to restore the disk group with the changed disk group specification, failure group specification, name, and other attributes and use RMAN to restore the data. 

D. Use the MKDG command to restore the disk group with the same configuration as the backed-up disk group name and same set of disks and failure group configuration, and use RMAN to restore the data. 

E. Use the MD_RESTORE command to restore both the metadata and data for the failed disk group.

F. Use the MKDG command to add a new disk group DG1 with the same or different specifications for failure group and other attributes and use RMAN to restore the data. 

View Answer

Q7. You plan to migrate your database from a File system to Automata Storage Management (ASM) on same platform.

Which two methods or commands would you use to accomplish this task?

A. RMAN CONVERT command

B. Data Pump Export and import

C. Conventional Export and Import

D. The BACKUP AS COPY DATABASE . . . command of RMAN

E. DBMS_FILE_TRANSFER with transportable tablespace

View Answer

Q8. Which statement is true about Oracle Net Listener?

A. It acts as the listening endpoint for the Oracle database instance for all local and non-local user connections.

B. A single listener can service only one database instance and multiple remote client connections.

C. Service registration with the listener is performed by the process monitor (LREG) process of each database instance.

D. The listener.ora configuration file must be configured with one or more listening protocol addresses to allow remote users to connect to a database instance.

E. The listener.ora configuration file must be located in the ORACLE_HOME/network/admin directly.

View Answer

Q9. Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?

A. Reduced logical I/O

B. Reduced virtual memory utilization

C. Improved parallel Execution performance

D. Improved Serial Execution performance

E. Reduced physical I/O

F. Reduced CPU utilization

View Answer

Q10. Examine the following command:

CREATE TABLE (prod_id number(4), 

Prod_name varchar2 (20), 

Category_id number(30),

Quantity_on_hand number (3) INVISIBLE);

Which three statements are true about using an invisible column in the PRODUCTS table?

A. The %ROWTYPE attribute declarations in PL/SQL to access a row will not display the invisible column in the output.

B. The DESCRIBE commands in SQL *Plus will not display the invisible column in the output.

C. Referential integrity constraint cannot be set on the invisible column.

D. The invisible column cannot be made visible and can only be marked as unused. 

E. A primary key constraint can be added on the invisible column.

View Answer