Home » RDBMS Server » Server Administration » ORA-01092..........
ORA-01092.......... [message #258083] Fri, 10 August 2007 02:47 Go to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi,

In book i read that "If you specify automatic undo management but provide no undo tablespace for oracle to use, oracle will not let your database start. So for example if you set UNDO_MANAGEMENT to AUTO but set UNDO_TABLESPACE to a tablespace that does not exist, then oracle will issue the ORA-01092 error when you attempt to start up nad open the database".

In init.ora i changed undo_tablespace from USERUNDO to NAVTBS which was not available.
UNDO_TABLESPACE=NAVTBS

But when i tried to startup the database, the database got started without any error.and when i checked UNDO_TABLESPACE it was showing USERUNDO instead of NAVTBS.

SQL> sho parameter undo_tablespace;

NAME TYPE VALUE
------------------------------------
undo_tablespace string USERUNDO


So can anyone help me :-
A) In getting ORA-01092 error.
B) Why it is showing USERUNDO instead of NAVTBS.


Regards,
Naveen
Re: ORA-01092.......... [message #258085 is a reply to message #258083] Fri, 10 August 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I could answer you but as you will neither feedback nor share the answer in other forums you post it, I won't.

Regards
Michel
Re: ORA-01092.......... [message #258114 is a reply to message #258085] Fri, 10 August 2007 05:21 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi Michel,

I am sorry i am not able to answer the questions posted by others as it will be really practical questions that they have faced, as i am still learinig DBA i am not able to answer those questions.
Once again i am sorry.

Regards,
Naveen
Re: ORA-01092.......... [message #258161 is a reply to message #258114] Fri, 10 August 2007 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't ask you to answer others' question, just to feedback the answers YOU got to the questions you post in several forums.
You are able to ask in many places, you should be able to post the answers you got in every place you posted.

Regards
Michel
Re: ORA-01092.......... [message #258169 is a reply to message #258161] Fri, 10 August 2007 07:56 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi Michel,

Ok Michel sorry i will do that.

Regards,
Naveen

Re: ORA-01092.......... [message #258226 is a reply to message #258169] Fri, 10 August 2007 10:46 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Did you restart your instance??

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      QLINK3_UNDO_TS


undo_retention=3600
undo_tablespace=DreamzZ


SQL> startup force
ORACLE instance started.

Total System Global Area 2013265920 bytes
Fixed Size                  2129560 bytes
Variable Size             463307112 bytes
Database Buffers         1543503872 bytes
Redo Buffers                4325376 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Re: ORA-01092.......... [message #258255 is a reply to message #258226] Fri, 10 August 2007 12:57 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi DreamzZ

I restarted but it dint show any error.

Regards,
Naveen
Re: ORA-01092.......... [message #258271 is a reply to message #258255] Fri, 10 August 2007 13:59 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi,

I came to know that i was using spfile by following query:-
SQL> select name,value from v$parameter where name='spfile';

NAME VALUE
--------- ----------------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

While i was browsing about spfile i came to know that the wrong thing i was doing is i was changing the value in Spfile manually which was wrong. So i tried to create Pfile from Spfile by following query, but i got ORA-00600 error.


SQL> create pfile='C:\oracle\admin\Chandu\pfile\Newpfile.ora'
2 from
3 spfile='C:\oracle\admin\Chandu\pfile\init.ora';

create pfile='C:\oracle\admin\Chandu\pfile\Newpfile.ora'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [733], [1866671152], [pga heap], [],
[], [], [], []


Please help me in creating Pfile from Spfile.

Regards,
Naveen




Re: ORA-01092.......... [message #258275 is a reply to message #258083] Fri, 10 August 2007 14:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i was changing the value in Spfile manually which was wrong
I strongly suspect you corrupted the SPFILE, which is why you are now getting the ORA-00600 failure.
Re: ORA-01092.......... [message #258285 is a reply to message #258275] Fri, 10 August 2007 16:11 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
3 spfile='C:\oracle\admin\Chandu\pfile\init.ora';



What are you specifying for creating PFILE??


init.ora?
Re: ORA-01092.......... [message #258288 is a reply to message #258083] Fri, 10 August 2007 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL> CREATE PFILE FROM SPFILE;
above is what I always do.
Re: ORA-01092.......... [message #258289 is a reply to message #258271] Fri, 10 August 2007 16:41 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
naveenkumar001 wrote on Fri, 10 August 2007 12:59
Hi,

I came to know that i was using spfile by following query:-
SQL> select name,value from v$parameter where name='spfile';

NAME VALUE
--------- ----------------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

While i was browsing about spfile i came to know that the wrong thing i was doing is i was changing the value in Spfile manually which was wrong. So i tried to create Pfile from Spfile by following query, but i got ORA-00600 error.


SQL> create pfile='C:\oracle\admin\Chandu\pfile\Newpfile.ora'
2 from
3 spfile='C:\oracle\admin\Chandu\pfile\init.ora';



I'm talking about this.


SQL> create pfile ='/export/home/oracle/pfile.ora'  FROM SPFILE='/opt/oracle/10.2.0/dbs/init.ora';
create pfile ='/export/home/oracle/pfile.ora'  FROM SPFILE='/opt/oracle/10.2.0/dbs/init.ora'
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1


SQL> create pfile ='/export/home/oracle/pfile.ora'  FROM SPFILE='/opt/oracle/10.2.0/dbs/spfileDS.ora';

File created.

SQL>

[Updated on: Fri, 10 August 2007 16:48]

Report message to a moderator

Re: ORA-01092.......... [message #258301 is a reply to message #258289] Fri, 10 August 2007 21:21 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi,

Thank you for everyone to solve the problem. I got ORA-01092 error.
I created Pfile using following query:-

SQL> create pfile='C:\oracle\admin\Chandu\NewPfile.ora' from spfile;

File created.

After that i shutdown and tried to restart the instance but it gave ORA-01092 error:-

SQL> startup pfile='C:\oracle\admin\Chandu\pfile\NewPfile.ora';
ORACLE instance started.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

But i have one doubt I created the database using "Database Configuration Assistant".
When i started the database i came to know i was using "Spfile" using following query.
SQL> select name,value from v$parameter where name='spfile';

NAME VALUE
------ -----------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

But spfile is a binary file and cant be changed manually that means i am using pfile so i was able to change manually.
But the value here shows as
"%ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA" then whats the meaning of this value whether it is a pfile or spfile.

Some where i read that if we use the above query and if it gives value means it is Spfile otherwise it is Pfile.

I am loading the file also please check it out and help me in finding Pfile or SPfile using above query.

Once again thanks a lot for all of your help.

Regards,
Naveen
  • Attachment: init.ora
    (Size: 2.52KB, Downloaded 268 times)

[Updated on: Fri, 10 August 2007 21:24]

Report message to a moderator

Re: ORA-01092.......... [message #258302 is a reply to message #258083] Fri, 10 August 2007 21:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
01092, 00000, "ORACLE instance terminated. Disconnection forced"
// *Cause:  The instance this process was connected to was terminated
//          abnormally, probably via a shutdown abort. This process
//          was forced to disconnect from the instance.
// *Action: Examine the alert log for more details. When the instance has been 
//          restarted, retry action.


What is the SID of your database?

[Updated on: Fri, 10 August 2007 21:40] by Moderator

Report message to a moderator

Re: ORA-01092.......... [message #258303 is a reply to message #258302] Fri, 10 August 2007 23:31 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
POST LAST 100 lines from alertlog

show parameter background_dump_dest
Re: ORA-01092.......... [message #258318 is a reply to message #258303] Sat, 11 August 2007 00:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,


@Ana,
SQL> CREATE PFILE FROM SPFILE;
above is what I always do.

and don't forget to recreate SPFILE from PFILE Smile


@Naveen
But spfile is a binary file and cant be changed manually that means i am using pfile so i was able to change manually.

Yes, SPFILE file is binary file but it is similar like pfile.
you should know which one is spfile or which one is pfile.

spfile === spfile<sid>.ora
pfile ====init<sid>.ora

NEVER EDIT DIRECTLY SPFILE<SID>.ORA if you DO MEANS YOU CORRUPT YOUR SPFILE FILES.

As Ana said "first create pfile from spfile" , edit parameter , again Create SPFILE from PFILE".




Regards
Taj


[Updated on: Sat, 11 August 2007 00:31]

Report message to a moderator

Re: ORA-01092.......... [message #258342 is a reply to message #258303] Sat, 11 August 2007 03:34 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi DreamzZ,

SID=Chandu
This is the last 100 lines of my Background_dump_dest:-

Sat Aug 11 13:57:03 2007
Successful mount of redo thread 1, with mount id 2164580202.
Sat Aug 11 13:57:03 2007
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Sat Aug 11 13:57:03 2007
alter database open
Sat Aug 11 13:57:04 2007
Errors in file C:\oracle\admin\Chandu\bdump\chanduDBW0.TRC:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: 'C:\ORACLE\ORADATA\CHANDU\TEMNAV.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Sat Aug 11 13:57:05 2007
Beginning crash recovery of 1 threads
Sat Aug 11 13:57:05 2007
Started recovery at
Thread 1: logseq 217, block 117, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 217 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\CHANDU\REDO03.LOG
Sat Aug 11 13:57:07 2007
Ended recovery at
Thread 1: logseq 217, block 4294967295, scn 0.4191308
14 data blocks read, 13 data blocks written, 12 redo blocks read
Crash recovery completed successfully
Sat Aug 11 13:57:09 2007
Errors in file C:\oracle\admin\Chandu\bdump\chanduDBW0.TRC:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: 'C:\ORACLE\ORADATA\CHANDU\TEMNAV.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Sat Aug 11 13:57:09 2007
Errors in file C:\oracle\admin\Chandu\bdump\chanduDBW0.TRC:
ORA-01186: file 202 failed verification tests
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: 'C:\ORACLE\ORADATA\CHANDU\TEMNAV.DBF'

Sat Aug 11 13:57:09 2007
File 202 not verified due to error ORA-01157
Sat Aug 11 13:57:10 2007
Thread 1 advanced to log sequence 218
Thread 1 opened at log sequence 218
Current log# 1 seq# 218 mem# 0: C:\ORACLE\ORADATA\CHANDU\REDO01.LOG
Successful open of redo thread 1.
Sat Aug 11 13:57:12 2007
SMON: enabling cache recovery
Sat Aug 11 13:57:17 2007
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Undo Segment 13 Onlined
Undo Segment 14 Onlined
Undo Segment 15 Onlined
Undo Segment 16 Onlined
Undo Segment 17 Onlined
Undo Segment 18 Onlined
Undo Segment 19 Onlined
Undo Segment 20 Onlined
Successfully onlined Undo Tablespace 26.
Sat Aug 11 13:57:17 2007
SMON: enabling tx recovery
Sat Aug 11 13:57:25 2007
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open


Regards,
Naveen
Re: ORA-01092.......... [message #258358 is a reply to message #258083] Sat, 11 August 2007 05:26 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hey gurus,

but what is the need to do the lengthy procedure..
create pfile from spfile -> change parameter -> create spfile from dat changed pfile..

We can change the parameters of spfile dynamically using "alter system" command.. cant we use this to change the parameter for Undo talblespace instade of above steps??


regards..
Dipali
Re: ORA-01092.......... [message #258370 is a reply to message #258358] Sat, 11 August 2007 06:10 Go to previous messageGo to next message
naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore
Member

Hi Dipali,
Your pocedure is working nice.
I wanted to get ORA-01092 error, so for UNDO_TABLESPACE i was giving the tablespace name which was not present but the wrong thing i was doing was i dint give any value to SCOPE which was by default BOTH so oracle use to check the tablespace which was not present and it was giving tablespace doesnot exist:-

SQL> alter system set undo_tablespace='UNDOTBS' scope=both;
alter system set undo_tablespace='UNDOTBS' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type

After that i came to know about scope and changed the SCOPE=SPFILE.
SQL> alter system set undo_tablespace='UNDOTBS' scope=spfile;

System altered.

After that i shutdown the database and restarted the database and i got ORA-01092 error.

SQL> startup;
ORACLE instance started.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

Thanks a lot. Its working nice.

Regards,
Naveen
Re: ORA-01092.......... [message #258384 is a reply to message #258358] Sat, 11 August 2007 07:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,


but what is the need to do the lengthy procedure..
Yes, it is lenghty procedure but @OP doing something which is not correct that is why we have to mention right thing when database initially shutdown and want to edit any parameter...that time we need to follow this lenghty procedure.

Re: ORA-01092.......... [message #258390 is a reply to message #258384] Sat, 11 August 2007 08:10 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Actually taj, my intetion for asking about need of such lengthy procedure is to know
whether is there any conditions where we have to follow that 3 step procedure instade of directly changing parameter of spfile using alter system command..??

regards..
Re: ORA-01092.......... [message #258391 is a reply to message #258390] Sat, 11 August 2007 08:12 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Smile
nice naveen..
congretss to all
at last naveen reached to the stage where he wanted to be

Smile

[Updated on: Sat, 11 August 2007 08:19]

Report message to a moderator

Re: ORA-01092.......... [message #258392 is a reply to message #258390] Sat, 11 August 2007 08:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

when database initially shutdown and want to edit any parameter...that time we need to follow this lengthy procedure.



Regards
Taj
Re: ORA-01092.......... [message #258394 is a reply to message #258392] Sat, 11 August 2007 08:17 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Smile
thanks

[Updated on: Sat, 11 August 2007 08:19]

Report message to a moderator

Previous Topic: select order
Next Topic: An Unhandled Exception Occurred
Goto Forum:
  


Current Time: Thu Dec 08 14:15:47 CST 2016

Total time taken to generate the page: 0.14447 seconds