Category Archives: 12c Dataguard

12c Dataguard: Restore Data File From Service

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.ora file 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

------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

– Verify that datafile for tablespace sample has been created on physical standby  (london)

LONDON>select name from v$datafile;


– Create table hr.employees2 in new tablespace sample on primary

BOSTON>sho con_name


BOSTON>create table hr.employees2 tablespace sample
       as select * from hr.employees;
      select count(*) from hr.employees2;


– 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;
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

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

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;


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.



Related Links :


12c Dataguard Index

Recover Standby Datafile From Primary (11g)