Oracle Database Administration II (1Z0-083) practice questions

Exam overview 1Z0-083

Domain coverage 8 domains

• Backup and Recovery (20%)
• Performance and Tuning (18%)
• Multitenant Architecture (18%)
• Patching and Upgrades (12%)
• Application PDBs (10%)
• Multitenant Security (8%)
• Flashback Technologies (8%)
• Database Duplication (6%)

Sample questions 14 of 139 shown

1. Q1

   Which three tasks can be automatically performed by the Automatic Data Optimization feature of Information Lifecycle Management (ILM)?

   1. Tracking the most recent read time for a table segment in a user tablespace
   2. Tracking the most recent write time for a table segment in a user tablespace
   3. Tracking insert time by row for table rows
   4. Tracking the most recent write time for a table block
   5. Tracking the most recent read time for a table segment in the SYSTEM tablespace
   6. Tracking the most recent write time for a table segment in the SYSAUX tablespace

   ADO automates ILM policies that are triggered by statistics collected by Heat Map. Heat Map automatically tracks segment-level access and modification times (read and write) in user tablespaces (A, B), and row-level creation/insert times for rows (C). D is incorrect because Heat Map tracks block-level modification only internally; ADO policies are evaluated at the segment or row level, not per block. E and F are incorrect because Heat Map tracking is disabled by default for the SYSTEM and SYSAUX tablespaces to avoid overhead on dictionary and repository objects.
2. Q2

   Which two are true about OS groups and users for Oracle Grid Infrastructure and the Oracle Relational Database Management System (RDBMS)? (Choose two.)

   1. By default, members of the OSASM group can access Automatic Storage Management and RDBMS instances.
   2. The primary group for the Oracle Grid Infrastructure and Oracle Database owners must be the Oracle Inventory group.
   3. The Oracle Grid Infrastructure installation must be owned by the grid user.
   4. The Oracle Grid Infrastructure owner owns Oracle Restart and Oracle Automatic Storage Management binaries.
   5. The Oracle Grid Infrastructure owner must have OSOPER, OSBACKUPDBA, and OSKMDBA as secondary groups.
   6. The same OSDBA group must be used for Automatic Storage Management and the Oracle Database.

   The Oracle Installation Guide requires the Oracle Inventory group (oinstall) to be the primary group of both the grid and oracle owners (B). The Grid Infrastructure home contains the Oracle Restart and Oracle ASM executables, so its owner owns those binaries (D). A is wrong because OSASM (SYSASM) grants access to ASM only, not to RDBMS instances. C is wrong because on standalone servers the oracle user may own the Grid Infrastructure installation; a separate grid user is recommended but not mandatory. E is wrong because those groups are optional. F is wrong because separate OSDBA groups (dba and asmdba) are permitted and recommended for role separation.
3. Q3

   Which four are true about duplicating a database using Recovery Manager (RMAN)? (Choose four.)

   1. Duplication can be done by having the auxiliary database instance pull backup sets from the target database instance.
   2. A connection to an auxiliary instance is always required.
   3. A subset of the target database can be duplicated.
   4. A new DBID is always created for the duplicated database.
   5. A connection to the recovery catalog instance is always required.
   6. A backup of the target database is always required.

   In the pull method (12c and later) the auxiliary instance pulls backup sets created over the network from the target (A), and in the push method the target instance pushes the data to the auxiliary instance (G); both are valid forms of active duplication. An auxiliary (destination) instance is always required for duplication (B), and RMAN can duplicate a subset of the database with TABLESPACE/DATABASE clauses (C). D is wrong because of the word "always": regular duplication assigns a new DBID, but DUPLICATE ... FOR STANDBY retains the target's DBID (a physical standby must share the primary's DBID), so a new DBID is not always created during duplication. E is wrong because the recovery catalog is optional. F is wrong because active duplication does not require pre-existing backups. H is wrong because backup-based duplication can be performed targetless, without a target connection.
4. Q4

   After a system crash, an instance restart and an attempted opening of the PDBs result in an error for data file 24, which belongs to PDB1. Which two are true? (Choose two.)

   1. Data file 24 can be recovered while PDB2 is opened.
   2. Data file 24 must be recovered while the CDB is opened.
   3. Data file 24 can be recovered while CDB$ROOT and PDB$SEED are opened.
   4. Data file 24 cannot be recovered while the CDB is opened.
   5. Data file 24 must be recovered while PDB2 is closed.

   Data file 24 belongs to PDB1, so recovery of that file is independent of the state of other PDBs. RMAN can perform media recovery of a data file while the CDB is open and while other PDBs such as PDB2 are open (A), and recovery can also be done with CDB$ROOT and PDB$SEED open (C); only PDB1 needs the affected file offline (or the whole PDB closed) during recovery of its file. B is wrong because the CDB does not have to be in any particular state beyond mounted for the file's container. D and E are wrong because no other container's state prevents the recovery of a file belonging to PDB1.
5. Q5

   Examine this output:

   SQL> select pluggable_database, shares, parallel_server_limit
     2  from dba_cdb_rsrc_plan_directives
     3  where plan = 'MY_PLAN'
     4  order by pluggable_database;
   
   PLUGGABLE_DATABASE            SHARES PARALLEL_SERVER_LIMIT
   ------------------------- ---------- ---------------------
   ORA$AUTOTASK                       1                   100
   ORA$DEFAULT_PDB_DIRECTIVE          1                     0
   PDB1                               2                   100
   PDB2                               2                    25
   PDB3                               1
   
   SQL> select name, value from v$parameter where name = 'resource_manager_plan';
   
   NAME                 VALUE
   -------------------- --------------------
   resource_manager_plan MY_PLAN

   Which two are true? (Choose two.)

   1. Any PDB not specified in the plan will be unable to execute statements in parallel.
   2. PDB3 can use all available parallel execution processes at times.
   3. PDB1 is always limited to 40% of the available system resources regardless of demand.
   4. Any PDB not specified in the plan will be able to use a maximum of 16.5% of the available system resources.
   5. PDB3 is guaranteed to receive at least 20% of the available system resources if there is enough demand.
   6. PDB2 is guaranteed at least 25% of the available parallel execution processes if there is enough demand.

   PDBs not named in the plan get the ORA$DEFAULT_PDB_DIRECTIVE, which has PARALLEL_SERVER_LIMIT = 0, so they cannot execute statements in parallel (A). PDB3's directive does not set PARALLEL_SERVER_LIMIT, so it defaults to 100%, meaning that at times when the other PDBs are not using parallel processes PDB3 can use all of them (B). C is wrong — PDB1 has 2 of the 7 total shares (a guaranteed minimum of roughly 28.6% under contention), and shares are minimums, not fixed caps on total resources; no directive sets a 40% utilization limit. D is wrong because shares guarantee a minimum (1/7 ≈ 14.3% for unspecified PDBs), not a maximum of 16.5%. E is wrong because PDB3 is guaranteed 1/7 ≈ 14.3%, not 20%. F is wrong because PARALLEL_SERVER_LIMIT = 25 for PDB2 is a ceiling on parallel server usage, not a guaranteed minimum allocation.
6. Q6

   Which two are true about flashback features in Oracle Database 19c and later releases? (Choose two.)

   1. Flashback logs are automatically purged when DB_FLASHBACK_RETENTION_TARGET is set lower than the time they have already been retained.
   2. Flashback logs are monitored and proactively deleted when beyond the retention period defined in DB_FLASHBACK_RETENTION_TARGET only after there is space pressure.
   3. Flashback logs are monitored and proactively deleted when beyond the retention period defined in DB_FLASHBACK_RETENTION_TARGET before there is space pressure.
   4. Flashback logs are monitored for being older than the retention period defined in DB_FLASHBACK_RETENTION_TARGET and can be deleted by an administrator written event trigger.
   5. Flashback logs are automatically purged whenever the value of DB_FLASHBACK_RETENTION_TARGET is changed.

   When DB_FLASHBACK_RETENTION_TARGET is lowered, flashback logs older than the new retention time are automatically purged (A). In 19c, the RVWR/monitoring process proactively deletes flashback logs that exceed the retention target even when there is no space pressure (C) — this proactive cleanup is a documented 19c improvement. B is wrong because deletion is proactive, not dependent on space pressure. D is wrong because there is no admin event-trigger mechanism for flashback log deletion. E is wrong because purging is driven by the retention threshold, not by any change of the parameter value itself.
7. Q7

   Which two are true about unplugging an application container from a container database and plugging it into a different container database?

   1. Plugging the application root into a different CDB plugs in all its application PDBs.
   2. The application root of an application container should be plugged into the other CDB before its application PDBs are plugged in.
   3. Application PDBs in the application container must be unplugged before the application root is unplugged.
   4. Unplugging the application root from a CDB unplugs all its application PDBs.
   5. It requires only local UNDO mode in the database where the application container will be unplugged.
   6. It requires local UNDO mode in both container databases.

   The application container is handled as a unit: when the application root is unplugged, all of its application PDBs are unplugged together with it (D), and when the application root is plugged into a different CDB the application PDBs come along as part of the container (A). B is wrong because the application PDBs are not plugged in separately, so there is no ordering requirement. C is wrong because the application PDBs do not need to be unplugged individually first. E and F are wrong because local undo is not a requirement for unplugging/plugging an application container.
8. Q8

   Which three are true about Automatic Workload Repository (AWR) snapshots?

   1. They can be retained forever.
   2. They are always created automatically.
   3. They are always created manually.
   4. They are generated if STATISTICS_LEVEL is set to TYPICAL.
   5. They are generated if STATISTICS_LEVEL is set to BASIC.
   6. They are generated if STATISTICS_LEVEL is set to ALL.

   AWR snapshot retention can be set to a value so large (or unlimited) that snapshots are effectively retained forever (A). Snapshots are generated when STATISTICS_LEVEL is TYPICAL (the default) or ALL (D, F). B and C are both wrong because snapshots are normally taken automatically every hour, but they can also be created manually with DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, so neither "always" statement is true. E is wrong because STATISTICS_LEVEL=BASIC disables automatic snapshot creation and most statistics collection.
9. Q9

   Choose three. Which three are true about Oracle Restart?

   1. It is used in both clustered and non-clustered environments.
   2. You can add and remove components from an Oracle Restart configuration with Server Control (SRVCTL) commands.
   3. It runs from the Oracle Grid Infrastructure home.
   4. If you shut down a database instance with SQL*Plus, Oracle Restart interprets this as a database failure and attempts to restart the instance.
   5. The Server Control (SRVCTL) utility can be used to start and stop Oracle Restart.
   6. It can manage Oracle Database instances, Oracle Net Listeners, and Oracle Automatic Storage Management instances.

   Components are added to or removed from the Oracle Restart configuration with SRVCTL ADD/REMOVE commands (B), the OHASD stack runs from the Grid Infrastructure home (C), and Oracle Restart manages database instances, listeners, ASM instances, disk groups and services (F). A is wrong because Oracle Restart is only for single-instance (non-clustered) environments — clustered systems use Oracle Clusterware. E is wrong because the Oracle Restart stack itself is started and stopped with CRSCTL (or automatically with the OS), not SRVCTL. D is a distractor: Oracle Restart restarts components only after a failure detected by its health checks, and shutdowns performed through SRVCTL are recognized as intentional, which is why SRVCTL should be used for planned stops.
10. Q10

    Choose three. Identify three reasons for using a recovery catalog with Recovery Manager (RMAN).

    1. to allow database duplication without a connection to the target instance
    2. to store backup metadata that would cause the controlfile to become too large
    3. to store backups that would cause the controlfile to become too large
    4. to report on the database schema at an earlier point in time
    5. to allow database duplication without a connection to the auxiliary instance

    A recovery catalog stores metadata for a longer period than CONTROL_FILE_RECORD_KEEP_TIME allows, preventing the control file from growing too large with backup records (B), and it enables REPORT SCHEMA ... AT TIME to report the schema as of a past time (D). Targetless (backup-based) duplication requires a recovery catalog because the auxiliary cannot connect to the target (A). C is wrong because the catalog stores metadata only, never backup pieces themselves. E is wrong because a connection to the auxiliary instance is always required for duplication.
11. Q11

    Examine this configuration: 1. The ORCL database data files are in Automatic Storage Management (Oracle ASM) disk group +DATA. 2. ORCL uses disk group +FRA for the Fast Recovery Area. 3. LISTENER is the listener for ORCL. 4. The database, listener, ASM instance, and ASM disk groups are managed by Oracle Restart. 5. All components are currently shut down. You execute: srvctl start database -db orcl. What is the outcome?

    1. The ORCL database, the Oracle ASM instances, the +DATA and +FRA disk groups, and the LISTENER are started.
    2. Only the ORCL database instance is started.
    3. Only the ORCL database and the ASM instances are started.
    4. Only the ORCL database instance, the Oracle ASM instance, and the +DATA and +FRA disk groups are started.
    5. Only the ORCL database instance and the +DATA and +FRA disk groups are started.

    SRVCTL computes dependencies: starting a database automatically starts its required Oracle ASM instance and mounts the disk groups the database uses (+DATA and +FRA). However, SRVCTL does not start the listener as a database dependency, so LISTENER remains down. A is wrong because the listener is not started. B and E are wrong because the database files and recovery area reside in ASM, so the ASM instance and disk groups are prerequisites and are started. C is incomplete because the required disk groups are also mounted.
12. Q12

    Choose two. Which two are true about PDB archive files?

    1. DBMS_PDB.CHECK_PLUG_COMPATIBILITY can be run against a PDB archive.
    2. They contain a PDB manifest file and all the data files in uncompressed form for a PDB.
    3. They contain all the data files only in uncompressed form for a PDB.
    4. They must be created using the DBMS_PDB package.
    5. They contain a PDB manifest file and all the data files in compressed form for a PDB.
    6. They contain all the data files only in compressed form for a PDB.

    A .pdb archive file is a compressed file (created with UNPLUG INTO ... FOR DOWNLOAD or DBMS_PDB operations) that contains both the PDB manifest (XML metadata) and all of its data files in compressed form (E), and DBMS_PDB.CHECK_PLUG_COMPATIBILITY can be executed against the archive to test compatibility with a target CDB (A). B and C are wrong because the archive is compressed. D is wrong because a .pdb archive can be created with the ALTER PLUGGABLE DATABASE ... UNPLUG INTO ... FOR DOWNLOAD statement without the DBMS_PDB package. F is wrong because the archive also contains the manifest file, not only data files.
13. Q13

    What is the effect of specifying the "ENABLE PLUGGABLE DATABASE" clause in a "CREATE DATABASE" statement?

    1. It will create a multitenant container database (CDB) with only the root opened.
    2. It will create a CDB with root opened and seed read only.
    3. It will create a CDB with root and seed opened and one PDB mounted.
    4. It will create a CDB that must be plugged into an existing CDB.
    5. It will create a CDB with root opened and seed mounted.

    ENABLE PLUGGABLE DATABASE creates a container database (CDB). After creation, CDB$ROOT is opened READ WRITE and PDB$SEED is opened READ ONLY, ready for creating PDBs from the seed. A is wrong because the seed is also opened, not just the root. C is wrong because no PDB is created automatically. D is wrong because the CDB is a standalone database, not something to be plugged in. E is wrong because the seed is opened READ ONLY, not left mounted.
14. Q14

    Choose two. RMAN 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?

    1. 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
    2. when a new tablespace is added to a registered target database
    3. when a backup of the current control file is created
    4. 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
    5. when the target is first registered

    A full resynchronization copies all information from the control file into the catalog. It is performed automatically when the target is first registered (E) and when the control file is re-created or a current control file backup is made, because these are structural changes that require complete metadata refresh (C). A and D are wrong: when control file records are aged out, the catalog retains its records — RMAN does not trigger a full resync for that, and ordinary metadata changes only cause a partial resync. B is wrong because structural changes like adding a tablespace trigger only a partial (not full) resynchronization.

Answer all 139 questions with instant feedback →

Reader reviews 43 ratings

• Amazing resource for 1Z0-083! The unlimited practice attempts and detailed tracking helped me focus my study time effectively.

  ★★★★★★ Thomas B.

• I bought the 1Z0-083 question bank a week before my exam and passed with 90%+. The questions are that good.

  ★★★★★ Maria V.

• The way the 1Z0-083 questions are broken down by domain is great. Let me focus on my weak spots without wasting time.

  ★★★★★★ Olivia H.

• Detailed, organized, and accurate. Exactly what you want in 1Z0-083 prep material. The explanations deserve special mention.

  ★★★★★★ Gabriel L.

• Good investment for the 1Z0-083 exam. My only regret is not buying it sooner — would have saved me a lot of study time.

  ★★★★★★ Henry C.

• I was really impressed by the quality of the 1Z0-083 questions. No typos, no vague wording — they clearly know the material.

  ★★★★★★ Stella P.

Frequently asked questions

More Oracle exams