Oracle Database Administration II common mistakes
Oracle Database Administration II · Understand the traps and how to avoid them.
Assuming that once a fast recovery area (FRA) is configured with DB_RECOVERY_FILE_DEST, RMAN automatically performs all backups and that no explicit BACKUP commands or retention policy are needed.
Candidates conflate the FRA (a storage location for recovery files) with a backup strategy. The FRA only stores archived logs, flashback logs, control file autobackups, and any backups written to it; it does not schedule or create datafile backups on its own, and without a configured retention policy RMAN treats everything as needed and never marks files obsolete.
What to do instead
Distinguish storage from strategy: configure a retention policy (CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF N DAYS or REDUNDANCY n), schedule explicit BACKUP DATABASE commands (or use a backup strategy such as incremental with a recovery window), and verify with REPORT OBSOLETE and LIST BACKUP that the intended backups exist. Treat the FRA purely as a destination whose size must be planned against retention.
Restoring a control file or performing incomplete recovery without first placing the database in the correct state (NOMOUNT for control file restore, MOUNT for restore/recover, OPEN RESETLOGS afterward), or forgetting that a RESTORE from autobackup requires the DBID when the control file is gone.
Candidates memorize command syntax but not the required startup state for each operation. RESTORE CONTROL FILE needs NOMOUNT; RESTORE DATABASE and RECOVER DATABASE need MOUNT; and any incomplete recovery or control file restore must end with ALTER DATABASE OPEN RESETLOGS. Without the DBID, RMAN cannot find the control file autobackup.
What to do instead
Drill the state machine: NOMOUNT to restore the control file (SET DBID first if the instance has no control file), MOUNT to restore and recover datafiles, then OPEN RESETLOGS after any incomplete recovery or control file restore. Practice recovering a control file autobackup from a known DBID so the sequence becomes automatic.
Confusing Flashback Database with Flashback Table/Drop/Query and assuming Flashback Database can undo a datafile loss, or assuming it can rewind past the point where flashback logs were disabled or the retention target was exceeded.
All 'flashback' features share a name but operate at different layers. Flashback Database rewinds the whole database using flashback logs and cannot recover from a missing or corrupted datafile (that needs RMAN restore). It is also bounded by DB_FLASHBACK_RETENTION_TARGET and the guarantee that flashback logging was on and logs still exist.
What to do instead
Map each feature to its mechanism and limits: Flashback Database uses flashback logs and needs the database in ARCHIVELOG and flashback logging enabled; Flashback Table uses undo; Flashback Drop uses the recycle bin; Flashback Query uses undo. Use Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) for datafile loss, and check V$FLASHBACK_DATABASE_LOG for the actual rewind window before relying on it.
Creating a user in the CDB root with a local (non-common) name, or expecting a common user created in the root to log in to a PDB without the appropriate privileges and CONTAINER settings.
Candidates treat the CDB like a single non-CDB database. In a CDB, common users (C## prefix by default) exist in root and every PDB, while local users exist only in one PDB. Creating a local user in the root is not allowed, and common users need privileges granted commonly (or via a role) to connect to a PDB.
What to do instead
Decide user scope first: create local users inside the target PDB (ALTER SESSION SET CONTAINER = pdbname) and common users in the root with the common prefix. Grant privileges with CONTAINER=ALL when they must apply across PDBs, and test the connection with the service name of the PDB, not the CDB.
Cloning a PDB from a remote CDB or a non-CDB without first opening the source in READ ONLY, or forgetting to create/verify the common user with the required privileges and the database link used for remote cloning.
Remote cloning and non-CDB to PDB migration require the source to be in READ ONLY (or, for some methods, mounted) so the datafiles and metadata are consistent. Candidates also forget that the clone operation connects through a database link and needs CREATE PLUGGABLE DATABASE privilege plus a user with the right grants on the source.
What to do instead
For remote clone or non-CDB conversion, open the source READ ONLY, create the database link from the target CDB, and confirm the cloning user has CREATE PLUGGABLE DATABASE and the needed source privileges. Use the CREATE PLUGGABLE DATABASE ... FROM ...@dblink syntax and check the resulting PDB with ALTER PLUGGABLE DATABASE ... OPEN and the alert log if it stays MOUNTED.
Using transportable tablespaces without checking platform endianness and character set compatibility, or forgetting that the tablespace must be READ ONLY and that the metadata (Data Pump export) must be imported before the datafiles are made READ WRITE.
Transportable tablespaces move datafiles plus metadata, and cross-platform moves depend on the source and target platforms having compatible endian formats (or using RMAN CONVERT). Candidates skip the READ ONLY step, omit the Data Pump metadata export/import, or try to plug in datafiles from an incompatible character set.
What to do instead
Sequence it explicitly: make the tablespace READ ONLY, export metadata with Data Pump (TRANSPORT_TABLESPACES), copy or CONVERT the datafiles (use V$TRANSPORTABLE_PLATFORM to check endianness), import metadata with the datafiles in place, then set the tablespace READ WRITE. Verify object counts and invalid objects after the move.
Reading AWR or Statspack output and tuning by the top timed events without checking the load profile, top SQL by elapsed time versus CPU, and whether the snapshot interval actually covers the problem window.
Candidates treat the 'Top 5 Timed Events' as the answer. A high wait event can be an idle event (e.g., SQL*Net message from client) or a symptom of a small snapshot window; tuning requires correlating DB time, top SQL, and the load profile, and confirming the AWR snapshots bracket the incident.
What to do instead
Work the AWR report in order: load profile (DB time, transactions, logical reads), top foreground events excluding idle waits, top SQL by elapsed time and by CPU, then instance efficiency percentages. Confirm the snapshot range covers the incident and compare against a baseline period before changing anything.
Configuring Resource Manager plans only at the CDB level and expecting them to control PDB workloads, or forgetting to set the plan and enable it (RESOURCE_MANAGER_PLAN) and to create a pending area before changing plan directives.
In a multitenant database, CDB plans govern shares among PDBs and PDB plans govern sessions within a PDB; candidates configure one and assume the other. Plan changes also require a pending area (CREATE_PENDING_AREA, create/modify directives, SUBMIT_PENDING_AREA) and an explicit RESOURCE_MANAGER_PLAN setting to take effect.
What to do instead
Decide the scope: CDB resource plan for inter-PDB shares (with shares and utilization limits), PDB resource plan for intra-PDB consumer groups. Use DBMS_RESOURCE_MANAGER pending-area procedures, validate with VALIDATE_PENDING_AREA, submit, then set RESOURCE_MANAGER_PLAN at the appropriate container and verify with V$RSRC_PLAN.
Assuming Scheduler jobs run as the invoking user with that user's privileges regardless of how they were created, or forgetting that a job's enabled state, schedule, and window determine whether it actually fires.
Scheduler jobs have an owner, a credential (for external jobs), and a program/schedule; a job created but not enabled never runs, and jobs in a PDB are scoped to that PDB. Candidates also confuse repeating schedules with windows and expect a job to run outside its window.
What to do instead
Check the job definition end to end: DBMS_SCHEDULER.CREATE_JOB with ENABLED => TRUE, the program and schedule referenced, the correct container, and any window or resource consumer group. Verify with DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_LOG, and test the job action manually before relying on the schedule.
Relying on automated maintenance tasks (Automatic SQL Tuning, segment advisor, statistics gathering) without checking the maintenance windows and the task's resource consumer group, then being surprised that tuning advisors did not run or that statistics are stale.
Automated maintenance tasks run inside maintenance windows under the ORA$AUTOTASK consumer group; if windows are disabled, too short, or the tasks are disabled, nothing runs. Candidates also assume the SQL Tuning Advisor automatically applies its recommendations, when it only produces advice unless the task is configured to accept them.
What to do instead
Inspect DBA_AUTOTASK_WINDOW_CLIENTS, DBA_AUTOTASK_TASK, and DBA_SCHEDULER_WINDOWS to confirm windows are enabled and long enough. Check task status and history, keep statistics gathering current (or run DBMS_STATS manually for volatile tables), and remember that automatic SQL tuning reports recommendations rather than silently rewriting SQL unless configured otherwise.