how to switch over the datafile on another mount point

how to switch over the datafile on another mount point using RMAN

1)Datafiles location in production db

SQL>Select file_name from dba_data_files;

FILE_NAME

------------------------------

/RMANB/11g/oradata/orcl/users01.dbf

/RMANB/11g/oradata/orcl/undotbs01.dbf

/RMANB/11g/oradata/orcl/sysaux01.dbf

/RMANB/11g/oradata/orcl/system01.dbf

/RMANB/11g/oradata/orcl/example01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_02.dbf

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

OEAORATBS

Now we are going to switch the datafile of oeaoratbs to new /opt mountpoint.

Rman rarget /

RMAN> 

run{

Backup tablespace oeaoratbs;

sql 'alter tablespace oeaoratbs offline immediate';

set newname for datafile  '/RMANB/11g/data_file/tables/oeaoratbs_01.dbf

' to '/opt/oeaoratbs_01.dbf ';

restore tablespace oeaoratbs;

switch datafile all;

recover tablespace oeaoratbs;

sql 'alter tablespace oeaoratbs online';

 }

2> 3> 4> 5> 6> 7> 8>

sql statement: alter tablespace oeaoratbs offline immediate

executing command: SET NEWNAME

Starting restore at 20-JAN-12

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 00005 to /opt/ oeaoratbs_01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/PROD/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T003258_7kjt73qm_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/flash_recovery_area/PROD/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T003258_7kjt73qm_.bkp tag=TAG20120120T003258

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 20-JAN-12

datafile 5 switched to datafile copy

input datafile copy recid=2 stamp=773022827 filename=/opt/ oeaoratbs_01.dbf

Starting recover at 20-JAN-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:08

Finished recover at 20-JAN-12

sql statement: alter tablespace oeaoratbs online

RMAN> exit

  

SQL>Select file_name from dba_data_files;

FILE_NAME

------------------------------

/RMANB/11g/oradata/orcl/users01.dbf

/RMANB/11g/oradata/orcl/undotbs01.dbf

/RMANB/11g/oradata/orcl/sysaux01.dbf

/RMANB/11g/oradata/orcl/system01.dbf

/RMANB/11g/oradata/orcl/example01.dbf

/opt/oeaoratbs_01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_02.dbf

finally the datafile has been moved to new mont point.

Thanks

how to recover a datafile without backup.

how to recover a datafile without backup

Your database must be in archivelog mode.

Scenario:

The datafile was created at 9 am.

The datafile was corrupted or dropped at 5 pm.

SQL> select count(*) from event;

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/opt/oracle/oradata/pindb/god.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Solution for the issue:

Sql> alter database datafile 6 offline;

Database altered.

Sql> alter database create datafile ‘/opt/oracle/oradata/pindb/god.dbf';

Database altered.

sql> recover datafile 6;

Media recovery complete.

sql> alter database datafile 6 online;

Database altered.

SQL> select count(*) from event;