RMAN - RESTORING A DATAFILE

1) Click the start button and give RUN. Type cmd and the command window will open.

2) Type sqlplusw/nolog and the sql window will open

3) Type conn / as sysdba, a message “connected to an idle instance” will be displayed

4) Now give startup , you will get the below message,

Total System Global Area 118255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

Database mounted.

Database opened.

5) The above steps you have to use every time you use oracle

For working with RMAN, the archive log mode must be enabled.

6) To check whether the database is in archive log mode use this command,

Sql> select log_mode from v$database;

If its in noarchive log mode then you have to shut down the database and then start the database in mount state and then change it to archive log mode.

i.e. sql>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

Sql> conn / as sysdba

connected to an idle instance

sql>startup mount

Total System Global Area 18255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

Database mounted

Sql>alter database archivelog;

Database altered.

Sql>alter database open;

Database altered.

The database is in archive log mode, now we can use RMAN.

7) Go to the command prompt and type RMAN

Now you will see the directory will change from c:/documents and settings / to RMAN>

RMAN>connect target /

RMAN will now be connected to the target database

Taking backup of the entire database:

RMAN>BACKUP DATABASE;

The backup of the entire database is taken using this command

We can also use

RMAN>BACKUP DATABASE PLUS ARCHIVELOG;

Recovery from missing or corrupted datafile(s):


This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. Go to oracle->oradata->DINESH (db name)and delete users01.dbf.Then connect to oracle. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments; all other lines are feedback from SQL Plus :) Backup of the entire database is necessary here.

.

Ok now,

In the command prompt give
C:\>sqlplusw /nolog



--Connect to the idle Oracle process as a privileged user and start up instance

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 118255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

ORA-01110: data file 8: 'G:\ORACLE\ORADATA\DINESH\USERS01.DBF'

The error message tells us that file# 8 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository, can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN :)

--logon to RMAN

C:\>rman

Recovery Manager: Release 9.0.1.1.1 - Production

(c) Copyright 2001 Oracle Corporation. All rights reserved.

RMAN> connect target /

connected to target database: DINESH (DBID=3800052808)

--restore missing datafile

RMAN> restore datafile 8;

--restore missing datafile

Starting restore at 18-DEC-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00008 to G:\ORACLE\ORADATA\DINESH\USERS01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=G:\ORACLE\ORA90\DATABASE\01L16IB2_1_1 tag=null params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 18-DEC-09



RMAN> recover datafile 8;

Starting recover at 18-DEC-09

Using channel1 ORA_DISK_1

Starting media recovery

Media recovery complete

Finished recover at 18-DEC-09

--open database for general use

RMAN> alter database open;

database opened

Now open oracle->oradata->DINESH and you will find that the deleted USERS01.DBF is present.

CLICK HERE TO KNOW MORE