OCP Security And Upgrades Practice Test 3 — 15 Questions

Question 1

You are managing an Oracle Database 23ai environment with Unified Auditing enabled. A security audit requires that you capture all failed login attempts and all changes to user privileges. You need to create an audit policy that records these events and ensures the audit records are written to the unified audit trail. Which statement should you execute?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

CREATE AUDIT POLICY security_policy ACTIONS LOGON WHENEVER NOT SUCCESSFUL, GRANT, REVOKE;. To capture failed login attempts and all privilege changes, you create a Unified Auditing policy with the LOGON action qualified by WHENEVER NOT SUCCESSFUL, and include GRANT and REVOKE actions without qualification. This ensures failed logons are audited while all privilege changes, successful or not, are recorded. The policy is then enabled, and audit records go to the unified audit trail. This approach balances security needs with audit volume.

Answer

CREATE AUDIT POLICY security_policy ACTIONS LOGON, GRANT, REVOKE;

Answer

CREATE AUDIT POLICY security_policy ACTIONS LOGON, GRANT, REVOKE WHENEVER NOT SUCCESSFUL;

Answer

CREATE AUDIT POLICY security_policy ACTIONS LOGON WHEN 'SYS_CONTEXT(''USERENV'', ''ACTION'') = ''FAILED''', GRANT, REVOKE;

Question 2

You are upgrading an Oracle Database 19c non-CDB to Oracle Database 23ai using the plug-in method. During the upgrade, you notice that the target PDB has several locally managed tablespaces that are dictionary-managed in the source. Which action must you take before plugging in the non-CDB as a PDB?

Accepted Answer

Convert all dictionary-managed tablespaces to locally managed tablespaces.. Before plugging a non-CDB into a 23ai CDB, all tablespaces must be locally managed because 23ai does not support dictionary-managed tablespaces. The conversion must be performed in the source database prior to plug-in. Other options are incorrect because they either reference outdated scripts, impossible parameter settings, or incomplete actions. The correct approach is to convert dictionary-managed tablespaces to locally managed.

Answer

Set the COMPATIBLE parameter to 23.0.0 in the source database.

Answer

Enable the DBMS_SPACE_ADMIN package and use it to migrate the tablespaces.

Answer

Run the utlu112i.sql script to check for upgrade issues.

Question 3

You are preparing a 19c non-CDB database for migration to Oracle Database 23ai. The database uses a password-protected TDE master encryption key stored in a software keystore. To comply with security policy, you must migrate the keystore to an HSM-based hardware keystore before the upgrade. Which command correctly performs this migration?

Accepted Answer

ADMINISTER KEY MANAGEMENT SET KEYSTORE IDENTIFIED BY "software_password" MIGRATE TO HARDWARE KEYSTORE IDENTIFIED BY "hsm_password";. The correct command migrates the software keystore to a hardware keystore by exporting the keys and importing them into the HSM. This is essential when security policies mandate hardware-based key storage. The other commands either change passwords, open the keystore, or export keys without performing the migration, so they do not meet the requirement to move to an HSM before upgrading to 23ai.

Answer

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "secret" TO '/path/keys.exp' IDENTIFIED BY "password";

Answer

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password" CONTAINER = ALL;

Answer

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "new_password" WITH BACKUP USING 'backup_tag';

Question 4

You are the DBA for an Oracle Database 23ai multitenant environment. The security team requires that all local users in the PDB named SALESPDB be forced to change their passwords every 90 days and that they cannot reuse any of their last 10 passwords. The SALESPDB currently uses the default profile. What is the most efficient way to meet these requirements?

Accepted Answer

Alter the DEFAULT profile in SALESPDB to set PASSWORD_LIFE_TIME to 90 and PASSWORD_REUSE_MAX to 10.. Profiles are local objects in a multitenant database, so the DEFAULT profile in SALESPDB must be modified to enforce password lifetime and reuse limits for local users. Changing the PDB's DEFAULT profile automatically covers existing and future users who rely on it, avoiding per-user assignments. Common profiles and initialization parameters do not apply to local users in the PDB.

Answer

Create a new profile with the required password limits and assign it to each local user in SALESPDB.

Answer

Create a common profile in the CDB root with the required password limits and assign it to the local users in SALESPDB.

Answer

Set the PASSWORD_LIFE_TIME and PASSWORD_REUSE_MAX parameters in the CDB root and restart the instance.

Question 5

You are upgrading a 19c multitenant container database (CDB) to Oracle Database 23ai. The CDB contains several pluggable databases (PDBs). A security audit requires that you verify the unified audit trail configuration and ensure that audit records are being written to the correct destination. Which two tasks must you perform to validate the unified audit trail setup before the upgrade? (Choose two.)

Accepted Answer

Query the UNIFIED_AUDIT_TRAIL view to confirm that audit records are being generated.. Validating the unified audit trail involves checking that audit records are actually being generated and that the infrastructure can handle the audit volume. Querying UNIFIED_AUDIT_TRAIL confirms records exist, and checking UNIFIED_AUDIT_SGA_QUEUE_SIZE ensures the queue is adequately sized to prevent record loss. The other options relate to legacy auditing parameters or relocation tasks, which do not validate the unified audit trail configuration.

Answer

Check the AUDIT_TRAIL initialization parameter to ensure it is set to DB, EXTENDED.

Answer

Ensure that the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.

Answer

Run the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure to move the audit trail to a new tablespace.

Question 6

You are administering an Oracle Database 23ai container database (CDB) with several pluggable databases (PDBs). A compliance rule requires that all administrative actions performed by local DBAs in the PDB named FINPDB be audited, including actions by users with the SYSDBA privilege when connected to that PDB. Unified Auditing is enabled in the CDB. Which statement should you execute to meet this requirement?

Accepted Answer

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE; and create an audit policy for administrative actions, then enable it in FINPDB.. To audit SYSDBA actions, set AUDIT_SYS_OPERATIONS to TRUE. For local DBA administrative actions in FINPDB, create a Unified Auditing policy and enable it in that PDB. Combining these two mechanisms covers both privileged and non-privileged administrative activity, satisfying the compliance rule. Traditional auditing statements and invalid privilege syntax do not provide the required coverage.

Answer

CREATE AUDIT POLICY admin_actions_pol ACTIONS ALL; AUDIT POLICY admin_actions_pol;

Answer

AUDIT ALL BY ACCESS; and set audit_trail=DB,EXTENDED.

Answer

CREATE AUDIT POLICY admin_actions_pol PRIVILEGES SYSDBA; AUDIT POLICY admin_actions_pol;

Question 7

You are planning to upgrade a 19c database to Oracle Database 23ai. The database has Unified Auditing enabled, and the audit trail is stored in the SYSAUX tablespace. Your security team requires that audit records be retained for at least 90 days and that the audit trail does not impact the performance of the SYSAUX tablespace. What should you do before the upgrade?

Accepted Answer

Move the unified audit trail to a dedicated tablespace using DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.. Moving the unified audit trail to a dedicated tablespace separates audit I/O from other SYSAUX activity, reducing performance impact. This is a proactive step before upgrading to 23ai. While increasing SYSAUX size, configuring purge jobs, or enabling compression can help manage the audit trail, they do not fully address the performance isolation requirement. The dedicated tablespace approach is the most effective solution.

Answer

Increase the size of the SYSAUX tablespace to accommodate the audit trail growth.

Answer

Configure a purge job using DBMS_AUDIT_MGMT.CREATE_PURGE_JOB to delete audit records older than 90 days.

Answer

Enable audit trail compression using DBMS_AUDIT_MGMT.ENABLE_AUDIT_TRAIL_COMPRESSION.

Question 8

You manage an Oracle Database 23ai environment with Transparent Data Encryption (TDE) enabled. The master encryption key is stored in a software keystore. A security audit requires that the master key be rotated immediately and that the old key be retained for decryption of existing data. Which statement should you execute?

Accepted Answer

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'rotate' WITH BACKUP;. Rotating the TDE master key is done with ADMINISTER KEY MANAGEMENT SET KEY. To retain the old key for decryption of existing data, use the WITH BACKUP clause, which saves a copy of the keystore containing the old key. This allows data encrypted with the previous key to remain accessible while new data uses the new master key, satisfying the audit requirement.

Answer

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/new/location' IDENTIFIED BY password;

Answer

ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD;

Answer

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'rotate' FORCE KEYSTORE;

Question 9

You are upgrading a 19c database to Oracle Database 23ai. The database uses Transparent Data Encryption (TDE) with a software keystore. During the upgrade, you need to ensure that the TDE master encryption key is backed up. Which command should you use to back up the encryption key?

Accepted Answer

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'backup_tag' IDENTIFIED BY "password";. The BACKUP KEYSTORE command creates a backup of the keystore, including the TDE master encryption key, ensuring that you can restore it if needed. This is a critical pre-upgrade step. Exporting keys is used for migration, rekeying changes the key, and creating a new keystore does not back up existing keys. Therefore, the BACKUP KEYSTORE command is the correct choice.

Answer

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "secret" TO '/path/backup.exp' IDENTIFIED BY "password";

Answer

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "password" WITH BACKUP;

Answer

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/path/to/keystore' IDENTIFIED BY "password";

Question 10

You are preparing to upgrade a 19c non-CDB database to Oracle Database 23ai as a PDB using the plug-in method with AutoUpgrade. You have already run the pre-upgrade checks and fixed the issues. Which two actions must you perform before running the actual upgrade with AutoUpgrade? (Choose two.)

Accepted Answer

Convert the non-CDB to a PDB using DBMS_PDB.DESCRIBE to generate a manifest file.. To plug a non-CDB into a CDB as a PDB, you must first create a target PDB and generate a manifest file using DBMS_PDB.DESCRIBE. These two actions are prerequisites for the plug-in process. Recompiling PL/SQL, setting COMPATIBLE, and using Data Pump are either post-upgrade tasks or alternative migration methods, not pre-plug-in requirements.

Answer

Set the COMPATIBLE parameter to 23.0 in the non-CDB before plugging it in.

Answer

Export the non-CDB using Data Pump and import it into the target PDB.

Answer

Run the utlrp.sql script on the non-CDB to recompile all PL/SQL packages.

Question 11

You are managing an Oracle Database 23ai environment with Unified Auditing enabled. A security audit requires that you capture all failed login attempts and all changes to user privileges. You need to create an audit policy that records these events and ensures the audit records are written to the unified audit trail. Which statement should you execute?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a unified audit policy with a WHEN condition that specifies the time range.. Unified auditing in 23ai allows conditional auditing through the WHEN clause of a unified audit policy. You can use a condition that evaluates the current time to capture only out-of-hours access. The AFTER EVENT clause is for fine-grained auditing, not unified policies. Triggers cannot be used on SELECT, and traditional auditing with AUDIT_TRAIL lacks the required conditional logic.

Answer

Create a unified audit policy with an AFTER EVENT clause that checks the current time.

Answer

Use a database trigger that fires on SELECT and writes to an audit table.

Answer

Enable auditing by setting the AUDIT_TRAIL parameter to DB, EXTENDED and using the AUDIT statement with a BY SESSION clause.

Question 12

You have upgraded a 19c database to Oracle Database 23ai. The database uses Transparent Data Encryption (TDE) with a software keystore. After the upgrade, you need to rotate the TDE master encryption key for the pluggable database (PDB) named SALESPDB. Which command should you execute?

Accepted Answer

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP.. To rotate the TDE master encryption key for a PDB in 23ai, you connect to the PDB and execute ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP. This generates a new master key and backs up the keystore. Changing the keystore password, using ALTER SYSTEM, or attempting CREATE KEY are incorrect and will not achieve key rotation.

Answer

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY keystore_password.

Answer

ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY new_password.

Answer

ADMINISTER KEY MANAGEMENT CREATE KEY IDENTIFIED BY keystore_password.

Question 13

Which view should you query to verify the encryption status of tablespaces in 23ai?

Accepted Answer

V$ENCRYPTED_TABLESPACES. V$ENCRYPTED_TABLESPACES shows the encryption status of all tablespaces in the database.

Answer

DBA_ENCRYPTED_COLUMNS

Answer

DBA_ENCRYPTION_KEYS

Answer

DBA_TABLESPACES

Answer

V$WALLET

Question 14

Which component is required to implement Transparent Data Encryption (TDE) in 23ai?

Accepted Answer

Oracle Wallet. A keystore (wallet) is required to hold the master encryption key for TDE.

Answer

Oracle Audit Vault

Answer

Oracle Key Vault

Answer

Oracle Data Guard

Question 15

What is the primary benefit of using Unified Auditing in 23ai?

Accepted Answer

Simplified audit management and unified audit trail. Unified Auditing provides a single, unified audit trail for all audit records from various sources.

Answer

Improved query performance for application tables

Answer

Increased storage capacity

Answer

Automatic data masking