Home
 | 
Articles
 | 
High Availability
 | 
Creating a Physical Standby Database using RMAN (WITHOUT BACKUPS)
 | 
Creating a Physical Standby Database
 using RMAN (ACTIVE DUPLICATE)
Overview
Recent Article
All Archives
Topics
Comments 
Last modified: December 2016
»»
Physical Standby
From Oracle Database 11g onwards building a standby database has become a whole lot easier. With just one powerful RMAN command, the standby database can be created on another server without any backups.
Primary Database (RAC):

Oracle Release: 12c RAC (12.1.0.2.0)
Hostname(s):	ol-alpha.shannura.com (172.168.190.101)
				ol-beta.shannura.com  (172.168.190.102)
Operating System: Oracle Linux 7.3 (x64)
Database Name [DB_NAME]: 'shannura'
Database Unique Name [DB_NAME]: 'shannura'
Instance(s): 	shannura1, shannura2
TNS Name: shannura
Standby Database (Single Instance):

Oracle Release: 12c (12.1.0.2.0)
Hostname: ol-alpha-dr.shannura.com (172.168.190.110)
Operating System: Oracle Linux 7.3 (x64)
Database Name [DB_NAME]: 'SHANNURA'
Database Unique Name [DB_UNIQUE_NAME]: 'standby'
TNS Name: standby
Configuring the Primary
Let's configure the primary database first. Ensure database is in archive log mode:
SQL>
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
Enable force logging:
SQL>
SQL> select force_logging from v$database ;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.
Initialization parameters:
Take notice of the DB_NAME & DB_UNIQUE_NAME which is same, however, it shouldn't be same in the standby database i.e. DB_UNIQUE_NAME must be unique in the standby database. I'm going to call it as STANDBY

*.audit_file_dest='/u01/app/oracle/admin/shannura/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='+DATADG/SHANNURA/CONTROLFILE/current.261.933619011', '+FRADG/SHANNURA/CONTROLFILE/current.256.933619011'
*.db_block_size=8192
*.db_domain='shannura.com'
*.db_name='shannura'
*.db_unique_name='shannura'
*.db_recovery_file_dest='+FRADG'
*.db_recovery_file_dest_size=4785M
*.log_archive_format='%t_%s_%r.dbf'
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=1568M
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
SQL><>  
SQL> alter system set log_archive_config='DG_CONFIG=(shannura,standby)' scope=spfile;
System altered.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST arch reopen=60 max_failure=0 mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=shannura' scope=spfile;
System altered.

SQL> alter system set log_archive_dest_2='service=standby LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;
System altered.

SQL> alter system set log_archive_dest_state_2=defer scope=spfile;
System altered.

SQL> alter system set log_archive_format='shannura_%t_%s_%r.arc' scope=spfile;
System altered.

SQL> alter system set log_archive_max_processes=12 scope=spfile;
System altered.

SQL> alter system set fal_server=standby scope=spfile;
System altered.

SQL> alter system set fal_client=shannura scope=spfile;
System altered.

SQL> alter system set db_file_name_convert='shannura','standby','SHANNURA','STANDBY' scope=spfile ;
System altered.

SQL> alter system set log_file_name_convert='shannura',' standby','SHANNURA','STANDBY' scope=spfile ;
System altered.

SQL> alter system set standby_file_management=auto scope=spfile;
System altered.
Then bounce the primary database to take effect of these parameters.
$
[oracle@ol-alpha ~]$ srvctl stop database -database shannura
[oracle@ol-alpha ~]$ srvctl start database -database shannura
Standby Redo Logs (SRL) on the primary:
Standby redo logs enable the changes occurring in the primary database to be reflected in almost real time in the standby, a concept known as Real Time Apply (RTA). So, here we create the standby redo logs on the primary database (note: the standby redo logs are created in the primary. RMAN will create them in standby automatically):
There should be minimum of (threads)*(groups per threads + 1) SRLs created on the standby database. There are 2 threads with 2 groups per thread in this configuration on the primary side so there should be total of 6 SLRs at minimum needs to be created.
$
SQL> alter database add standby logfile group 5 ('+FRADG','+DATADG') size 50m;
Database altered.

SQL> alter database add standby logfile group 6 ('+FRADG','+DATADG') size 50m;
Database altered.

SQL> alter database add standby logfile group 7 ('+FRADG','+DATADG') size 50m;
Database altered.

SQL> alter database add standby logfile group 8 ('+FRADG','+DATADG') size 50m;
Database altered.

SQL> alter database add standby logfile group 9 ('+FRADG','+DATADG') size 50m;
Database altered.

SQL> alter database add standby logfile group 10 ('+FRADG','+DATADG') size 50m;
Database altered.
Setting up the TNS
Note: In RAC environment, the listener is running from GI home. My listener is listening for service 'shannura.shannura.com', looks perfect.
$<>  
[grid@ol-alpha admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-FEB-2017 05:30:58

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                01-FEB-2017 05:19:48
Uptime                    0 days 0 hr. 11 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ol-alpha/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.190.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.168.190.201)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ol-alpha.shannura.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/db_1/admin/shannura/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "shanma_cluster" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "shannura.shannura.com" has 1 instance(s).
  Instance "shannura1", status READY, has 1 handler(s) for this service...
Service "shannuraXDB.shannura.com" has 1 instance(s).
  Instance "shannura1", status READY, has 1 handler(s) for this service...
The command completed successfully
This is my TNS entry in the primary. 'STANDBY' is for standby. Ensure these entries are available on both nodes.
$<>  
[oracle@ol-alpha admin]$ cat tnsnames.ora
# tnsnames.ora.ol-alpha Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora.ol-alpha
# Generated by Oracle configuration tools.

SHANNURA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shannuracluster-scan.shannura.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = shannura.shannura.com)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol-alpha-dr.shannura.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
	  (UR = A)                              /* this is very important */
    )
  )
$<>  
[grid@ol-alpha admin]$ tnsping shannura

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 01-FEB-2017 05:34:50

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

Used parameter files:
/u01/app/12.1.0/grid/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = shannuracluster-scan.shannura.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = shannura.shannura.com)))
OK (10 msec)
Setting up the STANDBY server
I have already created another virtual machine running the same Oracle Linux 7.3. Oracle Restart is already up and running. Oracle Database Server binaries are also already installed under /u01/app/oracle/product/12.1.0/db_1
Ensure firewall is stopped and disabled:
$
[root@ol-alpha-dr ~]# systemctl stop firewalld.service
[root@ol-alpha-dr ~]# systemctl disable firewalld.service
$<>  
[grid@ol-alpha-dr ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG.dg
               ONLINE  ONLINE       ol-alpha-dr              STABLE
ora.FRADG.dg
               ONLINE  ONLINE       ol-alpha-dr              STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol-alpha-dr              STABLE
ora.asm
               ONLINE  ONLINE       ol-alpha-dr              Started,STABLE
ora.ons
               OFFLINE OFFLINE      ol-alpha-dr              STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       ol-alpha-dr              STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       ol-alpha-dr              STABLE
--------------------------------------------------------------------------------
Ensure following directory structures does exist:

/u01/app/oracle/admin/standby/adump
On the standby server, create parameter file 'initstandby.ora' containing just one line:

*.db_name='SHANNURA'
This will serve as the initialization file for the standby instance; the rest of the parameters will be populated automatically by the RMAN.
Create the password file in the node 1 and copy it across to node 2:
$<>  
[oracle@ol-alpha dbs]$ scp -p orapwshannura 172.168.190.102:/u01/app/oracle/product/12.1.0/db_1/dbs/
Also, copy the password file 'orapwshannura' from the primary server to the standby server and rename it as 'orapwstandby'.
I have setup the TNS as below so that I can get response from both servers:
$
[grid@ol-alpha-dr admin]$ cat tnsnames.ora
SHANNURA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shannuracluster-scan.shannura.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = shannura.shannura.com)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol-alpha-dr.shannura.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
Then startup the standby instance in NOMOUNT state:
$
$ ORACLE_SID=STANDBY
$ sqlplus / as sysdba
SQL>
SQL> startup nomount;
This will start the standby instance but mount nothing.
Note: Standby instance shouldn't be started using spfile, but pfile.
Now that all initial preparation is completed, it's time to call the all-powerful RMAN script that creates the standby database.
From the primary server, start RMAN and run the following script.
RMAN>
RMAN> connect target sys/sysadmin@shannura ;
connected to target database: SHANNURA (DBID=2095222274)

RMAN> connect auxiliary sys/sysadmin@standby ;
connected to auxiliary database: SHANNURA (not mounted)
RMAN><>  
RMN> run {
allocate channel p1 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database dorecover 
spfile 
parameter_value_convert 'shannura','standby','SHANNURA','STANDBY'
set db_name='phoenix'
set db_unique_name='standby'
set db_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set log_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set control_files='+DATADG','+FRADG'
set db_recovery_file_dest='+FRADG'
set db_recovery_file_dest_size='4G'
set fal_client='standby'
set fal_server='shannura'
set standby_file_management='AUTO'
set log_archive_config='DG_CONFIG=(shannura,standby)'
set log_archive_dest_2='service=shannura LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=shannura'
set log_archive_dest_state_2='defer'
set log_archive_format='standby_%t_%s_%r.arc'
;
sql channel p1 "alter system archive log current";
sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}

using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=267 instance=shannura1 device type=DISK

allocated channel: s1
channel s1: SID=173 device type=DISK

Starting Duplicate Db at 01-FEB-2017 22:11:06
current log archived

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATADG/SHANNURA/PASSWORD/pwdshannura.256.933618819' auxiliary format
 '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwshannura'   ;
   restore clone from service  'shannura' spfile to
 '/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora''";
}
executing Memory Script

Starting backup at 01-FEB-2017 22:11:06
Finished backup at 01-FEB-2017 22:11:07

-- ----------------------------
-- ----------------------------
-- PURPOSELY SKIPPED THE OUTPUT
-- ----------------------------
-- ----------------------------

sql statement: alter system set  log_archive_config =  ''DG_CONFIG=(shannura,standby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_state_2 =  ''defer'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_format =  ''standby_%t_%s_%r.arc'' comment= '''' scope=spfile

Oracle instance shut down

released channel: p1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2017 22:11:23
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

RMAN>
It took me a while to figure out what the issue was…
Note: Duplicating from an active database DOES NOT WORK when connecting to an CLUSTER/SCAN adress!
$
[grid@ol-alpha-dr admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol-alpha-dr.shannura.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
Solution:
Modify the listener with the SID list for RMAN duplication in Target,Update the Tnsnames.ora and try. For example, as shown below:
$
[grid@ol-alpha-dr admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol-alpha-dr.shannura.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (SID_NAME=standby)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
         )
        )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
Then retried the above duplicate one more time (remember to remove spfile from the standby and startup the server in nomount once again).
This time, encountered another error:
$
connected to auxiliary database (not started)
released channel: p1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2017 22:44:05
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00439: feature not enabled: Real Application Clusters
This error happens when the primary database is in RAC and the standby database running on single instance. During the duplicate the RMAN will read the source control file, its RAC parameters and try to duplicate RAC as well which is not possible as the standby is running on single instance.
So, I'm going to retry with 'SET CLUSTER_DATABASE='FALSE'' in RMAN duplicate command.

run {
allocate channel p1 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database dorecover 
spfile 
parameter_value_convert 'shannura','standby','SHANNURA','STANDBY'
set cluster_database='false'
set db_name='phoenix'
set db_unique_name='standby'
set db_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set log_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set control_files='+DATADG','+FRADG'
set db_recovery_file_dest='+FRADG'
set db_recovery_file_dest_size='4G'
set fal_client='standby'
set fal_server='shannura'
set standby_file_management='AUTO'
set log_archive_config='DG_CONFIG=(shannura,standby)'
set log_archive_dest_2='service=shannura LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=shannura'
set log_archive_dest_state_2='defer'
set log_archive_format='standby_%t_%s_%r.arc'
;
sql channel p1 "alter system archive log current";
sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}
Oops! Another error to deal with:

allocated channel: s1
channel s1: SID=11 device type=DISK
released channel: p1
released channel: s1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/01/2017 22:57:36
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created
ORA-00202: control file: '+DATADG'
ORA-17502: ksfdcre:4 Failed to create file +DATADG
ORA-15001: diskgroup "DATADG" does not exist or is not mounted
ORA-01017: invalid username/password; logon denied
This is because, oracle user in the standby server wasn't part of the 'asmdba' group to access the asm.
$
[root@ol-alpha-dr ~]# usermod -a -G asmdba oracle
One final attempt, hopefully it should run as expected.
RMAN><>  
RMAN> 
run {
allocate channel p1 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database dorecover
spfile
parameter_value_convert 'shannura','standby','SHANNURA','STANDBY'
set cluster_database='false'
set db_name='phoenix'
set db_unique_name='standby'
set db_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set log_file_name_convert='shannura','standby','SHANNURA','STANDBY'
set control_files='+DATADG','+FRADG'
set db_recovery_file_dest='+FRADG'
set db_recovery_file_dest_size='4G'
set fal_client='standby'
set fal_server='shannura'
set standby_file_management='AUTO'
set log_archive_config='DG_CONFIG=(shannura,standby)'
set log_archive_dest_2='service=shannura LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=shannura'
set log_archive_dest_state_2='defer'
set log_archive_format='standby_%t_%s_%r.arc'
;
sql channel p1 "alter system archive log current";
sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}

using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=53 instance=shannura1 device type=DISK

allocated channel: s1
channel s1: SID=173 device type=DISK

Starting Duplicate Db at 01-FEB-2017 23:36:23
current log archived

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATADG/SHANNURA/PASSWORD/pwdshannura.256.933618819' auxiliary format
 '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwshannura'   ;
   restore clone from service  'shannura' spfile to
 '/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora''";
}
executing Memory Script

Starting backup at 01-FEB-2017 23:36:23
Finished backup at 01-FEB-2017 23:36:24

Starting restore at 01-FEB-2017 23:36:24

channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora
channel s1: restore complete, elapsed time: 00:00:02
Finished restore at 01-FEB-2017 23:36:26

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfileshannura.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/standby/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=USE_DB_RECOVERY_FILE_DEST arch reopen=60 max_failure=0 mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  cluster_database =
 false comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''standby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''shannura'', ''standby'', ''SHANNURA'', ''STANDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''shannura'', ''standby'', ''SHANNURA'', ''STANDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATADG'', ''+FRADG'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
 ''+FRADG'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest_size =
 4G comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''standby'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''shannura'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''DG_CONFIG=(shannura,standby)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=shannura LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=shannura'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''DG_CONFIG=(shannura,standby)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_state_2 =
 ''defer'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_format =
 ''standby_%t_%s_%r.arc'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/standby/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=standbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=USE_DB_RECOVERY_FILE_DEST arch reopen=60 max_failure=0 mandatory valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'' comment= '''' scope=spfile

sql statement: alter system set  cluster_database =  false comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''standby'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''shannura'', ''standby'', ''SHANNURA'', ''STANDBY'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''shannura'', ''standby'', ''SHANNURA'', ''STANDBY'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATADG'', ''+FRADG'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''+FRADG'' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest_size =  4G comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''standby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''shannura'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''DG_CONFIG=(shannura,standby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=shannura LGWR ASYNC NOAFFIRM max_failure=10 max_connections=2 reopen=400 valid_for=(online_logfiles,primary_role) db_unique_name=shannura'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''DG_CONFIG=(shannura,standby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_state_2 =  ''defer'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_format =  ''standby_%t_%s_%r.arc'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1644167168 bytes

Fixed Size                     2925024 bytes
Variable Size               1056968224 bytes
Database Buffers             570425344 bytes
Redo Buffers                  13848576 bytes
allocated channel: s1
channel s1: SID=14 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATADG/STANDBY/CONTROLFILE/current.256.934846609'', ''+FRADG/STANDBY/CONTROLFILE/current.257.934846609'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone from service  'shannura' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATADG/STANDBY/CONTROLFILE/current.256.934846609'', ''+FRADG/STANDBY/CONTROLFILE/current.257.934846609'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 01-FEB-2017 23:36:49

channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: restoring control file
channel s1: restore complete, elapsed time: 00:00:02
output file name=+DATADG/STANDBY/CONTROLFILE/current.258.934846611
output file name=+FRADG/STANDBY/CONTROLFILE/current.259.934846611
Finished restore at 01-FEB-2017 23:36:52

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATADG";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATADG";
   set newname for datafile  2 to
 "+DATADG";
   set newname for datafile  3 to
 "+DATADG";
   set newname for datafile  4 to
 "+DATADG";
   set newname for datafile  5 to
 "+DATADG";
   set newname for datafile  6 to
 "+DATADG";
   restore
   from service  'shannura'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATADG in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-FEB-2017 23:36:59

channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00001 to +DATADG
channel s1: restore complete, elapsed time: 00:00:46
channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00002 to +DATADG
channel s1: restore complete, elapsed time: 00:00:03
channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00003 to +DATADG
channel s1: restore complete, elapsed time: 00:00:39
channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00004 to +DATADG
channel s1: restore complete, elapsed time: 00:00:08
channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00005 to +DATADG
channel s1: restore complete, elapsed time: 00:00:55
channel s1: starting datafile backup set restore
channel s1: using network backup set from service shannura
channel s1: specifying datafile(s) to restore from backup set
channel s1: restoring datafile 00006 to +DATADG
channel s1: restore complete, elapsed time: 00:00:01
Finished restore at 01-FEB-2017 23:39:31

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'shannura'
           archivelog from scn  2046530;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 01-FEB-2017 23:39:35

channel s1: starting archived log restore to default destination
channel s1: using network backup set from service shannura
channel s1: restoring archived log
archived log thread=1 sequence=39
channel s1: restore complete, elapsed time: 00:00:01
channel s1: starting archived log restore to default destination
channel s1: using network backup set from service shannura
channel s1: restoring archived log
archived log thread=1 sequence=40
channel s1: restore complete, elapsed time: 00:00:01
Finished restore at 01-FEB-2017 23:39:37

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/system.259.934846623
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/undotbs2.260.934846667
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/sysaux.261.934846673
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/undotbs1.262.934846709
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/example.263.934846717
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=934846778 file name=+DATADG/STANDBY/DATAFILE/users.264.934846771

contents of Memory Script:
{
   set until scn  2047041;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-FEB-2017 23:39:38

starting media recovery

archived log for thread 1 with sequence 39 is already on disk as file +FRADG/STANDBY/ARCHIVELOG/2017_02_01/thread_1_seq_39.260.934846775
archived log for thread 1 with sequence 40 is already on disk as file +FRADG/STANDBY/ARCHIVELOG/2017_02_01/thread_1_seq_40.261.934846777
archived log file name=+FRADG/STANDBY/ARCHIVELOG/2017_02_01/thread_1_seq_39.260.934846775 thread=1 sequence=39
archived log file name=+FRADG/STANDBY/ARCHIVELOG/2017_02_01/thread_1_seq_40.261.934846777 thread=1 sequence=40
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-FEB-2017 23:39:41
Finished Duplicate Db at 01-FEB-2017 23:39:54

sql statement: alter system archive log current

sql statement: alter database recover managed standby database using current logfile disconnect
released channel: p1
released channel: s1

RMAN>
So far looks good, however, I tried to logon to the standby database, I got some I/O error whatever dynamic view I was trying to execute, . I aborted the instance and tried to mount the database.
$
[oracle@ol-alpha-dr ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 1 23:58:14 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1056968224 bytes
Database Buffers          570425344 bytes
Redo Buffers               13848576 bytes
ORA-03113: end-of-file on communication channel
Process ID: 15097
Session ID: 237 Serial number: 18398
Alert log indicated that some process is terminating the ASM.

RBAL (ospid: 15080): terminating the instance due to error 27625
I realized that it is an ownership issue. Earlier, I added asmadmin group to oracle user. Perhaps it changed the group 'asmadmin' to 'oinstall' for this oracle binary located under $ORACLE_HOME/bin folder. So now I need to change the group back again to ASMADMIN and give appropriate permission also.
I aborted the database instance one more time and stopped all the Oracle Restart resources (as grid user) before proceeding with this fix.
$
[root@ol-alpha-dr bin]# ls -l oracle
-rwsr-s--x. 1 oracle oinstall 323649840 Jan 31 21:15 oracle

[root@ol-alpha-dr bin]# chgrp asmadmin oracle
[root@ol-alpha-dr bin]# chmod 6751 oracle

[root@ol-alpha-dr bin]# ls -l oracle
-rwsr-s--x. 1 oracle asmadmin 323649840 Jan 31 21:15 oracle
Now, I will restart the crs resources and try to open the database.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1056968224 bytes
Database Buffers          570425344 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
One last step we need to do in the primary to enable the sync.
SQL>
SQL> alter system set log_archive_dest_state_2=enable sid='*' scope=BOTH ;
On the primary database (node1):
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     55
Next log sequence to archive   56
Current log sequence           56
Now, I'm going to do a couple of log switches on the primary:
SQL>
SQL> alter system archive log current;
SQL> /
SQL> /
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   59
Current log sequence           59
Let's check the V$MANAGED_STANDBY on the standby:
$<>  
SSQL> @_managed_standby

// 02-feb-2017 21:29:32, sys@STANDBY (237) //
// *** PHYSICAL STANDBY DATABASE PROCESS INFORMATION *** //


PROCESS                        PRIMARY DB PROCESS             STATUS                     THREAD#     SEQUENCE#        BLOCK#        BLOCKS    DELAY_MINS
============================== ============================== ==================== ============= ============= ============= ============= =============
ARCH                           ARCH                           CONNECTED                        0             0             0             0             0
ARCH                           ARCH                           CONNECTED                        0             0             0             0             0
ARCH                           ARCH                           CONNECTED                        0             0             0             0             0
ARCH                           ARCH                           CONNECTED                        0             0             0             0             0
ARCH                           ARCH                           CONNECTED                        0             0             0             0             0
MRP0                           N/A                            APPLYING_LOG                     1            59            37        102400             0
Active Data Guard
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open ;    /* this will open the database in READ ONLY mode */
Database altered.

SQL> alter database recover managed standby database disconnect from session ;
Database altered.
$<>  
SQL> @_database

// 02-feb-2017 21:23:10, sys@STANDBY (237) //
// *** DATABASE OVERVIEW *** //


NAME                      DB_UNIQUE_NAME       LOG_MODE     OPEN_MODE             DATABASE_ROLE        PROTECTION_MODE           CURRENT_SCN  ARCHLOG_CHANGE# FLASHBACK_ON    FORCE_LOGGING   GUARD_STATUS    CHARACTER SET   NATIONAL CHARACTER SET
========================= ==================== ============ ===================== ==================== ==================== ================ ================ =============== =============== =============== =============== =======================
SHANNURA (2095222274)     standby              ARCHIVELOG   READ ONLY WITH APPLY  PHYSICAL STANDBY     MAXIMUM PERFORMANCE           2085675          2085378 NO              YES             NONE            AL32UTF8        AL16UTF16


// *** INSTANCE(S) *** //

   INST_ID INSTANCE_NAME    DB_NAME   HOST_NAME                      DATABASE_ROLE        OPEN_MODE            DB_VERSION        LOGINS     DB UP TIME
========== ================ ========= ============================== ==================== ==================== ================= ========== ==============================
         1 standby         SHANNURA  ol-alpha-dr.shannura.com         PHYSICAL STANDBY     READ ONLY WITH APPLY 12.1.0.2.0        ALLOWED    02-FEB-2017 21:20:50
		 
Sync status between primary and standby:
On the primary:
$<>  
SQL> @_last

// 02-feb-2017 21:34:29, sys@SHANNURA1 (254) //
// *** LAST PRODUCTION SEQUENCE GENERATED *** //
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________


   THREAD#  SEQUENCE# FIRST_CHANGE#                       NEXT_CHANGE#
========== ========== =================================== ===================================
         2         33 2086531 ( 02-FEB-2017 21:28:50 )    2086545 ( 02-FEB-2017 21:28:53 )
         1         58 2086537 ( 02-FEB-2017 21:28:50 )    2086554 ( 02-FEB-2017 21:28:53 )


// *** ARCHIVED REDO LOG DESTINATIONS *** //

  DEST_ID DEST_NAME            STATUS               TYPE                 DATABASE_MODE             PROTECTION_MODE                DESTINATION          DB_UNIQUE_NAME       SRL        GAP_STATUS
========= ==================== ==================== ==================== ========================= ============================== ==================== ==================== ========== ======================
        1 LOG_ARCHIVE_DEST_1   VALID                LOCAL                OPEN                      MAXIMUM PERFORMANCE                                 shannura             NO
        2 LOG_ARCHIVE_DEST_2   VALID                PHYSICAL             OPEN_READ-ONLY            MAXIMUM PERFORMANCE            standby              standby             YES        NO GAP
		
On the standby:
$<>  
SQL> @_last_sync1

// 02-feb-2017 21:35:28, sys@STANDBY (237) //
// *** LAST SYNC STATUS *** //
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________


   THREAD# LAST SEQUENCE RECEIVED REGISTRAR   APPLIED   LAST SEQUENCE APPLIED       DIFF
========== ====================== =========== ========= ===================== ==========
         2                     33 RFS         YES                          33          0
         1                     58 RFS         IN-MEMORY                    58          0

Note:
If REGISTRAR=RFS and APPLIED=NO, then it is received not applied yet.
If REGISTRAR=RFS and APPLIED=YES, then it is applied and datafiles updated.
If REGISTRAR=RFS and APPLIED=IN-MEMORY, then it is applied in memory, but datafiles not updated.


   THREAD#              SEQUENCE#          FIRST_CHANGE#           NEXT_CHANGE# COMPLETION_TIME
========== ====================== ====================== ====================== =====================
         1                     58                2086537                2086554 02-FEB-2017 21:28:56
         2                     33                2086531                2086545 02-FEB-2017 21:28:54


// *** LAST REDO *** //

   THREAD#              SEQUENCE# STATUS              FIRST_CHANGE# FIRST_TIME                      LAST_CHANGE# LAST_TIME
========== ====================== ========== ====================== ===================== ====================== =====================
         1                     59 ACTIVE                    2086554 02-FEB-2017 21:28:56                 2087810 02-FEB-2017 21:35:28
         2                     34 ACTIVE                    2086545 02-FEB-2017 21:28:54                 2087810 02-FEB-2017 21:35:28

 
Creating a LOGICAL standby
We can easily convert a physical standby to a logical standby.
The standby database will need to get the data dictionary from somewhere. The dictionary information should be put into the redo stream that comes from the primary. So, on the primary database, issue the following to build the LogMiner tables for dictionary.
SQL>
SQL> begin
  2  dbms_logstdby.build;
  3  end;
  4  /
  PL/SQL procedure successfully completed.
On the standby database, stop the managed recover process:
SQL>
SQL> alter database recover managed standby database cancel;
Then issue this command to convert physical standby to logical:

SQL> alter database recover to logical standby <physical_standby_database> ;  
SQL>
SQL> alter database recover to logical standby standby;
If you didn't execute 'dbms_logstdby.build;' on the primary, the above command will wait since the dictionary information is not found. No worries, we can still execute that command now on the primary. Followed by, do a couple of log switches on the primary.
Now, back on the standby, shutdown the standby and startup in MOUNT state.
SQL>
SQL> shut immediate
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> alter database guard ALL;
SQL> alter database start logical standby apply immediate;
To stop SQL apply:
SQL>
SQL> alter database stop logical standby apply ;
Please write your comment if this article was useful.

ShanNura

/
You might want to read this:
Data Guard - RAC Physical Standby (12c)