-
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.
-
-
"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?
-
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 ...
...
-
Originally Posted by nnshahul
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.
...
I doubt the backup was taken when the files were in OPEN state.
-
Originally Posted by Kamkan
I doubt the backup was taken when the files were in OPEN state.
Thats becuase the OP did a create control file command prior and then performed media recovery! There is absolutely no need to create a control file.
-
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
-
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.
-
I took a cold backup by just copying all the files like control, redo logs, data files to another directory
So....
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|