OraFAQ Forum: Server Administration » ORA-01092: ORACLE instance terminated. Disconnection forced

Home » RDBMS Server » Server Administration » ORA-01092: ORACLE instance terminated. Disconnection forced (Oracle 10.2.0.3 on windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01092: ORACLE instance terminated. Disconnection forced [message #285873]

Wed, 05 December 2007 23:44

sudhir1582
Messages: 17
Registered: December 2007

Junior Member

Hi,

I'm getting the following error while starting the database. My instance get started and database get mounted but it gives the error while opening the database.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1289484 bytes
Variable Size 104858356 bytes
Database Buffers 54525952 bytes
Redo Buffers 7098368 bytes
SQL> alter database mount;

Database altered.

SQL> alter data open;
alter data open
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

When i check the alert log file following error shown
Errors in file e:\oracle\product\admin\test\udump\test_ora_872.trc:
ORA-00604: error occurred at recursive SQL level

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Thu Dec 06 10:30:37 2007
Errors in file e:\oracle\product\admin\test\bdump\test_pmon_2868.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:37 2007
Errors in file e:\oracle\product\admin\test\bdump\test_reco_2264.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:37 2007
Errors in file e:\oracle\product\admin\test\bdump\test_ckpt_3288.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:38 2007
Errors in file e:\oracle\product\admin\test\bdump\test_lgwr_2628.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:39 2007
Errors in file e:\oracle\product\admin\test\bdump\test_dbw0_2512.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:39 2007
Errors in file e:\oracle\product\admin\test\bdump\test_mman_1252.trc:
ORA-00604: error occurred at recursive SQL level

Thu Dec 06 10:30:39 2007
Errors in file e:\oracle\product\admin\test\bdump\test_psp0_3296.trc:
ORA-00604: error occurred at recursive SQL level

Instance terminated by USER, pid = 872
ORA-1092 signalled during: alter database open...

When i checked the test_lgwr_2628.trc file following error shown. Rest all trc file show only last 2 line from the following error.

Media recovery not enabled or manual archival only 0x10000
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150672 bytes
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

If anybody know the solution of this issue pls let me know.

Thanks
Regards

Sudhir

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285878 is a reply to message #285873]

Wed, 05 December 2007 23:59

varu123
Messages: 754
Registered: October 2007

Senior Member

ORA-600 error series seems to be an oracle internal error.
I once got ora-600 error.Fortunately i had the backup. I had to remove oracle and reinstall it then imported all the data.

Don't do anymore shutdown abort.
Contact Oracle support.

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285883 is a reply to message #285873]

Thu, 06 December 2007 00:06

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

May be someone issued shutdown abort while you invoked alter database open. Re-invoke command and see,

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285884 is a reply to message #285873]

Thu, 06 December 2007 00:07

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Varu, I could not find ORA-600 error in his whole topic.

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285886 is a reply to message #285873]

Thu, 06 December 2007 00:11

varu123
Messages: 754
Registered: October 2007

Senior Member

I did not say ora-600 error.
I said ora-600 error series. I meant errors of the kind
ora-600,601,602..604.....

@OP
try startup force

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285902 is a reply to message #285873]

Thu, 06 December 2007 01:00

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And before these lines? Use code tags to format your listing. Remove useless empty lines and code errors (we don't you tried to execute "alter data" instead of "alter database").

What is your Oracle version?
What is the log_archive_start parameters value (see alert.log)?
What is your archivelog mode?

Don't you have any "on startup" trigger?

Regards
Michel

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286014 is a reply to message #285902]

Thu, 06 December 2007 05:20

sudhir1582
Messages: 17
Registered: December 2007

Junior Member

Hi Michel,

I tried opening the database in Startup force command but that also did not work.I'm getting the same error

But in test_lgwr_1956.trc file i'm getting the following error. What is this error? and why it comes?

Dump file e:\oracle\product\admin\test\bdump\test_lgwr_1956.trc
Thu Dec 06 16:39:14 2007
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:89M/502M, Ph+PgF:307M/1225M, VA:1712M/2047M
Instance name: test

Redo thread mounted by this instance: 1

Oracle process number: 6

Windows thread id: 1956, image: ORACLE.EXE (LGWR)

*** 2007-12-06 16:39:14.468
*** SERVICE NAME:() 2007-12-06 16:39:14.390
*** SESSION ID:(166.1) 2007-12-06 16:39:14.390
Media recovery not enabled or manual archival only 0x10000
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150672 bytes
error 604 detected in background process
ORA-00604: error occurred at recursive SQL level

Oracle version is 10.2.0.3
log_archive_start value is FALSE
Database is in noarchive mode

Thanks
Regards

Sudhir

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286043 is a reply to message #285873]

Thu, 06 December 2007 06:20

varu123
Messages: 754
Registered: October 2007

Senior Member

when oracle itself don't know what error to show then it throws
ora-00604
and with messages like

Quote:

Media recovery not enabled or manual archival only 0x10000

the error cannot be resolved at user end.

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286046 is a reply to message #286014]

Thu, 06 December 2007 06:25

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Can you please post test_ora_
.trc file contents.
>Errors in file e:\oracle\product\admin\test\udump\test_ora_872.trc:

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286077 is a reply to message #286014]

Thu, 06 December 2007 07:17

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the content of alert.log file BEFORE the ORA-00604 error?

Regards
Michel

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286223 is a reply to message #286077]

Thu, 06 December 2007 23:19

sudhir1582
Messages: 17
Registered: December 2007

Junior Member

Hello Michel & Mohammad,

Pls find the required attached file.

Thanks & Regards

Sudhir

Attachment: alert_&_trc_files.zip
(Size: 16.14KB, Downloaded 1542 times)

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286251 is a reply to message #286223]

Fri, 07 December 2007 00:37

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post pmon, smon, lgwr, dbwr, reco, ckpt traces.

Regards
Michel

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286255 is a reply to message #286251]

Fri, 07 December 2007 00:51

sudhir1582
Messages: 17
Registered: December 2007

Junior Member

Hi Michel,

Pls find all trace file.

Also one more thing i would like to tell u that , earlier this DB was running fine with pfile. But after creating spfile when i started the DB with spfile it gave me that error ora-01092

Then after it is not running neither with pfile nor spfile.

Thanks & Regards

Sudhir

Attachment: all_trace_files.zip
(Size: 7.72KB, Downloaded 1564 times)

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286275 is a reply to message #286255]

Fri, 07 December 2007 01:34

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Ah ah! Why didn't you say this before? Your spfile maybe corrupted or Oracle may not be able to write into it.

It simple to fix that.
1/ startup mount
2/ create spfile from pfile
3/ shutdown
4/ Check Oracle has read and write access to it (it should if the previous step worked)
5/ startup

Regards
Michel

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286325 is a reply to message #286275]

Fri, 07 December 2007 03:13

sudhir1582
Messages: 17
Registered: December 2007

Junior Member

Hi Michel,

Even after following your given method it's not comming up.It is showing the same error.

Do i need to recreate the database.

Thanks & Regards

Sudhir

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286333 is a reply to message #285873]

Fri, 07 December 2007 03:20

varu123
Messages: 754
Registered: October 2007

Senior Member

The problem is in the software.
Thats why it is known as oracle internal error.
YOu might have to reinstall oracle and create database.

Report message to a moderator

Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286334 is a reply to message #286325]

Fri, 07 December 2007 03:22

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Then you should have a bad startup trigger.
Try to set your NLS_DATE_FORMAT environment variable to 'DD/MM/YYYY'.

Regards
Michel

Report message to a moderator

Previous Topic:	listener failed
Next Topic:	max datafile size in Unix/Windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 14:14:05 CDT 2024