Oracle Database Administration II (1Z0-083) — Free Practice Questions
10 free sample questions from a bank of 258, with the correct answers and explanations. No signup required — start practising right now.
1Which three are true about thresholds, metrics, and server-generated alerts? (Choose three.)
All metrics are instance related.
Cleared stateful alerts are displayed by querying DBA_ALERT_HISTORY.
A space usage management alert is automatically cleared after the underlying problem is resolved.
They are generated by SMON when a tablespace is 97% full.
Metrics are statistical counts for a specific unit.
STATISTICS_LEVEL must be set to ALL to generate alerts.
Answer: B, C, E
The short version
BCE — thresholds, metrics and stateful alerts behave as documented. Cleared stateful alerts remain queryable in DBA_ALERT_HISTORY, space-usage alerts clear automatically, and metrics are statistical counts.
Key concepts in this question
Metrics: statistical counts measuring a unit such as physical reads, redo writes, or session counts, collected by MMON for baseline and threshold evaluation.
Thresholds: boundary values set on metrics that trigger server-generated alerts when crossed, with warning and critical levels driving notification policy.
Stateful versus stateless alerts: stateful alerts persist through DBA_ALERT_HISTORY after clearing, while stateless alerts fire once per condition without lifecycle tracking.
MMON and DBA_ALERT_HISTORY: MMON evaluates metrics on schedule, records outstanding alerts in DBA_ALERT_HISTORY, and retains cleared rows for audit and trend review.
Why BCE is correct
B is correct because cleared stateful alerts are preserved in DBA_ALERT_HISTORY, which is the documented dictionary view for alert lifecycle history. C is correct because space-usage alerts are stateful and Oracle clears them automatically once the underlying tablespace pressure is resolved. E is correct because Oracle defines a metric as a statistical count for a specific unit, distinct from a threshold or an alert itself.
Why the others are wrong
A. Not all metrics are instance related, since many metrics cover database, tablespace, service, or file scope.
D. Alerts are generated by MMON evaluation, not by SMON at a fixed 97 percent full condition.
F. Alerts generate at the default TYPICAL statistics level, while ALL is unnecessary and adds overhead.
1z0-083 exam tip
Remember HISTORY means cleared: DBA_ALERT_HISTORY keeps stateful alerts after MMON clears them, and space alerts self-clear on recovery.
2Which three are true about opatchauto? (Choose three.)
It performs a shutdown and then a restart of all processes in both Oracle Grid Infrastructure and Oracle Database home during the patching process.
It must be invoked by a user with root user privileges.
Patches are applied via opatchauto.
Users must always input patch plans to opatchauto.
It requires the Oracle Grid Infrastructure and Oracle Database instances to be shut down before being invoked.
It applies patches in nonrolling mode by default.
It is used to apply interim patches to Oracle Grid Infrastructure and Oracle Database home combinations.
Answer: A, B, G
The short version
For this 1z0-083 question the correct set is A, B and G. OPatchAuto automates Grid plus RAC patching, must run as root, does stop plus patch plus start, defaults to rolling, and covers interim patches across combined homes.
Key concepts in this question
Orchestration means prechecks, stop, apply, start without manual node work. Root is needed for CRS operations and protected homes. Rolling is default, nonrolling needs a flag. Core scope is GI plus managed RAC homes together. Patch plans and shutdown state are the classic distractors in this objective.
Why ABG is correct
A holds because apply and rollback stop then start GI and database services as part of the flow. B holds because Oracle requires an OS user with root privileges for every apply, resume, rollback and version operation. G holds because the tool targets interim patches and Release Updates for GI plus RAC combos after conflict checks in those homes.
Why the others are wrong
C overstates the engine, helpers perform parts of the work rather than one binary doing all. D fails because rolling is the default with no input, so patch plans are optional. E fails because the stack must be up on the local node, not down before start. F fails because nonrolling must be explicitly requested with an option.
1Z0-083 exam tip
Eliminate F for rolling default, E for stack up, D for optional plans. Root plus auto restart plus GI plus DB combo as a trio is usually the intended set. Check the patch README for rollable status and run apply with analyze first in production.
3Application PDBs, SALES_APP1 and SALES_APP2, must be created and they must access common tables of the SALES_APP application.
Examine these steps:
1. Install the SALES_APP application, including the common tables, in the application root.
2. Install the SALES_APP application in the application root and the common tables in both the CDB root and the application root.
3. Create an application seed.
4. Install the SALES_APP application in the application seed.
5. Create the SALES_APP1 and SALES_APP2 application PDBs.
6. Sync the SALES_APP1 and SALES_APP2 application PDBs with the application root.
7. Sync the SALES_APP1 and SALES_APP2 application PDBs with the application seed.
8. Sync the application seed with the application root.
Which are the minimum required steps in the correct sequence?
3, 4, 1, 6, 8
2, 5, 6
1, 5, 6
1, 3, 5, 6, 7
1, 3, 5, 7
Answer: C
The short version
C — install the application once in the root, then create and sync the PDBs. Steps 1, 5 and 6 are the minimum path for SALES_APP1 and SALES_APP2 to share common tables.
Key concepts in this question
Application root: container holding the application definition and shared common objects that all application PDBs inherit through sync.
Application PDB: pluggable database plugged into an application root, isolated for data yet sharing versioned application code and common tables.
Application seed: optional template PDB for mass provisioning, useful at scale but not required for only two application PDBs.
Sync operation: step that applies root application changes into a PDB, aligning its data dictionary and objects with the parent version.
Why C is correct
Installing SALES_APP with its common tables in the application root establishes the single sharable definition required for inheritance. Creating SALES_APP1 and SALES_APP2 as application PDBs registers them as members of that application container. Syncing both PDBs with the root propagates the application version and common tables, completing the minimum correct sequence without seed overhead.
Why the others are wrong
A. Creating and installing into an application seed adds unnecessary steps when only two PDBs are needed.
B. Installing common tables in the CDB root breaks the application-container model and prevents proper sharing.
D. Adding a seed, syncing with the seed, and syncing the seed with the root exceeds the minimum and misorders sync.
E. Installing only in the seed without the root install leaves no root application to sync from.
1z0-083 exam tip
Think root first: install once in the application root, create the PDBs, then sync PDBs to root, and skip the seed unless mass cloning is required.
4Which two are prerequisites for using FLASHBACK TABLE? (Choose two.)
A table's constraints must be disabled before issuing the flashback.
The FLASHBACK ANY TABLE system privilege or the FLASHBACK object privilege must be granted to the user performing the flashback.
A table's indexes must be set to unusable before issuing the flashback.
Row Movement must be enabled on the table being flashed back.
The FLASHBACK ANY TABLE system privilege or the FLASHBACK object privilege must be granted to the table owner.
Answer: B, D
The short version
BD — FLASHBACK TABLE needs row movement plus a flashback privilege. The mover needs FLASHBACK ANY TABLE or object FLASHBACK privilege, and the table must allow row movement.
Key concepts in this question
FLASHBACK TABLE: row-level rewind using undo data to restore a table to an earlier SCN or timestamp without full database recovery.
Row movement: table property allowing Oracle to change ROWIDs during flashback, DDL, or partition maintenance, required for the operation to relocate rows.
FLASHBACK ANY TABLE versus object privilege: system-wide grant or table-specific FLASHBACK grant authorizing theInvoker to run the flashback.
Undo dependence: operation succeeds only while undo covering the target time remains available and no intervening DDL has altered structure.
Why BD is correct
B is correct because Oracle requires the user performing the flashback to hold FLASHBACK ANY TABLE or the object-level FLASHBACK privilege on that table. D is correct because FLASHBACK TABLE physically moves rows between blocks as it rewinds, which Oracle permits only when ROW MOVEMENT is enabled. Together the privilege check and the row-movement setting are the documented prerequisites before syntax execution.
Why the others are wrong
A. Constraints need not be disabled beforehand, since Oracle preserves them during the rewind.
C. Indexes remain usable and are maintained automatically, so setting them unusable is unnecessary.
E. The grant must belong to the user performing the flashback, not merely to the table owner.
1z0-083 exam tip
Link movement to flashback: if the question mentions FLASHBACK TABLE, immediately check ROW MOVEMENT enabled plus a FLASHBACK privilege for the actor.
5Which two are true about RMAN Multisection backups when a very large data file is divided into four sections? (Choose two.)
Each of the file sections must be processed serially.
The four sections can be created serially.
The four sections must be image copies.
The four sections can be created in parallel.
The four sections must be contained in backup sets.
Answer: B, D
The short version
BD — four RMAN sections can run serially or in parallel. Multisection parallelism depends on allocated channels, and both serial and parallel execution are valid.
Key concepts in this question
Multisection backup: RMAN feature splitting a large datafile into independent sections, each processed as a separate backup piece for throughput.
SECTION SIZE: parameter controlling section granularity, enabling a single large file to use multiple channels concurrently.
Channels: RMAN server sessions that read and write backup pieces, where channel count determines achievable parallelism.
Backup sets versus image copies: multisection output can be stored either way since 12c, including multisection image copies.
Why BD is correct
B is correct because with a single channel RMAN processes the four sections one after another, so serial creation is explicitly supported. D is correct because with four channels RMAN assigns each section to its own channel and creates them concurrently for large-file throughput. Oracle documentation confirms both modes depend only on channel allocation, not on a fixed serial or parallel mandate.
Why the others are wrong
A. Sections are not forced to be serial, since multiple channels enable concurrent processing.
C. Sections need not be image copies, because backup sets are also permitted for multisection output.
E. Sections need not be in backup sets, because image-copy multisection backups are supported since 12c.
1z0-083 exam tip
Associate SECTION SIZE with channels: one channel means serial sections, multiple channels mean parallel sections, and storage format stays flexible.
6Which three are true about the Oracle Optimizer? (Choose three.)
It obeys all hints.
It considers the filters in WHERE clauses when generating execution plans for SQL statements.
It can re-optimize execution plans after previous executions detect suboptimal plans.
It considers object statistics when generating execution plans for SQL statements.
It updates stale object statistics in the Data Dictionary.
It can only use SQL Plan Directives tied to the SQL statement for which a plan is being generated.
Answer: B, C, D
The short version
BCD — the optimizer weighs filters and statistics and can re-optimize later. It considers WHERE clause selectivity, uses object statistics, and applies automatic reoptimization when a prior plan proves suboptimal.
Key concepts in this question
Cost-based optimization: evaluation of competing execution plans using selectivity, cardinality, and cost derived from statistics and predicates.
WHERE clause filters: predicates that drive selectivity estimates, access paths, and join orders considered during plan generation.
Object statistics: table, index, column, and histogram data gathered by DBMS_STATS that inform cardinality and cost calculations.
Automatic reoptimization: adaptive behavior where the optimizer learns from execution feedback and changes the plan on subsequent runs.
Why BCD is correct
B is correct because the optimizer parses WHERE clause filters to estimate selectivity and choose among full scans, index access, and join methods. C is correct because automatic reoptimization lets the optimizer revise a plan after monitoring reveals cardinality misestimates or poor performance. D is correct because current object statistics are a primary input to cost and cardinality, directly shaping the selected plan.
Why the others are wrong
A. Hints are directives the optimizer may ignore when invalid, contradictory, or prevented by conditions.
E. The optimizer never updates stale statistics itself, since collection is done by DBMS_STATS jobs.
F. SQL Plan Directives can be shared across statements with common patterns, not tied to only one statement.
1z0-083 exam tip
Link optimizer inputs to outputs: filters plus fresh statistics feed cost decisions, and reoptimization fixes the plan after execution feedback.
7RMAN has just been connected to a target database and the recovery catalog database.
In which two cases would an automatic full resynchronization occur between this target database's control file and the RMAN recovery catalog? (Choose two.)
when control file metadata for archive log backups or image copies has been overwritten in the target database's control file due to being older than CONTROL_FILE_RECORD_KEEP_TIME
when control file metadata for data file backups or image copies has been overwritten in the target database's control file due to being older than CONTROL_FILE_RECORD_KEEP_TIME
when a new tablespace is added to a registered target database
when a backup of the current control file is created
when the target is first registered
Answer: C, E
The short version
CE — full resync happens on structural change and on first registration. Adding a tablespace alters the schema record, and initial registration forces a complete catalog load.
Key concepts in this question
Full versus partial resync: full resync rewrites all changed schema and configuration records, while partial resync updates only backup and archivelog metadata.
Recovery catalog: separate database storing RMAN metadata, persistent configuration, and history beyond control-file retention.
CONTROL_FILE_RECORD_KEEP_TIME: retention window for reusable records in the control file before they can be overwritten and lost to partial resync.
REGISTER DATABASE: first-time enrollment of a target database that populates the catalog with its full file and incarnation inventory.
Why CE is correct
C is correct because adding a tablespace is a structural schema change that requires RMAN to refresh all database-schema records through a full resynchronization. E is correct because the first REGISTER DATABASE performs an initial full synchronization to seed the catalog with datafiles, tablespaces, and incarnation data. Oracle documentation lists schema changes and initial registration as the canonical full-resync triggers.
Why the others are wrong
A. Overwritten archivelog records do not trigger automatic full resync, since catalog history already preserves them.
B. Overwritten datafile backup records likewise do not force full resync, as only schema or config changes do.
D. Backing up the current control file is routine metadata and does not alter schema requiring full sync.
1z0-083 exam tip
Memorize the two full-sync triggers: brand-new registration plus any structural schema change such as adding a tablespace or datafile.
8You plan to perform cross-platform PDB transport using XTTS.
Which two are true? (Choose two.)
A backup of the PDB must exist, taken using the BACKUP command with the TO PLATFORM clause.
The source PDB must be in MOUNT state.
The source PDB must not be an application root.
The source PDB can be in MOUNT or OPEN state.
The source and target platforms must have the same endianness.
Automatic conversion of endianness occurs.
Answer: B, E
The short version
BE — cross-platform PDB transport needs MOUNT plus same endianness. The source PDB stays mounted while backup sets move bytes between identical endian platforms.
Key concepts in this question
XTTS cross-platform transport: method moving PDBs or tablespaces across operating systems using backup sets plus metadata export.
MOUNT state: PDB state where datafiles are attached but not opened, allowing consistent backup for transport workflows.
Endianness: byte-order format of a platform, where same-endian moves avoid conversion while cross-endian needs conversion paths.
BACKUP TO PLATFORM versus FOR TRANSPORT: RMAN options preparing backup sets for destination platforms without live database copy.
Why BE is correct
B is correct because the settled key requires the source PDB to be in MOUNT state so files are closed consistently for the transport backup. E is correct because the backup-set PDB technique in this key mandates identical endianness between source and target, with no automatic endian conversion. Together MOUNT plus same-endian defines the eligible path for XTTS PDB transport.
Why the others are wrong
A. A TO PLATFORM backup is one valid preparation, not a universal must for every PDB transport variant.
C. The source can be an application root in general flows, so outright exclusion is incorrect.
D. Allowing OPEN state contradicts the MOUNT requirement enforced by this settled key.
F. Automatic endian conversion does not occur on this same-endian backup-set path.
1z0-083 exam tip
Lock MOUNT plus same endian: XTTS PDB backup transport means mounted source and matching byte order.
9Which three true about recovering tables using RMAN? (Choose three.)
RMAN can recover tables in the SYSAUX tablespace.
RMAN can recover tables owned by the SYS user.
RMAN can recover tables in the SYSTEM tablespace.
RMAN always uses an auxiliary instance.
RMAN can recover tables in a standby database.
RMAN can recover a table after a DDL operation has altered the table structure.
RMAN can recover tables owed by the SYSTEM user.
Answer: D, F, G
The short version
For this 1z0-083 question the correct set is D, F and G. RMAN table recovery always builds an auxiliary database, it is the path when DDL blocks Flashback, and only SYS schema plus SYSTEM and SYSAUX tablespaces plus standby are barred, so a SYSTEM owned table outside those spaces stays eligible.
Key concepts in this question
RECOVER TABLE means pick backup, check space, build auxiliary database, recover to UNTIL TIME or SCN or SEQUENCE, Data Pump export, then optional import with REMAP. ARCHIVELOG plus full backups of undo plus SYSTEM plus SYSAUX plus user space are needed. Flashback fails after truncate style DDL, while RMAN point in time recovery succeeds. The four bars decide most options.
Why DFG is correct
D holds because every flow creates an auxiliary database on the target host, with AUXILIARY DESTINATION or SET NEWNAME for its files. F holds because Oracle lists post DDL loss as a core purpose, since Flashback cannot rewind structural changes while RMAN can. G holds by exclusion, the ban names SYS schema, not SYSTEM user, so SYSTEM owner outside banned spaces is allowed.
Why the others are wrong
A fails because tables from SYSAUX cannot be recovered, though SYSAUX backups feed the auxiliary set. B fails because SYS schema tables cannot be recovered under any option. C fails because SYSTEM tablespace tables cannot be recovered. E fails because recovery is not supported on physical standby, with only logical standby propagation from primary allowed.
1Z0-083 exam tip
Memorize the bars as SYS schema, SYSTEM space, SYSAUX space, and standby, then treat DDL as the signal for RMAN over Flashback. If auxiliary instance appears, lean true because Data Pump plus auxiliary work is central. For SYSTEM user versus SYSTEM space, read carefully, owner alone is not a bar.
10Which operating system group is NOT needed to perform an Oracle Database installation?
OSASM
OSKMDBA
OSDBA
OSOPER
OSRACDBA
Answer: A
The short version
A — OSASM is the Grid Infrastructure group, not a database install prerequisite. Standard database installs rely on OSDBA, OSOPER, OSBACKUPDBA family groups, while OSASM serves ASM administration.
Key concepts in this question
OSDBA: primary operating-system group whose members connect with SYSDBA privilege for database administration.
OSOPER: optional group granting SYSOPER startup, shutdown, and limited operational authority without full SYSDBA power.
OSASM: ASM-specific group for SYSASM administration of Grid Infrastructure and disk groups, separate from RDBMS roles.
OSKMDBA, OSRACDBA, OSBACKUPDBA family: extended groups for TDE keystore, RAC management, backup, DG, and KM duties in modern installs.
Why A is correct
A is correct because OSASM authorizes SYSASM for Automatic Storage Management and Grid Infrastructure, not the core RDBMS software install covered by this key. A standalone database install can proceed with OSDBA and optional OSOPER plus role-specific groups, without requiring an ASM administration group. The settled key therefore distinguishes Grid and ASM administration from mandatory database OS groups.
Why the others are wrong
B. OSKMDBA supports TDE keystore administration and is part of the install group family.
C. OSDBA is the mandatory SYSDBA group needed for installation ownership and administration.
D. OSOPER provides optional but recognized SYSOPER coverage during install configuration.
E. OSRACDBA covers Real Application Clusters administration in clustered install paths.
1z0-083 exam tip
Separate ASM from RDBMS: OSASM means Grid plus SYSASM, so it is the outlier when the question asks which group a plain database install does not need.