Database Solutions practice questions

Worked examples for Database Solutions

1. Question 1

   A primary database in an OCI Data Guard association becomes unreachable after an unplanned outage in its region, and the operations team promotes the standby so the application can resume. Later, the original region is restored and the team wants to re-establish protection. Which characterization of the roles involved is correct?

   1. A. A failover copies the standby's data files back onto the original primary, so the original database automatically resumes the primary role once its region returns.

      Misunderstands failover as a data-restore operation onto the old primary. Failover is a role transition on the standby; the old primary is not automatically rebuilt or re-promoted when its region recovers.

   2. B. A failover promotes the standby to the primary role, and the former primary must be reinstated as a standby before the association is protected again.Correct answer

      Correct. Data Guard roles are transitional: failover makes the standby the new primary, leaving the configuration unprotected until the old primary is reinstated in the standby role and redo apply resumes.

   3. C. A failover leaves both databases in the primary role simultaneously, and applications may write to either one until an administrator chooses a survivor.

      Describes an unmanaged split-brain. Data Guard maintains exactly one primary; allowing writes to two databases would produce divergent data that redo apply could never reconcile.

   4. D. A failover is only a routine maintenance operation, so an unreachable primary must instead be repaired in place before any role change is possible.

      Confuses failover with switchover. Switchover is the planned, no-data-loss role reversal used for maintenance; failover is the operation intended precisely for an unplanned outage where the primary cannot be reached.

   Explanation

   Data Guard maintains exactly one primary and one or more standbys, and the operations that move databases between those roles are switchover (planned, coordinated, no data loss) and failover (unplanned, used when the primary is unavailable). After a failover the promoted standby is the new primary and the configuration runs unprotected until the former primary is reinstated as a standby and redo apply resumes. Failover does not push data files back to the old primary, it never leaves two writable primaries, and it is specifically the unplanned-outage operation rather than the routine maintenance one.

2. Question 2

   A company is lifting and shifting an on-premises Oracle Database 19c application to OCI. The application relies on thousands of lines of PL/SQL packages and Oracle-specific features, and the project has a hard deadline that rules out any application rewrite. A colleague proposes moving the data to MySQL HeatWave to reduce cost. How should the architect respond?

   1. A. Accept the proposal, because MySQL HeatWave can run Oracle PL/SQL packages after enabling Oracle compatibility mode.

      Invents an engine capability. MySQL HeatWave is a MySQL service and does not execute Oracle PL/SQL; there is no such compatibility mode.

   2. B. Accept the proposal, because the HeatWave analytics cluster removes the need for PL/SQL logic entirely.

      Confuses an analytics accelerator with application-logic portability. HeatWave accelerates MySQL query processing; it does not replace or translate Oracle stored procedural code.

   3. C. Reject the proposal and target an Oracle Database service such as Base Database Service or Autonomous Database, since HeatWave runs the MySQL engine and cannot host Oracle Database workloads.Correct answer

      Correct: service selection starts with the database engine. Oracle Database workloads with PL/SQL and Oracle-specific features belong on an OCI Oracle Database service, not on the managed MySQL service.

   4. D. Accept the proposal only if the database is deployed on Exadata infrastructure to preserve performance.

      Mixes two unrelated services. MySQL HeatWave is not deployed on Exadata Database Service infrastructure, and infrastructure choice cannot resolve an engine incompatibility.

   Explanation

   The first filter in OCI database service selection is which database engine the workload requires: MySQL HeatWave is a managed MySQL service with in-memory analytics acceleration and cannot run Oracle Database or its PL/SQL, so a no-rewrite Oracle migration must target an OCI Oracle Database service such as Base Database Service, Exadata Database Service, or Autonomous Database. Claims of an Oracle compatibility mode, of analytics removing the need for stored procedural code, or of deploying HeatWave on Exadata infrastructure all misdescribe the service.

3. Question 3

   An operations team currently takes only occasional on-demand backups of an OCI managed database. Management now asks for a guaranteed recovery window: any restore request covering the last N days must be satisfiable without manual intervention. What must the team change?

   1. A. Nothing — on-demand backups already provide a rolling recovery window because the service automatically ages them to match the requested number of days.

      Misconception that manual backups carry an implicit retention window. On-demand backups exist only where and when an operator created them; the service does not synthesize a rolling window from them.

   2. B. Enable automatic backups and set the retention period to cover the required number of days, so scheduled backups are taken and kept for that window.Correct answer

      Correct. A guaranteed, hands-off recovery window comes from enabling automatic backups and configuring the retention period, which controls both the schedule's coverage and how long backups are kept.

   3. C. Increase the size of the DB system's storage, since a larger data volume automatically lengthens how far back the database can be recovered.

      Confuses capacity with recovery policy. Storage size does not define a recovery window; the backup configuration and its retention period do.

   4. D. Switch the TDE master key to a customer-managed key in Vault, because key rotation history determines how far back the database can be restored.

      Conflates key management with backup retention. Key rotation controls encryption governance and has no bearing on how many days of backups are retained.

   Explanation

   A guaranteed recovery window that needs no manual action is a property of automatic backups with a configured retention period: the service takes backups on a schedule and keeps them for the retention duration, so any restore inside that window is servable. On-demand backups only exist at the moments an operator chose and carry no rolling window of their own. Storage capacity governs how much data the system holds, not how far back it can be recovered, and TDE key management governs encryption control rather than backup retention.

4. Question 4

   An architect is comparing Autonomous Database Dedicated with Autonomous Database Serverless for a regulated workload. Which statement about the dedicated deployment choice is correct?

   1. A. Dedicated provides a private database cloud on Exadata infrastructure exclusive to the customer, while Oracle continues to automate database operations.Correct answer

      Correct: the dedicated deployment gives the customer an isolated, customizable private database cloud on its own Exadata infrastructure while retaining the self-driving automation of Autonomous Database.

   2. B. Choosing dedicated turns off the autonomous features, so the customer becomes responsible for tuning, patching, and backups.

      The 'dedicated removes all automation' misconception. Dedicated is still Autonomous Database: patching, tuning, and backups remain automated; only the infrastructure tenancy and governance model change.

   3. C. Dedicated is simply serverless with a larger fixed capacity allocation and is otherwise identical in isolation.

      Treats the deployment choice as a sizing option. The essential difference is that dedicated infrastructure is exclusive to one customer, whereas serverless infrastructure is shared.

   4. D. Dedicated grants SSH access to the Exadata database servers so the customer can install monitoring agents.

      Confuses Autonomous Database Dedicated with Exadata Database Service. Dedicated Autonomous infrastructure is still Oracle-operated; host-level access belongs to the co-managed Exadata service.

   Explanation

   The dedicated deployment choice changes who the infrastructure belongs to, not whether the database is autonomous: the customer gets a private database cloud on Exadata infrastructure used by no one else, with control over policies such as software update scheduling, while Oracle keeps automating tuning, patching, and backups. Claiming automation is lost, treating dedicated as merely a bigger serverless allocation, or expecting host shell access all confuse it with either serverless sizing or the co-managed Exadata Database Service.

5. Question 5

   An ISV is migrating a legacy Oracle Database application to OCI. The application requires a third-party agent installed on the database host operating system, a custom kernel parameter, and the ability for its own DBAs to control exactly when database patches are applied. Which OCI database service should the architect select?

   1. A. Autonomous Database Serverless, because the ISV can request custom OS packages through a service request

      Misconception that Autonomous Database exposes the host OS. Autonomous Database is fully managed and provides no operating-system access, so host agents and kernel tuning are not possible regardless of support channels.

   2. B. Base Database Service, which runs Oracle Database on a customer-managed VM DB systemCorrect answer

      Correct: Base Database Service gives the customer administrative control of the database and its host, so third-party agents, OS-level tuning, and customer-scheduled patching are all possible.

   3. C. Autonomous Database Dedicated, because dedicated infrastructure grants the customer root access to the database servers

      Misconception that dedicated Exadata infrastructure turns Autonomous Database into a self-managed system. Dedicated deployment adds isolation and control over policies such as maintenance scheduling, but the databases remain Oracle-managed with no OS access.

   4. D. MySQL HeatWave with a bastion host for OS administration

      Misconception that a managed MySQL service can substitute for Oracle Database. It runs a different database engine and also does not expose the host operating system.

   Explanation

   The decisive requirements are operating-system access and customer-controlled patch timing, which are characteristics of the co-managed services rather than the fully managed ones. Base Database Service runs Oracle Database on a DB system the customer administers, satisfying host-level customization and patch scheduling. Both Autonomous deployment choices deliberately hide the OS as part of the self-driving model, and a managed MySQL service is the wrong engine entirely.

6. Question 6

   An organization plans to move a self-managed Oracle database from its own data center to OCI. A stated goal is to reduce the operational effort its DBAs spend on infrastructure-level backup and patching mechanics while retaining full control of the database software version and configuration. Which OCI database offering best matches that goal?

   1. A. Oracle Database installed by the team on a Compute instance, where the team builds and operates its own backup and patching tooling.

      Maximizes rather than reduces operational effort: with a self-installed database on Compute the customer owns every backup and patch mechanism, which is the burden the organization is trying to shed.

   2. B. Object Storage with lifecycle policies, used as the database tier so that backup and patching are handled by the storage service.

      Category error: Object Storage is a storage service and holds database backups, but it is not a database offering and cannot run or patch a database.

   3. C. Base Database Service, where OCI provides automated backup and patching capabilities for the DB system while the customer still administers the database itself.Correct answer

      Correct. Base Database Service is the managed offering that automates backup and patching mechanics at the DB system level while leaving version choice and database administration with the customer — the balance the requirement describes.

   4. D. A Block Volume backup policy applied to the boot volume of the database host, which patches the database software as part of each scheduled backup.

      Confuses volume backups with database lifecycle management. Block Volume backup policies copy volume data on a schedule; they neither produce database-consistent backups nor apply any software patch.

   Explanation

   OCI's database portfolio spans a spectrum of shared responsibility, and Base Database Service sits where the platform automates DB-system-level backup and patching operations while the customer keeps administrative control over the database and its version. Running the database yourself on a Compute instance leaves all of that mechanics with the customer, which is the opposite of the goal. Object Storage is where backups land, not a place a database runs, and Block Volume backup policies snapshot volumes on a schedule without any awareness of database consistency or software patch level.

7. Question 7

   During a migration assessment, an architect finds that an application depends on database features and administrative operations that Autonomous Database does not expose, and the customer is unwilling to refactor. Which characteristic of the workload most strongly indicates that Base Database Service, rather than Autonomous Database, is the correct target?

   1. A. The application issues a high volume of concurrent short transactions.

      High OLTP concurrency is precisely what the Autonomous Transaction Processing workload type is designed for, so it does not push the design toward a co-managed service.

   2. B. The database must be encrypted at rest and accessible only over a private endpoint.

      Misconception that security requirements force a self-managed database. Autonomous Database encrypts data at rest by default and supports private endpoints in a customer VCN.

   3. C. The database requires custom configuration at the operating-system and database-instance level that only a DBA with full administrative access can apply.Correct answer

      Correct: the boundary between Autonomous and co-managed services is administrative control. Requiring OS- and instance-level customization means Base Database Service, where the customer administers the DB system.

   4. D. The database must be automatically backed up without the customer configuring a backup destination.

      Inverts the responsibility model. Automatic backups are an Autonomous Database strength; needing them argues for Autonomous, not against it.

   Explanation

   The deciding factor between Autonomous Database and the co-managed services is how much administrative control the workload demands: Autonomous Database hides the operating system and instance-level administration as the price of full automation, so a hard requirement for that control points to Base Database Service. High transactional concurrency, encryption at rest, private-endpoint networking, and automatic backups are all things Autonomous Database already provides, so none of them justify moving to a customer-managed DB system.

8. Question 8

   A regulated workload states an **RPO of zero** — no committed transaction may be lost if the primary database fails. The architect is configuring a Data Guard association between two DB systems in different availability domains of the same region. Which configuration choice is required to meet the stated RPO, and what is the operational cost of that choice?

   1. A. Use the Maximum Performance protection mode, because asynchronous redo transport still guarantees that every committed transaction reaches the standby before the commit returns.

      Misconception that asynchronous transport is lossless. Maximum Performance acknowledges the commit on the primary without waiting for the standby, so redo still in flight at the moment of failure is lost — the RPO is non-zero and equals the transport lag.

   2. B. Use the Maximum Availability protection mode with synchronous redo transport, accepting added commit latency proportional to the round-trip time to the standby.Correct answer

      Correct. Maximum Availability uses SYNC redo transport: the primary does not acknowledge a commit until the redo is written to the standby's redo log, which yields zero data loss for committed transactions at the cost of paying the network round trip on every commit.

   3. C. Enable Active Data Guard, because opening the standby read-only forces every transaction to be applied on the standby before the primary commits.

      Confuses read-only offload with a data-protection guarantee. Active Data Guard changes what the standby can serve (read-only queries during redo apply); it does not change redo transport semantics, and RPO is still governed by the protection mode.

   4. D. Rely on automatic backups with a short backup interval, because point-in-time recovery restores the database to the exact instant of failure.

      Misconception that backups can deliver zero RPO. Recovery is bounded by the most recent archived redo safely stored; anything generated after that is lost, and the restore also imposes a long RTO.

   Explanation

   RPO in a Data Guard design is set by the redo transport mode, not by backups or by whether the standby is open for reads. Synchronous transport under Maximum Availability holds the commit until the redo is persisted on the standby, giving zero data loss for committed transactions while adding the network round trip to every commit; asynchronous transport under Maximum Performance protects commit latency but leaves a data-loss window equal to the unshipped redo. Read-only standby capability is an offload feature with no bearing on transport guarantees, and backup-based recovery can only reach the last safely stored redo, so it cannot achieve an RPO of zero.