Previous | Table of Contents | Next

Page 615

ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: `/opt/oracle/dbs/usr1test.dbf'
SVRMGR> recover database automatic;
ORA-00274: Illegal recovery option AUTOMATIC
SVRMGR> recover automatic database;
ORA-00279: Change 15185 generated at 08/24/97 21:33:21 needed for thread 1
ORA-00289: Suggestion : /opt/oracle/archive/test200.arc
ORA-00280: Change 15185 for thread 1 is in sequence #40
ORA-00278: Logfile `/opt/oracle/archive/test199.arc' no longer
 needed for this ry
ORA-00308: cannot open archived log `/opt/oracle/archive/test200.arc'
ORA-07360: sfifi: stat error, unable to obtain information about file.
AT&T System V/386 Error: 2: No such file or directory
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Oracle is now waiting for you to tell it what to do next. Because your complete recovery failed, you must now begin an incomplete recovery. First, cancel the current recovery by typing CANCEL. Now shut down the database normally.

The tablespace USERS uses usrtest.dbf, which Oracle cannot completely recover because of the missing archived logs. Unfortunately, your life just got much more complicated. Now would be a good time to let the spouse know you will not be home for a while because there is no way to bring the usrtest.dbf file back to a consistent state relative to the rest of the database. Your only safe alternative (from a data consistency standpoint) is to restore all the data files from your last backup and apply archived logs through log #199. You will be losing all of the transactions that have occurred since log #199. Here are the steps you'll need to follow:

  1. Restore all of the data files from your last backup.

  2. Try to start the database normally.

  3. When Oracle says it is in need of recovery, enter this SQL statement:
          RECOVER AUTOMATIC DATABASE;
    
  4. Oracle will stop recovering when it cannot find the archived log file you are missing.

  5. Cancel the recovery by entering CANCEL.

  6. Cause an incomplete recovery with this SQL statement:
         RECOVER DATABASE UNTIL CANCEL;
    
  7. Reply with CANCEL at the prompt.

  8. Force the database open with this SQL statement:
    ALTER DATABASE OPEN RESETLOGS;
    
  1. Immediately shut down the database.

  2. Make a cold backup.

  3. Restart the database normally.

Page 616

CAUTION
Be sure to make a cold backup anytime you open the database with RESETLOGS. If you do not, any archived log files will be useless because RESETLOGS makes the current archived log files incompatible with a pre-RESETLOGS backup of the database.

Through a lot of work, you can restore the data contained in the USERS tablespace up to log #199 and logically move it into the database without losing any data in other tablespaces. Doing this may create referential integrity problems with your data. Although the specific procedures for this are beyond the scope of this book, here's the general plan:

  1. Restore your last backup of the system to a test machine. You'll need to re-create redo logs manually.

  2. Copy all of the archived logs through #199 to the test machine.

  3. Recover the database with the RECOVER DATABASE UNTIL CANCEL command.

  4. Step through all of the archived logs until you reach #199. After 199, cancel the restore.

  5. Open the database with the RESETLOGS option.

  6. Perform a logical backup of all of the tables in the USERS tablespace.

  7. Re-create the USERS tablespace on your failed system.

  8. Logically restore the tables exported in step #6 back to the instance that lost the USERS tablespace in the first place.

In a situation such as this, it is best to bring Oracle Support into the process. Their expertise will provide all of the details relevant to your particular recovery situation. If you do intend to pursue this course of action, call Oracle Support before you attempt any type of recovery—do not attempt an incomplete recovery.

Renaming and Moving Database FilesThere are many times when you need to either rename files in your database or move them from one location on the server to another. Oracle does allow you to do all of this, but there are very specific steps that should be followed. The most important of these steps is that you should do all of this type of work during off-hours. You will need to shut down the database to perform many of these operations. Be aware that dropping a redo log file, changing a control file entry, or other operations within Oracle will not physically remove or rename files in the OS. You must perform these operations manually.

Control FilesThe location of control files are kept in the appropriate init.ora/config.ora file with the control_files keyword. To move control files, complete the following steps:

  1. Shut down the database.

  2. Copy or move a control file to the new location.

  3. Edit the appropriate init.ora or config.ora file. There is a list of control files following the control_files keyword. You may change the location or name of a control file, delete a control file from the list, or add a location to a new control file.

  4. Start up the database.

Page 617

Redo LogsRedo logs must be changed from within either SQL*Plus or Server Manager. Technically, you can perform this operation while the database is up and running, but it is much easier to do when you are sure there is no activity on the system. To move or rename redo logs, complete the following steps:

  1. Shut down the database.

  2. Start the database in EXCLUSIVE mode. The following example shows this operation from within Server Manager:
    STARTUP EXCLUSIVE;
    
  3. Use this SQL statement to find out the group number of the file(s) you want to change:
    SELECT * FROM V$LOGFILE;
    
    Keep a note of the group number you are working with.

  4. Now determine which redo log group is active:
    SELECT * FROM V$LOG;
    
  5. The active redo log group is indicated by CURRENT, shown in the STATUS column from the query run in #4. If a file you need to move is currently in an active group, switch Oracle to the next redo log group:
    ALTER SYSTEM SWITCH LOGFILE;
    
  6. Now drop the redo log group. In the example, you're dropping the #2 log group. In your system, use the number you wrote down from step 3.
    ALTER DATABASE DROP LOGFILE GROUP 2;
    
  7. Create the redo log file group with the files in the location you want. In this example, you will re-create group #2 with two files (you should always have at least two files in each redo log group). You will use 1MB for each file. Obviously, your group number, actual filenames, and size will vary according to your site requirements.
    ALTER DATABASE ADD LOGFILE GROUP 2
    (`/opt/oracle/dbs1/log2atest.dbf','/opt/oracle/dbs2/log2btest.dbf') size 1M;
    
  8. Shut down the database and restart it normally.

Data Files Data files are fairly easy to move around. You'll need to use Server Manager and an OS utility to actually move or rename data files. If you are relocating files as part of a physical recovery, just place the data files where you would like them to be. Here's how to change your data file locations/names:

  1. Shut down the database if it is running.

  2. With an OS utility, move or rename the files to the new locations or names. Be sure to keep track of exactly where files were and where they are now.

  3. Start the database in MOUNT mode. The following example shows this operation from within Server Manager:
    STARTUP MOUNT;
    

Previous | Table of Contents | Next