Author Archives: Prakash



RAC Notes Scanned Documents

10gR2 RAC Installation on RHEL 4U8

Administrating RAC Using SRVCTL and CRSCTL

Cluster Startup Process in 10g and 11gRel 1

Clusterware Architecture

Deinstallation of RAC Environment

RAC Service Management

Converting Standalone database to RAC Using rconfig utility


> Oracle has introduced rconfig utility in version 10g of oracle.

> The pre-requisite for rconfig is database area location should either
Cluster filesystem or ASM.


#su – oracle
rac1>$crsctl check crs
$ps -ef|grep smon
$srvctl stop database -d hrms
(no need to shutdown the database it should be up and running while creating
a database)

>>>Creating a standalone database

#xhost +
.oracle single instance db
.create database
.General purpose
global database name:prod
.use same password
confirm password
.select asm
.select one disk group
.use common location for all data files
.click on browse
.select ASM_DG_FRA
.click on ok
.enable archiving
.edit archive mode parameters
Remove the entries
.click on ok

Note:To find a file location when the instance is not running

$find /u01 -name “alert_*.log”

rac1># su – oracle
$ ps-ef|grep smon
$ sqlplus / as sysdba

SQL> select name,open_mode,log_mode from v$database;
SQL>show parameter cluster
SQL>select name,open_mode,log_mode from v$database;
SQL>select name from v$controlfile;
SQL>select name from v$datafile;
SQL>select member from v$logfile;

# su – oracle
$cd $ORACLE_HOME/dbs
$cd assistants
$cd rconfig
$cd SampleXMLs/
$cp ConvertTORAC.xml ~
$vi ConvertTORAC.xml

specify current Oraclehome of non-rac database for source DBHome


specify OracleHome where the rac database should be configured


specify SID for non RAC database and credential

<n:Source DBInfo SID=”prod”>

Note:asminfo element is required only if the current non-rac


specify prefix for RAC instances Specify the list on non rac

<n:InstancePrefix>prod <n:Node name=”rac1″>

<n:InstancePrefix>prod <n:Node name=”rac2″>

The non-Rac database should have some storage

<n:shared storage type=”ASM”

Specify database area location to be configured


Specify flash Recovery area


$ rconfig ConvertTORAC.xml

Video Tutorial Notes

Active dataguard configuration in 11g

Adding Disks to ASM Diskgroup using ASMCA

Backup and Recovery (Incomplete Data Recovery)

Clone oracle database without using RMAN

Cloning a database using Hot backup

Cloning database using RMAN (Refreshing database)

Configure Database in archivelog mode and noarchivelog mode

Configure Database in flashback mode

Configure RMAN with Recovery Catalog

Convert ASM File System to Cooked (Normal ) File Systems Using RMAN

Convert Cooked (Normal ) to ASM File System Using RMAN

Create duplicate database using rman (rman cloning)

Creating 11g database manually and regestering it to listener and tnsnames

Creating database in silent mode without GUI

Database Auditing Theory

Database Auditing

Database switchover role in Dataguard

Deleting a Database using DBCA Silent mode response File

Difference between gzip and tar Command in Linux

Disaster recovery using RMAN (i.e loss of all datafiles)

Enable disable block change tracking

expdp and impdp

Flashback a table to a particular SCN

How to create and execute RMAN Backup Scripts

How to kill and RMAN Session

How to put a oracle database in restricted session mode

How to recover oracle database to a particualr SCN (point in time recovery) not using RMAN

How to recreate a dropped deleted data dictionary views

How to use configure command of RMAN

Network configuration in silent mode using response file

Oracle RDBMS Installation sielnt mode (using response file)

Recovering data block using RMAN

Scheduling jobs using cron tab in linux

Simple backup and recovery using RMAN

Some linux usefull commands


Steps to configure 11g physical standby database dataguard

Steps to configure read only standby dataguard

Upgrade 11g to 12c

Use RMAN Backup commands to take full and incremental backup

Using Oracle SQL Loader to load data

Using rlwrap package in Linux

Oracle Books For Reading

1.Fundamentals of Database Systems by Elmasri and Navathe on Benjamin/Cummings Press.
2.The Oracle 11g SQL Reference Manual.
3.Oracle 11g DBA Handbook By Loney.
4.Expert One on One Oracle (ISBN 1-59059-243-3) (2003) By Tom Kyte

Oracle Backup and Recovery Solutions

When implementing a backup and recovery strategy, you have the following solutions available:

– Recovery Manager (RMAN)

This tool integrates with sessions running on an Oracle database to perform a
range of backup and recovery activities, including maintaining an RMAN
repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

– User-managed backup and recovery

In this solution, you perform backup and recovery with a mixture of host
operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery.

RMAN performs the same types of backup and recovery available through user-managed techniques more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.
Most of this manual focuses on RMAN-based backup and recovery.

User-managed backup and recovery techniques are covered in Section VIII, “Performing User-Managed Backup and Recovery.” RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

– Incremental backups

An incremental backup stores only blocks changed since a previous backup.
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery. If you enable block change tracking, then you can improve performance by avoiding full scans of every input datafile. You use the BACKUP INCREMENTAL command to perform incremental backups.

– Block media recovery

You an repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup. You use the RECOVER command to perform block media recovery.

– Unused block compression

In unused block compression, RMAN can skip data blocks that have never been used and, in some cases, used blocks that are currently unused.

– Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups.

– Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format. To create encrypted backups on disk, the database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option.
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility.

You can later use Data Pump Import to re-create data after restore and recovery. Logical backups are for the most part beyond the scope of the backup and recovery documentation.

– Oracle Flashback Technology

As explained in Oracle Database Concepts, Oracle Flashback Technology complements your physical backup and recovery strategy. This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

– Logical Flashback Features

Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

– Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

Oracle Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start and end time, operation, and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

Oracle Database Advanced Aplication Developer’s Guide explains how to use this feature.

– Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions uring a period of time. Oracle Database Advanced Application Developer’s Guide xplains how to use this feature.

– Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction, and any transactions that could be dependent on it, had never happened. Oracle
Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past
without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining
associated attributes such as current indexes, triggers, and constraints, and in this way enabling you to avoid finding and restoring database-specific properties.

– Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. “Rewinding a DROP
TABLE Operation with Flashback Drop” on page 16-7 explains how to use this

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

– Flashback Database

At the physical level, Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but is generally much faster because it does not require restoring datafiles from backup and requires less redo than media recovery.

Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a flash recovery area for a database because the flashback logs can only be stored there. Flashback logging is not enabled by default. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the flash recovery area.

Oracle Database also supports restore points in conjunction with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

– Data Recovery Advisor

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

A database failure usually manifests itself as a set of symptoms: error messages, alerts, trace files and dumps, and failed data integrity checks. Data Recovery Advisor automatically diagnoses and informs you of these failures. Within the context of Data Recovery Advisor, a failure is a persistent data corruption that can be directly mapped to a set of repair actions. Each failure has a status of open or closed. Each failure also has a priority of critical, high, or low.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it with respect to the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

My Oracle Support Help

Please check the below link for oracle sid related help…

How do I delete an entire database (UNIX only)?

The following shows the steps to drop a database in Unix enviroment. In order to delete a database, there are few things need to be taken care of. First, all the database related files eg *.dbf, *.ctl, *.rdo, *.arc need to be deleted. Then, the entry in listener.ora and tnsnames.ora need to be removed. Third, all the database links need to be removed since it will be invalid anyways.

It depends how you login to oracle account in Unix, you should have environment set for the user oracle. To confirm that the environment variable is set, do a env|grep ORACLE and you will notice that your ORACLE_SID=SOME_SID and ORACLE_HOME=SOME_PATH. If you do not already have the ORACLE_SID and ORACLE_HOME set, do it now.

Make sure also, that you set the ORACLE_SID and ORACLE_HOME correct else you will end up deleting other database. Next, you will have to query all the database related files from dictionaries in order to identify which files to delete. Do the following:

01. Login as connect / as sysdba at svrmgrl
02. startup the database if it’s not already started. The database must at least mounted.
03. spool /tmp/deletelist.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
05. select member from v$logfile;
06. select name from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
10. Delete in O/S level the files listed in /tmp/deletelist.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check “/var/opt/oracle/oratab” to make sure there is no entry of the database deleted. If yes, remove it.
14. DONE

“Need to Know” for Oracle DBA Begineers

 There is more to being a DBA than knowing SQL.  The DBA works at the intersection of the database, server, operations group, and developers.  A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software issues. Below is a list of the ten topics I feel every entry level DBA should understand.  The list I created stems from my experiences working with databases as both a DBA, Developer, and Manager.   When looking to hire entry level DBA’s, the interview questions I ask are drawn from these areas. If you’re looking to start a job as DBA, then you’ll want to be familiar with these topics.

Backup and Restore

Any DBA worth their salt should know the DBMS’ (Database Management System’s) built-in methods to backup and restore data, such as using Oracle Recovery Manager, but in addition to these built-in utilities, it also makes sense to understand what third party offerings exist. Enterprise backup solutions are used in many larger IT shops.  Be familiar with products such as NetBackup or NetApp SnapManager. As a junior DBA it would be impressive if you knew these tools existed and that not all backups are created equal.  That is to say, just because you back up the database files, doesn’t mean get a good backup… in fact, you didn’t.

Basic optimizations

It is important to know when to recommend when an index should be created.  You should know some basic indexing strategies.  When are clustered indexes appropriate?  When should you use a covered index? Also know how your database optimizer works.  Does it rely on special table statistics?  How do you update those? Know what it means to reorganize tables and indexes.  When should they be reorganized, and what can you do to automate the process?



Software developers can make or break a database.  It is important that you can work with others to help them write efficient queries.  You need to help them understand that one call to a database is much more efficient than one thousand!  In most cases it is faster run one query that returns a 1,000 rows, than it is to call 1,000 queries that return one row each. As a DBA you should help them understand when it is better to perform processing on the DBMS rather than in code.  Pulling volumes of data across your network to perform a sum is most likely slower than writing a query with an aggregate function.

Storage Systems

In most databases the main bottle neck is disk access.  Understanding where your database is stored and how the DBMS accesses the physical data is important.  Is your database on local disk or a SAN (Storage Area Network)? If you company has a storage team, get to know them, and understand what tools they use to monitor IOPS (Input/output Operations per Second).

How to read a query plan

As a junior DBA you should know how to generate and read a basic query plan.  I wouldn’t expect you know fully understand all verbiage, but several key phrases, such as “Full Table Scan,” and “Nested Loops” should jump out as red flags. Also, you should understand, when the optimizer recommends a change be made, why that change would work, and what the trade-offs are in making it.  For instance, the optimizer may recommend that an index be created.  It this to compensate for poor programming?  Also, if you add it, could something else suffer, such as a process to insert or update data?

Knowledge of normalization

Normalized tables are the foundation of a well-designed relational database, yet they can also be its bane. A junior DBA should understand and know how to put data into 1st, 2nd, and 3rd normal form.  Why is normalization important and when can it become a liability? Knowing the difference between a Primary, Foreign, and Unique key is important.  Also is understanding and knowing how to enforce one-to-one, one-to-many relationships.

Knowledge of SQL

It might seem obvious, but a DBA should have a really good grasp of both SQL DML (Data Manipulation Language) and DDL (Data Definition Language). DML covers items such as Select, Update, Insert and Delete.  You should understand all the major clauses such as WHERE, GROUP BY, HAVING, and ORDER BY.  In addition you should be comfortable with sub queries and joins. DDL covers items such as CREATE TABLE and ALTER TABLE.  A junior DBA should understand how to create and modify tables and indexes and know the difference between deleting records, truncating a table, or dropping it! And… don’t forget views!

Operating System

As a DBA you need to be familiar with the OS (Operating System) your DBMS lives within.  You should understand nuances of your operating system, such as security settings, integration with Active Directory, LDAP (Lightweight Directory Access Protocol), and naming conventions.  Also, how is your DBMS started?  What scripts are used to start, shutdown, or lock users out of your database?


To become efficient it is important to know when OS scripting, such as PowerScript, can help you manage your servers.  Consider having ten or more DB servers?  If you had to shut down all the DBMS on them, would you individually log in and manually shut them down or use a script? In my book, if you want to get an edge on other junior DBA’s you learn scripting.  This will only make you more efficient at your job.

Stored Procedures and Triggers

I made stored procedures and triggers a separate category since they’re more like programming than pumping out SQL statements.  Regardless, you should know when to use them and recommend to your developers their virtues versus writing SQL in code. Also, many third party applications ship with Stored Procedures and Triggers.  Being able to read them and understand their logic can go a long ways when trying to troubleshoot a performance issue. The sooner you can avoid treating them like black boxes, the quicker you’ll understand and become more comfortable with the databases you support.

Oracle Instance

An Oracle Instance
  • Every running Oracle database is associated with an Oracle instance.
  • Every time a database is started on a database server, Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes.
  • The combination of the SGA and the Oracle processes is called an Oracle database instance.
  • The memory and processes of an instance work to manage the database’s data efficiently and serve the one or multiple users of the associated database.
  1. Oracle starts an instance, then mounts a database to the instance.
  2. Multiple instances can execute concurrently on the same machine, each
    accessing its own physical database. 

    Single-Process Oracle Instance

    1. A single-user Oracle instance is a database system in which all Oracle code is executed by one process.
    2. All code of Oracle and the lone user’s database application is executed by a single process. 
      For example, Oracle running under the MS DOS operating system on a PC can only be accessed by a single user because MS DOS is not capable of running multiple processes.

      Multiple-Process Oracle Instance

Multi-user Oracle uses several processes to execute different parts   of oracle  and a separate process for each connected user.

  1. In a multiple-process system, processes can be categorized into two groups: user processes and Oracle processes.
  2. Each process in a multiple-process Oracle instance performs a specific job.
  3. By dividing the work of Oracle and database applications into several processes, multiple users and applications can simultaneously connect to a single database instance while the system maintains adequate performance.
  4. Most database systems are multi-user, because one of the primary benefits of a database is managing data needed by multiple users at the same time.