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:
RECOVER AUTOMATIC DATABASE;
RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;
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:
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 recoverydo 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:
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:
STARTUP EXCLUSIVE;
SELECT * FROM V$LOGFILE;Keep a note of the group number you are working with.
SELECT * FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 (`/opt/oracle/dbs1/log2atest.dbf','/opt/oracle/dbs2/log2btest.dbf') size 1M;
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:
STARTUP MOUNT;