Link to home
Start Free TrialLog in
Avatar of bbasumatary
bbasumatary

asked on

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
Avatar of MohanKNair
MohanKNair

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.
ASKER CERTIFIED SOLUTION
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bbasumatary

ASKER

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..

>>'/usr/local/ora9/dbs/TEMP'
      what is this? is it a data file?
or a link to a temp file? try to remove it and retry.
its is a tempfile...I can only make it alvailable to the database if the database opens.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin