Oracle Interview Preparation Notes Part 10

A DBA’s most important responsibility is to keep the database available to users.

1. Protect the database from failures.

2. Increase Mean-Time-Between-Failures (MTBF).

3. Decrease Mean-Time-To-Recover (MTTR).

4. Minimize Data Loss.

· 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 (8I – reusable by RMAN), log sequence number, checkpoint information.

· Are read to mount and open the database.

· Should be backed up any time physical changes are made to the database.

Online Redo Logs:

· Used only for recovery of committed data not yet written to the data files.

· Do not need to be backed up, but can be backed up cold to copy database and avoid having to open new database with RESETLOGS.

· A filled redo log cannot be reused until a checkpoint has occurred and the redo log has been written by the ARCn process.

· Multiplex. Processing can continue as long as 1 member from each group is available.

· Log switches occur when…

· When online redo log fills.

· When fast start checkpointing is set.

· At the log_checkpoint_interval.

· At the log_checkpoint_timeout.

· At instance shutdown (except ABORT).

· When forced by an ALTER SYSTEM CHECKPOINT

· When a tablespace is taken offline.

· When an online backup is started.

Archived Redo Logs:

· Redo files that have filled and copied to a backup location (Archive_log_dest).

· Are critical for recovery in a hot backup scheme.

· Writes all modifies buffers to the datafiles. Datafiles always have committed data.

· Records SCN in the control file and updates all file headers. (Note: except read only tablespaces).

Fast Start Checkpointing (8I): FAST_START_IO_TARGET=n (blocks).

· Reduces time needed for instance crash recovery by ensuring that roll forward will only require n blocks.

· Can also use LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT to influence.

Fast Start Recovery: DB is available at the end of the roll forward process, individual user processes perform rollback when the blocks needing rolled back are requested.

Database Synchronization: All online datafiles (except read-only tablespaces), redos, and control files must have the same checkpoint number (SCN) for the database to be opened.

Rollback Segments:

· Store location of data and the data as it existed before being modified for read consistency.

· During recovery, used to undo uncommitted changes applied from redo logs to datafiles.

Transportable Tablespaces:

1) Make tablespace Read Only.

2) Export the Metadata. EXP TRANSPORT_TABLESPACE=Y TABLESPACE ts1

3) Copy data file to target system.

4) Copy export dump to target system.

5) Import the Metadata. IMP TRANSPORT_TABLESPACE=Y DATAFILES (file1, file2)

6) Bring tablespace online and enable original for Read/Write.

Note: Nested tables, varrays, and bitmap indexes are NOT transportable.

V$DATABASE – Lists status and recovery info (db name, ID, creation date, last checkpoint, etc)

V$DATAFILE – Info on file #, name, creation date, status, checkpoint, etc.

V$BACKUP – Which files are ACTIVE in backup mode.

V$DATAFILE_HEADER – Which file are in backup mode (FUZZY=YES).

V$INSTANCE_RECOVERY – Contains size information of redo logs.

V$RECOVER_FILE - use to determine which file(s) need recovery.

V$RECOVERY_FILE_STATUS – files needing recovery and their recovery status.

V$RECOVERY_STATUS – overall database recovery info.

Database Failure Types:

1) Statement failure – failed SQL is automatically rolled back and an error is returned to user.

2) User Process failure – abnormal disconnect – PMON detects and rolls back and releases locks.

3) User Error (drop table, data) – DBA is required to recover data (import or incomplete recovery)

4) Instance Failure – Abnormal shutdown – Instance simply needs restarted, SMON auto recovers by

Rolling forward changes in the redo log not recorded in the datafiles before Open of database.
Rollbacks can occur after the database is open, when block data is requested.

5) Media Failure – Loss or corruption of files – DBA needs to apply appropriate recovery.

Read Only Tablespaces:

ALTER TABLESPACE ts1 READ ONLY;

· Checkpoint occurs when made read only, SCN is written to headers, and does not change.

· Should be backed up (along with control file) immediately following read only.

Datafiles CAN contain uncommitted data – Data is changed and uncommitted and forced off to disk by the DBWR process when more db buffer space is requested by other transactions.

Setting up Archive Logging:

LOG_ARCHIVE_START = TRUE in init.ora, ALTER DATABASE ARCHIVE LOG;

In Oracle8i you can have multiple archive destinations and multiple archive processes.

LOG_ARCHIVE_MAX_PROCESSES = n Use to define multiple archiver processes.

For multiple archiver destinations,

LOG_ARCHIVE_DUPLEX_DEST = ‘dir’ OR

LOG_ARCHIVE_DEST_1 = ‘dir’; … LOG_ARCHIVE_DEST_5 = ‘dir’;

LOG_ARCHIVE_SUCCEED_DEST = n (number of destinations for successful log switch).