2 Replies Latest reply: Mar 31, 2013 8:43 PM by Hemant K Chitale RSS

    Restoring database with NOARCHIVELOG

    1000135
      After several failed attempts at trying to get Enterprise Manager to work with my database, I decided to uninstall the Enterprise Edition completely and then to install it from scratch. I did and found, as expected, that my database files were still intact, on another drive. However, what I didn't expect was that uninstalling Oracle also removed the init*.ora files I had for the different databases, and so the DBCA showed no databases at all. So I figured I would create new databases with the same settings, and then copy all the files from the old locations to the new ones. That went well, except for with one database.

      When trying to open that database, I get a ORA-00280 and the suggested file that should contain the sequence, is not at that location. In fact I could not find it anywhere on my disks, even after using a program to undelete content. However I know for a fact that I ran my old databases in NOARCHIVELOG mode, yet the name of the suggested file hinted at it being an archive log.

      I have been googling all day long, and tried out several different methods, restoring, recovering, RESETLOGS, NORESETLOGS, what to do when not having neither archive logs or possibly redo logs (as I don't know what the ONLINELOG files are specifically), but they all ended up with me being at the same point, needing that .arc file which I should've never had, due to NOARCHIVELOG mode.

      So here is where I'm at. I have a CONTROLFILE folder with 1 control file, a DATAFILE folder with the usual tablespace files, and an ONLINELOG folder with 3 log files. I even tried using those 3 log files for recovering the database, but to no avail. I have all the data, and while I guess the ORA-00280 means that there has been a change in the database since the data files were written, I haven't changed much data so I could easily add whatever little data is missing from the tablespace USERS. So I need some instructions, some help on how to open and mount the database and avoid getting the ORA-00280 or being able to ignore it and still open the database. As I created a new database, there are no archive logs backed up, so I can't recover any files that way, I only have the data files, control files and log files to work with.
        • 1. Re: Restoring database with NOARCHIVELOG
          stevencallan
          You're not in as bad as shape as you think.

          If you have the data files and one control file, you can recover/restore the database. First thing to do is get a working init.ora file and get the instance up and running. Then, work on the control file (so you can at least mount the database). Fix what you need to in terms of paths/file locations. It may take several iterations. You can also bypass the existing controlfile and create your own. Eventually, you'll be able to have a successful "alter database open" step.

          http://www.databasejournal.com/features/oracle/article.php/3738736/Recovering-from-Loss-of-All-Control-Files.htm

          Number one precaution: take a copy of what you have now and put it somewhere else, just in case. You don't want to make the situation worse.
          • 2. Re: Restoring database with NOARCHIVELOG
            Hemant K Chitale
            From the SQLPlus command line try
            shutdown immediate
            startup mount
            recover database until cancel;
            -- here issue a CANCEL when it prompts for the archivelog;
            alter database open resetlogs;
            However, as already adviced, take a fresh backup of what you have -- with the instance and oracle service shutdown -- before you try anything.


            Hemant K Chitale