Home » RDBMS Server » Server Administration » ORA-01092 ERROR
ORA-01092 ERROR [message #231972] Thu, 19 April 2007 00:04 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi
I getting the follwing error while starting the oracle 10 g database .I am able to startup the instance---->mount ,but not able to open the database.I restarted the system.even though i couldn't open the database.pls urgent...

C:\Documents and Settings\db>set oracle_sid=teng
C:\Documents and Settings\db>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 19 10:28:24 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 788529152 bytes
Fixed Size 790956 bytes
Variable Size 732950100 bytes
Database Buffers 54525952 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

by
balamuralikrishnan.s
Re: ORA-01092 ERROR [message #231979 is a reply to message #231972] Thu, 19 April 2007 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the last lines of the alert.log, from the message "Successful mount" or the like.

Regards
Michel
Re: ORA-01092 ERROR [message #231984 is a reply to message #231972] Thu, 19 April 2007 00:36 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi Mr.Michal
This is last few lines of my alert log file (yesterday we droped OUTLN Schema is this user must exist in database ?)


Errors in file f:\oracle\product\10.1.0\admin\teng\udump\teng_ora_3988.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-18008: cannot find OUTLN schema

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Thu Apr 19 10:57:09 2007
Errors in file f:\oracle\product\10.1.0\admin\teng\bdump\teng_reco_2632.trc:
ORA-00604: error occurred at recursive SQL level

Thu Apr 19 10:57:09 2007
Errors in file f:\oracle\product\10.1.0\admin\teng\bdump\teng_ckpt_3068.trc:
ORA-00604: error occurred at recursive SQL level

Thu Apr 19 10:57:09 2007
Errors in file f:\oracle\product\10.1.0\admin\teng\bdump\teng_lgwr_3048.trc:
ORA-00604: error occurred at recursive SQL level

Thu Apr 19 10:57:09 2007
Errors in file f:\oracle\product\10.1.0\admin\teng\bdump\teng_dbw0_3552.trc:
ORA-00604: error occurred at recursive SQL level

Thu Apr 19 10:57:09 2007
Errors in file f:\oracle\product\10.1.0\admin\teng\bdump\teng_mman_860.trc:
ORA-00604: error occurred at recursive SQL level

Instance terminated by USER, pid = 3988
ORA-1092 signalled during: ALTER DATABASE OPEN...

Thanks in advance
balamuralikrishnan.s

Re: ORA-01092 ERROR [message #231986 is a reply to message #231984] Thu, 19 April 2007 00:40 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

Check the status of your undo tablespace / rollback segments. . most probably you created it with 1 name and using another name in init.ora ...

-Sai Sreenivas Jeedigunta
Re: ORA-01092 ERROR [message #231991 is a reply to message #231972] Thu, 19 April 2007 00:52 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi Mr vjeedigunda
I am using oracle 10.1.0.1.0 last 3 months its running smoothly,I have not changed any undo tablespace name ,but yesterday i droped few users(all acounts are expired&locked)OUTLN,HR like this 12 users i droped ,Will it make any problem to my database ?
Is there any way to repair my database using installation cd.

Thanks in advance
balamuralikrishnan.s
Re: ORA-01092 ERROR [message #231992 is a reply to message #231991] Thu, 19 April 2007 00:54 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

Nope...there is no problem with removing unwanted users.. unless some important users were dropped ..
But this error generally comes due to conflict with undo tablespace..
Re: ORA-01092 ERROR [message #232007 is a reply to message #231984] Thu, 19 April 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you have a "CREATE_STORED_OUTLINES" parameter set to something other than FALSE.
In this case, Oracle tries to access OUTLN schema and can't find it so abort the startup.

Regards
Michel
Re: ORA-01092 ERROR [message #232109 is a reply to message #231991] Thu, 19 April 2007 08:19 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
sbmk_design wrote on Thu, 19 April 2007 01:52
yesterday i droped few users(all acounts are expired&locked)OUTLN,HR like this 12 users i droped ,Will it make any problem to my database ?



You should not have dropped the OUTLN schema. I don't think you can just recreate it either. Look at Metalink note 1071358.6.
Re: ORA-01092 ERROR [message #232188 is a reply to message #232109] Thu, 19 April 2007 14:31 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Hi Try to create OUTLN schema from below schema.

This script will only work if the user OUTLN has been dropped
'cascade' from the database.

This script MUST be run as the user INTERNAL.
This script was extracted from C0800050.sql

Version Testing:
This script was tested on Oracle V8.1.5.

Note:

After running this script, the user will need to run
catalog.sql and catproc.sql. These scripts must be run
as the user SYS or INTERNAL.

Script:
***************** Begin Script ***********************

set serveroutput on

DECLARE
user_exists EXCEPTION;
outln_user number;
outln_tables number;
extra_outln_tables number;
DDL_CURSOR integer;
BEGIN
select count(*) into outln_user from user$ where name='OUTLN';

select count(*) into outln_tables from obj$ where name in
('OL$', 'OL$HINTS') and owner#=
(select user# from user$ where name='OUTLN');

select count(*) into extra_outln_tables from obj$ where name not in
('OL$', 'OL$HINTS') and type#=2 and owner#=
(select user# from user$ where name='OUTLN');


DDL_CURSOR := dbms_sql.open_cursor;
IF outln_user = 0 THEN
dbms_sql.parse(DDL_CURSOR, 'create user outln identified by outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR,
'grant connect, resource, execute any procedure to outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$ ( '||
'ol_name varchar2(30), ' ||
'sql_text long, ' ||
'textlen number, ' ||
'signature raw(16), ' ||
'hash_value number, ' ||
'category varchar2(30), ' ||
'version varchar2(64), ' ||
'creator varchar2(30), ' ||
'timestamp date, ' ||
'flags number, ' ||
'hintcount number)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$hints ( '||
'ol_name varchar2(30), '||
'hint# number, '||
'category varchar2(30), '||
'hint_type number, '||
'hint_text varchar2(512), '||
'stage# number, '||
'node# number, '||
'table_name varchar2(30), '||
'table_tin number, '||
'table_pos number)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$name '||
'on outln.ol$(ol_name)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$signature '||
' on outln.ol$(signature,category)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$hnt_num '||
' on outln.ol$hints(ol_name, hint#)', dbms_sql.native);
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
ELSE
IF outln_tables!=2 or extra_outln_tables!=0 THEN
dbms_output.put_line('ERROR - OUTLN USER ALREADY EXISTS');
RAISE user_exists;
ELSE
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
END IF;
END IF;

EXCEPTION
WHEN user_exists THEN
RAISE;

END;
/

Regards,
Harsh
Previous Topic: uninstall
Next Topic: Listener fails to start
Goto Forum:
  


Current Time: Thu Dec 08 22:23:11 CST 2016

Total time taken to generate the page: 0.07601 seconds