Discoverer server

About Discoverer:

Discoverer is the end user adhoc query, reporting and analysis tool, which provides quick development environment to develop Data Warehousing & Business intelligence reports.

What is business intelligence?

Business Intelligence is the ability to analyze data to answer business questions and predict futures.

Features and Benefits:

• Automated Summary Management – improving query performance in Discoverer Plus is now easier with this new feature.
• Oracle Applications support – This enables you to connect to Oracle Applications EULs as well as standard Discoverer EULs.
• Identifiers – all objects in a Business Area are now uniquely defined within each EUL by an Identifier.
• Change schema owner – you can now manually edit the schema Owner attribute (for a Folder) or leave it blank.
• Support for Oracle Materialized Views.
• Analytic Functions – perform complex mathematical analysis with this extended range of statistical functions.

How does it Works?

When a user creates or opens a worksheet, Discoverer:

•  Converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively).
•  Sends the SQL statements to the database.
•  Displays the result set that is returned from the database.

Discoverer Components:

•  Oracle Discoverer Administrator
•  Oracle Discoverer Plus
•  Oracle Discoverer Desktop Edition (Viewer)

Oracle Discoverer Administrator:

Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer.

Discoverer Administrator’s wizard-style interfaces enable you to:

• Set up and maintain the End User Layer (EUL)
• Create Folders, Items and Item Classes
• Control access to information
• Create conditions and calculations for Discoverer end users to include in their worksheets

Oracle Discoverer Plus:

It is a web portal like Oracle Application front end through which you can log on with your oracle application username and password and create and run discoverer reports.

Oracle Discoverer Desktop Edition (Viewer):

It works same as Discoverer Plus, but it is a software that comes with Oracle BI Publisher setup. Here also you can create and run your discoverer reports.

End User Layer (EUL):

End User Layer is a Metadata repository for Business areas, folders and items on which Discoverer workbooks or reports based on. The EUL is a set of database tables that contain information (or ‘metadata’) about the other tables and views in the database.

Business Area:

A Business Area is a set of folders containing related information with a common business purpose.

Business Areas:

• Meet the specific data needs of the users
• Usually contain data from several different tables or views
• Have tables or views and their associated columns mapped to “folders” and “items” respectively
• Can contain many simple folders and complex folders
• May contain folders from one or more physical databases include conditions, joins, calculations, formatting,   hierarchy structures, and other custom features
•  May be accessible to one or many user IDs or roles; also, a user ID or role may be granted access to many business areas
•  Let users access data, without having to understand the database structure

Folders:

The tables and views loaded into a Business Area are called as folders. There are 3 types of folders that you can create – Simple, Complex and Custom.

Items:

The columns of the folders are named as items.

Item Classes:

Item classes are like LOVs in Oracle Application. We can create item classes based on any columns of a table and we can join that item classes to any parameters of the discoverer reports.

Joins:

A Join relates two folders using common items.

Conditions:

Conditions filter worksheet data, enabling Discoverer end users to analyze only the data they are interested. Conditions are applied to the folders or items.

Calculations & Hierarchies:

A calculated item is an item that uses a formula to derive data for the item.

Hierarchies are default drill paths between item that you define in Discoverer Administrator. There are two types of Hierarchy in Discoverer Administration Edition:

1. Item Hierarchies
2. Date Hierarchies

Summaries & Summary Folders:

A summary improves query performance in Discoverer Plus or Discoverer Viewer by using pre-aggregated data created through Discoverer Administration Edition.

A Summary Folder is how Discoverer represents the underlying structure of a summary table or MV. Each Summary Folder has one or more Items (i.e. columns from a summary table or MV).

ASM:

Automated Summary Management – It delivers fast performing queries while greatly reducing the amount of time spent on administration. ASM analyzes your tables, uses query statistics (when available) and default values (the summary policy) to determine how summaries are created for you. ASM is able to automatically create and maintain the best set of summaries based on this combination.

Discoverer4i Troubleshooting

Below are the errors which we had encountered post upgrade of Discoverer4i wherein the discoverer component doesn't startup. 
This post will be a running document, wherein it will be updated as and when we encounter a new issue and obviously a solution 

First -> Make sure Jserv is up and running. 

Issue 1  OAD Fatal Error in oad.log

$ORACLE_HOME/discwb4/util > vi oad.log 
"oad.log" 3 lines, 184 characters OAD: Fatal Error [Cross-device link]. 

Solution Make sure VBROKER_ADM environment variable is set up correctly. 
________________________________________
 Issue 2  Unable to create repository file: 

$ORACLE_HOME/vbroker/adm/impl_dir/impl_rep. in oad.log 

Solution Add this line in $ORACLE_HOME/discwb4/discwb4.sh 
TMPDIR=/dchpgi/applmgr/product/806/discwb4/tmp 
export TMPDIR 
________________________________________
 Issue 3  DISCOVERER VIEWER is not working. 
Abstract: DISCOVERER VIEWER DOES NOT WORK WITH THE PARAMETER "SHOW_GRAPHS=TRUE" 

Solution The file $IAS_ORACLE_HOME/Apache/Apache/htdocs/disco4iv/html/disco4iv.html has to have the entry 
show_graphs="false" instead of show_graphs="true". 
This is the fix and the relevant bug is 2664219. 
________________________________________
 Issue 4  DISCOVERER VIEWER Page is blank and also hanging for long time 

Solution Correct the entry for the TNS in discwb.sh to point to correct TNS_ADMIN value. 
echo $TNS_ADMIN can be used to get the value. Bounce the apache and start the services. 
________________________________________
 Issue 5 INTERNAL SERVER ERROR on Viewer Page 
Solution Check these values wrapper.classpath=$IAS_ORACLE_HOME/Apache/Apache/htdocs/disco4iv/disco4iv.jar was added in viewer4i.properties file before the translator.zip 

It should be in two separate lines
 wrapper.classpath=$IAS_ORACLE_HOME/Apache/Apache/htdocs/
disco4iv/disco4iv.jar wrapper.classpath=$IAS_ORACLE_HOME/sqlj/lib/translator.zip 
________________________________________
 Issue 6 Unable to find the Locator.ior file. Error message in Discoverer viewer page 
Solution vi $IAS_ORACLE_HOME/Apache/Apache/htdocs/disco4iv/html/disco4iv.xml 
Locator name="Hostname.domainname_PortOracleDiscovererLocator4" 
path="$IAS_ORACLE_HOME/Apache/Apache/htdocs/discwb4/applet/"/> Modify the Alias (file or path) to the Real URL: $IAS_ORACLE_HOME//Apache/Apache/htdocs/disco4iv/html/disco4iv.xml servlet.Viewer.code=oracle.discoiv.Disco4iv servlet.Viewer.initArgs=config=file:$IAS_ORACLE_HOME/Apache/Apache/htdocs/disco4iv/html/disco4iv.xml 
________________________________________
 Issue 7 Discoverer BIS report not working. 
Solution To resolve errors accessing Discoverer BIS report 
(login as SYSADMIN, on Selfservice application -> Disco BIS Report) Add FND_DEBUG_DIR=$APPLCSF/tmp ; 
export FND_DEBUG_DIR in $ORACLE_HOME/discwb4/discwb.sh 
and bounce discoverer services. 
________________________________________
 Issue 8 Unable to start locator session while starting discoverer services 
Solution Go to $ORACLE_HOME/discwb4 
Verify values in discwb4.sh vi discwb4.sh 
INSTANCE_NAME=Hostname.DomainName_Port TMPDIR=$ORACLE_HOME/discwb4/tmp 
Export TMPDIR Save and exit the file 
cd $VBROKER_ADM pwd $ORACLE_HOME/vbroker/adm 
cd impl_dir mv impl_rep impl_rep_bkp mv impl_rep~ impl_rep~_bkp 
Run Registerall.sh will create these new folders. ls README README.txt impl_rep impl_rep_bkp impl_rep~ 
impl_rep~_bkp 
________________________________________
 Issue 9 Unable to start Gatekeeper services 
Solution Go to $IAS_ORACLE_HOME/Apache/htdocs/discwb4/applet 
directory Check exterior_port in 
$ORACLE_HOME/vbroker/bin/gatekeeper.properties and 
$APACHE_TOP/Apache/htdocs/discwb4/applet/
gatekeeper.properties Check the gatekeeper.log file for errors like 
'unable to bind to port' or other errors. Fix the error and restart the gatekeeper.

Database Size

DB UP TIME

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours" from sys.v_$session where sid=1 /* this is PMON */ /

DB SIZE (Database Size,Used space,Free space)

col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p;

Database grouth year wise

select to_char(b.CREATION_TIME,'YYYY'), count(1) "NO_OF_FILES", sum( round(b.bytes/1024/1024,2)) "SIZE_IN_MB" from v$datafile b,dba_data_files a where a.file_id=b.file# group by to_char(b.CREATION_TIME,'YYYY') order by to_char(b.CREATION_TIME,'YYYY') desc;

Database grouth monthly wise

select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month');

Calculate total space for all datafiles within database.

select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

Calculate space for list of tablespaces within primary database.

select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

Schema SIZE

select sum(bytes)/1024/1024/1024 from dba_segments where owner=upper('&owner');

Tablespace Information

ALL TABLESPACES SIZE IN MB

SET LINES 150 SET pages 300 col “Tablespace” FOR a30 col “Total MB” FOR a15 col “Free MB” FOR a15 col “Used MB” FOR a15 col “% Used” FOR a15 SELECT tablespace_name “Tablespace”, d.STATUS “Status”, TO_CHAR((a.bytes / 1048576),’99,999,990.900′) “Total MB”, TO_CHAR(((DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),’99,999,990.900′) “Free MB”, TO_CHAR(((a.bytes – DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),’99,999,990.900′) “Used MB”, TO_CHAR((((a.bytes – DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),’999,999.9′) “% Used” FROM (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name)) LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name) ORDER BY 6 /Another useful query col “Tablespace” for a13 col “Used MB” for 99,999,999 col “Free MB” for 99,999,999 col “Total MB” for 99,999,999 col “Block Size” for 9,999,999 select df.tablespace_name “Tablespace”, block_size “Block Size”, (df.totalspace – fs.freespace) “Used MB”, fs.freespace “Free MB”, df.totalspace “Total MB”, round(100 * (fs.freespace / df.totalspace)) “Pct. Free” from dba_tablespaces ts, (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name) fs where ts.tablespace_name = fs.tablespace_name and df.tablespace_name = fs.tablespace_name(+) ;

TO CHECK SINGLE TABLESPACE AND ALL ITS DATAFILE LIST IN MB

set lines 190 col FILE_NAME for a55 col TABLESPACE_NAME for a20 select TABLESPACE_NAME,FILE_NAME,bytes/1024/1024 “MB” from dba_data_files where TABLESPACE_NAME=upper(‘&tsname’);

TO ADD NEW DATAFILE

alter tablespace STAGE_DATA add datafile ‘+KITTU_VG/INST/datafile/stage15.dbf’ size 4000m;

TO RESIZE A DATAFILE

ALTER DATABASE DATAFILE ‘+KITTU_VG/INST/datafile/stage15.dbf’ RESIZE 4G;

TO CHECK THE TEMP TABLESPACE

set lines 160 set pages 300 col FILE_NAME for a70 col PROPERTY_VALUE for a40 Select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files; select TABLESPACE_NAME,sum(BYTES_USED)/1024/1024 USED_MB, sum(BYTES_FREE)/1024/1024 FREE_MB from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;

To findout default temp tablespace

set lines 160 set pages 300 col PROPERTY_VALUE for a40 select property_name,property_value from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE';

TO ADD TEMP FILE

ALTER TABLESPACE PSTEMP ADD TEMPFILE ‘/u02/oradata/hrcopy/pstemp01.dbf’ SIZE 1000M;

TO RESIZE TEMP FILE

alter database tempfile ‘/u02/oradata/TESTDB/temp01.dbf’ resize 250M;

TOTAL TEMP USAGE

SELECT (SELECT (SUM (tu.blocks)) FROM v$tempseg_usage tu) * (SELECT VALUE FROM v$parameter WHERE NAME = ‘db_block_size’) / 1024 / 1024 AS “Used (MB)”, (SELECT SUM (tf.BYTES) FROM dba_temp_files tf) / 1024 / 1024 AS “Total (MB)”, ROUND ((SELECT (SUM (tu.blocks)) FROM v$tempseg_usage tu) / (SELECT SUM (tf.blocks) FROM dba_temp_files tf) * 100, 2 ) AS pct FROM DUAL;

WHO IS USING TEMP TABLESPACE

set lines 160 set pages 500 col SID_SERIAL for a15; col USERNAME for a15; col osuser for a15; col SPID for a15 col MODULE for a30 col PROGRAM for a20 col TABLESPACE for a10 SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;

WHO IS USING UNDO TABLESPACE

SET PAGESIZE 500 SET LINESIZE 160 COLUMN pgm_notes FORMAT a80 HEADING ‘Notes’ COLUMN rbs FORMAT a12 HEADING ‘Undo Segment’ COLUMN oracle_user FORMAT a12 HEADING ‘Oracle|Username’ COLUMN sid_serial FORMAT a12 HEADING ‘SID,Serial’ COLUMN unix_pid FORMAT a6 HEADING ‘O/S|PID’ COLUMN Client_User FORMAT a20 HEADING ‘Client|Username’ COLUMN Unix_user FORMAT a12 HEADING ‘O/S|Username’ COLUMN login_time FOR/MAT a17 HEADING ‘Login Time’ COLUMN last_txn FORMAT a17 HEADING ‘Last Active’ COLUMN undo_kb FORMAT 99,999,999 HEADING ‘Undo KB’ SELECT r.name rbs, nvl(s.username, ‘None’) oracle_user, s.osuser client_user, p.username unix_user, to_char(s.sid)||’,’||to_char(s.serial#) as sid_serial, p.spid unix_pid, — TO_CHAR(s.logon_time, ‘mm/dd/yy hh24:mi:ss’) as login_time, — TO_CHAR(sysdate – (s.last_call_et) / 86400,’mm/dd/yy hh24:mi:ss’) as last_txn, t.used_ublk * TO_NUMBER(x.value)/1024 as undo_kb FROM v$process p, v$rollname r, v$session s, v$transaction t, v$parameter x WHERE s.taddr = t.addr AND s.paddr = p.addr(+) AND r.usn = t.xidusn(+) AND x.name = ‘db_block_size’ ORDER BY r.name ;

CALCULATE UNDO RETENTION

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”, SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”, ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]” FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = ‘UNDO’ AND c.status = ‘ONLINE’ AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = ‘undo_retention’ AND f.name = ‘db_block_size’ /

SYSAUX USAGE

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from V$sysaux_occupants order by SPACE_USAGE_KBYTES;

COMPONENTS ARE OCCUPYING SPACE IN SYSAUX

select space_usage_kbytes, occupant_name, occupant_desc from v$sysaux_occupants order by 1 desc;

ALL DATAFILES USAGE

set pages 8000 set lines 700 col FILE_NAME for a60 SELECT SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name;

To find out the RETENTION period stats:

Select dbms_stats.get_stats_history_retention from dual;

LOCALLY VS DICTIONARY MANAGED TABLESPACES

select tablespace_name, extent_management from dba_tablespaces;

TO DETERMINE AUTOEXTEND ON/OFF TABLESPACES

select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name from dba_data_files where autoextensible = ‘YES';

TO VERIFY THE LOCATION OF ALL FILES

set lines 160 set lines 100 set pages 999 col name format a70 set lines 100 set pages 999 col name format a70 select name, bytes from (select name, bytes from v$datafile union all select name, bytes from v$tempfile union all select lf.member “name”, l.bytes from v$logfile lf , v$log l where lf.group# = l.group# union all select name, 0 from v$controlfile) used , (select sum(bytes) as p from dba_free_space) free /

THE FOLLOWING QUERY RETURNS A LIST OF OBJECTS THAT ARE CREATED IN THE SYSTEM TABLESPACE BUT NOT OWNED BY SYS OR SYSTEM.(DEPENDENCY)

select owner, segment_name, segment_type from dba_segments where tablespace_name = ‘SYSTEM’ and owner not in (‘SYS’,’SYSTEM’);

To Determine the Tablespace more then 90%

SELECT vm.tablespace_name, (100 – (NVL (vf.BYTES, 0) / vm.BYTES) * 100) pctinuse, NVL (vf.BYTES, 0)/1024/1024 free_mb, vm.BYTES max_bytes, NVL (MAX (f.BYTES), 0) free_ext FROM SYS.sm$ts_avail vm, SYS.sm$ts_free vf, SYS.dba_free_space f WHERE vm.tablespace_name = vf.tablespace_name AND vm.tablespace_name = f.tablespace_name AND (100 – (NVL (vf.BYTES, 0) / vm.BYTES) * 100) > 90 GROUP BY vm.tablespace_name, vf.BYTES, vm.BYTES ORDER BY 1;

To Determine the Tablespace more then 80% full

set pages 999 lines 100 col tablespace_name format a40 col “size MB” format 999999999 col “% Used” format 999 col “80%” format 999999 col “75%” format 999999 select tsu.tablespace_name , ceil(tsu.used_mb) “size MB” , 100 – floor(tsf.free_mb/tsu.used_mb*100) “% Used” , (tsu.used_mb / 100) * (20 – floor(tsf.free_mb/tsu.used_mb*100)) “80%” , (tsu.used_mb / 100) * (25 – floor(tsf.free_mb/tsu.used_mb*100)) “75%” from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name) tsu , (select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) tsf where tsu.tablespace_name = tsf.tablespace_name (+) and 100 – floor(tsf.free_mb/tsu.used_mb*100) >= 80 order by 3,4,5