Tuesday, May 24, 2011

FINDING SCHEMA SIZE FOR A ORACLE DATABASE


SQL> set pagesize 10000
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL OF "Size of Each Segment in MB" ON REPORT
SQL> select segment_type, sum(bytes/1024/1024) "Size of Each Segment in MB" from dba_segments where owner='SYS' group by segment_type order by 1;

SEGMENT_TYPE       Size of Each Segment in MB
------------------          --------------------------
CLUSTER                              1872.0625
INDEX                                   1419.3125
INDEX PARTITION                    166.25
LOB PARTITION                          .1875
LOBINDEX                                17.1875
LOBSEGMENT                        478.8125
NESTED TABLE                                   1
ROLLBACK                                          2
TABLE                                      1218.875
TABLE PARTITION                 441.3125
TYPE2 UNDO                         1055.5625
                                    --------------------------
TOTAL                                    6672.5625

11 rows selected.

Wednesday, May 18, 2011

Recreating Oracle Inventory in 10g/11g


CREATE / RECREATING CORRUPTED ORACLE INVENTORY IN UNIX SYSTEMS FROM 10G & 11G

The Oracle Universal Installer creates the oraInventory directory the first time it is run on a computer. oraInventory is repository which store/records oracle software products & their ORACLE_HOME location on a machine. This Inventory now days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.
There are basically two kind of Inventory Global Inventory (also called as Central Inventory) and Local Inventory also called as Oracle Home Inventory.

Below document explains you the step by step approach for recreating the oracle inventory for oracle 10g & 11g database.

Note:
1)  No down time is required for recreating the global inventory (oraInventory)
2)  if you have corrupted oracle inventory or improper oracle inventory, you can rename the directory to avoid the confusion.

      mv oraInventory oraInventory_orig

Central Inventory
-----------------
Central Inventory contains the information relating to all Oracle products
Installed on a host. Central inventory (oraInventory) is an inventory that
lists ORACLE_HOMEs installed in the system using the inventory.xml file.
Each central inventory consists of a file called inventory.xml, which
contains the list of Oracle Homes installed.

Local Inventory
---------------
Oracle home inventory or local inventory is present inside each Oracle home.
It contains information relevant to the particular Oracle home only.
This inventory contains, among other things, a file called comps.xml,
which contains all the components  as well as patchsets or interim patches
installed in the ORACLE_HOME.

To determine where oraInventory is located

/var/opt/oracle/oraInst.loc or /etc/oraInst.loc depending upon the Platform.

Sample oraInst.loc file

/var/opt/oracle/oraInst.loc
inst_group=dba
inventory_loc=/apps/oracle/product/oraInventory

ORACLE_HOME/oraInst.loc
inst_group=dba
inventory_loc=/apps/oracle/product/oraInventory




To find the ORACLE_HOME & ORACLE_HOME_NAME

if you have oracle old inventory, then you can view from
/apps/oracle/product/oraInventory/ContentsXML/inventory.xml
you can see ORACLE_HOME & ORACLE_HOME_NAME

SAMPLE OUTPUT

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 2009 Oracle Corporation. All rights Reserved -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>10.2.0.5.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="orahome_102" LOC="/apps/oracle/product/10.2.0.2" TYPE="O" IDX="1"/>
<HOME NAME="agent10g" LOC="/apps/oracle/product/agent10g" TYPE="O" IDX="2"/>
</HOME_LIST>
</INVENTORY>


Go to Oracle Universal installer location for creating Oracle Inventory
$ORACLE_HOME/oui/bin

if you have more than one Oracle product, you have to update the inventory for all the oracle home
Ex: Oracle DB home(different versions includes)
    Oracle Agent

Note: when you are running for different homes, you have run the oui in there respective home only
     
Ex: output for ORACLE_HOME(run it in $ORACLE_HOME/oui/bin)

SAMPLE OUTPUT

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/apps/oracle/product/10.2.0.2" ORACLE_HOME_NAME="orahome_102"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /apps/oracle/product/oraInventory
'AttachHome' was successful.

Ex: output for AGENT_HOME(run it in $AGENT_HOME/oui/bin)




SAMPLE OUTPUT

$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/apps/oracle/product/agent10g" ORACLE_HOME_NAME="agent10g"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

>>> Ignoring required pre-requisite failures. Continuing...

The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /apps/oracle/product/oraInventory
'AttachHome' was successful.



We can apply the patch by specify oracle inventory location by the below option.

-- Apply the Patch with the address of the Oracle Inventory

$ opatch apply -invPtrLoc /app/oracle/oraInst.loc

$ opatch lsinventory -invPtrLoc /app/oracle/oraInst.loc


We can apply the patch without the oracle inventory location also by the below option. BUT THIS IS NOT A BEST PRATICE.

The patch can be applied to an ORACLE_HOME with -no_inventory option, but then you won’t have the record of patches applied to that ORACLE_HOME.