Home » RDBMS Server » Server Administration » ORA-01092: ORACLE instance terminated. Disconnection forced (Oracle 10.2.0.3 on windows XP)
ORA-01092: ORACLE instance terminated. Disconnection forced [message #285873] Wed, 05 December 2007 23:44 Go to next message
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285878 is a reply to message #285873] Wed, 05 December 2007 23:59 Go to previous messageGo to next message
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.
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285883 is a reply to message #285873] Thu, 06 December 2007 00:06 Go to previous messageGo to next message
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,
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285884 is a reply to message #285873] Thu, 06 December 2007 00:07 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Varu, I could not find ORA-600 error in his whole topic.
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285886 is a reply to message #285873] Thu, 06 December 2007 00:11 Go to previous messageGo to next message
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #285902 is a reply to message #285873] Thu, 06 December 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286014 is a reply to message #285902] Thu, 06 December 2007 05:20 Go to previous messageGo to next message
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



Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286043 is a reply to message #285873] Thu, 06 December 2007 06:20 Go to previous messageGo to next message
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.
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286046 is a reply to message #286014] Thu, 06 December 2007 06:25 Go to previous messageGo to next message
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:
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286077 is a reply to message #286014] Thu, 06 December 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286223 is a reply to message #286077] Thu, 06 December 2007 23:19 Go to previous messageGo to next message
sudhir1582
Messages: 17
Registered: December 2007
Junior Member
Hello Michel & Mohammad,

Pls find the required attached file.

Thanks & Regards

Sudhir
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286251 is a reply to message #286223] Fri, 07 December 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post pmon, smon, lgwr, dbwr, reco, ckpt traces.

Regards
Michel
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286255 is a reply to message #286251] Fri, 07 December 2007 00:51 Go to previous messageGo to next message
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286275 is a reply to message #286255] Fri, 07 December 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286325 is a reply to message #286275] Fri, 07 December 2007 03:13 Go to previous messageGo to next message
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
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286333 is a reply to message #285873] Fri, 07 December 2007 03:20 Go to previous messageGo to next message
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.
Re: ORA-01092: ORACLE instance terminated. Disconnection forced [message #286334 is a reply to message #286325] Fri, 07 December 2007 03:22 Go to previous message
Michel Cadot
Messages: 64133
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
Previous Topic: listener failed
Next Topic: max datafile size in Unix/Windows
Goto Forum:
  


Current Time: Thu Dec 08 04:26:18 CST 2016

Total time taken to generate the page: 0.09516 seconds