13 Replies Latest reply on Jan 23, 2009 7:24 AM by 577696

    Unable to mount the database

    577696
      Hi everybody,

      I am using oracle 9i rel 2 in Windows 2000 server.
      I have two databases db1 & db2 with different db_name and instance_name different. Due to some power outage now only db2 is functional. If I try to restore the db1 by shutting down the database.

      shutdown immediate;
      startup nomount pfile='d:\oracle\ora92\database\initdb1.ora';
      alter database mount;
      *
      ERROR at line 1:
      ORA-01102: cannot mount database in EXCLUSIVE mode

      Can any one please help me out in restoring the database.

      Awaiting your valued suggestions.

      Regards

      Vijay Kumar
        • 1. Re: Unable to mount the database
          Surachart Opun
          please check and post message from alertlog...
          • 2. Re: Unable to mount the database
            577696
            Thanks for the reply. I find the nothing error shown.
            • 3. Re: Unable to mount the database
              Lukasz Mastalerz
              You have two databases or two instances? Look like another instance is currently accessing your database. Check in services or in processes if any instance is running, if you can do it - stop it and then try running your command one more time
              • 4. Re: Unable to mount the database
                420940
                Your instance is running. Please shut it down properly and try. May be you are connected to the other database. check for ORACLE_SID and look for alert log .... for latest info and errors
                • 5. Re: Unable to mount the database
                  150505
                  Hi, I am not very sure how to ORACLE_SID on windows but looks that is your problem. You are not able to set correctly the sid you want and becauase of same you are unkowingly starting the DB which is already started

                  regards
                  Pravin
                  • 6. Re: Unable to mount the database
                    577696
                    Thanks for all your valuable suggestions. Actually their two databases and two instances. I am unable to mount the database after I shutdown the database and tried following

                    startup nomount pfile='initfile of db1'
                    alter database mount;
                    Gives error.

                    So I took all the necessary files and placed in a separate machine installed oracle 9i. Changed the parameter settings in the initfile to point the locations of control file,datafile and redo log file.

                    Now I am able to mount the database but unable to open the database.
                    alter database backup controlfile to trace;
                    then,
                    shutdown immediate;
                    startup nomount;
                    CREATE CONTROLFILE REUSE DATABASE "MIDC" RESETLOGS NOARCHIVELOG
                    -- SET STANDBY TO MAXIMIZE PERFORMANCE
                    MAXLOGFILES 50
                    MAXLOGMEMBERS 5
                    MAXDATAFILES 100
                    MAXINSTANCES 1
                    MAXLOGHISTORY 453
                    LOGFILE
                    GROUP 1 'E:\ORACLE\ORADATA\MIDC\REDO01.LOG' SIZE 100M,
                    GROUP 2 'E:\ORACLE\ORADATA\MIDC\REDO02.LOG' SIZE 100M,
                    GROUP 3 'E:\ORACLE\ORADATA\MIDC\REDO03.LOG' SIZE 100M
                    -- STANDBY LOGFILE
                    DATAFILE
                    'E:\ORACLE\ORADATA\MIDC\SYSTEM01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\UNDOTBS01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\CWMLITE01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\DRSYS01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\EXAMPLE01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\INDX01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\ODM01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\TOOLS01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\USERS01.DBF',
                    'E:\ORACLE\ORADATA\MIDC\XDB01.DBF'
                    CHARACTER SET WE8MSWIN1252
                    ;
                    ERROR at line 1:
                    ORA-01503: CREATE CONTROLFILE failed
                    ORA-01200: actual file size of 12160 is smaller than correct size of 12320
                    blocks
                    ORA-01110: data file 9: 'E:\ORACLE\ORADATA\MIDC\USERS01.DBF'

                    Can any one please help me to resolve the issue. I am unable to open the database.

                    Awaiting all your valued suggestions.

                    Regards
                    Vijay Kumar
                    • 7. Re: Unable to mount the database
                      577696
                      I would like post the alertlog file contents for all your suggestions

                      Sun Jan 18 14:21:16 2009
                      ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "MIDC" RESETLOGS...
                      Sun Jan 18 14:21:46 2009
                      alter database mount
                      Sun Jan 18 14:21:50 2009
                      Successful mount of redo thread 1, with mount id 2370033082.
                      Sun Jan 18 14:21:50 2009
                      Database mounted in Exclusive Mode.
                      Completed: alter database mount
                      Sun Jan 18 14:22:54 2009
                      alter database datafile 'E:\ORACLE\ORADATA\MIDC\USERS01.DBF' offline
                      Sun Jan 18 14:22:54 2009
                      ORA-1516 signalled during: alter database datafile 'E:\ORACLE\ORADATA\MIDC\US...
                      Sun Jan 18 14:28:30 2009
                      ALTER DATABASE RECOVER database
                      Sun Jan 18 14:28:30 2009
                      Media Recovery Start
                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
                      ORA-01110: data file 1: 'D:\ORACLE\ORADATA\MIDC\SYSTEM01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
                      ORA-01110: data file 2: 'D:\ORACLE\ORADATA\MIDC\UNDOTBS01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
                      ORA-01110: data file 3: 'D:\ORACLE\ORADATA\MIDC\CWMLITE01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
                      ORA-01110: data file 4: 'D:\ORACLE\ORADATA\MIDC\DRSYS01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
                      ORA-01110: data file 5: 'D:\ORACLE\ORADATA\MIDC\EXAMPLE01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
                      ORA-01110: data file 6: 'D:\ORACLE\ORADATA\MIDC\INDX01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
                      ORA-01110: data file 7: 'D:\ORACLE\ORADATA\MIDC\ODM01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
                      ORA-01110: data file 8: 'D:\ORACLE\ORADATA\MIDC\TOOLS01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
                      ORA-01110: data file 9: 'D:\ORACLE\ORADATA\MIDC\USERS01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Sun Jan 18 14:28:30 2009
                      Errors in file e:\oracle\admin\midc\bdump\midc_dbw0_2716.trc:
                      ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
                      ORA-01110: data file 10: 'D:\ORACLE\ORADATA\MIDC\XDB01.DBF'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 3) The system cannot find the path specified.

                      Media Recovery failed with error 1610
                      ORA-283 signalled during: ALTER DATABASE RECOVER database ...

                      Can please help me out in restoring the database.

                      Regards

                      Vijay Kumar
                      • 8. Re: Unable to mount the database
                        420940
                        it seems the USERS01.dbf file is not the correct one that database already know. May be it is not copied properly and it is still under copy. Make sure you have exact copy of the datafile with same size as it was there on the previous server.
                        • 9. Re: Unable to mount the database
                          577696
                          I am for sure the only files available with me were copied after a proper shutdown. Suggest ways and means to recover the data from the available resources. Awaiting all your valuable suggestions.

                          Regards

                          Vijay Kumar
                          • 10. Re: Unable to mount the database
                            420940
                            Please paste the list of steps you did to move this database. If possible screenshots ... like create controlfile .....
                            Becasue, from the alert log I can see that... all Error messages are coming for datafiles on D: drive. while in your create controlfile script, all datafiles are in E: drive. Why this mismatch is there?

                            Thanks to send more details and list of steps done
                            • 11. Re: Unable to mount the database
                              577696
                              Thanks for the reply. Previously I was trying to restore the files I have in a separate machine in oracle was installed in E:\ drive and the files I copied from originally was in D:\. I changed the parameters in init file. Now currently I restored all the files in another machine with oracle installed in D:\ as well. Now I am able to mount the database but unable to recover or open the database.
                              ERROR in ALERTLOG file.

                              alter database mount exclusive
                              Wed Jan 21 10:04:47 2009
                              Successful mount of redo thread 1, with mount id 2370220538.
                              Wed Jan 21 10:04:47 2009
                              Database mounted in Exclusive Mode.
                              Completed: alter database mount exclusive
                              Wed Jan 21 10:04:47 2009
                              alter database open
                              Wed Jan 21 10:04:47 2009
                              Errors in file d:\oracle\admin\midc\bdump\midc_dbw0_416.trc:
                              ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
                              ORA-01110: data file 1: 'D:\ORACLE\ORADATA\MIDC\SYSTEM01.DBF'
                              ORA-27041: unable to open file
                              OSD-04002: unable to open file
                              O/S-Error: (OS 2) The system cannot find the file specified.

                              Wed Jan 21 10:04:47 2009
                              Errors in file d:\oracle\admin\midc\bdump\midc_dbw0_416.trc:
                              ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
                              ORA-01110: data file 9: 'D:\ORACLE\ORADATA\MIDC\USERS01.DBF'
                              ORA-27046: file size is not a multiple of logical block size
                              OSD-04012: file size mismatch (OS 100941825)

                              ORA-1157 signalled during: alter database open...
                              Wed Jan 21 10:07:04 2009
                              alter database backup controlfile to trace
                              Completed: alter database backup controlfile to trace
                              Wed Jan 21 10:07:48 2009
                              alter database recover until cancel using backup controlfile
                              Wed Jan 21 10:07:48 2009
                              Media Recovery Start
                              Wed Jan 21 10:07:48 2009
                              Errors in file d:\oracle\admin\midc\bdump\midc_dbw0_416.trc:
                              ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
                              ORA-01110: data file 1: 'D:\ORACLE\ORADATA\MIDC\SYSTEM01.DBF'
                              ORA-27041: unable to open file
                              OSD-04002: unable to open file
                              O/S-Error: (OS 2) The system cannot find the file specified.

                              Wed Jan 21 10:07:48 2009
                              Errors in file d:\oracle\admin\midc\bdump\midc_dbw0_416.trc:
                              ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
                              ORA-01110: data file 9: 'D:\ORACLE\ORADATA\MIDC\USERS01.DBF'
                              ORA-27046: file size is not a multiple of logical block size
                              OSD-04012: file size mismatch (OS 100941825)

                              Media Recovery failed with error 1110
                              ORA-283 signalled during: alter database recover until cancel using backup c...
                              Wed Jan 21 10:14:39 2009
                              alter database archivelog
                              Wed Jan 21 10:14:39 2009
                              ORA-265 signalled during: alter database archivelog...
                              Wed Jan 21 10:15:52 2009
                              alter database datafile 'd:\oracle\oradata\midc\USERS01.DBF' offline
                              Wed Jan 21 10:15:52 2009
                              ORA-1145 signalled during: alter database datafile 'd:\oracle\oradata\midc\US...

                              Any suggestions to retrieve the data is higly appreciable.

                              Regards
                              Vijay Kumar
                              • 12. Re: Unable to mount the database
                                420940
                                Since your database is in mount stage, can you check the location of datafiles in the dictionary with below command and send the output:

                                SQL> select name from v$datafile;

                                Comment: I think in your controlfile the location of datafiles is different than the physical location of datafiles.
                                • 13. Re: Unable to mount the database
                                  577696
                                  Thanks to all. My issue has been solved. At the start of issue I was not able to mount the database but later the prime problem was recovering data from the data file which was not in sync with the control file.
                                  Please check this link for solution.
                                  unable to recover datafile or make it offline

                                  Cheers....

                                  Regards
                                  Vijay Kumar