Oracle Database Basics

Physical Backups and Logical Backups: A backup is a copy of data from your database that can be used to reconstruct that data. Backups can be divided into physical backups and logical backups.

Physical backups are backups of the physical files used in storing and recovering your database, such as datafiles, control files, and archived redo logs. Ultimately, every physical backup is a copy of files storing database information to some other location.

Logical backups contain logical data (for example, tables etc.) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

Physical backups are the foundation of any sound backup and recovery strategy. Logical backups are a useful supplement to physical backups in many circumstances but are not sufficient protection against data loss without physical backups.

Recovery from Backup

The backup strategy determines how gracefully you recover the lost data when user error such as a manual mis-step or due to an error in application logic does cause data loss. Any database file can be vulnerable to a media failure. The appropriate recovery technique following a media failure depends on the files affected and the types of backup available.

Physical Database Structures Used in Recovering Data

·         Datafiles and Data Blocks

·         Redo Logs

·         Control Files

·         Undo Segments

Database Recovery Process: Reconstructing the contents of all or part of a database from a backup typically involves two phases:

1)    Restoring datafiles from backup- To restore a datafile or control file from backup is to retrieve the file onto disk from a backup location and make it available to the database server.

2)    Reapplying changes to the file since the backup from the archived and online redo logs, to bring the database to a desired SCN since the backup.

Complete, Incomplete and Point-In-Time Recovery: Complete recovery is recovering a database to the most recent point in time, without the loss of any committed transactions. In an Incomplete recovery, also known as point-in-time recovery, the goal is to restore the database to its state at some previous target SCN or time. For example, to undo the effect of a user error, such as dropping or deleting the contents of a table, you may want to return the database to its contents before the delete occurred.

ARCHIVELOG and NOARCHIVELOG Mode

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE NOARCHIVELOG;

The redo logs of your database provide a complete record of changes to the datafiles of your database (with a few exceptions, such as direct path loads).

Database can be run in one of two modes: ARCHIVELOG mode or NOARCHIVELOG mode. In ARCHIVELOG mode, Oracle copies filled online redo logs to one or more archive destinations before it can be reused. In NOARCHIVELOG mode, the online redo log groups are simply overwritten when the log is reused.

* Some performance overhead is associated with the background processes ARC0 through ARCn which copy filled online redo logs to the archiving destinations.

* In NOARCHIVELOG Mode, you cannot perform online backups of your database. You must shut your database down cleanly before you can take a backup.

Archived Redo Log: A copy of one of the filled members of an online redo log group made when the database is in ARCHIVELOG mode. After the LGWR process fills each online redo log with redo records, the archiver process (ARC0 through ARCn) copies the log to one or more redo log archiving destinations. This copy is the archived redo log.

Archived Redo Log Data Dictionary views:

-       V$ARCHIVED_LOG

-       V$PARAMETER
-       V$LOGHISTORY

Log Sequence Number: A number that uniquely identifies a set of redo records in a redo-log file, when Oracle fills one online redo log file and switches to a different one, it automatically assigns the new file a log sequence number.

Log Switch: A log switch is a point at which LGWR stops writing to the archive redo log file and switches to the next available redo log. LGWR switches when either the active log file is filled or you force a switch manually.

ALTER SYSTEM SWITCH LOGFILE;

Mirroring: Maintaining identical copies of data on one or more disks. Mirroring is performed on duplicate hard disks at the operating system level, so that if one of the disks becomes unavailable, the other disk can continue to service requests without interruptions. When mirroring files, Oracle writes once while the operating system writes to multiple disks; when multiplexing files, Oracle writes the same data to multiple files.

Multiplexing-

-       online redo logs: The automated maintenance of more than one identical copy of the online redo log.

-       control file: The automated maintenance of more than one identical copy of a database's control file.

-       archived redo logs: The Oracle archiver process is able to archive multiple copies of a redo log.

RESETLOGS: A method for opening a database that archives any current online redo logs (if using ARCHIVELOG mode), resets the log sequence number to 1, and clears the online redo logs.

An OPEN RESETLOGS operation is required after incomplete recovery or recovery with a backup control file.

System Change Number (SCN): A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.

SYSTEM tablespace: It contains Oracle data dictionary for a database, which is the metadata that describes the complete contnents of the database. All datafiles contained in this tablespace must be online for Oracle to function.

Transportable tablespace: Transporting a tablespace into a database is like creating a tablespace with preloaded data. This is a feature that transports a set of tablespaces from one database to another, or from one database to itself.

    
Undo tablespace: A dedicated tablespace that stores only undo information when a database is run in Automatic Undo Management mode.

SYSAUX tablespace - whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. This history information is stored in SYSAUX tablespace. The MMON (Manageability Monitor) performs the automatic purge and removes all stats history older than - current time - statistics history retention (by default 31 days) or time of recent analyze in the system - 1 . MMON by default performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. MMON will do this activity once in 24 hrs. If the operation takes more than 5 minutes, then it is aborted and stats is not purged.

SYSTEM tablespace - every oracle database has a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

* Once a tablespace is locally managed, it cannot be reverted back to being dictionary managed.

Default Temporary Tablespace - it is used as an intermediate storage while processing transactions e.g. sort operations etc. Wwhen the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.