DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: ORA-01092: ORACLE instance terminated. Disconnection forced

  1. #1
    Join Date
    Jun 2007
    Posts
    4

    ORA-01092: ORACLE instance terminated. Disconnection forced

    Hi All,

    I need a expert help in recovering my database.

    Currently I am preparing for my DBA Fundamentals II exam. To test the db recovery process, I corrupted my database by deleting a datafile.

    I have Oracle 9i installed in Windows XP. By default, as archiving is disabled i thought my database is running under NOARCHIVELOG Mode. I took a cold backup by just copying all the files like control, redo logs, data files to another directory. But actually i did changed the database to ARCHIVELOG mode sometime back and I totally forgot about it.

    Then logged in and added some data to some table. Then stopped the database and removed a datafile and tried to restart the database again and got the error "ORA-01157: Cannot identify/lock data file 4 - see DBWR trace file".

    As I had an idea that the db is running under NOARCHIVELOG mode. I just unmounted the db and copied the backup files from the backup directory to the data directory and tried restarting again.

    D:\Profiles\e4007z>sqlplus /nolog

    SQL*Plus: Release 9.0.1.0.1 - Production on Sun Jun 3 12:25:55 2007

    (c) Copyright 2001 Oracle Corporation. All rights reserved.

    SQL> connect sys as sysdba
    Enter password:
    Connected to an idle instance.
    SQL> startup
    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

    I got the above error message.

    I need the expert help to identify the better way to restore my database back.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    whats in the alert log

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    "I took a cold backup by just copying all the files like control, redo logs, data files to another directory. "
    Sorry for asking, but was the database shut down normal/immediate/transactional before copying?

  4. #4
    Join Date
    Jun 2007
    Posts
    4
    Shutdown was normal. I just gave "shutdown" at the sql prompt and then once the shutdown was complete, I did a cold backup of all the data files to a backup folder.

    One big mistake i did was that i forget to check if the db was running in ARCHIVELOG OR NOARCHIVELOG mode. Thought it was running in NOARCHIVELOG and just moved all the backed up files to the data directory and tried to start the database which failed with the ORA-01092 error.

    Tried to create CONTROL FILE back and tried the recovery but it doesn't worked out. Error in alert log is given below:

    CREATE CONTROLFILE REUSE DATABASE "ORACLE9I" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
    LOGFILE
    GROUP 1 'D:\ORACLE\ORADATA\ORACLE9I\REDO01.LOG' SIZE 100M,
    GROUP 2 'D:\ORACLE\ORADATA\ORACLE9I\REDO02.LOG' SIZE 100M,
    GROUP 3 'D:\ORACLE\ORADATA\ORACLE9I\REDO03.LOG' SIZE 100M
    DATAFILE
    'D:\ORACLE\ORADATA\ORACLE9I\SYSTEM01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\UNDOTBS01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\CWMLITE01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\DRSYS01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\EXAMPLE01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\INDX01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\TOOLS01.DBF',
    'D:\ORACLE\ORADATA\ORACLE9I\USERS01.DBF'
    CHARACTER SET WE8MSWIN1252
    Sun Jun 03 14:05:56 2007
    Successful mount of redo thread 1, with mount id 3288464132.
    Sun Jun 03 14:05:56 2007
    Completed: CREATE CONTROLFILE REUSE DATABASE "ORACLE9I" NORES
    Sun Jun 03 14:06:09 2007
    ALTER DATABASE RECOVER database using backup controlfile
    Sun Jun 03 14:06:09 2007
    Media Recovery Start
    WARNING! Recovering data file 1 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 2 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 3 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 4 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 5 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 6 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 7 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    WARNING! Recovering data file 8 from a fuzzy file. If not the current file
    it might be an online backup taken without entering the begin backup command.
    Successfully started datafile 1 media recovery
    Datafile #1: 'D:\ORACLE\ORADATA\ORACLE9I\SYSTEM01.DBF'
    Successfully started datafile 2 media recovery
    Datafile #2: 'D:\ORACLE\ORADATA\ORACLE9I\UNDOTBS01.DBF'
    Successfully started datafile 3 media recovery
    Datafile #3: 'D:\ORACLE\ORADATA\ORACLE9I\CWMLITE01.DBF'
    Successfully started datafile 4 media recovery
    Datafile #4: 'D:\ORACLE\ORADATA\ORACLE9I\DRSYS01.DBF'
    Successfully started datafile 5 media recovery
    Datafile #5: 'D:\ORACLE\ORADATA\ORACLE9I\EXAMPLE01.DBF'
    Successfully started datafile 6 media recovery
    Datafile #6: 'D:\ORACLE\ORADATA\ORACLE9I\INDX01.DBF'
    Successfully started datafile 7 media recovery
    Datafile #7: 'D:\ORACLE\ORADATA\ORACLE9I\TOOLS01.DBF'
    Successfully started datafile 8 media recovery
    Datafile #8: 'D:\ORACLE\ORADATA\ORACLE9I\USERS01.DBF'
    Media Recovery Log
    ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...
    Sun Jun 03 14:06:13 2007
    ALTER DATABASE RECOVER CONTINUE DEFAULT
    Media Recovery Log D:\ORACLE\ADMIN\ORACLE9I\ARCH\ARC00197.001
    ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
    Sun Jun 03 14:06:13 2007
    ALTER DATABASE RECOVER CANCEL
    Media Recovery Cancelled
    Completed: ALTER DATABASE RECOVER CANCEL
    Sun Jun 03 14:06:29 2007
    ALTER DATABASE RECOVER database
    Sun Jun 03 14:06:29 2007
    Media Recovery Start
    Media Recovery failed with error 1610
    ORA-283 signalled during: ALTER DATABASE RECOVER database ...

    ...

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    OK,seems u messed up something with the restore.
    Just shutdown the database
    Return all the data files, control files and redo logs from the cold backup to their original places and startup. As far as u have good cold backup (seems that is the case) u should startup with no probs. If it does not, please post the alert log of the startup command here, and do not try to recreate anything nor execute any scripts

    Regards

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Bore
    Just shutdown the database
    Return all the data files, control files and redo logs from the cold backup to their original places and startup.
    OP didn't mention he/she backup neither control files nor RedoLogs, did you?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    I took a cold backup by just copying all the files like control, redo logs, data files to another directory
    So....

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    oops... my bad. I stand corrected. Sorry.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Jun 2007
    Posts
    4
    Yes I have a good cold backup of all the files. I also have all the archived log files in location.

    Let me know how can i restore the database now.

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    If you have a good cold backup restored you don't need to "recover" the database. Just start it up.

    I think your missing the conceptual difference between a "cold backup" and a "hot backup". Your original post indicated that you took a cold backup of a database that was in archive log mode.

    Just because the DB is in archivelog mode doesnt mean you can't do a cold backup and a restore of that cold backup. No need to re-create the control file using a script. Just restore the files to the original location and start the DB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width