Recovering Datafiles in ARCHIVELOG Mode

Recovering Database when the database is running in ARCHIVELOG Mode.

Recovering from the lost of Damaged Datafile.

If you have lost one datafile. Then follow the steps shown below.

STEP 1. Shutdown the Database if it is running.

STEP 2. Restore the datafile from most recent backup.

STEP 3. Then Start sqlplus and connect as SYSDBA.

$sqlplus

Enter User:/ as sysdba

SQL>Startup mount;

SQL>Set autorecovery on;

SQL>alter database recover;

 If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.

STEP 4. Now open the database

SQL>alter database open;

Upon Startup DB Instance one of Datafile is missing or corrupted

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state

Step 1 Copy the missing Datafile from last taken Backup.

Note : Here you need all the archive log files after last taken backup

Step 2 sql> recover datafile 4.

When Media recovery completed messages shown you can open the database

Step 3 sql> alter database open.

Time Based Recovery (INCOMPLETE RECOVERY).

Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.

You have taken a full backup of the database on Monday night and the table was created on Tuesday and thousands of rows were inserted into it. Some user accidentally drop the table on Thursday and nobody notice this until Saturday.

Now to recover the table follow these steps.

STEP 1. Shutdown the database and take a full offline backup.

STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.

STEP 3. Start SQLPLUS and start and mount the database.

STEP 4. Then give the following command to recover database until specified time.

SQL> recover database until time '2011:08:16:13:55:00' using backup controlfile;

STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this

SQL> alter database open resetlogs;

STEP 6. After database is open. Export the table to a dump file using Export Utility.

STEP 7. Restore from the full database backup which you have taken before this activity.

STEP 8. Open the database and Import the table.

RECOVERING THE DATABASE IN NOARCHIVELOG MODE.

Option 1: When you don’t have a backup.

If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.

The following are the steps to drop a damaged datafile and open the database.

(UNIX)

STEP 1: First take full backup of database for safety.

STEP 2: Start the sqlplus and give the following commands.

$sqlplus

Enter User:/ as sysdba

SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE  '/u01/ica/usr1.dbf '  offline drop;

SQL>alter database open;

Option 2: When you have the Backup.

If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.

1. Either you can drop the damaged datafile, if it does not contain important information which you can  afford to loose.

2 . Or you can restore from full cold backup. You will loose all the changes made to the database since last full backup.

STEP 1: Take a full backup of current database.

STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.

(UNIX)

Suppose the backup is in  "/u2/oracle/backup" directory. Then do the following.

sqlplus>Shutdown Abort

$cp /u02/backup/*  /u01/ica

Note: This will copy all the files from backup directory to original destination. Also remember to copy the control files and redologfiles to all the mirrored locations.

sqlplus> startup

How to take user managed Database Backups

TAKING OFFLINE BACKUPS. ( UNIX )

Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.

$sqlplus

SQL> connect / as sysdba

SQL> Shutdown immediate

SQL> Exit

After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.

TIP:

To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.

Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.

$cd /u01/ica

$cp * /u02/backup/

Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.

TAKING ONLINE (HOT) BACKUPS.(UNIX)

To take online backups the database should be running in Archivelog mode. To check whether the database is running in  Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.

After connecting give the command "archive log list" this will show you the status of archiving.

$sqlplus

Enter User:/ as sysdba

SQL> ARCHIVE LOG LIST

If the database is running in archive log mode then you can take online backups.

Let us suppose we want to take online backup of  "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is  

"/u01/ica/usr1.dbf ".

Give the following series of commands to take online backup of USERS tablespace.

$sqlplus

Enter User:/ as sysdba

SQL> alter tablespace users begin backup;

SQL> host cp /u01/ica/usr1.dbf   /u02/backup

SQL> alter tablespace users end backup;

SQL> exit;

ALTER DATABASE BEGIN BACKUP ON OPEN MODE

AS SYSDBA

sql>alter database begin backup;
Database altered.

sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

NOTE: All the datafiles are in backup mode, now you can copy your all datafiles to backup location.


sql>alter database end backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

Bringing the database in Archivelog /No Archive Mode

Opening or Bringing the database in Archivelog mode.

To open the database in Archive log mode. Follow these steps:

STEP 1: Shutdown the database if it is running.

STEP 2: Take a full offline backup.

STEP 3: Set the following parameters in parameter file.

LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc

LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”

If you want you can specify second destination also

LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”

Step 3: Start and mount the database.

SQL> STARTUP MOUNT

STEP 4: Give the following command

SQL> ALTER DATABASE ARCHIVELOG;

STEP 5: Then type the following to confirm.

SQL> ARCHIVE LOG LIST;

STEP 6: Now open the database

SQL>alter database open;

Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.

To again bring back the database in NOARCHIVELOG mode.

STEP 1: Shutdown the database if it is running.

STEP 2: Comment the following parameters in parameter file by putting " # " .

 # LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”

# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”

# LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc

 STEP 3: Startup and mount the database.

 SQL> STARTUP MOUNT;

STEP 4: Give the following Commands

SQL> ALTER DATABASE NOARCHIVELOG;

STEP 5: Shutdown the database and take full offline backup.