1z0-182 Oracle Database 23ai Administration Associate Questions and Answers

A.True. Without DB_CREATE_ONLINE_LOG_DEST_n, redo logs and control files default to DB_RECOVERY_FILE_DEST.

B.False. If only DB_CREATE_FILE_DEST is set, redo logs and control files also use it unless overridden.

C.True. DB_CREATE_ONLINE_LOG_DEST_1 becomes the default for data files and temp files if DB_CREATE_FILE_DEST is unset.

D.True. DB_CREATE_FILE_DEST serves as the default for all file types if no log-specific parameter is set.

E.False. Data files and temp files would also use DB_CREATE_ONLINE_LOG_DEST_1 if no other parameter is specified.

F.False. No such requirement exists; DB_RECOVERY_FILE_DEST operates independently.

A.False. No such restriction exists; DB_32K_CACHE_SIZE is independent of DB_CACHE_SIZE.

B.True. A nonstandard block size (32K) requires a corresponding cache (DB_32K_CACHE_SIZE) set to a non-zero value within SGA limits.

C.False. OS block size is irrelevant; Oracle manages its own block sizes.

D.False. No requirement for it to exceed DB_CACHE_SIZE.

E.True. The file system must have 100G available for the datafile.

User authentication in Oracle 23ai determines how users (especially administrative ones) connect to the database. Let’s analyze each option with extensive detail:

A. Operating System authentication may be used for system-privileged administrative users.

Explanation:True. OS authentication allows users mapped to OS accounts (e.g., ops$oracle) to connect without a password, often used for administrative users like SYS or SYSTEM. This is configured by creating an externally authenticated user (e.g., CREATE USER "OPS$ORACLE" IDENTIFIED EXTERNALLY) and relies on the OS to verify identity.

Mechanics:When a user logs in via sqlplus / as sysdba, Oracle checks the OS user against the dba group (Unix) or ORA_DBA (Windows). If matched, no password is needed, leveraging OS security.

Practical Use:Common for DBAs managing local instances, reducing password management overhead.

Edge Case:Requires REMOTE_LOGIN_PASSWORDFILE=NONE for exclusive OS auth, but this isn’t mandatory if a password file exists alongside.

Historical Note:Introduced in early Oracle versions, this remains a robust option in 23ai for local admin access.

B. Password authentication must be used for system-privileged administrative users.

Explanation:False. “Must” is incorrect; password authentication (e.g., sqlplus sys/password) is an option, not a requirement. OS authentication or password file authentication can also be used for users like SYS. This option overstates the necessity of password-based login.

Why Incorrect:Oracle’s flexibility allows multiple methods, contradicting the absolute phrasing here.

C. Password File authentication is supported for any type of database user.

Explanation:False. Password file authentication is restricted to users with SYSDBA, SYSOPER, or similar system privileges (e.g., SYSBACKUP). Regular users (e.g., HR) can’t use the password file (orapw); they rely on database authentication (passwords stored in the DB) or external methods.

Mechanics:The password file stores hashed credentials for privileged users, checked during remote AS SYSDBA logins.

Why Incorrect:Extending this to “any user” ignores Oracle’s security model limiting password file usage.

D. REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE to permit password changes for system-privileged administrative users.

Explanation:False. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE allows a dedicated password file for one instance, enabling password changes via ALTER USER SYS IDENTIFIED BY newpass. However, SHARED mode also permits changes for SYS, though not for other users added to the file. The “must” overstates the requirement; it’s sufficient, not necessary.

Mechanics:EXCLUSIVE locks the file to one DB, while SHARED allows multiple DBs to use it, with restrictions on non-SYS users.

E. Password File authentication must be used for system-privileged administrative users.

Explanation:True. For remote administrative access (e.g., sqlplus sys/password@orcl as sysdba), a password file is mandatory when REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE or SHARED. Local OS authentication is an alternative, but for network-based admin tasks, the password file is required, making this statement true in that context.

Mechanics:Set via orapwd (e.g., orapwd file=orapworcl password=oracle entries=10), enabling remote SYSDBA logins.

Edge Case:If REMOTE_LOGIN_PASSWORDFILE=NONE, only OS auth works locally, but this isn’t the default or typical setup.

A.True. PDB_DBA grants admin rights (e.g., CREATE SESSION, ALTER SYSTEM) within the PDB.

B.False. DDL on objects requires ownership or explicit grants.

C.False. User management needs CREATE USER privilege, not inherent.

D.False. It includes multiple privileges, not none.

A.False. Requires MOUNT to access data files.

B.False. Requires MOUNT for redo logs.

C.True. CREATE DATABASE runs in NOMOUNT.

D.True. CREATE CONTROLFILE is possible.

E.False. Recovery needs MOUNT or OPEN.

A.False. LREG handles registration in 23ai, not PMON.

B.False. CKPT (Checkpoint) process updates the control file.

C.True. PMON cleans up after failed processes (e.g., releases locks).

D.True. PMON rolls back uncommitted transactions of failed processes.

E.False. SMON (System Monitor) manages temporary segment cleanup.

D.True. The .pdb file (archive) contains the manifest and data files; all must be copied to the target CDB for plugging.

A-C.False. The archive alone isn’t enough; all components are needed.

A.False. Net Manager is local, not centralized.

B.False. NetCA can run standalone.

C.True. EMCC manages service names centrally.

D.True. EMCC configures listeners on managed targets.

E.False. lsnrctl starts a default listener if no listener.ora exists.

F.True. Net Manager configures local tnsnames.ora.

A.True. ASSM categorizes blocks (e.g., 0-25%, 25-50%) for efficient space use.

B.False. Updates may cause chaining/migration, not always relocation.

C.True. ASSM optimizes inserts into suitable blocks.

D.False. ASSM uses a bitmap, not necessarily the first block.

E.False. PCTFREE is segment-specific, not universally 10%.

Automatic Maintenance Tasks (AMTs) in 23ai optimize database performance. Let’s explore:

A.False. SQL Access Advisor exists but isn’t an AMT; it’s manual or invoked separately.

B.False. SQL Performance Analyzer assesses change impacts but isn’t part of AMTs.

C.False. No “Automatic Index Advisor” exists as an AMT; Auto Index is a separate feature.

D.True. The SPM Evolve Advisor task (part of ORA$AUTOTASK) automatically evolves SQL plan baselines, testing and accepting new plans.

Mechanics:Runs in maintenance windows, managed by DBMS_SPM.

E.True. SQL Tuning Advisor (STA) runs automatically via AMTs, tuning high-load SQL.

Mechanics:Identifies candidates from AWR and suggests indexes, profiles, etc.

With OMF enabled, Oracle automatically manages file creation. The command ALTER TABLESPACE sales_q1 ADD DATAFILE without a file specification relies on initialization parameters:

A. Specify a path in the DATAFILE clause ... with at least 100 MB of available space.False. With OMF, explicitly specifying a path overrides OMF behavior, but it’s not required for success if OMF parameters are set correctly.

B. Add the AUTOEXTEND ON clause with NEXT set to 100M.False. AUTOEXTEND is optional and affects file growth, not the initial creation success, which depends on available space in the OMF location.

C. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 MB of available space.True. If both parameters are set,Oracle may use either for data files (depending on context), and sufficient space (e.g., 50 MB minimum for a smallfile) ensures success.

D. Ensure that DB_CREATE_FILE_DEST specifies a location with at least 100 MB of available space.True. This is the primary OMF parameter for data files; sufficient space (typically 100 MB minimum for a new file) guarantees the command succeeds.

E. Ensure that DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST each specify locations with at least 50 MB of available space.False. This is redundant with C; only one needs sufficient space, though C’s phrasing makes it a valid independent action.

C.True. Hot cloning requires the source PDB (PDB1) to be read-only to ensure consistency during the clone. Others are not prerequisites.

The Automatic Workload Repository (AWR) is a cornerstone of Oracle’s self-tuning capabilities, collecting and storing performance statistics for analysis. Let’s dissect each option:

A. Automatic capture of statistical information from the SGA and storing it in the AWR using Automatic Database Diagnostic.

Explanation:True. AWR automatically captures statistics (e.g., wait events, SQL stats) from the System Global Area (SGA) via the MMON (Manageability Monitor) process, storing them in the AWR repository (in SYSAUX). This is part of the Automatic Database Diagnostic Monitor (ADDM) framework, though “Automatic Database Diagnostic” likely refers to this broader mechanism.

Mechanics:Snapshots are taken hourly by default (configurable via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS), persisting data like DBA_HIST_SYSSTAT for self-tuning analysis.

Practical Use:Enables historical performance tracking, feeding tools like ADDM and SQL Tuning Advisor.

Edge Case:If STATISTICS_LEVEL=BASIC, AWR collection is disabled, haltingself-tuning.

Historical Note:Introduced in 10g, enhanced in 23ai for finer granularity.

B. ADDM, a server-based expert that reviews database performance statistics captured by Snapshots to identify potential problems before system performance degrades noticeably.

Explanation:True. The Automatic Database Diagnostic Monitor (ADDM) analyzes AWR snapshots to proactively detect issues (e.g., high CPU usage, I/O bottlenecks) and recommend fixes. It runs automatically after each snapshot in maintenance windows.

Mechanics:ADDM uses DBA_ADVISOR_FINDINGS to log issues, leveraging AWR data like DBA_HIST_SQLSTAT. Example: It might suggest adding an index for a slow query.

Practical Use:Prevents performance degradation in production systems by catching trends early.

Edge Case:Limited by snapshot frequency; real-time issues may need manual intervention.

C. Automatic Diagnostic Collector used to capture and store database errors and hung analysis.

Explanation:False. No such tool exists as an “Automatic Diagnostic Collector” in AWR context. This likely confuses the Incident Packaging Service (IPS) or ADR (Automatic Diagnostic Repository), which handles errors but isn’t AWR-based or self-tuning.

Why Incorrect:ADR collects trace files and logs, not AWR statistics, and isn’t part of self-tuning.

D. Automatic population of performance views (V$ views) from statistical data stored in AWR repository and using Automatic Database Diagnostic.

Explanation:False. V$ views (e.g., V$SESSION) are real-time memory structures in the SGA, not populated from AWR, which is historical (e.g., DBA_HIST_*). AWR doesn’t back-feed V$ views; the reverse occurs via snapshots.

Why Incorrect:Misunderstands the data flow; AWR is a sink, not a source for V$ views.

E. Automatic Application Tracing used to collect High-Load SQL statements and statistics.

Explanation:False. No “Automatic Application Tracing” exists as an AWR tool. SQL tracing (e.g., DBMS_MONITOR) is manual, and high-load SQL is captured by AWR indirectly via V$SQL snapshots, not a distinct tracing tool.

Why Incorrect:Conflates manual tracing with AWR’s passive collection.

A.True. Integrates with MOS for patches and support.

B.True. Manages Exadata, etc.

C.True. Covers WebLogic and other middleware.

D.False. Web-based, but not built into the DB.

E.False. Can manage stopped instances via agents.

A.False. Connect Time Failover doesn’t need TAF; TAF is for runtime failover.

B.False. Source Routing doesn’t mandate encryption.

C.True. Needs multiple addresses (e.g., (ADDRESS_LIST=...)) for failover.

D.False. Load balancing works with tnsnames.ora, no name server required.

E.True. Balances connections across shared server dispatchers.

A.False. ADR is file-based, not in a schema.

B.False. No such default exists.

C.True. ADR logs ASM diagnostics.

D.True. Supports Clusterware diagnostics.

E.False. Defaults to $ORACLE_BASE or /u01/app/oracle if unset.

A.False. Not all trace files indicate errors; some log normal activity.

B.True. Server processes write traces (e.g., ora_s000_123.trc) to the file system.

C.True. Background processes (e.g., ora_lgwr_123.trc) also write traces.

D.False. Names include process type and PID, not just DB name and sequence.

E.False. Traces go to DIAGNOSTIC_DEST, not necessarily FRA unless configured there.

A.True. Multiple sessions with different users are possible.

B.False. Buffer cache and redo log buffer are in SGA, not large pool.

C.True. Session tracks login state.

D.True. Each process has its own PGA.

E.False. Connection is the network link; session is the state.

F.True. Same user can have multiple sessions (e.g., via different terminals).

A.False. “Logging out to the abc database” is nonsensical; abc is a script, not a database.

B.False. SQL*Plus finds abc.sql in the current directory by default; a full path isn’t required unless it’s elsewhere.

C.False. The first @ specifies the TNS alias (orcl), the second runs the script (@abc); this is valid syntax.

D & E.True (identical options). The command connects to orcl via TNSNAMES.ORA, authenticates HR, and executes abc.sql.

Mechanics:sqlplus hr/hr@orcl resolves orcl to a listener address, connects, and@abc runs the script post-login.

A.True. V$ views reflect real-time memory data, not consistent snapshots.

B.True. V$FIXED_TABLE lists all V$ views.

C.True. Data comes from memory structures and data dictionary metadata.

D.False. Owned by SYS, not SYSTEM.

E.False. Some V$ views are accessible in MOUNT state.

A.True. Background processes (e.g., PMON) start.

B.False. Consistency checks require MOUNT.

C.False. Control files open in MOUNT.

D.True. SGA is allocated at startup.

E.False. SYS access requires OPEN.

A.False. Backups are via RMAN, not AMTs.

B.True. Stats collection is an AMT.

C.False. Error logging isn’t an AMT.

D.True. SPM tasks are included.

E.False. Notifications aren’t AMTs.

F.True. Segment Advisor is an AMT.

B.True. Without SCOPE, ALTER SYSTEM defaults to BOTH (memory and SPFILE); PFILE isn’t updated unless manually recreated.

B.True. The SQL Statistics Advisor (new in 23ai) identifies stale or missing statistics. Others focus on tuning, performance analysis, or access paths.

A.False. V$TABLESPACE doesn’t show free space directly.

B.False. It’s from the control file, not the data dictionary.

C.True. Renaming in MOUNT state updates DBA_DATA_FILES post-open.

D.False. DBA_TABLESPACES shows attributes, not free space directly (use DBA_FREE_SPACE).

E.True. V$TABLESPACE reflects control file data.