Oracle Central Inventory is corrupted [10.2]

We had a linux machine having oracle client 10.2.0.1 installed on it and we were preparing to install a patch for "sqlplus".
To check "opatch" we executed "opatch lsinventory" and found below output:

$ opatch lsinventory

LsInventory Session failed: OPatch failed to locate Central Inventory.
Possible causes are:
The Central Inventory is corrupted
The oraInst.loc file specified is not valid.

OPatch failed with error code 72

According to the output "Oracle Inventory" is corrupted.
Then we check the inventory location form file "/oracle/product/10.2.0/client_1/oraInst.loc" which was "/oracle/base/oraInventory".
Then we executed below set of commands [under oracle user] to repaier "Oracle Inventory":

$ mv /oracle/base/oraInventory /oracle/base/oraInventory_old
$ cd /oracle/product/10.2.0/client_1/oui/bin #location of OUI
$ ./runInstaller -silent -invPtrLoc "/oracle/product/10.2.0/client_1/oraInst.loc" -attachHome ORACLE_HOME="/oracle/product/10.2.0/client_1" ORACLE_HOME_NAME="Ora10gHome"

Starting Oracle Universal Installer...

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

Check with opatch:
$ opatch lsinventory

Now we had "OPatch succeeded."
Thanks to http://www.oraclemasters.in/?p=412

sqlplus hangs [10.2.0.1]

On a Redhat Linux 4.3 32bit machine, we just installed oracle client 10.2.0.1.
While we were trying to use sqlplus as below, it hanged:

$ sqlplus /nolog

Not only this command all the commands with "sqlplus" were also hanged.
But tnsping was working fine.

Solution:
This is a bug [MOS Note ID 338461.1].
When the machine uptime hits 248 days, this bug hits.
In our case up time was 406 days.

And there are two solution restart the machine or apply patch 4612267.
In our case we applied the patch and just after patch installation, "sqlplus" started working fine.

Determine Number of Log Switch for Oracle Database

A very common query, which can be found from many sources. I am keeping this for my personal reference:


SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
;

Oracle Datamining 11gR2 Repository Installation on Oracle Database 11.2.0.1

We followed below steps to install Oracle Datamining 11gR2 Repository our Oracle Database 11.2.0.1 [Exadata V2]:

Download scrits for ODM 11.2 form
http://www.oracle.com/technetwork/database/options/odm/dataminerinstallationscripts-112-323231.zip
and follow steps below:


1. Check Data Mining Option is installed in the database, if not then install Oracle Option on the Oracle Database:

SELECT * FROM V$OPTION where value='TRUE' and parameter='Data Mining';

If above returns a row then Data Mining Option is installed on the Database.
If it is not installed then install it using MOS Note:ID 818314.1

2. Check Oracle Text is installed:

select * from dba_registry where comp_id = 'CONTEXT';

If above returns no rows then, install Oracle Text using MOS Note:ID 970473.1

3. Check XML/XDB is installed:

select * from dba_registry where comp_id ='XDB';

If above returns no rows then, install XML using MOS Note:ID 1292089.1

** But while inatalling XDB we found below error, because JVM was not installed on this DB, so we need to install JVM:

GRANT javauserpriv TO xdb
*
ERROR at line 1:
ORA-01919: role 'JAVAUSERPRIV' does not exist


4. Check whether JAVA/JVM is installed:

select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;

select * from dba_registry where comp_id in('CATJAVA','JAVAVM');

If JVM is not installed then install it using MOS Note:1112983.1

After JVM installation, run below command:

SQL> GRANT javauserpriv TO xdb;

5.Create tablespace for ODM repository:

CREATE TABLESPACE "TBS_ODM" DATAFILE '+DATA1(DATAFILE)' SIZE 1024M auto extend off; --need atleast 1GB space

6. Create ODM user:

create user ODMUSER identified by password;
alter user ODMUSER default tablespace TBS_ODM temporary tablespace TEMP;
grant create session to ODMUSER;
alter user ODMUSER quota unlimited on TBS_ODM;-- [for default tablespace]

Nevigate to unzipped folder of downloaded dataminerinstallationscripts-112-323231.zip and run commands in the below steps from sqlplus:

7. Create ODM Repository:[@installodmr.sql ][as sysdba]
set serveroutput on
set echo on
@installodmr.sql TBS_ODM TEMP

8. Granting a User the Rights to Access the Repository: [@usergrants.sql ]
set serveroutput on
set echo on
@usergrants.sql ODMUSER

Now Check as: [tested with sqldeveloper-3.0.04.34]
- Open Oracle sql developer
- goto View > Data Miner > Data Miner > Dataminer Connection
- Give: Connection Name, Username, password and connection details
- "Data Miner Panel" will be shown
- Now form Data Miner Panel, try to browse your newly created connection
and you will not be warrened some thing like "no data miner repository found"

9. To drop ODM repository:

@dropRepositoryAndUserObjects.sql

Installing JVM on Oracle Database 11gR2

JVM was not installed on our Oracle 11gR2 database and we installed it manually by executing below steps from sqlplus login directly on the server [please follow all th steps]:

spool /home/oracle/ODMRINSTL/JVM/jvm_install.txt
set echo on
connect / as sysdba
shutdown immediate
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
shutdown immediate
set echo off
spool off
exit

Verify with below queries & outputs:

SQL> select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;

OWNER COUNT(*)
------ ----------
SYS 21313
EXFSYS 48

SQL> select comp_id,comp_name from dba_registry where comp_id in('CATJAVA','JAVAVM');

COMP_ID COMP_NAME
------- ------------------------------
JAVAVM JServer JAVA Virtual Machine
CATJAVA Oracle Database Java Packages

We have followed above for our case. For details and uninstallation please go through MOS Note:1112983.1 and prepare steps for your case.

Install XDB on Oracle Database 11gR2

We have prepared below steps to install XDB on our Oracle Database 11gR2.
For details please follow MOS Note:ID 1292089.1.

1. install XDB

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql welcome SYSAUX TEMP NO
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

2. verify

- Check status of XDB

select comp_name, version, status
from dba_registry
where comp_id = 'XDB';

- Check for invalid objects owned by XDB

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';

Install Oracle Text on Oracle Database 11gR2

We have prepared below steps to install Oracle Text on our Oracle Database 11gR2.
For details please follow MOS Note:ID 970473.1

1. Install Oracle Text

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql welcome SYSAUX TEMP NOLOCK

2. install language

SQL> connect CTXSYS/welcome
SQL> @?/ctx/admin/defaults/dr0defin.sql "AMERICAN";
SQL> connect SYS/password as SYSDBA
SQL> alter user ctxsys account lock password expire;
SQL> spool off

3. verify

SQL> select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';

COMP_NAME STATUS VERSION
------------------- -------- ----------
Oracle Text VALID 11.2.0.1.0

SQL> select * from ctxsys.ctx_version;

VER_DICT VER_CODE
---------- ----------
11.2.0.1.0 11.2.0.1.0

SQL> select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

VER_CODE
----------
11.2.0.1.0

SQL> select count(*) from dba_objects where owner='CTXSYS';

COUNT(*)
----------
366

- Get a summary count

SQL> select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 3
PROCEDURE 2
OPERATOR 6
PACKAGE 73
PACKAGE BODY 62
LIBRARY 1
LOB 2
TYPE BODY 6
VIEW 71
INDEXTYPE 4
FUNCTION 2
TABLE 47
INDEX 56
TYPE 31


- Any invalid objects
SQL> select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

no rows selected