An example of ORA-07445 troubleshooting

Source: Internet
Author: User

An example of ORA-07445 troubleshooting

The tester reported an error when running SQL statements on a test database (the specific SQL statement is not mentioned here. In short, it is a complicated SQL statement with more than rows .), The error is:

ORA-03113: end-of-file on communication channel

At the beginning, I suspected that SQL is too complicated and the database is overwhelmed. Our testing database machines are not very good, and many of them are virtual machines, in addition, it is the kind of physical machine that runs more than a dozen virtual machines... Therefore, there is no reason not to doubt the possibility of database disconnection.

Check the connection to the server. The database runs well and the listening is normal. No error is reported. Okay, check the alert log. An error is returned:

ORA-07445: exception encountered: core dump [kkqudhus () + 756] [SIGSEGV] [Address not mapped to object] [0x000000068] [] []

There are many causes for 7445 errors, such as bad blocks, memory errors, and Oracle bugs. And there is a high possibility of bugs. Oracle also provides ORA-600/ORA-7445/ORA-700 Error Look-up Tool, this is a good Tool, enter the first Error code, the system will help you find the relevant documentation:

Sure enough, the error is caused by Oracle BUG (BUG: 4664788). Oracle provides two solutions:

1. Set the hidden parameter _ optimizer_cbqt_no_size_restriction to false.

2. Apply patches.

Because the tester said it was a little urgent and only a Test Library, the first method to modify the hidden parameters was used here. View information about the currently hidden parameters:

SQL> select x. ksppinm name,
Y. ksppstvl value,
Y. ksppstdf isdefault,
Decode (bitand (y. ksppstvf, 7 ),
1,
\ 'Modified \',
4,
\ 'System _ MOD \',
\ 'False \ ') ismod,
Decode (bitand (y. ksppstvf, 2), 2, \ 'true \ ', \ 'false \') isadj
From sys. x $ ksppi x, sys. x $ ksppcv y
Where x. inst_id = userenv (\ 'instance \')
And y. inst_id = userenv (\ 'instance \')
And x. indx = y. indx
And x. ksppinm like \ '% _ optimizer_cbqt_no_size_restriction % \'
Order by translate (x. ksppinm ,\'_\',\'\');
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISDEFAULT ISMOD ISADJ
------------------------
_ Optimizer_cbqt_no_size_restriction
TRUE
TRUE FALSE

Modify hidden parameters:

SQL> alter system set \ "_ optimizer_cbqt_no_size_restriction \" = false scope = both;

System altered.

Let the tester re-run the SQL statement, and this time it is executed normally. Now the problem is solved.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.