Saturday, April 28, 2012

Understanding Indexes Concept

Indexes plays and crucial role in the performance tunning of a database . It is very important to know how the index  work i.e, how indexes fetches the data's from a tables . There is a very good post by  rleishman on the working of indexes . Let's have a look . 

What is an Index ? 
An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. It is just as the index in this manual helps us to locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data . 

First we need to understand a block. A block - or page for Microsoft boffins - is the smallest unit of disk that Oracle will read or write. All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So we never read "just one row"; we will always read the entire block and ignore the rows we don't need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.

Oracle uses two different index architectures: b-Tree indexes and bitmap indexes. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the "normal" index .

The "-Tree" in b-Tree 
A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book .  Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells us the physical location of the telephone (row in the table).
The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

For example : 
To find the name Gallileo in this b-Tree phone book, we:
=> Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.
=> Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.
=> Read page 350, which is a leaf block; we find Gallileo's address and phone number.
=> That's it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:

SQL> select index_name,  blevel+1  from  user_indexes  order  by  2 ;
user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells us the number of blocks a unique index scan must read to reach the leaf-block. If we're really, really, insatiably curious; try this in SQL*Plus:

SQL> accept   index_name  prompt   "Index Name: " 
SQL> alter session set tracefile_identifier='&index_name' ; 
SQL> column object_id new_value object_id
SQL> select  object_id  from user_objects where object_type = 'INDEX'  and  object_name=upper('&index_name');
SQL> alter session set events 'Immediate trace name treedump level &object_id';
SQL> alter session set tracefile identifier="" ;
SQL> show parameter user_dump_dest 

Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find the trace file - the file name will contain the index name. Here is a sample:

---- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
   leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
   leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
   leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
   leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
   leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
   leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
   leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
   leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
   leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump
This index has only a root branch with 323 leaf nodes. Each leaf node contains a variable number of index entries up to 807! A deeper index would be more interesting, but it would take a while to dump.

"B"  is  for...
Contrary to popular belief, b is not for binary; it's balanced.
As we insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length. 

How are Indexes used ?
Indexes have three main uses:

  • To quickly find specific rows by avoiding a Full Table Scan

We've already seen above how a Unique Scan works. Using the phone book metaphor, it's not hard to understand how a Range Scan works in much the same way to find all people named "Gallileo", or all of the names alphabetically between "Smith" and "Smythe". Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.
  • To avoid a table access altogether

If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we'd have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.
Oracle does the same thing. If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.
  • To avoid a sort

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.

1. GROUP BY : 

SQL> select src_sys, sum(actl_expns_amt), count(*)  from ef_actl_expns
           where src_sys = 'CDW'   and actl_expns_amt > 0  
           group by src_sys ; 
| Id   |      Operation                                               |     Name             |
|   0  | SELECT STATEMENT                                     |                           |
|   1  |  SORT GROUP BY NOSORT  <-------           |                           |
|*  3 |    INDEX RANGE SCAN                                 | EF_AEXP_PK       |

Predicate Information (identified by operation id):
   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')
Note the NOSORT qualifier in Step 1.

2. ORDER BY : 

SQL> select *  from ef_actl_expns
          where src_sys = 'CDW' and actl_expns_amt > 0
          order by src_sys 
| Id   | Operation                                                     |     Name            |
|   0  | SELECT STATEMENT                                     |                           |
|*  2 |   INDEX RANGE SCAN                                   | EF_AEXP_PK      |

Predicate Information (identified by operation id):
   1 - filter("ACTL_EXPNS_AMT">0)
   2 - access("SRC_SYS"='CDW')

Note that there is no SORT operation, despite the ORDER BY clause. Compare this to the following:

SQL>  select * from ef_actl_expns
            where src_sys = 'CDW'  and actl_expns_amt > 0
            order by actl_expns_amt ; 
| Id  | Operation                                                      |         Name          |
|   0 | SELECT STATEMENT                                       |                            |
|   1 |  SORT ORDER BY                                            |                            |
|*  3 |    INDEX RANGE SCAN                                   | EF_AEXP_PK       |

Predicate Information (identified by operation id):
   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')


SQL> select distinct src_sys  from ef_actl_expns
           where src_sys = 'CDW'  and actl_expns_amt > 0 ; 
| Id  |          Operation                                             |         Name          |
|   0 | SELECT STATEMENT                                       |                            |
|   1 |  SORT UNIQUE NOSORT                                 |                            |
|*  3 |    INDEX RANGE SCAN                                   | EF_AEXP_PK       |

Predicate Information (identified by operation id):
   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')

Again, note the NOSORT qualifier.

This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.

Full table Scans are not bad : 
Up to now, we've seen how indexes can be good. It's not always the case; sometimes indexes are no help at all, or worse: they make a query slower.

A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.

Much more interesting - and important - are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.

To explain the problem, we need a new metaphor. Imagine a large deciduous tree in our front yard. It's Autumn, and it's our job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac...) would be get down on hands and knees with a bag and work our way back and forth over the lawn, stuffing leaves in the bag as we go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: we would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon. Type this in SQL*Plus:

SQL> show parameter  db_file_multiblock_read_count 

Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), we decide to pick up the leaves in order of size. In support of this endeavour, we take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because we cover much more distance walking from leaf to leaf.

So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.

The main reasons for this are :

  • As implied above, reading a table in indexed order means more movement for the disk head.
  • Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.
  • The db_file_multiblock_read_count setting described earlier means FTS requires fewer visits to the physical disk.
  • Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.

So what's the lesson here? Know our data! If our query needs 50% of the rows in the table to resolve our query, an index scan just won't help. Not only should we not bother creating or investigating the existence of an index, we should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule - there's always one - is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.

Summary : 
Indexes are not a dark-art; they work in an entirely predictable and even intuitive way. Understanding how they work moves Performance Tuning from the realm of guesswork to that of science; so embrace the technology and read the manual.


Enjoy    :-)

Thursday, April 26, 2012

Beginning Performance Tuning

Performance tunning is one of the biggest responsibilities of a DBA to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. Performance tunning is not an easy task. The main issues with tunning for beginners is that from where to start and what should be the right approach . Here is very good presesntation by Arup Nanda who is having more than 16 years of experience as Oracle DBA .  Click the below link to find his presentation .

Enjoy       :-) 

Monday, April 23, 2012

How to Identify the Static and Dynamic Parameter in Oracle

Sometimes, we may not very sure whether an oracle parameter is static(restarting database is required to come under the action) parameter or dynamic(can be changed without restarting) parameter . We can check this by using the v$parameter2 view which is very similar to v$parameter having few extra rows for long parameters . The another difference between the v$parameter and v$parameter2 is that the format of the output .. For example, if a parameter value say  "x,y"  in V$PARAMETER view does not tell us if the parameter has two values ("x" and "y") or one value ("x, y") whereas V$PARAMETER2 makes the distinction between the list parameter values clear.

SQL> select value from v$parameter WHERE name LIKE 'control_files' ; 

SQL> select value from v$parameter2 WHERE name LIKE 'control_files' ; 

Here, If  ISSES_MODIFIABLE  parameter is true, the parameter can be changed on session level , and if  ISSES_MODIFIABLE or ISINSTANCE_MODIFIABLE is true, then parameter can be changed on system level. Here is an example


Enjoy     :-)

Friday, April 13, 2012

Oracle(OUI) Silent Mode Installation on Linux

OUI (Oracle Universal Installer) is a program used to install Oracle software and database options.We generally use the OUI in GUI mode to install the Oracle software . Sometimes, it may required to install the oracle software in silent mode that is without invoking the OUI . This can be done by using the reponse file . 
An Oracle database response file specifies parameters for configuring an Oracle event database.The response file can be created by running the Oracle universal installer (OUI) and records the steps and stages in the response file or we can found the demo response file in oracle software in "response" directory .We can create the response file by invoking the below command 
[oracle@Ramtek ~]$ ./runInstaller -record -destinationFile /tmp/db_silent.rsp 

The "-record" parameter tells the installer to write to the response file and the "-destinationFile" parameter defines the name and location of the response file.The process is similar to using Kickstart for Linux installations .We can prepare the oracle environment from here . I have editted by response file and highlighted(bold with blue shade) the modified value. Below is response file.

FROM_LOCATION_CD_LABEL=<Value Unspecified>
sl_superAdminPasswds=<Value Unspecified>
s_superAdminSamePasswd=<Value Unspecified>
s_dlgASMCfgRedundancyValue="2 (Norm)"
s_dlgASMCfgAdditionalSpaceNeeded=" MB"
s_ASMSYSPassword=<Value Unspecified>
sl_dlgASMDskGrpSelectedGroup={" "," "," "," "}
s_dlgEMCentralAgentSelected="No Agents Found"
s_superAdminSamePasswdAgain=<Value Unspecified>
s_ASMSYSPasswordAgain=<Value Unspecified>
s_dlgRBOPassword=<Value Unspecified>
sl_superAdminPasswdsAgain=<Value Unspecified>

A silent installation is initiated using the following command.

[root@Ramtek ~]# su - oracle
[oracle@Ramtek ~]$ cd /home/oracle/database
[oracle@Ramtek database]$ ./runInstaller -silent -force -ignoreSysPrereqs -responseFile /tmp/db.rsp
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-04-12_11-25-29AM. Please wait ...[oracle@Ramtek database]$ Oracle Universal Installer, Version Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.
You can find a log of this install session at:
.................................................................................................... 100% Done.
Loading Product Information
................................................................................................................... 100% Done.
Analyzing dependencies
Starting execution of Prerequisites...
Total No of checks: 9
Performing check for CertifiedVersions
Checking operating system requirements ...
Expected result: One of redhat-3,redhat-4,SuSE-9,asianux-1,asianux-2
Actual Result: redhat-4
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for Packages
Checking operating system package requirements ...
Checking for make-3.79; found make-1:3.81-3.el5.        Passed
Checking for binutils-2.14; found binutils-   Passed
Checking for gcc-3.2; found Not found.  Failed <<<<
Checking for libaio-0.3.96; found libaio-0.3.106-3.2.   Passed
Check complete. The overall result of this check is: Failed <<<<

Check complete: Failed <<<<
Problem: Some packages required for the Oracle Database 10g to function properly are missing (see above).
Recommendation: Install the required packages before continuing with the installation.
Performing check for Security
Checking security kernel parameters
Checking for semmsl=250; found semmsl=250.      Passed
Checking for semmns=32000; found semmns=32000.  Passed
Checking for semopm=100; found semopm=100.      Passed
Checking for semmni=128; found semmni=128.      Passed
Checking for shmmax=536870912; found shmmax=4294967295. Passed
Checking for shmmni=4096; found shmmni=4096.    Passed
Checking for shmall=2097152; found shmall=268435456.    Passed
Checking for file-max=65536; found file-max=65536.      Passed
Checking for VERSION=2.6.9; found VERSION=2.6.18-128.el5.       Passed
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000.  Passed
Checking for rmem_default=262144; found rmem_default=262144.    Passed
Checking for rmem_max=262144; found rmem_max=262144.    Passed
Checking for wmem_default=262144; found wmem_default=262144.    Passed
Checking for wmem_max=262144; found wmem_max=262144.    Passed
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for GLIBC
Checking Recommended glibc version
Expected result: ATLEAST=2.3.2-95.27
Actual Result: 2.5-34
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for TotalMemory
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 1008MB
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for SwapSpace
Checking available swap space requirements ...
Expected result: 1512MB
Actual Result: 2047MB
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for OracleBase
Validating ORACLE_BASE location (if set) ...
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for DetectAnyInvalidASMHome
Checking for proper system clean-up....
Check complete. The overall result of this check is: Passed
Check complete: Passed
Performing check for CompatibilityChecks_Custom
Checking for Oracle Home incompatibilities ....
Actual Result: NEW_HOME
Check complete. The overall result of this check is: Passed
Check complete: Passed
PrereqChecks complete
........................................... 100% Done.
Global Settings
    Source: /home/oracle/database/stage/products.xml
    Oracle Home: /home/oracle/product/10.2.0/db_1 (OraDb10g_home1)
    Installation Type: Custom
Product Languages
Space Requirements
   /home/ Required 1.21GB : Available 8.40GB
   / Required 108MB (only as temporary space) : Available 1004MB
New Installations (100 products)
   Oracle Database 10g
   Enterprise Edition Options
   Oracle Partitioning
   Oracle Enterprise Manager Console DB
   Oracle Net Services
   Oracle Database 10g
   Oracle Net Listener
   HAS Files for DB
   Oracle Internet Directory Client
   Oracle Call Interface (OCI)
   Oracle interMedia
   Enterprise Manager Agent Core
   Oracle JVM
   Database Configuration and Upgrade Assistants
   Oracle interMedia Locator
   Oracle XML Development Kit
   Oracle Text
   Oracle Database Utilities
   Generic Connectivity Common Files
   Oracle Advanced Security
   Enterprise Manager Repository Core
   Oracle Net
   Assistant Common Files
   Enterprise Manager plugin Common Files Beta
   Buildtools Common Files
   Installation Common Files
   Oracle LDAP administration
   Oracle Java Client
   Precompiler Common Files
   Oracle Recovery Manager
   Enterprise Manager plugin Common Files
   HAS Common Files
   Oracle Clusterware RDBMS Files
   Oracle Wallet Manager
   Enterprise Manager Minimal Integration
   Oracle Database User Interface
   Secure Socket Layer
   Required Support Files
   Database SQL Scripts
   OLAP SQL Scripts
   PL/SQL Embedded Gateway
   Oracle Globalization Support
   Character Set Migration Utility
   LDAP Required Support Files
   Oracle Help for the  Web
   Oracle JDBC Thin Driver for JDK 1.4
   Oracle JDBC Thin Driver for JDK 1.2
   Oracle interMedia Client Option
   Oracle Notification Service
   Oracle Code Editor
   Perl Interpreter
   JDBC Common Files
   Oracle Locale Builder
   Oracle Containers for Java
   Database Workspace Manager
   Oracle Core Required Support Files
   Platform Required Support Files
   Oracle interMedia Locator RDBMS Files
   Oracle JDBC/OCI Instant Client
   Oracle interMedia Annotator
   SQLJ Runtime
   Oracle interMedia Java Advanced Imaging
   Oracle Database 10g interMedia Files
   Oracle Data Mining RDBMS Files
   Enterprise Manager Baseline
   Oracle Help For Java
   Oracle UIX
   XML Parser for Java
   Precompiler Required Support Files
   XML Parser for Oracle JVM
   Oracle Message Gateway Common Files
   Oracle Starter Database
   Sample Schema Data
   Parser Generator Required Support Files
   Agent Required Support Files
   Oracle RAC Required Support Files-HAS
   RDBMS Required Support Files
   RDBMS Required Support Files for Instant Client
   XDK Required Support Files
   DBJAVA Required Support Files
   SQL*Plus Required Support Files
   Oracle JFC Extended Windowing Toolkit
   Oracle Ice Browser
   Oracle Display Fonts
   Oracle Extended Windowing Toolkit
   Enterprise Manager Common Files
   Enterprise Manager Agent DB
   Oracle Net Required Support Files
   Enterprise Manager Repository DB
   SSL Required Support Files for InstantClient
   Bali Share
   Oracle Universal Installer
   Oracle One-Off Patch Installer
   Installer SDK Component
   Java Runtime Environment
   Sun JDK
   Sun JDK extensions
Installation in progress (Thu Apr 12 11:26:13 PDT 2012)
.................................................... ..........  19% Done.
...............................................................  38% Done.
...............................................................  57% Done.
.............................................................    75% Done.
Install successful
Linking in progress (Thu Apr 12 11:43:57 PDT 2012)
...............................................         75% Done.
Link successful
Setup in progress (Thu Apr 12 11:44:36 PDT 2012)
..............                                                  100% Done.
Setup successful
End of install phases.(Thu Apr 12 11:45:01 PDT 2012)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/home/oracle/oraInventory/' with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.

The installation of Oracle Database 10g was successful.
Please check '/home/oracle/oraInventory/logs/silentInstall2012-04-12_11-25-29AM.log' for more details.

[oracle@Ramtek database]$ su - 
[root@Ramtek ~]# /home/oracle/oraInventory/
Changing permissions of /home/oracle/oraInventory to 770.
Changing groupname of /home/oracle/oraInventory to dba.
The execution of the script is complete

For  preparing environment and database installation  click here

Enjoy         :-) 

Wednesday, April 11, 2012

What is redo log thread in oracle ?

On googling about the redo log thread, i have not found proper documentation  that clearly explains clearly what the redo log thread is . Here i am trying to cover the redo log threads in case of single instance and  RAC  taking reference from ASKTOM site .

Each instance has it's own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files) .Two instances will never write to the same redo files - each instance has it's own set of redo logs to write to . Another instance may well READ some other instances redo logs - after that other instance fails for example - to perform recovery. Here is a scenario which helps us to understand the thread concepts .

Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in single Instance(i.e, 1) is actually 
SQL>select  *  from  gv$instance where inst_id= 1 ;

On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our session. 

This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per "thread") and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3. 

When we select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, we will get 3X9 = 27 records in GV$LOG! 
To avoid this: 
1.) Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading  or 
2.)  Add a predicate to match  THREAD#  with  INST_ID. (Beware: thread numbers are by default the same as the instance_id; but we may have defined a different thread number while creating the database) as 
SQL> select * from gv$log where inst_log=thread# ; 

Ref :

Enjoy    :-) 

Tuesday, April 3, 2012

User Managed Hot Backups in Oracle

A cold backup does have the somewhat bad side effect of wiping out our shared pool, our buffer cache and preventing our users from logging in to do work. Our database is  like a car, it runs better when it is warmed up.  If  we want to cold start it - be  prepared for rough running when we restart as we have to rebuild that shared pool, that  buffer cache and so on . I would never pick cold over hot given the chance.  No benefit, only downsides (Acc. to Tkye). The only kind of backup we do on our production systems here is hot .

There are two ways to perform Oracle backup and recovery : 

1.)  Recovery Manager (RMAN) : It is an Oracle utility that can backup, restore, and recover database files. It is a feature of the Oracle database server and does not require separate installation.
2.) User-Managed backup and recovery : We use operating system commands for backups and SQL*Plus for recovery. This method is  called user-managed backup and recovery and  is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.

There are basically two types of  backup .The backup are as 

1.) Consistent Backup :  This is also know as Cold Backup . A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time .
2.) Inconsistent Backup :  This is also known as Hot backup . An inconsistent backup is a backup in which the files being backed up do not contain all the changes made at all the SCNs . This can occur because the datafiles are being modified as backups are being taken. 

There are  some DBAs which prefer oracle user-managed backups.They put their database into backup mode prior to backing  up and take it out of backup mode after backup. If  we 're going to perform user-managed backups, we must back up all of the following file : 
  • Datafiles
  • Control files
  • Online redo logs (if performing a cold backup)
  • The parameter file (not mandatory )
  • Archived redo logs
  • Password file if used

The below diagram shows the Whole Database Backup Options :  
A hot backup requires quite a bit more work than cold backup.Below are steps required for Hot backup.

Step 1 :  Check the log mode of the database  Whenever we go for hot backup then the database must be in archivelog  mode . 

Step 2 :  Put the database into backup mode  If we are using the oracle 10gR2 or later , then we can put the entire database into backup mode and if we are using the oracle prior to 10gR2 ,then we have to put each tablespace in backup mode . In my case , I am having 11gR2 . 
SQL> alter database begin backup ; 
Database altered.
In case of oracle prior to 10gR2 use the below command as 
SQL> set echo off 
SQL> set heading off 
SQL>  set feedback off 
SQL> set termout  off 
SQL> spool backmode.sql 
SQL> select 'alter tablespace  '||name||'  begin backup ;'   "Tablespace in backup mode"  from v$tablespace;
SQL> spool off 
SQL>  @C:\backmode.sql 

Step 3 :  Backup all the datafiles  Copy all the datafile using the operating system command and Paste it on the desired backup location .Meanwhile,we can verify the status of the datafile by using the v$backup view  to check the status of the datafiles.
SQL> select  *  from  v$backup ; 
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                3967181 03-APR-12
         2 ACTIVE                3967187 03-APR-12
         3 ACTIVE                3967193 03-APR-12
         4 ACTIVE                3967199 03-APR-12
         5 ACTIVE                3967205 03-APR-12
         6 ACTIVE                3967211 03-APR-12
         7 ACTIVE                3967217 03-APR-12
         8 ACTIVE                3967223 03-APR-12
         9 ACTIVE                3967229 03-APR-12
The Column STATUS=ACTIVE  shows that the datafiles are in backup mode . 

Step  4  : Take out the database from backup mode  If we are using 10gR2 or above version of oracle , we use the below command to take out the database from backup mode as 
SQL> alter database end backup ; 
Database Altered 
If we are having version prior to 10gR2 , then we use the below command as above : 
SQL> set echo off 
SQL> set heading off 
SQL> set feedback off 
SQL> set termout  off 
SQL> spool end_mode.sql 
SQL> select  'alter tablespace  '||name||'  end backup ;'   "tablespace in backup mode"  from v$tablespace ; 
SQL> spool off 
SQL> @C:\endmode.sql 

Step 5 :  Switch the redolog file and backup archivelogs   After taking the database out of Hot Backup we must switch logfile (preferably more than once) and backup the archivelogs generated .We may backup archivelogs while the database is in backup mode but we must also backup the first archivelog(s) after the end backup. The best method to do both is to run the SQL command alter system archive log current. This switches the logfile but does not return the prompt until the previous redo log has been archived. We can run alter system switch logfile, but then we won't be sure that the latest redo log has been archived before we move on to the next step. 
SQL> alter system archive log current ; 
System altered.
System altered.
Now backup the archivelogs to the backup location .

Step 6  : Back up the control file  Now , we can backup the controlfile as binary file and as human readable .We should use both methods to back up the control file; either one may come in handy at different times . The commands are as 
(Human readable)
SQL> alter database backup controlfile to trace ;  or 
Database altered.
SQL> alter database backup controlfile to trace as '<backup location>' ; 
Database altered.
(Binary format)
SQL> alter database backup controlfile to '<backup location>' ; 
Database altered.

Step 7 : Backup the passwordfile and spfile  We can backup the passwordfile and spfile though it is not mandatory.

Some Points Worth Remembering 
  • We  need to backup all the archived log files, these files are very important to do recovery. 
  • It is advisable to backup all of  tablespaces (except read-only tablespaces), else complete recovery is not possible.
  • Backup of online redo log files are not required, as the online log file has the end of backup marker and would cause corruption if used in recovery.
  • It is Preferable to start the hot backups at low activity time.
  • When hot backups are in progress we  "cannot" shutdown the database in NORMAL or IMMEDIATE mode (and it is also not desirable to ABORT).

For More Click Here 

Enjoy    :-)