Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Examine the memory-related parameters set in the SPFILE of an Oracle database:
memory_max_target—6G memory_target=5G pga_aggregate_target=500M sga_max_size=0 sga_target=0
Which statement is true?
A. Only SGA components are sized automatically-
B. Memory is dynamically re-allocated between the SGA and PGA as needed.
C. The size of the PGA cannot grow automatically beyond 500 MB.
D. The value of the MEMORY_TARGET parameter cannot be changed dynamically.
Answer: C
Q2. You run a script that completes successfully using SQL*Plus that performs these actions:
1. Creates a multitenant container database (CDB)
2. Plugs in three pluggable databases (PDBs)
3. Shuts down the CDB instance
4. Starts up the CDB instance using STARTUP OPEN READ WRITE
Which two statements are true about the outcome after running the script?
A. The seed will be in mount state.
B. The seed will be opened read-only.
C. The seed will be opened read/write.
D. The other PDBs will be in mount state.
E. The other PDBs will be opened read-only.
F. The PDBs will be opened read/write.
Answer: B,D
Explanation: B: The seed is always read-only.
D: Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.
Q3. 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.
Q4. You notice that the elapsed time for an important database scheduler Job is unacceptably long.
The job belongs to a scheduler job class and window.
Which two actions would reduce the job's elapsed time?
A. Increasing the priority of the job class to which the job belongs
B. Increasing the job's relative priority within the Job class to which it belongs
C. Increasing the resource allocation for the consumer group mapped to the scheduler job's job class within the plan mapped to the scheduler window
D. Moving the job to an existing higher priority scheduler window with the same schedule and duration
E. Increasing the value of the JOB_QUEUE_PROCESSES parameter
F. Increasing the priority of the scheduler window to which the job belongs
Answer: B,C
Explanation: B: Job priorities are used only to prioritize among jobs in the same class.
Note: Group jobs for prioritization Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the class are scheduled to start at the same time, the one with the higher priority takes precedence. This ensures that you do not have a less important job preventing the timely completion of a more important one.
C: Set resource allocation for member jobs Job classes provide the link between the Database Resource Manager and the Scheduler, because each job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified consumer group and are assigned resources according to settings in the current resource plan.
Q5. Which two tasks can be performed on an external table?
A. partitioning the table
B. creating an invisible index
C. updating the table by using an update statement
D. creating a public synonym
E. creating a view
Answer: C,D
Q6. Which three statements are true regarding the use of the Database Migration Assistant for Unicode (DMU)?
A. A DBA 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: A,D,E
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.
Q7. Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB).
A. Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
B. Use the CREATE DATABASE . . . ENABLE PLUGGABLE DATABASE statement to provision a PDB by copying file from the SEED.
C. Use the DBMS_PDB package to clone an existing PDB.
D. Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
E. Use the DBMS_PDB package to plug an Oracle 11 g Release 2 (11.2.0.3.0) non-CDB database into an existing CDB.
Answer: A,C,D
Explanation: Use the CREATE PLUGGABLE DATABASE statement to create a pluggable database (PDB).
This statement enables you to perform the following tasks:
* (A) Create a PDB by using the seed as a template
Use the create_pdb_from_seed clause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
* (C) Create a PDB by cloning an existing PDB
Use the create_pdb_clone clause to create a PDB by copying an existing PDB (the source PDB) and then plugging the copy into the CDB. The files associated with the source PDB are copied to a new location and the copied files are associated with the new PDB. This operation is called cloning a PDB.
The source PDB can be plugged in or unplugged. If plugged in, then the source PDB can be in the same CDB or in a remote CDB. If the source PDB is in a remote CDB, then a database link is used to connect to the remote CDB and copy the files.
* Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB
Use the create_pdb_from_xml clause to plug an unplugged PDB or a non-CDB into a CDB, using an XML metadata file.
Q8. Which three factors influence the optimizer's choice of an execution plan?
A. the optimizer_mode initialization parameter
B. operating system (OS) statistics
C. cardinality estimates
D. object statistics in the data dictionary
E. fixed baselines
Answer: A,B
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm
Q9. You support Oracle Database 12c Oracle Database 11g, and Oracle Database log on the same server.
All databases of all versions use Automatic Storage Management (ASM).
Which three statements are true about the ASM disk group compatibility attributes that are set for a disk group?
A. The ASM compatibility attribute controls the format of the disk group metadata.
B. RDBMS compatibility together with the database version determines whether a database Instance can mount the ASM disk group.
C. The RDBMS compatibility setting allows only databases set to the same version as the compatibility value, to mount the ASM disk group.
D. The ASM compatibility attribute determines some of the ASM features that may be used by the Oracle disk group.
E. The ADVM compatibility attribute determines the ACFS features that may be used by the Oracle 10 g database.
Answer: A,B,D
Explanation: AD: The value for the disk group COMPATIBLE.ASM attribute determines the minimum software version for an Oracle ASM instance that can use the disk group. This setting also affects the format of the data structures for the Oracle ASM metadata on the disk.
B: The value for the disk group COMPATIBLE.RDBMS attribute determines the minimum COMPATIBLE database initialization parameter setting for any database instance that is allowed to use the disk group. Before advancing the COMPATIBLE.RDBMS attribute, ensure that the values for the COMPATIBLE initialization parameter for all of the databases that access the disk group are set to at least the value of the new setting for COMPATIBLE.RDBMS.
For example, if the COMPATIBLE initialization parameters of the databases are set to either 11.1 or 11.2, then COMPATIBLE.RDBMS can be set to any value between 10.1 and
11.1 inclusively.
Not E: /The value for the disk group COMPATIBLE.ADVM attribute determines whether the disk group can contain Oracle ASM volumes. The value must be set to 11.2 or higher. Before setting this attribute, the COMPATIBLE.ASM value must be 11.2 or higher. Also, the Oracle ADVM volume drivers must be loaded in the supported environment.
/ You can create an Oracle ASM Dynamic Volume Manager (Oracle ADVM) volume in a disk group. The volume device associated with the dynamic volume can then be used to host an Oracle ACFS file system.
The compatibility parameters COMPATIBLE.ASM and COMPATIBLE.ADVM must be set to
11.2 or higher for the disk group.
Note:
* The disk group attributes that determine compatibility are COMPATIBLE.ASM, COMPATIBLE.RDBMS. and COMPATIBLE.ADVM. The COMPATIBLE.ASM and COMPATIBLE.RDBMS attribute settings determine the minimum Oracle Database software version numbers that a system can use for Oracle ASM and the database instance types respectively. For example, if the Oracle ASM compatibility setting is 11.2, and RDBMS compatibility is set to 11.1, then the Oracle ASM software version must be at least 11.2, and the Oracle Database client software version must be at least 11.1. The COMPATIBLE.ADVM attribute determines whether the Oracle ASM Dynamic Volume Manager feature can create an volume in a disk group.
Q10. In your multitenant container database (CDB) containing pluggable database (PDBs), you granted the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all
PDBs. You execute the following command from the root container:
SQL > REVOKE create table FROM C # # A_ADMIN;
What is the result?
A. It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root only.
B. It fails and reports an error because the CONTAINER=ALL clause is not used.
C. It excludes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root and all PDBs.
D. It fails and reports an error because the CONTAINER=CURRENT clause is not used.
E. It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in all PDBs.
Answer: A
Explanation: REVOKE ..FROM
If the current container is the root:
/ Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.
/ Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted with CONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.
If you omit this clause, then CONTAINER = CURRENT is the default.
Reference: Oracle Database SQL Language Reference 12c, Revoke
Q11. An administrator account is granted the CREATE SESSION and SET CONTAINER system privileges.
A multitenant container database (CDB) instant has the following parameter set:
THREADED_EXECUTION = FALSE
Which four statements are true about this administrator establishing connections to root in a CDB that has been opened in read only mode?
A. You can conned as a common user by using the connect statement.
B. You can connect as a local user by using the connect statement.
C. You can connect by using easy connect.
D. You can connect by using OS authentication.
E. You can connect by using a Net Service name.
F. You can connect as a local user by using the SET CONTAINER statement.
Answer: C,D,E,F
Explanation:
* The choice of threading model is dictated by the THREADED_EXECUTION initialization parameter.
THREADED_EXECUTION=FALSE : The default value causes Oracle to run using the multiprocess model.
THREADED_EXECUTION=TRUE : Oracle runs with the multithreaded model.
* OS Authentication is not supported with the multithreaded model.
* THREADED_EXECUTION When this initialization parameter is set to TRUE, which enables the multithreaded Oracle model, operating system authentication is not supported. Attempts to connect to the database using operating system authentication (for example, CONNECT / AS SYSDBA or
CONNECT / ) when this initialization parameter is set to TRUE receive an ORA-01031"insufficient privileges" error.
F: The new SET CONTAINER statement within a call back function:
The advantage of SET CONTAINER is that the pool does not have to create a new connection to a PDB, if there is an exisitng connection to a different PDB. The pool can use the existing connection, and through SET CONTAINER, can connect to the desired PDB. This can be done using:
ALTER SESSION SET CONTAINER=<PDB Name>
This avoids the need to create a new connection from scratch.
Q12. You notice a high number of waits for the db file scattered read and db file sequential read events in the recent Automatic Database Diagnostic Monitor (ADDM) report. After further investigation, you find that queries are performing too many full table scans and indexes are not being used even though the filter columns are indexed.
Identify three possible reasons for this.
A. Missing or stale histogram statistics
B. Undersized shared pool
C. High clustering factor for the indexes
D. High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
E. Oversized buffer cache
Answer: A,C,D
Explanation: D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
Q13. You executed this command to create a password file:
$ orapwd file = orapworcl entries = 10 ignorecase = N
Which two statements are true about the password file?
A. It will permit the use of uppercase passwords for database users who have been granted the SYSOPER role.
B. It contains username and passwords of database users who are members of the OSOPER operating system group.
C. It contains usernames and passwords of database users who are members of the OSDBA operating system group.
D. It will permit the use of lowercase passwords for database users who have granted the SYSDBA role.
E. It will not permit the use of mixed case passwords for the database users who have been granted the SYSDBA role.
Answer: A,D
Explanation: * You can create a password file using the password file creation utility, ORAPWD.
* Adding Users to a Password File
When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE
password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.
A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.
* The syntax of the ORAPWD command is as follows:
ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]
* IGNORECASE If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.
Q14. Which three statements are true about Automatic Workload Repository (AWR)?
A. All AWR tables belong to the SYSTEM schema.
B. The AWR data is stored in memory and in the database.
C. The snapshots collected by AWR are used by the self-tuning components in the database
D. AWR computes time model statistics based on time usage for activities, which are displayed in the v$SYS time model and V$SESS_TIME_MODEL views.
E. AWR contains system wide tracing and logging information.
Answer: B,C,E
Explanation: * A fundamental aspect of the workload repository is that it collects and persists database performance data in a manner that enables historical performance analysis. The mechanism for this is the AWR snapshot. On a periodic basis, AWR takes a “snapshot” of the current statistic values stored in the database instance’s memory and persists them to its tables residing in the SYSAUX tablespace.
* AWR is primarily designed to provide input to higherlevel components such as automatic tuning algorithms and advisors, but can also provide a wealth of information for the manual tuning process.
Q15. Which three statements are true about a job chain?
A. It can contain a nested chain of jobs.
B. It can be used to implement dependency-based scheduling.
C. It cannot invoke the same program or nested chain in multiple steps in the chain.
D. It cannot have more than one dependency.
E. It can be executed using event-based or time-based schedules.
Answer: A,B,E
Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse009.htm#ADMIN12459
Q16. Which three statements are true when the listener handles connection requests to an Oracle 12c database instance with multithreaded architecture enabled In UNIX?
A. Thread creation must be routed through a dispatcher process
B. The local listener may spawn a now process and have that new process create a thread
C. Each Oracle process runs an SCMN thread.
D. Each multithreaded Oracle process has an SCMN thread.
E. The local listener may pass the request to an existing process which in turn will create a thread.
Answer: A,D,E