UCM Service Can't Start Due To Jdbc Temp Table

UCM service can't start and stay in "Admin" mode in WebLogic Console
From weblogic .out file ,we can see such error

java.sql.SQLException: !csJdbcTempTableExists,USERSTemp,Users
        at intradoc.jdbc.JdbcWorkspace.alterTable(JdbcWorkspace.java:1679)
        at intradoc.server.datastoredesign.DataDesignGenerator.alterTable(DataDesignGenerator.java:2075)
        at intradoc.server.datastoredesign.DataDesignGenerator.generateTable(DataDesignGenerator.java:255)
        at intradoc.server.datastoredesign.DataDesignInstall.configTableForComponents(DataDesignInstall.java:284)
        at intradoc.server.IdcExtendedLoader.dataStoreDesignConfigTables(IdcExtendedLoader.java:2551)
        at intradoc.server.IdcExtendedLoader.performDatabaseUpgradeTasks(IdcExtendedLoader.java:397)


It's quite possible there was hiccup when we create weblogic DataSource or mis-communication  somewhere among UCM cluster. The temp was not deleted after test

we need to drop it manually on DB end to fix the issue

How To Check Dead Process in Linux

use lsof command to check if any files deleted when processes are running

    * lsof -p PID

use strace to check the pids are not processing anything

    * time strace -p PID

use top  then H, to see all the threads

or use -m in ps

ps -efwm|grep httpd

strace -o /tmp/strace.output  -p  PID

how to check which process is taking a port
for example, apache can't start on port 80

lsof -i TCP:80

How to Recover Datafile From Standby DB

Goal

Problem Statement

How to recover the primary database's datafile using a copy of a standby database's datafile.

and

How to recover the standby database's datafile using a copy of a primary database's datafile.
Solution
Recovering the Primary's Datafile

How to recover the primary database's datafile using a copy of a standby database's datafile.

1) copy the standby database's datafile

2) ftp the file to the primary site

3) catalog the datafile copy on the primary site

4) on the primary site restore and recovery the datafile copy

This procedure will work for all file systems - cooked, raw or ASM.
Example

Through this example we will be using datafile 25.

1) On standby database, copy datafile from ASM to a cooked file system:

       9i:  RMAN> copy datafile 25 to '/tmp/df25.cpy';

       10g: RMAN> backup as copy datafile 25 format '/tmp/df25.cpy';


2) FTP the file to primary server on cooked system

On primary database
~~~~~~~~~~~~~~~~~~~
3) catalog this datafile copy:
       SQL> alter database datafile 25 offline;
       RMAN> catalog datafilecopy '/tmp/df25.cpy';

4) Confirm that datafile exists:
       RMAN> list copy of datafile 25;

5) Restore the datafile:
       RMAN> restore datafile 25;

6) Recover the datafile:
       RMAN> recover datafile 25;


recover datafile 7 until time '09-AUG-2010 14:00:00';

recover datafile 7 until time to_date('09-aug-2010 14:00:00','dd-mon-rrrr hh24:mi:ss')

7) Place the datafile online:
       SQL> alter database datafile 25 online;


Recovering the Standby's Datafile

If recovering the standby, reverse the steps. That is:

1) copy the primary database's datafile

2) ftp the file to the standby site

3) catalog the datafile copy on the standby site

4) stop Redo Apply on the Physical Standby Database

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5) on the standby site restore and recovery the datafile copy

A Useful Sql To Check Tablespace Growth

To find latest 30 days tablespace growth report:

set heading on
set linesize 5500
set pages 999

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
   , ts.tsname
   , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
   , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
   FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  , DBA_HIST_TABLESPACE_STAT ts
    , DBA_HIST_SNAPSHOT sp
    , DBA_TABLESPACES dt
    WHERE tsu.tablespace_id= ts.ts#
   AND tsu.snap_id = sp.snap_id
   AND ts.tsname = dt.tablespace_name
   AND ts.tsname  IN ('TEST1','TEST2')
   AND sp.begin_interval_time  between sysdate -30 and sysdate
   GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
   ORDER BY ts.tsname, days;

To find specific date of tablespace report: ie 01sep2012 or 11nov0212

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
   , ts.tsname
   , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
   , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
   FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  , DBA_HIST_TABLESPACE_STAT ts
    , DBA_HIST_SNAPSHOT sp
    , DBA_TABLESPACES dt
    WHERE tsu.tablespace_id= ts.ts#
   AND tsu.snap_id = sp.snap_id
   AND ts.tsname = dt.tablespace_name
   AND ts.tsname  IN ('TEST1','TEST2')
   AND TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') = '01-09-2012'
   GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
   ORDER BY ts.tsname, days;

then you can easily figure how much space growth between 01sep2012 and 11nov0212

How To View Oracle Text Query Exec Plan

Oracle Text Query is not like normal sql which we can get sql plan via "explain plan for" "select * from table(dbms_xplan.display());".....

It has specific API to get them:

Creating the Explain Table
To create an explain table called test_explain for example, use the following SQL
statement:
create table test_explain(
explain_id varchar2(30),
id number,
parent_id number,
operation varchar2(30),
options varchar2(30),
object_name varchar2(64),
position number,
cardinality number);

Running CTX_QUERY.EXPLAIN
To obtain the expansion of a query expression such as comp% OR ?smith, use CTX_QUERY.EXPLAIN as follows:
ctx_query.explain(
index_name => ''TEST_IDCTEXT2',
text_query => 'comp% OR ?smith',
explain_table => 'test_explain',
sharelevel => 0,
explain_id => 'Test');

exec ctx_query.explain(index_name => 'TEST_IDCTEXT2',text_query => '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount))))',explain_table => 'test_explain',sharelevel => 0,explain_id => 'Test');

Retrieving Data from Explain Table
To read the explain table, you can select the columns as follows:

  select lpad('   ',level-1)||operation||' '||options||' '||object_name "Plan"
 from test_explain
  connect by prior id = parent_id
start with id=1
 order siblings by position