Creating and Configuring an Oracle Database
Scope
This domain covers database creation with DBCA or CREATE DATABASE, initialization parameters, the server parameter file, and basic network configuration. Expect questions on what can be changed after creation and how clients resolve a service.
Core rules
- DBCA can create a database from a template or silently; CREATE DATABASE requires a parameter file, password file, directories, and catalog scripts.
- DB_NAME identifies the database; DB_UNIQUE_NAME distinguishes members in Data Guard. DB_BLOCK_SIZE is fixed at creation. DB_CREATE_FILE_DEST enables Oracle Managed Files.
- Memory can be set with MEMORY_TARGET or with SGA_TARGET and PGA_AGGREGATE_TARGET. SGA_TARGET uses automatic shared memory management.
- The listener listens on a port; tnsnames.ora supplies connect descriptors. PMON registers services dynamically; LOCAL_LISTENER points the instance to a nondefault listener. Static registration uses SID_LIST_LISTENER.
- A password file allows remote SYSDBA and SYSOPER connections. ORACLE_SID selects the local instance.
Worked example
A DBCA silent creation sets SGA_TARGET=2G and creates ORCL with a password file. After startup, V$PARAMETER shows sga_target=2147483648. The client connects with a tnsnames alias; lsnrctl status shows the service ORCL registered by PMON. If registration is missing, LOCAL_LISTENER or the listener port is wrong.
Traps
DB_BLOCK_SIZE cannot be changed after creation without recreating the database. DB_NAME and DB_UNIQUE_NAME are not interchangeable. Dynamic registration is not immediate. A directory object is not an OS directory.