Ora-01092...................

[nkumarin001]

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

[nkumarin001]

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

[nkumarin001]

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.

Please help me out in finding Pfile or SPfile using above query.

Once again thanks a lot for all of your help.

Regards,
Naveen