Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost mount point in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain the location of all datafiles from the table dba_data_files. Note: There exist two data files on /u05 that can be used for this test.

[oracle@testdb02 ~]$ SQL*PLUS ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

SQL>

3. Exit SQL*PLUS and navigate to /u05/oradata/ORCL, create temp directory and move all files located on the mount point /u05 to the new directory. This will simulate the loss of all Oracle related files on /u05. In a real situation, you would have your SA recreate the /u05 mount point.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$ mv tools01.dbf temp/
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp temp02.dbf
[oracle@testdb02 ORCL]$

4. Log back into SQL*PLUS and attempt to shutdown the database. You will receive an indicating that the files on mount point /u05 cannot be located.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL> shutdown
ORA-01116: error in opening database file 3
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

5. Exit SQL*PLUS and force a shutdown of the database by killing the smon process.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 12677 1 0 21:04 ? 00:00:00 ora_smon_ORCL
oracle 14115 13009 0 21:35 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 12677
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 14137 13009 0 21:36 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

6. Logon to SQL*PLUS and perform open mount operation, so the recovery operation can be performed.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
SQL>

7. Exit SQL*PLUS and enter the RMAN utility connecting to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

RMAN>

8. Issue the restore database command, which will restore all database components.

RMAN> restore database;

RMAN>

9. Issue the recover database command, which will apply all archive redo logs since the last backup set.

RMAN> recover database;

RMAN>

10. Exit out of RMAN, enter into SQL*PLUS and open the database. Once the database has successfully opened, you know that all database files are in a consistent state.

RMAN> exit

SQL>

This completes recovery of all files on a single mount point or directory.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com