In the architecture section of the exam, expect questions that test your knowledge of very specific details about the Oracle software architecture. Specifically, you should be intimately familiar with what functions each Oracle background process performs; make sure that you know details such as what SMON does, what events cause DBWn to write dirty buffers to data files, what events cause LGWR to write the log buffer to the current online log group, which background processes perform I/O, and so on.

Understand that when a user establishes a dedicated server connection, Oracle allocates all portions of the corresponding PGA outside the shared pool. However, when a user establishes a multithreaded server (MTS) connection,Oracle allocates memory for the session’s cursor state and sort area, collectively known as the User Global Area (UGA) from the shared pool. Therefore, when you configure an Oracle instance with MTS, it is important to consider how much memory MTS sessions can allocate from the shared pool.

The architecture section of the exam will also ask you questions about the operations performed during SQL statement processing, including the commit of a transaction. For example, you should understand that a server process reads Oracle blocks from a data file into the buffer cache, that the server process updates buffers in the buffer cache, and that LGWR writes redo entries to the log buffer and then to the current online log group to protect all changes made to the database.

 You should understand the guidelines set forth by the Optimal Flexible Architecture (OFA). For example, know the directory structure that OFA guidelines recommend for software files and database files, how to name database files so that they are distinguishable, and so on.

In particular, know how to use the orapwd utility to create a password file and understand the effects of setting the REMOTE_LOGIN_PASSWORD FILE parameter to EXCLUSIVE, NONE (the default), and SHARED.

Consider the following scenario for an existing database:
1. The instance’s REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE.
2. The current password for SYS is SYSPWD.
3. You execute the following command to create or re-create the database’s password file

orapwd file=… password

4. You start SQL*Plus and then execute then following commands:

CONNECT sys/oracle AS sysdba;
CREATE USER test IDENTIFIED BY password;
GRANT SYSDBA TO test;

First, when you create or re-create a password file for a database, Oracle registers the SYS user in the new password file with the SYSDBA and SYSOPER privileges, and the SYS user’s entry in the password file uses the password that you specify in the PASSWORD parameter of the orapwd command, NOT the current password for the SYS account in the database. The CONNECT command above demonstrates this fact.

Now, which one of the following CONNECT commands do you think will execute successfully?
CONNECT test/oracle AS SYSDBA;
CONNECT test/password AS SYSDBA;

The correct answer is the second command, NOT the first command, as several practice test and actual exam questions that we have seen incorrectly state! With REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE, when you grant the SYSDBA or SYSOPER privilege to a user or execute an ALTER USER … IDENTIFIED BY … statement for a user, Oracle updates the entry in the password file for the user with that user’s current database password.

Continuing with the scenario, suppose you now issue the following command:

ALTER USER sys IDENTIFIED BY syspwd;

Which one of the following CONNECT commands do you think will execute successfully now?
CONNECT sys/oracle AS SYSDBA;
CONNECT sys/syspwd AS SYSDBA;
The correct answer is the second command, NOT the first command.

A final note on this subject. When testing the functionality described above in your practice environment, make sure to use a remote connection to your database or a host operating system account that does not have the operating system privileges to establish SYSDBA/SYSOPER database connections; otherwise, you might get confused at the results because you can enter whatever you want for a password when trying to establish SYSDBA/SYSOPER database connections - if it is not the correct password, Oracle authenticates your privileged connection request using operating system authentication instead.
 

When you do not specify a PFILE parameter with a STARTUP command when using either SQL*Plus or iSQL*Plus,

1) Oracle reads the default persistent parameter file (SPFILE) on the host, if available;

 2) if the default SPFILE is not available on the host, Oracle reads the SPFILE named spfile.ora in the default location on the host, if available;

 3) if neither SPFILE is available, Oracle reads the default PFILE on the host, if available;

4) otherwise, Oracle returns an error.

Be careful with questions about ALTER TABLE … DEALLOCATE UNUSED and ALTER INDEX … DEALLOCATE UNUSED. These statements deallocate unused space above the segment’s high water mark, NOT all unused space.

Carefully review the default settings for resource limit profile parameters and the units for each parameter.

For
example, for the CREATE PROFILE and ALTER PROFILE commands, the unit for the CPU_PER_SESSION and CPU_PER_CALL parameters is hundredths of a second, the unit for the IDLE_TIME parameter is minutes, the unit for the PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_LOCK_TIME, and PASSWORD_GRACE_TIME parameters is days, etc.

You should know the rules set forth by the default password complexity checking function, SYS.VERIFY_FUNCTION, when using password checking with resource limit profiles. Specifically, a user’s password cannot match the username; the password must be at least four characters in length; the password cannot be the simple words welcome, database, account, user, password, oracle, computer, or abcd; the password must contain at least one letter, one digit and one punctuation mark; a new password must differ from the old password by at least three characters.