Database Creation, Startup and Shutdown

Key parameters: DB_NAME, DB_UNIQUE_NAME, DB_BLOCK_SIZE, CONTROL_FILES, SGA_TARGET, PGA_AGGREGATE_TARGET, PGA_AGGREGATE_LIMIT, MEMORY_TARGET, PROCESSES, SESSIONS, OPEN_CURSORS, UNDO_TABLESPACE, UNDO_RETENTION, DB_RECOVERY_FILE_DEST, LOG_ARCHIVE_DEST_n, COMPA…

35.1 Creating a Database

DBCA (GUI or silent): dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ORCL -createAsContainerDatabase true -numberOfPDBs 1 -pdbName PDB1 ...

Manual CREATE DATABASE statement (rare): then run catalog.sql, catproc.sql (catcdb.sql for a CDB).

35.2 Parameter Files

PFILE (init<SID>.ora, text) vs SPFILE (spfile<SID>.ora, binary, server-managed).

Search order at startup: spfile<SID>.ora → spfile.ora → init<SID>.ora.

SHOW PARAMETER sga_target
ALTER SYSTEM SET sga_target = 4G SCOPE = SPFILE;        -- MEMORY | SPFILE | BOTH
ALTER SYSTEM SET open_cursors = 500 SCOPE = BOTH;
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
CREATE PFILE = '/tmp/init.ora' FROM SPFILE;
CREATE SPFILE FROM PFILE = '/tmp/init.ora';
SELECT name, value, isdefault, issys_modifiable FROM v$parameter WHERE name LIKE 'db_%';

35.3 Startup Stages

SHUTDOWN → NOMOUNT (instance started: reads the parameter file, allocates SGA, starts background processes)
         → MOUNT   (control file opened: database structure known)
         → OPEN    (datafiles and redo logs opened: users can connect)

STARTUP;                        -- full open
STARTUP NOMOUNT;                -- for CREATE DATABASE, control file re-creation
STARTUP MOUNT;                  -- for recovery, ARCHIVELOG changes, renaming files
ALTER DATABASE MOUNT;  ALTER DATABASE OPEN;
STARTUP RESTRICT;   ALTER SYSTEM ENABLE RESTRICTED SESSION;
STARTUP FORCE;
ALTER DATABASE OPEN READ ONLY;

35.4 Shutdown Modes

Waits for Sessions Waits for Transactions Recovery on Startup No (rolls back) Yes (instance recovery)

35.5 Authentication for Administrators

OS authentication (dba, oper, backupdba, dgdba, kmdba, racdba OS groups).

Password file (orapwd file=$ORACLE_HOME/dbs/orapwORCL format=12.2). REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE.

Administrative privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, SYSRAC.

35.6 Alert Log, Trace Files and ADR

SELECT name, value FROM v$diag_info;     -- ADR home, trace, alert log location

adrci> show alert -tail 50
adrci> show problem
adrci> ips pack problem 1 in /tmp      # package diagnostics for Oracle Support