NEW QUESTION 1
Your database supports a DSS workload that involves the execution of complex queries: Currently, the library cache contains the ideal workload for analysis. You want to analyze some of the queries for an application that are cached in the library cache.
What must you do to receive recommendations about the efficient use of indexes and materialized views to improve query performance?

• A. Create a SQL Tuning Set (STS) that contains the queries cached in the library cache and run the SQL Tuning Advisor (STA) on the workload captured in the STS.
• B. Run the Automatic Workload Repository Monitor (ADDM).
• C. Create an STS that contains the queries cached in the library cache and run the SQL Performance Analyzer (SPA) on the workload captured in the STS.
• D. Create an STS that contains the queries cached in the library cache and run the SQL Access Advisor on the workload captured in the STS.

Answer: D

Explanation:
* SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements.
* The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL
Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics.
Note:
* Decision support system (DSS) workload
* The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles.

NEW QUESTION 2
Examine the following parameters for a database instance: MEMORY_MAX_TARGET=0 MEMORY_TARGET=0 SGA_TARGET=0 PGA_AGGREGATE_TARGET=500m
Which three initialization parameters are not controlled by Automatic Shared Memory Management (ASMM)? (Choose three.)

• A. LOG_BUFFER
• B. SORT_AREA_SIZE
• C. JAVA_POOL_SIZE
• D. STREAMS_POOL_SIZE
• E. DB_16K_CACHE_SZIE
• F. DB_KEEP_CACHE_SIZE

Answer: AEF

Explanation:
Manually Sized SGAComponents that Use SGA_TARGET Space SGAComponent, Initialization Parameter
/ The log buffer LOG_BUFFER
/ The keep and recycle buffer caches DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE
/ Nonstandard block size buffer caches DB_nK_CACHE_SIZE Note:
* In addition to setting SGA_TARGET to a nonzero value, you must set to zero all initialization parameters listed in the table below to enable full automatic tuning of the automatically sized SGA components.
* Table, Automatically Sized SGAComponents and Corresponding Parameters

NEW QUESTION 3
Identify three valid methods of opening, pluggable databases (PDBs).

• A. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
• B. ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB
• C. ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed
• D. ALTER DATABASE PDB OPEN issued from the root
• E. ALTER DATABASE OPEN issued from that PDB
• F. ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB
• G. ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Answer: AEG

Explanation:
E: You can perform all ALTER PLUGGABLE DATABASE tasks by connecting to a PDB and running the corresponding ALTER DATABASE statement. This functionality is provided to maintain backward compatibility for applications that have been migrated to a CDB environment.
AG: When you issue an ALTER PLUGGABLE DATABASE OPEN statement, READ WRITE is the default unless a PDB being opened belongs to a CDB that is used as a physical standby database, in which case READ ONLY is the default.
You can specify which PDBs to modify in the following ways: List one or more PDBs.
Specify ALL to modify all of the PDBs.
Specify ALL EXCEPT to modify all of the PDBs, except for the PDBs listed.

NEW QUESTION 4
You execute the following PL/SQL:

Which two statements are true? (Choose two.)

• A. Fine-Grained Auditing (FGA) is enabled for the PRICE column in the PRODUCTS table for SELECT statements only when a row with PRICE > 10000 is accessed.
• B. FGA is enabled for the PRODUCTS.PRICE column and an audit record is written whenever a row with PRICE > 10000 is accessed.
• C. FGA is enabled for all DML operations by JIM on the PRODUCTS.PRICE column.
• D. FGA is enabled for the PRICE column of the PRODUCTS table and the SQL statements is captured in the FGA audit trial.

Answer: AB

Explanation:
DBMS_FGA.add_policy
* The DBMS_FGA package provides fine-grained security functions.
* ADD_POLICY Procedure
This procedure creates an audit policy using the supplied predicate as the audit condition. Incorrect:
Not C: object_schema
The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)

NEW QUESTION 5
You want to load data from a large file into your database without causing an overhead on the SGA. Which tool would you use.

• A. external table
• B. Oracle data Pump
• C. SQL*Loader with a direct data path
• D. SQL*Loader with a conventional data path
• E. Enterprise Manager Database Express

Answer: C

Explanation:
References: https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_modes.htm#i1007501

NEW QUESTION 6
Your single-instance Oracle 12c database home currently supports conventional auditing and uses Automatic Storage Management (ASM). You want to enable unified auditing by executing the command:
$ make –fins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME Which two steps should you perform before executing this command? (Choose two.)

• A. Ensure that the initialization parameter AUDIT_TRAIL is set to DB.
• B. Drop any existing fine-grained audit (FGA) policies.
• C. Stop the listener.
• D. Shut down the database instance.
• E. Disable auditing by setting the initialization parameter AUDIT_TRAIL to NONE.

Answer: CE

Explanation:
References: https://blogs.oracle.com/UPGRADE/entry/unified_auditing_is_it_on

NEW QUESTION 7
Your multitenant container database, CDB1, is running in ARCHIVELOG mode and has two pluggable databases, HR_PDB and ACCOUNTS_PDB. An RMAN backup exists for the database.
You issue the command to open ACCOUNTS_PDB and find that the USERDATA.DBF data file for the default permanent tablespace USERDATA belonging to ACCOUNTS_PDB is corrupted.
What should you do before executing the commands to restore and recover the data file in ACCOUNTS_PDB?

• A. Place CDB1 in the mount stage and then the USERDATA tablespace offline in ACCOUNTS_PDB.
• B. Place CDB1 in the mount stage and issue the ALTER PLUGGABLE DATABASE accounts_pdb CLOSE IMMEDIATE command.
• C. Issue the ALTER PLUGGABLE DATABASE accounts_pdb RESTRICTED command.
• D. Take the USERDATA tablespace offline in ACCOUNTS_PDB.

Answer: D

Explanation:
* You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

NEW QUESTION 8
Your database instance has the following parameter setting: OS_AUTHENT_PREFIX = OPS$
You execute the following command:

And then grant OPS$GUEST_USER the CREATE SESSION privilege. Which two statements are true? (Choose two.)

• A. GUEST_USER can query the tables created in the USERS tablespace by default.
• B. The authentication details for GUEST_USER are stored in the database password file.
• C. A local GUEST_USER OS account should exist before GUEST_USER can log on to the database.
• D. GUEST_USER can log on to the database without specifying a username and password.
• E. GUEST_USER is forced to change the password at the first logi

Answer: CD

NEW QUESTION 9
You conned using SQL Plus to the root container of a multitenant container database (CDB) with SYSDBA privilege. The CDB has several pluggable databases (PDBs) open in the read/write mode. There are ongoing transactions in both the CDB and PDBs.
What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?

• A. The shutdown proceeds immediatel
• B. The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled hack.
• C. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled back.
• D. The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either committed or rolled back.
• E. The statement results in an error because there are open PDBs.

Answer: B

Explanation:
* SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle Database instance, optionally closing and dismounting a database. If the current database is a pluggable database, only the pluggable database is closed. The consolidated instance continues to run. Shutdown commands that wait for current calls to complete or users to disconnect such as SHUTDOWN NORMAL and SHUTDOWN TRANSACTIONAL have a time limit that the SHUTDOWN command will wait. If all events blocking
the shutdown have not occurred within the time limit, the shutdown command cancels with the following message: ORA-01013: user requested cancel of current operation
* If logged into a CDB, shutdown closes the CDB instance.
To shutdown a CDB or non CDB, you must be connected to the CDB or non CDB instance that you want to close, and then enter
SHUTDOWN
Database closed. Database dismounted. Oracle instance shut down.
To shutdown a PDB, you must log into the PDB to issue the SHUTDOWN command. SHUTDOWN Pluggable Database closed. Note:
* Prerequisites for PDB Shutdown
When the current container is a pluggable database (PDB), the SHUTDOWN command can only be used if: The current user has SYSDBA, SYSOPER, SYSBACKUP, or SYSDG system privilege.
The privilege is either commonly granted or locally granted in the PDB.
The current user exercises the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG at connect time.
To close a PDB, the PDB must be open.

NEW QUESTION 10
Which three statements are true regarding the use of the Database Migration Assistant for Unicode (DMU)? (Choose three.)

• A. ADBA can check specific tables with the DMU
• B. The database to be migrated must be opened read-only.
• C. The release of the database to be converted can be any release since 9.2.0.8.
• D. The DMU can report columns that are too long in the converted characterset.
• E. The DMU can report columns that are not represented in the converted characterset.

Answer: ADE

Explanation:
A: In certain situations, you may want to exclude selected columns or tables from scanning or conversion steps of the migration process.
D: Exceed column limit
The cell data will not fit into a column after conversion. E: Need conversion
The cell data needs to be converted, because its binary representation in the target character set is different than the representation in the current character set, but neither length limit issues nor invalid representation issues have been found
* Oracle Database Migration Assistant for Unicode (DMU) is a unique next-generation migration tool providing an end- to-end solution for migrating your databases from legacy encodings to Unicode.
Incorrect:
Not C: The release of Oracle Database must be 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1, or later.

NEW QUESTION 11
The schema SALES exists in two databases, ORCL1 and ORCL2, and has the same password, SALES123. User SALES has CREATE DATABASE LINK and CREATE SESSION privileges on both databases. Examine these commands: Conn SALES/SALES123
CREATE DATABASE LINK orcl2 USING 'orcl2';
What is the outcome of executing these commands in the ORCL1 database?

• A. ORCL2 is created as a public database link to connect a single session to the SALES schema in the ORCL2 database.
• B. ORCL2 is created as a shared database link to connect multiple sessions to the SALES schema in the ORCL2 database.
• C. ORCL2 is created as a private database link to connect to only the SALES schema in the ORCL2 database.
• D. ORCL2 database link creation fail

Answer: C

NEW QUESTION 12
You executed the following command to create a password file in the database server:
$ orapwd file = orapworcl entries = 5 ignorecase=N
Which statement describes the purpose of the above password file?

• A. It records usernames and passwords of users when granted the DBA role
• B. It contains usernames and passwords of users for whom auditing is enabled
• C. It is used by Oracle to authenticate users for remote database administrator
• D. It records usernames and passwords of all users when they are added to OSDBA or OSOPER operating groups

Answer: C

NEW QUESTION 13
You want to create a test database as a replica of your production database with minimum intervention from a DBA. Which method would you use?

• A. Use DBCA to create a template from the existing database to contain the database structure and then manually copy the data by using Oracle Data Pump.
• B. Use Database Configuration Assistant (DBCA) to create a template from the existing database to contain the database structure.
• C. Create the database by using the CREATE DATAEAS
• D. . . command and manually import data by using Data Pump.
• E. Use DBCA to create a template from the existing database to contain the database structure with data files and then use the same template to create the database in the new location.

Answer: A

NEW QUESTION 14
You have the following entry in the tnsnames.ors of your hq.us.example.com host machine:

You issue the following command at the command prompt: Sqlplus HR/HR@ORCL
Which statement is true about the connection to the ORCL database instance?

• A. The connection succeeds, provided the NAMES.DEFAULT_DOMAIN parameter is set to us.example.com in the sqlnet.ora file on the client side.
• B. The connection fails because the net service name does not have the suffix us.example.com.
• C. The connection succeeds, provided the SERVICE_NAMES initialization parameter is set to ORCL.
• D. The connection succeeds, provided the ORCL.us.example.com database service is registered with a listener, the listener is up, and the database is open.

Answer: D

NEW QUESTION 15
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.

Answer: D

NEW QUESTION 16
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 (PMON) 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.

Answer: C

Explanation:
https://docs.oracle.com/database/121/CNCPT/process.htm

NEW QUESTION 17
When is the UNDO_RETENTION parameter value ignored by a transaction?

• A. when there are multiple undo tablespaces available in a database
• B. when the data file of the undo tablespace is autoextensible
• C. when the undo tablespace is of a fixed size and retention guarantee is not enabled
• D. when Flashback Database is enabled

Answer: C

Explanation:
References: https://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm (undo retention)

NEW QUESTION 18
You create a new pluggable database, HR_PDB, from the seed database. Which three tablespaces are created by default in HR_PDB? (Choose three.)

• A. SYSTEM
• B. SYSAUX
• C. EXAMPLE
• D. UNDO
• E. TEMP
• F. USERS

Answer: ABE

Explanation:
* A PDB would have its SYSTEM, SYSAUX, TEMP tablespaces.It can also contains other user created tablespaces in it.
* Oracle Database creates both the SYSTEM and SYSAUX tablespaces as part of every database.
* tablespace_datafile_clauses
Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX tablespaces in the seed PDB.
Incorrect:
Not D: a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. Note:
* Example:
CONN pdb_admin@pdb1
SELECT tablespace_name FROM dba_tablespaces; TABLESPACE_NAME
------------------------------ SYSTEM
SYSAUX TEMP USERS SQL>

NEW QUESTION 19
The HR schema exists in two databases, BOSTON and DENVER, and has the same password, HR. You have the CREATE DATABASE LINK and CREATE SESSION privileges on both the database. BOSTON is defined as a service name in the tnsnames.ora of both the databases.
You plan to use the command:
CREATE DATABASE LINK hr_link CONNECT to hr IDENTIFIED BY hr USING 'denver';
What must be done to ensure only the HR user in the BOSTON database can access the HR schema in the DENVER database?

• A. Execute this command as HR user in the BOSTON database and SYS user in the DENVER database.
• B. Execute this command as SYS user in both the databases.
• C. Execute this command as HR user in the DENVER database.
• D. Execute this command as HR user in the BOSTON databas

Answer: D

NEW QUESTION 20
Which files must you copy from the Oracle home of the database that is being upgraded to the new Oracle home for Oracle Database 12c? (Choose three.)

• A. the tnsnames.ora file
• B. the sqlnet.ora file
• C. the initialization parameter file
• D. the password file
• E. the listener.ora file

Answer: ABE

Explanation:
References https://docs.oracle.com/cd/E11882_01/server.112/e23633/afterup.htm#UPGRD52747

NEW QUESTION 21
Your database is in NOARCHIVELOG mode. You want to enable archiving for the database. Examine the steps:
1. Execute the ALTER DATABASE ARCHIVELOG command.
2. Execute SHUTDOWN IMMEDIATE.
3. Execute STARTUP MOUNT.
4. Set the DB_RECOVERY_FILE_DEST parameter to $ORACLE_HOME/dbs/.
5. Execute STARTUP NOMOUNT.
6. Open the database.
Identify the required steps in the correct sequence.

• A. 4, 2, 5, 1, 6
• B. 1, 2, 3, 4, 6
• C. 2, 3, 1, 6
• D. 2, 5, 1, 6

Answer: C

NEW QUESTION 22
Which task is performed by a background process in a database instance?

• A. Connecting between a client process and a dispatcher
• B. Executing PL/SQL code
• C. Creating dedicated server connections
• D. Copying online redo log files to offline storage

Answer: D

NEW QUESTION 23
Which two are prerequisites for performing a flashback transaction? (Choose two.)

• A. Flashback Database must be enabled.
• B. Undo retention guarantee for the database must be configured.
• C. EXECUTE privilege on the DBMS_FLASHBACK package must be granted to the user flashing back transaction.
• D. Supplemental logging must be enabled.
• E. Recycle bin must be enabled for the database.
• F. Block change tracking must be enabled tor the database.

Answer: BD

Explanation:
References: http://searchoracle.techtarget.com/tip/How-to-perform-Oracle-Flashback-Transaction-Queries https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS610

NEW QUESTION 24
Which action takes place when a file checkpoint occurs?

• A. The checkpoint position is advanced in the checkpoint queue.
• B. All buffers for a checkpointed file that were modified before a specific SCN are written to disk by DBWn and the SCN is stored in the control file.
• C. The Database Writer process (DBWn) writes all dirty buffers in the buffer cache to data files.
• D. The Log Writer process (LGWR) writes all redo entries in the log buffer to online redo log file

Answer: B

NEW QUESTION 25
Your multitenant container database (CDB) contains a pluggable database, HR_PDB. The default permanent tablespace in HR_PDB is USERDATA. The container database (CDB) is open and you connect RMAN.
You want to issue the following RMAN command: RMAN > BACKUP TABLESPACE hr_pdb:userdata;
Which task should you perform before issuing the command?

• A. Place the root container in ARHCHIVELOG mode.
• B. Take the user data tablespace offline.
• C. Place the root container in the nomount stage.
• D. Ensure that HR_PDB is ope

Answer: A

NEW QUESTION 26
You install a non-RAC Oracle Database. During Installation, the Oracle Universal Installer (OUI) prompts you to enter the path of the Inventory directory and also to specify an operating system group name.
Which statement is true?

• A. The ORACLE_BASE base parameter is not set.
• B. The installation is being performed by the root user.
• C. The operating system group that is specified should have the root user as its member.
• D. The operating system group that is specified must have permission to write to the inventory directory.

Answer: D

Explanation:
Note:
Providing a UNIX Group Name
If you are installing a product on a UNIX system, the Installer will also prompt you to provide the name of the group which should own the base directory.
You must choose a UNIX group name which will have permissions to update, install, and deinstall Oracle software. Members of this group must have write permissions to the base directory chosen.
Only users who belong to this group are able to install or deinstall software on this machine.

NEW QUESTION 27
......