Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

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

  • veer

    when we drop a datafile or tbs in primary and same happens in standby right? so even then will the above procedure works?