OCA Study Sheet
Database Writer writes when:
- No free buffers
- Too many dirty buffers
- 3 second timout
- Checkpoint
Log Writer writes when:
- session COMMIT
- buffer 1/3 full
- DBWR is about to write
UNDO is used for:
- Reversing incomplete transactions
- Read consistency (temporary)
- Transaction isolation
- Flashback (9i temporary)
UNDO errors:
Run out of space ORA-30036 “unable to extend segment in undo tablespace” Lets the query succeed
Fails on consistent read- undot for CR has been overwritten ORA-01555 “snapshot too old”. This means the transaction will succeed.
FAILURE TYPES
- Statement failure
- User process failure- Network(listener, network card, route), User error
- Media Failure
- Instance Failure
RECOVERY
- Loss of control file
- Loss of online redo log
- Loss of datafile in NOARCHIVELOG mode
- Loss of datafile in ARCHIVELOG mode
Init parameters
FAST_START_MTTR_TARGET
Views
V$INSTANCE_RECOVERY
Commands
alter system checkpoint
alter system switch logfile
FLASHBACK
db_file_recovery_dest
db_file_recovery_dest_size
V$RECOERVY_FILE_DEST
V$FLASH_RECOVERY_USAGE
How to Set Archive Log Mode
- ARCHIVE LOG LIST
- Shutdown
- ALTER DATABASE ARCHIVELOG
- ALTER SYSTEM ARCHIVE LOG CURRENT, or
- ALTER SYSTEM SWITCH LOGFILE
- SELECT name, is_recovery_dest_file FROM v$archive_log
Commands to Perform a Hot Backup
- ALTER TABLESPACE <name> BEGIN BACKUP
- LIST BACKUP OF DATABASE
- LIST BACKUP OF ARCHIVE ALL;
- REPORT NEED BACKUP;
- REPORTOBSOLETE
RMAN commands
- CROSSCHECK
- DELETE EXPIRED
- DELETE OBSOLETE
- CATALOG
AUDITING
DB_DBA_AUDIT_TRAIL –à
Value Based Triggers
FGA_DBMS_FGA (all rows) These two views combine to form DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL—à
ALERTS – Threshold Alerts – MMON – stateful
DBA_SERVER_ALERT
DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY à “snapshot too old is stateless”
STATISTICS
TABLEs DBA_TABLES
COLUMNS DBA_TAB_COLUMNS
INDEXES DBA_INDEXES, INDEX_STATS
STATISTICS_LEVEL = TYPICAL, ALL, BASIC
OPATCH
opatch apply
opatch rollback