oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Recovering from loss of datafile by changing file location

Assume we have lost a datafile '/u03/oracle/test/users01.dbf' because the disk mounted on /u03 is corrupt.

Now we have a database with all datafiles intact in '/u02' mount point except one datafile located in '/u03' mount point is not available.

We have RMAN backups and can recover this file completely through RMAN, but since the original location of the datafile is not available and it takes a day or two to attach a new disk and format it.

In this kind of situation we can minimize the downtime by restoring the file to another location and then recover the datafile.

Let's see how to do it using RMAN

Case Study

Recover from the loss of a datafile by changing location.

Assuming the following

Solution

To restore the file to a different location and  to recover it through RMAN do the following

Step 1.

Start and mount the database by starting SQL Plus

$ export ORACLE_SID=test
$ sqlplus
SQL> startup

When we try to open, Oracle will show an error that a datafile is missing as shown below

open db in sqlplus

From the above the above output we can see that '/u03/oracle/test/users01.dbf' datafile is not found

We have also found that the disk mounted on '/u03' is damage and doesn't get mount. Now we need to restore the file to a different location say '/u02' mount point and then perform complete recovery on it.

To achieve this please proceed to next step

Step 2

Exit from SQL Plus and start RMAN by typing the following command

$ rman target / nocatalog

start rman for recovery

 

Step 3

Now type the following script at RMAN prompt to recover the loss datafile

RMAN> run {
        set newname for datafile '/u03/oracle/test/users01.dbf' to
                '/u02/oracle/testdb/test/users01.dbf';
        restore datafile '/u03/oracle/test/users01.dbf';
        switch datafile '/u03/oracle/test/users01.dbf';
        recover datafile '/u02/oracle/testdb/test/users01.dbf';
}

 In the above script

set newname...

This command tells RMAN to change location while restoring file

restore datafile ....

Line will restore the datafile to new location.

switch datafile....

Command in this line changes the location of the datafile from '/u03..' to '/u02..' in control file

recover datafile ....

Line will apply transactions from archive files to perform complete recovery

The output is shown below when we execute the above script

restore datafile to new location using rman

Step 4

We can now go to SQL Plus and open the database and check status of the datafile

sqlplus open database

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved