Database practice questions

A Public Load Balancer fronting the standby database listener

Why it fails: Redo transport between regions requires the primary and standby to reach each other over a routable path, typically a remote peering connection or VPN, not a public load balancer. It is tempting because load balancers front database listeners, but they do not carry Data Guard redo traffic across regions.

Enabling NAT gateways in both regions to route private database redo packets

Why it fails: Network Address Translation gateways manipulate IP packet headers to translate private addresses to public IPs for outbound internet access, but cross-region Oracle Data Guard redo transport requires direct routable private IP connectivity via Remote Peering Connections across Virtual Cloud Networks. This approach appears plausible because administrators frequently deploy NAT gateways to facilitate secure outbound internet connectivity for database patches or software updates. It would represent the correct choice if the primary and standby database instances needed to communicate with external internet endpoints or third-party APIs rather than establishing direct cross-region database replication channels.

Installing an IPSec VPN appliance directly on the database node operating systems

Why it fails: Routing cross-region Oracle Data Guard redo transport through manually installed host-level IPSec VPN appliances is unnecessary and operationally flawed, as Oracle Cloud Infrastructure manages cross-region VCN peering natively through Remote Peering Connections without modifying guest operating systems. Administrators are often tempted by this approach because host-based IPsec tunnels provide encrypted transport across untrusted public networks, making them the standard choice for connecting on-premises datacenters to cloud environments or securing traffic over unmanaged third-party networks.

Autonomous Patching Daemon (autopatchd)

Why it fails: Oracle does not provide a utility named the Autonomous Patching Daemon for managing Oracle Base Database System lifecycles. This choice is confusing because it mimics the nomenclature of the automated patching features found within Autonomous Database environments. While Autonomous Database services utilise internal orchestration to apply patches without user intervention, those specific background processes are not exposed or configurable as a standalone tool for manual Base Database System patching, which instead requires the OCI CLI or the Database Console to trigger fleet-wide maintenance operations.

Oracle Enterprise Manager Cloud Control Cloud Agent only

Why it fails: While OEM can manage patches, it is not the native OCI cloud control plane tool.

YUM Package Manager

Why it fails: YUM manages individual software packages at the operating system level and lacks the orchestration capabilities required to perform the complex, multi-step workflow of patching an Oracle Database home. While YUM is the standard tool for updating system-level libraries and dependencies within the Linux environment, it does not interact with the Oracle Database lifecycle management APIs. Administrators use YUM to maintain the underlying OS kernel and base packages, but it cannot automate the database-specific pre-checks, binary switching, and post-patching validation required for Oracle Base Database Systems.

Maximum Performance

Why it fails: Maximum Performance fails to satisfy the stated requirement because it operates asynchronously, meaning transaction commit confirmation occurs on the primary database before redo data is written to the standby disk, which risks data loss during an abrupt failure. This mode is tempting because it prioritises throughput by completely eliminating redo transport latency overhead on the primary database, making it the correct choice when database performance is paramount and minor data loss during an unexpected outage is acceptable.

Maximum Protection

Why it fails: Maximum Protection enforces zero data loss by halting primary database transactions if redo data cannot be written to at least one standby destination, which violates the requirement to minimise performance impact on the primary system. This mode is tempting because it provides the highest level of data availability and is the correct choice only in strict regulatory or financial environments where zero data loss takes absolute precedence over primary database uptime and transactional performance.

Maximum Throughput

Why it fails: Maximum Throughput is tempting because it prioritises low overhead on the primary database by not waiting for redo log transmission confirmation, making it ideal for scenarios where application performance outweighs replication guarantees and data loss is acceptable. However, it fails this specific scenario because it does not ensure zero data loss, as transactions can be committed on the primary before redo is safely written to a standby database, violating the requirement to prevent any loss of committed data during a failure.

OCI File Storage Service (FSS) NFS mount

Why it fails: FSS provides an NFS mount, but manual RMAN staging for external targets uses Object Storage via the DBMS_CLOUD package or Swift/S3 credentials. FSS is for shared file access across compute instances, not the documented RMAN staging path.

OCI Block Volume service attached via iSCSI

Why it fails: Block Volume via iSCSI presents raw block storage to the DB system, not an object-storage endpoint RMAN can address for external backups. Object Storage is the staging area; Block Volume suits database data files or manual disk-level operations instead.

Local NVMe scratch disks

Why it fails: Local NVMe scratch disks fail because they provide ephemeral node-local block storage that does not persist beyond the lifecycle of the specific database compute instance, rendering them useless as a persistent external staging area for cross-system backups or manual migration. They are tempting because they offer ultra-high input/output operations per second with minimal latency, making them the ideal choice for high-performance database tempfiles,redo log staging, or demanding transient caching workloads where temporary scratch space is required directly on the compute node.

Oracle Enterprise Manager Tuning Pack

Why it fails: Oracle Enterprise Manager Tuning Pack requires manual DBA intervention to analyse SQL performance reports such as SQL Access Advisor and Automatic Workload Repository, whereas the question specifies fully autonomous self-tuning capabilities without human intervention. This tool is tempting because it provides comprehensive performance diagnosis and tuning recommendations for standard Oracle Database deployments. It would be the correct choice when managing on-premises or non-autonomous cloud databases where database administrators retain complete control over executing recommended SQL profile creations and index implementations.

Database Resource Manager

Why it fails: Database Resource Manager manages CPU, memory, and parallel execution bandwidth across consumer groups, making it tempting if you are trying to control workload prioritisation and prevent runaway queries from starving critical applications. However, it fails in this scenario because it allocates hardware resources rather than performing automatic SQL tuning, index creation, or execution plan optimisation.

Automatic Storage Management (ASM)

Why it fails: Automatic Storage Management (ASM) is tempting because it automates complex database administration tasks, managing disk groups, striping, and mirroring to optimise storage I/O and simplify volume management. It would be the correct choice when configuring automated disk volume allocation and redundancy for database file storage across multiple physical drives. However, it fails this scenario because it operates strictly at the storage and disk layout layer, whereas SQL tuning, index creation, and execution plan optimisation require the database query optimiser and SQL tuning advisor engines to process application workloads.

You must manually configure Oracle Net listener files and run 'DGMGRL' commands from the OS command line.

Why it fails: Autonomous infrastructure abstracts OS and DGMGRL management; setup is fully automated via OCI control planes.

You must back up the database to Object Storage and restore it manually as a standby in the target region.

Why it fails: Autonomous Database on Dedicated Infrastructure automates Data Guard configuration through the Oracle Cloud Infrastructure control plane, which handles standby provisioning and synchronisation without manual intervention. Manually restoring from Object Storage backups is a legacy approach used for cross-region migration or disaster recovery when Data Guard is not enabled. This method fails to establish the continuous, automated replication required for a managed Data Guard association, as it lacks the necessary background processes to maintain the standby database state in real-time.

Data Guard is not supported on Autonomous Database Dedicated; you must use GoldenGate instead.

Why it fails: Oracle Autonomous Database on Dedicated Infrastructure fully supports native Data Guard associations for disaster recovery, making this claim factually incorrect. Oracle GoldenGate is instead designed for real-time logical data replication, heterogeneous migrations, or scenarios requiring selective data distribution across independent databases. While GoldenGate provides advanced transactional streaming capabilities, it is not required nor utilised for managing high-availability physical standby associations within the Autonomous Database infrastructure.

Direct SSH tunneling through the database control plane

Why it fails: Autonomous Database infrastructure restricts management access to Oracle-managed control planes, meaning administrators cannot establish direct SSH tunnels into the database host. This approach appears attractive because traditional on-premises database deployments frequently rely on SSH port forwarding through a jump host to secure remote management traffic. However, cloud-managed database services abstract the underlying operating system layer, so secure application connectivity requires mutual TLS (mTLS) via downloaded credentials or Oracle Cloud Infrastructure (OCI) Vault integration rather than direct operating system access.

Unencrypted Telnet connection over port 1521

Why it fails: Telnet transmits all data in plaintext over port 1521 without any cryptographic protection, failing to satisfy the mandatory secure connection requirements for accessing Oracle Autonomous Database from an external on-premises environment. Administrators historically used Telnet for legacy remote terminal management and troubleshooting plain text network services on local area networks, where encrypting the underlying transport layer was unnecessary because the physical network was assumed to be entirely trusted.

Anonymous FTP file transfer into the database home

Why it fails: Anonymous FTP relies on insecure, unencrypted file transfers over plaintext ports, completely failing to satisfy the mutual TLS (mTLS) or secure wallet-based connection requirements mandated for Autonomous Database. Administrators might mistakenly consider FTP when migrating flat files or bulk datadumps between legacy servers and cloud storage repositories. Such a mechanism would be appropriate only within isolated, non-production environments for transferring public, non-sensitive datasets where cryptographic confidentiality and identity verification are entirely unnecessary.

The database files are permanently deleted from the original primary server.

Why it fails: Data Guard switchover maintains high availability by reversing roles between primary and standby databases, ensuring that data remains intact on both servers for potential future failover. Deleting database files occurs only during a decommissioning process or when removing a standby database instance to reclaim storage resources. This option incorrectly describes a destructive maintenance task, whereas a switchover requires the original primary to remain fully functional as the new standby to preserve the integrity of the Data Guard configuration.

The Virtual Cloud Network (VCN) peering connection is automatically deleted.

Why it fails: VCN peering connections remain entirely independent of database role transitions, as they function at the network infrastructure layer rather than the database control plane. This option appears attractive because administrators often manage network connectivity when architecting disaster recovery solutions across regions. In scenarios involving the decommissioning of cross-region standby infrastructure or the permanent removal of a remote site, manual deletion of peering connections becomes a necessary administrative task to clean up network resources, but it plays no role in a standard switchover.

OCI NoSQL Database

Why it fails: OCI NoSQL Database provides a managed, distributed key-value store rather than a relational database management system, meaning it lacks the Oracle Database engine required to host a traditional relational schema. It serves developers building high-throughput, low-latency applications that require flexible data models and horizontal scaling. You would select this service when your architecture demands a non-relational, schemaless storage solution, but it fails to provide the root-level operating system access or the Oracle Database software stack specified in the requirements.

Exadata Cloud Service with Autonomous Container Databases

Why it fails: Autonomous Container Databases restrict root-level operating system access to ensure the automation of patching, backups, and tuning remains functional. While this service provides high-performance Exadata infrastructure for demanding enterprise workloads requiring automated lifecycle management, it fails the requirement for full administrative control over the underlying server. You would select this option when you need to offload database administration tasks to Oracle’s managed automation rather than maintaining manual configuration of the operating system and database environment.

Oracle Autonomous Database

Why it fails: Autonomous Database is a fully managed service that abstracts the underlying infrastructure, meaning Oracle manages the operating system and patching cycles, which prevents you from obtaining root access. It is designed for organisations seeking to offload database administration tasks like tuning, security patching, and backups to Oracle’s automation. This service is the correct choice when you require a serverless, self-driving environment that minimises manual intervention for standard transactional or analytical workloads.

Exadata.X9M.8

Why it fails: Exadata X9M shapes follow a format such as Exadata.X9M.8 for database servers and storage servers.

BM.GPU.DenseIO2.8

Why it fails: Graphics processing unit dense input/output shapes are designed for massively parallel computing tasks such as machine learning and graphics rendering, meaning they lack the dedicated database storage servers and core architectures required to run Exadata Database Service on Dedicated Infrastructure. Although this shape offers high input/output performance and local Non-Volatile Memory Express storage that can appeal to administrators seeking fast data access, it would be the correct choice when deploying standalone virtual machine instances that require hardware acceleration for deep learning models or visual computing workloads rather than Oracle Database management.

VM.Standard2.4

Why it fails: Dedicated Exadata infrastructure requires specialised database shapes rather than general-purpose compute instances. This option represents a standard virtual machine shape used for standard workloads on general compute instances, making it completely incompatible with the storage servers and InfiniBand or RoCE networking architecture intrinsic to Exadata Database Service. It often appears tempting because developers frequently select standard VM shapes for routine application tiers, and it would indeed be the correct choice if you were deploying a standard Oracle Database on single-node Compute instances rather than provisioning an engineered Exadata system.

30 days to 365 days

Why it fails: Automated backups on Oracle Base Database Service support retention from 7 up to 60 days, so 30 to 365 exceeds the native maximum. Longer retention is tempting for compliance, but it requires manual backups or copying to object storage instead.

1 day to 14 days

Why it fails: Oracle Base Database Service supports automated backup retention from 7 up to 60 days, so a 1-day minimum falls below the native floor. The 1–14 day range is tempting because it mirrors the shorter retention windows offered by some other managed database backup services, but it does not match this platform's supported span.

1 hour to 24 hours

Why it fails: Automated backups for Oracle Base Database Service require a retention period between 7 and 60 days, rendering the 1-hour to 24-hour range technically invalid for this configuration. This option mimics the retention windows found in short-term recovery strategies for transient data or specific log-based snapshots. While such granular intervals exist for high-frequency transaction log archiving or temporary staging environments, they do not align with the mandatory long-term retention policies enforced by the Oracle Cloud Infrastructure automated backup service.

Automatic zero-downtime shard migration across tenants

Why it fails: Sharding and cross-tenant migrations are managed at the Oracle Global Data Base architecture level rather than within a single Oracle Base Database Service virtual machine instance, making this feature unsupported for individual VM deployments. This feature is tempting because database administrators often need to scale out globally distributed transactional applications across multiple cloud tenants to balance loads and maintain high availability, which would make cross-tenant shard migration the correct choice in complex enterprise distributed database architectures spanning multiple administrative domains.

Automatic scaling of physical server hardware racks

Why it fails: Oracle Base Database Service runs on predefined virtual machine shapes rather than Exadata infrastructure, meaning physical hardware rack provisioning is managed entirely by Oracle and inaccessible to end users. This choice appears plausible because automatic hardware scaling is a core capability of Exadata Database Service on Dedicated Infrastructure, where resource pools scale dynamically across physical database server racks. Therefore, it represents a valid mechanism when managing Exadata hardware architectures, but it is fundamentally inapplicable to virtual machine deployments based on standard compute shapes.

Unencrypted plain text TCP/IP sockets on port 1521

Why it fails: Autonomous Database enforces strict encryption in transit, meaning unencrypted plain text TCP/IP sockets on port 1521 fail to meet the mandatory security requirements specified in the question stem. Nevertheless, this protocol appears tempting because standard Oracle Database deployments traditionally rely on unencrypted port 1521 for legacy database connectivity. Such a configuration would be the correct choice only when establishing connections within an isolated, trusted internal network environment that explicitly does not mandate TLS encryption for database traffic.

Direct unauthenticated HTTP REST calls without authorization headers

Why it fails: Autonomous Database rejects unauthenticated HTTP REST calls because all ingress traffic requires cryptographic validation through TLS certificates and mandatory authorization headers such as Oracle REST Data Services tokens or database credentials. This approach appears attractive when attempting rapid prototyping or public web hook integration where quick endpoint testing is needed without managing credential lifecycles. Such a mechanism would be correct in low-security, public-facing testing environments that deliberately expose non-sensitive static assets over the web without backend database dependencies.

Direct SSH tunneling through port 22 without database credentials

Why it fails: Direct SSH access on port 22 is disabled by default on Oracle Cloud Infrastructure Autonomous Database instances because database traffic must traverse the mTLS wallet or private endpoint architectures rather than generic operating-system-level forwarding. This approach appears plausible because administrators frequently rely on standard SSH tunnels to reach managed virtual machines for basic troubleshooting or administrative tasks. It would be the correct choice when establishing a direct shell session to a standard compute instance running a self-managed database where root OS access is required.

Manually configure custom standby redo log files using SQL*Plus.

Why it fails: Autonomous databases fully automate database maintenance, including all physical storage structures and redo log management, meaning manual configuration of standby redo logs via SQL*Plus is restricted. Standby redo log files are normally required when setting up traditional Oracle Data Guard environments to facilitate fast redo transport and apply operations on physical standby databases. However, because Oracle Cloud Infrastructure manages high availability and Data Guard integration automatically through the console or APIs, manual low-level modifications to redo architectures are neither exposed nor permitted.

Directly access the database server via SSH using root privileges.

Why it fails: Oracle fully manages Autonomous Transaction Processing instances on dedicated infrastructure where direct operating system access and root privileges are deliberately restricted to prevent manual interference with automated patching and maintenance routines. This administrative restriction appears tempting because direct Secure Shell access is the standard troubleshooting method for traditional on-premises databases or user-managed Oracle Database Cloud Service deployments where administrators must personally configure storage mounts, modify kernel parameters, and manually apply operating system patches.

Modify the underlying Linux operating system kernel parameters.

Why it fails: Autonomous Database operates as a fully managed cloud service where Oracle abstracts all direct infrastructure management, meaning customers cannot access the host operating system to alter kernel parameters. Administrators often assume they retain low-level administrative control because traditional on-premises database deployments routinely require OS-level tuning for performance optimisation. Such low-level interventions would be relevant if you were managing a custom database installation on Oracle Compute Instances, where full root access permits direct kernel configuration.

Automatic deletion of backup files immediately after database shutdown

Why it fails: Oracle Cloud Infrastructure database backup policies retain backups based on retention periods or explicit manual deletion rather than purging files automatically upon a database shutdown event. This distractor sounds tempting because DBAs frequently need to automate housekeeping tasks around maintenance windows or lifecycle events to control storage costs. In a different administrative scenario, scripting local OS-level cleanups of temporary dump files or archived logs triggered by shutdown hooks might be necessary, but cloud-managed automated backups operate independently of the database instance lifecycle to ensure data durability and point-in-time recovery capabilities remain intact.

Direct replication of backup files to local customer tape drives

Why it fails: Oracle Base Database Service manages automated and on-demand backups exclusively within managed cloud storage infrastructure such as Oracle Cloud Infrastructure Object Storage, lacking any direct architectural mechanism to stream or replicate backup files to on-premises customer tape drives. This distractor appears plausible because enterprises frequently utilise local tape libraries for long-term archival compliance and off-site disaster recovery strategies. Such a configuration would represent a valid requirement if the service natively supported physical media integration or external storage target mounts, whereas cloud-native database architectures deliberately abstract underlying backup media to maintain automated retention policies and secure cloud-based recovery points.

Up to 7 days only

Why it fails: Oracle Base Database Service automated backups support retention selections well beyond seven days, configurable up to a much longer maximum in the console. The option tempts because seven days is a common default elsewhere, but the retention slider here permits longer periods, so capping at seven misstates the available range.

Real-time streaming backups with no retention limits

Why it fails: Automated backups are scheduled full and incremental copies with a configurable retention window, not continuous real-time streaming, and retention limits always apply. The option tempts because continuous replication exists in Data Guard, but that is a separate feature; here the console offers discrete retention choices rather than unlimited streaming.

Exactly 365 days fixed

Why it fails: Retention for automated backups is a selectable range rather than a single fixed value, so pinning it to exactly 365 days is wrong. The option tempts because one year sounds like a plausible maximum, but the console presents multiple retention options, and the default is not locked to 365 days.

Individual DB System node

Why it fails: Exadata is provisioned as an entire infrastructure unit (quarter, half, or full rack) rather than individual DB nodes.

Autonomous Data Warehouse instance

Why it fails: An Autonomous Data Warehouse instance represents a pre-configured database deployment rather than the underlying physical compute and storage capacity required to build an Exadata infrastructure. This option is tempting because it involves provisioning an Oracle database resource, which is the ultimate goal of the workflow. It would be the correct choice when you want to deploy a fully managed, serverless analytical database directly on shared Exadata infrastructure without manually managing virtual machines or cluster nodes.

Autonomous Container Database

Why it fails: Provisioning an Autonomous Container Database requires underlying compute and storage resources to already exist, making it invalid as the initial step when setting up infrastructure from scratch. Administrators are tempted to select this option because it is the fundamental construct used to organise Autonomous Databases and manage resource allocation. It would be the correct choice when you have already provisioned your Exadata infrastructure and are now ready to create container databases to host specific Autonomous workloads.

Custom RMAN compression scripts written in shell script format executed via cron.

Why it fails: Managing backups through custom shell scripts and cron jobs bypasses the native OCI Database management plane, meaning the Oracle Cloud infrastructure cannot track the backup lifecycle, generate automated retention policies, or integrate with OCI Object Storage lifecycle rules. This approach appears tempting because experienced database administrators frequently rely on cron and custom shell scripting to automate routine Recovery Manager operations in traditional on-premises Oracle environments, making it the standard method when migrating custom enterprise automation frameworks directly to cloud infrastructure.

Manual physical block-level checksum encryption algorithms via direct OS kernel edits.

Why it fails: Block checksum encryption algorithms cannot be altered via manual OS kernel edits for managed backups.

Data Pump (impdp)

Why it fails: Data Pump moves logical schema objects between databases; it cannot apply incremental backups or archived redo logs to roll a database forward. It would be correct for exporting and importing tables, schemas or tablespaces during migration, not for media recovery to a point in time.

OCI Block Storage Volume Snapshots

Why it fails: Block volume snapshots capture storage state at a layer beneath the database, so they cannot replay archived redo logs to a chosen timestamp. They suit whole-volume rollback after corruption or accidental deletion, not fine-grained database point-in-time recovery.

SQL*Loader

Why it fails: SQL*Loader is designed exclusively for bulk-loading external data files into database tables rather than performing database recovery operations, making it completely useless for applying archived redo logs or restoring a database to a point in time. It is tempting because DBAs frequently use it for manual data migration and ingestion tasks at the OS level, meaning it would be the correct choice if the objective were to import flat-file records into a specific schema rather than restore system state.

Terminate the current DB system and provision a new one with the larger shape, then restore from a manual backup.

Why it fails: Scaling an Oracle Base Database System supports in-place shape modification, which allows you to increase OCPUs without data migration or service recreation. Terminating the system introduces unnecessary downtime and risks data loss during the restore process. This approach is only required when migrating between incompatible architectures or moving databases across different cloud regions or compartments where direct in-place resizing is technically unsupported. By avoiding manual restoration, you maintain continuous database availability while meeting the increased performance demands of your month-end processing spike.

Stop the database instance via SQL*Plus and run the 'alter system modify shape' command.

Why it fails: Scaling the compute shape of an Oracle Base Database System is an infrastructure-level operation managed exclusively through the Oracle Cloud Infrastructure Console or API, rather than via SQL*Plus commands. While SQL*Plus is the standard interface for managing database-level parameters and instance states, the `ALTER SYSTEM` command lacks the capability to modify underlying virtual machine hardware resources. This option is tempting because administrators frequently use SQL*Plus to perform routine instance maintenance, but it remains limited to database-level configuration changes rather than infrastructure scaling.

Attach an additional block storage volume with higher performance tiers to absorb the extra compute load.

Why it fails: Adding block storage volumes fails to address the requirement because it modifies I/O throughput and storage capacity rather than increasing the available CPU cycles needed for intensive processing tasks. While this action is tempting because it improves disk performance for database operations, it is intended for scenarios where the workload is constrained by storage latency or requires additional space for data files. Scaling compute shapes is the only mechanism that directly increases the OCPU count to handle the computational demands of month-end processing.