Monthly Archives: January 2015

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)



OTNYathra 2015

The Oracle ACE directors and Java champions will be organizing an evangelist event called ‘OTNYathra 2015’  during February 2015. during which a series of 7 conferences will be held across 7 major cities of India  in a time period of 2 weeks.  This event will bring the Oracle community together, spread the knowledge and increase the networking opportunities in the region. The detailed information about the event can be viewed at

I will be presenting a session on Adaptive Query Optimization on 13th Feb 2015 at FMDI, Sector 17B, IFFCO Chowk , Gurgaon.

Thanks to Sir Murali Vallath  and his team for organizing it and giving me an opportunity to present.

Hope to see you there!!


Map OS Groups To Administrative Privileges After Installation

During installing database software, user is prompted to enter names of various operating system groups mapping to various administrative privileges (SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSDG). One might map one operating system group to multiple administrative privileges if role separation is not desired.  In case the need for role separation arises later, the mapping can be specified by updating  $ORACLE_HOME/rdbms/lib/config.c file and then relinking it. This post explains the various steps.

While installing database software on linux, I had not  created OS groups corresponding to administrative privileges SYSBACKUP, SYSKM, SYSDG.  Now I want OS groups dgdba, backupdba and kmdba to map to SYSDG, SYSBACKUP and SYSKM administrative privileges respectively.

-- Check that groups dgdba, backupdba and kmdba do not exist

[root@host01 etc]# cat /etc/group | grep dba

– Create groups dgdba, backupdba and kmdba

#groupadd -g 54321 dgdba
groupadd -g 54322 backupdba
groupadd -g 54323 kmdba

– Check that groups dgdba, backupdba and kmdba have been created

[root@host01 etc]# cat /etc/group | grep dba

– Create a user test which is a member of dgdba group

[root@host01 /]# useradd test -g oinstall -G dgdba

[root@host01 /]# passwd test
Changing password for user test.
New UNIX password:

– Login as test user

[root@host01 /]# su - test

[test@host01 ~]$ . oraenv
ORACLE_SID = [test] ? orcl

– As test user try to connect as sysdg – fails as dgdba group
has not been mapped to SYSDG administrative privilege

[test@host01 ~]$ dgmgrl

DGMGRL> connect sysdg/xx
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

– Verify in configuration file that currently OS group dba corresponds to administrative priviliges SYSDBA, SYSKM, SYSDG and SYSBACKUP

[oracle@host01 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c |grep define
/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
#define SS_BKP_GRP "dba"
#define SS_DGD_GRP "dba"
#define SS_KMT_GRP "dba"

– Edit configuration file so that OS groups dgdba, backupdba and kmdba to map to SYSDG, SYSBACKUP and SYSKM administrative privileges respectively.

[oracle@host01 ~]$ vi $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
#define SS_BKP_GRP "backupdba"
 #define SS_DGD_GRP "dgdba"
 #define SS_KMT_GRP "kmdba"

– To relink oracle binaries, Shut down all Oracle processes of all instances

a. Shut down the listener.

$ lsnrctl stop

b. Shut down all instances.

$ ps -ef |grep pmon |grep -v grep
oracle 11832 1 0 15:21 ? 00:00:00 ora_pmon_orcl

ORCL> shutdown immediate

— Relink binaries

[oracle@host01 ~]$ cd $ORACLE_HOME/bin; relink all

writing relink log to: /u01/app/oracle/product/

– Now as test user connect as sysdg – succeeds

[test@host01 bin]$ dgmgrl

DGMGRL> connect sysdg/xx
Connected as SYSDG.

– Optionally modify existing OS user oracle to become part of new groups

#usermod -a -G dgdba,backupdba,kmdba oracle

[root@host01 /]# su - oracle

[oracle@host01 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin),54321(dgdba),54322(backupdba),54323(kmdba)

Hope it helps!

Your comments and suggestions are always welcome.


Related Links:


Oracle 12c Index

Oracle Ace Associate

It gives me immense pleasure to share with you the news that
I am an Oracle Ace Associate“.

Thanks to the “Oracle ACE Program” for accepting  me  to receive the Oracle ACE Associate award.

My heart is full of gratitude for Sir Murali Vallath who nominated me for this.

Thanks to AIOUG for giving me an opportunity to speak during SANGAM 14 and publishing my white paper on ‘Histograms – Pre-12c and now” in  Oracle Connect Issue Dec 2014.

I want to  thank  my husband  for encouraging me, and readers of my blog for their time, comments and suggestions.

Thank you so much!