[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-27
9
Medium Priority
?
4,967 Views
Last Modified: 2008-03-03
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
0
Comment
Question by:bbasumatary
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16779179
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
 
LVL 19

Accepted Solution

by:
actonwang earned 1000 total points
ID: 16795497
>>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
 

Author Comment

by:bbasumatary
ID: 16796940
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 19

Expert Comment

by:actonwang
ID: 16797651
>>'/usr/local/ora9/dbs/TEMP'
      what is this? is it a data file?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16797659
or a link to a temp file? try to remove it and retry.
0
 

Author Comment

by:bbasumatary
ID: 16798010
its is a tempfile...I can only make it alvailable to the database if the database opens.
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 1000 total points
ID: 16830790
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20246584
Forced accept.

Computer101
EE Admin
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question