1Z0-082 - Oracle Database Administration I Managing Database Instances Questions and Answers — Questions and Answers
Question 1: A database administrator needs to enable Automatic Memory Management (AMM) for an Oracle instance. Which two initialization parameters must be set to achieve this?
- SGA_TARGET and PGA_AGGREGATE_TARGET
- MEMORY_TARGET and MEMORY_MAX_TARGET (Correct answer)
- DB_CACHE_SIZE and SHARED_POOL_SIZE
- SGA_MAX_SIZE and PGA_AGGREGATE_LIMIT
Correct answer: MEMORY_TARGET and MEMORY_MAX_TARGET
Automatic Memory Management (AMM) allows Oracle to automatically manage and tune the total memory allocated to the System Global Area (SGA) and the Program Global Area (PGA). To enable AMM, you must set MEMORY_TARGET to define the total memory for the instance, and it is best practice to also set MEMORY_MAX_TARGET, which specifies the maximum value to which MEMORY_TARGET can be dynamically increased.
Question 2: A DBA issues the `SHUTDOWN IMMEDIATE` command. Which of the following best describes the actions the Oracle instance will take?
- Waits for all active user sessions to disconnect before shutting down.
- Terminates all active sessions, rolls back uncommitted transactions, and then shuts down. (Correct answer)
- Immediately stops all database processes without rolling back transactions, requiring instance recovery on the next startup.
- Waits for all active transactions to complete, prevents new transactions, and then shuts down.
Correct answer: Terminates all active sessions, rolls back uncommitted transactions, and then shuts down.
The `SHUTDOWN IMMEDIATE` command does not wait for current user sessions to disconnect. It proceeds to terminate all active sessions, roll back any uncommitted transactions, and then performs a clean shutdown. No instance recovery is needed upon the next startup.
Question 3: During the startup sequence of an Oracle database, in which stage are the control files read to identify the location of data files and online redo log files?
- NOMOUNT
- OPEN
- QUIESCE
- MOUNT (Correct answer)
Correct answer: MOUNT
The Oracle startup sequence proceeds through three main stages: NOMOUNT, MOUNT, and OPEN. During the MOUNT stage, the instance reads the control files to get the names and locations of the data files and online redo log files, thereby associating the database with the instance.
Question 4: You are managing an Oracle database that uses a Server Parameter File (SPFILE). You need to change a dynamic initialization parameter and ensure the change persists across instance restarts. Which `SCOPE` option should you use with the `ALTER SYSTEM` command?
- SCOPE=MEMORY
- SCOPE=PFILE
- SCOPE=BOTH (Correct answer)
- SCOPE=SPFILE
Correct answer: SCOPE=BOTH
When using an SPFILE, the `SCOPE=BOTH` option applies the change to the currently running instance (memory) and also writes the change to the SPFILE. This ensures the new parameter value is used immediately and also persists after the next shutdown and startup.
Question 5: A DBA needs to perform a critical administrative task that requires no active non-DBA transactions to be running. However, they want to avoid shutting down the database and disconnecting all users. Which command can be used to achieve this state?
- STARTUP RESTRICT
- ALTER SYSTEM ENABLE RESTRICTED SESSION
- SHUTDOWN TRANSACTIONAL
- ALTER SYSTEM QUIESCE RESTRICTED (Correct answer)
Correct answer: ALTER SYSTEM QUIESCE RESTRICTED
The `ALTER SYSTEM QUIESCE RESTRICTED` command puts the database into a quiesced state. In this state, all active non-DBA sessions are allowed to complete their current transaction or query, but no new non-DBA activity is permitted. This allows DBAs to perform tasks that require a stable state without shutting down the instance.
Question 6: What is the primary difference between an Oracle SPFILE and a PFILE?
- A PFILE is a binary file, while an SPFILE is a text file.
- Changes to a PFILE can be made dynamically, while SPFILE changes require a restart.
- An SPFILE is a binary file whose parameters can be changed dynamically, while a PFILE is a text file that requires an instance restart for changes to take effect. (Correct answer)
- An SPFILE is only used in Real Application Clusters (RAC) environments, while a PFILE is for single-instance databases.
Correct answer: An SPFILE is a binary file whose parameters can be changed dynamically, while a PFILE is a text file that requires an instance restart for changes to take effect.
The fundamental difference is that an SPFILE (Server Parameter File) is a binary file maintained by the Oracle server, allowing for dynamic parameter changes using `ALTER SYSTEM` that can persist across restarts. A PFILE (Parameter File) is a client-side, static text file that can be manually edited, but any changes require the instance to be restarted to become effective.
1Z0-082 - Oracle Database Administration I Managing Database Instances Questions and Answers
A database administrator needs to enable Automatic Memory Management (AMM) for an Oracle instance.
Which two initialization parameters must be set to achieve this?