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

Timezone settings

Last updated on December 21st, 2015 at 06:15 amI am presenting here the some of the good information on timezone settings How to find the latest DST patch applied SQL> select * from v$timezone_file; FILENAME VERSION ———— ———- timezlrg.dat 4 This select gives all TimeStamp with Time Zone (TSTZ) columns in your database: select c.owner || ‘.’ || c.table_name || ‘(‘ || c.column_name || ‘) … Continue reading Timezone settings »

How to find table where statistics are locked

Last updated on December 21st, 2015 at 06:17 amYou can use the below query on How to find table where statistics are locked select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; Now once we find out the objects,we can use below queries to unlock them exec dbms_stats.unlock_schema_stats(‘schema_owner’); exec dbms_stats.unlock_table_stats(‘table_owner’,’table_name’);

How to migrate the schema from one database to another database

Last updated on December 21st, 2015 at 05:50 amHere ,I will give u Detailed steps for Schema Migration using exp/imp,pipe,gz between two databases How to migrate the schema from one database to another database The below steps need to be done on the Source Database Metadata about schema 1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in (‘EXAMPLE_SCH’) group by owner; 2. SQL> select tablespace_name,sum(bytes)/1024/1024 … Continue reading How to migrate the schema from one database to another database »

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 »