How to Create a Physical Standby Database using Backup Pieces

In this article we will learn How to create a new Physical Standby using Backup Set and also DUPLICATE in RMAN, this is a fast way to create Physical Standby when to create the Standby using ACTIVE DATAGUARD is too expensive for our network. In that case a good way is to create the backup in Primary Server and transfer the backup via unattach the mounted disk from the Primary Server and attach it and mount it in the Physical Server and then creating a new and fresh Physical Standby using those Backup Pieces.  

For this article I will use two servers:

1. db12102 –  Primary Server
2. db12102s – Physical Standby Server

I am using Oracle Database 12.1.0.2 for this article. 

Once we complete this article we will have a configuration like the following:

On primary site:

Creating a Backup with RMAN: Let’s start from the beginning, Creating a Backup with RMAN of our production Primary Database which I am suppose it is in Archivelog mode. 

RMAN> backup database format '/data/Backup_%U' include current controlfile;

Create pfile pfile: I am creating pfile because I have to modify the parameter db_unique_name in the Standby Instance,
 so it is easier for me to use a pfile. 

SQL> create pfile='/data/pfile.txt' from spfile;

File created.

Enable Broker: This is a optional, however I am planning to use Broker for this Data Guard Configuration so I have turn it on. 

SQL> alter system set dg_broker_start=true;

System altered.

Configuring Oracle Network: Oracle network is very important when we are configuring Standby Databases, since Oracle send redo logs relying on this configurations. We have to set properly all the entries in our tnsnames.ora with both connection descriptions (Primary and Standby) and also it is recommended to create a static service for role operations with Broker. 

I added the following two entries to my tnsnames.ora file (in both sites, Primary and Standby):

[oracle@db12102s data]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
db1 =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = db12102)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = db1_dg)
     )
   )
db1s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db12102s)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =db1s_dg)
      (UR = A)
    )
  )

I added a static service as it is required for role operations with broker:

[grid@db12102 ~]$ cat /u01/app/grid/product/12.1.0/grid/network/admin/listener.ora


SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = db1_dg)
   (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
   (SID_NAME = db1)
  )
 )

[grid@db12102 ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-SEP-2016 23:21:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db12102)(PORT=1521)))
Services Summary...
Service "db1_dg" has 1 instance(s).
  Instance "db1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
        LOCAL SERVER
The command completed successfully
[grid@db12102 ~]$

Once we have our tnsnames.ora and listener.ora well configured, we can proceed. Remember to reboot the listener when you perform changes on it. 

Transfering the Backup Pieces to Standby Server: And now the last step in the Primary Server, transfering the backup pieces to the Standby Server. I am using scp for this example, however as I said, if you have any other storage solutions to transfer all these files you can use it. 

[oracle@db12102 data]$ scp Backup_i* oracle@db12102s:/data
The authenticity of host '192.168.56.101 (192.168.56.101)' can't be established.
RSA key fingerprint is ef:5b:7b:4f:d1:8d:fd:a4:76:f3:88:4b:d0:8e:41:7b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.101' (RSA) to the list of known hosts.
oracle@192.168.56.101's password: 
Backup_i1rgqbnr_1_1 100% 1205MB 33.5MB/s 00:36 
Backup_i2rgqboa_1_1 100% 14MB 13.6MB/s 00:01

On Standby site:

Verifying tha the Backup Pieces are available in Standby Server: Firstable let’s ensure the Backup Pieces are in the Standby Server with the right permissions and ready to be used. 

[oracle@db12102s ~]$ ls -ltr /data
total 1696700
drwx------ 2 root root 16384 Jan 7 2016 lost+found
drwxr-xr-x 4 oracle oinstall 4096 May 29 04:07 db1
-rw-r----- 1 oracle oinstall 1263550464 Sep 26 22:36 Backup_i1rgqbnr_1_1
-rw-r----- 1 oracle oinstall 14286848 Sep 26 22:36 Backup_i2rgqboa_1_1
[oracle@db12102s ~]$

Configuring Oracle Network: We have to configure tnsnames.ora in Standby site as well including connection descriptions for Primary and Standby, since it is the same, I just copied the entries from the tnsnames.ora in the Primary Server, that’s why I am not including again that info. Here in Standby Server we have to configure also a static service if we want to use Role operations with Broker like Switchovers. 

I have added a static service in Standby Site as well for role operations with Broker:

[grid@db12102s ~]$ cat /u01/app/grid/product/12.1.0/grid/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = db1s_dg)
    (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    (SID_NAME = db1s)
  )
 )

[grid@db12102s ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-SEP-2016 23:44:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db12102s)(PORT=1521)))
Services Summary...
Service "db1s_dg" has 1 instance(s).
Instance "db1", status UNKNOWN, has 1 handler(s) for this service...
  Handler(s):
    "DEDICATED" established:0 refused:0
      LOCAL SERVER
The command completed successfully
[grid@db12102s ~]$

Transfering the Password file from Primary Site: This step is very important, since broker connects to a database that is not open (Physical Standby  Database) we have to use password file, this is useful as well when we are doing role operations like Switchover, because Oracle has to connect and start the instance up for the current Physical Standby whenever a switchover is performed. 

[oracle@db12102s ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
[oracle@db12102s ~]$

[oracle@db12102s data]$ scp oracle@db12102:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdb1$ORACLE_HOME/dbs/orapwdb1s
oracle@db12102's password: 
orapwdb1 100% 7680 7.5KB/s 00:00 
[oracle@db12102s data]$

Transfering the pfile from Primary Site: And now lets bring the pfile we created in the Primary Site in order to use it to start the Standby Instance.

[oracle@db12102s ~]$ scp oracle@db12102:/data/pfile.txt /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
The authenticity of host 'db12102 (192.168.56.102)' can't be established.
RSA key fingerprint is ef:5b:7b:4f:d1:8d:fd:a4:76:f3:88:4b:d0:8e:41:7b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'db12102,192.168.56.102' (RSA) to the list of known hosts.
oracle@db12102's password: 
pfile.txt 100% 1096 1.1KB/s 00:00 
[oracle@db12102s ~]$

This is the time when we modify the parameter db_unique_name with a different name than Primary Instance. 

[oracle@db12102s ~]$ cat /u01/app/oracle/product/12.1.0/dbhome_1/dbs/pfile.txt|grep name
*.db_name='db1'
*.db_unique_name='db1s'
[oracle@db12102s ~]$

Starting the new instance using the pfile we have modified:

[oracle@db12102s ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/pfile.txt';
ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
SQL> exit

Enabling the Broker in the Standby Instance: As I said this step is optional, but I am planning to use Broker so I have to enable it. 

SQL> alter system set dg_broker_start=true;

System altered.

Creating the Physical Standby Database using Backup Pieces: And here is where the magic appears, We use DUPLICATE DATABASE but using Backup Pieces, all what we have to specify is where the backup is located and RMAN will do all the work for us. Please note also that we are connecting to the Standby Instance as “auxiliary”. We usually use “target” when we are performing an ACTIVE DUPLICATE which is not the case of this article. 

[oracle@db12102s data]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 26 22:53:37 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: DB1 (not mounted)

RMAN> duplicate database 'db1' for standby backup location '/data';

Starting Duplicate Db at 26-SEP-16

contents of Memory Script:
{
restore clone standby controlfile from '/data/Backup_i2rgqboa_1_1';
}
executing Memory Script

Starting restore at 26-SEP-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/db1/DB1/controlfile/o1_mf_cnnz809t_.ctl
output file name=/data/db1/DB1/controlfile/o1_mf_cnnz80by_.ctl
Finished restore at 26-SEP-16

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

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/db1/DB1S/datafile/o1_mf_temp_%u_.tmp 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

executing command: SET NEWNAME

Starting restore at 26-SEP-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/db1/DB1S/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/db1/DB1S/datafile/o1_mf_tbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/db1/DB1S/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/db1/DB1S/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/db1/DB1S/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data/db1/DB1S/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/db1/DB1S/datafile/o1_mf_thebigfi_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /data/Backup_i1rgqbnr_1_1
channel ORA_AUX_DISK_1: piece handle=/data/Backup_i1rgqbnr_1_1 tag=TAG20160926T223435
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 26-SEP-16

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_system_cymqsxd2_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_tbs1_cymqsxd7_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_sysaux_cymqsxd4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_undotbs1_cymqsxdc_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_users_cymqsxd9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_users_cymqsxdk_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=923612044 file name=/data/db1/DB1S/datafile/o1_mf_thebigfi_cymqsxdm_.dbf
Finished Duplicate Db at 26-SEP-16

RMAN>

Verifying the new Physical Standby Database: Once the duplicate completes we can verify the status of the database.

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1       MOUNTED              PHYSICAL STANDBY

SQL>

Best Practices for Redo Logs:

Oracle recommends to create the number of Standby Redo Logs = number of Redo Log Groups + 1. Since my Primary Database has only 3 redo log groups I will create 4 Standby Redo Log Groups. The same number should be created in Primary Site in case it becomes Physical Standby after a planned Switchover. In order to add the standby logs we have to stop the redo apply, once the standby logs are created we have to start again the redo apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

Database altered.

Creating the Broker configuration: And now it’s time to let the Broker manages our Physical Standby…

DGMGRL> CREATE CONFIGURATION 'NuvolaConfig' AS PRIMARY DATABASE IS 'db1' CONNECT IDENTIFIER IS db1;
Configuration "NuvolaConfig" created with primary database "db1"
DGMGRL>

DGMGRL> ADD DATABASE 'db1s' AS CONNECT IDENTIFIER IS db1s;
Database "db1s" added
DGMGRL>

DGMGRL> enable configuration;
Enabled.


DGMGRL> show configuration;

Configuration - NuvolaConfig

Protection Mode: MaxPerformance
Members:
db1 - Primary database
db1s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 29 seconds ago)

DGMGRL>

As a double Check you can use the function "validate database":

DGMGRL> validate database 'db1';

  Database Role: Primary database

  Ready for Switchover: Yes

  Flashback Database Status:
    db1: Off

DGMGRL> validate database 'db1s';

  Database Role: Physical standby database
  Primary Database: db1

  Ready for Switchover: Yes
  Ready for Failover: Yes (Primary Running)

  Flashback Database Status:
    db1: Off
    db1s: Off

DGMGRL>