Starting with Oracle Database 12c, in a Data Guard environment, you can restore data files on a primary (standby) database by connecting to a standby (primary) database over the network .
RMAN restores database files, over the network, from the physical standby (primary) database by using the FROM SERVICE clause of the RESTORE command. The FROM SERVICE clause provides the service name of the physical standby (primary) database from which the files must be restored. During the restore operation, RMAN creates backup sets, on the physical standby database (primary), of the files that need to be restored and then transfers these backup sets to the target database over the network.”
Optionally, you can use SECTION SIZE to restore files from the source database as multisection backup sets. You can also compress the transferred files by specifying the USING COMPRESSED BACKUPSET.
Prerequisites for restoring Files from remote host :
- The password file on the source database and the target database must be the same.
- The
tnsnames.orafile in the target database must contain an entry that corresponds to the remote database.
In this post, I will demonstrate restore of a data file on primary from standby using service clause of RMAN Restore command.
Current scenario:
- Primary CDB : Boston
- Physical Standby CDB : London
- PDB : Dev1
– Create a new tablespace called sample in PDB dev1 on primary (boston)
BOSTON>alter session set container=dev1;
create tablespace sample
datafile '/u01/app/oracle/oradata/boston/dev1/sample01.dbf'
size 5m;
– Verify that parameter standby_file_management = auto
on standby database (london)
LONDON>sho parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
– Verify that datafile for tablespace sample has been created on physical standby (london)
LONDON>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/london/system01.dbf
/u01/app/oracle/oradata/london/sysaux01.dbf
/u01/app/oracle/oradata/london/undotbs01.dbf
/u01/app/oracle/oradata/london/pdbseed/system01.dbf
/u01/app/oracle/oradata/london/users01.dbf
/u01/app/oracle/oradata/london/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/london/dev1/system01.dbf
/u01/app/oracle/oradata/london/dev1/sysaux01.dbf
/u01/app/oracle/oradata/london/dev1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/london/dev1/example01.dbf
/u01/app/oracle/oradata/london/dev1/sample01.dbf
– Create table hr.employees2 in new tablespace sample on primary
BOSTON>sho con_name
CON_NAME
------------------------------
DEV1
BOSTON>create table hr.employees2 tablespace sample
as select * from hr.employees;
select count(*) from hr.employees2;
COUNT(*)
----------
107
– To simulate loss of datafile, rename sample01.dbf to sample01.sav on primary host
BOSTON>!mv /u01/app/oracle/oradata/boston/dev1/sample01.dbf /u01/app/oracle/oradata/boston/dev1/sample01.sav
– Restart primary – error while opening as datafile is missing
BOSTON>conn / as sysdba
shu abort;
startup
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/u01/app/oracle/oradata/boston/dev1/sample01.dbf'
– Take the missing datafile offline on primary and then open primary database
BOSTON>alter session set container=dev1;
alter tablespace sample datafile offline;
alter session set container=cdb$root;
alter database open;
BOSTON>sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DEV1 MOUNTED
BOSTON>alter pluggable database dev1 open;
-- Connect to primary (boston) using RMAN
[oracle@host01 ~]$ . oraenv
ORACLE_SID = [boston] ?
[oracle@host01 ~]$ rman target /
-- Restore datafile from physical standby database (london) over network
RMAN> restore tablespace dev1:sample from service 'london';
Starting restore at 23-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service london
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/boston/dev1/sample01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 23-JAN-15
– Recover the restored tablespace using archivelogs available
locally on primary database (boston)
RMAN> recover tablespace dev1:sample; Starting recover at 23-JAN-15 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 23-JAN-15
– Bring tablespce online
BOSTON>alter session set container=dev1;
alter tablespace sample datafile online;
select count(*) from hr.employees2;
COUNT(*)
----------
107
Note:
As mentioned by a reader, datafile can be restored over network in 11g as well using auxiliary connection (rman target sys/@PRIMARY_db auxiliary sys/@STANDBY_db) as is demonstrated in this post. But this syntax supports only image copy backups and not backupset / compressed backupsets. The restore from service in 12c supports backupset and compressed backupset as well resulting in reduced network traffic.
I hope this post was useful.
Your comments and suggestions are always welcome.
References:
https://docs.oracle.com/database/121/RCMRF/rcmsynta2008.htm#RCMRF149
http://docs.oracle.com/database/121/BRADV/rcmadvre.htm#BRADV681
——————————————————————————–
Related Links :
Recover Standby Datafile From Primary (11g)
———————