D330 - Data Systems Administration
Which profile parameters in Oracle can be used to prevent passwords from being reused? A. PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX B. PASSWORD_HISTORY and PASSWORD_REUSE_LIMIT C. PASSWORD_REUSE_TIME and PASSWORD_HISTORY D. PASSWORD_REUSE_MAX and PASSWORD_REUSE_LIMIT
A
Which role is required to manage Unified Auditing in Oracle Database 12c? A) AUDIT_ADMIN B) AUDIT_USER C) DBA D) SYSDBA
A) AUDIT_ADMIN
Which tools can be used together to determine if I/O can be minimized and overall DB Time reduced for a targeted SQL statement? A) SQL Tuning Advisor and SQL Access Advisor B) Automatic Shared Memory Management and Automatic Memory Management C) SQL Performance Analyzer and SQL Access Advisor D) Database Replay and SQL Tuning Advisor
A) SQL Tuning Advisor and SQL Access Advisor
How do you enable Automatic Memory Management in Oracle Database? A) Set a nonzero value for MEMORY_TARGET B) Set a nonzero value for SGA_TARGET C) Set a nonzero value for AMM_TARGET D) Set a nonzero value for AUTOMATIC_MEMORY
A) Set a nonzero value for MEMORY_TARGET
Which type of auditing rule records table insert operations? A. Action B. Privilege C. Role D. Object
A. Action
Choose the best option regarding extents. A. An extent is a grouping of Oracle blocks. B. An extent is a grouping of OS blocks. C. An extent is a grouping of segments. D. An extent is allocated when a table is created.
A. An extent is a grouping of Oracle blocks. 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.
How can you add more space to a tablespace? A. By adding a data file B. By increasing the size of an existing data file C. By creating a new tablespace D. By adding a new segment
A. By adding a data file
Which file must be present to start an instance of a database? A. Control B. Redo C. Archive D. Alert
A. Control
Which template in the DBCA allows you to choose the database block size? A. Custom template B. General Purpose template C. Transaction Processing template D. Data Warehouse template
A. Custom template
Which method allows any Oracle release to be migrated to Oracle 12c? A. Export and Import B. Manual Upgrades C. Transportable Tablespaces D. Database Upgrade Assistant
A. Export and Import
What is the primary purpose of database links? A. Facilitate communication between databases B. Create new tablespaces C. Establish user privileges D. Optimize query performance
A. Facilitate communication between databases
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 A. Group 1 and 4 B. Group 2 and 3 C. Groups 1 through 4 D. Group 1 E. Group 4
A. Group 1 and 4
How many data files can a bigfile tablespace have? A. Only one B. Up to 1022 C. Unlimited D. Depends on the operating system
A. Only one
Among the failure events, which is the most serious and may cause data loss? A. The loss of an entire redo log-file group but no loss in any other group B. The loss of one member of each redo log-file group C. The failure of the ARCn background process D. The failure of the LGWR background process
A. The loss of an entire redo log-file group but no loss in any other group
What is the correct syntax to use Easy Connect and DRCP? A. host:port/service_name:POOLED B. host:port/service_name C. host:port/POOLED
A. host:port/service_name:POOLED
If the tablespace is offline, which statements should be executed to make the USERS tablespace read-only? (Choose Two) 1. ALTER TABLESPACE USERS READ ONLY 2. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY 3. ALTER TABLESPACE USERS ONLINE 4. ALTER TABLESPACE USERS TEMPORARY A) 1, 2 B) 1, 3 C) 2, 3 D) 3, 4
B) 1, 3
When does ADDM run? (Choose two.) 1. When an AWR snapshot is taken automatically by the MMON process 2. When an AWR snapshot is taken automatically by the MMNL process 3. When an AWR snapshot is taken manually by DBA 4. When the AutoTask process runs hourly to take workload snapshots A) 1, 2 B) 1, 3 C) 2, 3 D) 3, 4
B) 1, 3
What is the minimum COMPATIBLE value for Oracle Database 12c? A) 10.0.0 B) 11.0.0 C) 11.1.0.7 D) 11.2.0.2
B) 11.0.0
In ARCHIVELOG mode, up to which point can you recover the database? A) Up to the last checkpoint B) Up to the last COMMIT statement C) Up to the last executed SQL statement D) Up to the last saved image copy
B) Up to the last COMMIT statement
When is a commit operation considered complete in an Oracle database? A. After the database writer processes the commit event B. After the redo buffer is written to the online redo log files C. After the query is aged out of the library cache D. After the instance recovery is performed
B. After the redo buffer is written to the online redo log files
What happens during a SHUTDOWN ABORT in an Oracle database? A. All sessions and processes are terminated gracefully B. All sessions and processes are terminated immediately C. The instance is shut down after waiting for all transactions to complete D. The instance is restarted
B. All sessions and processes are terminated immediately
Which parameter shows the full path of the trace directory location in an Oracle database? A. DIAGNOSTIC_DEST B. BACKGROUND_DUMP_DEST C. CORE_DUMP_DEST D. USER_DUMP_DEST
B. BACKGROUND_DUMP_DEST
When an incremental checkpoint happens in a database, which file(s) are updated with the checkpoint position? A. Data files B. Control files C. Initialization Parameter Files D. Redo log files E. Archive log files
B. Control files
What are the main components of the Oracle database? A. SGA and Oracle background processes B. Control files, data files, and redo logs C. Database blocks and extents D. Segments and tablespaces
B. Control files, data files, and redo logs
In which view can you find information about data files (temporary files) belonging to locally managed temporary tablespaces? A. DBA_TABLESPACES B. DBA_TEMP_FILES C. DBA_DATA_FILES D. DBA_SEGMENTS
B. DBA_TEMP_FILES
What happens during a checkpoint in an Oracle database? A. Data files are updated with the changed blocks by the LGWR process B. Data files are updated with the changed blocks by the DBWn process C. The redo log buffers are written to the redo log files on disk D. The server parameter file is updated with new parameter values
B. Data files are updated with the changed blocks by the DBWn process
Which additional information can administrators collect by configuring the Automatic Workload Repository (AWR) to use the ALL statistics collection level? A. Row count B. Execution plan C. Table histogram D. Column density
B. Execution plan
What is the correct hierarchy for data organization within an Oracle database? A. Data block > Extent > Segment > Data file B. Extent > Data block > Segment > Data file C. Data file > Segment > Extent > Data block D. Segment > Extent > Data block > Data file
B. Extent > Data block > Segment > Data file
What clause should be used to drop a tablespace that is not empty? A. EXCLUDING CONTENTS B. INCLUDING CONTENTS C. CASCADE CONSTRAINTS D. PURGE
B. INCLUDING CONTENTS
Which parameter is used to enable Automatic Memory Management? A. AMM_TARGET B. MEMORY_TARGET C. SGA_TARGET D. AUTOMATIC_MEMORY
B. MEMORY_TARGET
In which state should the database be when creating a new database or control file? A. MOUNT B. NOMOUNT C. OPEN D. CLOSED
B. NOMOUNT
Can the block size of the database be changed after database creation? A. Yes B. No
B. No
In a pooled server connection, where does the memory for session data come from? A. SGA B. PGA C. UGA D. Large Pool
B. PGA
Which is not a type of segment that is stored in a tablespace? A. Undo B. Redo C. Permanent D. Temporary
B. Redo
Which network architecture connection uses session memory from the SGA? A. Dedicated Server B. Shared Server C. Database Resident Connection Pooling D. All of the above
B. Shared Server
What is the advantage of using Automatic Memory Management or Automatic Shared Memory Management in an Oracle database? A. The DBA can fine-tune individual components. B. The DBA does not need to tune individual components. C. It allows the database to start without a control file. D. It makes the redo log buffer dynamically alterable.
B. The DBA does not need to tune individual components.
Your users have called to complain that system performance has suddenly decreased markedly. Which is the most likely place to look for the cause of the problem in EM Database Control? A. The Main screen B. The Performance tab C. The Administration tab D. The Maintenance tab
B. The Performance tab
What will happen if the PMON process is terminated in Oracle Database 12c? A. The database will continue to function normally B. The database will crash C. The database will enter recovery mode D. The database will perform a full checkpoint
B. The database will crash
All of the following are reasons to configure the server using Shared Server except which one? A. Overall memory utilization is reduced. B. The system is predominantly used for decision support with large result sets returned. C. The system is predominantly used for small transactions with many users. D. The number of idle connections on the server is reduced.
B. The system is predominantly used for decision support with large result sets returned.
What is the effect of granting system privileges through an intermediary when the intermediary is dropped? A. The system privileges are dropped B. The system privileges are not affected C. The system privileges are suspended D. The system privileges are transferred to another user
B. The system privileges are not affected
What happens to object privileges granted through an intermediary when the intermediary is dropped? A. They are explicitly dropped with CASCADE REVOKE B. They are implicitly dropped C. They are maintained using NOCASCADE REVOKE D. They are unaffected
B. They are implicitly dropped
When is the MMAN (Memory Manager) process active in an Oracle database? A. When the database is running in ARCHIVELOG mode B. When Automatic Memory operations are configured C. When active session-history sampling is performed D. When the large pool is configured
B. When Automatic Memory operations are configured
How do you ensure you are notified when a resumable session is suspended? (Choose two.) A. You'll be notified by email at the address registered in database properties. B. Write a custom script to look for "statement in resumable session * was suspended" in the alert log and notify the DBA. C. Create an AFTER SUSPEND trigger and a code notification. D. A suspended session displays a message on the screen.
B. Write a custom script to look for "statement in resumable session * was suspended" in the alert log and notify the DBA. C. Create an AFTER SUSPEND trigger and a code notification.
If your database name is PROD and your instance name is PROD1, what would be the name of the text-alert log file? A. alertPROD.log B. alert_PROD1.log C. PROD1alert.log D. PROD_alert.log
B. alert_PROD1.log
How can you start a default listener on port 1521? A. lsnrctl start LISTENER B. lsnrctl start C. lsnrctl start DEFAULT D. lsnrctl start PORT_1521
B. lsnrctl start
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? (Choose Two) 1. STARTUP AFTER SHUTDOWN 2. STARTUP FORCE 3. SHUTDOWN FORCE 4. SHUTDOWN ABORT and STARTUP A) 1, 2 B) 1, 3 C) 2, 4 D) 2, 3
C) 2, 4
How long are database statistics retained in the AWR by default? A) 1 day B) 3 days C) 8 days D) 15 days
C) 8 days
Which command must be run before you can perform REPAIR FAILURE with Data Recovery Advisor? A) LIST FAILURE B) CHANGE FAILURE C) ADVISE FAILURE D) DIAGNOSE FAILURE
C) ADVISE FAILURE
When using ALTER SYSTEM to change parameter values, what is the default SCOPE clause value? A. MEMORY B. SPFILE C. BOTH B & D D. PFILE
C. BOTH B & D
Suppose you have used EM Database Control to drill down into ADDM findings and have found that a single SQL statement is causing the majority of the I/O on your system. Which of the following advisors is best suited to troubleshoot this SQL statement? A. SQL Tuning Advisor B. SQL Access Advisor C. Both A and B D. Neither A nor B
C. Both A and B
Which component is not part of an Oracle instance? A. System global area B. Process monitor C. Control file D. Shared pool E. None
C. Control file
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? A. NONE B. DB C. DB,EXTENDED D. OS E. OS,EXTENDED
C. DB,EXTENDED
Which allocation unit is the smallest? A. Data file B. Extent C. Data block D. Segment
C. Data block
Which of the following is a performance metric that could be defined as "the amount of work that a system can perform in a given amount of time"? A. Response time B. Uptime C. Throughput D. Runtime
C. Throughput
Which file does the Database Upgrade Assistant (DBUA) obtain its list of databases from? A. tnsnames.ora B. glogin.sql C. host_name.olr D. sqlnet.ora
C. host_name.olr
Which AUDIT_TRAIL parameter value enables capturing SQL statements and bind variables in auditing? A. NONE B. DB C. OS,EXTENDED D. DB,EXTENDED
D. DB,EXTENDED
Which data dictionary view contains information explaining why ADDM made its recommendations? A. DBA_ADVISOR_FINDINGS B. DBA_ADVISOR_OBJECTS C. DBA_ADVISOR_RECOMMENDATIONS D. DBA_ADVISOR_RATIONALE
D. DBA_ADVISOR_RATIONALE
Under normal circumstances, which of the following actions or events is not found in the Oracle alert log? A. Database startup and shutdown information B. Nondefault initialization parameters C. ORA-00600 errors D. New columns added to a user table
D. New columns added to a user table
Which of the following conditions prevents the instance from progressing through the NOMOUNT, MOUNT, and OPEN states? A. One of the redo log-file groups is missing a member. B. The instance was previously shut down uncleanly with SHUTDOWN ABORT. C. Either the spfile or init.ora file is missing. D. One of the five multiplexed control files is damaged. E. The USERS tablespace is offline, with one of its data files deleted.
D. One of the five multiplexed control files is damaged.
Which memory component in an Oracle database is NOT dynamically alterable without restarting the instance? A. Database buffer cache B. Shared pool C. Log buffer D. Redo log buffer
D. Redo log buffer
In the connection string, which part specifies the database service to connect to? A. HOST B. PORT C. CONNECT_DATA D. SERVICE_NAME
D. SERVICE_NAME
Which of the following database options must be upgraded individually when you're upgrading the database using the manual method? A. JServer Java Virtual Machine B. Oracle Real Application Clusters C. Oracle XML Database D. All of the above E. None of the above
E. None of the above
Where are the generated scripts and log files created if $ORACLE_BASE is defined? A) $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade B) $ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade C) $ORACLE_BASE/diag/db_unique_name/preupgrade D) $ORACLE_HOME/diag/db_unique_name/preupgrade
A) $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade
Where are the generated scripts and log files saved when $ORACLE_BASE is defined? A) $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade B) $ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade C) $ORACLE_BASE/rdbms/db_unique_name/preupgrade D) $ORACLE_HOME/rdbms/db_unique_name/preupgrade
A) $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade
Which of the following statements create an Oracle account but let the operating system authenticate logons? (Choose two.) 1. create user ops$admin identified by os; 2. create user ops$admin identified externally; 3. create user ops$admin nopassword; 4. create user ops$admin authenticated by os; A) 1, 2 B) 1, 3 C) 2, 3 D) 2, 4
A) 1, 2
Choose the statements that are resumable. (Choose three.) 1. ALTER TABLE ... SPLIT PARTITION 2. SELECT 3. INSERT INTO ... SELECT 4. CREATE TABLESPACE 5. ALTER TABLE ... SHRINK SPACE A) 1, 2, 3 B) 1, 3, 4 C) 2, 3, 5 D) 3, 4, 5
A) 1, 2, 3
Where are audit records written in Oracle Database 12c? A) A read-only table owned by the AUDSYS user B) A log file in the operating system C) A table owned by the SYS user D) A table owned by the SYSTEM user
A) A read-only table owned by the AUDSYS user
How can you limit the number of failed logon attempts for a user? A) By setting a limit in the user's profile B) By using a GRANT statement
A) By setting a limit in the user's profile
How can you query the status of components and their schema owners in an Oracle Database? A) DBA_REGISTRY B) DBMS_REGISTRY C) DBA_COMPONENTS D) DBMS_COMPONENTS
A) DBA_REGISTRY
Which procedure is used to set the PUBLISH preference to FALSE for a table? A) DBMS_STATS.SET_TABLE_PREFS B) DBMS_STATS.GATHER_TABLE_STATS C) DBMS_STATS.CREATE_STAT_TABLE D) DBMS_STATS.EXPORT_TABLE_STATS
A) DBMS_STATS.SET_TABLE_PREFS
What happens when STATISTICS_LEVEL is set to BASIC? A) Disables Automatic Memory and Shared Memory Management and the memory advisors B) Enables Automatic Memory and Shared Memory Management and the memory advisors C) Enables Automatic Memory Management but disables Shared Memory Management and the memory advisors D) Disables Automatic Memory Management but enables Shared Memory Management and the memory advisors
A) Disables Automatic Memory and Shared Memory Management and the memory advisors
Which parameter specifies the desired time to recover a single instance from a crash or instance failure? A) FAST_START_MTTR_TARGET B) LOG_CHECKPOINT_TIMEOUT C) FAST_START_IO_TARGET D) MTTR_TARGET_ADVICE
A) FAST_START_MTTR_TARGET
What is the main difference between image copies and backup sets? A) Image copies are in OS format, while backup sets are in Oracle proprietary format. B) Image copies are binary compressed, while backup sets contain duplicate data and log files. C) Image copies store multiple data files in a single output file, while backup sets store only a single data file. D) Image copies contain unused blocks, while backup sets do not.
A) Image copies are in OS format, while backup sets are in Oracle proprietary format.
Where is the Text alert log written in the ADR Home directory? A) In the trace directory, named alert_SID.log B) In the alert directory, named log.xml C) In the trace directory, named log.xml D) In the alert directory, named alert_SID.log
A) In the trace directory, named alert_SID.log
Which parameter enables Automatic Memory Management in Oracle Database? A) MEMORY_TARGET B) SGA_TARGET C) PGA_AGGREGATE_TARGET D) STATISTICS_LEVEL
A) MEMORY_TARGET
Which tool can help determine whether the overall size of the SGA is appropriate and if memory is properly allocated to the SGA components? A) Memory Advisor B) SQL Tuning Advisor C) SQL Access Advisor D) Automatic Database Diagnostic Monitor (ADDM)
A) Memory Advisor
What can DBUA do for the listener during the upgrade process? A) Migrate, copy, and configure the listener in the new 12c Oracle home B) Delete the old listener configuration C) Disable the listener during the upgrade D) Set up a new listener for each database instance
A) Migrate, copy, and configure the listener in the new 12c Oracle home
When the WORKAREA_SIZE_POLICY is set to AUTO, what is used to size work areas for memory-intensive operations? A) PGA memory used by the system and the target PGA memory set in PGA_AGGREGATE_TARGET B) SGA memory used by the system and the target SGA memory set in SGA_AGGREGATE_TARGET C) Manual configuration of *_AREA_SIZE parameters D) Automatic Shared Memory Management (ASSM) feature
A) PGA memory used by the system and the target PGA memory set in PGA_AGGREGATE_TARGET
What does DBUA do during a restore from its backup? A) Restores the database files and configuration files B) Upgrades the database components C) Rebuilds the database from scratch D) Restores only the configuration files
A) Restores the database files and configuration files
What is the first step in the manual database upgrade process? A) Run the Pre-Upgrade Information utility preupgrd.sql B) Start the database using STARTUP UPGRADE C) Run the catctl.pl script D) Start up the database and perform utlu121s.sql
A) Run the Pre-Upgrade Information utility preupgrd.sql
What is the requirement for the source database while running the PreUpgrade Information tool? A) Running from the original Oracle database home B) Running in ARCHIVELOG mode C) Running in a separate Oracle home D) Running in a read-only mode
A) Running from the original Oracle database home
What happens when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified and SGA_MAX_SIZE is not specified? A) SGA_MAX_SIZE is set to the larger of MEMORY_TARGET or MEMORY_MAX_TARGET B) SGA_MAX_SIZE is set to the smaller of MEMORY_TARGET or MEMORY_MAX_TARGET C) SGA_MAX_SIZE is set to the sum of MEMORY_TARGET and MEMORY_MAX_TARGET D) SGA_MAX_SIZE remains unchanged
A) SGA_MAX_SIZE is set to the larger of MEMORY_TARGET or MEMORY_MAX_TARGET
What should be done to repair or replace a missing or damaged control file? A) The instance must be shut down. B) The instance must be restarted. C) The instance must be in ARCHIVELOG mode. D) The instance must be in the MOUNT state.
A) The instance must be shut down.
What does the MTTR Advisor help DBAs determine? A) The most effective value for the FAST_START_MTTR_TARGET parameter B) The maximum size of the SGA C) The optimal number of redo log-file groups D) The ideal number of control files
A) The most effective value for the FAST_START_MTTR_TARGET parameter
What does Oracle use DBMS_REGISTRY for during the upgrade process? A) To automatically upgrade the database components B) To store the backup files C) To track the upgrade progress D) To check for compatibility issues
A) To automatically upgrade the database components
What is the purpose of STARTUP UPGRADE? A) To bring up an instance prior to upgrading the database B) To automatically upgrade the database components C) To run the upgrade scripts for the database D) To document the upgrade process
A) To bring up an instance prior to upgrading the database
In Oracle Database 12c, are user passwords case sensitive? A) Yes B) No
A) Yes
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? A. (SERVER=DEDICATED) B. (CONNECT=DEDICATED) C. (INSTANCE=DEDICATED) D. (MULTITHREADED=FALSE) E. None of the above
A. (SERVER=DEDICATED)
In your Oracle Database 11g environment, the ORACLE_SID is set to my11g, the ORACLE_ BASE directory is set to /u01/app/oracle, and the ORACLE_HOME directory is set to /u01/ app/oracle/db/11.2.0. When you run the Pre-Upgrade Information tool on this environment from the /home/dba/preup directory, to which location are the log file and fix-up scripts written? A. /u01/app/oracle/cfgtoollogs/my11g/preupgrade B. /u01/app/oracle/db/11.2.0/cfgtoollogs/my11g/preupgrade C. /home/dba/preup/cfgtoollogs/my11g/preupgrade D. /home/dba/preup
A. /u01/app/oracle/cfgtoollogs/my11g/preupgrade
How would you add more space to a tablespace? (Choose Two.) 1. ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE SIZE < N > 2. ALTER DATABASE DATAFILE <FILENAME> RESIZE < N > 3. ALTER DATAFILE < FILENAME > RESIZE < N > 4. ALTER TABLESPACE < TABLESPACE NAME > DATAFILE < FILENAME > RESIZE < N > A) 1, 2 B) 2, 3 C) 2, 4 D) 3, 4
A. 1, 2
Before manually upgrading an 11.1.0.7 database, what should be the appropriate value of the COMPATIBLE parameter? The current value is the default and is not specified in the parameter file. A. 11.1.0.7 B. 10.2.0.5 C. 12.1.0 D. Do not specify the COMPATIBLE parameter in the parameter file.
A. 11.1.0.7
By default, how long will database statistics be retained in the AWR? A. 8 days B. 30 days C. 7 hours D. Indefinitely
A. 8 days
What does the SERVER=DEDICATED element in a tnsnames.ora file associate with each client connection? A. A committed server process B. A shared server process C. A pooled server process D. A dispatched server process
A. A committed server process
Given the following transactions and sessions named Session1 and Session2: At 8:00, Session1 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='bound' AND LOB_CAT='cancel'; At 8:05, Session1 commits the update transaction. At 8:05, Session2 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='submitted' And LOB_CAT='cancel'; At 8:15, Session1 issues the statement: UPDATE POLICY SET LOB= '81' WHERE status='bound' AND LOB_CAT='cancel'; At 8:30, Session2 issues the statement: UPDATE POLICY SET LOB= '16' WHERE status='bound' AND LOB_CAT='cancel'; What happens after Session2 issues the statement at 8:30? A. A deadlock occurs, and the database cancels Session2. B. The database is updated by the second transaction from Session2. C. A deadlock occurs, and the database cancels Session1. D. The database is not updated, and both sessions are terminated.
A. A deadlock occurs, and the database cancels Session2.
Which command is used to enable the autoextensible feature for a file if the file is already part of a tablespace? A. ALTER DATABASE. B. ALTER TABLESPACE. C. ALTER DATA FILE. D. You cannot change the autoextensible feature once the data file is created.
A. ALTER DATABASE.
Which of the following events is not recorded in the Oracle alert log by default? A. Adding a column to a user table B. Administrative actions C. Database errors D. Instance startup and shutdown
A. Adding a column to a user table
What is the result of the CASCADE option when used with the DROP USER command? A. All the user's objects are removed from the database. B. Any privileges granted by the user are revoked. C. All objects that depended on the users' objects are removed. D. Any user accounts that accessed the users' data are disabled.
A. All the user's objects are removed from the database.
Which is a true statement regarding image copies and backup sets. A. An image copy stores one data file per image copy, and a backup set can store many data files in a single file. B. An image copy stores one data file per image copy, and a backup set consists of one file per data file backed up. C. Both image copies and backup sets use a single file to store all objects to be backed up. D. A backup set stores each data file in its own backup file, but an image copy places all data files into a single output file.
A. An image copy stores one data file per image copy, and a backup set can store many data files in a single file.
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? A. An instance consists of memory structures and processes, whereas a database is composed of physical files. B. An instance is used only during database creation; after that, the database is all that is needed. C. An instance is started whenever the demands on the database are high, but the database is used all the time. D. An instance is configured using a pfile, whereas a database is configured using a spfile.
A. An instance consists of memory structures and processes, whereas a database is composed of physical files.
What method does automatic segment space management use for free-space management? A. Bitmaps B. FREELISTS C. PCTFREE D. PCTUSED
A. Bitmaps
How should an administrator enable mixed-mode auditing for a database? A. By configuring the ORA_ACCOUNT_MGMT predefined policy B. By recompiling the Oracle executable using the uniaud_on parameter C. By setting the parameter COMPATIBLE to 12.1 D. By setting the parameter STATISTICS_LEVEL to ALL
A. By configuring the ORA_ACCOUNT_MGMT predefined policy
Which data pump parameter can an administrator use to perform a metadata-only export? A. CONTENT B. SCHEMAS C. INCLUDE D. ATTACH
A. CONTENT
All of the following are database-management options within the Database Configuration Assistant except which one? A. Change Database Initialization Parameters B. Create a Database C. Manage Templates D. Delete a Database
A. Change Database Initialization Parameters
Which type of connection uses the Oracle Listener to communicate? A. Client with database server B. Browser with application server C. Two application servers D. Client with backup tool
A. Client with database server
Which format minimizes the space required for a full database backup? A. Compressed B. Image C. Backup set D. Binary file
A. Compressed
An administrator plans to use SQL*Loader to import a data file using a fixed-width format. Which file must be configured before importing the file? A. Control B. Data C. Bad D. Discard
A. Control
Which package should an administrator use to configure local extent management for tablespaces? A. DBMS_SPACE_ADMIN B. DBMS_AUDIT_MGMT C. DBMS_TRANSFORM D. DBMS_METADATA
A. DBMS_SPACE_ADMIN
Which tool contains procedures to collect performance information on database objects? A. DBMS_STATS B. Automated Maintenance Tasks C. DBMS_WORKLOAD_REPOSITORY D. Cost-based optimizer
A. DBMS_STATS
Which initialization parameter cannot be changed after the database is created? A. DB_BLOCK_SIZE B. DB_NAME C. CONTROL_FILES D. None. All parameters can be changed as and when required.
A. DB_BLOCK_SIZE
Which initialization parameter determines the location of the Automatic Diagnostic Repository home? A. DIAGNOSTIC_DEST B. BACKGROUND_DUMP_DEST C. CORE_DUMP_DEST D. USER_DUMP_DEST
A. DIAGNOSTIC_DEST
Which of the following initialization parameters specifies the location where the control file trace backup is sent? A. DIAGNOSTIC_DEST B. BACKGROUND_DUMP_DEST C. LOG_ARCHIVE_DEST D. CORE_DUMP_DEST
A. DIAGNOSTIC_DEST
Which method is used to perform an in-place upgrade? A. Database Upgrade Assistant B. Export/import C. Transportable tablespace D. Oracle GoldenGate
A. Database Upgrade Assistant
Which condition is automatically resolved by Oracle without human intervention? A. Deadlocks B. "Snapshot too old" error C. Resumable space allocation D. Flash recovery area
A. Deadlocks
Which privilege provides grantees permission to remove accounts from a database? A. Drop user B. Alter user C. Drop profile D. Alter profile
A. Drop user
When do threaded checkpoint or full database checkpoint occur in an Oracle database? A. During a normal shutdown and during online log switch B. During instance recovery and log switch C. When the PMON process is terminated D. During incremental database checkpoint
A. During a normal shutdown and during online log switch
An administrator attempts to roll back a transaction from the previous day. The rollback attempt fails. Which action will prevent the failure from recurring? A. Enabling the retention guarantee B. Setting the session to be resumable C. Flushing the log buffer D. Restarting the database
A. Enabling the retention guarantee
What additional clause should be used when dropping a tablespace to remove the associated data files? A. INCLUDING DATAFILES B. PURGE DATAFILES C. DELETE DATAFILES D. DROP DATAFILES
A. INCLUDING DATAFILES
In Oracle Database 12c, where is the alert log file saved? A. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/trace directory B. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/alert directory C. In the BACKGROUND_DUMP_DEST directory D. In the DIAGNOSTIC_DEST directory
A. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/trace directory
In which directory can you find the alert log file? A. In the /diag/rdbms/<dbname>/<instance>/alert directory B. In the /diag/rdbms/<dbname>/<instance>/trace directory C. In the BACKGROUND_DUMP_DEST directory D. In the DIAGNOSTIC_DEST directory
A. In the /diag/rdbms/<dbname>/<instance>/alert directory
Where is the shared SQL area stored in an Oracle database? A. In the library cache in a shared pool B. In the database buffer cache C. In the dictionary cache D. In the parse cache
A. In the library cache in a shared pool
Where are data blocks changed in an Oracle database? A. In the memory B. On disk C. In the redo log buffer D. In the server parameter file
A. In the memory
Which information is included in the output of the utlu121s.sql post-upgrade status script? A. Information about the current version of database components B. The before and after upgrade size of the SYSTEM tablespace C. Newly created default accounts that should be locked D. A list of patches recommended for the new version
A. Information about the current version of database components
What does the V$PARAMETER view show in an Oracle database? A. Information about the parameters and their current values in the database B. Information as read from the spfile C. Information about the control files D. Information about the data files
A. Information about the parameters and their current values in the database
Which of the following pieces of information is not available in the control file? A. Instance name B. Database name C. Tablespace names D. Log sequence number
A. Instance name
What can be queried from a database while it is in the NOMOUNT state? A. Instance parameters B. Control file records C. Checkpoint information D. Incarnation information
A. Instance parameters
Which area is a member of the System Global Area (SGA)? A. Java pool B. Log writer C. Database writer D. Process monitor
A. Java Pool
To place the database into ARCHIVELOG mode, in which state must you start the database? A. MOUNT B. NOMOUNT C. OPEN D. SHUTDOWN E. Any of the above
A. MOUNT
A user creates a role granting select and update access to table1, and assigns the role to a coworker. The next day the user revokes select access from the role. Which actions will the coworker still be able to perform on table1? A. Modify B. Display C. Add D. Delete
A. Modify
What is the relationship between operating-system blocks, database blocks, and extents? A. Multiple operating-system blocks make up database blocks, and contiguous chunks of database blocks make up extents B. Multiple extents make up operating-system blocks, and contiguous chunks of extents make up database blocks C. Multiple database blocks make up operating-system blocks, and contiguous chunks of operating-system blocks make up extents D. Multiple extents make up database blocks, and contiguous chunks of database blocks make up operating-system blocks
A. Multiple operating-system blocks make up database blocks, and contiguous chunks of database blocks make up extents
By default, how much space can any account use for a new table? A. None B. Up to the current free space in the tablespace C. Unlimited space, including autoextends D. Up to the default quota established at tablespace creation time
A. None
How many tablespaces can a segment belong to in an Oracle database? A. One B. Two C. Multiple D. None
A. One
In Real Application Clusters, how many databases can one instance be associated with? A. One B. Two C. Multiple D. None
A. One
Which of the following best describes a RAC configuration? A. One database, multiple instances B. One instance, multiple databases C. Multiple databases plugged in from multiple servers D. Multiple databases, multiple instances
A. One database, multiple instances
After an administrator granted some privileges to a user, all database users automatically had those same privileges. Which user was assigned the privileges? A. PUBLIC B. SCOTT C. SYSTEM D. DBSNMP
A. PUBLIC
What is the primary responsibility of the System Monitor (SMON) process in an Oracle database? A. Performing instance recovery using the online redo log files B. Writing the redo log buffer information to the online redo log files C. Executing user queries D. Performing session cleanup
A. Performing instance recovery using the online redo log files
An administrator wants to limit CPU time for accounts. Which object should the administrator alter? A. Profile B. Role C. Tablespace D. Schema
A. Profile
Which privilege is required to add a foreign key constraint to a table owned by another user? A. REFERENCES B. UPDATE C. ALTER D. INSERT
A. REFERENCES
Which component is configured at database startup and cannot be dynamically managed? A. Redo log buffer B. Streams pool C. Java pool D. Shared pool E. None of the above
A. Redo log buffer
Which feature allows a failed transaction to be suspended until the problem that caused the failure is resolved? A. Resumable space allocation B. Segment shrinking C. Instance recovery D. Oracle managed files
A. Resumable space allocation
A company plans to use Data Guard SQL Apply to migrate a database to Oracle 12c. Which type of upgrade does this tool perform? A. Rolling B. Export/import C. Transportable tablespace D. Direct
A. Rolling
Using EM Database Control, you've identified the following SQL statement as the source of a lot of disk I/O: SELECT NAME, LOCATION, CREDIT_LIMIT FROM CUSTOMERS What should you do first to try to improve performance? A. Run the SQL Tuning Advisor. B. Run the SQL Access Advisor. C. Check the EM Database Control main screen for alerts. D. Click the Alert Log Content link in the EM Database Control main screen.
A. Run the SQL Tuning Advisor.
A user needs to view the table privileges of other users. Which role should be granted to the user? A. SELECT_CATALOG_ROLE B. CONNECT C. EXECUTE_CATALOG_ROLE D. RESOURCE
A. SELECT_CATALOG_ROLE
What does the instance of an Oracle database consist of? A. SGA and all the Oracle background processes B. Control files, data files, and redo logs C. Database blocks and extents D. Segments and tablespaces
A. SGA and all the Oracle background processes
Which of the following shutdown statements does not perform a clean shutdown? A. SHUTDOWN ABORT B. SHUTDOWN TRANSACTIONAL C. SHUTDOWN D. SHUTDOWN IMMEDIATE E. All of these are considered clean shutdowns.
A. SHUTDOWN ABORT
Which tool is started by the system and automatically improves the performance of queries? A. SQL Tuning Advisor B. SQL*Loader C. SQL Access Advisor D. SQL*Plus
A. SQL Tuning Advisor
What allows users to capture data from non-Oracle sources into an Oracle database? A. SQL*Loader B. Recovery Manager C. Transportable Tablespaces D. Import Transformation
A. SQL*Loader
Which of the following startup modes does not attempt to open the database? A. STARTUP NOMOUNT B. STARTUP MOUNT C. STARTUP OPEN D. STARTUP RESTRICT
A. STARTUP NOMOUNT
How is undo tablespace storage allocated? A. Segments are assigned dynamically each time a change is made. B. Segments are static and are configured by the database administrator. C. Each transaction specifies how many segments are required for the transaction. D. Each transaction uses segments that are released from prior transactions.
A. Segments are assigned dynamically each time a change is made.
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? A. Stuart loses his SELECT privilege on sh.products. B. Stuart retains his SELECT privilege on sh.products. C. Stuart loses his SELECT privilege if Ian was dropped with the CASCADE REVOKE option. D. Stuart retains his SELECT privilege if Ian was dropped with the NOCASCADE REVOKE option.
A. Stuart loses his SELECT privilege on sh.products.
What is the requirement for using host naming in Oracle? A. TCP/IP B. Database name C. Service name
A. TCP/IP
What happens during an incremental database checkpoint? A. The control file is updated with the checkpoint position, but data files are not updated B. Data file headers are updated with checkpoint information, but the control file is not updated C. Both the control file and data file headers are updated with checkpoint information D. Neither the control file nor data file headers are updated with checkpoint information
A. The control file is updated with the checkpoint position, but data files are not updated
An administrator runs the following SQL statements: CREATE USER HR_User IDENTIFIED BY 'Password' DEFAULT TABLESPACE DATA; GRANT CREATE SESSION, CREATE TABLE to HR_User; HR_User creates a new table in their own schema. What happens when HR_User inserts a row into the new table? A. The insert fails with an error for missing privileges on the DATA tablespace. B. The insert succeeds and its segment is added in the DATA tablespace. C. The insert fails with an error for missing privileges on the TEMPORARY tablespace. D. The insert succeeds and its segment is added to the SYSTEM tablespace.
A. The insert fails with an error for missing privileges on the DATA tablespace.
Select the statement that is not true regarding media failure. A media failure occurs when: A. The network card on the server fails. B. The DBA accidentally deletes one of the data files for the SYSTEM tablespace. C. There is a head crash on all physical drives in the RAID controller box. D. A corrupted track on a CD containing a read-only tablespace causes a query to fail.
A. The network card on the server fails.
Which statement regarding Unified Auditing is true regarding a database after upgrade? A. The predefined Unified Auditing policies are disabled by default after a manual upgrade. B. Pre-12c audit configuration is disabled by default after the upgrade, and audit records are no longer written to the AUD$ table. C. One of the predefined Unified Auditing policies is enabled by default after a DBUA upgrade. D. Traditional pre-12c audit and 12c Unified Auditing policies cannot co-exist in the same database.
A. The predefined Unified Auditing policies are disabled by default after a manual upgrade.
Which parameter determines how long information should be kept before it is overwritten in an undo tablespace? A. UNDO_RETENTION B. DBA_FLASHBACK_RETENTION_TARGET C. SORT_AREA_RETAINED_SIZE D. UNDO_MANAGEMENT
A. UNDO_RETENTION
Which privilege must be granted to allow a user to modify existing rows in a table? A. UPDATE B. ALTER C. DEBUG D. INSERT
A. UPDATE
Which dictionary table stores the credentials for a password-authenticated account? A. USER$ B. UET$ C. FET$ D. AUD$
A. USER$
Which information from a remote database is included in the configuration of a database link? A. User name B. Data files C. Tables D. Views
A. User name
Which action updates a database from an earlier version to a newer version while the database remains online? A. Using Oracle Golden Gate B. Using Oracle Universal Installer C. Performing a manual upgrade D. Performing a parallel upgrade
A. Using Oracle Golden Gate
Which view displays database parameters and values modified using the SCOPE=SPFILE clause? A. V$SPPARAMETER B. V$NLS_PARAMETER C. V$HS_PARAMETER D. V$PARAMETER
A. V$SPPARAMETER
When does a DBA run an Automatic Workload Repository (AWR) report to find the root cause of an issue? A. When a job is running slow in the database B. When the database fails to recover C. When the database fails to start up D. When a space issue occurs in the database
A. When a job is running slow in the database
When are data file headers updated with checkpoint information by the DBWn process? A. When dirty buffers are written to the files B. During instance recovery C. During a log switch D. When the PMON process is terminated
A. When dirty buffers are written to the files
What is the primary responsibility of the Log Writer (LGWR) process in an Oracle database? A. Writing the redo log buffer information to the online redo log files B. Executing user queries C. Coalescing contiguous free space in tablespaces D. Performing session cleanup
A. Writing the redo log buffer information to the online redo log files
Can the database name be changed after the control file is re-created with a new name? A. Yes B. No
A. Yes
Which interactive tool presents a view of an alert log? A. adrci B. imp C. lsnrctl D. tkprof
A. adrci
Which of the following SQL statements limits attempts to guess passwords by locking an account after three failed logon attempts? A. alter profile default limit failed_login_attempts 3; B. alter system set max_logon_failures = 3 scope=both; C. alter user set failed_login_attempts = 3; D. alter system set failed_login_attempts = 3 scope=both;
A. alter profile default limit failed_login_attempts 3;
Which of the following statements give user desmond the ability to alter table gl.accounts? A. grant alter on gl.accounts to desmond; B. grant alter to desmond on gl.accounts; C. grant alter table to desmond; D. allow desmond to alter table gl.accounts;
A. grant alter on gl.accounts to desmond;
If neither spfileOCA12C.ora nor spfile.ora files are found when starting the instance, which file does Oracle look for? A. initOCA12C.ora B. init.ora C. None of the above D. Both A and B
A. initOCA12C.ora
Which file is used to configure a nondefault listener in Oracle? A. listener.ora B. sqlnet.ora C. tnsnames.ora D. lsnrctl.ora
A. listener.ora
Which of the following files must be present on the Oracle server to start a nondefault Oracle listener? A. listener.ora B. lsnrctl.ora C. sqlnet.ora D. tnsnames.ora
A. listener.ora 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.
A database link named wgu2021 has been created to link to a remote object in the test database. The object is named employee and is owned by Scott. Which reference resolves to the remote object? A. scott.employee@wgu2021 B. scott.employee C. employee D. scott.employee@test
A. scott.employee@wgu2021
What should an administrator use to identify tables that are fragmented? A. Segment Advisor B. Automatic Database Diagnostic Monitor C. DBMS_SPACE D. DBMS_REPAIR
A. segment advisor
If the spfileOCA12C.ora file is not found when starting the instance, which file does Oracle look for next? A. spfile.ora B. initOCA12C.ora C. init.ora D. None of the above
A. spfile.ora
When starting the instance, what is the first file Oracle looks for? A. spfileOCA12C.ora B. spfile.ora C. initOCA12C.ora D. init.ora
A. spfileOCA12C.ora
A database administrator needs to recover unused space from a tablespace while preventing data loss. What should the administrator use? A. The SHRINK option B. The DROP command C. The FLASHBACK command D. The OFFLINE clause
A. the SHRINK option
Which statements regarding Database Resident Connection Pooling (DRCP) are true? (Choose two.) 1. When a DRCP pooled server connection is made by an application, it is equivalent to a dedicated server connection. 2. When a database has DRCP enabled, all connections default to a pooled connection unless DEDICATED server is explicitly specified in the connect string. 3. When an application using the DRCP connection disconnects, the server connection (process) is handed off to the broker. 4. When using the Oracle Net Easy Connect method, it is not possible to utilize DRCP. A) 1, 2 B) 1, 3 C) 2, 3
B) 1, 3
Which tasks are accomplished by the SMON process? (Choose all that apply.) 1. Performs recovery at instance startup 2. Performs cleanup after a user session is terminated 3. Starts any server process that stopped running 4. Coalesces contiguous free space in dictionary-managed tablespaces A) 1, 2 B) 1, 4 C) 2, 3 D) 2, 4
B) 1, 4
Which versions of Oracle Database can be directly upgraded to Oracle Database 12c? A) 10.2.0.3, 11.1.0.6, and 11.2.0.1 B) 10.2.0.5, 11.1.0.7, 11.2.0.2, and higher C) 10.1.0.5, 11.1.0.6, and 11.2.0.3 D) 10.2.0.4, 11.1.0.7, and 11.2.0.4
B) 10.2.0.5, 11.1.0.7, 11.2.0.2, and higher
In Oracle Database 12c, which versions can be directly upgraded? A) All versions B) 10.2.0.5, 11.1.0.7, and 11.2.0.x C) 10.2.0.5 only D) 11.1.0.7 and 11.2.0.x only
B) 10.2.0.5, 11.1.0.7, and 11.2.0.x
What is the lowest version you can downgrade to after upgrading an 11g R1 or 10g R2 database? A) Any version of Oracle 11g B) 11.1.0.7 C) The version from which you upgraded D) 10.2.0.5
B) 11.1.0.7
For which database versions is the Full Transportable Export/Import option available? A) 11.2.0.2 and higher B) 11.2.0.3 and higher C) 11.1.0.7 and higher D) 10.2.0.5 and higher
B) 11.2.0.3 and higher
What does ROWID include? 1. Segment ID 2. Data object number 3. Relative file number 4. Block number in the file 5. Row number A) 1, 2, 3, 4 B) 2, 3, 4, 5 C) 1, 2, 4, 5 D) 1, 3, 4, 5
B) 2, 3, 4, 5
Which parameter specifies which of the Server Manageability Packs is active in the Enterprise Edition of Oracle Database 12c? A) STATISTICS_LEVEL B) CONTROL_MANAGEMENT_PACK_ACCESS C) MEMORY_TARGET D) SGA_TARGET
B) CONTROL_MANAGEMENT_PACK_ACCESS
In which database view can you find the rationale for each ADDM recommendation? A) DBA_ADVISOR_FINDINGS B) DBA_ADVISOR_RATIONALE C) DBA_ADVISOR_OBJECTS D) DBA_ADVISOR_RECOMMENDATIONS
B) DBA_ADVISOR_RATIONALE
What happens when all members of a redo log-file group are lost except for one? A) Database operation is affected, and data loss occurs. B) Database operation is not affected, and no data loss occurs. C) Instance failure occurs. D) An ARCn process is spawned.
B) Database operation is not affected, and no data loss occurs.
What is the main advantage of the Data Pump Export/Import method for upgrading to Oracle Database 12c? A) Defragments tables B) Eliminates fragmentation of space in the new database C) Directly upgrades the database D) Transports the entire database
B) Eliminates fragmentation of space in the new database
Which of the following is true when upgrading a small Oracle database? A) Performing a direct upgrade is always the best option B) Exporting and importing to an existing or newly created Oracle Database 12c database is faster and cleaner C) Using Transportable Tablespaces is the most efficient method D) RMAN duplication is the recommended approach
B) Exporting and importing to an existing or newly created Oracle Database 12c database is faster and cleaner
What is the purpose of the MMON process in Oracle Database? A) Manage database memory B) Gather statistics from the SGA and store them in the AWR C) Perform database backups D) Handle user connections
B) Gather statistics from the SGA and store them in the AWR
Where is the trace backup created? A) In the $DIAGNOSTIC_DEST/trace directory. B) In the $DIAGNOSTIC_DEST/diag/<rdbms>/<database>/<instance>/trace directory. C) In the $DIAGNOSTIC_DEST/backup directory. D) In the $DIAGNOSTIC_DEST/data directory.
B) In the $DIAGNOSTIC_DEST/diag/<rdbms>/<database>/<instance>/trace directory.
Why is having a relevant baseline for each type of usage pattern in a transactional system important? A) It improves the accuracy of memory allocation B) It yields better results in terms of alerts and ADDM recommendations C) It helps to balance the workload between multiple instances D) It speeds up query execution
B) It yields better results in terms of alerts and ADDM recommendations
Which process is responsible for taking AWR snapshots and running ADDM? A) MMNL B) MMON C) AutoTask D) ASH
B) MMON
In which state must the database be to change it to ARCHIVELOG mode? A) OPEN B) MOUNT C) NOMOUNT D) CLOSE
B) MOUNT
What type of failure is it when a network card fails? A) Media failure B) Network failure C) Physical corruption D) Logical corruption
B) Network failure
Does the Data Recovery Advisor in Oracle Database 12c Release 1 support RAC databases? A) Yes B) No
B) No
Is the instance name stored in the control file? A) Yes, it is stored in the control file. B) No, it is not stored in the control file. C) It is stored in the control file only for certain configurations. D) It depends on the database version.
B) No, it is not stored in the control file.
During an online backup, are all data files in sync with the control file? A) Yes, they are always in sync. B) No, they are rarely, if ever, in sync. C) It depends on the backup method used. D) Only if the backup is taken at the same time.
B) No, they are rarely, if ever, in sync.
Which of the following is not required for an instance to enter the NOMOUNT state? A) All copies of the control files B) One of the redo log-file groups C) An spfile D) An init.ora file
B) One of the redo log-file groups
When is the substitution variable %d required in the archive log destination? A) Always B) Only if multiple databases share the same archive log destination. C) Only if the database has multiple instances. D) Never
B) Only if multiple databases share the same archive log destination.
Which tab in the EM Database Control provides a quick overview of how the host system, user sessions, and throughput are impacted by the system slowdown? A) Overview B) Performance C) Diagnostics D) Sessions
B) Performance
Which tool provides the most information about how the performance of a SQL statement might be improved? A) SQL Access Advisor B) SQL Tuning Advisor C) EM Database Control alerts D) Alert log
B) SQL Tuning Advisor
What does Oracle recommend for performance reasons after upgrading to Oracle Database 12c? A) Keeping pre-12c audit configuration B) Switching to Unified Auditing Only mode C) Running full database statistics D) Purging the RECYCLE_BIN
B) Switching to Unified Auditing Only mode
What is the default setting for the STATISTICS_LEVEL parameter? A) BASIC B) TYPICAL C) ALL D) OFF
B) TYPICAL
What happens to the tables when a space quota is exceeded or a quota is removed from a user on a tablespace? A) The tables are deleted B) The tables remain in the tablespace, but no new extents can be allocated C) The tables are locked D) The tables are moved to another tablespace
B) The tables remain in the tablespace, but no new extents can be allocated
Which of the following is true about specifying warning and critical thresholds for monitoring the available free space in a tablespace? A) The warning threshold is generally a higher number than the critical threshold B) The warning threshold is generally a lower number than the critical threshold C) The warning and critical thresholds must be the same D) Only one threshold can be specified at a time
B) The warning threshold is generally a lower number than the critical threshold
Which performance metric is an overall measure of performance that can be compared against similar measures taken before and after tuning changes are implemented? A) Latency B) Throughput C) Response time D) CPU usage
B) Throughput
What is the correct name of the PostUpgrade Status tool in Oracle Database 12c? A) utlu121i.sql B) utlu121s.sql C) preupgrd.sql D) utluppkg.sql
B) utlu121s.sql
Where is the listener.ora file located by default? A. $ORACLE_HOME/RDBMS B. $ORACLE_HOME/NETWORK/ADMIN C. $ORACLE_HOME/DATABASE/ADMIN D. $ORACLE_HOME/DBS/ADMIN
B. $ORACLE_HOME/NETWORK/ADMIN
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? A. /u01/app/oracle/product/12.1.0/log/rdbms/xyz/xyz/trace B. /u01/app/oracle/diag/rdbms/xyz/xyz/trace C. /u01/app/oracle/diag/rdbms/xyz/xyz/alert D. /u01/app/oracle/product/12.1.0/diag/rdbms/xyz/xyz/trace E. /u01/app/oracle/log/rdbms/xyz/xyz/trace
B. /u01/app/oracle/diag/rdbms/xyz/xyz/trace
How many control files should a database have at minimum to start the database? A. 0 B. 1 C. 2 D. 3
B. 1
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 Two) 1. The listener is not running on the Oracle server. 2. The user entered an invalid net service name. 3. The user supplied the correct net service name, but the net service name is misspelled in the tnsnames.ora file on the client file. 4. The listener is not configured to listen for this service. A) 1, 2 B) 2, 3 C) 3, 4
B. 2, 3
When you're performing a manual upgrade to Oracle Database 12c, in what order are the following steps performed? 1. Run catctl.pl. 2. Run preupgrd.sql. 3. Run utlu121s.sql. 4. Start the database using the STARTUP UPGRADE option. 5. Start the database using the STARTUP NORMAL option. A. 2, 5, 1, 4, 2 B. 2, 4, 1, 5, 3 C. 4, 2, 1, 5, 3 D. 5, 2, 4, 1, 3
B. 2, 4, 1, 5, 3
Place the following events or actions leading up to and during instance recovery in the correct order. 1. The database is opened and available. 2. Oracle uses undo segments in the undo tablespace to roll back uncommitted transactions. 3. The DBA issues the STARTUP command at the SQL*Plus prompt. 4. Oracle applies the information in the online redo log files to the data files. A. 4, 3, 2, 1 B. 3, 4, 1, 2 C. 2, 1, 3, 4 D. 2, 1, 4, 3 E. 3, 2, 4, 1 F. 3, 4, 2, 1
B. 3, 4, 1, 2
Choose the best statement from the options related to segments. A. A contiguous set of blocks constitutes a segment. B. A nonpartitioned table can have only one segment. C. A segment can belong to more than one tablespace. D. All of the above are true.
B. A nonpartitioned table can have only one segment.
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. A. Use of 'ocasvr:1522/ocadb' is invalid in database link definition. B. A private database link is created under user MIKE. C. A private database link is created under user SCOTT. D. If the SALES_INT user does not exist or its password is not SALESPWD1, the database link will not be created.
B. A private database link is created under user MIKE.
Which statement adds a member /logs/redo22.log to redo log-file group 2? A. ALTER DATABASE ADD LOGFILE '/logs/redo22.log' TO GROUP 2; B. ALTER DATABASE ADD LOGFILE MEMBER '/logs/redo22.log' TO GROUP 2; C. ALTER DATABASE ADD MEMBER '/logs/redo22.log' TO GROUP 2; D. ALTER DATABASE ADD LOGFILE '/logs/redo22.log';
B. ALTER DATABASE ADD LOGFILE MEMBER '/logs/redo22.log' TO GROUP 2;
Which command should be used to enable autoextension for a data file in a tablespace? A. ALTER TABLESPACE B. ALTER DATABASE DATAFILE C. ALTER DATABASE TABLESPACE D. ALTER TABLE
B. ALTER DATABASE DATAFILE
Which of the following statements is NOT resumable? A. ALTER TABLE ... SPLIT PARTITION B. ALTER TABLE ... SHRINK SPACE C. SELECT D. INSERT INTO ... SELECT
B. ALTER TABLE ... SHRINK SPACE
Which of the following components of the Oracle architecture stores the statistics gathered by the MMON process? A. ADDM B. AWR C. ASMM D. ADR
B. AWR
When can the CONTROL_FILES parameter be changed? A. After the database is restarted B. After the control files are copied to a new location C. After the data dictionary views are created D. After the instance is shut down
B. After the control files are copied to a new location
Which of the following options prevents a user from reusing a password when they change their password? A. Setting the initialization parameter NO_PASSWORD_REUSE to TRUE B. Altering that user's profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX C. Altering that user's profile to UNLIMITED for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX D. Using a password verify function to record the new password and comparing the new passwords to those recorded previously
B. Altering that user's profile to UNLIMITED for PASSWORD_REUSE_TIME and 1 for PASSWORD_REUSE_MAX
Which statement about extents is true? A. An extent consists of one or more segments. B. An extent consists of one or more contiguous Oracle blocks. C. An extent is always allocated when a table is created. D. An extent consists of one or more data files.
B. An extent consists of one or more contiguous Oracle blocks.
Which tool identifies potential performance bottlenecks? A. Cluster Verification Utility B. Automatic Database Diagnostic Monitor C. Oracle Data Guard Broker D. Privilege Analysis
B. Automatic Database Diagnostic Monitor
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? A. ALERT_LOG_DEST B. BACKGROUND_DUMP_DEST C. DIAGNOSTIC_DEST D. INIT_LOG_DUMP_DEST
B. BACKGROUND_DUMP_DEST
What does the COMPRESS option enable in Oracle? A. Advanced compression B. Basic compression C. Compression for online DML D. No compression
B. Basic compression
In a Shared Server configuration, how can a user request a dedicated connection? A. By specifying (SERVER=SHARED) in the connection string B. By specifying (SERVER=DEDICATED) in the connection string C. By connecting as SYSDBA D. No option is required; the user will always get a dedicated connection
B. By specifying (SERVER=DEDICATED) in the connection string
What is the purpose of the database buffer cache in an Oracle database? A. Storing parsed code and execution plans B. Caching data blocks C. Caching data dictionary information D. Performing instance recovery
B. Caching data blocks
What is the primary responsibility of the MMON (Manageability Monitor) process in an Oracle database? A. Resizing memory components B. Capturing the AWR database and performing ADDM analysis C. Writing redo log copies to the archive log location D. Performing session cleanup
B. Capturing the AWR database and performing ADDM analysis
A database will be migrated to a platform that has a different endianness. Which action must be performed on the data files before the migration? A. Compress by using a compression tool B. Convert by using Recovery Manager C. Include in a transportable tablespace D. Verify free space allocation of 20%
B. Convert by using Recovery Manager
Due to its importance, what is the recommended practice for control files in an Oracle database? A. Create only one control file for simplicity. B. Create at least two more copies of the control file. C. Store the control file in the same location as the data files. D. Remove all control files after the database has started.
B. Create at least two more copies of the control file.
Which procedure is used to tell Oracle that the statistics gathered should not be published? A. DBMS_STATS.PUBLISH_STATS B. DBMS_STATS.SET_TABLE_PREFS C. DBMS_STATS.PENDING_STATS D. DBMS_STATS.GATHER_TABLE_STATS
B. DBMS_STATS.SET_TABLE_PREFS
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? A. DB_8K_CACHE_SIZE B. DB_16K_CACHE_SIZE C. DB_CACHE_SIZE D. None of the above
B. DB_16K_CACHE_SIZE
What is the impact of setting the value of the undo retention initialization parameter to 900 in an undo tablespace that uses a fixed size? A. Data for committed transactions will be kept for the specified period of time. B. Data for committed transactions will be overwritten if an active transaction needs the space. C. Up to 900 MB of data will be protected from being overwritten. D. Up to 900 transactions will be protected from being overwritten.
B. Data for committed transactions will be overwritten if an active transaction needs the space.
Communication between two Oracle databases is configured using: A. Database Resident Connection Pooling B. Database link C. Connection Manager D. Oracle Net Manager
B. Database link
Which process is NOT involved in a commit event in an Oracle database? A. Log Writer (LGWR) B. Database writer C. System Monitor (SMON) D. Process Monitor (PMON)
B. Database writer
In a Shared Server configuration, what kind of connections can users still request? A. Shared connections only B. Dedicated connections C. SYSDBA connections D. None of the above
B. Dedicated connections
How is authentication by the operating system referred to in Oracle? A. Internal authentication B. External authentication C. OS-based authentication D. System authentication
B. External authentication
Which type of authentication requires the Oracle account name to match the operating-system account name prefixed with the OS_AUTHENT_PREFIX string? A. Internal authentication B. External authentication C. OS-based authentication D. System authentication
B. External authentication
Complete the sentence. To run the Pre-Upgrade Information tool, the database must be started _____. A. From Oracle Database 12c home B. From the original database home C. With the STARTUP UPGRADE option D. With the STARTUP PREUPGRADE option
B. From the original database home
Which option is not available for upgrading a database from 11.2.0.2 to 12.1.0? A. Full Database Export/Import using Data Pump. B. Full Transportable Export/Import. C. Manual upgrade using catctl.pl. D. DBUA upgrade. E. All of the above are available options.
B. Full Transportable Export/Import.
Examine the code, and choose the option that best describes the reason for error. CREATE USER JOHN IDENTIFIED BY JOHN1; CREATE ROLE HR_QUERY; GRANT CONNECT, OEQUERY, SELECT ANY TABLE TO HR_QUERY; ALTER USER JOHN DEFAULT ROLE ALL EXCEPT HR_QUERY; GRANT HR_QUERY TO JOHN; CONNECT JOHN/JOHN1 SELECT COUNT(*) FROM HR.EMPLOYEES; Error: ORA-01031: insufficient privileges A. John needs the SELECT_CATALOG_ROLE privilege. B. HR_QUERY is not a default role for John. C. The SELECT privilege on the HR.EMPLOYEES table is not granted to JOHN or HR_QUERY. D. John should enable the role using the SET ROLE statement and a password.
B. HR_QUERY is not a default role for John. Because HR_QUERY has the SELECT ANY TABLE privilege, no other privilege is required to query user tables in the database. To avoid the error, HR_QUERY must be defined as a default role for John, or John should use the SET ROLE statement. A password is not needed for SET ROLE because the role is not password protected.
Where is redo information stored in an Oracle database? A. In a segment B. In redo logs C. In the undo tablespace D. In the temporary tablespace
B. In redo logs
In Oracle Database 12c, where is the XML version of the alert log file located? A. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/trace directory B. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/alert directory C. In the BACKGROUND_DUMP_DEST directory D. In the DIAGNOSTIC_DEST directory
B. In the $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/alert directory
Where can you find the text version of the alert log in an Oracle database? A. In the /diag/rdbms/<dbname>/<instance>/alert directory B. In the /diag/rdbms/<dbname>/<instance>/trace directory C. In the BACKGROUND_DUMP_DEST directory D. In the DIAGNOSTIC_DEST directory
B. In the /diag/rdbms/<dbname>/<instance>/trace directory
What does the V$SPPARAMETER view show in an Oracle database? A. Information about the parameters and their current values in the database B. Information as read from the spfile C. Information about the control files D. Information about the data files
B. Information as read from the spfile
What happens when services are dynamically registered with the listener? A. Information is present in the listener.ora file B. Information is not present in the listener.ora file
B. Information is not present in the listener.ora file
Which statement about the MEMORY_TARGET parameter is not true? A. It is a dynamic initialization parameter. B. It represents the total maximum memory that can be allocated to the instance memory (PGA and SGA combined). C. Its default value is zero. D. You will not get an error when SGA_TARGET and PGA_AGGREGATE_TARGET parameters are set to nonzero values.
B. It represents the total maximum memory that can be allocated to the instance memory (PGA and SGA combined).
Examine the CREATE USER statement and choose which of the following options best applies. CREATE USER JOHN IDENTIFIED BY JOHNNY DEFAULT TABLESPACE INDEX01 PASSWORD EXPIRE QUOTA UNLIMITED ON DATA01 QUOTA UNLIMITED ON INDEX01; GRANT CONNECT TO JOHN; A. JOHN will not be able to log in to the database using SQL*Plus until the DBA changes his password. B. JOHN is authenticated by the database. C. When tables are being created, if JOHN did not specify the TABLESPACE clause, the table will be created on the DATA01 tablespace. D. Specifying unlimited space quota on INDEX01 is a redundant step because INDEX01 is JOHN's default tablespace.
B. JOHN is authenticated by the database.
Which parameter affects the mean time to recovery target for a database instance? A. LOG_CHECKPOINTS_TO_ALERT B. LOG_CHECKPOINT_INTERVAL C. LOG_ARCHIVE_TRACE D. LOG_FILE_NAME_CONVERT
B. LOG_CHECKPOINT_INTERVAL
What does segment space management refer to in Oracle? A. Management of disk space for data files B. Management of free space within tablespaces C. Management of memory allocation for database processes D. Management of object storage within tablespaces
B. Management of free space within tablespaces
What constitutes an Oracle instance? A. Control file, data file, and redo log files B. Memory structures and background processes C. SGA structures only D. Automatic Memory Management and Automatic Shared Memory Management
B. Memory structures and background processes
Which of the following is not a step in configuring your database to archive redo log files? A. Place the database in ARCHIVELOG mode. B. Multiplex the online redo log files. C. Specify a destination for archived redo log files. D. Specify a naming convention for your archived redo log files.
B. Multiplex the online redo log files.
Can you start or stop a database using OEM Database Express? A. Yes B. No
B. No
Does the CREATE TABLESPACE statement create any extents when a tablespace is created? A. Yes B. No
B. No
In a Shared Server configuration, can the database be stopped or started by the DBA when connected over a Shared Server connection? A. Yes B. No
B. No
What does a segment in an Oracle database consist of? A. One or more tables B. One or more extents C. One or more tablespaces D. One or more data files
B. One or more extents
When you are upgrading a database to Oracle Database 12c, which of the following options are true? A. Any version of an Oracle 10g or Oracle 11g database can be upgraded to Oracle Database 12c using DBUA. B. Only the versions 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3, and 11.2.0.4 can be upgraded to 12.1.0. C. Once upgraded to Oracle Database 12c, the upgraded database can only be downgraded to Oracle 11g. D. When a 10.2.0.5 database is upgraded to Oracle Database 12c, it cannot be downgraded.
B. Only the versions 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3, and 11.2.0.4 can be upgraded to 12.1.0.
What does the STARTUP RESTRICT command do in an Oracle database? A. Opens the database and allows all users to connect B. Opens the database and allows only users with RESTRICTED database access to connect C. Closes the database and allows only users with RESTRICTED database access to connect D. Closes the database and allows all users to connect
B. Opens the database and allows only users with RESTRICTED database access to connect
Which of the following is the proper order of Oracle's storage hierarchy, from smallest to largest? A. Operating-system block, database block, segment, extent B. Operating-system block, database block, extent, segment C. Segment, extent, database block, operating-system block D. Segment, database block, extent, operating-system block
B. Operating-system block, database block, extent, segment
Which process is responsible for cleaning up failed user processes in an Oracle database? A. MMON (Manageability Monitor) B. PMON (Process Monitor) C. ARCn (Archiver) D. MMAN (Memory Manager)
B. PMON (Process Monitor)
Which is the best option for upgrading an Oracle 10g R2 database that is in its terminal release to Oracle Database 12c? A. Use Data Pump utilities to export and import. B. Perform a direct upgrade using DBUA. C. Upgrade to 11g 11.1.0.7 or 11.2.0.2 using DBUA, and then upgrade the database to 12.1.0 using Oracle Database 12c DBUA. D. Run catctl.pl script on the Oracle 10g instance, and then start the instance in Oracle Database 12c.
B. Perform a direct upgrade using DBUA.
What does the runInstaller executable do before starting the Oracle Universal Installer (OUI) graphical tool? A. Installs Oracle binaries B. Performs a preinstall check of the operating system and hardware resources C. Creates volume groups and mount points D. Configures the database
B. Performs a preinstall check of the operating system and hardware resources
In a Shared Server configuration, where is the request and response information kept? A. PGA B. SGA C. UGA D. None of the above
B. SGA
Which statement regarding SGA_MAX_SIZE is true? A. SGA_MAX_SIZE is modifiable after an instance is started, only when Automatic Memory Management is used. B. SGA_MAX_SIZE is not dynamically modifiable. C. SGA_MAX_SIZE is ignored when MEMORY_TARGET > 0. D. SGA_MAX_SIZE must be specified when SGA_TARGET > 0.
B. SGA_MAX_SIZE is not dynamically modifiable.
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? A. STARTUP NOMOUNT RESTRICT B. STARTUP RESTRICT C. STARTUP MOUNT RESTRICT D. STARTUP MOUNT FORCE RESTRICT
B. STARTUP RESTRICT
Which initialization parameter can disable memory advisors and Automatic Shared Memory Management? (Choose the best answer.) A. CONTROL_MANAGEMENT_PACK_ACCESS B. STATISTICS_LEVEL C. MEMORY_TARGET D. Memory advisors cannot be disabled.
B. STATISTICS_LEVEL
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? A. Stuart loses his privileges. B. Stuart retains his privileges. C. Stuart loses his privileges if Ian was dropped with the CASCADE REVOKE option. D. Stuart retains his privileges if Ian was dropped with the NOCASCADE REVOKE option.
B. Stuart retains his privileges.
Given the following SQL statement: DROP USER User1 CASCADE; What happens to tables that User1 owns when an administrator runs the SQL statement? A. Tables are moved to a default account. B. Tables and the data are deleted from the database. C. Tables are archived by the system. D. Tables and data are moved to the undo tablespace.
B. Tables and the data are deleted from the database.
You issue the statement CREATE TABLESPACE X; . Which of the following is the best option? A. The statement fails because mandatory properties are not defined. B. The 100MB tablespace is created. C. The mandatory DATAFILE clause is missing. D. The tablespace name should be at least three characters long.
B. The 100MB tablespace is created. 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.
Choose the correct statement about the Data Recovery Advisor. A. The Data Recovery Advisor is a standalone tool. B. The Data Recovery Advisor does not support RAC databases. C. The CHANGE FAILURE command can be used in a SQL*Plus session. D. The REPAIR FAILURE command works only after LIST FAILURE.
B. The Data Recovery Advisor does not support RAC databases.
When you click the Restore Database button on the Upgrade Results page, which options must be true to perform a complete restore? A. The database is upgraded from 11.2 release to Oracle Database 12c. B. The database must be backed up using DBUA. C. The COMPATIBLE parameter value must be 11.2.0. D. The database must be backed up prior to the upgrade.
B. The database must be backed up using DBUA.
In ARCHIVELOG mode, the loss of a data file for any tablespace other than the SYSTEM or UNDO tablespace affects which objects in the database? A. The loss affects only objects whose extents reside in the lost data file. B. The loss affects only the objects in the affected tablespace, and work can continue in other tablespaces. C. The loss will not abort the instance but will prevent other transactions in any tablespace other than SYSTEM or UNDO until the affected tablespace is recovered. D. The loss affects only those users whose default tablespace contains the lost or damaged data file
B. The loss affects only the objects in the affected tablespace, and work can continue in other tablespaces.
Identify the statement that is not true regarding the loss of a control file. A. A damaged control file can be repaired by using one of the remaining undamaged control files, assuming there are at least two copies of the control file. B. The missing or damaged control file can be replaced while the instance is still active. C. You can temporarily run the instance with one fewer control file, as long as you remove one of the references to the missing control file in the spfile or init.ora file. D. An instance typically fails when one of the multiplexed control files is lost or damaged
B. The missing or damaged control file can be replaced while the instance is still active.
Why is the large pool configured in an Oracle database? A. To enable the ARCn (Archiver) process B. To ensure that RMAN does not use the shared pool C. To enable the MMAN (Memory Manager) process D. To store the shared SQL area
B. To ensure that RMAN does not use the shared pool
What is the purpose of the undo tablespace? A. To manage space for sort operations B. To facilitate the rollback of transactions C. To allocate space for SQL cursors D. To maintain the Automatic Workload Repository
B. To facilitate the rollback of transactions
Which statement regarding Unified Auditing is true? A. Unified Auditing is enabled by setting the parameter AUDIT_TRAIL=UNIFIED. B. Unified Auditing writes audit records to the table owned by user AUDSYS. C. You have to log in as SYSDBA to purge the unified audit-trail records. D. Audit records for RMAN and Datapump are written to audit tables in SYS schema.
B. Unified Auditing writes audit records to the table owned by user AUDSYS. Audit records are written to a read-only table owned by AUDSYS user. The view UNIFIED_AUDIT_TRAIL shows the audit trail records.
Who is responsible for creating volume groups and mount points on Unix systems during the Oracle installation process? A. Oracle Universal Installer B. Unix system administrator C. Database administrator D. Application user
B. Unix system administrator
You have a database in Oracle 8.1.7 .4 version. The database size is less than 5GB, including all tablespaces (user data, system, temp, and rollback). Which method would you choose to upgrade to Oracle Database 12c? (Choose the best answer.) A. A two-phased upgrade. Upgrade to 10.2.0.5 first, and then upgrade to 12.1.0. B. Use traditional export/import using exp/imp tools. C. Use the transportable tablespace feature to copy the user_data tablespace to an Oracle Database 12c database. D. Use DBUA to upgrade the 8.1.7.4 database directly to 12.1.0.
B. Use traditional export/import using exp/imp tools.
Which keywords are required to grant a system or role privilege so that the grantee can further grant the privilege? A. WITH GRANT OPTION B. WITH ADMIN OPTION C. CASCADE REVOKE D. NOCASCADE REVOKE
B. WITH ADMIN OPTION
When is the Database Control repository created? A. When the CREATE DATABASE statement is executed B. When DBCA is used to create the database C. When OEM Database Express is configured D. When the instance is started
B. When DBCA is used to create the database
When does the recovery of a database occur? A. When the database moves from CLOSED to MOUNT mode B. When the database moves from MOUNT to OPEN mode C. When the database moves from NOMOUNT to MOUNT mode D. When the database moves from OPEN to CLOSED mode
B. When the database moves from MOUNT to OPEN mode
How do you manage fine-grained auditing? A. With the AUDIT and NOAUDIT statements B. With the DBMS_FGA package C. With the GRANT and REVOKE statements D. With the DBMS_AUDIT_MGMT package and CREATE_POLICY procedure
B. With the DBMS_FGA package 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.
What does the LGWR process do when a commit occurs? A. Writes the redo log buffers to the data files on disk B. Writes the redo log buffers to the redo log files on disk C. Updates the data files with the changed blocks D. Writes the change vectors to the redo log buffer
B. Writes the redo log buffers to the redo log files on disk
Multiple baseline metrics can be gathered and stored in the AWR. Why might you want more than one metrics baseline? A. You might want a separate baseline metric for each user. B. You might want a separate baseline metric for daytime usage versus off-hours usage. C. You might want a separate baseline metric for each schema. D. You would never want more than one baseline metric, even though it is possible to gather and store them.
B. You might want a separate baseline metric for daytime usage versus off-hours usage.
Which is a requirement for using host naming? A. You must use tnsnames.ora on the client. B. You must be using TCP/IP. C. You must have an OID present. D. You must have a sqlnet.ora file present on the client. E. None of the above.
B. You must be using TCP/IP.
What is the name of the text-alert log file in an Oracle database? A. alert_<dbname>.log B. alert_<instance>.log C. alert_<oracle_home>.log D. alert_<control_file>.log
B. alert_<instance>.log
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? A. set role all; B. alter user augustin default role all; C. alter session enable role info_czar; D. alter session enable info_czar identified by brozo;
B. alter user augustin default role all;
Which of the following is the correct way to start a listener called LISTENER ? A. lsnrctl startup listener B. lsnrctl start C. netca start D. netmgr start listener
B. lsnrctl start 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.
The Oracle Universal Installer is started by executing which program? A. emctl B. runInstaller C. ouistart D. isqlplusctl
B. runInstaller
Which type of parameter file is created when a database is created using DBCA? A. pfile B. spfile C. Both pfile and spfile D. None
B. spfile
Which object can be added to an existing bigfile tablespace? A. datafiletemp table B. table data C. tablespacedictionary table D. undo data
B. table data
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. 1. Oracle reads the blocks from data file to buffer cache and updates the blocks. 2. The server process writes the change vectors to the redo log buffer. 3. The user commits the change. 4. LGWR flushes the redo log buffer to redo log files. 5. A checkpoint occurs. 6. Changed blocks from the buffer cache are written to data files. 7. LGWR writes the changed blocks to the redo log buffer. A) 2, 3, 4, 5, 6, 7 B) 1, 2, 4, 5, 6, 7 C) 1, 2, 3, 4, 5, 6
C) 1, 2, 3, 4, 5, 6
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.) 1. CONNECT scott/[email protected]:1522 2. CONNECT scott/tiger@1522:dbprod.com/dbprod.com 3. CONNECT scott/tiger@//dbprod.com/1522:dbprod.com 4. CONNECT scott/[email protected]:1522/dbprod.com A) 1, 2 B) 1, 3 C) 1, 4
C) 1, 4
What is the best COMPATIBLE value to set during an Oracle Database 12c upgrade for maintaining the downgrade opportunity? A) 12.0.0 B) 11.0.0 C) 11.1.0.7 D) 11.2.0.2
C) 11.1.0.7
Choose two SGA structures that are required in every Oracle instance. 1. Large pool 2. Shared pool 3. Buffer cache 4. Java pool A) 1, 2 B) 1, 4 C) 2, 3 D) 3, 4
C) 2, 3
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 ? 1. Use Oracle Enterprise Manager Database Express. 2. Use Oracle Enterprise Manager Cloud Control. 3. Use DBMS_SERVER_ALERT package. 4. Use DBMS_SPACE package. A) 1, 2 B) 1, 3 C) 2, 3 D) 3, 4
C) 2, 3
Which two parameters configure automatic PGA memory management? 1. SGA_TARGET 2. PGA_AGGREGATE_TARGET 3. WORKAREA_SIZE_POLICY 4. PGA_AGGREGATE_LIMIT A) 1, 2 B) 1, 3 C) 2, 3 D) 2, 4
C) 2, 3
What option does the DBUA offer during the upgrade process? A) Disable archiving B) Back up the database after the upgrade C) Back up the database prior to the upgrade D) Create a new database
C) Back up the database prior to the upgrade
What must be fixed manually outside DBUA if it identifies space deficiency in tablespaces or the fast recovery area during the upgrade? A) Tablespace fragmentation B) Fast recovery area allocation C) Both A and B D) None of the above
C) Both A and B
Which package is used to manage fine-grained auditing in Oracle Database? A) DBMS_AUDIT B) DBMS_PRIVILEGES C) DBMS_FGA D) DBMS_POLICY
C) DBMS_FGA
When adding log-file members, what should you specify? A) The group number only. B) The existing group members only. C) Either the group number or all the existing group members. D) The instance name and the group number.
C) Either the group number or all the existing group members.
Is it necessary to multiplex your online redo log files to enable ARCHIVELOG mode of the database? A) Yes, it is required. B) No, it is not required. C) It is recommended but not required. D) It depends on the database configuration.
C) It is recommended but not required.
Which component in the SGA cannot be dynamically modified once the instance is started? A) Shared pool B) Database buffer cache C) Log buffer D) Large pool
C) Log buffer
What happens when one or more of a tablespace's data files are lost? A) The database becomes unavailable B) Users cannot work in other tablespaces C) Recovery of the affected data files can continue while the database is still online D) The instance will not start up
C) Recovery of the affected data files can continue while the database is still online
In which situation can the instance still be started if a tablespace is taken offline due to a missing data file? A) The missing data file belongs to the SYSTEM tablespace. B) The missing data file belongs to the UNDO tablespace. C) The missing data file does not belong to the SYSTEM or UNDO tablespace. D) The missing data file belongs to the TEMP tablespace.
C) The missing data file does not belong to the SYSTEM or UNDO tablespace.
What does the MEMORY_TARGET parameter represent in Oracle Database? A) The total size allocated for the SGA only B) The total size allocated for the PGA only C) The total size allocated for both SGA and PGA components D) The maximum size allocated for SGA and PGA components
C) The total size allocated for both SGA and PGA components
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; A. No more rows can be added to the JOBS table. B. No new blocks can be allocated to the JOBS table. C. No new extents can be allocated to the JOBS table. D. The table JOBS cannot be accessed. E. 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.
What is the block size of the predefined templates that come with data files in the DBCA? A. 2K B. 4K C. 8K D. 16K
C. 8K
Which of the following statements about tablespaces is true? A. A tablespace is the physical implementation of a logical structure called a namespace. B. A tablespace can hold the objects of only one schema. C. A bigfile tablespace can have only one data file. D. The SYSAUX tablespace is an optional tablespace created only if you install certain database options.
C. A bigfile tablespace can have only one data file.
During instance startup, which redo log files are required for instance recovery? A. INACTIVE and ACTIVE B. CURRENT and INACTIVE C. ACTIVE and CURRENT D. NONE
C. ACTIVE and CURRENT
Which redo log groups are required during instance crash recovery? A. INACTIVE and ACTIVE B. CURRENT and INACTIVE C. ACTIVE and CURRENT D. NONE
C. ACTIVE and CURRENT
Which process is responsible for writing redo log copies to the archive log location in an Oracle database? A. MMON (Manageability Monitor) B. MMNL (Manageability Monitor Lite) C. ARCn (Archiver) D. MMAN (Memory Manager)
C. ARCn (Archiver)
Of the following privileges, which is the least privilege required to create and manage Unified Auditing in a database, including purging of audit trail? A. SYSDBA B. DBA C. AUDIT_ADMIN D. AUDIT ANY
C. AUDIT_ADMIN 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.
When you configure an alert, which of the following types of alert thresholds can you use to monitor a tablespace for diminishing free space? A. Warning threshold B. Critical threshold C. Both A and B D. Neither A nor B
C. Both A and B
In response to users' complaints that the system is slow, a database administrator detects that a user is consuming too many resources. Which parameter controls the use of service units? A. CPU_PER_CALL B. CPU_PER_SESSION C. COMPOSITE_LIMIT D. ASM_POWER_LIMIT
C. COMPOSITE_LIMIT
Which compression option should be specified to compress blocks of tables that are used by the OLTP application? A. COMPRESS B. NOCOMPRESS C. COMPRESS FOR OLTP D. COMPRESS FOR ONLINE DML
C. COMPRESS FOR OLTP
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; A. CONNECT JOHN/JOHN1 B. CONNECT JOHN/john1 C. CONNECT john/John1 D. All of the above
C. CONNECT john/John1 In Oracle Database 12c, user passwords are case sensitive. The username is not case sensitive if you did not enclose it in double quotes
During an upgrade using DBUA, which task cannot be performed by DBUA? A. Configure listener.ora in the 12c Oracle home, if no listener.ora file exists. B. Register the database with an existing listener in the 12c Oracle home. C. Copy the tnsnames.ora file from an old Oracle home to the 12c Oracle home. D. Migrate the listener running in the old Oracle home to the 12c Oracle home.
C. Copy the tnsnames.ora file from an old Oracle home to the 12c Oracle home.
Which dictionary view is available in the database to view segment advisor results? A. DBA_ADVISOR_TASKS B. DBA_ADVISOR_TEMPLATES C. DBA_ADVISOR_OBJECTS D. DBA_ADVISOR_USAGE
C. DBA_ADVISOR_OBJECTS
Which data dictionary view can be queried to obtain information about the files that belong to locally managed temporary tablespaces? A. DBA_DATA_FILES B. DBA_TABLESPACES C. DBA_TEMP_FILES D. DBA_LOCAL_FILES
C. DBA_TEMP_FILES
Which mechanism is used by Oracle to identify the components that need to be upgraded while upgrading a database? A. V$OPTION B. V$LICENSE C. DBMS_REGISTRY D. DBMS_OPTIONS
C. DBMS_REGISTRY
Which parameter should be set for a nonstandard block size when Automatic Memory Management is used? A. DB_CACHE_SIZE B. DB_8K_CACHE_SIZE C. DB_16K_CACHE_SIZE D. None of the above
C. DB_16K_CACHE_SIZE
Which initialization parameter sets the location of the alert log? A. AUDIT_FILE_DEST B. LOG_ARCHIVE_DEST C. DIAGNOSTIC_DEST D. CORE_DUMP_DEST
C. DIAGNOSTIC_DEST
Which parameter disables conventional path loading when using SQL*Loader? A. PARALLEL B. SILENT C. DIRECT D. RESUMABLE
C. DIRECT
An administrator starts a database and initiates instance recovery. Which type of files can be recovered? A. Control B. Redo log C. Data D. Trace
C. Data
Which option provides the opportunity to defragment the database during the upgrade? A. Manual upgrade B. DBUA upgrade C. Data Pump Export/Import D. Transport Database
C. Data Pump Export/Import
Which statement is true regarding the SYSTEM tablespace? A. It can be made read-only. B. It can be offline. C. Data files can be renamed. D. Data files cannot be resized.
C. Data files can be renamed.
Which upgrade option automates the upgrade process without user intervention? A. Database Upgrade Assistant GUI interface B. Manual database upgrade C. Database Upgrade Assistant in silent mode D. Export/import upgrade
C. Database Upgrade Assistant in silent mode
Which process duplicates modified blocks from a buffer cache to files on disk? A. Apply Server (ASnn) B. Checkpoint (CKPT) C. Database Writer (DBWn) D. Log Writer (LGWR)
C. Database Writer (DBWn)
Who decides where the Oracle binaries will be installed on a Unix system? A. Oracle Universal Installer B. Unix system administrator C. Database administrator D. Application user
C. Database administrator
Which of the following is false about request queues? A. They reside in the SGA. B. They are shared by all the dispatchers. C. Each dispatcher has its own request queue. D. The shared server processes remove requests from the request queue.
C. Each dispatcher has its own request queue.
What must be done before a user can create a table or an index in a tablespace? A. Grant the user a quota in one or more specific tablespaces B. Grant the UNLIMITED TABLESPACE system privilege C. Either grant a quota in one or more tablespaces or grant the UNLIMITED TABLESPACE system privilege D. Assign the user a default tablespace
C. Either grant a quota in one or more tablespaces or grant the UNLIMITED TABLESPACE system privilege
What is the purpose of the SET ROLE statement in Oracle? A. Create new roles B. Grant system privileges C. Enable or disable roles in the current session D. Set default roles for a user
C. Enable or disable roles in the current session
Which of the following initialization parameters controls the mean time to recover the database, in seconds, after an instance failure? A. FAST_START_IO_TARGET B. LOG_CHECKPOINT_TIMEOUT C. FAST_START_MTTR_TARGET D. MTTR_TARGET_ADVICE E. FAST_START_TARGET_MTTR
C. FAST_START_MTTR_TARGET
For which type of system is the Shared Server configuration well suited? A. Large-scale data retrieval applications B. Decision support systems C. High-volume, small-transaction-oriented systems with many users connected D. Low-volume systems with a few users
C. High-volume, small-transaction-oriented systems with many users connected
Which net service naming method requires the client to use a fixed port number? A. Local B. External C. Host D. Directory
C. Host
In a Shared Server environment, where is the request queue located? A. In each dispatcher's memory B. In the PGA C. In the SGA D. In the UGA
C. In the SGA
Where do the Shared Server configuration parameters exist? A. Listener.ora file B. Tnsnames.ora file C. Init.ora or SPFILE file D. Sqlnet.ora file
C. Init.ora or SPFILE file
Which background process guarantees that committed data is saved even when the changes have not been recorded in data files? A. DBWn B. PMON C. LGWR D. CKPT E. ARCn
C. LGWR
What is the process that notifies the listener after a database connection is established? A. SMON B. PMON C. LREG D. LGWR
C. LREG
What process notifies the listener after a client connection is established in Oracle Database 12c and later? A. PMON B. SMON C. LREG D. DISPATCHER
C. LREG
Which component of the SGA contains the parsed SQL code? A. Database buffer cache B. Dictionary cache C. Library cache D. Parse cache
C. Library cache
Which of the following advisors determines if the space allocated to the shared pool, large pool, or buffer cache is adequate? A. SQL Tuning Advisor B. SGA Tuning Advisor C. Memory Advisor D. Pool Advisor
C. Memory Advisor
An administrator creates a user profile that forces a change to the user's password at the first login. Which clause did the administrator include in the create user statement? A. BY PASSWORD B. ACCOUNT LOCK C. PASSWORD EXPIRE D. ACCOUNT UNLOCK
C. PASSWORD EXPIRE
Which part of the connection string specifies the location where the listener is listening on the HOST? A. HOST B. CONNECT_DATA C. PORT D. SERVICE_NAME
C. PORT
What does the STARTUP FORCE command do in an Oracle database? A. Starts up the database normally B. Performs a SHUTDOWN IMMEDIATE and STARTUP of the database C. Performs a SHUTDOWN ABORT and STARTUP of the database D. Restarts the instance
C. Performs a SHUTDOWN ABORT and STARTUP of the database
What is the first step that the dispatcher performs after it receives a request from the user? A. Pass the request to a shared server. B. Place the request in a request queue in the PGA. C. Place the request in a request queue in the SGA. D. Process the request.
C. Place the request in a request queue in the SGA.
What do profiles in Oracle Database primarily limit? A. Number of user accounts B. Size of the database C. Resource-oriented session limits D. Number of tablespaces
C. Resource-oriented session limits
Which SQL statement is allowed with external tables? A. UPDATE B. DELETE C. SELECT D. INSERT
C. SELECT
Which parameter can an administrator enable without restarting a database? A. COMPATIBLE B. DB_DOMAIN C. SGA_TARGET D. UNDO_MANAGEMENT
C. SGA_TARGET
Which of the following startup options does not perform a database recovery? A. STARTUP B. STARTUP FORCE RESTRICT C. STARTUP NOMOUNT D. STARTUP OPEN E. STARTUP RESTRICT
C. STARTUP NOMOUNT
Choose the information that is not part of a ROWID. A. Data object number B. Relative file number C. Segment ID where the block belongs D. Block number in the file
C. Segment ID where the block belongs Segment ID is not part of ROWID. ROWID includes the data object number, relative f ile number where the block is stored, the block number in the file, and the row number.
You want to start up the database using a binary initialization file. What is another name for this file? A. Configfile B. Pfile C. Spfile D. init_pfile.ora
C. Spfile
What must be true about a tablespace's data files before you can make the tablespace read-only? A. The data files must be offline B. The data files must be in the backup mode C. The data files must be online and available D. The data files must be in a separate disk group
C. The data files must be online and available
When the database is in ARCHIVELOG mode, database recovery is possible up to which event or time? A. The last redo log file switch B. The last checkpoint position C. The last commit D. The last incremental backup using RMAN
C. The last commit
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? A. UNDO_RETENTION is a static parameter and, therefore, cannot be changed using ALTER SYSTEM. B. The change will be available to the instance only after a database cycle. C. The value is changed in memory, and when the database restarts the next time, the new value will be preserved when using the spfile. D. 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. The value is changed in memory, and when the database restarts the next time, the new value will be preserved when using the spfile.
What is the purpose of the DIAGNOSTIC_DEST initialization parameter in an Oracle database? A. To determine the location of the data files B. To determine the location of the redo log files C. To determine the location of the Automatic Diagnostic Repository home D. To determine the location of the server parameter file
C. To determine the location of the Automatic Diagnostic Repository home
What is the purpose of the LRU mechanism in an Oracle database? A. To enable Automatic Memory operations B. To perform ADDM analysis C. To ensure that free space is available in the shared pool and database buffer cache for each user's server process D. To store the shared SQL area
C. To ensure that free space is available in the shared pool and database buffer cache for each user's server process
Which data dictionary view is used to view the current values of parameters? A. V$DATABASE B. V$SPPARAMETER C. V$PARAMETER D. V$SYSPARAMETER
C. V$PARAMETER
You want to create a database using the DBCA with DB_BLOCK_SIZE as 32KB. Which statement is true? A. A block size of 32KB is not allowed in Oracle Database 12c. B. You must choose the Data Warehouse template in the DBCA. C. You must choose the Custom template in the DBCA. D. You must set the environment variable DB_BLOCK_SIZE to 32,768.
C. You must choose the Custom template in the DBCA.
Which of the following SQL statements disconnects a session after it has been idle for 30 minutes? A. alter session set idle_timeout=30; B. alter session set idle_timeout=1800; C. alter profile default limit idle_time 30; D. alter profile default set idle_timeout 30;
C. alter profile default limit idle_time 30;
Which parameter must be set after an upgrade to enable the new version's features? A. processes B. cluster_database C. compatible D. hs_autoregister
C. compatible
In which of the following files would you find the Shared Server configuration parameters? A. listener.ora B. mts.ora C. init.ora D. tnsnames.ora E. sqlnet.ora
C. init.ora
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? A. sqlnet.ora B. tnsnames.ora C. listener.ora D. None of the above
C. listener.ora
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? A. initOCA12C.ora B. OCA12Cspfile.ora C. spfile.ora D. init.ora
C. spfile.ora
From the following list, choose two processes that are optional in an Oracle Database 12c database. 1. MMON 2. MMNL 3. ARCn 4. MMAN A) 1, 2 B) 2, 3 C) 1, 3 D) 3, 4
D) 3, 4
When you're using DBUA to upgrade a database from Oracle 10g, which of the following activities are not performed by DBUA? (Choose two.) 1. Perform the pre-upgrade steps. 2. Change listener.ora to configure the new Oracle home directory information. 3. Disable archiving during the upgrade. 4. Back up the database after the upgrade. 5. Recompile any invalid objects. 6. Lock the new user accounts that were created. 7. Adjust the initialization parameter values. 8. Remove the deprecated initialization parameters. A) 1, 2 B) 1, 3 C) 2, 3 D) 3, 4
D) 3, 4
You performed the following statement in the database. ALTER TABLESPACE CUST_DATA READ ONLY; What actions can you perform on the CUST_INFO table in the CUST_DATA tablespace. (Choose Two.) 1. ALTER TABLE CUST_INFO DROP COLUMN xx; 2. TRUNCATE TABLE CUST_INFO; 3. INSERT INTO CUST_INFO VALUES (...); 4. DROP TABLE CUST_INFO; 5. RENAME CUST_INFO TO CUSTOMER_INFO; A) 1, 2 B) 2, 3 C) 3, 4 D) 4, 5
D) 4, 5
Which action is required for John to avoid the error when querying user tables in the database, given that HR_QUERY has the SELECT ANY TABLE privilege? A) Define HR_QUERY as a default role for John B) Use the SET ROLE statement C) Assign a password to HR_QUERY D) A and B
D) A and B
Which of the following is NOT managed by AutoTask? A) Optimizer statistics B) Segment Advisor C) SQL tuning D) AWR snapshots
D) AWR snapshots
Which of the following tasks does DBUA handle during the Oracle Database upgrade process? A) Running the Pre-Upgrade Information tool B) Configuring the network C) Performing a backup of the database before the upgrade D) All of the above
D) All of the above
What occurs during instance recovery? A) Oracle uses redo log files to restore data files to the state before the instance failure. B) Oracle uses undo data in the undo tablespace to roll back uncommitted transactions. C) Oracle synchronizes all data files to the same SCN. D) Both A and B
D) Both A and B
What does the Pre-Upgrade Information utility do? A) Makes changes to the database B) Creates a fix-up script that can fix all issues in the database C) Requires only preupgrd.sql to be copied D) Creates a fix-up script that can be used to make trivial changes to the database
D) Creates a fix-up script that can be used to make trivial changes to the database
Which statement is true? A. A database can have only one control file. B. A database must have at least two control files. C. A database may have zero or more control files. D. A database must have at least one control file.
D. A database must have at least one control file.
Which statement regarding reclaiming wasted space is true? A. Segment shrink is accomplished using the ALTER TABLE ... MOVE and ALTER INDEX ... REBUILD statements. B. Segment shrink and reorganize are similar operations. C. When a table segment shrink operation is completed, the dependent indexes are in invalid state and need to be rebuilt. D. A segment shrink operation is applicable only on tablespaces with automatic segment space management.
D. A segment shrink operation is applicable only on tablespaces with automatic segment space management 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.
What is an spfile in Oracle? A. A text file containing default initialization parameters B. A text file containing nondefault initialization parameters C. A server-side binary file containing default initialization parameters D. A server-side binary file containing nondefault initialization parameters
D. A server-side binary file containing nondefault initialization parameters
What can cause an ORA-12154 error? A. Supplying a net service name not in the tnsnames.ora file B. Problems with the tnsnames.ora file C. Listener problems D. A, B
D. A, B
Connection Manager provides which of the following? A. Multiplexing B. Cross-protocol connectivity C. Network access control D. All of the above
D. All of the above
What does Connection Manager provide? A. Multiplexing of connections B. Cross-protocol connectivity C. Network access control D. All of the above
D. All of the above
Which of the following are part of the Oracle database? A. Control file B. Data file C. Redo log files D. All of the above
D. All of the above
Which of the following are required SGA structures in an Oracle database instance? A. Database buffer cache B. Shared pool C. Log buffer D. All of the above
D. All of the above
Which of the following system privileges should be granted judiciously because they can allow the grantee to masquerade as another user? A. CREATE ANY JOB B. ALTER USER C. CREATE ANY PROCEDURE D. All of the above
D. All of the above
Which of the following options for the pfile/spfile's STATISTICS_LEVEL parameter turns off AWR statistics gathering and ADDM advisory services? A. OFF B. TYPICAL C. ALL D. BASIC
D. BASIC
Why are online backups known as inconsistent backups? A. Because not all control files are synchronized to the same SCN until the database is shut down B. Because both committed and uncommitted transactions are included in a backup when the database is online C. Because a database failure while an online backup is in progress can leave the database in an inconsistent state D. Because online backups make copies of data files while they are not consistent with the control file
D. Because online backups make copies of data files while they are not consistent with the control file
Which operations require an exclusive lock on the object? A. ALTER TABLE ... MOVE B. ALTER TABLE ... REBUILD C. Segment shrink D. Both A and B
D. Both A and B
Which Oracle tools or methods can be used to set the threshold for tablespaces? A. OEM Database Express B. OEM Cloud Control C. DBMS_SERVER_ALERT.SET_THRESHOLD D. Both A and C
D. Both A and C
Which view has space information? A. DBA_OBJECTS B. V$TABLESPACE C. V$SQLTEXT D. DBA_DATA_FILES
D. DBA_DATA_FILES
Which parameter determines the location of the alert log? A. CORE_DUMP_DEST B. BACKGROUND_DUMP_DEST C. ALERT_LOG_DEST D. DIAGNOSTIC_DEST
D. DIAGNOSTIC_DEST
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? A. Modify the server's kernel parameters. B. Create a new Unix user to own the Oracle software. C. Create the mount points and directory structure using the OFA model. D. Determine which directory will be used for $ORACLE_HOME.
D. Determine which directory will be used for $ORACLE_HOME.
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? A. Writes the changed blocks to data files. B. Writes the changed blocks to redo log files. C. Triggers checkpoint and thus LGWR writes the changes to redo log files. D. Does nothing.
D. Does nothing
Which parameter is used when creating a user account that will be authenticated by the operating system? A. GLOBALLY B. VALUES C. USING D. EXTERNALLY
D. EXTERNALLY
What portion of the tnsnames.ora file specifies the name or IP address of the server where the listener process is listening? A. CONNECT_DATA B. SERVER C. SERVICE_NAME D. HOST
D. HOST
Which type of database backup can be performed while a database is online? A. Partial B. Full C. Consistent D. Inconsistent
D. Inconsistent
Which SGA component will you increase or configure so that RMAN tape backups do not use memory from the shared pool? A. Java pool B. Streams pool C. Recovery pool D. Large pool
D. Large pool
In which state can you rename the data files belonging to the SYSTEM tablespace using the ALTER DATABASE RENAME FILE statement? A. OPEN B. CLOSED C. NOMOUNT D. MOUNT
D. MOUNT
Automatic segment space management on the tablespace causes which of the following table attributes in that tablespace to be ignored? A. The whole storage clause B. NEXT and PCTINCREASE C. BUFFERPOOL and FREEPOOL D. PCTFREE and PCTUSED
D. PCTFREE and PCTUSED
When a user session is terminated, which processes are responsible for cleaning up and releasing locks? A. DBWn B. LGWR C. MMON D. PMON E. SMON
D. PMON
What is the primary responsibility of the Process Monitor (PMON) process in an Oracle database? A. Coalescing contiguous free space in tablespaces B. Writing the redo log buffer information to the online redo log files C. Executing user queries D. Performing session cleanup and freeing up resources after a user session is terminated
D. Performing session cleanup and freeing up resources after a user session is terminated
What type of shutdown does not perform a clean shutdown in an Oracle database? A. SHUTDOWN IMMEDIATE B. SHUTDOWN TRANSACTIONAL C. SHUTDOWN NORMAL D. SHUTDOWN ABORT
D. SHUTDOWN ABORT
Which command performs a clean shutdown without waiting for clients to disconnect? A. SHUTDOWN ABORT B. SHUTDOWN NORMAL C. SHUTDOWN TRANSACTIONAL D. SHUTDOWN IMMEDIATE
D. SHUTDOWN IMMEDIATE
Which startup options must be used to start the instance when you create a new database? A. STARTUP FORCE B. STARTUP MOUNT C. STARTUP RESTRICT D. STARTUP NOMOUNT
D. STARTUP NOMOUNT
Which statement about DBUA upgrade is true? A. You must shut down and start the source database using the UPGRADE option before invoking DBUA for upgrade. B. The new home of the database must be added to oratab file. C. DBUA displays the progress of the upgrade, but you have no way of knowing which step or phase is being executed. D. Space requirements are identified by DBUA in the Fast Recovery Area; the SYSTEM and SYSAUX tablespaces must be fixed manually outside DBUA.
D. Space requirements are identified by DBUA in the Fast Recovery Area; the SYSTEM and SYSAUX tablespaces must be fixed manually outside DBUA.
When configured in Shared Server mode, which of the following is contained in the PGA? A. Cursor state B. Sort information C. User session data D. Stack space E. None of the above
D. Stack space
What privilege is needed to alter a table in another user's schema? A. Object privilege ALTER on that object B. System privilege CREATE TABLE C. System privilege ALTER TABLE D. System privilege ALTER ANY TABLE
D. System privilege ALTER ANY TABLE
Which types of events are recorded in an alert log? A. Package creation B. User creation C. Role creation D. Tablespace creation
D. Tablespace creation
You've noticed that when an instance crashes, it takes a long time to start up the database. Which advisor can be used to tune this situation? A. The Undo Advisor B. The SQL Tuning Advisor C. The Database Tuning Advisor D. The MTTR Advisor E. The Instance Tuning Advisor
D. The MTTR Advisor
Which of the following is true about Shared Server? A. Dedicated connections cannot be made when Shared Server is configured. B. It is recommended that DSS type batch jobs be performed when connected via Shared Server. C. The database can be started when connected via Shared Server. D. The database cannot be stopped when connected via Shared Server.
D. The database cannot be stopped when connected via Shared Server.
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: A. Oracle spawns another PMON process automatically. B. The database hangs, and the DBA must manually start a PMON process. C. If the database is in ARCHIVELOG mode, Oracle automatically starts another PMON process and recovers from the database hang. D. The instance crashes and needs to be restarted.
D. The instance crashes and needs to be restarted.
The LRU algorithm is used to manage what part of the Oracle architecture? A. A. Users who log on to the database infrequently and may be candidates for being dropped B. The data file that stores the least amount of information and will need the least frequent backup C. The tables that users rarely access so that they can be moved to a less active tablespace D. The shared pool and database buffer cache portions of the SGA
D. The shared pool and database buffer cache portions of the SGA.
How would you drop a tablespace if the tablespace were not empty? A. Rename all the objects in the tablespace, and then drop the tablespace. B. Remove the data files belonging to the tablespace from the disk. C. Use ALTER DATABASE DROP <TABLESPACE NAME> CASCADE . D. Use DROP TABLESPACE <TABLESPACE NAME> INCLUDING CONTENTS .
D. Use DROP TABLESPACE <TABLESPACE NAME> INCLUDING CONTENTS .
Which script creates the database dictionary? A. dictionary.sql B. catdict.sql C. catproc.sql D. catalog.sql
D. catalog.sql
What is the correct syntax to use with the Oracle Easy Connect method when connecting to a non-URL location where the database name and the hostname are the same, and the port is nondefault? A. connect username/password@host:port B. connect username/password@host C. connect username/password@service_name D. connect username/password@host:port/service_name
D. connect username/password@host:port/service_name
Which of the following statements has the correct syntax and gives the ability to grant the privilege to other users? A. grant alter any table with grant option to desmond; B. grant alter on gl.accounts to desmond with admin option; C. grant alter any table to desmond with grant option; D. grant alter any table to desmond with admin option;
D. grant alter any table to desmond with admin option;
Name the scripts used by the Pre-Upgrade Information and Post-Upgrade Status tools? A. preupgrd.sql, postupgrd.sql B. utlu121i.sql, utlu121s.sql C. utlu121i.sql, postupgrd.sql D. preupgrd.sql, utlu121s.sql
D. preupgrd.sql, utlu121s.sql
Which of the following statements enables the role user_admin in the current session? A. alter session enable role user_admin; B. alter session set role user_admin; C. alter role user_admin enable; D. set role user_admin;
D. set role user_admin;
Which tool provides status upgrade result information after an upgrade? A. utluiobj.sql B. emremove.sql C. catuppst.sql D. utlu121s.sql
D. utlu121s.sql
Which types of issues can be identified by the Data Recovery Advisor? A) Media failure B) Physical corruption C) Logical corruption D) Missing data files E) All of the above
E) All of the above
To improve upgrade performance and reduce the upgrade time, what tasks should be completed before starting the upgrade? A. Run dbms_stats.gather_dictionary_stats. B. Run $ORACLE_HOME/rdbms/admin/emremove.sql. C. Run dbms_stats.gather_database_stats. D. Purge DBA_RECYCLEBIN. E. A, B
E. A, B
What does the catalog.sql script create? A. Data dictionary views B. Dynamic performance views C. Synonyms D. Tablespaces E. A, B, C
E. A, B, C
Which of the following shutdowns will not require a recovery on a subsequent database startup? A. SHUTDOWN IMMEDIATE B. SHUTDOWN TRANSACTIONAL C. SHUTDOWN NORMAL D. SHUTDOWN ABORT E. A,B, C
E. A, B, C
Which two options are not true with the STARTUP UPGRADE mode instance startup? A. It initiates the upgrade process automatically after the instance is started. B. It suppresses spurious and unnecessary error messages, especially the ORA-00942. C. It handles certain system startup parameters that could interfere with the upgrade. D. This option is more of a documentation purpose when the database is started for upgrade. Its functionality is no different than the default STARTUP option. E. A, D
E. A, D
What are some of the functions of the Database Configuration Assistant (DBCA)? A. Create databases B. Manage templates C. Add database options D. Delete databases E. All of the above
E. All of the above
What are the default characteristics of tablespace X? A. Oracle Managed File of 100M B. Autoextensible up to 32GB C. LOGGING, NOCOMPRESS, ONLINE, PERMANENT D. EXTENT MANAGEMENT LOCAL AUTOALLOCATE, SEGMENT SPACE MANAGEMENT AUTO E. All of the above
E. All of the above
The instance can still be started even if some data files are missing; this rule does not apply to which tablespaces? (Choose all that apply.) A. USERS B. SYSTEM C. UNDO D. SYSAUX E. B, C
E. B, C
What types of operations are not allowed on a read-only tablespace? A. SELECT and DELETE B. INSERT and UPDATE C. TRUNCATE and ALTER TABLE D. DROP and RENAME E. B, C
E. B, C
Which failures can be detected by the Data Recovery Advisor, which then provides repair recommendations? Choose all that apply. A. Instance failure B. Accidental deletion of a data file C. Disk containing one redo log member is offline D. User accidentally dropped a table E. B, C
E. B, C
Which of the following is true about EM Database Express? (Choose Two) A. You can start up and shut down a database using Database Express. B. You can read the contents of the alert log file. C. You can modify static initialization parameters. D. The CREATE DATABASE statement creates the Database Control repository in the Database. E. B, C
E. B, C
How is a suspended resumable session notified in Oracle? A. Through an out-of-the-box mechanism B. Writing information to the alert log C. Firing an AFTER SUSPEND trigger D. Issuing a resumable session suspended alert E. B, C, D
E. B, C, D
Which of the following substitution-variable formats are always required for specifying the names of the archived redo log files? Choose all that apply. A. %d B. %s C. %r D. %t E. B, C, D
E. B, C, D
Which of the following statements regarding the Pre-Upgrade utility preupgrd.sql are correct? (Choose two.) A. It checks for space availability, user conflicts, role conflicts, initialization parameters, etc., and prepares fix-up scripts to take care of all issues. B. The Pre-Upgrade Information utility does not make any changes to the database, but merely reports results. C. preupgrd.sql is a standalone script and can be copied to another location to execute on the database to be upgraded. D. It recommends the amount of free space required in the SYSTEM and SYSAUX tablespaces. E. B, D
E. B, D
