ORA-1092 signalled during: ALTER DATABASE OPEN...

Hi gurus,

I need help urgent...to solve this problemmm...

History is ...I was getting error ORA-00600: internal error code, arguments: [25015], [17], [18], [18], [], [], [], [] which is related to my previous question. On solving the previous problemmm i followed the following steps..

1. alter database backup controlfile to trace.
2. copied the noresetlogs part of trace file to a sql file for recreating the controlfile.
3. There was an unnecesary datafile entry..so I removed it ..

The fin93jrt.sql is as follows...

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "FIN93JRT" NORESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/usr/local/ora9/oradata/fin93jrt/redo01.log'  SIZE 100M,
  GROUP 4 '/usr/local/ora9/oradata/fin93jrt/redo04.log'  SIZE 250M,
  GROUP 5 '/usr/local/ora9/oradata/fin93jrt/redo05.log'  SIZE 250M
-- STANDBY LOGFILE
DATAFILE
  '/usr/local/ora9/oradata/fin93jrt/system01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/undotbs01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/drsys01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/indx01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/tools01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/xdb01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/SCHEMA.dbf',
  '/usr/local/ora9/oradata/fin93jrt/SCHEMA_INDEX.dbf',
  '/usr/local/ora9/oradata/fin93jrt/EPINET_DATA.dbf',
  '/usr/local/ora9/oradata/fin93jrt/ANALYSTS.dbf',
  '/usr/local/ora9/oradata/fin93jrt/EPINET_INDEX.dbf',
  '/usr/local/ora9/oradata/fin93jrt/ANALYSTS_INDEX.dbf',
  '/usr/local/ora9/oradata/fin93jrt/SDA_DATA.dbf',
  '/usr/local/ora9/oradata/fin93jrt/SDA_TEMP.dbf',
  '/disk25/oracle_data/fin93jrt/dpda_log.dbf',
  '/disk23/fin93/finder_project_data/db/SKY_INDEX',
  '/usr/local/oracle_data/fin93jrt/SCHEMA01.dbf',
  '/usr/local/fin93/finder_project_data/db/SKY1', ------ this is the file identified that wrongly linked to a tablespace dpda_logs which does not exist...so I removed it.
  '/usr/local/ora9/oradata/fin93jrt/MERGE.dbf',
  '/usr/local/ora9/oradata/fin93jrt/staging.dbf',
  '/usr/local/ora9/oradata/fin93jrt/SOURCE.dbf',
  '/usr/local/ora9/dbs/ANALYST_INDEX',
  '/usr/local/ora9/dbs/EMP',
  '/usr/local/ora9/oradata/fin93jrt/EPINET_DATA01.dbf',
  '/usr/local/ora9/oradata/fin93jrt/FIN93JRT_MERGE1.dbf',
  '/usr/local/ora9/oradata/fin93jrt/FIN93JRT_STAGE1.dbf',
  '/usr/local/ora9/oradata/fin93jrt/ERBC_STAGE1.dbf',
  '/usr/local/ora9/oradata/fin93jrt/EPINET_DATA02.dbf',
  '/usr/local/ora9/oradata/fin93jrt/EPINET_INDEX01.dbf',
  '/disk25/oracle_data/fin93jrt/dpda_log1.dbf',
  '/disk25/oracle_data/fin93jrt/dpda_log2.dbf',
  '/usr/local/ora9/dbs/TEMP'
CHARACTER SET WE8ISO8859P1
;
-- Take files offline to match current control file.
-- ALTER DATABASE DATAFILE '/usr/local/fin93/finder_project_data/db/SKY1' OFFLINE DROP;
-- Recovery is required if any of the datafiles are restored backups,
--or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/usr/local/ora9/oradata/fin93jrt/temp101.dbf'
     SIZE 209715200  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

4. when executing the fin9ejrt.sql file i get the following error...

                SQL> @fin93jrt.sql-----------the script created from trace file
      ORACLE instance started.

      Total System Global Area  236015756 bytes
      Fixed Size                   454796 bytes
      Variable Size             117440512 bytes
      Database Buffers          117440512 bytes
      Redo Buffers                 679936 bytes

      Control file created.


      Database altered.

      Media recovery complete.
      ALTER DATABASE OPEN
      *
      ERROR at line 1:
      ORA-01092: ORACLE instance terminated. Disconnection forced


      ALTER TABLESPACE TEMP1 ADD TEMPFILE '/usr/local/ora9/oradata/fin93jrt/temp101.dbf'
      *
      ERROR at line 1:
      ORA-03114: not connected to ORACLE


      SQL>


On the alert log it shows....

Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Tablespace 'TEMP1' #26 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Fri May 26 15:32:37 2006
Errors in file /usr/local/ora9/oradata/fin93jrt/udump/fin93jrt_ora_23432.trc:
ORA-00600: internal error code, arguments: [25016], [18], [17], [], [], [], [], []
Fri May 26 15:32:38 2006
Errors in file /usr/local/ora9/oradata/fin93jrt/udump/fin93jrt_ora_23432.trc:
ORA-00600: internal error code, arguments: [25016], [18], [17], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 23432
ORA-1092 signalled during: ALTER DATABASE OPEN...

Kindly help...

Many Many thanks in advance.

Regards,
bbasumatary
bbasumataryAsked:
Who is Participating?
 
actonwangCommented:
>>Errors in file /usr/local/ora9/oradata/fin93jrt/udump/fin93jrt_ora_23432.trc
     any more informaiton. it seems that some datafiles or tablespaces are not in the correct status.
0
 
MohanKNairCommented:
Restart the database. Also make sure that the newly created control file is used while restarting. The new control file must be in sync with data dictionary now.
0
 
bbasumataryAuthor Commented:
Dictionary check beginning
Tablespace 'TEMP1' #26 found in data dictionary,
but not in the controlfile. Adding to controlfile.

the TEMP1 tablespace here is the temporary tablespace. As fas as I know temporary tablespace is not a condition for opening a database. SO why...? the error is  comming..

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
actonwangCommented:
>>'/usr/local/ora9/dbs/TEMP'
      what is this? is it a data file?
0
 
actonwangCommented:
or a link to a temp file? try to remove it and retry.
0
 
bbasumataryAuthor Commented:
its is a tempfile...I can only make it alvailable to the database if the database opens.
0
 
MohanKNairCommented:
Mount the database and then put the tempfile offline and open the database.

SQL> startup mount;
SQL> alter database TEMPFILE '/usr/local/ora9/oradata/fin93jrt/temp101.dbf' OFFLINE;
SQL> alter database open;

The other option is to drop the tempfile

SQL> startup mount;
SQL> alter database TEMPFILE '/usr/local/ora9/oradata/fin93jrt/temp101.dbf' DROP INCLUDING DATAFILES;
SQL> alter database open;
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.