Odd disconnection after 9.2.0.1 upgrade

Printable View

03-14-2003, 07:09 AM
jovery

Odd disconnection after 9.2.0.1 upgrade

Here's a strange one...

I have a database which has recently been migrated from 8.1.7 to 9.2.0.1 (part of a group of 7 databases all on Sun Solaris) which is experiencing the following problem

Code:

$ sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on Fri Mar 14 10:37:28 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected. SQL> SELECT 2 REQUEST_FAILURES, 3 LAST_FAILURE_SIZE, 4 MAX_FREE_SIZE, 5 FREE_SPACE, 6 REQUEST_MISSES 7 FROM 8 V$SHARED_POOL_RESERVED; SELECT * ERROR at line 1: ORA-03113: end-of-file on communication channel

In the trace file which is created the following error is given

Code:

Exception signal: 10 (SIGBUS), code: 1 (Invalid address alignment), addr: 0x40 43, PC: [0x1796a1c, kghrsc()+472] *** 2003-03-14 10:55:13.971 ksedmp: internal or fatal error ORA-07445: exception encountered: core dump [kghrsc()+472] [SIGBUS] [Invalid a ddress alignment] [0x4043] [] []

At first I thought it might be a DD corruption so I have rerun catalog, catproc but still the error occurs. Of the 7 databases I migrated this is the only one experiencing this problem, I've checked Metalink and no luck, has anyone else encounter this? I really don't want to go back to 8.1.7 and I don't want to have to recreate the database again and import the data (nearly 200Gb)

Please feel free to point out my ineptitude if you spot any glaringly obvious mistakes :)

Cheers
03-14-2003, 07:16 AM
ggnanaraj

Hope you checked this out..

http://metalink.oracle.com/metalink/...&p_id=211909.1
03-14-2003, 07:29 AM
jovery

Yep, sure did

It did point to a similar problem on 8.1.7.4 but no solution was ever given.

Thanks anyway

Keep 'em coming....
03-14-2003, 07:56 AM
ggnanaraj

A bug seems to have been raised on this kghrsc function.

http://metalink.oracle.com/metalink/...G&p_id=2713652

Is it you ?

Well, the Status is "Q/A To Development "

Cheers.

P.S.: Maybe you could log an iTAR that it also occurs in 9.0.2.
03-14-2003, 08:03 AM
jovery

That's not mine, but it was the one I refered to in my last post.

I've raised an iTAR to see if Oracle can shed any light it just seems a bit odd that I haven't experienced the same problem in any of the other six databases I migrated on the same box:confused:

Still Oracle can do some strange things sometimes ;)

Regards
03-14-2003, 08:43 AM
adewri

I searched the whole world upside down (i have been searching for last 2 hrs :() but i couldn't find any thing to help you.

and do tell us what the Oracle peeps say, ( i will bet that they will ask you to upgrade to 9.2.0.2.0)
03-14-2003, 09:11 AM
jovery

Cheers Amar,

I don't doubt that their first response will be upgrade :)

After a couple of tests on other boxes I'm sometimes able to reproduce the error when I dynaically reduce the shared pool size so I think the corruption may stem from that somehow.

Still looking...
03-17-2003, 07:07 AM
jovery

Just thought I'd keep anybody who's interested up-to-date wiuth this problem.

Oracle support have suggested that cause of the problem is a heap corrution caused when the SGA was dynamically shrunk. This was backed up by the fact that later in the day the instance completely froze had had to be manually killed (kill the processes). Once the instance restarted the problem disappeared.

extract from the TAR

Quote:

Explained this make sense in a way as we may have lost a memory address during the resizing and therefore the heap manager lost track of its work, thus ORA-7445 was reported.

* This could be bug 2621310, but the call stack does not match completely and I am not seeing child latches blocking each other
* Bug 2713652 looks similar, but we are not failing in strncpy, so again maybe only similar, but not the same
* I cannot say this is one or the other bug, but it is definitely some sort of heap problem

Has anyone else had these sorts of issues when changing the size of the SGA dynamincally?
03-17-2003, 07:11 AM
adewri

Quote:

Originally posted by jovery
Oracle support have suggested that cause of the problem is a heap corrution caused when the SGA was dynamically shrunk.

Useful piece of information. Thanks for the update.

BTW which part of SGA did you shrink, want to see if i can reproduce it at my place.
03-17-2003, 07:17 AM
jovery

Hi Amar,

I reduce the shared pool size by about 20%.
03-17-2003, 07:36 AM
adewri

Hi Jim,

I could reproduce it at my place. A possible candidate for BUG i suppose.

Cheers
Amar

Code:

SQL*Plus: Release 9.2.0.2.0 - Production on Mon Mar 17 16:59:14 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production SQL> show sga Total System Global Area 101784268 bytes Fixed Size 453324 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> set lines 200 SQL> show parameter shared_pool_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ shared_pool_size big integer 50331648 SQL> alter system set shared_pool_size=40331648 scope = both; System altered. SQL> show parameter shared_pool_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ shared_pool_size big integer 41943040 SQL> SELECT 2 REQUEST_FAILURES, 3 LAST_FAILURE_SIZE, 4 MAX_FREE_SIZE, 5 FREE_SPACE, 6 REQUEST_MISSES 7 FROM 8 V$SHARED_POOL_RESERVED; SELECT * ERROR at line 1: ORA-03113: end-of-file on communication channel SQL> SQL> conn sys as sysdba Enter password: Connected. SQL> alter system set shared_pool_size=50331648 scope = both; System altered. SQL> show parameter shared_pool_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ shared_pool_size big integer 50331648 SQL> SELECT 2 REQUEST_FAILURES, 3 LAST_FAILURE_SIZE, 4 MAX_FREE_SIZE, 5 FREE_SPACE, 6 REQUEST_MISSES 7 FROM 8 V$SHARED_POOL_RESERVED;

The last step hangs the system...
03-17-2003, 07:54 AM
jovery

It's interesting that you can reproduce the error because I have attempted the same thing on 2 different test instances and still can't get it to reproduce :confused:

I wondered if it had anything to do with the contents of the shared poll when it is resized.

The details from Oracle seem to imply that they are working on a backport for a previous bug to be released in 9.2.0.3

Regards
03-17-2003, 07:59 AM
adewri

Quote:

Originally posted by jovery
I wondered if it had anything to do with the contents of the shared poll when it is resized.

Well, i tried on a test database, and had just started it, so there was nothing in the shared pool :)

Atleast now if they tell you to upgrade then you can tell them that it happens in 9.2.0.2.0 also

Regards
03-17-2003, 08:04 AM
jovery

Thanks for your help Amar :)
03-17-2003, 08:23 AM
nabeel

I tried to reproduce the problem on Tru64 5.1A with 9.2.0.1.0, I reduced the shared pool about 100 MB and got the followin results

SQL> CONN SYS/ORACLE@ALPHA AS SYSDBA
Connected.
SQL> SHOW SGA

Total System Global Area 370642376 bytes Fixed Size 732616 bytes
Variable Size 251658240 bytes Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
SQL> SHOW PARAMETER SHARED_POOL_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ shared_pool_size big integer 218103808

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 113246208;

System altered.

SQL> SHOW PARAMETER SHARED_POOL_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ shared_pool_size big integer 117440512

SQL> SELECT
2 REQUEST_FAILURES,
3 LAST_FAILURE_SIZE,
4 MAX_FREE_SIZE,
5 FREE_SPACE,
6 REQUEST_MISSES
7 FROM
8 V$SHARED_POOL_RESERVED
9 ;
V$SHARED_POOL_RESERVED
*
ERROR at line 8:
ORA-04030: out of process memory when trying to allocate 88 bytes (cursor work he,x$ksmsp)

(This error took alot of time to come up so i tried again)

SQL> SHOW PARAMETER SHARED_POOL_SIZE
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------shared_pool_size big integer 117440512

SQL> L
1 SELECT
2 REQUEST_FAILURES,
3 LAST_FAILURE_SIZE,
4 MAX_FREE_SIZE,
5 FREE_SPACE,
6 REQUEST_MISSES
7 FROM
8 V$SHARED_POOL_RESERVED
9*
SQL> /
V$SHARED_POOL_RESERVED
*
ERROR at line 8:
ORA-04030: out of process memory when trying to allocate 88 bytes (cursor work he,x$ksmsp)

SQL> spool off

I think i have to try more to get the error u got Jim
03-17-2003, 08:44 AM
adewri

Quote:

Originally posted by nabeel
SQL> CONN SYS/ORACLE@ALPHA AS SYSDBA

Please change your password, you should never give out your passwords(and that too of sys), and never on the web :D. BTW that's a very lousy password for sys.
03-18-2003, 03:01 AM
nabeel

Very Smart Sir,

My dear Sir, that was a fake pwd ;) Do u think that someone would give out there sys pwd so openly on net and thats also on a form?

adewri, grow up and thing mature! :D