Wednesday, May 24, 2006

Getting information about the database

One of the tasks I've recently been given is to collate information about our Oracle and SQLServer databases. Stuff like sizes, init.ora parameters &c. I've developed a script that gets a lot of the stuff we need out of Oracle.

Here it is:
set echo off
set verify off
set feedback off
set linesize 132
set pagesize 60
set heading off
/* This script must be run as SYS (SYSDBA) */


Prompt "Audit Information"
select 'Run on: '||to_char(sysdate, 'HH24:MI DD-MON-YYYY') from dual
/
prompt
prompt

set heading on
column name heading "Database Name"
select name from v$database
/
prompt
column banner heading "Database Version Information"
select banner from v$version
/
prompt
prompt

column log_mode format a16 heading "Archivelog mode"
select log_mode from v$database
/
prompt
prompt


column option_param format a70 heading "Option"
prompt "Options Installed"
select parameter option_param
from v$option
where value='TRUE'
order by parameter
/
prompt
prompt
prompt

prompt "Parameter file parameters"
Prompt "Visible Parameters"
column name format a34 wrap heading "Parameter"
column value format a70 wrap heading "Value"
column isdefault format a9 heading "Default?"
set pagesize 300
select name, value, decode(isdefault, 'TRUE', 'Y', 'FALSE', 'N', '?') isdefault
from v$parameter
where value is not null
order by name
/
prompt
prompt "Hidden Paramteters that are non-default"
select
ksppinm NAME,
ksppstvl VALUE,
ksppdesc description,
ksppstcmnt update_comment
from sys.x$ksppi x,
sys.x$ksppcv y
where (x.indx = y.indx)
and
translate(ksppinm,'_','#') like '#%'
and
ksppstdf='FALSE'
/
prompt
prompt
prompt


Prompt "Tablespace and File Information"
column bigness format 999999.99 heading "Size (Gb)"
column tablespace_name format a20 heading "Tablespace Name"
column nofiles format 99999 heading "No. Files"
column file_name format a60 heading "Filename and Path"
column extent_management format a11 heading "Extent|Management"
prompt "Datafiles"
select a.tablespace_name tablespace_name, count(a.file_name) nofiles, sum(a.bytes)/(1024*1024*1024) bigness, b.extent_management
from dba_data_files a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name, b.extent_management
/
column autoextensible heading "Auto|Extensible" format a10
select tablespace_name, file_name, bytes/(1024*1024*1024) bigness, autoextensible
from dba_data_files
order by tablespace_name, file_id
/
prompt
prompt

Prompt "Tempfiles"
select a.tablespace_name, count(a.file_name) nofiles, sum(a.bytes)/(1024*1024*1024) bigness, b.extent_management
from dba_temp_files a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name, b.extent_management
/
select tablespace_name, file_name, bytes/(1024*1024*1024) bigness, autoextensible
from dba_temp_files
order by tablespace_name, file_id
/
prompt
prompt


Prompt "Online Redo Logs"
column group# format 999 heading "Group No."
column status format a10
column type format a10
column member format a100 heading "Filename and Path"
select group#, status, type, member
from v$logfile
order by group#, member
/
prompt

column redo_size format 99999.99 heading "File Size Mb"
select group#, bytes/(1024*1024) redo_size
from v$log
order by group#
/
prompt
prompt
prompt


prompt "System/Shared Global Area"
prompt "Summary"
column sga_size format 9999.99 heading "Size (Mb)"
select name parameter, value/(1024*1024) sga_size
from v$sga
/
prompt

prompt "Block Caches"
column cache format a30 heading "Cache Name"
select name cache, value/(1024*1024) sga_size
from v$parameter
where name like 'db_%ache_size'
/
prompt

prompt "Pools"
column pool heading "Pool|Name"
select pool, sum(bytes)/(1024*1024) sga_size
from v$sgastat
where pool is not null
group by pool
/
prompt
prompt
prompt


prompt "Users and Schemas"

select count(username) "No. Users" from dba_users
/
prompt "Users and Objects"
column no_objects format 99999999 heading "No.|Objects"
select owner, object_type, count(object_type) no_objects
from dba_objects
group by owner, object_type
/
prompt
prompt

Prompt "End Of Report


Usual disclaimers apply. I have tested this script under 9.2.0 but if you use it and it causes problems they're your problems. Test it yourself first, there's no warrantees or promises that it won't damage your systems.

It's been suggested that we use Remote Diagnostic Agent (RDA) [link require Metalink login]. I've been looking at RDA but it seems a bit clunky for what we need it for. Where I can see us using it, other than to collect information for Service Requests with Oracle Support, is maybe if we ran it for each database when it's created and each time it's patched or has some other major change (perhaps once every 6 months) and store the resulting .zip file sort of document management system indexed by the database name and date. I think that as a means of documenting the database like that it would be useful but a single report of the 'highlights' such as the script is more useful right now, the information from RDA is rather too lengthy and 'overkill'.

Solution to Dreaded ORA-07445 error

Finally got a response from Oracle, turned out they had put it with their US office who don't start work until 13:00 our time.

The eventual response was:
23-MAY-06 14:49:43 GMT

DATA COLLECTED
===============
On alert.log we can see some:
ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to obj
ect] [0x000000000] [] []

Trace file uploaded shows:
/opt/oracle/app/oracle/admin/dchr/udump/dchr_ora_5662.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

*** SESSION ID:(37.1317) 2006-05-18 15:34:04.409
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04]
*** 2006-05-18 15:34:04.410
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to object] [0x000000000
] [] []
Current SQL statement for this session:
select all count(dm_type.r_object_id) "the_count" from dm_type_sp dm_type where (dm_type.name=:"SYS_B_0" and ex
ists (select r_object_id from dm_type_r where dm_type.r_object_id = r_object_id
and attr_name=:"SYS_B_1"))
----- Call Stack Trace -----
ksedmp ssexhd sigacthandler evaopn2 qerixGetKey qerixStart qertbStart qerjoFetch qerstFetch
qergsFetch qerstFetch opifch2 opiall0 kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o
main


******************** Cursor Dump ************************
Current cursor: 1, pgadep: 0
pgactx: 39b95a998 ctxcbk: 0 ctxqbc: 0 ctxrws: 396c5ba70
Explain plan:
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | TQ |
IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 0 | 0 | 2 |
| | | | |
| SORT AGGREGATE | | 1 | 47 | 0 | | | | |
|
| NESTED LOOPS SEMI | | 1 | 47 | 2 | | | | | |
| INDEX UNIQUE SCAN | D_1F01487D80000002 | 1 | 15 | 1 | | | | |
|
| TABLE ACCESS BY INDEX ROWID | DM_TYPE_R | 62 | 1984 | 1 | | | | | |
| INDEX RANGE SCAN | D_1F01487D80000009 | 109 | 0 | 1 | | | |
| |
-------------------------------------------------------------------------------------------------------------------------
Cursor Dump:
----------------------------------------
Cursor 1 (ffffffff7cd60418): CURFETCH curiob: ffffffff7cc465d0
curflg: 46 curpar: 0 curusr: 0 curses 392779298
cursor name: select all count(dm_type.r_object_id) "the_count" from dm_type_sp dm_type where (dm_typ
e.name=:"SYS_B_0" and exists (select r_object_id from dm_type_r where dm_type.r
_object_id = r_object_id and attr_name=:"SYS_B_1"))
child pin: 3992242b8, child lock: 397e020c8, parent lock: 3a21f62f0
xscflg: 80110476, parent handle: 39d351938, xscfl2: 6a00409
Dumping Literal Information
Bind Pos: 0, Bind Len: 12, Bind Val:
Bind Pos: 1, Bind Len: 9, Bind Val:
nxt: 4.0x00000738 nxt: 3.0x00000710 nxt: 2.0x00000220 nxt: 1.0x00000720
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bhp size: 160/600
whp size: 8688/12440

bind 0: dty=1 mxl=32(12) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32 offset=0
bfp=ffffffff7cc469b0 bln=32 avl=12 flg=09
value="dmi_workitem"
bind 1: dty=1 mxl=32(09) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32 offset=0
bfp=ffffffff7cc46960 bln=32 avl=09 flg=09
value="a_wq_name"

.
eos (end of section)
.
ISSUE VERIFICATION
===================
Verified on alert.log and trace file provided

.
eos (end of section)
.
RESEARCH
=========
Found bug
Bug 4098853
Abstract: Dump (qerixGetKey->evaopn2) using COUNT(NOT_NULL_COLUMN)
with a view
Fixed-Releases: 9207 A105 A201
Tags: CBO DUMP R9206 REGRESSION
Details:
This problem is not a true regression but is a problem which
is exposed by the fix for Bug 3440097 in 9.2.0.6.
SQL selecting COUNT(NOT_NULL_COLUMN) from a view may dump
under qerixGetKey->evaopn2 (or qerhjSplit->evaopn2)
Workaround:
Set Event 10122 and ensure the SQL is reparsed with this event set.

Another Workaround:
query_rewrite_enabled=TRUE


.
eos (end of section)
.
CAUSE DETERMINATION
====================
Bug 4098853
.
eos (end of section)
.
CAUSE JUSTIFICATION
====================
Stack trace and behaviour matches this bug

.
eos (end of section)
.
POTENTIAL SOLUTION(S)
======================
We may apply 9.2.0.7 patchset where bug is already fixed, or try workarounds provided
Workaround: Set Event 10122 and ensure the SQL is reparsed with this event set.

Another Workaround:
query_rewrite_enabled=TRUE

.
eos (end of section)
.
POTENTIAL SOLUTION JUSTIFICATION(S)
====================================
As per bug fix

.
eos (end of section)
.
SOLUTION / ACTION PLAN
=======================
Researching about this issue I was able to identify the Bug 4098853. The fix will be included in patchset 92070.
As workaround, you can set the Event 10122 or query_rewrite_enabled=TRUE and ensure the SQL is reparsed with this event set.

1. alter session set query_rewrite_enabled=TRUE or

2. alter session set events '10122 trace name context forever'

3. alter system flush shared_pool;

3. Try to reproduce the issue.
.
eos (end of section)
.

.
KNOWLEDGE CONTENT
==================
Platform / Port Specific? = NO

Knowledge content not created because the following note already addresses this issue: Bug 4098853


By this time we'd already found a work around which was to apply a patch to the application that avoided the error by doing the query a different way. The bug appears (if I'm reading the response correctly) when you try to do a count on a not null column.

We've decided to move up our plans to go to 10g2, which has the bug fix, to get around it.

Friday, May 19, 2006

Dreaded ORA-07445 error

Late yesterday one of our projects passed me information abvout an ORA-07445 error they'd had earlier in the day, apparently this is stopping one of the modules in their application to stop working. Why they couldn't have told me about it earlier I don't know, I would have had time to raise a Service Request with Oracle during our maintenence hours (the why we don't have 24x7 cover is a rant for another time).

I raised a call with Oracle before I went home but there's been no updates on it as of yet, other than a note saying they will update it when they have some information (fair enough I suppose). This long silence is worrying, maybe it's something serious?

Here's the header of the trace file:
*** 2006-05-19 10:57:39.159
*** SESSION ID:(21.4255) 2006-05-19 10:57:39.158
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x1009b0b04, 00000001009B0B04]
*** 2006-05-19 10:57:39.159
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
select all count(dm_type.r_object_id) "the_count" from dm_type_sp dm_type where (dm_type.name=:"SYS_B_0" and exists (select r_object_id from dm_type_r where dm_type.r_object_id = r_object_id and attr_name=:"SYS_B_1"))
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+328 CALL ksedst() 00000000B ? 000000000 ?
000000000 ? 00000004A ?
FFFFFFFF7FFF3C78 ?
102F5E0F8 ?
ssexhd()+676 CALL ksedmp() 000103348 ? 103348000 ?
103348CA8 ? 10334D000 ?
000102800 ? 000000000 ?
sigacthandler()+44 PTR_CALL 0000000000000000 000103350 ?
FFFFFFFF7FFFAD10 ?
103350000 ? 10334DE60 ?
000000000 ? 103350DB8 ?
evaopn2()+196 PTR_CALL 0000000000000000 00000000B ?
FFFFFFFF7FFFAD10 ?
FFFFFFFF7FFFAA30 ?
00010334B ? 000000000 ?
000000000 ?
qerixGetKey()+1420 CALL evaopn2() 3966E1C20 ? 000010000 ?
103350000 ? 000000001 ?
080000002 ?
FFFFFFFF7CB20288 ?
qerixStart()+956 CALL qerixGetKey() 000000000 ? 3966E1DE0 ?
FFFFFFFFFFFFFFFF ?
FFFFFFFF7CB31C98 ?
000103000 ?
FFFFFFFF7CB316C0 ?
qertbStart()+1044 PTR_CALL 0000000000000000 396780958 ? 0001A2C91 ?
000000000 ?
FFFFFFFF7CB325EC ?
FFFFFFFF7CB32528 ?
1033503A8 ?
qerjoFetch()+396 PTR_CALL 0000000000000000 102AE1000 ? 000000001 ?
10334B208 ? 000000018 ?
000103000 ? 3950B5A90 ?
qerstFetch()+212 PTR_CALL 0000000000000000 3966E24A8 ? 1016276A0 ?
FFFFFFFF7FFFB558 ?
000007FFF ? 0000005F8 ?
FFFFFFFF7CB31488 ?
qergsFetch()+2268 PTR_CALL 0000000000000000 3966E2440 ? 000000014 ?
000000005 ? 00000000F ?
0000000F0 ? 000007FFF ?
qerstFetch()+212 PTR_CALL 0000000000000000 000101400 ?
FFFFFFFF7CB31820 ?
000000000 ? 000000028 ?
1016276A0 ? 3966E23A8 ?
opifch2()+1724 PTR_CALL 0000000000000000 3966E2340 ? 00000010C ?
000000043 ? 0000000C9 ?
000000C90 ? 000000028 ?
opiall0()+3860 CALL opifch2() 100FD2000 ? 102AE1398 ?
100FA6640 ? 000000012 ?
FFFFFFFF7FFFBB20 ?
FFFFFFFF7FFFC14C ?
kpoal8()+1040 CALL opiall0() 000000000 ? 00000005E ?
FFFFFFFF7FFFC3D8 ?
103349048 ?
FFFFFFFF7CB206B0 ?
FFFFFFFF7FFFC768 ?
opiodr()+1688 PTR_CALL 0000000000000000 000000000 ? 000000028 ?
FFFFFFFF7FFFEC20 ?
000000024 ? 000000000 ?
0000022B0 ?
ttcpip()+1556 PTR_CALL 0000000000000000 000103000 ? 100F8D200 ?
103351048 ? 103349048 ?
10334B580 ?
FFFFFFFF7FFFCDC0 ?
opitsk()+984 CALL ttcpip() 103351040 ? 000000014 ?
FFFFFFFF7FFFEC20 ?
000000000 ? 000000000 ?
FFFFFFFF7FFFDF0C ?
opiino()+1572 CALL opitsk() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
10334B568 ?
FFFFFFFF7FFFED74 ?
opiodr()+1688 PTR_CALL 0000000000000000 000380007 ? 10334FE98 ?
103411DE8 ?
FFFFFFFF7FFFFAB0 ?
000000000 ? 392ACB4C8 ?
opidrv()+736 CALL opiodr() 000103000 ? 100FDD5C0 ?
103351048 ? 103349048 ?
10334B580 ?
FFFFFFFF7FFFF5D0 ?
sou2o()+16 CALL opidrv() 000000000 ? 000000004 ?
103348A2C ? 00000003C ?
103348F08 ? 000103000 ?
main()+184 CALL sou2o() FFFFFFFF7FFFFAD0 ?
00000003C ? 000000004 ?
FFFFFFFF7FFFFAB0 ?
00002E708 ? 000000000 ?
_start()+380 CALL main() 000000002 ?
FFFFFFFF7FFFFC18 ?
FFFFFFFF7FFFFC30 ?
000000000 ? 000000000 ?
100000000 ?

Hope they get back to me soon!

[Edited to Add: See the solution to ORA-07445: exception encountered: core dump [00000001009B0B04] [SIGSEGV] [Address not mapped to object] [0x000000000] [] [].]