Create Physical Standby Database With Rman Backup of Primary

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:

db12102 - Primary Server
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:

redoapply.gif-640x480

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

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

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>

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor's Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle's website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book "Oracle Database 12cR2 Multitenant - Oracle Press" and he is co-author of the book "Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press". He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

Oracle Database Cloud Administrator Certified Associate
Oracle Database Cloud Administrator Certified Professional
Oracle Database Cloud Service Operations Certified Associate
Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
Oracle Database 12c Maximum Availability Architecture Certified Master
Oracle Database 12c: Data Guard Administrator
Oracle Database 12c Administrator Certified Master (OCM 12c)
Oracle Real Application Clusters 12c Certified Implementation Specialist
Oracle Database 11g Administrator Certified Master (OCM 11g)
Oracle Database 12c Administrator Certified Professional (OCP 12c)
Oracle RAC 11g and Grid Infraestructure Administrator
Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
Oracle Database 11g Administrator Certified Professional (OCP 11g)
Oracle Linux Certified Implementation Specialist
Oracle Database 11g Administrator Certified Associate

Create Physical Standby Database With Rman Backup of Primary

Source: https://blog.toadworld.com/how-to-create-a-physical-standby-database-using-backup-pieces

Create Physical Standby Database With Rman Backup of Primary

How to Create a Physical Standby Database using Backup Pieces

On primary site:

On Standby site:

0 Response to "Create Physical Standby Database With Rman Backup of Primary"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel