OCA12

Which of the following statements create an Oracle account but let the operating system authenticate logons? (Choose two.) create user ops$admin identified by os; create user ops$admin identified externally; create user ops$admin nopassword; create user ops$admin authenticated by os;

A, B. Authentication by the operating system is called external authentication, and the Oracle account name must match the operating-system account name prefixed with the OS_AUTHENT_PREFIX string. When a user is created with the OS_AUTHENT_PREFIX string, the password provided in option A is ignored, and the user is created as externally authenticated.

Which statements regarding Database Resident Connection Pooling (DRCP) are true? (Choose two.) When a DRCP pooled server connection is made by an application, it is equivalent to a dedicated server connection. When a database has DRCP enabled, all connections default to a pooled connection unless DEDICATED server is explicitly specified in the connect string. When an application using the DRCP connection disconnects, the server connection (process) is handed off to the broker. When using the Oracle Net Easy Connect method, it is not possible to utilize DRCP.

A, C. Even if DRCP is enabled in the database, the client or application connection must always request a pooled connection using SERVER=POOLED in connect syntax. To use Easy Connect and DRCP, the syntax is host:port/service_name:POOLED.

Which tasks are accomplished by the SMON process? (Choose all that apply.) Performs recovery at instance startup Performs cleanup after a user session is terminated Starts any server process that stopped running Coalesces contiguous free space in dictionary-managed tablespaces

A, D. SMON is responsible for performing instance recovery using the online redo log files and for coalescing contiguous free space in tablespaces. The PMON is responsible for session cleanup and for freeing up all resources after a user session is terminated.

A client wants to connect to the database service dbprod.com located on the dbprod.com server through a nondefault port (1522) using Oracle Easy Connect. Which of the following connect strings are the choices for the client to use? (Choose two.) CONNECT scott/[email protected]:1522 CONNECT scott/tiger@1522:dbprod.com/dbprod.com CONNECT scott/tiger@//dbprod.com/1522:dbprod.com CONNECT scott/[email protected]:1522/dbprod.com Answer:

A, D. The correct syntax to use with the Oracle Easy Connect method when you are connecting to a non-URL location is connect username/password@host:port/service_name. If the service name and the host are identical, you do not have to include the service name. If the port is any port other than the default port of 1521, it must be specified. Because you want to connect to a nondefault port where the database name and the hostname are the same, the best answer is A, but D is also correct.

User Isabella updates a table and commits the change after a few seconds. Which of the following actions are happening in the database? Order them in the correct sequence and ignore the actions that are not relevant. Oracle reads the blocks from data file to buffer cache and updates the blocks. Changed blocks from the buffer cache are written to data files. The user commits the change. LGWR writes the changed blocks to the redo log buffer. The server process writes the change vectors to the redo log buffer. LGWR flushes the redo log buffer to redo log files. A checkpoint occurs.

A, E, C, F, G, B. Data blocks are always changed in the memory, and the change vectors are written to redo the log buffer. LGWR writes the redo log buffers to redo the log files on disk as soon as the commit occurs. This guarantees recoverability. During a checkpoint, data files are updated with the changed blocks by the DBWn process.

The administrator wants to allow a user to connect via a dedicated connection into a database configured in Shared Server mode. Which of the following options accomplishes this? (SERVER=DEDICATED) (CONNECT=DEDICATED) (INSTANCE=DEDICATED) (MULTITHREADED=FALSE) None of the above

A. A user must explicitly request a dedicated connection when a server is configured in Shared Server mode. Otherwise, the user gets a Shared Server connection. The correct parameter is (SERVER=DEDICATED).

Which of the following statements give user desmond the ability to alter table gl.accounts? grant alter on gl.accounts to desmond; grant alter to desmond on gl.accounts; grant alter table to desmond; allow desmond to alter table gl.accounts;

A. Altering a table in another user's schema requires either the object privilege ALTER on that object or the system privilege ALTER ANY TABLE. Option A has the correct syntax for granting the object privilege on ALTER gl.accounts to user desmond. The alter table privilege in option C is invalid. CREATE TABLE implicitly gives the privilege to alter a table. One would need the ALTER ANY TABLE privilege to alter another user's table.

Which component is configured at database startup and cannot be dynamically managed? Redo log buffer Streams pool Java pool Shared pool None of the above

A. An Oracle database allows you to manage all memory components dynamically, except the redo log buffer. Redo log buffer is set at instance startup and is not dynamically alterable without restarting the instance.

Two structures make up an Oracle server: an instance and a database. Which of the following best describes the difference between an Oracle instance and a database? An instance consists of memory structures and processes, whereas a database is composed of physical files. An instance is used only during database creation; after that, the database is all that is needed. An instance is started whenever the demands on the database are high, but the database is used all the time. An instance is configured using a pfile, whereas a database is configured using a spfile.

A. The instance consists of the SGA and all the Oracle background processes. The database is composed of the control files, data files, and redo logs.

Which of the following files must be present on the Oracle server to start a nondefault Oracle listener? listener.ora lsnrctl.ora sqlnet.ora tnsnames.ora

A. The listener is the process that manages incoming connection requests. The listener.ora file is used to configure the listener and must be configured to start a nondefault listener. The sqlnet.ora file is an optional client- and server-side file. The tnsnames.ora file is used for doing local naming resolution. There is no such file as lsnrctl.ora. You do not need the listener.ora file to start a default listener on port 1521.

User system granted the SELECT privilege on sh.products to user ian using WITH GRANT OPTION. Ian then granted SELECT on sh.products to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart's privileges on sh.products? Stuart loses his SELECT privilege on sh.products. Stuart retains his SELECT privilege on sh.products. Stuart loses his SELECT privilege if Ian was dropped with the CASCADE REVOKE option. Stuart retains his SELECT privilege if Ian was dropped with the NOCASCADE REVOKE option.

A. When object privileges are granted through an intermediary, they are implicitly dropped when the intermediary is dropped. CASCADE REVOKE and NOCASCADE REVOKE are not part of the GRANT statement syntax.

Which of the following best describes a RAC configuration? One database, multiple instances One instance, multiple databases Multiple databases plugged in from multiple servers Multiple databases, multiple instances

A. With Real Application Clusters, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database.

Choose the statements that are resumable. (Choose three.) ALTER TABLE ... SPLIT PARTITION SELECT INSERT INTO ... SELECT CREATE TABLESPACE ALTER TABLE ... SHRINK SPACE

Answer: A, B, C. ALTER TABLE ... SPLIT PARTITION involves creating extents; therefore, it is resumable. However, ALTER TABLE ... SHRINKSPACE does not create any new extents and is not resumable. The SELECT statement is resumable, because it could create sort extents. INSERT INTO ...SELECT is resumable, as is any INSERT statement. CREATE TABLESPACE is not resumable, because it does not create any extents when a tablespace is created.

How would you add more space to a tablespace? (Choose all that apply.) ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE SIZE <N> ALTER DATABASE DATAFILE <FILENAME> RESIZE <N> ALTER DATAFILE<FILENAME>RESIZE<N> ALTER TABLESPACE<TABLESPACE NAME>DATAFILE <FILENAME> RESIZE <N>

Answer: A, B. You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file. Option A does not have a filename specified; it uses the OMF feature to generate the filename.

Choose the best option regarding extents. An extent is a grouping of Oracle blocks. An extent is a grouping of OS blocks. An extent is a grouping of segments. An extent is allocated when a table is created.

Answer: A. An extent consists of one or more contiguous Oracle blocks. Option B is true if you extend the answer a little bit, that an Oracle block is a multiple of OS blocks. A segment consists of one or more extents, not the other way. An extent is not always allocated when a table is created; an extent is allocated when the first row is inserted into the table.

Which command is used to enable the autoextensible feature for a file if the file is already part of a tablespace? ALTER DATABASE. ALTER TABLESPACE. ALTER DATA FILE. You cannot change the autoextensible feature once the data file is created.

Answer: A. You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but all other file-management operations are done through the ALTER DATABASE command. To enable autoextension, use ALTER DATABASE DATAFILE <FILENAME> AUTOEXTEND ON NEXT <INTEGER> MAXSIZE <INTEGER>.

The default critical threshold for a tablespace is set at 97 percent, and you think that is too low. Which two options can you use to change the threshold value to 90 percent for tablespace APPS_DATA? Use Oracle Enterprise Manager Database Express. Use Oracle Enterprise Manager Cloud Control. Use DBMS_SERVER_ALERT package. Use DBMS_SPACE package.

Answer: B, C. OEM Database Express does not have an option to adjust the server-managed alerts in Database 12c Release 1. OEM Cloud Control and DBMS_SERVER_ALERT.SET_THRESHOLD can be used to set the threshold for tablespaces.

The database is using automatic memory management. The standard block size for the database is 8KB. You need to create a tablespace with a block size of 16KB. Which initialization parameter should be set? DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_CACHE_SIZE None of the above

Answer: B. DB_CACHE_SIZE doesn't need to be set for the standard block size because Automatic Memory Management is used. If you set DB_CACHE_SIZE, its value will be used as the minimum. DB_16K_CACHE_SIZE should be set for the nonstandard block size. You must not set the DB_8K_CACHE_SIZE parameter because the standard block size is 8KB.

Which is a requirement for using host naming? You must use tnsnames.ora on the client. You must be using TCP/IP. You must have an OID present. You must have a sqlnet.ora file present on the client. None of the above.

Answer: B. Host naming is typically used in small installations that have few Oracle databases. This is an attractive option when you want to minimize client-side configuration. TCP/IP is a requirement when you use host naming.

You issue the statement CREATE TABLESPACE X;.Which of the following is the best option? The statement fails because mandatory properties are not defined. The 100MB tablespace is created. The mandatory DATAFILE clause is missing. The tablespace name should be at least three characters long.

Answer: B. The tablespace X is created without any error and the syntax is correct. The tablespace created will have the default characteristics. It will use an Oracle Managed File of 100M, autoextensible up to 32GB. It will also have LOGGING, NOCOMPRESS, ONLINE, PERMANENT, EXTENT MANAGEMENT LOCAL AUTOALLOCATE, and SEGMENT SPACE MANAGEMENT AUTO properties.

If the tablespace is offline, which statements should be executed to make the USERS tablespace read-only? (Choose all that apply.) ALTER TABLESPACE USERS READ ONLY ALTER DATABASE MAKE TABLESPACE USERS READ ONLY ALTER TABLESPACE USERS ONLINE ALTER TABLESPACE USERS TEMPORARY

Answer: C, A. To make a tablespace read-only, all the data files belonging to the tablespace must be online and available. So bring the tablespace online, and then make it read-only.

Which allocation unit is the smallest? Data file Extent Data block Segment

Answer: C. An extent is composed of two or more data blocks; a segment is composed of one or more extents; and a data file houses all these.

Which of the following statements about tablespaces is true? A tablespace is the physical implementation of a logical structure called a namespace. A tablespace can hold the objects of only one schema. A bigfile tablespace can have only one data file. The SYSAUX tablespace is an optional tablespace created only if you install certain database options.

Answer: C. Bigfile tablespaces can have only a single data file. The traditional or smallfile tablespace can have many data files (the limit is OS-dependent, mostly 1022).

Which data dictionary view can be queried to obtain information about the files that belong to locally managed temporary tablespaces? DBA_DATA_FILES DBA_TABLESPACES DBA_TEMP_FILES DBA_LOCAL_FILES

Answer: C. Locally managed temporary tablespaces are created using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view. The EXTENT_MANAGEMENT column of the DBA_TABLESPACES view shows the type of tablespace. The data files belonging to locally managed permanent tablespaces and dictionary-managed (permanent and temporary) tablespaces can be queried from DBA_DATA_FILES. Locally managed temporary tablespaces reduce contention on the data dictionary tables.

Which compression option should be specified to compress blocks of tables that are used by the OLTP application? COMPRESS NOCOMPRESS COMPRESS FOR OLTP COMPRESS FOR ONLINE DML

Answer: C. The COMPRESS option by itself enables basic compression, and it compresses only direct-load operations. NOCOMPRESS disables compression. COMPRESS for ONLINE DML is an invalid syntax.

You performed the following statement in the database. What actions can you perform on the CUST_INFO table in the CUST_DATA tablespace. (Choose all that apply.) ALTER TABLESPACE CUST_DATA READ ONLY; ALTER TABLE CUST_INFO DROP COLUMN xx; TRUNCATE TABLE CUST_INFO; INSERT INTO CUST_INFO VALUES (...); DROP TABLE CUST_INFO; RENAME CUST_INFO TO CUSTOMER_INFO;

Answer: D, E. When a tablespace is read-only, DML operations and operations that affect data in the table are not allowed. TRUNCATE is not allowed, but the DROP operation is allowed. You can also rename the table using the RENAME statement or the ALTER TABLE statement.

Which statement regarding reclaiming wasted space is true? Segment shrink is accomplished using the ALTER TABLE ... MOVE and ALTER INDEX ... REBUILD statements. Segment shrink and reorganize are similar operations. When a table segment shrink operation is completed, the dependent indexes are in invalid state and need to be rebuilt. A segment shrink operation is applicable only on tablespaces with automatic segment space management.

Answer: D. ALTER TABLE ... MOVE and ALTER TABLE ... REBUILD are not online operations and will require an exclusive lock on the object. Segment shrink is accomplished using the SHRINK SPACE clause of ALTER TABLE and ALTER INDEX. A shrink operation does not invalidate dependent objects.

When a user session is terminated, which processes are responsible for cleaning up and releasing locks? (Choose all that apply.) DBWn LGWR MMON PMON SMON

Answer: D. PMON is responsible for cleaning up failed user processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user. No other process is involved in the session cleanup.

Automatic segment space management on the tablespace causes which of the following table attributes in that tablespace to be ignored? The whole storage clause NEXT and PCTINCREASE BUFFERPOOL and FREEPOOL PCTFREE and PCTUSED

Answer: D. Segment space management refers to free-space management, with automatic segment space management using bitmaps instead of FREELISTS, PCTFREE, and PCTUSED.

How would you drop a tablespace if the tablespace were not empty? Rename all the objects in the tablespace, and then drop the tablespace. Remove the data files belonging to the tablespace from the disk. Use ALTER DATABASE DROP <TABLESPACE NAME> CASCADE. Use DROP TABLESPACE <TABLESPACE NAME> INCLUDING CONTENTS.

Answer: D. The INCLUDING CONTENTS clause is used to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace if the files are not Oracle-managed; you need to do it manually using an OS command. Oracle updates only the control file. To remove the files, you can include the INCLUDING CONTENTS AND DATAFILES clause.

The LRU algorithm is used to manage what part of the Oracle architecture? Users who log on to the database infrequently and may be candidates for being dropped The data file that stores the least amount of information and will need the least frequent backup The tables that users rarely access so that they can be moved to a less active tablespace The shared pool and database buffer cache portions of the SGA

Answer: D. The LRU mechanism ensures that each user's server process can find free space in the shared pool and database buffer cache whenever they need it, but it also keeps frequently used objects cached in those memory areas.

Which of the following is true about EM Database Express? (Choose all that apply.) You can start up and shut down a database using Database Express. You can read the contents of the alert log file. You can modify static initialization parameters. The CREATE DATABASE statement creates the Database Control repository in the database.

B, C. A database cannot be started or stopped using OEM Database Express. The Database Control repository is not created when the CREATE DATABASE statement is executed. DBCA creates the Database Control repository and configures Database Control for you.

A client receives the following error message: "ORA-12154 TNS:could not resolve the connect identifier specified" Which of the following could be possible causes of the error? Choose all that apply. The listener is not running on the Oracle server. The user entered an invalid net service name. The user supplied the correct net service name, but the net service name is misspelled in the tnsnames.ora file on the client file. The listener is not configured to listen for this service.

B, C. Supplying a net service name that is not contained in the tnsnames.ora file can cause this error. Problems with the tnsnames.ora file can cause this error, too. Listener problems will not cause this error.

How do you ensure you are notified when a resumable session is suspended? (Choose two.) You'll be notified by email at the address registered in database properties. Write a custom script to look for "statement in resumable session * was suspended" in the alert log and notify the DBA. Create an AFTER SUSPEND trigger and a code notification. A suspended session displays a message on the screen. Answer:

B, C. There is no out-of-the-box mechanism to notify a suspended resumable session. When a session is suspended, information is written to the alert log, an AFTER SUSPEND trigger is fired, and a resumable session suspended alert is issued.

Your database is not responding and is in a hung state. You want to shut down and start the database to release all resources. Which statements would you use? STARTUP AFTER SHUTDOWN STARTUP FORCE SHUTDOWN FORCE SHUTDOWN ABORT and STARTUP

B, D. STARTUP FORCE will perform a SHUTDOWN ABORT and STARTUP of the database. SHUTDOWN ABORT will terminate all sessions and processes and will shut down the instance.

Choose the best statement from the options related to segments. A contiguous set of blocks constitutes a segment. A nonpartitioned table can have only one segment. A segment can belong to more than one tablespace. All of the above are true.

B. A table or index has a segment. A segment consists of one or more extents. A segment can belong to only one tablespace, but it can span across multiple data files.

Which of the following options prevents a user from reusing a password when they change their password? Setting the initialization parameter NO_PASSWORD_REUSE to TRUE Altering that user's profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX Altering that user's profile to UNLIMITED for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX Using a password verify function to record the new password and comparing the new passwords to those recorded previously

B. Although option D could also work, it involves storing the passwords in a table in the database, which could be a security concern. It also takes a lot more effort to configure and maintain. The better technique is to use the standard database profile features PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX. Setting one of these profile parameters to UNLIMITED and the other to a specific value prevents passwords from being reused. If both of these profile parameters are set to UNLIMITED, these parameters are essentially disabled. There is no initialization parameter called NO_PASSWORD_REUSE.

Which statement regarding Unified Auditing is true? Unified Auditing is enabled by setting the parameter AUDIT_TRAIL=UNIFIED. Unified Auditing writes audit records to the table owned by user AUDSYS. You have to log in as SYSDBA to purge the unified audit-trail records. Audit records for RMAN and Datapump are written to audit tables in SYS schema.

B. Audit records are written to a read-only table owned by AUDSYS user. The view UNIFIED_AUDIT_TRAIL shows the audit trail records.

You need to find the directory where the Oracle alert log is being written. Which initialization parameter can be best used to list the full directory path of the alert log location? ALERT_LOG_DEST BACKGROUND_DUMP_DEST DIAGNOSTIC_DEST INIT_LOG_DUMP_DEST

B. DIAGNOSTIC_DEST is the initialization parameter that determines where the Automatic Diagnostic Repository home is located. The alert log file would be in the <diagnostic_dest>/diag/rdbms/<dbname>/<instancename>/alert directory. A text version of the alert log is in the <diagnostic_dest>/diag/rdbms/<dbname>/<instancename>/trace directory. But BACKGROUND_DUMP_DEST shows the full path of the trace directory location.

Communication between two Oracle databases is configured using: Database Resident Connection Pooling Database link Connection Manager Oracle Net Manager

B. Database links facilitate communication between databases. Using a database link access syntax (@dblink_name), data from a remote database is available locally.

Which network architecture connection uses session memory from the SGA? Dedicated Server Shared Server Database Resident Connection Pooling All of the above

B. Dedicated server uses session memory from PGA. A pooled server connection is similar to dedicated server once connected; therefore, the memory is coming from PGA as well. For a shared server, the server process is shared; therefore, the request and response information must be kept in the SGA.

When an incremental checkpoint happens in a database, which file(s) are updated with the checkpoint position? Choose all options that are correct. Data files Control files Initialization Parameter Files Redo log files Archive log files

B. During an incremental database checkpoint, the control file is updated with the checkpoint position; data files are not updated. Data file headers are updated with checkpoint information by the DBWn process when dirty buffers are written to the files. During threaded checkpoint or full database checkpoint, all file headers are updated. This happens during a normal shutdown as well as during online log switch.

How do you manage fine-grained auditing? With the AUDIT and NOAUDIT statements With the DBMS_FGA package With the GRANT and REVOKE statements With the DBMS_AUDIT_MGMT package and CREATE_POLICY procedure

B. Fine-grained auditing is managed using the DBMS_FGA package. The AUDIT and NOAUDIT statements are used to manage statement, privilege, and object auditing. The GRANT and REVOKE statements are used to manage system, object, and role privileges. FGA compliments Unified Auditing by enabling audit conditions to be associated with specific columns.

Examine the CREATE USER statement and choose which of the following options best applies. Larger View JOHN will not be able to log in to the database using SQL*Plus until the DBA changes his password. JOHN is authenticated by the database. When tables are being created, if JOHN did not specify the TABLESPACE clause, the table will be created on the DATA01 tablespace. Specifying unlimited space quota on INDEX01 is a redundant step because INDEX01 is JOHN's default tablespace.

B. JOHN will be able to log in to the database using SQL*Plus, and Oracle will prompt for new password when John logs in the first time. Because John's default tablespace is INDEX01, the tables and indexes created will be on the INDEX01 tablespace if the TABLESPACE clause is omitted. Although INDEX01 is the default tablespace, to create objects on INDEX01 or any other tablespace, a specific space quota needs to be defined, or the user should have the UNLIMITEDTABLESPACE system privilege.

Which of the following is the proper order of Oracle's storage hierarchy, from smallest to largest? Operating-system block, database block, segment, extent Operating-system block, database block, extent, segment Segment, extent, database block, operating-system block Segment, database block, extent, operating-system block

B. Multiple operating-system blocks make up database blocks, contiguous chunks of which make up extents. A segment consists of one or more extents.

DBA user MIKE ran the SQL statement CREATE DATABASE LINK SCOTT.SALES_LINK.BJS.COM CONNECT TO SALES_INT IDENTIFIED BY SALESPWD1 USING 'ocasvr:1522/ocadb'. Choose the option that is true. Use of 'ocasvr:1522/ocadb' is invalid in database link definition. A private database link is created under user MIKE. A private database link is created under user SCOTT. If the SALES_INT user does not exist or its password is not SALESPWD1, the database link will not be created.

B. Private database links are created under the user who executes the CREATE DATABASE LINK statement. You cannot create a database link under another schema. A link by the name SCOTT.SALES_LINK.BJS.COM is created under schema MIKE. You can use the Easy Connect syntax or the Oracle Net connect syntax, or you can resolve the alias name by using tnsnames.ora in the USING clause. During database link creation, a connection is not made to the target database to validate the connection or credentials.

All of the following are reasons to configure the server using Shared Server except which one? Overall memory utilization is reduced. The system is predominantly used for decision support with large result sets returned. The system is predominantly used for small transactions with many users. The number of idle connections on the server is reduced.

B. Shared Server is a scalability option of Oracle. It provides a way to increase the number of supported user processes while reducing the overall memory usage. This configuration is well suited to high-volume, small-transaction-oriented systems with many users connected. Because users share processes, the number of overall idle processes is also reduced. It is not well suited for large data-retrieval-type applications such as decision support.

The DIAGNOSTIC_DEST parameter is not set up in the initialization-parameter file. The value of the ORACLE_HOME environment variable is /u01/app/oracle/product/12.1.0, and the value of ORACLE_BASE is /u01/app/oracle. The database and instance name is xyz. What is the location of the text-alert log file for the xyz database? /u01/app/oracle/product/12.1.0/log/rdbms/xyz/xyz/trace /u01/app/oracle/diag/rdbms/xyz/xyz/trace /u01/app/oracle/diag/rdbms/xyz/xyz/alert /u01/app/oracle/product/12.1.0/diag/rdbms/xyz/xyz/trace /u01/app/oracle/log/rdbms/xyz/xyz/trace

B. The alert log file in Oracle Database 12c is saved in the $ORACLE_BASE/diag/rdbms/<dbname>/<instancename>/trace directory. The XML version of the alert log file is in the $ORACLE_BASE/diag/rdbms/<dbname>/<instancename>/alert directory.

The Oracle Universal Installer is started by executing which program? emctl runInstaller ouistart isqlplusctl

B. The runInstaller executable performs a preinstall check of the operating system and hardware resources before starting the OUI graphical tool.

If your database name is PROD and your instance name is PROD1, what would be the name of the text-alert log file? alertPROD.log alert_PROD1.log PROD1alert.log PROD_alert.log

B. The text-alert log file has the name alert_<instancename>.log. For most non-RAC databases, the instance name and database name would be the same.

Which of the following SQL statements allows user augustin to use the privileges associated with the password-protected role info_czar that has been granted to him? set role all; alter user augustin default role all; alter session enable role info_czar; alter session enable info_czar identified by brozo;

B. To enable a password-protected role, you need to either execute a SET ROLE statement specifying the password or alter the user to make the role a default role. Default roles do not require a SET ROLE statement or a password to become enabled.

User system granted the SELECT ANY TABLE privilege to user ian using WITH ADMIN OPTION. Ian then granted SELECT ANY TABLE to user stuart. Ian has left the company, and his account has been dropped. What happens to Stuart's privileges? Stuart loses his privileges. Stuart retains his privileges. Stuart loses his privileges if Ian was dropped with the CASCADE REVOKE option. Stuart retains his privileges if Ian was dropped with the NOCASCADE REVOKE option.

B. When system privileges are granted through an intermediary, they are not affected when the intermediary is dropped. CASCADE REVOKE and NOCASCADE REVOKE are not part of the GRANT statement syntax.

From the following list, choose two processes that are optional in an Oracle Database 12c database. MMON MMNL ARCn MMAN

C, D. MMON (Manageability Monitor) captures the AWR database and performs ADDM analysis. MMNL (Manageability Monitor Lite) performs tasks related to active session-history sampling and metrics computation. The ARCn (Archiver) process is responsible for writing redo log copies to the archive log location. This process is enabled only when the database is running in ARCHIVELOG mode. MMAN (Memory Manager) is responsible for resizing the memory components when required. This process is active only when Automatic Memory operations are configured.

You want to start up the database using a binary initialization file. What is another name for this file? Configfile Pfile Spfile init_pfile.ora

C. An spfile is another term for a server-side binary file that Oracle reads when a database startup is performed. This binary file contains all the nondefault initialization parameters used at startup.

Which component is not part of an Oracle instance? System global area Process monitor Control file Shared pool None Answer:

C. Control file, data file, and redo log files are part of the Oracle database. The Oracle instance constitutes the memory structures and background processes.

You created a database user using the following statement. Which option will connect the user successfully to the database? CREATE USER JOHN IDENTIFIED BY John1; GRANT CONNECT TO JOHN; CONNECT JOHN/JOHN1 CONNECT JOHN/john1 CONNECT john/John1 All of the above Answer:

C. In Oracle Database 12c, user passwords are case sensitive. The username is not case sensitive if you did not enclose it in double quotes.

What is the first step that the dispatcher performs after it receives a request from the user? Pass the request to a shared server. Place the request in a request queue in the PGA. Place the request in a request queue in the SGA. Process the request.

C. Once a dispatcher receives a request from the user process, it places the request on the request queue. Remember that in a Shared Server environment, a request can be handled by a shared server process. This is made possible by placing the request and user information in the SGA.

Which of the following SQL statements disconnects a session after it has been idle for 30 minutes? alter session set idle_timeout=30; alter session set idle_timeout=1800; alter profile default limit idle_time 30; alter profile default set idle_timeout 30;

C. Profiles limit the amount of idle time, CPU time, logical reads, or other resource-oriented session limits. Option C uses the correct syntax to limit idle time for a session to 30 minutes.

Which of the following is false about request queues? They reside in the SGA. They are shared by all the dispatchers. Each dispatcher has its own request queue. The shared server processes remove requests from the request queue.

C. Request queues reside in the SGA, and there is one request queue per instance. This is where shared server processes pick up requests that are made by users. Dispatchers have their own response queues, but they share a single request queue.

Choose the information that is not part of a ROWID. Data object number Relative file number Segment ID where the block belongs Block number in the file Answer:

C. Segment ID is not part of ROWID. ROWID includes the data object number, relative file number where the block is stored, the block number in the file, and the row number.

Of the following privileges, which is the least privilege required to create and manage Unified Auditing in a database, including purging of audit trail? SYSDBA DBA AUDIT_ADMIN AUDIT ANY

C. The AUDIT_ADMIN role is required to manage Unified Auditing in Oracle Database 12c. This role is granted to SYS and SYSTEM. An audit policy can be created using the AUDIT ANY privilege, but not other administrative actions. DBA and SYSDBA roles are not required, though those privileges work.

If you want to capture the SQL statement and bind variables when performing a standard statement audit, which value should the AUDIT_TRAIL parameter have? NONE DB DB,EXTENDED OS OS,EXTENDED

C. The AUDIT_TRAIL parameter with the value DB,EXTENDED enables you to capture SQL statements and bind variables in auditing. OS,EXTENDED is not a valid value for AUDIT_TRAIL.

You want to create a database using the DBCA with DB_BLOCK_SIZE as 32KB. Which statement is true? A block size of 32KB is not allowed in Oracle Database 12c. You must choose the Data Warehouse template in the DBCA. You must choose the Custom template in the DBCA. You must set the environment variable DB_BLOCK_SIZE to 32,768.

C. The Custom template lets you choose the database block size in the DBCA. If the template includes data files, the block size of the template cannot be changed. The predefined templates that come with data files have the block size at 8KB.

What is the process that notifies the listener after a database connection is established? SMON PMON LREG LGWR Answer:

C. The LREG process notifies the listener after a client connection is established. This is so that the listener can keep track of the number of connections being serviced by each dispatcher. In versions prior to Oracle Database 12c, the functions of LREG were performed by PMON.

In which of the following files would you find the Shared Server configuration parameters? listener.ora mts.ora init.ora tnsnames.ora sqlnet.ora

C. The Shared Server configuration parameters exist in the init.ora or the SPFILE file on the Oracle Server machine.

Which statement is true regarding the SYSTEM tablespace? It can be made read-only. It can be offline. Data files can be renamed. Data files cannot be resized.

C. The data files belonging to the SYSTEM tablespace can be renamed when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement.

Which background process guarantees that committed data is saved even when the changes have not been recorded in data files? DBWn PMON LGWR CKPT ARCn

C. The log writer (LGWR) process writes the redo log buffer information to the online redo log files. A commit operation is completed only after the redo buffer is written to online redo log files.

Which of the following startup options does not perform a database recovery? STARTUP STARTUP FORCE RESTRICT STARTUP NOMOUNT STARTUP OPEN STARTUP RESTRICT

C. The recovery of a database occurs when the database moves from the MOUNT mode to the OPEN mode. All these options attempt to start up and open the database except for option C, which only puts the database in NOMOUNT mode.

Which component of the SGA contains the parsed SQL code? Database buffer cache Dictionary cache Library cache Parse cache

C. The shared SQL area is stored in the library cache in a shared pool and is shared between users. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The database buffer cache has the data blocks cached. The dictionary cache caches data dictionary information. There is no SGA component called the parse cache.

Which data dictionary view is used to view the current values of parameters? V$DATABASE V$SPPARAMETER V$PARAMETER V$SYSPARAMETER Answer:

C. V$PARAMETER shows information about the parameters and their current values in the database. V$SPPARAMTER shows the information as read from the spfile.

User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect will it have on the JOBS table? ALTER USER JAMES QUOTA 0 ON USERS; No more rows can be added to the JOBS table. No new blocks can be allocated to the JOBS table. No new extents can be allocated to the JOBS table. The table JOBS cannot be accessed. The table is truncated.

C. When a space quota is exceeded or a quota is removed from a user on a tablespace, the tables remain in the tablespace, but no new extents can be allocated. New rows can be inserted into the table as long as the table does not require Oracle to allocate a new extent in the table.

When dynamic service registration is used, you will not see the service listed in which of the following files where it would normally be located? sqlnet.ora tnsnames.ora listener.ora None of the above

C. When services are dynamically registered with the listener, their information is not present in the listener.ora file.

Your database name is OCA12C. The options show the files that are available in the $ORACLE_HOME/dbs directory. Which file is used to start up the database instance when you issue the STARTUP command? initOCA12C.ora OCA12Cspfile.ora spfile.ora init.ora Answer:

C. When starting the instance, Oracle looks for spfileOCA12C.ora file. If it cannot find that file, it looks for spfile.ora. If that file is not found, Oracle looks for the initOCA12C.ora file.

You noticed that the current value of the UNDO_RETENTION parameter is 900 and is too low for some of your transactions. The database was created using DBCA. You issue the following statement: ALTER SYSTEM SET UNDO_RETENTION=4800; Which option is true? UNDO_RETENTION is a static parameter and, therefore, cannot be changed using ALTER SYSTEM. The change will be available to the instance only after a database cycle. The value is changed in memory, and when the database restarts the next time, the new value will be preserved when using the spfile. The value is changed only in memory, and the server parameter file needs to be updated for the change to persist across database shutdowns.

C. When using ALTER SYSTEM to change parameter values, the change is made to the server parameter file (spfile) too, because the default for the SCOPE clause is BOTH. Option D would have been correct, if the pfile were used to start up the database. When a database is created using DBCA, the parameter file created is always spfile.

Which of the following statements has the correct syntax and gives the ability to grant the privilege to other users? grant alter any table with grant option to desmond; grant alter on gl.accounts to desmond with admin option; grant alter any table to desmond with grant option; grant alter any table to desmond with admin option;

D. Conferring the ability to further grant the privilege requires the keywords WITH ADMIN OPTION for system or role privileges or the keywords WITH GRANT OPTION for object privileges. Only option D has both the correct syntax and the correct keywords. Option B would be correct if WITH GRANT OPTION were used. Option C would be correct if WITH ADMIN OPTION were used. Option A is not syntactically correct.

The DBA unknowingly terminated the process ID belonging to the PMON process of Oracle Database 12c database using the kill -9 command on Unix. Choose the best answer: Oracle spawns another PMON process automatically. The database hangs, and the DBA must manually start a PMON process. If the database is in ARCHIVELOG mode, Oracle automatically starts another PMON process and recovers from the database hang. The instance crashes and needs to be restarted.

D. PMON is one of the critical processes of Oracle Database 12c, and terminating that process will crash the database. During instance startup, Oracle will require the redo log files with status CURRENT as well as ACTIVE in order to perform instance recovery.

Which of the following system privileges should be granted judiciously because they can allow the grantee to masquerade as another user? CREATE ANY JOB ALTER USER CREATE ANY PROCEDURE All of the above

D. The CREATE ANY JOB and CREATE ANY PROCEDURE system privileges allow the grantee to create and run programs with the privileges of another user. The ALTER USER privilege allows the grantee to change a user's password, connect as that user, and then change the password back. These are all powerful system privileges and should be restricted to as few administrative users as practical.

Under normal circumstances, which of the following actions or events is not found in the Oracle alert log? Database startup and shutdown information Nondefault initialization parameters ORA-00600 errors New columns added to a user table

D. The Oracle alert log contains a chronological history of administrative events and actions and certain types of database errors that occur within the database. Adding a column to a user table is not an administrative action and is not recorded in the alert log. Adding a column will be written to alert log if the parameter enable_ddl_logging is set to TRUE; the default is FALSE.

Which script creates the database dictionary? dictionary.sql catdict.sql catproc.sql catalog.sql

D. The catalog.sql script creates the data dictionary views, dynamic performance views, and synonyms.

Which statement is true? A database can have only one control file. A database must have at least two control files. A database may have zero or more control files. A database must have at least one control file.

D. The control file is the most key file in an Oracle database. Due to its importance, it is a good practice to have two more copies of the file. A database must have at least one control file to start the database.

Which SGA component will you increase or configure so that RMAN tape backups do not use memory from the shared pool? Java pool Streams pool Recovery pool Large pool Answer:

D. The large pool is configured so that RMAN does not use the shared pool; therefore, the shared pool is totally dedicated to application space.

Which of the following is true about Shared Server? Dedicated connections cannot be made when Shared Server is configured. It is recommended that DSS type batch jobs be performed when connected via Shared Server. The database can be started when connected via Shared Server. The database cannot be stopped when connected via Shared Server.

D. Users can still request dedicated connections in a Shared Server configuration. Bequeathed and dedicated connections are one and the same. The database cannot be stopped or started by the DBA when connected over a Shared Server connection.

User John has updated several rows in a table and issued a commit. What does the DBWn (database writer) process do at this time in response to the commit event? Writes the changed blocks to data files. Writes the changed blocks to redo log files. Triggers checkpoint and thus LGWR writes the changes to redo log files. Does nothing.

D. When a user issues a commit, the LGWR process makes sure the redo log buffer is written to the online redo log files. Database writer takes no action against the commit event.

Which startup options must be used to start the instance when you create a new database? STARTUP FORCE STARTUP MOUNT STARTUP RESTRICT STARTUP NOMOUNT

D. When creating a new database or creating a control file, the database should be in the NOMOUNT state.

You've been asked to install Oracle Database 12c on a new Linux server. You're likely to ask the Unix system administrator to do all but which one of the following for you in order to get the new server ready for Oracle? Modify the server's kernel parameters. Create a new Unix user to own the Oracle software. Create the mount points and directory structure using the OFA model. Determine which directory will be used for $ORACLE_HOME.

D. While the Unix system administrator is responsible for creating volume groups and mount points, the DBA generally decides where the Oracle binaries will be installed—the location derived from $ORACLE_BASE or designated by the $ORACLE_HOME environment variable.

By default, how much space can any account use for a new table? None Up to the current free space in the tablespace Unlimited space, including autoextends Up to the default quota established at tablespace creation time

A. By default, user accounts have no quota in any tablespace. Before a user can create a table or an index, you need to either give the user a quota in one or more specific tablespaces or grant the UNLIMITED TABLESPACE system privilege to give an unlimited quota (including autoextends) in all tablespaces.

Querying the V$LOG file shows the following information. Which redo group files are required for instance crash recovery? SQL> select GROUP#, ARCHIVED, STATUS from V$LOG; GROUP# ARC STATUS ---------- --- ---------------- 1 NO CURRENT 2 NO INACTIVE 3 NO INACTIVE 4 NO ACTIVE Group 1 and 4 Group 2 and 3 Groups 1 through 4 Group 1 Group 4

A. Redo log groups with status CURRENT and ACTIVE are required during instance crash recovery.

All of the following are database-management options within the Database Configuration Assistant except which one? Change Database Initialization Parameters Create a Database Manage Templates Delete a Database Answer:

A. The Database Configuration Assistant lets you create databases, manage templates, add database options, and delete databases. Although you can change initialization parameters when you are defining a database, this is not one of the management options available.

Which initialization parameter cannot be changed after the database is created? DB_BLOCK_SIZE DB_NAME CONTROL_FILES None. All parameters can be changed as and when required.

A. The block size of the database cannot be changed after database creation. The database name can be changed after the control file is re-created with a new name, and the CONTROL_FILES parameter can be changed after the control files are copied to the new location.

Which of the following shutdown statements does not perform a clean shutdown? SHUTDOWN ABORT SHUTDOWN TRANSACTIONAL SHUTDOWN SHUTDOWN IMMEDIATE All of these are considered clean shutdowns.

A. When you perform a SHUTDOWN ABORT, Oracle never performs a clean shutdown. All other types of shutdowns are considered clean shutdowns because Oracle will not have to perform a recovery on a subsequent database startup.

Which of the following SQL statements limits attempts to guess passwords by locking an account after three failed logon attempts? alter profile default limit failed_login_attempts 3; alter system set max_logon_failures = 3 scope=both; alter user set failed_login_attempts = 3; alter system set failed_login_attempts = 3 scope=both;

A. You limit the number of failed logon attempts with a profile.

Which is not a type of segment that is stored in a tablespace? Undo Redo Permanent Temporary

Answer: B. Redo information is not stored in a segment; it is stored in the redo logs. Undo segments are stored in the undo tablespace; temporary segments are in the temporary tablespace; and permanent segments go into all the other tablespaces.

Choose two SGA structures that are required in every Oracle instance. Large pool Shared pool Buffer cache Java pool

B, C. Database buffer cache, shared pool, and log buffer are required; they are configured automatically in every instance. It is better to use Automatic Memory Management or Automatic Shared Memory Management, so that the DBA does not need to tune individual components.

Which of the following is the correct way to start a listener called LISTENER? lsnrctl startup listener lsnrctl start netca start netmgr start listener Answer:

B. Because the default listener name is LISTENER, simply enter lsnrctl start. The name LISTENER is assumed to be the listener to start in this case.

You would like to perform maintenance on the system and limit access to only the DBA staff during the maintenance window. Which of the following startup options should you use? STARTUP NOMOUNT RESTRICT STARTUP RESTRICT STARTUP MOUNT RESTRICT STARTUP MOUNT FORCE RESTRICT

B. The STARTUP RESTRICT choice opens the database and allows only users with RESTRICTED database access to connect and use it.

When configured in Shared Server mode, which of the following is contained in the PGA? Cursor state Sort information User session data Stack space None of the above

D. A small PGA is maintained even though most of the user-specific information is moved to the SGA (specifically called the UGA in the shared pool or the large pool). The only information left in the reduced PGA is stack space.

Connection Manager provides which of the following? Multiplexing Cross-protocol connectivity Network access control All of the above

D. Connection Manager is a middleware solution that provides for the multiplexing of connections, cross-protocol connectivity, and network access control. All the answers describe Connection Manager.

What portion of the tnsnames.ora file specifies the name or IP address of the server where the listener process is listening? CONNECT_DATA SERVER SERVICE_NAME HOST

D. The HOST portion specifies the name of the server to contact. CONNECT_DATA specifies the database service to connect to. The PORT portion specifies the location where the listener is listening on the HOST. Option C, SERVICE_NAME, is the name of the actual database service.

Which of the following statements enables the role user_admin in the current session? alter session enable role user_admin; alter session set role user_admin; alter role user_admin enable; set role user_admin;

D. The SET ROLE statement enables or disables roles in the current session.