Ace the Oracle OCM Exam: Insider Tips, Environment Setup, and Common Pitfalls

Exam Overview and Locations

The author completed the Oracle OCM exam in early May, receiving the pass notification on May 31. In China, exam centers are available in Beijing, Shanghai, and Guangzhou; the author chose Beijing but notes that Guangzhou now offers more sessions, so candidates should check the schedule at

http://education.oracle.co.uk/html/oracle/28US/SCHED_SP_OCM.htm

.

Machine Types (Odd/Even)

During the exam two independent PCs are placed in front of each candidate. Their hostnames differ only in the last digit, designating one as the odd machine (e.g., edsir5p1) and the other as the even machine (e.g., edsir5p2). The odd machine hosts the exam tasks.

Required Software (EM, DNS, Packages)

The exam environment does not include Oracle Enterprise Manager (EM); candidates must install it manually and run it in a separate process while continuing other tasks. DNS is pre‑installed on a dedicated server; its functionality can be verified with nslookup. No additional RPM packages are needed because the 32‑bit Oracle Linux image already contains all required database binaries.

Database Crash Scenarios

Two crash scenarios appear in the exam: one in the Data Management section and another in the Performance Management section. Both involve corrupted files that require database recovery when the candidate attempts to start the instance. The first scenario deletes the redo log and the USERS tablespace files; the second deletes the SYSTEM tablespace and control files.

Skillset Tasks

Skillset 1 – Server Configuration

1) Redirect all error and warning logs to the $ORACLE_HOME directory by setting the diagnostic_dest parameter.

2) Apply a patch whose number starts with 128. The author encountered an opatch apply error because the oracle binary in $ORACLE_HOME/bin was active; shutting down all instances allowed the patch to succeed.

3) After the “end state” check, the prod4 instance appeared incorrect despite OS‑level checks showing normal listener and instance status. The fix is to correct the TNS entry for prod4.

Skillset 2 – Data Management

1) Materialized view tuning: after running dbms_advisor.tune_mview, the view still cannot be rewritten. The author was unable to resolve this and advises caution.

2) Transportable tablespace: remember to include the Solaris dump file mentioned in the task. Verify supported platforms via V$TRANSPORTABLE_PLATFORM and use RMAN with exact syntax, e.g.

convert datafile '******' from platform '******' to platform '*******' format '*******';

3) External table: create the external table directly from the provided gzip file and control file, using the uncompress utility specified in the control file; do not decompress the gzip file beforehand.

Skillset 3 – Data Warehouse Management

Insert data into a table via a script, then revert the changes without using flashback database. The correct approach is to call dbms_flashback.transaction_backout with the appropriate XID array, for example:

select versions_xid
from test_employees
where versions_operation='U'
order by versions_startscn;
exec dbms_flashback.transaction_backout(2, xid_array('*****','******'));

Skillset 4 – Performance Tuning

When using SPA tuning, apply SQL TUNE ADVISOR and SQL ACCESS ADVISOR to the target statements. The author notes that in both 10g and 11g environments the expected performance gains were not achieved, suggesting this may be an intentional exam trap; therefore, complete every prescribed step.

Skillset 5 – Data Guard

Duplicate the database using the duplicate command. Pay attention to differing ORACLE_HOME paths on the two machines and configure the TNS entry correctly. Service names must include the domain, e.g., prod1.us.oracle.com, and the Data Guard manager service should be set as prod1_DGMGRL.us.oracle.com.

Exam Review Summary

Create a detailed daily study plan and stick to it.

Practice the exam flow repeatedly to manage the tight time limits; automate complex commands with EM where possible.

Complete every task fully, even if the result is not optimal, as the exam scoring focuses on the steps taken.

If the exam machine malfunctions, inform the proctor promptly; extensions may be granted.