OCR & Vote disk locations

ocrconfig - run as root

To find OCR Backup
[root@node1 mapper]# ocrconfig -showbackup

node2     2014/08/11 23:28:58     /u01/app/11.2.0/grid/cdata/nodescan/backup00.ocr

node2     2014/08/11 16:26:48     /u01/app/11.2.0/grid/cdata/nodescan/backup01.ocr

node1     2014/08/10 01:51:04     /u01/app/11.2.0/grid/cdata/nodescan/backup02.ocr

node1     2014/08/10 01:51:04     /u01/app/11.2.0/grid/cdata/nodescan/day.ocr

node1     2014/08/10 01:51:04     /u01/app/11.2.0/grid/cdata/nodescan/week.ocr

node2     2014/08/11 19:42:42     /u01/app/11.2.0/grid/cdata/nodescan/backup_20140811_194242.ocr

Take Local OCR backup
[root@node2 ~]# ocrconfig -local -manualbackup

node2     2014/11/11 18:17:00     /u01/app/11.2.0/grid/cdata/node2/backup_20141111_181700.olr

node2     2014/07/31 09:59:03     /u01/app/11.2.0/grid/cdata/node2/backup_20140731_095903.olr

To find the local OCR backup
[root@node2 ~]# ocrconfig -local -showbackup

node2     2014/11/11 18:17:00     /u01/app/11.2.0/grid/cdata/node2/backup_20141111_181700.olr

node2     2014/07/31 09:59:03     /u01/app/11.2.0/grid/cdata/node2/backup_20140731_095903.olr

[oracle@node1 oracle]$pwd

[oracle@node1 oracle]$ls
lastgasp  ocr.loc  ocr.loc.orig  olr.loc  olr.loc.orig  oprocd  scls_scr
[oracle@node1 oracle]$cat ocr.loc

To find the Vote disk
[root@node1 mapper]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   65ab90bf35854fb6bf33422a1ea60616 (/dev/oracleasm/disks/DATA1) [DATA]
Located 1 voting disk(s).

Oracle FAQ's :

How to  connect to a pluggable database in 12c using SQL*plus:

There are 2 methods to connect to a 12c Pluggable database,

 i. using alter session set container on the server
ii. creating a service and connecting thru tns

 First method :-

[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_SID
[oracle@rac1 ~]$ echo $ORACLE_HOME
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release Production on Tue Jan 14 21:43:31 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
SQL> show con_name   >>>>>>>>> Now we are connected to CDB - Container database <<<<<<<<<

SQL> show pdbs      >>>>>>>>> Lists the available PDB's under this container db <<<<<<<<<

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO


SQL> alter session set container = pdb; >>>>>>>>> Setting container in the session level <<<<<<<<<

Session altered.

SQL> show con_name   >>>>>>>>> Now we are connected to PDB - Pluggable database <<<<<<<<<


SQL> show con_id


 Second method :-

[oracle@rac1 admin]$ lsnrctl status  >>>>>>>>> Checking listener status <<<<<<<<<

LSNRCTL for Linux: Version - Production on 14-JAN-2014 21:55:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@rac1 admin]$
[oracle@rac1 admin]$ lsnrctl start  >>>>>>>>> Starting the listener <<<<<<<<<

LSNRCTL for Linux: Version - Production on 14-JAN-2014 21:55:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version - Production
Start Date                14-JAN-2014 21:55:18
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...
The listener supports no services
The command completed successfully
[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora 
>>>>>>>>> creating a tns entry for pdb <<<<<<<<<

pdb =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
        (SERVICE_NAME = pdb)

SQL> alter pluggable database all open;
  >>>>>>>>> start all the pluggable databases <<<<<<<<<
Pluggable database altered.

SQL> show pdbs  >>>>>>>>> Checking the status of the PDB's <<<<<<<<<

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> ! lsnrctl services
   >>>>>>>>> checking the listener services - PDB instance is registered & listed <<<<<<<<<

LSNRCTL for Linux: Version - Production on 14-JAN-2014 22:09:53

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "cdb" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:0 refused:0 state:ready
Service "cdbXDB" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: rac1.india.com, pid: 5925>
Service "pdb" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:0 refused:0 state:ready
The command completed successfully

SQL> conn hr/manager@pdb  >>>>>>>>> connect to PDB using connect string <<<<<<<<<
SQL> show con_name

SQL> show user
USER is "HR"


Block device error  when trying to mount a filesystem:

[root@rac1 ~]# mount -a
mount: /u01 is not a block device
mount: /u02 is not a block device

This error is occurred when there is misplaced entry in your fstab file. The /etc/fstab file should have entries as below. The /dev/sdb1 is the block device and /u01 is the mount point.

[root@rac1 ~]# cat /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=1500m      0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0
/dev/sdb1               /u01                    ext3    defaults,rw        0 0
/dev/sdc1               /u02                    ext3    defaults,rw        0 0


Creating active dataguard - 11g feature

Here we have primary database(pridb) and standby database(stddb)
Steps to create an active dataguard(11g)

1.copy the password file from the primary to the secondary server under $ORACLE_HOME/dbs directory.

2.Ensure the primary database is in archive log mode if not convert it to archive log mode.

alter system set log_archive_dest_1='location=/vol1/app/oradata/arch valid_for=(all_logfiles,all_roles)';

In mount stage issue the following command

SQL> alter database archivelog;

Enable force logging

SQL> alter database force logging;

Enable the flashback which is not mandatory but recommended.

SQL> alter database flashback on;

Create the standby redolog files in the primary. The recommendation is,

 number of standby redo log groups=(number of online redo log file groups + 1) * number of threads

*The redo log & standby redologs should have same size
*The standby redologs are mandatory only when you do real time apply.

3.Create a controlfile from primary for creating standby.

RMAN>backup current controlfile for standby format='/vol1/rmanbkup/stby_cfile.%U';

4.Backing up the archivelogs and deleting input archivelogs for resolving space constraints.

RMAN>backup archivelog all delete input;

5.create directories for datafiles and logfiles in secondary as like in primary server

6.create pfile from spfile

7.change init parameters in primary database

SQL> alter system set fal_client=pridb;

System altered.

SQL> alter system set fal_server=stddb;

System altered.

SQL> alter system set log_archive_dest_2='service=stddb LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=stddb';

System altered.

SQL>  alter system set log_archive_dest_state_2=defer;

System altered.

8.Start standby instance

SQL> conn sys/syspridb@stddb as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/vol1/initstddb.ora';
ORACLE instance started.

run {
    # Set the last log sequence number
    set until sequence = 208 thread = 1;

    # Allocate the channel for the duplicate work
    allocate auxiliary channel ch1 type disk;

set newname for datafile  1 to
set newname for datafile  2 to
set newname for datafile  3 to
set newname for datafile  4 to
    duplicate target database for standby dorecover nofilenamecheck ;

9.Start the mrp

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect;

10.Verify MRP is running:

ps -ef | grep mrp

11.Check applied status in secondary

SQL> select count(*) from v$archived_log where applied='NO';

Automatic memory management(AMM) in oracle 11g

   Oracle has improvised the memory management in all the versions from 9i. We have pga_aggregate_target parameter to govern the programable global area management. The same automation in memory allocation is enforced in sga from 10g. But in oracle 11g this goes even better that is AMM, which oracle uses to dynamically allocate memory between PGA and SGA. 
    The 2 parameters that rule the AMM are MEMORY_MAX_TARGET and MEMORY_TARGET. Memory_max_target is static parameter and memory_max_target is dynamic. Any non zero value for memory_target will enable the AMM feature. This feature is very much helpful for OLTP environments running with share server configuration.

Index rebuild

alter index index_name tablespace tbs_name rebuild online parallel 2;

The parallel parameter works only in Enterprise edition of oracle.

Because parallelism doesnt comes free with Standard Edition. Same way the compression techniques used in oralce, for example compressed backup, compressed table(LOB), compressed expdp, compressed archive log files for standby.

Index rebuild with online option requires disk space equal to the size of the index in-order to make the index usable for the current running sessions which ever required.


Oracle FAQ's

Recover a deleted rows from undo without flashback.

select * from testtable versions between timestamp to_timestamp('22-feb-2012 03:25:35' ,'dd-mon-rrrr HH24:MI:SS') and to_timestamp('22-feb-2012 03:28:35' ,'dd-mon-rrrr HH24:MI:SS')

This query will return the deleted rows of the test table in the mentioned time. This will work even without flashback on.

Oracle 11g RAC - Implementation Steps on Linux


There are different types of cores, namely
1-Single core
2-Dual core
4-Quad core
6-Hex core
8-Oct core

   # cat /proc/cpuinfo
    # grep cores /proc/cpuinfo
    # grep “core id” /proc/cpuinfo
    # grep -i core /proc/cpuinfo

example for finding physical cores, here each CPU holds 6 cores(HEXA)

[root@server1 ~]# cat /proc/cpuinfo | egrep "processor|physical\ id|core\ id|cpu\ cores"
processor       : 0
physical id     : 0
core id         : 0
cpu cores       : 6

psrinfo -v :for Solaris

To find CPU Sockets

dmidecode|grep CPU

will display the number of CPU sockets

dmidecode|grep Populated

will display the populated sockets.


SQLPLUS shortcuts:

/ -will execute the last run statement in the sqlplus buffer
1 -will display the last run statement in the sqlplus buffer
change /old/new -Changes the first occurrence of the text on the current line in the sqlplus buffer.


sqlplus user/pwd@//

With this we can connect to a database without TNSNAMES configuration.
Oracle Analyze a table:

alter table table_name estimate statistics;

Analyze a schema:

login as sys user

exec dbms_stats.gather_schema_stats("SCOTT");
HP UX commands:

machinfo - to find the machine information
swapinfo - to find the swap partition size
bdf - to check the partition information (in kbytes)
Configuring SAMBA server in RHEL5.5.

-give full permission on the sharable directory( chmod 777 /pack/sample
-samba package should be installed or do yum install samba if you have yum repository
-$vi /etc/samba/smb.conf
-add the following lines in the file mentioned to share the mentioned directory
- [Share]
   path = /home/share
   writable = yes
   guest ok = yes
   guest only = yes
   create mode = 0777
   directory mode = 0777
   share modes = yes

use testparm command to check and verify the samba configuration file.
ORATAB file is created during oracle installation. This file resides in /etc directory or in /var/opt/oracle directory.
This file is used to list the databases installed and the oracle versions. Oratab file contains ORACLE_SID, ORACLE_HOME, and automatic startup , shutdown of database with OS startup and shutdown. The environment variables in OS level are taken from this file.
For example:


The Y in the above line mentions to autostart the db and listener while OS startup. This oratab file works for only  one instance and you cannot add multiple instances of database into this file.


Oracle DB link

Creating dblink from oracle 10g database to a user in oracle 11g database :

create database link link_name connect to "username" identified by "password" using 'connection_string';

The username and password should be given within double quotes.

There are other conditions too for a dblink, that is

  Both the database servers should be in same domain

  The user should have 'CREATE DATABASE LINK' privilege


Oracle - Create Tablespace

SQL> create tablespace tbs_name datafile '/vol01/oradata/sample/datafile/exp101.dbf' size 100M autoextend on next 2M maxsize unlimited extent management local segment space management auto;
Force Drop a tablespace:
SQL> alter tablespace tbs_name offline drop;

Ensure no active connections writing on that particular table.

Change DB name:

nid is the utility to change the dbname.

The database should be in the mounted exclusive state,

nid TARGET=sys/password@tnsname
Compile Oracle Schema using sqlplus

execute dbms_utility.compile_schema('SCOTT');

as a 'SYS' user.

Managing Rouge sessions in Oracle:

1. Identify the session to be killed

SELECT a.inst_id,
FROM   gv$session a
       JOIN gv$process b ON b.addr = a.paddr AND b.inst_id = a.inst_id
WHERE  a.type not like  'BACKGROUND';

2. Alter system kill session 'sid,serial#';

This command will kill the session. Run as sys user. If still the session persists you can use "immediate" clause with the above command. like

Alter system kill session 'sid,serial#' immediate;

If its a RAC instance then

Alter system kill session 'sid,serial#,@inst_id' immediate;\

3. The other way is Disconnect Session

This option does not kill the session but it will disconnect the session and make the dedicated server process(dedicated server mode)  in os level to shut for that particular session.

Alter  system disconnect session 'sid,serial#' post_transaction;
the post_transaction option will wait for the current transaction to complete.

Alter system disconnect session 'sid,serial#'  immediate;
The immediate option will stop the current transaction too. (recommended)

4. Killing in OS level

If you want to kill a OS level oracle process(server process) responsible for the session then issue

orakill ORACLE_SID spid;

from command prompt for windows.

kill -9 spid

for unix flavours.(the spid for the session can be acquired from the v$process, refer the query in point 1)

Oracle archive_lag_target

The archive_lag_target parameter is very useful for DataGuard setup, since this parameter will switch the logfile in the specified time interval. By default the parameter is 0 sec. The unit for this parameter is in seconds.

Responsibilities of an ORACLE dba

    As I am a DBA for more than a year I have came to know lot of things happening in the industry. I would like to share all those with you. A DBA is solely responsible for the database, so ensure before firing any commands. When you are working with OLTP environment, you have to be more cautious. You have to know all the features and differences between oracle versions.version info. He should be able to work on any OS platforms, also should have some idea about hardware setup, memory management like RAID, san storage etc. You all may think that a DBA should do only backup, recovery, monitoring operations. But other than this there are lot of roles a DBA has. It differs based on the industry and institutions you work.oracle.

Installing, upgrading, and patching Oracle Database software

Designing databases based on requirements

Creating Oracle databases

Testing backup and recovery strategy, and prepare a plan for failure cases.

Backup data regularly

Making connectivity for clients to connect the database across network.

Startup and shutdown the database

Managing tablespaces and datafiles

Managing users and security

Managing database objects

tuning database for performance

Proper usage of Oracle corp support while needy

Evaluating and testing new database features

Oracle Database Control file

Control files has the status of the physical structure of a database.  A control file should be backup whenever a change is made to the database.The controlfile contains the following sections:
  • ARCHIVED LOG (reusable)
  • BACKUP SET (reusable)
  • BACKUP DATAFILE (reusable)
  • BACKUP CORRUPTION (reusable)
  • BACKUP PIECE (reusable)
  • BACKUP REDOLOG (reusable)
  • BACKUP SET (reusable)
  • COPY CORRUPTION (reusable)
  • DATAFILE COPY (reusable)
  • DELETED OBJECT (reusable)
  • LOG HISTORY (reusable)
  • MTTR
  • OFFLINE RANGE (reusable)
To view control file status
SQL> show parameter control_files

To backup control file before any operation:
SQL> alter database backup controlfile to 'c:\ora\control01.ctl'

Multiplexing of control files:
Can be done by giving the following line in pfile: control_files='E:\app\oradata\revolt\control01.ctl','E:\app\oradata\revolt\control02.ctl'

Recovery of database without controlfile and redolog
alter database backup controlfile to trace resetlogs 

Importance of Parameter File or PFILE:

    During an oracle instance startup, the characteristic features of the instance are configured by the parameters written in a PFILE or Initialization parameter file or Static Parameter file. The PFILE is commonly known as init.ora file among the DBA community. The naming convention for PFILE is initSID.ora and placed in the $ORACLE_HOME/dbs folder in default. PFILE is a txt file and can be edited using an operating system editor like notepad. PFILE is opened only during instance startup. There is an other similar file in oracle server named as SPFILE or Persistent parameter file. SPFILE is a binary file which cannot be opened manually. SPFILE concept is available only from oracle 9i and above versions. For detailed information regarding the parameter files please visit initSID.ora

Example of a PFILE:

      # Initialization Parameter File : inituser.ora
      db_name = user
      instance_name= user
      control_files = (home/user/ORADATA/u01/controluser.ctl)
      db_block_size = 4096
      shared_pool_size = 50000000
      java_pool_size =50000000
      max_dump_file_size = 10240
      background_dump_dest = /home/user/ADMIN/BDUMP
      user_dump_dest = /home/user/ADMIN/UDUMP
      core_dump_dest = /home/user/ADMIN/CDUMP
      undo_management = AUTO
      undo_tablespace = UNDOTBS


Oracle database server architecture
Oracle Utilities
SQL Loader

Linux Commands

       Linux commands can be  used configure and interact with Linux OS. The linux commands can be used at the shell prompt, or command prompt (Terminal). If you are using  linux in a GUI, try with the shell programming icon. If you do not have GUI then  you should be working with the shell prompt, and if you are signing through remote then you will be at the prompt. The two types of commands are shell commands and linux commands.

Linux Commands:

The Linux commands are not  shell commands. Each linux command is a individual executable program, thats written in  C-programming language. These executable programmes are stored in various physical directories such as /bin.The $PATH variable defines  the location of the directories. These linux commands differ from the linux distrubutions, and remains similar in any shell they are being used.

 Shell Commands:

The shell program consists of the shell commands. There are different kind of shells likely  bash shell, C shell, bourne shell etc. Each shells have variance in them. The commands structure vary between shells, but each shell is same among different linux platforms.

 The Command Prompt:

The prompts look is depend on the shell you use, and the settings you have for that shell.
The bash shell have the prompt set up like the following

[adm@cric home]#

Here the user logged is adm, and the computer name is  cric where the folder is home. The password file(pwd) is to be used to get full path.

Your prompt is bound to look different, so for the purpose of this section I will just use the > to represent the prompt.

Linux Commands:

Some linux commands are very simple and need nothing more than the command itself.
For example the command ls (list) will simply list the names of the directories and files in the current directory...
In this instance ls shows there are two directories, called files and images, and a text file called index.
However you can often add options or parameters, which are usually added to the command after a '-'.
Adding the option -l will give you a long listing which includes the permissions, ownership, size, date/time, and name of the files and directories...
>ls -l
drwx------ 2 bob bob 4096 Aug 22 10:31 files/
drwx------ 3 bob bob 4096 Mar 19 11:17 images/
-rwx------ 1 bob bob 284 Mar 18 10:23 index.txt
Another example would be adding -a (ls -a) to list ALL the files in the current directory, including hidden files.
>ls -a
This shows there is a hidden file (hidden files have a '.' before the filename) called '.hiddenfile', as well as the two directories and index file.
You can use more that one option at a time...
>ls -la would produce a long listing format of ALL (including hidden) directories and files in the current working directory.
There are many options for some linux commands, and the best place to find out about then all is the 'man' pages. These man pages show you how to format the command, and what options and parameters there are to use with each command.

 Few Basic commands

#free  -shows free RAM space
#date -current date
#time -shows system time
-to copy a file or directory#mkdir dir1   -to create directory
#rmdir dir1    -to remove a directory

Locate command is works only on linux kernel. It displays the absolute path for the search term.locate reads from the database created by the updatedb command. Locate does not check the existance of the file. Here is the usage for the locate command.

$ locate -h
Usage: locate [OPTION]... [PATTERN]...
Search for entries in a mlocate database.

  -b, --basename         match only the base name of path names
  -c, --count            only print number of found entries
  -d, --database DBPATH  use DBPATH instead of default database (which is
  -e, --existing         only print entries for currently existing files
  -L, --follow           follow trailing symbolic links when checking file
                         existence (default)
  -h, --help             print this help
  -i, --ignore-case      ignore case distinctions when matching patterns
  -l, --limit, -n LIMIT  limit output (or counting) to LIMIT entries
  -m, --mmap             ignored, for backward compatibility
  -P, --nofollow, -H     don't follow trailing symbolic links when checking file
  -0, --null             separate entries with NUL on output
  -S, --statistics       don't search for entries, print statistics about each
                         used database
  -q, --quiet            report no error messages about reading databases
  -r, --regexp REGEXP    search for basic regexp REGEXP instead of patterns
      --regex            patterns are extended regexps
  -s, --stdio            ignored, for backward compatibility
  -V, --version          print version information
  -w, --wholename        match whole path name (default)

updatedb - can be run only as root. This updates or creates a database for locate utility.

# updatedb -h
Usage: updatedb [OPTION]...
Update a mlocate database.

  -f, --add-prunefs FS           omit also FS
  -e, --add-prunepaths PATHS     omit also PATHS
  -U, --database-root PATH       the subtree to store in database (default "/")
  -h, --help                     print this help
  -o, --output FILE              database to update (default
      --prunefs FS               filesystems to omit from database
      --prunepaths PATHS         paths to omit from database
  -l, --require-visibility FLAG  check visibility before reporting files
                                 (default "true")
  -v, --verbose                  print paths of files as they are found
  -V, --version                  print version information

The lists of paths and filesystems to omit default to values read from

The Oracle 11g Database Administrator

           The Oracle Database 11g is designed for administrators to provide an effective curriculum syllabus. You people learn and get knowledge to configure, install and maintenance of the database. When you learn the essentials of administrating the oracle database, you can develop knowledge in any of the following key focus areas of oracle database. They are

  1. Performance Management
  2. Manageability
  3. Linux Administration
  4. Security management
  5. High Availability
  6. Data Warehousing
  7. Grid maintenance
  8. Storage Server maintenance
The Oracle University curriculum is framed in the way to meet the DBA's challenges faced today.

Oracle indexes tuning

Indexes in Oracle database.

The indexes are

  • B-Tree indexes or balanced tree indexes;
  • bitmap indexes
  • partitioned indexes
  • domain indexes
  • function-based indexes

The B-Tree Indexes
          B-Tree Indexes are the most convenient thing when querying for a small amount of data from a large table. Indexes help out the most in reducing the i/o in this case.The bigger the table and lower the number of rows that you want to get them, then the more effective way is the index.
          Even if you see a book it has an index, this shows the importance of index. Even a book needs a index then think of the case of a terabyte oracle database. The reasons are a lot. When searching an article it would take huge time to find the particular area of interest but without using the index then it would to read the whole book. It's similar when you're querying against a database. so by working on the index, the database would have to read more data.
          The indexes improves read and update operations using sub-queries. However when updates are performed often in a table with index then every time the index has to get updated regarding the change in the oracle database. Sincethe amount of i/o is drastically increased causing burden to the DB server. So having indexes for read only table is recommended by oracle.The oracle dba should ensure the usage of the index. Otherwise its going to be a performance degrading for the oracle database.

Oracle Interview FAQ's.

  1. Explain about Oracle Catalog and  Archive log? 
           The Oracle  catalog contains tables and views which allows the oracle user to obtain the information about  database, such as what other tables and views are available, data dictionary views their attributes, constraints, etc. Oracle has hundreds of  system catalog relations, only some of which are available to the limited user.

     2. Give some of the  views in RMAN catalog which contains the catalog information?


3. How to find alert log file location?
SQL> show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      c:\app\user\diag\rdbms\rev\rev\trace

Oracle RMAN

RMAN is a recovery management utility of oracle database. RMAN is recommended by oracle for backup and recovery. It makes the job easier for a DBA and gives flexibility to work on the database. RMAN optimizes the performance and space occupied while backup with file multiplexing and backup set compression.

Syntax for RMAN Command-line
[ TARGET connectStringSpec 
| { CATALOG connectStringSpec } 
| LOG ['] filename ['] [ APPEND ]
1.First create a tablespace for RMAN
2.Create a user for rman CREATE USER rman IDENTIFIED BY rmanDEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;3. Grant privileges for the user GRANT connect, resource, recovery_catalog_owner TO rman;
4. Assign catalog
C:\>rman catalog=rman/rman

RMAN> create catalog tablespace "RMAN";
RMAN> register database;

hits count
Database | Oracle PartnerNetwork Blog