| ORA-01092.......... [message #258083] |
Fri, 10 August 2007 02:47  |
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 #258226 is a reply to message #258169] |
Fri, 10 August 2007 10:46  |
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 #258271 is a reply to message #258255] |
Fri, 10 August 2007 13:59 |
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 #258289 is a reply to message #258271] |
Fri, 10 August 2007 16:41 |
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 |
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 1391 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 |
 |
BlackSwan
Messages: 26766
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 #258318 is a reply to message #258303] |
Sat, 11 August 2007 00:30 |
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 
@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 |
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 |
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 #258390 is a reply to message #258384] |
Sat, 11 August 2007 08:10 |
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..
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
