Oracle Certification
121 practice questions with correct answers and detailed explanations. Use this guide to review concepts before taking the practice exam.
The Oracle Oracle Database Administration II (1Z0-083) certification validates professional expertise in Oracle technologies. This study guide covers all 121 practice questions from our 1Z0-083 practice test, complete with correct answers and explanations to help you understand each concept thoroughly.
Review each question and explanation below, then test yourself with the full interactive practice exam to measure your readiness.
You need to configure Oracle Database to automatically manage memory allocation across the SGA and PGA. Which parameter should you set to enable this feature?
MEMORY_TARGET enables Automatic Memory Management (AMM), which automatically distributes memory between SGA and PGA components based on workload demands.
A database user reports that they cannot create new tables due to insufficient space. After investigation, you find the tablespace is full but has free space in a datafile that can be extended. What is the BEST approach to resolve this immediately?
Resizing (extending) an existing datafile is the quickest solution to provide immediate space without the overhead of adding new files or migrating objects.
You are implementing a backup strategy for a critical production database. The database must be able to recover to any point in time within the last 7 days. Which feature must be enabled?
Archive Log mode combined with sufficient archive log retention for 7 days enables point-in-time recovery (PITR). Flashback Database alone cannot guarantee 7-day recovery without archive logs.
What is the primary purpose of the Fast Recovery Area (FRA)?
The FRA (Fast Recovery Area) is a dedicated disk location that stores backups, archive logs, and flashback logs, enabling faster and more organized recovery operations.
You notice that a long-running SQL query is consuming excessive CPU and memory resources. Which view should you query to identify the session consuming the most resources?
V$SESSSTAT provides detailed statistics per session (including CPU and memory), which can be joined with V$SESSION to identify the problematic session and its resource usage patterns.
When configuring Oracle Net Services, which file contains the listener configuration and specifies which databases and services the listener will manage?
The listener.ora file contains Listener process configuration, including which databases the listener manages and the protocols it listens on.
You have configured Oracle Enterprise Manager Cloud Control to monitor 50 databases across your organization. A database is showing a critical alert for high redo log generation. Where would you check detailed metrics to diagnose the root cause?
AWR reports provide comprehensive historical data on database performance, including redo generation rates, which helps identify trends and the actual cause of high redo log activity.
A production database experiences intermittent performance degradation during peak hours. You want to establish a performance baseline and identify when degradation occurs. Which approach is most appropriate?
AWR provides periodic snapshots of database activity; comparing baseline AWR reports with those from degradation periods clearly identifies performance changes and their causes.
You are managing tablespace fragmentation and want to coalesce free space in a locally managed tablespace. Which statement correctly coalesces free extents?
Locally managed tablespaces automatically coalesce adjacent free extents, eliminating the need for manual coalescing operations that were required with dictionary-managed tablespaces.
Which initialization parameter controls the maximum number of processes that can connect to the database simultaneously?
The PROCESSES parameter specifies the maximum number of operating system processes that can connect to an Oracle instance.
You need to implement a strategy where archived redo logs are automatically backed up and deleted after backup completion. Which parameter should you configure?
The LOG_ARCHIVE_DEST_1 parameter with the DELETE option automatically deletes archived logs after they are successfully backed up, implementing the desired backup-and-delete workflow.
A table in your database contains sensitive personal information and must be encrypted at the column level. Which Oracle feature provides transparent column-level encryption?
Transparent Data Encryption (TDE) supports column-level encryption using the ENCRYPT option in the column definition, providing transparent encryption and decryption without application changes.
When using RMAN to restore a database, you want to recover all changes up to but not including a specific time. Which RMAN RECOVER command option should you use?
The UNTIL BEFORE TIME clause recovers the database to the point immediately before the specified time, excluding transactions at or after that time.
You are configuring a standby database using Oracle Data Guard. The primary database is in ARCHIVELOG mode and you want automatic failover capability. Which Data Guard protection mode should you use?
Maximum Protection mode ensures zero data loss during failover by requiring acknowledgment from at least one standby before the primary commits, meeting the automatic failover requirement with data safety.
Which parameter controls the interval at which DBWR flushes dirty buffers from the SGA to disk?
DBWR is a background process that flushes dirty buffers based on checkpoints and log switches; no specific parameter controls flush intervals—DBWR operates opportunistically based on database events and thresholds.
After a failed RMAN restore operation, you need to verify which backup sets are valid and usable. Which RMAN command will validate all backup sets in the repository?
The VALIDATE BACKUPSET ALL command in RMAN checks all backup sets for corruption and validity without actually restoring data, helping identify usable backups.
You want to implement an automated job that backs up the database every night at 10 PM and sends an email notification on completion or failure. Which Oracle component should you use?
DBMS_SCHEDULER is the modern Oracle scheduling tool that supports job definitions, notifications via email through job chains, and is the recommended approach for automated database tasks.
A database is running in NOARCHIVELOG mode and you need to change it to ARCHIVELOG mode with minimal downtime. What is the correct sequence of steps?
ARCHIVELOG mode requires the database to be in MOUNT (not OPEN) state. The correct sequence is shutdown, mount the database, enable archivelog, and then open it.
You observe that a specific table experiences significant fragmentation due to frequent DML operations. Which method is most efficient for defragmenting this table without causing extended downtime?
ALTER TABLE SHRINK SPACE compacts a table and reclaims unused space with minimal blocking (online shrink), making it the most efficient method for addressing fragmentation.
When configuring Oracle Database Network Encryption, which sqlnet.ora parameter specifies the encryption algorithms that can be used for client-server communication?
The SQLNET.ENCRYPTION_ALGORITHMS_CLIENT parameter in sqlnet.ora specifies which encryption algorithms the client will accept, controlling encryption negotiation with the server.
A table contains 100 million rows and you need to create an index on a large column. Creating a traditional B-tree index proves too slow. Which indexing technique provides faster index creation with potential space savings?
Compressed indexes reduce space requirements and parallel index creation (PARALLEL clause) significantly accelerates index building on large tables by utilizing multiple processes.
You need to restore a database from a backup taken 3 days ago, but you want to recover all transactions up to 2 hours before the current time. Which combination of RMAN commands correctly accomplishes this?
To recover to a specific point in time, you must have the backup plus all archive logs from backup time to the recovery time. RESTORE restores the backup; RECOVER applies archive logs until the specified time.
Which Oracle background process is responsible for cleaning up rejected connections and managing connection pooling in the database listener?
PMON (Process Monitor) is responsible for detecting and cleaning up abnormal process terminations, managing connection resources, and handling connection pooling operations.
You are implementing a Disaster Recovery (DR) solution where the standby database must never lag more than 30 seconds behind the primary. Which Data Guard configuration and redo transport mechanism best meets this requirement?
Maximum Protection mode with synchronous redo transport ensures the standby is never more than a few seconds behind, though at the cost of potential performance impact when standby acknowledgment is slow.
A report job is scheduled to run daily using Oracle Scheduler, but it frequently encounters locking conflicts with OLTP transactions. How should you modify the job to reduce contention?
Scheduling jobs during off-peak hours (using DBMS_SCHEDULER's scheduling capabilities) is the most straightforward approach to reduce contention with OLTP transactions.
Which parameter controls the maximum number of processes that can connect to an Oracle database instance?
The PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. This is a fundamental initialization parameter for database capacity planning.
You need to move a datafile from one location to another on the same server while the database is online. Which method is most appropriate?
To move a datafile while the database is open, you must offline the associated tablespace, physically move the file, rename the datafile reference in the database, and then online the tablespace. This maintains data integrity and allows continued database operation.
What is the primary purpose of the UNDO tablespace in Oracle Database?
The UNDO tablespace maintains undo records that provide read consistency for queries and allow transactions to be rolled back. It is essential for Oracle's MVCC (Multi-Version Concurrency Control) mechanism.
A user reports that their long-running query is waiting on a resource. You suspect there is a blocking lock. Which view should you query to identify which session is holding the lock?
V$LOCKED_OBJECT and DBA_LOCKS views show which objects are locked and by which sessions. These views are essential for diagnosing blocking lock situations in the database.
You want to implement automatic undo retention to help with long-running queries and Flashback operations. Which parameter must be set?
The UNDO_RETENTION parameter specifies how long undo data should be retained in seconds. When combined with an auto-extending undo tablespace, it ensures adequate undo for long-running queries and Flashback operations.
Which of the following best describes the role of the checkpoint process in Oracle Database?
The checkpoint process writes modified data blocks from the SGA to disk and updates the control file with the current checkpoint SCN. This reduces recovery time and ensures data durability.
You are implementing a backup strategy that includes daily incremental backups. What is a key advantage of incremental backups over full backups?
Incremental backups only capture blocks that have changed since the previous backup, significantly reducing backup time, network bandwidth, and storage requirements compared to full backups.
A tablespace is running out of space. You have added new datafiles, but space issues persist. Which of the following should you investigate first?
Before adding more storage, you should identify what is consuming space. Large objects, unneeded data, or poorly designed tables may be the root cause. Archiving or purging old data is often more cost-effective than continuously adding storage.
Which initialization parameter controls the size of the Shared Pool, which caches SQL statements and their execution plans?
SHARED_POOL_SIZE directly controls the size of the Shared Pool component of the SGA. A larger Shared Pool allows more SQL statements and execution plans to be cached, improving performance for frequently executed queries.
You need to perform a point-in-time recovery to restore the database to a specific time in the past. Which method is most appropriate when the database is in ARCHIVELOG mode?
The standard method for point-in-time recovery in ARCHIVELOG mode is to restore from the most recent backup and apply archived redo logs up to the target time using the RECOVER DATABASE UNTIL TIME command.
A database experiences high disk I/O on the redo log files. What might be the cause, and how would you address it?
If redo log files are undersized, the log buffer fills quickly, causing frequent log switches and increased disk I/O. Increasing redo log file size allows longer intervals between switches, reducing disk write pressure.
You want to grant a user the ability to create tables, indexes, and views in their own schema, but prevent them from creating other objects. Which approach is best?
Granting specific system privileges (CREATE TABLE, CREATE INDEX, CREATE VIEW) without ADMIN OPTION allows the user to create those objects only in their own schema. ADMIN OPTION allows the user to grant these privileges to others, which you want to prevent.
What is the primary function of the alert log file in Oracle Database?
The alert log is a chronological log of database errors, warnings, and significant events. It is essential for diagnosing problems and monitoring database health. The location is determined by the DIAGNOSTIC_DEST parameter.
You are designing a backup strategy for a critical production database. The database is in ARCHIVELOG mode. Which combination provides the best protection against data loss?
For critical databases, daily full or incremental backups combined with continuous archiving and off-site storage provide the best protection. This minimizes data loss risk and enables recovery to any point in time.
A table has grown significantly, and queries are becoming slower. You decide to partition the table. Which partitioning method is best when you want to divide data based on date ranges?
Range partitioning is ideal for time-based data because it divides rows into partitions based on ranges of column values (e.g., by month or year). This is the most common approach for date-based partitioning.
Which dynamic performance view can be used to monitor real-time session activity and resource consumption?
V$SESSION displays information about all current sessions, while V$SESSTAT provides session-level statistics on CPU, memory, and I/O. Together, they offer comprehensive real-time monitoring of session activity.
You need to implement a data dictionary view that shows all privileges granted to a specific user. Which approach would you use?
DBA_TAB_PRIVS (for object privileges) and DBA_SYS_PRIVS (for system privileges) are the data dictionary views that show granted privileges. Filtering the GRANTEE column returns privileges for a specific user.
A database has experienced a media failure on a non-critical datafile. The tablespace containing the file is still needed. What is the best approach for recovery with minimal downtime?
For a single datafile failure in a non-critical tablespace, offlining the tablespace allows the rest of the database to remain available. Then restore and recover just that datafile before bringing the tablespace online.
Which parameter determines whether the database automatically generates statistics for the optimizer when they are missing?
The STATISTICS_LEVEL parameter controls the level of automatic statistics collection. When set to ALL, the database automatically gathers optimizer statistics for missing or stale data.
You want to ensure that a user cannot exceed a specific amount of CPU time per session. Which resource limit should you implement?
CPU_PER_SESSION is a resource limit within a database profile that restricts CPU usage per session, measured in centiseconds. Assigning this profile to a user enforces the limit automatically.
A table has multiple columns with NULL values, and you want to optimize queries that check for the presence or absence of NULL. What should you do?
A function-based index using NVL() or COALESCE() creates an index on the result of the function, allowing the optimizer to use the index for queries that filter on NULL values. Standard B-tree indexes do not include all NULL values.
You need to configure database auditing to track when users attempt to execute privileged operations. Which auditing approach should you use?
Unified auditing provides comprehensive tracking of all database activity including administrative operations, user actions, and security-relevant events. It is the recommended modern approach in newer Oracle versions.
What is the impact of setting LOG_ARCHIVE_DEST_STATE_<n> to DEFER for an archiving destination?
Setting LOG_ARCHIVE_DEST_STATE_<n> to DEFER temporarily disables archiving to that destination. This allows maintenance without permanently removing the configuration. Other destinations continue to function normally.
A user reports that they cannot access a table they previously had access to. The user's privileges were revoked yesterday. Which step would restore access with the least disruption?
The quickest and least disruptive approach is to re-grant the necessary object privileges either directly or through a role. This restores access without recreating the user or affecting other database objects.
You want to implement a recovery strategy where the database can be recovered to any point in time within the last 7 days. What configuration is required?
To enable 7-day point-in-time recovery, enable ARCHIVELOG mode for archive retention, set UNDO_RETENTION to 604800 seconds (7 days), and enable FLASHBACK DATABASE in the FRA. Together these provide comprehensive recovery options.
When examining V$SESSION_WAIT, you notice many sessions waiting on 'db file scattered read'. What does this indicate?
The 'db file scattered read' wait event indicates sessions are waiting for multi-block reads from disk. This is common during full table scans and index fast full scans. High occurrences may suggest the need for more memory or query optimization.
You want to create a tablespace that grows automatically when space is needed. Which characteristics should the tablespace have?
A self-extending tablespace requires datafiles with AUTOEXTEND ON, local extent management (EXTENT_MANAGEMENT LOCAL), and automatic segment space management (SEGMENT_SPACE_MANAGEMENT AUTO). This provides efficient and automatic space administration.
You are managing an Oracle Database 19c instance and need to implement a backup strategy that ensures point-in-time recovery. Which initialization parameter must be set to enable the database to generate archive log files?
The log_archive_mode parameter controls whether the database operates in ARCHIVELOG mode or NOARCHIVELOG mode. When set to TRUE, archive log files are generated automatically, enabling point-in-time recovery. The other parameters configure archive destinations but do not enable archiving itself.
A database administrator needs to recover a table that was accidentally dropped 2 hours ago. The database is running in ARCHIVELOG mode with flashback enabled. Which recovery method would be most efficient in this scenario?
FLASHBACK TABLE is the most efficient method to recover a dropped table when Flashback Database is enabled. It allows point-in-time recovery of a single table without affecting other objects or requiring a full database recovery. This operation is much faster than performing complete restore and recovery procedures.
You are configuring Oracle Data Guard for a production database. Which protection mode provides synchronous log transmission and guarantees zero data loss in case of primary failure?
Maximum Protection mode ensures synchronous transmission of redo logs to the standby database and commits are blocked if the standby cannot be updated. This guarantees zero data loss (RPO = 0) but may impact performance. Maximum Availability uses asynchronous transmission with safeguards, while Maximum Performance prioritizes performance over protection.
During a database audit, you discover that multiple user sessions have excessive privileges. You need to implement the principle of least privilege. Which feature allows you to grant a subset of privileges for a limited time period?
Oracle Database Vault provides mechanisms to grant temporary privileges with automatic expiration, enabling enforcement of the principle of least privilege. Roles provide persistent groupings, proxy authentication enables delegation but not time-limited grants, and edition-based redefinition is for application versioning, not privilege management.
You are troubleshooting a performance issue in your Oracle Database 19c instance. The alert log shows excessive full table scans despite the presence of appropriate indexes. Which parameter adjustment would most likely help the optimizer make better decisions?
The optimizer_index_cost_adj parameter controls the perceived cost of index access relative to full table scans. Decreasing this value makes indexes appear more cost-effective, encouraging the optimizer to use them. The other options address different performance aspects but do not directly influence index selection decisions.
Your organization requires that all administrative activities on the Oracle Database be logged and audited for compliance purposes. Which feature provides real-time auditing of privileged user actions with minimal performance overhead?
Unified Auditing (available in Oracle Database 12c and later) provides comprehensive, real-time auditing of privileged actions with flexible audit policies and centralized audit trail management. It has lower overhead than traditional auditing and provides better filtering and reporting capabilities compared to standard database auditing.
You are managing a large Oracle Database with multiple tablespaces. A tablespace has approached its maximum size limit, and you need to extend storage capacity. Which approach is NOT a valid method to increase tablespace space?
While ASM can be used for storage management, migrating a tablespace is not a direct method to extend its capacity—it is a storage architecture change. Valid methods include adding new datafiles, resizing existing datafiles with ALTER DATABASE DATAFILE, or enabling autoextend. Migration to ASM would require additional steps and is not a straightforward capacity extension.
You need to implement a disaster recovery solution that maintains a synchronized copy of your production database in a remote location. The recovery time objective (RTO) is 4 hours and recovery point objective (RPO) is 1 hour. Which Data Guard configuration would be most appropriate?
A physical standby in Maximum Performance mode with hourly log transmission meets the stated RPO of 1 hour and RTO of 4 hours. Physical standbys provide faster recovery than logical standbys or Golden Gate, and Maximum Performance mode allows asynchronous transmission suitable for these objectives. Golden Gate and logical standbys add unnecessary complexity for this requirement.
During database maintenance, you need to move a datafile from one disk location to another without shutting down the database. Which procedure correctly accomplishes this task?
The correct procedure involves taking the datafile offline, moving the physical file at the operating system level, using ALTER DATABASE RENAME DATAFILE to update the data dictionary, and bringing the datafile back online. This allows datafile relocation without full database shutdown. The other options contain incorrect syntax or non-existent commands.
You are configuring resource management for your Oracle Database to ensure critical applications receive adequate CPU and memory resources. Which initialization parameter defines resource allocation constraints for consumer groups?
The resource_limits parameter enables resource limit enforcement at the session level in conjunction with the resource manager. When set to TRUE, it enforces limits defined in resource consumer groups such as CPU, memory, and session limits. memory_target controls total SGA/PGA size but not consumer group allocation, and db_recovery_file_dest_size controls Flash Recovery Area size.
Which parameter should you set to enable automatic undo management in Oracle Database?
The UNDO_MANAGEMENT parameter controls whether undo is managed automatically or manually; setting it to AUTO enables Automatic Undo Management (AUM).
You need to recover a tablespace that was accidentally dropped. Which recovery method would be most appropriate if you have recent RMAN backups?
RMAN can restore specific tablespaces from backups and perform point-in-time recovery to recover the dropped tablespace without recovering the entire database.
What is the primary advantage of using Oracle Data Guard with synchronous redo transmission (Maximum Protection mode)?
Maximum Protection mode ensures that redo is written synchronously to the standby before commit on the primary, guaranteeing zero data loss (RPO=0).
When using RMAN, which command would you use to create a backup of the database that includes all datafiles, control files, and server parameter file?
The BACKUP DATABASE command automatically includes all datafiles, the current control file, and the SPFILE by default in RMAN backups.
You are monitoring a database and notice that the SMON process is consuming excessive CPU. Which condition would most likely cause this behavior?
SMON (System Monitor) is responsible for cleaning up dead transactions and coalescing free space; excessive activity indicates many transactions need cleanup.
In a Data Guard environment, what is the primary purpose of the LOG_ARCHIVE_DEST_n parameter?
LOG_ARCHIVE_DEST_n parameters define archiving destinations which can include local or remote locations (standby databases), with various attributes like synchronous transmission.
Which view should you query to monitor the current status of RMAN backup jobs and their progress?
V$RMAN_BACKUP_JOB_DETAILS provides detailed information about completed and running RMAN backup jobs including status and progress metrics.
You need to implement a solution where the standby database processes redo logs while remaining available for read-only queries. Which Data Guard configuration would you implement?
Active Data Guard allows the physical standby database to apply redo logs in real-time (Managed Recovery Mode) while serving read-only queries simultaneously.
What is the purpose of the DBMS_REPAIR package in Oracle Database administration?
DBMS_REPAIR provides procedures to detect corrupt blocks, mark them, and repair logical corruption in tables and indexes using skip_corrupt_blocks and fix_corrupt_blocks.
Which initialization parameter controls the maximum number of processes that can be created in an Oracle instance?
The PROCESSES parameter specifies the maximum number of OS processes that can connect to the database; it must be set before database startup.
You are implementing a backup strategy and need to ensure that full backups are retained for compliance purposes. Which RMAN retention policy would you implement?
The CONFIGURE RETENTION POLICY command sets RMAN retention rules; REDUNDANCY specifies the number of backup copies to retain for each datafile.
When you execute RMAN RECOVER command on a physical standby database, what must be true for recovery to proceed successfully?
RMAN recovery on standby requires the database in MOUNT state; standby must have access to archived logs needed for recovery from the point of backup.
What is the primary function of the archiver (ARCn) background process in Oracle Database?
The ARCn process automatically copies completed redo log files to archive log destinations when the database is in ARCHIVELOG mode.
You need to perform a point-in-time recovery (PITR) of a single table without recovering the entire database. Which approach would you use?
Single table PITR typically requires cloning/duplicating the database to a point-in-time, extracting the table via Data Pump, and importing it into the production database.
Which SQL statement would you use to check the current size and utilization of the UNDO tablespace?
Querying DBA_DATA_FILES with the UNDOTBS1 tablespace name provides the allocated size of the undo tablespace.
In an Oracle RAC environment, which component is responsible for maintaining cache coherency across multiple instances?
GCS and GES are RAC services that manage cache coherency by coordinating block access and lock requests across multiple instances in the cluster.
What does the COMPATIBLE initialization parameter determine?
COMPATIBLE specifies the minimum database release level for backward compatibility; it determines which features and behaviors are available in the running database.
You are implementing block change tracking to improve RMAN incremental backup performance. Which file stores the change tracking data?
DB_BLOCK_CHANGE_TRACKING_FILE parameter specifies the location of the change tracking file used by RMAN to identify changed blocks since the last backup.
Which situation would require you to perform an incomplete recovery rather than complete recovery?
Incomplete recovery is necessary when archive logs are missing or corrupted, preventing the application of all redo through the current time.
In a Data Guard environment, what does the DB_UNIQUE_NAME parameter identify?
DB_UNIQUE_NAME provides a unique identifier for each database in a Data Guard setup, allowing proper configuration and distinction between primary and standby databases.
You need to restore a datafile that was accidentally deleted from disk. The database is open and you don't want to shut it down. Which steps would you follow?
To restore a deleted datafile while the database is open: take it offline with ALTER DATABASE, restore via RMAN, bring it online, and recover to apply missing transactions.
Which view would you query to identify which tablespaces contain segments that are experiencing fragmentation?
Querying DBA_FREE_SPACE shows the distribution of free space across extents; high fragmentation is indicated by many small free extents in a tablespace.
What is the maximum number of redo log groups that can be configured for a single database instance?
Oracle does not impose a hard limit on the number of redo log groups; the practical limit depends on disk space, I/O performance, and administration overhead.
You are analyzing the performance impact of enabling archivelog mode on a production database. Which aspect would NOT typically cause performance degradation?
Enabling archivelog mode enables incremental backups and block change tracking, which actually improves backup performance; the other options represent typical performance impacts.
You are managing an Oracle Database 19c instance and need to configure automatic undo management. Which initialization parameter controls the retention period for undo data?
UNDO_RETENTION specifies the minimum amount of time in seconds that Oracle retains undo information. This parameter is essential for configuring automatic undo management behavior.
What is the primary advantage of using Oracle Data Guard with synchronous redo transport mode compared to asynchronous mode?
Synchronous redo transport (SYNC) waits for acknowledgment from the standby before committing, ensuring zero data loss. Asynchronous mode prioritizes performance but may lose some committed transactions.
You need to implement a backup strategy using Recovery Manager (RMAN). Which backup type creates a copy of only the database blocks that have changed since the last full backup?
Incremental backups capture only changed blocks, reducing backup time and storage. Differential incremental backups start from the last incremental backup, while cumulative incremental backups start from the last full backup.
When configuring Oracle Flashback Database, which initialization parameter must be set to enable the flashback logs?
The DB_FLASHBACK_ENABLED parameter controls whether flashback logs are generated. Additional parameters like DB_FLASHBACK_RETENTION_TARGET specify the retention time in minutes.
You are troubleshooting a parameter file and discover that the database was started with a server parameter file (SPFILE). Which statement correctly describes the relationship between SPFILE and PFILE?
SPFILE (Server Parameter File) is a binary file that persists ALTER SYSTEM changes across database restarts. PFILE (initialization parameter file) is a text file that must be manually edited and does not persist dynamic changes.
During a database migration, you need to transport tablespaces across two Oracle Database 19c instances. What is the first step in the tablespace transport process?
The first step in tablespace transport is to set the tablespace to read-only mode and use Data Pump or traditional export to generate the transport set metadata. This ensures data consistency during transport.
You are monitoring the alert log and notice frequent 'LGWR wait on REDO copy' wait events. What does this typically indicate?
This wait event indicates that LGWR (Log Writer) is waiting for free space in the redo log files, typically because log switches cannot complete. This often results from slow archiving or inadequate redo log sizing.
What is the correct method to enable block change tracking for use with incremental backups in RMAN?
The ALTER DATABASE ENABLE BLOCK CHANGE TRACKING command enables block change tracking, which RMAN uses to identify changed blocks for incremental backups. This significantly improves incremental backup performance.
You are configuring archive log deletion policies in Data Guard. Which RMAN command correctly specifies that archived logs can be deleted after being applied to the standby database?
The CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY command ensures that archived logs are retained until applied to all standby databases before deletion. This is essential for Data Guard protection.
You need to add a new log group to your database. Which statement best describes the proper procedure?
The ALTER DATABASE ADD LOGFILE GROUP command can be executed while the database is open and in normal operational mode. The new redo log group becomes available immediately after successful creation.
When implementing Oracle GoldenGate for replication, which component is responsible for capturing database changes from the source database?
The Extract process captures data changes from the source database by reading database logs and transaction records. The Replicat process applies these changes to the target database.
You are setting up a database with Oracle Managed Files (OMF). Which initialization parameter specifies the default location for all OMF-managed datafiles?
The DB_CREATE_FILE_DEST parameter specifies the location where Oracle automatically creates OMF datafiles, log files, and control files without requiring explicit file path specification.
During performance tuning, you discover that the database has high session wait times on 'library cache lock' events. Which action would most likely resolve this issue?
Library cache lock waits typically indicate insufficient shared pool space, causing excessive object invalidation and reparsing. Increasing the shared pool reduces contention and improves performance.
What is the primary purpose of the Data Recovery Advisor (DRA) in Oracle Database 19c?
The Data Recovery Advisor analyzes failures detected by the Health Monitor and recommends manual or automated recovery procedures. It provides repair scripts through RMAN.
You are configuring a standby database using Data Guard and need to ensure redo logs are applied automatically. Which parameter controls this behavior?
The LOG_ARCHIVE_DEST_STATE_2 parameter controls whether the standby database is open for read-only access and whether redo logs are automatically applied. Setting it to 'DEFER' enables managed recovery.
When using RMAN for backup, what is the difference between a 'backup set' and an 'image copy'?
Backup sets compress and multiplex multiple files into backup pieces, while image copies are bit-for-bit duplicates of datafiles that can be directly used by Oracle without processing.
You need to create a new tablespace with specific storage characteristics. Which clause in the CREATE TABLESPACE statement defines the space allocated to a segment when initially created?
The INITIAL parameter within the STORAGE clause defines the size of the first extent allocated to a segment. Modern tablespaces typically use AUTOEXTEND and automatic extent allocation.
During database startup, you receive the error 'ORA-00205: error in identifying control file'. What is the most likely cause and appropriate resolution?
ORA-00205 indicates that Oracle cannot locate or read the control files specified in the CONTROL_FILES initialization parameter. Verify file paths, existence, and file permissions are correct.
You are implementing a compression strategy for a large table. Which compression method reduces space consumption by storing duplicate values within a single block?
Basic row compression stores duplicate values as symbols within a block's symbol table, reducing space without affecting DML performance. Advanced compression provides better ratios for read-heavy workloads.
What is the correct procedure to transport a pluggable database (PDB) from one CDB (Container Database) to another in Oracle 19c?
PDB transport involves closing the source PDB, copying its datafiles and associated files, then plugging it into the destination CDB using CREATE PLUGGABLE DATABASE ... USING DATAFILE COPY. This maintains all PDB configurations.
You are monitoring database performance and notice the KEEP pool in the buffer cache has very low hit ratio. What does this typically indicate?
A low hit ratio in the KEEP pool indicates the pool is not large enough to hold all the objects designated for it, causing necessary evictions. Increasing the KEEP pool size improves hit ratio.
When configuring Oracle Net Services, which file contains the network configuration for client connections to the database?
The tnsnames.ora file on the client defines available database services, while sqlnet.ora on the server controls connection behavior. The listener.ora file configures the Oracle Listener process.
You need to implement secure database backup with encryption. Which RMAN command enables encryption for all subsequent backups?
The CONFIGURE ENCRYPTION ALGORITHM command enables transparent backup encryption with specified cipher algorithm. This persists across RMAN sessions and applies to all subsequent backups automatically.
During application testing, you receive 'ORA-00054: resource busy' errors during index creation. What does this error indicate and how should you proceed?
ORA-00054 indicates the resource (table) is locked by another session. Use V$SESSIONS to identify the blocking session, then kill it or wait for the lock to release.
What is the primary advantage of using Active Data Guard compared to a standard read-only standby database?
Active Data Guard enables read-only queries on the standby while redo is actively being applied, providing both disaster recovery capability and reporting capacity without impacting protection.
You need to configure Data Guard to protect your production database. Which component is responsible for transmitting redo logs from the primary to standby databases?
The LGWR process transmits redo logs from primary to standby in real-time using the LOG_ARCHIVE_DEST_n parameter. Archive processes handle only archived logs after they're full, not real-time transmission.
When implementing Oracle Database Vault, what is the primary purpose of a Realm?
Realms in Database Vault define protected groups of objects (tables, views, procedures) and enforce access controls through roles and command rules. Encryption, monitoring, and backup are separate Database Vault features.
You are troubleshooting a database performance issue. You examine the AWR report and notice that the 'log file sync' wait event has the highest wait time. What does this typically indicate?
The 'log file sync' wait event indicates slow disk I/O when writing redo logs to disk, which directly impacts transaction commit times. This suggests the redo log disk subsystem needs optimization.
What is the correct method to add a new online redo log group to an Oracle Database?
The correct syntax is ALTER DATABASE ADD LOGFILE GROUP to add new online redo log groups. ALTER SYSTEM is used for parameter changes, not redo log management.
You need to implement a backup strategy that allows for point-in-time recovery. Which RMAN backup mode should you configure, and what must be enabled on the database?
Point-in-time recovery requires the database to be in ARCHIVELOG mode so redo logs are archived, allowing recovery to any point between backups. Full or incremental backups with archived redo logs enable this capability.
A developer reports that their application receives 'ORA-01555: snapshot too old' errors during a long-running query. What is the most likely cause and appropriate solution?
ORA-01555 occurs when undo retention expires before long-running queries complete. Increasing UNDO_RETENTION and the undo tablespace size allows queries to access older undo blocks needed for consistent reads.
When configuring Oracle GoldenGate for real-time data replication, which process is responsible for reading the database redo logs?
EXTRACT reads redo logs and transaction data from the source database, captures changes, and writes them to trail files. REPLICAT applies these changes to the target, while PUMP manages delivery.
You are managing storage for a large production database and want to implement Automatic Storage Management (ASM). What is a key requirement before migrating existing files to an ASM disk group?
ASM runs as a separate instance (typically ASM+SID) and requires dedicated disk devices or partitions. The database can remain online during migration, bigfile conversion is optional, and undo conversion is automatic.
While monitoring your database, you observe that the parameter STATISTICS_LEVEL is set to 'BASIC'. What is the impact of this setting on database diagnostics and performance tuning?
STATISTICS_LEVEL=BASIC limits diagnostic data collection and disables ASH and many AWR features. Setting it to 'TYPICAL' or 'ALL' enables full diagnostic capabilities. Basic level reduces overhead but limits troubleshooting ability.
You need to perform a full recovery of your database after a media failure. The backup was taken 3 days ago, and redo logs are available up to the point of failure. What is the correct recovery approach?
Complete recovery uses the backup as a starting point, then applies all archived redo logs followed by current redo logs to recover to point of failure. Flashback is for logical errors, and DUPLICATE is for cloning, not recovery.
You've reviewed all 121 questions. Take the interactive practice exam to simulate the real test environment.
▶ Start Practice Exam — Free