Saturday, 6 February 2016

Understanding of Oracle database administration

Understanding of Oracle database

What is Database?

A Database is a system which stores Data in an organized way so that it can be retrieved and modified as needed.

What is DBMS?

DBMS is an acronym for Database Management System. DBMS is a collection of programs that enables you to store, modify and extract inform from database.
The DBMS is perhaps most useful for providing a centralized view of data that can be accessed by multiple users, from multiple locations, in a controlled manner. A DBMS can limit what data the end user sees, as well as how that end user can view the data, providing many views of a single database schema.

Introduction of DBA:

What Is a DBA? What are roles and responsibilities of DBA?

Every organization using a database management system (DBMS) to manage data requires a database administration group to ensure the effective use and deployment of the company’s databases. Since most modern organizations of any size use a DBMS, the need for a database administrator (DBA) is greater today than ever before. However, the discipline of database administration is neither well understood nor universally practiced in a coherent and easily replicated manner.
DBAs, more than most, need to acquire exceptional communication skills.Data is the lifeblood of computerized applications. Application programs are developed to read and write data, analyze data, move data, perform calculations using data,modify data,and so on. Without data,there would be nothing for the programs to do. The DBA is at the center of the development life cycle—ensuring that application programs have efficient, accurate access to the corporation’s data. As such, DBAs frequently interface with many different types of people: technicians, programmers, end users, customers, and executives

The DBA is responsible for managing the overall database environment. Often this includes installing the DBMS and setting up the IT infrastructure to allow applications to access databases. These tasks need to be completed before any application programs can be implemented. Furthermore, ad hoc database access is a requirement for many organizations. Additionally, the DBA is in charge of setting up an ad hoc query environment that includes evaluating and implementing query and reporting tools, establishing policies and procedures to ensure efficient ad hoc queries, and monitoring and tuning ad hoc SQL.The DBA is “in demand” for his knowledge of data and the way in which data is managed by modern applications.
The DBA maintains production and test environments, monitors active application development projects, attends strategy and design meetings, selects and evaluates new products

DBA Tasks are 

  • Ensuring that an organization’s data and databases are useful,usable,available,and correct requires the DBA to perform a variety of tasks in a variety of areas.
  • Database Design
  • Performance Monitoring and Tuning
  • Availability
  • Database Security and Authorization
  • Backup and Recovery
  • Data Integrity
  • DBMS Release Migration 

Oracle DBA Basics:  Introduction to Database Objects

What is a Table?

Table in database refers to data arranged in rows and columns. Same like spreadsheets.

What is an Index?

Indexes are used to search any key in the table quickly. Same as you have index section in your text books, if you would like to go to particular topics, you would simply refer the index and directly go the page number.; Skipping all other pages.

What is a View?

View is a result set of any query on the table/ multiple tables/ or let’s says data.

What is Unique Key?\

A unique key is a column in a table that doesn’t have/allows duplicate values into the column. Unique Key may contain null value.

What is a Primary Key?

A primary key is unique for each record in the column. It must have all unique values but cant have null value.

What is a Foreign Key?

A foreign key is a column in a table that does NOT uniquely identify rows in that table, but is used as a link to matching columns in other tables to indicate a relationship.

What is a RDBMS?

RDMS stands for Relational Database Management System. RDBMS is the DBMS that stores data in the form of related tables. Tables may have relationship with each other via primary key and foreign key.

Oracle DBA Basics: Database Languages

Data Definition Language

As the name suggests, DDL commands are the ones used to change the structure of database objects like tables, views, mview, store procedure.DDL are performed on database objects.
Examples are create statement, drop statement, etc. Refer below links for detail.

Data Manipulation Language

DML is used for tampering/playing with the data on database objects. DML statements are used to change data on database objects. Like if you want to insert , update or delete any data; that is DML operation.DML are performed on data residing on database objects.

Data Control Language

DCL is used for controlling user privileges on oracle database objects.There are two commands:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

Oracle DBA Basics: 

Overview of Oracle Database Architecture

Oracle database architecture consists of 3 major components
  • Memory structures: Memory structure consists SGA. SGA has following components shared pool, database buffer cache, redo log buffer, large pool, java pool.
  • Background Processes: Background process has 5 mandatory processes DBWR, LGWR, CKPT, SMON and PMON.
  • Physical files: Physical files consist of data files, control files, redo log files, password file, pfile/spfile and archive logs.

Oracle Physical Storage Structures

Data files: 

A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace.

Control files: 

A control file is a root file that tracks the physical components of the database. The control file contains the database name, data about the database log files. Oracle cannot function without valid control files.

Online redo log files: 

The online redo log is a set of files containing records of changes made to data. Each and every change is captured.

Oracle Logical Data Structures

Data Blocks: 

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk. You set the data block size for every Oracle database when you create the database. This data block size should be a multiple 
of the operating system's block size within the maximum limit. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.


The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that is allocated for storing a specific type of information.


The level of logical database storage above an extent is called a segment. A segment is a set of extents that have been allocated for a specific type of data structure, and that all are stored in the same tablespace. For example,each table's data is stored in its own data segment, while each index's datails stored in its own index segment. Oracle allocates space for segments in extents. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk. The segments also can span files, but the individual extents cannot.

What is a Raw Device?

A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

What is a File System?

A filesystem is the methods and data structures that an operating system uses to keep track of files on a disk or partition; that is, the way the files are organized on the disk. The word is also used to refer to a partition or disk that is used to store the files or the type of the filesystem. 

What is an Oracle ASM?

Automatic Storage Management (ASM) is oracle’s logical volume manager, it uses OMF (Oracle Managed Files) to name and locate the database files. It can use raw disks, filesystems or files which can be made to look like disks as long as the device is raw.
Oracle ASM being a vast topic. We will discuss in details in the Oracle DBA Advanced.

What is a PFILE?

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

What is a SPFILE?

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

What is an Oracle instance?

When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance.

What is an Oracle database?

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

How does an Oracle database start?

There are 3 modes when Oracle database start 

Nomount Stage

During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured. After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started.
SQL> startup nomount;

Mount Stage

When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.
In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.
SQL> alter database mount;

Open Oracle Stage

The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.
SQL> alter database open;

What is Listener? 

The Oracle Net Listener is a service that runs only on the server and listens for incoming connection requests. Oracle provides a utility called lsnrctl to manage the listener process. Here’s a summary of how the listener fits into Oracle networking:
  • The database registers information about the services, instances, and service handlers with the listener.
  • The client makes the initial connection with the listener.
  • The listener receives and verifies the client connection request and forwards it to the service handler for the database service. Once the listener hands off the client request, the listener is out of the picture for that connection.
The listener.ora file, whose default location is the $ORACLE_HOME/network/admin directory on UNIX systems and the $ORACLE_HOME\network\admin directory on Windows systems, contains the configuration information for the listener. Because the listener service is run only on the server, there is no listener.ora file on the client machines. Below shows a typical listener.ora file. All the configuration parameters in listener.ora have default values, and you don’t have to configure a listener service manually anymore. After the first database on the server is created, the listener service automatically starts, and the listener configuration file, listener.ora, is placed in the default directory. Upon the creation of a new database, the database’s network and service information is automatically added to the listener’s configuration file. Upon instance startup, the database registers itself automatically with the listener, and the listener starts listening for connection requests to this database.

Automatic Service Registration

The Oracle PMON process is in charge of the dynamic service registration of new Oracle database service names with the listener—when you create new Oracle databases, they’ll automatically register themselves with the listener service. The PMON process will update the listener.ora file after each new database is created on a server. For automatic service registration, the init.ora file or the SPFILE should contain the following parameters:
  • SERVICE_NAMES (for example,
  • INSTANCE_NAME (for example, sales)
If you don’t specify a value for the SERVICE_NAMES parameter, it defaults to the global database name, which is a combination of the DB_NAME and DB_DOMAIN parameters. The INSTANCE_NAME parameter’s value defaults to the SID entered during Oracle installation or database creation. You can check the status of the listener on the server by using the lsnrctl utility, as shown below. The output shows how long the listener has been up and where the configuration file for the listener service is located. It also tells you the names of the databases for which the listener is “listening” for connect requests.

Status can have one of the following values:

  • READY: The instance can accept connections.
  • BLOCKED: The instance cannot accept connections.
  • UNKNOWN: The instance is registered in the listener.ora file rather than through dynamic service registration. The status is thus unknown.

Listener Commands:

You can run other important commands besides the status command after invoking the lsnrctl utility, reload the listener on the fly, without your having to bounce it. Currently connected clients will continue to be connected while the listener is being reloaded (or even bounced) because the listener has already “handed off” the connections to the database and isn’t involved between the client and the database service.

How to login to Oracle database?

To connect oracle database sql prompt in Windows or Linux environment, very first, we need to set the path of oracle binaries.

For Windows environment:

  • Select Computer from the Start menu
  • Choose System Properties from the context menu
  • Click Advanced system settings > Advanced tab
  • Click on Environment Variables, under System Variables, find PATH, and click on it.
  • In the Edit windows, modify PATH by adding the location of the class to the value for PATH. 
  • Update Path=”Your Oracle installion path”
  • Example : Path=C:\Oracle11gR2\app\product\11.2.0\db_home\bin;
  • Go to command prompt and type sqlplus “sys as sysdba” and you will be connected to sql prompt

For Linux or Unix environment:

  • export ORACLE_HOME =/u01/app/oracle/product/
  • export ORACLE_SID=ORCL 
  • sqlplus “sys as sysdba”
  • Note: Considering ORCL is database instance name

Oracle Database from Web applications and other clients?

To make an Internet connection to an Oracle database, the web browser on the client communicates with the web server and makes the connection request using the HTTP protocol. The web server passes the request along to an application, which processes it and communicates with the Oracle database server using Oracle. Some important terms that are crucial in Oracle networking.

Database Instance Names: 

As you know by now, an Oracle instance consists of the SGA and a set of Oracle processes. The database instance name is specified in the initialization file (init.ora) as the INSTANCE_NAME parameter. When you talk about the Oracle system identifier (SID), you are simply referring to the Oracle instance. Normally, each database can have only one instance associated with it. In an Oracle Real Application. Clusters (RAC) configuration, however, a single database could be associated with multiple instances.

Global Database Names:

The global database name uniquely identifies an Oracle database and is of the format database_name.database_domain, for example, In this global database name, “sales” is the database name and “” is the database domain. Since no two databases in the same domain could have the same database name, every global database name is unique.

Database Service Names:

To a client, the database logically appears as simply a service. There is a many-to-many relationship between services and databases, since a database can be represented by one or more services, each dedicated to a different set of clients, and a service can cover more than one database instance. You identify each database in your system by its service name, and you specify the service name of a database with the SERVICE_NAMES initialization parameter. The service name parameter’s value defaults to the global database name. Note that a database can be addressed by more than one service name. You may do this when you want different sets of clients to address the database differently to suit their particular needs.
For example, you can take the same database and create two service names like the following:
The sales people will use the service name, and the service name will be used by the accounting and finance departments.

Connect Descriptors:

To connect to any database service in the world from your desktop, you need to provide two bits of information:
  • Name of the database service
  • Location of the address
Oracle uses the term connect descriptor to refer to the combined specification of the two necessary components for a database connection: a database service name and its address. A connect descriptor address portion contains three components: the communications protocol used for the connection, the host name, and the port number. Knowing the communication protocol helps ensure that the networking protocols agree, so you can establish a connection. The standard protocol is TCP/IP or TCP/IP with Secure Sockets Layer(SSL). The standard port number for Oracle connections on UNIX servers is either 1521 or 1526. The default port on Windows machines is 1521. Because you can’t have more than one database with the same service name on any host, an Oracle database service name and a host name will uniquely identify any Oracle database in the world.

Tablespaces in Oracle

A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments.
Whenever we create a database using DBCA, oracle by default creates 5 tablespaces:
  1. System - for dictionary info
  2. Sysaux - Reporting data
  3. Undo – Pre image data
  4. Temp - Session temporary data
  5. Users - Actual Data

How to create tablespace?

Create tablespace tab_demo datafile ‘+DATA’ size 10m autoextend on next 5m maxsize 50m;
where (below Keywords with their Usage)
  • tab_demo - Name of the tablespace
  • +DATA - Nome of the asm diskgroup
  • size - Initial size of tablespace
  • autoextent - Once tablespace reaches initial space it will automatically increase size by 5m till it reaches maxsize

How to add datafile to the existing table space?

Alter tablespace tab_demo  add datafile ‘+DATA’ size 2m autoextend on next 5m maxsize 50m;

How to resize existing datafile?

Alter database datafile '+DATA/test/datafile/tab_demo.1155.8769904949' resize 100M;

Removing Tablespaces

We can remove the tablespace by drop table space, but tables space is not simply drop if it consists of content, or sometime tablespace may drop but datafile may exists, consumes space, so need to drop datafile also.

Ways to drop database:

  1. Drop tablespace tab_demo; (This will drop the tablespace but datafiles will be there physically occupying the space)
  2. Drop tablespace tab_demo including contents and datafiles;  (This will drop tablespace and remove all the datafiles)

Oracle User Management

Oracle user creation:

The DBA creates the users in the database and sets limits on their access to the various components. The DBA also limits the physical space and system resources that the users can use, generally by assigning database roles and setting privileges.We must assign a specific tablespace to user, if not then it will take system tablespace by default, if user use the complete tablespace then the SYS super user will not be able to create any new objects.
Users info is stored in dba_users. To get all the column field name of dba_users, use
desc dba_users;

Create User:

You use the CREATE USER statement to create a user
CREATE USER user_name
DEFAULT TABLESPACE tablespace_nanme

Example :
account unlock;

Once user is created you need to run below grant statement.
grant create session to UserF;
grant connect,resource to UserF;

Drop User:

To drop a user, you use the DROP USER statement.
Drop user username;
Drop user UserF cascade;

Roles and Privileges in Oracle

Privilege is a right to execute particular type of sql statement of to execute a database object owned by other user. Privileges and Roles are required to control the database access.
Types of privileges:
  • System Privileges
  • Object Privileges

System Privileges:

When we grant system privileges to a user, user can use the privileges at the same time, means system privileges works in dynamic fashion. To the new user, either grant create session or connect role to the new user in order to provide connectivity the user.
The most important system privileges are:
  1. create session : (A user cannot login without this privilege. If User tries, User gets an ORA-01045).
  2. create table
  3. create view
  4. create procedure
  5. sysdba 
  6. sysoper

Object Privileges: 

Privileges granted on database objects.
Privileges can be assigned to the following types of database objects:
Tables (select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all)
Views (select, insert, update, delete, under, references, flashback, debug)
Sequence (alter, select)
Packeges, Procedures, Functions ,Java classes, sources..,etc (execute, debug)
Materialized Views (delete, flashback, insert, select, update)
Directories (read, write)
Libraries (execute)
User defined types (execute, debug, under)
Operators (execute)
Indextypes (execute)

Predefine roles

  • CONNECT role: Earlier connect role has several other privileges, but now it has only single CREATE privilege.
  • DBA role: Includes all the privileges with ADMIN GRANT OPTION

Creating A role:

Syntax : Create role new_role;
The role created is empty, we need to assign privileges to this role as:
  • GRANT connect to new_role
  • GRANT SELECT ANY TABLE to new_role
Now new_role contains connect and select any table privileges.We directly assign these roles to the user as:
Grant new_role to username
Granting role using WITH ADMIN OPTION it will do the following:
  • Grant the ROLE to or REVOKE it from any user
  • Grant the role with the WITH ADMIN OPTION
  • ALTER or DROP the role

What is Oracle Schema?

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.
You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

Oracle Database Dictionary

Metadata is data about data or data that defines other data. The Oracle data dictionary is metadata about the database. For example, if you create a table in Oracle, metadata about that table is stored in the data dictionary. Such things as column names, length, and other attributes are stored. Thus, the data dictionary contains a great volume of useful information about your database. Pretty much everything you would want to know about your database is contained in the data dictionary in some form.
The data dictionary is full of 'Metadata', information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. The dictionary views come in two primary forms:
The DBA, ALL or USER views
These views are used to manage database structures.
The V$ Dynamic Performance Views
These views are used to monitor real time database statistics
There are hundreds of views in the data dictionary.  To see the depth of the data dictionary views, here are the views that store data about Oracle tables:
  • dba_all_tables
  • dba_indexes
  • dba_ind_partitions
  • dba_ind_subpartitions
  • dba_object_tables
  • dba_part_col_statistics
  • dba_subpart_col_statistics
  • dba_tables
  • dba_tab_cols
  • dba_tab_columns
  • dba_tab_col_statistics
  • dba_tab_partitions
  • dba_tab_subpartitions

Oracle Backup and Recovery

Database Backup: 

A backup, or the process of backing up, refers to the copying and archiving of database data so it may be used to restore the original after a data loss event. Database Backup provide the basis of all databases recoveries—no backup, no recovery.

Purpose of Backup

The purpose of a backup and recovery strategy is to protect the database against data loss and reconstruct the database after data loss. Following are the reason of data loss:
  • Instance Failure: Usually connected with an Oracle process failure.
  • Media Failure: Disk failure, storage array controller failure etc.
  • Block Corruption: Usually caused by bugs in Oracle software.
  • Human error: In most cases accidentally deleted/updated data.
  • Disaster: Fire, flood, earthquake etc.

Introduction to some backup terms: Archivelog and Noarchivelog Modes

Archivelog Mode: 

In this mode, Oracle saves (archives) the filled redo logs. Thus, no matter how old the database backup is, if you are running in archivelog mode, you can recover the database to any point in time using the archived logs

Noarchivelog Mode: 

In this mode, the filled redo logs are overwritten and not saved. Thenoarchivelog mode thus implies that you can restore only the backup, and you’ll lose all the changes made to the database after the backup was performed. The noarchivelog mode of operation means that you can recover from a crash of only the database instance. If there is a media failure (for example, a loss of a disk), a database in noarchivelog mode may be restored from a backup, but it will lose all changes made to the database since the backup was made.

Introduction to some backup terms: Whole and Partial Database Backups

You can back up either an entire database or part of it, such as a tablespace or a datafile. Note that you can’t back up a partial database if the database is running in noarchivelog mode, unless all the tablespaces and files in the partial backup are read-only. You can make a whole database backup in either archivelog or noarchivelog mode. The most commonly performed backup is the whole database backup, and it consists of all the datafiles and one other important file: the control file. Without the control file, Oracle will not open the database, so you need the latest backup of the control file along with all the datafile backups for recovery.

Introduction to some backup terms: Consistent and Inconsistent Backups

The difference between consistent and inconsistent backups is simple. A consistent backup doesn't need to go through a recovery process. When a backup is used to recover a database or a part of a
database (such as a tablespace or a datafile), first you need to restore the backup, and then you recover the database. In the case of a consistent backup, you don’t have to perform any recovery steps. An inconsistent backup, on the other hand, always needs to undergo a recovery.

Introduction to some backup terms: Open and Closed Backups

Online or open (or hot/warm) backups are backups you make while the database is open and accessible to users. You can make an online backup of the entire database (or a tablespace or datafile) as
long as the database is being run in archivelog mode. You can’t make an online backup if the database is running in noarchivelog mode. A closed backup of a database, also called a cold backup, is made while the database is shut down. A closed backup is always consistent, as long as the database wasn’t shut down with the SHUTDOWN ABORT command.

Introduction to some backup terms: Physical and Logical Backups

Technically speaking, you can divide Oracle backups into logical and physical backups. Logical backups are backups made using the Data Pump Export utility, and they contain logical objects like tables and procedures. These backups are in proprietary binary form, and their data can be extracted only by using Oracle’s own Data Pump Import utility. Physical backups refer to the backing up of the key Oracle database files: datafiles, archived redo logs, and control files. Physical backups are made on disk or on tape drives.
Types of Backup
  • Full Backup
  • Incremental Backup 
  • Cumulative Backup
  • Differential Backup

Incremental Differential Backup 

Backup Level: Whole Database

You back up all files including the control file. This level is applicable to both archivelog and noarchivelog modes of operation.
For Example: If you want to back up the entire database, you use the BACKUP DATABASE command. RMAN will automatically back up all the datafiles that are part of the database.
Backing Up a Database Using RMAN

Backup Level : Tablespace Backups

You back up all the datafiles belonging to a tablespace. Tablespace backups are applicable only in archivelog mode. For Example: If you want to back up individual tablespace and you are operating the database in archivelog mode:

Backup Level :Datafile Backups

You can back up a single datafile. Datafile backups are valid in archivelog mode only. To backup a datafile, you need command BACKUP DATAFILE filename or, optionally, specifying the destination as well.
RMAN> BACKUP DATAFILE '/u01/orcl/oradata/datafile1.dbf';

Planning a Backup Strategy

Planning an efficient backup strategy will mean two important things:
  • You have all required backup files preferably on disk for a quick restoration and recovery.
  • You minimize the space requirements by deleting obsolete backups and keeping only the required backup files on hand.

User Managed Backup

One Oracle backup strategy is called user-managed backups. With user-managed backups, you basically have a shell script that is written by the user -- it is not provided by Oracle at all -- that utilizes built-in commands inside the Oracle database. This includes everything from shutting down the instance to just placing the database in backup mode. Oracle has actually added features to this over time.
So with a user-managed backup, you basically either put it into offline mode or you put it in hot backup mode and then back up the files. When you're done, you have to start the database up and take it out of backup mode. DBA will write these scripts and essentially dump the database by copying the files over after they've put them into backup mode or shut them down. Then they'll put them into a directory and the backup person will back up these files.

Challenges with user-managed backups:

  1. It will write these scripts and essentially dump the database by copying the files over after they've put them into backup mode or shut them down. Then they'll put them into a directory and the backup person will back up these files.
  2. Your backup software is just backing up files, and it doesn't necessarily know that those files are any good.
  3. It doesn't know if the DBA's shell script worked or didn't work. That disconnect between the backup process and the database process is the biggest challenge with user-managed backups.
  4. On the restore side, you're biggest challenge is that you're also managing the restore. It is up to you to start the database at various modes and manually restore files from tape or disk.


Introduction to RMAN: 

Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files.
The RMAN environment consists of the utilities and databases that play a role in backing up your data. At a minimum, the environment for RMAN must include the following components:

A target database: 

An Oracle database to which RMAN is connected with the TARGET keyword. A target database is a database on which RMAN is performing backup and recovery operations. RMAN always maintains metadata about its operations on a database in the control file of the database. The RMAN metadata is known as the RMAN repository.

The RMAN client: 

An Oracle Database executable that interprets commands, directs server sessions to execute those commands, and records its activity in the target database control file. The RMAN executable is automatically installed with the database and is typically located in the same directory as the other database executable. For example, the RMAN client on Linux is located in $ORACLE_HOME/bin.
Some environments use the following optional components:

A fast recovery area: 

A disk location in which the database can store and manage files related to backup and recovery. You set the fast recovery area location and size with the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters.

A media manager: 

An application required for RMAN to interact with sequential media devices such as tape libraries. A media manager controls these devices during backup and recovery, managing the loading, labeling, and unloading of media. Media management devices are sometimes called SBT (system backup to tape) devices.

A recovery catalog:

A separate database schema used to record RMAN activity against one or more target databases. A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file. The database may overwrite older records in the control file, but RMAN maintains records forever in the catalog unless the records are deleted by the user.

Advantages of RMAN

  • Supports incremental backup strategies
  • RMAN on-line backups are not so heavy for the system as manual on-line backups
  • RMAN can detect corrupted blocks
  • RMAN automatically track database structure changes
  • Provides easy, automated backup, restore and recovery operations
  • Keeps invenotory of taken backups
  • Can seamlessly work with third party media managers 

Important RMAN terms:

RMAN uses some special terminology. To use RMAN effectively, you need a good understanding of the terms discussed in the following sections.

Backup Piece A backup piece is an operating system file containing the backup of a datafile, a control file, or archived redo log files. This backup information is stored in an RMAN-specific format.

Backup Set A backup set is a logical structure that consists of one or more RMAN backup pieces (the default is one backup piece per backup set). You can create a backup set on disk or tape. If you back up a database, datafile, tablespace, or archivelog, RMAN groups the complete set of relevant backup pieces into one backup set. When you issue the backup command, RMAN creates the backup set to hold the output. Remember that a backup set is a file or set of files in a proprietary format that only RMAN can understand. Thus, only RMAN is able to use the backup sets to recover the database.By default, RMAN creates a backup set when you use a backup command, whether you are copying to disk or tape (through a media manager).

Image Copy Image copies are similar to the copies you can make of operating system files with the cp commandin UNIX or the copy command in DOS. You can make image copies of datafiles, control files, and archived redo log files. RMAN image copies can be made only to disk; they can’t be made to tape.RMAN can also use copies that you make using non-RMAN operating system utilities.

These types of copies are called user-managed copies or operating system copies. Really, there’s no difference between RMAN image copies and normal copies made with the cp command, for example, except that image copies made through the RMAN tool have information about them written to the control file or the recovery catalog. If you use an operating system command such as dd to produce image copies, you can then use the RMAN CATALOG command to record these copies in the RMAN repository. Thus, you can use a manually copied datafile during a recovery, if you first use the CATALOG command to register the file with RMAN. You can then use these user-made copies of datafiles in RMAN operations through the RESTORE and SWITCH commands. You use the RMAN command BACKUP AS COPY to make image copies. You may also direct RMAN to always produce image copies rather than backup sets (thus changing the default behavior of making backup sets) by performing the following configuration change:


New RMAN configuration parameters:
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
starting full resync of recovery catalog
full resync complete
You can use the image copies produced by the RMAN BACKUP AS COPY command just like any other file copies made with operating system utilities.

Channel An RMAN session must use some kind of a connection to the server to perform backup and recovery work, and channels represent those connections. Channels specify the specific device, disk, or tape that will be used for the backup or recovery. You can either have preconfigured channels (somewhat like default channels) or specify the channel manually. You can use automatic channel allocation to configure channels persistently across sessions. In the following examples, the default device is set to an SBT in the first case and to disk in the second case:
These devices are made part of the RMAN configuration, and until they are changed again through the use of the CONFIGURE command, they remain the default device types for all RMAN sessions. You can manually set the channel type by using the ALLOCATE CHANNEL command. The following command sets the device to sbt, which indicates a sequential tape device. Note that the example uses a RUN block for allocating the channel. A RUN block is used in RMAN when you need to set up the environment for the statements within the block:
 backup database;

RMAN Architecture:

 The Above figure Illustrates all possible components of  RMAN like: Media Manager/Management subsystem for tape backup, Recovery Catalog database (separate database), RMAN Client & OEM.  The figure shows that the primary database, standby database, and recovery catalog databases all reside on different computers. The primary and standby database hosts use a locally attached tape drive. The RMAN client and Enterprise Manager console run on a separate computer.

Database recoveries:

Introduction to Recovery: 

Data recovery is a process of retrieving inaccessible data from corrupted or damaged secondary storage, removable media or files, when the data they store cannot be accessed in a normal way. The data is most often retrieved from storage media such as internal or external hard disk drives (HDDs), solid-state drives (SSDs), USB flash drives, magnetic tapes and other electronic devices.
Recovery Classification

  • Crash and Instance recovery
  • Media Recovery

Crash and Instance recovery: 

Oracle automatically performs crash recovery when a single instance suddenly fails, or when all instances of a multiple-instance Oracle RAC fail. Also, if you shut down your database with the SHUTDOWN ABORT command, Oracle has to perform a crash recovery.

Crash and instance recovery involves the following two-step procedure:

  1. Roll-forward step: During this step, formally called cache recovery, the database applies the committed and uncommitted data in the current online redo log files to the current online datafiles.
  2. Rollback step: During this step, formally called transaction recovery, the database removes the uncommitted transactions applied in the previous step, using the undo data in the undo segments.

Media Recovery: 

Unlike crash and instance recovery, media recovery isn’t automatic—the DBA has to initiate the recovery process. You need the following four items to perform a complete media recovery:

  • A full backup of all datafiles
  • Archived redo logs since the last full backup
  • A control file copy
  • Current online redo logs

There are two steps in an Oracle media recovery process: first you restore a backup of the datafiles and make them available to Oracle. Then comes the recovery, when you bring the datafiles up to date by applying the archived redo log files and the online redo log files.
The recovery process itself has two steps:

Cache recovery (rolling forward): The redo log contains both committed and uncommitted changes. As you know, Oracle writes to the redo log first and the datafiles later. When you restore older files from backups to replace lost or damaged datafiles, those files are missing all the changes made since the time of the backup. The process of applying the contents of both the archived and redo log files to bring the datafiles up to date is called cache recovery or rolling forward. Once you complete cache recovery, you will have gained all your committed changes,but unfortunately, you’ll also have all the uncommitted changes that are part of the redo log.

Transaction recovery (rolling back): During the application of the redo log data to the datafiles, both committed and uncommitted changes get applied. The uncommitted changes must now be removed from the datafiles. Oracle uses the prechange versions of data stored in the undo segments to remove these uncommitted changes. This second step is called transaction recovery or rolling back. Oracle gets the undo data through cache recovery, which regenerates the undo segments from the redo log.

User Managed Recovery

If you can manage your own backups, user-managed techniques can be used to restore and recover a database. You should use the following general procedure during the user-managed recovery of databases running in the archivelog mode. Specific situations demand different recovery strategies, but the essential techniques are the same, no matter what type of file (control file, system tablespace file, datafile, and so forth) you are recovering.
  1. Decide whether you’re going to let users access your database during recovery. This decision depends on the extent of the media damage—if most of the files are affected, you need to start up the database in mount mode. If only a single datafile is affected, you can merely take the tablespace to which the datafile belongs offline and leave the database itself open.
  2. Restore the affected datafiles to their original location if possible or to an alternative location after renaming them. You must also restore any necessary archived redo log files. The V$RECOVERY_LOG and the V$ARCHIVED_LOG views list the names of archive log files. The V$RECOVERY_LOG view lists only those archived redo log files that the database needs to perform media recovery. If you have enough free space, restore the necessary archived red log files to the location specified by the LOG_ARCHIVE_DEST_1 initialization parameter. The database will automatically locate the correct log during media recovery.
  3. Use the RECOVER DATABASE, RECOVER TABLESPACE, or RECOVER DATAFILE command, depending on the situation, to recover the entire database, a tablespace, or a datafile, respectively.
  4. If any archive logs are needed to recover the database, tablespace, or datafiles, Oracle will ask you to supply the archived redo logs, and you can recover up until the point of failure for a complete recovery, or choose to recover to a point in time in the past, if you prefer an incomplete recovery.
  5. If you did not open the database in step 1, open it now, using the ALTER DATABASE OPEN Command.