Tuesday, November 15, 2011

DBConsole Login Error Message: Element Not Found

In DBConsole After click the login button in Internet Explorer (IE) 8 the following error is returned :


Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 1.1.4322; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; OfficeLiveConnector.1.5; OfficeLivePatch.1.3; .NET4.0C; .NET4.0E; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; Zune 4.7)
Timestamp: Tue, 15 Nov 2011 07:20:09 UTC
Message: Element not found.

Line: 2815
Char: 1
Code: 0
URI: http://labdb01:1158/em/cabo/jsLibs/Common2_2_24_6.js

FIX :
1. Click Start, Run and type Regedit.exe
2. Backup the registry
3. Navigate to the following location:
HKEY_CLASSES_ROOT\TypeLib\{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}\1.1\0\win32
4. Double-click (default) and set C:\WINDOWS\system32\ieframe.dll as its data
5. Quit Registry Editor.

References : Metalink Note ID : Note.1093475


Friday, November 4, 2011

Spliting a very large table into pieces using rowid nonverlapping ranges

Below you will find a script that spliits a table into pieces in order to effectively process the data.
Just replace [OWNER] with the table owner , [TABLE_NAME] with your table and [CHUNK] with the number of pieces you want.


select grp,
       dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
       dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
  from (
select distinct grp,
       first_value(relative_fno)
        over (partition by grp order by relative_fno, block_id
              rows between unbounded preceding and unbounded following) lo_fno,
       first_value(block_id    )
       over (partition by grp order by relative_fno, block_id
            rows between unbounded preceding and unbounded following) lo_block,
       last_value(relative_fno)
        over (partition by grp order by relative_fno, block_id
        rows between unbounded preceding and unbounded following) hi_fno,
       last_value(block_id+blocks-1)
        over (partition by grp order by relative_fno, block_id
         rows between unbounded preceding and unbounded following) hi_block,
       sum(blocks) over (partition by grp) sum_blocks
  from (
select relative_fno,
       block_id,
       blocks,
       trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
              (sum(blocks) over ()/[CHUNK]) ) grp
  from dba_extents
 where segment_name = upper('[TABLE_NAME]')
   and owner = '[OWNER]'
   order by block_id
       )
       ),
       (select data_object_id from dba_objects where owner='[OWNER]' and object_name = upper('[TABLE_NAME]') )
       order by grp;


References
Tom Kyte's "Efective Oracle by Design"

Wednesday, November 2, 2011

Parallel transaction recovery and SMON Monitoring

A very nice article to read when SMON is the actual blocker on other Database Sessions.

http://www.dba-oracle.com/t_parallel_transaction_recovery_undo_cpu.htm

Now in case the transaction rollback involves index block or when you querying the V$FAST_START_SERVERS you find only one server to be working then you may force disable the parallel rollback.

Follow these steps (If in RAC you must apply to all instances) :
1) Find SMON PID
select pid, program from v$process where program like '%SMON%';
2) on SQLPLUS as sysdba disable SMON transaction rollback/recovery

oradebug setorapid <SMON's Oracle PID>
oradebug event 10513 trace name context forever, level 2

3) Kill the PQ slaves that are doing parallel transaction recovery.

select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
 where pid in (select pid from v$fast_start_servers)) p
 where s.paddr=p.addr;

4)  Disable parallel rollback
alter system set fast_start_parallel_rollback=false;
5) Enable SMON to do serial recovery again

oradebug setorapid <SMON's Oracle PID>
oradebug event 10513 trace name context off
oradebug wakeup 13
6) Use the follwoing query to verify serial recovery and estimate when it will finish

select
usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"  
from v$fast_start_transactions;

REFERENCES :

Metalink Note Ids: 414242.1, 238507.1