Oracle Interview Preparation Notes Part 1

We will be providing the Interview and Certification Pracitce notes like a quick reference in Oracle Administration and Architecture, Performance Tuning, Back and Recovery. This is all you need to be able to answer most of the Interview Questions and also helps with the Oracle DBA Certification.

Architecture:

Instance = SGA Memory and Background processes, a Database = physical files (data, redo, and control)

Background Processes (5 required)

1. PMON - Cleans up after failed processes (rollback, release locks, etc.)
2. SMON - Auto recovery, coalesces free space, deallocates temp segments.
3. DBWR - Writes dirty db buffers to data files.
4. LGWR - Sequential writes from redo log buffer to redo log file.
5. CKPT - Synchronizes data files.
6. ARC0 - Archiver (optional).
7. S000 – Server process is responsible for reading data on behalf of user process.
8. LCK0 – (Parellel Server) Acquires locks on remote tables.
9. RECO – (Distributed option) Ensures consistency in remote transactions.

SGA = the db buffer cache, the shared pool, the redo log buffer
Shared Pool = Data Dictionary + Library Cache
Library Cache = Shared SQL Area (SQL text + Parse Tree)
PGA = Program Global Area = Sort area + session info + cursor info (Private SQL Area) + stack space.
[With MTS, session info is stored in the SGA.]

Query, 3 phases = Parse, Execute, Fetch

What Happens when a DML is executed

1. Read data & rollback into buffer,
2. Lock rows
3. Record changes in redo buffer
4. Before image to rollback.

What happens when a Commit (fast) is issued

1. SCN to redo buffer
2. LGWR writes to redo log
3. Informs user
4. Release locks.

** Note: Change is not written to disk yet, DBWR writes it only when…
· Threshold is hit in dirty buffers
· A process needs free buffers and cannot find them
· A timeout occurs (3 seconds)
· A checkpoint occurs.

Instance Management:

Startup:

1. NOMOUNT – Read init.ora, allocate SGA, start processes, open alert and trace files
2. MOUNT – Associate DB w/ started instance, open control files, read control file (no verify)
3. OPEN (default) – Open data files, open redo logs, check DB consistency, autorecovery.

Shutdown:

1. NORMAL – Oracle waits for all users to disconnect before shutting down clean.
2. TRANSACTIONAL Clients disconnected after transactions complete, immediate shutdown.
3. IMMEDIATE – Oracle rolls back all active transactions and shuts down.
4. ABORT – Current SQL terminated, no rollback, files are not closed, startup requires recovery.
ALTER SYSTEM ENABLE/DISABLE RESTRICTED SESSION
SELECT LOGINS FROM V$INSTANCE

Database Parameters:

Dynamic Performance (V$) Views: Some available from memory before mount, others read from control file.

SELECT * FROM V$FIXED_TABLE | SHOW PARAMETER x | SELECT * FROM V$PARAMETER

Some of these parameters can be changed using

ALTER SESSION or ALTER SYSTEM SET name=value [DEFERRED]

SQL.BSQ – creates the data dictionary base tables (automatic at create)
CATALOG.SQL – creates views (DBA_, USER_, ALL_) against base tables.
*.PLB = PL/SQL binaries.
CAT*.SQL = data dictionary views (most called by catalog.sql)
DBMS*.SQL = Oracle predefined packages (dbmsutil).
UTL*.SQL – Additional views or tables (utlxplan).

Invalid views and stored procedures are automatically recompiled upon next use.

New DBA trigger events – instance startup or shutdown, error message, user logon or logoff, create, alter, drop schema.

Control Files:

Binary file that is needed to mount a database and contains the following information:
Database name and ID, creation date, file and redo locations, tablespace names, log history, backup info, log sequence number, checkpoint information.

SELECT TYPE, RECORDS_TOTAL, RECORDS_USED FROM V$CONTROLFILE_RECORD_SECTION

--> Datafiles 30 4 (maxdatafiles = 30, used = 4)

V$CONTROLFILE, V$CONTROLFILE_RECORD_SECTION

Redo Logs:

• Used only for recovery of committed data not yet written to the data files.
• Log switches cause a check point.
  
• V$THREAD – displays sequence #s and groups.
• V$LOGFILE – displays redo log file information.
• V$LOG – displays details of each redo log.

Log Miner – can be used to analyze redo logs into SQL statements.

EXECUTE DBMS_LOGMNR_D.BUILD(‘dictfilename’,);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(‘redofile’,DBMS_LOGMNR.NEW));
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>’dictfilename’);
SELECT * FROM V$LOGMNR_CONTENTS;
EXECUTE DBMS_LOGMNR.END_LOGMNR;