Basic Tablespace Recovery Option

The Recover Tablespace option is used to recover all datafiles needing recovery in a tablespace up to the point of failure, synchronizing them with the other datafiles (complete recovery only). Recover Tablespace is performed from the OPEN state after taking the tablespace OFFLINE (which brings its datafiles offline). Since the SYSTEM tablespace CANNOT be taken offline, you cannot use Recover Tablespace on SYSTEM. The basic steps while the database is open are: offline the tablespace, restore the datafiles, recover tablespace, and online the tablespace, as shown below:

$ sqlplus “/ as sysdba”
SQL> alter tablespace development offline immediate;
Using Immediate rolls back currently pending transactions.
SQL> !cp -p /u03/oradata/prod1/devl_PROD* /u03/oradata/PROD
SQL> recover automatic tablespace development;
SQL> alter tablespace development online;

Doing a Recover Tablespace after opening the database can be used to bring up your database for use before doing a recovery. The basic steps are: mount, offline the bad datafiles, open, offline the tablespace, restore the datafiles, recover tablespace, and online the tablespace, as shown below:

$ sqlplus “/ as sysdba”
SQL> startup mount
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ offline;
SQL> alter database open;
SQL> alter tablespace development offline;
SQL> !cp -p /u03/oradata/prod1/devl_PROD* /u03/oradata/PROD
SQL> recover automatic tablespace development;
SQL> alter tablespace development online;

If a write error occurs on one of the datafiles when you offline a tablespace, use “alter tablespace tsname offline temporary;”. Then, you can run a recovery on the datafile. If all datafiles in an offlined tablespace have write errors, use “alter tablespace tsname offline immediate;”. Then, you can run a recovery on the tablespace.

Basic Datafile Recovery Option

The Recover Datafile option is used to recover the specified datafile up to the point of failure, synchronizing it with the other datafiles (complete recovery only). Recover Datafile is performed either from the MOUNT state (after a shutdown), with the datafile ONLINE or OFFLINE, or from the OPEN state, with the datafile OFFLINE. A bad datafile must be taken offline before the database can be opened. Since the SYSTEM tablespace CANNOT be taken offline, you cannot use Recover Datafile from the OPEN state on SYSTEM.

The basic steps for the MOUNT state are: restore the datafile, mount, recover datafile (using automatic here instead of autorecovery), online the datafile if needed, and open, as shown below:

$ cp -p /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
$ sqlplus “/ as sysdba”
SQL> startup mount
SQL> recover automatic datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’;
SQL> select * from v$datafile;
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ online;
SQL> alter database open;

The basic steps for Recover Datafile from the OPEN state (except for SYSTEM) while the rest of the database is still up and running, are: offline the datafile, restore the datafile, recover datafile, and online the datafile, as shown below:

$ sqlplus “/ as sysdba”
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ offline;
SQL> !cp -p /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
SQL> recover automatic datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ online;

Doing a Recover Datafile from the OPEN state (except for SYSTEM) can also be used to bring up your database for use before doing a recovery. The basic steps are: mount, offline the datafile, open, restore the datafile, recover datafile, and online the datafile, as shown below:

$ sqlplus “/ as sysdba”
SQL> startup mount
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ offline;
SQL> alter database open;
The database is now available to the users, except for that datafile.
SQL> !cp -p /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
SQL> recover automatic datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ online;

You can still access data in tables in a tablespace which has an offlined datafile, just so long as the data is in one of the other datafiles of the tablespace and the table header is not in the offlined datafile.

Moving/Renaming datafile to another location

You can move a datafile to another disk, or change it’s name (such as for restoring a copy of it from your backup tape to that other location in the event of a disk failure or moving it to another disk that has more room for it) by making the move after shutting down the database, and using the “alter database rename file” command to tell Oracle where the file has been moved to, as in the following example, which moves one of the development datafiles from disk u03 to disk u04:

$ sqlplus “/ as sysdba”
SQL> shutdown immediate
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> startup mount
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’ to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter database open;
SQL> select * from v$datafile;
Lists the current datafiles, showing your changes.

This can also be done without shutting down the database, but taking the associated tablespace offline first (which prevents others from accessing that tablespace’s tables, indexes, and other data):

$ sqlplus “/ as sysdba”
SQL> alter tablespace development offline;
SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD
SQL> alter database rename file ‘/u03/oradata/PROD/devl_PROD_01.dbf’ to ‘/u04/oradata/PROD/devl_PROD_01.dbf’;
SQL> alter tablespace development online;
SQL> select * from v$datafile;

Control file:

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:

* Database information (RESETLOGS SCN and their time stamp)
* Archive log history
* Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
* Redo threads (current online redo log)
* Database’s creation date
* database name
* current archive log mode
* Log records (sequence numbers, SCN range in each log)
* RMAN catalog
* Database block corruption information
* Database ID, which is unique to each DB

The location of the control files is specified through the control_files init param.

Backing up Controlfiles:When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.Because control files keep track of datafiles the control file should be backed up whenever a change is made to the database (alter database).
The command to back the control file up is: alter database backup controlfile to ‘filename’.

Recovering database when controlfile is lost:
If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated (with create controlfile whose syntax is a bit awkyard.
In order to recover from a lost spfile, the DBID is required.

Size of control files:
The size of the control files is governed by the following parameters:

* maxlogfiles
* maxlogmembers
* maxloghistory
* maxinstances
* control_file_record_keep_time

Sections:The controlfile contains the following sections:

* ARCHIVED LOG (reusable)
* BACKUP CORRUPTION (reusable)
* BACKUP DATAFILE (reusable)
* BACKUP PIECE (reusable)
* BACKUP REDOLOG (reusable)
* BACKUP SET (reusable)
* BACKUP SPFILE
* CKPT PROGRESS
* COPY CORRUPTION (reusable)
* DATABASE
* DATAFILE
* DATAFILE COPY (reusable)
* DATAFILE HISTORY
* DATABASE INCARNATION
* DELETED OBJECT (reusable)
* FILENAME
* FLASHBACK LOG
* INSTANCE SPACE RESERVATION
* LOG HISTORY (reusable)
* MTTR
* OFFLINE RANGE (reusable)
* RECOVERY DESTINATION
* REMOVABLE RECOVERY FILES
* RMAN STATUS
* RMAN CONFIGURATION
* REDO THREAD
* REDO LOG
* TABLESPACE
* TEMPORARY FILENAME
* THREAD INSTANCE NAME MAPPING
* PROXY COPY

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.

Oracle processes

In addition to the SGA region in RAM memory, an Oracle instance also consists of numerous background processes. It is important to remember that an Oracle database is really a very large program running on the server. When the Oracle program needs to perform a specific task, it will spawn a factotum (slave) task to service the task.

Advanced queuing: Aq_tnxx:
These are the Oracle8 advanced queuing processes that are used to thread processes through the Oracle8 instance.

Archive Monitor:ARCHMON:
This is a process on UNIX that monitors the archive process and writes the redo logs to the archives.

Archiver Process:ARCH:
This process is only active if archive logging is in effect. This process writes the redo log data files that are filled into the archive log data files.The archive process is optional process. If your database is in archive log mode and automating archiving is enabled, then this process is active. This process writes filled redo log files to archive log location specified by you. It is required while recovering database. LGWR is responsible for starting multiple ARCH process if workload increase. There can be up to 10 archiver processes.

Callout queues:EXTPROC
There will be one callout queue for each session performing callouts.

Checkpoint processes:CKPxx:
These are the checkpoint processes that can be started to optimize the checkpoint operation for Oracle logging.The checkpoint process regularly initiates a checkpoint, and updates controlfile and datafile header. This process flushes the redo entries by means of LGWR. Uses DBWR to write all dirty buffer to datafiles and updates datafile header.

Database Writer:DBWR
This process handles data transfer from the buffers in the SGA to the database files.The database writer process writes dirty blocks from database buffer cache to datafiles. Dirty blocks are those blocks which are modified by server process, and need to be flushed out to make room for new blocks in cache.DBWR never ever modify database buffer cache, it is done by server process. DBWR writes to datafiles when space is needed in cache, on commit and when forceful checkpoint is occurred. You can configure more than one DBWR process up to 10.

Dispatchers:Dnnn
This process allows multiple processes to share a finite number of Oracle servers. It queues and routes process requests to the next available server.

Distributed Recoverer:RICO
This is an Oracle process that resolves failures involving distributed transactions.

Listener (SQL*Net v1): ORASRV
If you are running SQL*Net version 1, this process will be running to service TWO_TASK requests. This parameter was obsoleted in Oracle8.

Listener (Net8):TNSLSNR
If you are running TCP/IP, this process, known as the TNS listener process, will be running.

Lock Processes:LCKn
This process is used for interinstance locking in an Oracle Parallel Server environment.

Log Writer:LGWR
The log writer process writes redo log buffer entries (redo entries) into online redo log files on disk. This is done by LGWR when transaction is committed, every 3 seconds and when DBWR signals LGWR to do so.

Parallel Query:Pnnn
These background processes are started when Oracle performs a full table scan on a table that is defined as PARALLEL. There will be one background process for each Parallel Query slave, as defined by DBA_TABLES.DEGREE

Process Monitor:PMON
This process recovers user processes that have failed and cleans up the cache. This process also recovers the resources from a failed process. It checks if any user process fails, it cleans up all resources that user process has acquired,and roll back uncommitted transaction and releasing locks. In shared server environment, PMON restarts any failed dispatcher or server process.

Servers:Snnn
This process makes all the required calls to the database to resolve user requests. It returns results to the Dnnn process that calls it.

Snapshot queues:Snpxx
These are snapshot process queues.

System Monitor:SMON
This process performs instance recovery on instance startup and is responsible for cleaning up temporary segments. In a parallel environment, this process recovers failed nodes.It also merges contiguous areas of free space in datafile, this process is called as coalescing. In RAC in non-failed instance, SMON performs instance recovery of other failed instance.
MMAN:

The MMAN dynamically adjust the size of SGA components. It is new process added to oracle 10g a a part of “Automatic Shared Memory Management”.