Recovery Scenarios

Recovering a Database in NOARCHIVELOG Mode:

When the database is running in noarchivelog mode, then its very difficult to perform the complete recovery. Because while the database is running, the online redo log file will get overwritten as the transactions proceeds. Following scenerio explains the complete and incomplete recovery.

Scenario 1: Incomplete Recovery

Situation

Losing Datafiles in NOARCHIVELOG Mode

Cause

Media failure happens and damages a datafile. Datafile is unusable now.

Solution

In this case since we lost a datafile and database is running in noarchivelog mode, you cannot recover the datafile from the online logs, because the datafile itself is lost. You need to restore the complete database from the latest old consistant backup. Restoring only that datafile wont help, because that datafile wont be consistant with respect to other datafile. Also we cannot apply the current online redo log files to that 1 datafile, since the changes must have been over written since we have taken a backup of that datafile.
If the backup is taken just few minutes back and a media failure has happned after opening the database, then there is a chance that restoring only that database and applying the online redo log file will recover the datafile and database can be opened. This is because the redo log might not have been over written within those few minutes. But this is a very very rare situation. Here we can have a complete recovery in no archivelog mode.

So the general solution applicable to this scenerio will be to restore the complete database (datafile and controlfile) from the latest old consistant backup. Since we dont take a backup of online redo log files, we have to use the current online redo log files. But the SCN in current online redo log file is different than the restored datafiles and controlfiles, we need to wipe out the content of current redo log files. This is called changing the incarnation. This is possible by using “alter database open resetlogs”.
Follow below steps to recover the database.

1) Shut down the database
SHUTDOWN IMMEDIATE

2) Restore the backup to the appropriate location.

cp (backup location) (datafile location)

In case the current datafile location is damaged due to media failure, you can restore the backup to some other location. In this case you need not restore the control files. You have to create a new control file as given below.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u03/oradata/orcl/redo01.log’ SIZE 50M,
GROUP 2 ‘/u03/oradata/orcl/redo02.log’ SIZE 50M,
GROUP 3 ‘/u03/oradata/orcl/redo03.log’ SIZE 50M
— STANDBY LOGFILE
DATAFILE
‘/u03/oradata/orcl/system01.dbf’,
‘/u03/oradata/orcl/undotbs01.dbf’,
‘/u03/oradata/orcl/sysaux01.dbf’,
‘/u03/oradata/orcl/users01.dbf’
CHARACTER SET WE8ISO8859P1
RECOVER DATABASE

you can obtain the above script for your database using below commands

STARTUP MOUNT
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Once you get the “Create Controlfile” command, change the location of datafiles and online redo log files to new location.

Change the location of control file in init.ora file (control_files is the name of parameter).
Run the script and create the control file to new location.

3) ALTER DATABASE OPEN RESETLOGS;

In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:

RECOVER DATABASE UNTIL CANCEL
CANCEL

ALTER DATABASE OPEN RESETLOGS;

Recovering a Database in NOARCHIVELOG Mode:

Scenario 2: Complete Recovery

Situation

Losing Datafiles in ARCHIVELOG Mode. Performing closed database recovery.

Cause

Media failure happens and damages a datafile. Datafile is unusable now. We can shutdown the database and recover.

Solution

If the datafile that is lost belongs to SYSTEM tablespace, then the database will get shutdown. If the media failure is temporary, then fix the media problem and then start the database. Usually crash recoery will restore the lost transactions and database will be up and consistant.

If there is a major problem with media, then the database needs to be restored to alternate location and recovery needs to be performed.

1) You can determine which file needs to be recovered by quering V$RECOVER_FILE table after mounting the database.

2) The files which needs to be recovered can be copied to alternate location using OS copy commands. If the current database files are damaged and cannot be used, then use the most recent backup of those files to restore to alternate location.

3) Once restored, you need to change the location of files in control file. Use the below command to do so after mounting the database.
STARTUP MOUNT
ALTER DATABASE RENAME FILE

4) Recovering the database involves applying the archivelog files and online redo log files.
Before applying the archivefiles and recovering the database, make sure that all the datafiles are online.
ALTER DATABASE DATAFILE ‘/oracle/dbs/user_01.dbf’ ONLINE;
5) You can issue any of the below commands to recover the database.
RECOVER DATABASE # This will recover complete database in one command
RECOVER TABLESPACE USER # This will recover one tablespace at a time.
RECOVER DATAFILE ‘/oracle/dbs/user_01.dbf’ # This will recover 1 datafile at a time.

During recovery it will ask for the archivefiles. Please provide the required archivefiles and redo log files and revover the database.

Once you get a message “Media recovery complete.”, means that recovery is done.
You can once again check V$RECOVER_FILE table to see if some datafile is still remaining to be recovered. Once everything is done use “ALTER DATABASE OPEN” to open the database. Do not use resetlogs to open the database.

Scenario 3: Complete Recovery

Situation

Losing Datafiles in ARCHIVELOG Mode. Performing open database recovery.

Cause

Media failure happens and damages a datafile. Datafile is unusable now. Some times the database wont shutdown down but makes the datafile offline, unless the datafile does not belongs to SYSTEM tablespace. You will come to know about the same when a user complain about getting the below error.

ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 11: ‘/oracle/oradata/trgt/cwmlite02.dbf’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

Solution

This clearly says that datafile ‘/oracle/oradata/trgt/cwmlite02.dbf’ is offline. The file now needs recovery to be consistant. If there is a media problem, you can check if that can be resolved quickly so that the datafile can be restored to same location. Else if the media failure takes time, you can restore the required datafile at some other locaiton.
Note that since the database is in archivelog mode, we dont need to restore the complete database back, only the files that needs recovery should be restored.
You can keep the database in open status and just make the respective tablespace offline, whose datafile needs to be recovered.

ALTER TABLESPACE users OFFLINE TEMPORARY;

Once taken offline, you can restore the file from the last consistant backup (if the current datafile is damaged). If the file is restored at some alternate location, then you need to make changes about the location of file in control file as well, using below command.

ALTER DATABASE RENAME FILE ‘?/oradata/trgt/users01.dbf’ TO ‘/disk2/users01.dbf’;

Recover the tablespace

RECOVER TABLESPACE users;

Bring the tablespace online.

ALTER TABLESPACE users ONLINE;

Scenario 4: InComplete Recovery

Situation

Doing an incomplete recovery due to user error.

Cause

In case of user error, we some times need to perform incomplete recovery to rollback the changes. User must have updated some tables with wrong value, to rollback we take the database or that specific tablespace back in time just before the user had made the changes.

Solution

Do the incomplete recovery even thought the database is in archivelog mode. We can recover the database until some SCN or until some time. Also we can do a tablespace point in time recoery.

References:

http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm