12c Database : RMAN Enhancement : Restore the datafiles directly from Standby using TNS Service

From 12c Onwards, RMAN has been enhanced to restore/recover the datafiles/tablespaces etc from the network services rather copying them manually. This enhanancement facilitates easy recovery operations when you have standby database without backups or activation etc. YOu can even recover the database from standby using service in RMAN in restore command.

Lets see how it works Create a tablespace for our test.

	SQL> create tablespace test datafile '/u01/app/oracle/oradata/DB12c/test.dbf' size 100m ;

	Tablespace created.
Create a sample table
	SQL> create table t tablespace test as select * from dba_objects;
	Table created.
Check in standby and in primary the datafile exists 

	SQL> select file_id,file_name from dba_data_files;

	   FILE_ID		FILE_NAME
	   ----------		--------------------------------------------------------------------------------
	         1		/u01/app/oracle/oradata/DB12c/system01.dbf
	         3		/u01/pp/oracle/oradata/DB12c/sysaux01.dbf
	         4		/u01/app/oracle/oradata/DB12c/undotbs01.dbf
	         6		/u01/app/oracle/oradata/DB12c/new/users01.dbf
	        13		/u01/app/oracle/oradata/DB12c/test.dbf

	The datafile number is 13 that we are going to delete and restore

Removed the datafile intentionally

	SQL> !rm -f /u01/app/oracle/oradata/DB12c/test.dbf

Try to access the file from database , it will throw error

	SQL> Select count(*) from t;
	ORA-00376: file 13 cannot be read at this time
	ORA-01110: data file 13: '/u01/app/oracle/oradata/DB12c/test.dbf'

In Primary Database connect to rman

	RMAN> connect target sys/admin123@db12c

	connected to target database: DB12C (DBID=1279089071)

Make the datafile Offline

	RMAN> alter database datafile 13 offline;

	Statement processed

Check my service

	[oracle@Geek DBA12c admin]$ tnsping stby12c

	TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 15-SEP-2013 18:07:20

	Copyright (c) 1997, 2013, Oracle.  All rights reserved.

	Used parameter files:
	/u01/app/oracle/product/12.1.0.1/db_1/network/admin/sqlnet.ora


	Used TNSNAMES adapter to resolve the alias
	Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY12C)))
	OK (40 msec)
	[oracle@Geek DBA12c admin]$

Restore the datafile 13 that we have deleted, 

	Please note, there is no backup and nothing, and use of new option " from service" this is the service where to connect to standby from primary.

	RMAN> restore datafile 13 from service stby12c;
	Starting restore at 15-SEP-13
	using channel ORA_DISK_1
	using channel ORA_DISK_2
	using channel ORA_DISK_3
	using channel ORA_DISK_4
	using channel ORA_DISK_5

	channel ORA_DISK_1: starting datafile backup set restore
	channel ORA_DISK_1: using network backup set from service stby12c
	channel ORA_DISK_1: specifying datafile(s) to restore from backup set
	channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/DB12c/test.dbf
	channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
	Finished restore at 15-SEP-13

Recover the datafile now

	RMAN> recover datafile 13;

	Starting recover at 15-SEP-13
	using channel ORA_DISK_1
	using channel ORA_DISK_2
	using channel ORA_DISK_3
	using channel ORA_DISK_4
	using channel ORA_DISK_5

	starting media recovery
	media recovery complete, elapsed time: 00:00:02

	Finished recover at 15-SEP-13

Bring the datafile online

RMAN> alter database datafile 13 online;

Statement processed

Check your table and its table and its respective datafile that is deleted

	RMAN> select count(*) from t;

	  COUNT(*)
	----------
	     90777
	RMAN> select file_id,file_name,tablespace_name from dba_data_files where file_id=13;

	   FILE_ID	FILE_NAME				TABLESPACE_NAME
	--------------------------------------------------------------------------------
		13	/u01/app/oracle/oradata/DB12c/test.dbf	TEST
-Thanks
Geek DBA