TIP #55 : iasconsole and Report server in 9.0.4 Oracle application server

If you try to use ias-console to restart report server in 9.0.4 Oracle application server, you will get the following error :

An error occurred while restarting "Reports Server: rep_name??".
An error occurred while talking to OPMN. Could not find entity for "rep_name???".

name ?? is name of report server.

Do not be worry. There is nothing wrong with report server.As Oracle confirmed in metalink note 299508.1, ias-console can not be used in 9.0.4 to bounce report server. (However start/stop button for report server in iasconsole is active !!!)Starting report server has to happen through opmn only.

TIP 54# : "Cannot open or remove a file" warning when applying patch

I faced with strange warning yesterday when I tried to upgrade Oracle application server from 10.1.2.0.2 to 10.1.2.2. Oracle Universal Installer, all of sudden stopped and showed me the following error :

Error in writing to file $ORACLE_HOME/lib32/libnjssl10.so
Cannot open or remove a file containing a running program


Patch was running on AIX platform and as Oracle recommendation before starting patchset, I ran /usr/sbin/slibclean as root which is supposed to clean box from program which got file descriptor.
This experience showed me that slibclean is not always perfect and there may be some odd situations which other remedies are required.
Here is what I did to get over the situation.
- cp $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
- cp $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Copy did not fix the situation

- mv $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
-mv $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Move fixed the situation

As the result :
- On AIX , first try /usr/sbin/slibclean as root.
- On all platforms, if file exists try to move it to new file and then move it back to original.

TIP 53# : Exclude option in data pump on Window.

As all know, filtering option in datapump (10g version of traditional export/import) is very powerful.
I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.
Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.

However, I got the following errors when I ran it.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE_NAME_EXPR filter is badly formed.
ORA-00920: invalid relational operator



I tried option with the following syntax :

exclude=TABLE:"= 'TABLE_NAME'"
exclude=TABLE:TABLE_NAME
exclude=TABLE:" IN ('TABLE_NAME') "

No luck. Got the same error.
Finally, I figured out that window can not parse " and need to add escape seuqence.
So exclude option on windows should have syntax like this :


exclude=TABLE:\"='TABLE_NAME'\"

TIP 52# : Tablespace map

Sometime for solving tablespace fragmentation issue, it is better off to have a better picture of tablespace allocated and free area.
In this post, I wrote a PL/SQL script which shows mapping of free space and used space in tablespace.This script helped me out to resolve tablespace fragmentation issue for a client.Analyzing the result of script would end up to detecting offending objects and relocating them.

Script details
------------------

CREATE OR REPLACE PROCEDURE mapts (target_ts VARCHAR2,min_extents NUMBER DEFAULT 8) IS
 cur_block_id NUMBER;
 prev_block_id NUMBER;
 cur_blocks NUMBER;
 prev_blocks NUMBER;
 map_str CLOB;
 alloc_str CLOB;
 tmp NUMBER;
 file_id_v NUMBER;
 CURSOR mycur IS select segment_name,blocks,block_id,EXTENT_ID from dba_extents where TABLESPACE_NAME=Upper(target_ts) AND file_id=file_id_v order by block_id,EXTENT_ID;
 myvar mycur%ROWTYPE;
BEGIN
 FOR rec IN (SELECT file_id FROM dba_data_files WHERE tablespace_name=Upper(target_ts)) LOOP
  file_id_v:=rec.file_id;
  cur_block_id:=1;
  prev_block_id:=1;
  prev_blocks:=0;
  OPEN mycur;
  LOOP
   FETCH mycur INTO myvar;
   EXIT WHEN mycur%NOTFOUND;
   cur_block_id:=myvar.block_id;
   cur_blocks:=myvar.blocks;
   IF(prev_block_id+ prev_blocks= cur_block_id) THEN
    FOR i IN 1..round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/min_extents);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   ELSE
    FOR i IN 1..Round((cur_block_id-prev_block_id-prev_blocks)/min_extents) LOOP
     map_str:=map_str||'-'|| '*';
     alloc_str:=alloc_str||'-'|| '*';
    END LOOP;
    FOR i IN 1..Round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/8);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   END IF;
   --Dbms_Output.put_line(map_str);
  END LOOP;
  INSERT INTO object_place_in_ts values(target_ts,file_id_v,alloc_str,map_str,sysdate);
  COMMIT;
  alloc_str:=NULL;
  map_str:=NULL;
 END LOOP;
 CLOSE mycur;
END;
/

Script needs the following table to be existed in database.

Table : object_place_in_ts
Table description:

Name Null? Type
----------------------------------------
TABLESPACE_NAME VARCHAR2(30)
ALLOC_PATTERN CLOB
OBJ_PATTERN CLOB
CRE_TIME DATE

Alloc_pattern : String pattern which allocated extents are shown with + and free extents are shown with *
Obj_patten : String pattern which allocated extents have name of object and free extents are shown with *.

Example :

tablespace_name allocated_pattern object_pattern
------------------------------------------------------------------------------
users ++*+++ -obj1(2)-*-objx(1)-objy(1)-objx(1)

In this example, two first extents have been allocated, third extent is free and fourth and fifth and sixth extents have been allocated . (Check allocated_pattern).
First two extents have been allocated by obj1, fourth and sixth extents have been allocated by objx.fifth extent has been allocated by objy.

I found this result and analysis very handy to resolve tablespace fragmentation.