Oracle DBA Interview Kit 2026

ORA-01078 is about the parameter file, and the next line usually says which: LRM-00109 means the file could not be opened at all. The instance searches $ORACLE_HOME/dbs for spfileSID.ora, then spfile.ora, then initSID.ora, so I check those names and permissions, and whether someone changed ORACLE_SID or the home. If the spfile is gone, the fastest options are a PFILE backup, if our scripts keep one, or RMAN: RMAN can start a dummy instance without a parameter file and restore the spfile from the control file autobackup. If the spfile exists but a bad value was set, I create a PFILE from it, fix the value and start from the PFILE, then recreate the spfile. Last resort is a minimal PFILE with db_name, control_files and memory settings.

There is no number I can give you without seeing the infrastructure. Channels only help up to what the slowest layer can absorb. With tape, it is typically one channel per drive, because two channels on one drive just fight. With disk or a backup appliance, I start around the number of independent I/O paths and measure throughput, not channel count. CPU matters too once compression or encryption is on. At 25 TB I would also use SECTION SIZE, so one huge bigfile datafile is split into sections that several channels back up in parallel, otherwise a single big file becomes a single-threaded tail at the end of the job. Then I record the measured MB per second for that platform and size the backup window from it, rather than guessing.

Zero data loss means the primary must not acknowledge a commit until the redo is safe at the standby, which means SYNC transport and a protection mode that uses it. Maximum Performance, the default, ships ASYNC, so RPO is seconds. Maximum Availability uses SYNC and gives zero data loss as long as a synchronised standby is reachable; if it is not, the primary keeps running unprotected and resynchronises later. Maximum Protection also uses SYNC but shuts the primary down rather than run unprotected. The cost is commit latency: every commit now pays the network round trip, so I would measure the latency to the standby site before promising anything, and test with real workload. So it is not a DBA flag; it is a business decision about latency and availability, and I would present it that way.

The same blocks are being modified from both instances, so they are shipped back and forth over the interconnect. 'gc buffer busy' means a session waits for a block that is already being transferred or held for another session. Typical hot spots: the right-hand leaf of an index on a sequence or timestamp, a small table updated by every transaction, a sequence with a small cache, segment header blocks. The AWR 'Segments by Global Cache Buffer Busy' section, or GV$SEGMENT_STATISTICS, names the objects. The fix is rarely hardware. Partition the workload with services so that the job runs on one instance, or partition the data so each node works on its own range; increase sequence caches; consider hash-partitioned or reverse-key indexes for right-growing keys. Then the interconnect only carries what genuinely must be shared.

About latency, yes; 2 ms for a single-block read is healthy. But 45% of DB time spent on single-block reads means the database is doing an enormous number of them, and that is a SQL question, not a storage question. The arithmetic makes it concrete: 45% of an hour of DB time is about 1,600 seconds; at 2 ms each, that is roughly 800,000 single-block reads in the hour. So I go to 'SQL ordered by Physical Reads' and 'by User I/O Wait Time' in AWR, and look at the plans. Typical culprits: index range scans on poorly selective indexes followed by table access by rowid, nested loops driving millions of lookups because of a misestimate, or a buffer cache too small for the working set. The fix is usually fewer reads, not faster reads.

The binaries are at the new RU but the dictionary is still at the old one. The new code calls package versions, views or internal structures that the SQL half of the patch was supposed to install, so you see invalid Oracle-supplied objects, errors from packages whose specification and code no longer match, or worse, internal errors in features the RU touched. Detection is simple: compare opatch lspatches with DBA_REGISTRY_SQLPATCH, and the latest RU will be missing or not SUCCESS; the alert log may also warn. The fix is to run datapatch -verbose with the database open, and in a CDB with all PDBs open, then recompile invalid objects and verify. It usually takes minutes. Then prevention: datapatch is a mandatory step in the runbook and in automation, and the post-patch check compares binary and SQL patch levels automatically.

A remote hot clone. On the target CDB I create a database link to the source CDB using a user with CREATE PLUGGABLE DATABASE privilege, then CREATE PLUGGABLE DATABASE ... FROM prod_pdb@link. Because the source CDB is in ARCHIVELOG mode with local undo, the source stays open read-write; Oracle copies the files and then applies redo to make the copy consistent. The cost to production is real: two terabytes of reads on the source storage and network traffic, plus redo generation continuing meanwhile. So I throttle with the PARALLEL clause, run it off-peak if possible, and watch latency on the source. Equally important: the clone goes into a non-production CDB, never into the production CDB, and afterwards it needs data masking before QA touches it.

Storage: a 384-dimension FLOAT32 vector is about 1.5 KB, so 20 million rows add roughly 30 GB of vector data before indexes, more for larger models; I plan the tablespace and backup growth accordingly. Memory: an HNSW index lives in the vector pool in the SGA, sized by VECTOR_MEMORY_SIZE, so it must be budgeted with the rest of the SGA; Oracle provides an advisor to estimate it. Index choice: HNSW for low latency if memory allows, IVF when the data is too big for memory. Embedding generation: inside the database with an imported ONNX model, which keeps data local, or from an external provider, which means credentials, network access control lists and a data-egress conversation with security. Then refresh: new and changed products must be re-embedded, so a pipeline and its load on production. And a test of accuracy versus speed with real queries.

Start on the host. emctl status agent tells me whether the agent is running, the OMS URL it uses, the last successful upload and how many files are waiting. If it is down, I check the agent logs, emagent.nohup and gcagent.log under the agent instance's sysman/log directory, and the obvious causes: a full file system under the agent home, an expired password or changed permissions, a host reboot without agent autostart. If it is running but cannot upload, emctl pingOMS and emctl upload agent show the error: firewall changes on the upload port, DNS, a proxy, or certificate problems after an OMS change, fixed with emctl secure agent. A host clock far out of sync also causes trouble. In the console, a 'blocked' agent after a host restore or clone needs a resync from the OMS. Once fixed, I confirm the backlog uploads and that availability history shows the gap correctly.

Database prep on the source: ENABLE_GOLDENGATE_REPLICATION, ARCHIVELOG and force logging, minimal supplemental logging, a GoldenGate admin user with the required privileges, a Streams pool. Target: a GoldenGate user with apply privileges. In the deployments: add credentials to the credential store and log in with DBLOGIN. On the source, ADD SCHEMATRANDATA for the schema, ADD EXTRACT as integrated, REGISTER it with the database for that PDB, ADD EXTTRAIL, and write the parameter file with the TABLE statements. Create a distribution path from the source Distribution Service to the target Receiver Service. On the target, ADD CHECKPOINTTABLE and ADD REPLICAT, Parallel or Integrated, reading the remote trail, with MAP statements. Before starting Replicat, instantiate the target with a consistent copy of the data, then start Replicat from the matching SCN. Finally verify with INFO ALL, LAG and STATS, and a row-count comparison.

First the plan: an offloaded full scan shows as TABLE ACCESS STORAGE FULL with storage() predicates, and the session waits on 'cell smart table scan' rather than 'cell multiblock physical read'. Then the numbers: V$SQL has IO_CELL_OFFLOAD_ELIGIBLE_BYTES and IO_INTERCONNECT_BYTES, and session statistics show bytes eligible for predicate offload versus bytes returned by Smart Scan. If eligible bytes are zero, there was no Smart Scan. Common reasons: the plan uses an index range scan instead of a full scan; the scan was done through the buffer cache, because Smart Scan needs direct path reads, which a serial scan of a smallish table may not use; the predicate uses something that cannot be offloaded; or many rows are chained or migrated. Sometimes the fix is removing an index the optimizer prefers wrongly, or running the report in parallel. A SQL Monitor report shows the cell offload efficiency per plan line.

Template: the business and its criticality, the database landscape in numbers, the architecture, your responsibilities versus the team's, tools and processes, and one improvement you made. Sample: 'The client is a payments company; downtime costs money every minute. We support about 80 databases: 19c on Linux, 12 in production, two RAC clusters for the transaction systems, Data Guard to a DR data centre 600 km away, and 3 PDB-based CDBs for internal applications. We work 24x7 in shifts with ITIL change and incident processes and use Enterprise Manager for monitoring. My role is L2: I take P1 and P2 incidents, do RU patching on non-production and assist on production, run quarterly switchover drills, and own the backup reports. One improvement: I noticed our archive log deletion was not standby-aware, which caused a gap twice; I changed the deletion policy and we have had no gaps since.'

Lost blocks mean Cache Fusion messages sent over the interconnect never arrived, and the instance had to resend after a timeout, which shows up as 'gc cr block lost' and 'gc current block lost' waits. Most of the time it is not a broken cable; it is the operating system dropping UDP packets. I check netstat -su on every node for receive buffer errors and packet reassembly failures, and the interface counters with ip -s link for drops and errors. Then the usual suspects: socket buffer limits, net.core.rmem_max and rmem_default, too small for the traffic; an MTU mismatch, for example jumbo frames on the hosts but not on a switch port; a bond running in an inappropriate mode; a NIC driver or firmware issue; or CPU starvation so packets are not drained. I confirm the database really uses the private interface through GV$CLUSTER_INTERCONNECTS.

Zero transport lag means the redo is arriving; the standby simply cannot apply it as fast as the primary generates it during the batch window. So it is a standby capacity problem, not a network problem. I check V$DATAGUARD_STATS for both lags and V$RECOVERY_PROGRESS for the active apply rate, then V$DATAGUARD_PROCESS to see whether the recovery process is working or waiting. Classic causes: standby storage that is slower than the primary's because someone sized it for 'it just receives'; standby redo logs missing or the wrong size, so real-time apply is not happening; too little apply parallelism; or, on Active Data Guard, heavy reporting queries competing for the same I/O. Redo generation itself is worth checking too: an unnecessary full reload every night may be the real issue. The data is safe, but failover at 1 AM would take hours.

Block change tracking. Without it, a level 1 has to read every block of every datafile to find the changed ones, so a small incremental costs as much I/O as a full scan. Either tracking has been disabled, for example the tracking file was lost or someone ran a restore or moved files and did not re-enable it, or tracking is on but RMAN cannot use it for this backup. The tracking file keeps a limited number of bitmaps, eight by default, so if the backup's parent is older than that, which happens with cumulative level 1s long after the last level 0, RMAN silently falls back to scanning everything. No error, just a slow job. I confirm it from evidence rather than guessing, then fix it and take a fresh level 0 so the bitmaps are useful again.