Category Archives: 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.Many companies has implemented their data in Oracle. Most of them are mission critical. So Oracle database concept need to be very clear to manage these mission critical things. At the same time Oracle database needs to deliver high performance also. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

Below section I have tried to present all the important features in Oracle Database management

How to find which sid is doing full table scan

Last updated on December 21st, 2015 at 05:42 amMany times the performance of the database will be slow.We need to first find out  if any big table full table scan is going on. We can use below query to find out what all the sid are doing full tables scan column user_process heading “Name |SID” format a20; column long_scans heading “Long Scans” format 999,999,999; column … Continue reading How to find which sid is doing full table scan »

Oracle Wait Events That Everyone Should Know Part -1

Last updated on July 28th, 2016 at 05:59 pmThis is part I in series of Oracle Wait Events That Everyone Should Know Here are some of the Common Oracle wait events that everyone should Know. Wait events You can find which event session is waiting for it by following query select event from V$session_wait where sid=&1 I am trying to explain few common Oracle wait … Continue reading Oracle Wait Events That Everyone Should Know Part -1 »

What is Oracle Clusterware?

Last updated on November 28th, 2015 at 12:28 pmThe Oracle Clusterware was introduced in 10.1 (initially called CRS ) and is the product that lies under RAC. Oracle Clusterware provides different services to RAC including: – Group Services – Node Monitor – Locking services – HA Resource management – Event framework, etc Components.Several components are needed to have CRS running on Unix machine. Here is … Continue reading What is Oracle Clusterware? »

How to find RAC interconnect information

Last updated on November 28th, 2015 at 12:25 pmHere are a few ways to How to find RAC interconnect information 1. Using the dynamic view gv$cluster_interconnects:SQL> select * from gv$cluster_interconnects; INST_ID NAME IP_ADDRESS IS_ SOURCE ———- ————— —————- — ——————————- 1 en12 192.158.100.65 NO Oracle Cluster Repository 2 en12 192.158.100.66 NO Oracle Cluster Repository 2. Using the clusterware command oifcfg: $oifcfg getif en10 10.127.149.0 global … Continue reading How to find RAC interconnect information »

Single Client Access Name (SCAN)

Last updated on August 27th, 2016 at 08:41 amWhat is Single Client Access Name (SCAN) It is a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes in the cluster. … Continue reading Single Client Access Name (SCAN) »

How to recover corrupt blocks using RMAN

Last updated on December 21st, 2015 at 05:34 am1. Recovering Data blocks By Using All Available Backups Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15; Recover multiple blocks in single command RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15 DATAFILE 2 BLOCK 10; 2. Recovering Data blocks Using Selected Backups Run the BLOCKRECOVER … Continue reading How to recover corrupt blocks using RMAN »

How to identify corrupts blocks in the database

Last updated on December 21st, 2015 at 05:35 am1. Run below command to populate v$database_block_corruption view with information of all the corrupted blocks. RMAN> backup validate check logical database; “CHECK LOGICAL” option is used to identify both Physical and Logical Block Corruptions. Select the view to identify the corrupted blocks detected by RMAN. SQL> select * from v$database_block_corruption; Please note, After a corrupt block is … Continue reading How to identify corrupts blocks in the database »

How to create recovery catalog and register database in catalog

Last updated on December 21st, 2015 at 04:46 amRecovery catalog is a very important registry. Control file registry get rotated after some time so you lost many backup information. By storing backup information in recovery catalog ,you get rid of this problem Also it provides a central repository where you can find all the backup information. Here are the steps on How to create recovery catalog … Continue reading How to create recovery catalog and register database in catalog »

RMAN connection decoded

Last updated on December 21st, 2015 at 04:54 amRMAN is a important tool used in Backup and recovery. Whenever you use RMAN tool in $ORACLE_HOME/bin and connect to database, it makes multiple connection to the database.Here are RMAN connection decoded Whenever we make RMAN connection to database for backup with or with out catalog,many processes are spawned and worked upon. RMAN always spawns TWO processes (sessions) … Continue reading RMAN connection decoded »

How to find how much space is actually used by the lobsegments

Last updated on November 28th, 2015 at 12:35 pmLet us take the example of FND_LOBS tables in Oracle Apps Database.FND_LOBS has LOB datatype column FILE_DATA How to  find how much space is actually used by the lobsegments Query to find the actual bytes used by the lob datatype select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS; SUM(DBMS_LOB.GETLENGTH(FILE_DATA)) ———————————- 13313 The total space allocated in the extents could be … Continue reading How to find how much space is actually used by the lobsegments »

How to correct error RMAN-20004

Last updated on December 21st, 2015 at 04:14 amProblem:When Recovery catalog has been configured.and Production Database has been cloned and a connection has been made from the clone to Catalog database using rman with out changing the DBID of the clone,then we are in some trouble Then when you try to backup the production instance with the catalog,The backup fails with following error RMAN-06004: ORACLE … Continue reading How to correct error RMAN-20004 »

Result cache in 11g

Last updated on September 1st, 2016 at 05:21 amPrior to 10g, every query must be re-executed even if there is no change in the tables. This reexecution is unnecessary for few static tables and only caching using at client tools resolve this issue. But, in 11g, query results can be cached in SGA. Just to avoid confusion, this is much different from buffer cache caching … Continue reading Result cache in 11g »