Playing with Oracle Cloud – Creating a Hybrid DR Deployment

On this article, we will create a Hybrid Disaster Recovery (DR by using Data Guard) Oracle Database Cloud Service database deployment with a primary database on-premises and a standby database in the cloud. This can be used to implement an improve the high availability of your Database by taking advantage of the Cloud, and even to migrate your database from on-premises to the Cloud within minimum downtime (Basically keep using your primary on-premise database until ready to migrate, and then switch over to your database on the Cloud, and you are good to go).

Before we start, let’s take a look at some requirements for the on-premise database that would become the primary environment for this Hybrid DR implementation:

• The owner of the Oracle Database software (dbowner) must be the useroracle.
• The database must be in ARCHIVELOG mode.
• Enable FORCE LOGGING mode to prevent the loss of data when statements in NOLOGGING mode are executed on the primary database.
• Ensure the listener.ora file is configured for static service registration, a requirement for Oracle Data Guard.
• Ensure one of the following RPMs is installed, based on the Oracle Database release of your system:
  • Oracle Database 11g Release 2: oracle-rdbms-server-11gR2-preinstall
  • Oracle Database 12g Release 1: oracle-rdbms-server-12cR1-preinstall
  • You can use this command as the root user to verify that the RPM is installed:  # rpm -qa|grep oracle-rdbms-server
• Ensure that the /etc/hostsfile contains the IP address for your on-premises system, the hostname with a fully qualified domain name, and a short hostname
• The Oracle listener port and the Secure Shell (SSH) port must be open for remote access from the compute node.
• Have access to Oracle Cloud Database Cloud Service, if not, try it for free here.

This type of scenario can be done in two different ways:

1. Using Oracle Database Cloud Services (DBaaS), or
2. Using Oracle Compute Classic (IaaS).

If using DBaaS (Our choice for this scenario), a new Cloud Instance will be created with an Oracle Database deployed on it (We can pay for the Oracle Database License within the service or Bring our own License) – we will then proceed to drop this database instance to create the standby database instead. The other option is by using IaaS, with this option we will create a new Compute Instance as per our needs and then install Oracle RDBMS software from scratch (Oracle Database License will not be included, you need to bring your own database license).

Now, it is time to start the fun!

To start, we will connect to the Oracle Database Cloud Service console and click on the  [Create Instance] option.

At the Create Instance page please enter the following details:

• Instance Name: The name you want for your Oracle Cloud Instance (not the database instance).
• Service Level: Oracle Database Cloud Service
• Metering Frequency: Monthly if this instance will be all the time on, our Hourly if this instance would be for a short time or sporadically on.
• Software Release: The version of your primary database (on premises). At this case 11gR2
• Software Edition: Choose Enterprise Edition, unless you want to make use of Active Data Guard, in that case, choose Enterprise Edition Extreme Performance.
• Database Type: Choose Single Instance.

And then click [Next].

Now we need to enter the Oracle Instance details, as per below:

• DB Name: Enter any database name (remember we will drop this database later)
• Administration Password: Enter any password here.
• Usable Database Storage: Enter enough space to replicate your primary database (Database size x 3)
• SSH Public Key: Enter the SSH Public Key that will grant you a secure access to your instance (Details of how to generate and setup a SSH Key could be found here ).
• Backup Destination: Select None

When all information above is entered please click on [Next].

Please review all details then click [Create].

Your Cloud Instance is being created as you can see below.

When your instance is created, click on the newly created instance name and take note of the public IP (1), then click on the menu on the top right corner of your screen (2) and click on the menu option [Access Rules] (3).

At the Public port 1521 row, click on the menu option on the right corner and choose the [Enable] option.

A warning message would be displayed and please select Enable once again.

Now that we have enabled all access, let’s connect to our primary database and add the following lines to our tnsnames.ora file:

ORCL112_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =129.158.71.120)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL112) (UR = A) ) )

We can test the connectivity by doing a $tnsping ORCL112_STBY

Now, please connect to your newly created instance via SSH using the IP we previously took note and the SSH key uploaded when creating the Cloud instance, and connect to the oracle user as shown bellow.

$ ssh -i /Users/franciscomunozalvarez/Downloads/testfcose opc@129.158.71.120 $ sudo -s $ su – oracle

The next step is to connect to SQL*Plus and drop the database instance that was automatically created by Oracle Database Cloud Service.

$ sqlplus / as sysdba

SQL> startup force mount exclusive restrict;

SQL> drop database;

To be able to connect to the Cloud Instance from outside, we will need to configure SSH first. For this we will connect to the primary database server and when connected to the Oracle use, create a .ssh folder and generate an ssh key using the ssh-keygen utility as follows:

$ mkdir .ssh $ chmod 700 ~/.ssh $ cd .ssh $ ssh-keygen

sss

Now we would need to copy the source of id_rsa.pub file (at /home/oracle/.ssh) and append it to the /home/oracle/.ssh/authorized_keys file in the cloud instance.

Then test the ssh connection between the primary server and the standby one at the Oracle Cloud, if all works well it will not ask you for any password.

$ ssh 129.158.71.120

We will now at the standby server create all folders required for the database duplication.

[oracle@dataguard ~]$ mkdir -p admin/ORCL112/adump
[oracle@dataguard ~]$ mkdir -p oradata/ORCL112
[oracle@dataguard ~]$ mkdir flash_recovery_area
[oracle@dataguard ~]$ mkdir arch

Now is time to create the parameter file for the standby database. At /home/oracle do the following:

$ vi pfile.ora

*.audit_file_dest='/home/oracle/admin/ORCL112/adump'
*.control_files='/home/oracle/oradata/ORCL112/control01.ctl'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL112/','/home/oracle/oradata/ORCL112/'
*.db_name='PROD'
*.db_unique_name='ORCL112'
*.db_recovery_file_dest='/home/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=5g
*.log_archive_dest_1='location=/home/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORCL112'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL112/',’/home/oracle/oradata/ORCL112/'
*.compatible=’11.2.0.4.0′
*.sga_target=300M
*.diagnostic_dest='/u01/app/oracle/diag'

Note: For the file and redo conversion please check the original location at your primary database by doing:

SQL> select name from v$datafile;
SQL> select * from V$logfile;

Then start the database by issuing the following commands (we will then create the spfile from pfile and restart the instance again):

$ sqlplus / as sysdba SQL> startup nomount pfile=pfile.ora SQL> create spfile from pfile SQL> shutdown immediate SQL> startup nomunt

Finally, we will now create the password file for the standby database as follows:

$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdORCL112 password=oracle entries=5

Also, make sure you rename the following files:

$ $ORACLE_HOME/network/admin/sqlnet.ora $ORACLE_HOME/network/admin/sqlnet.ora.old

$ $ORACLE_HOME/network/admin/listener.orapre_vncr_config $ORACLE_HOME/network/admin/listener.orapre_vncr_config.old

$ ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.old

Then create a new listener.ora as follows:

[oracle@dataguard admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/product/11.2.0/dbhome_1\/in\oraclr11.dll")
  )
  (SID_DESC =
  (SID_NAME=ORCL112)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard.compute-587848097.oraclecloud.internal)(PORT = 1521))
  )
)

Now, at the primary database:

$ rman target sys/oracle@prod auxiliary sys/oracle@orcl112_stby

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 9 06:36:03 2018

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

connected to target database: ORCL112 (DBID=3534260988)

connected to auxiliary database: ORCL112 (not mounted)

RMAN>

Now is time to perform the database duplication by executing the following RMAN command:

RMAN> duplicate target database for standby from active database;;

Starting Duplicate Db at 09-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
 backup as copy reuse
 targetfile '/u01/app/oracle/product/11.2.0/db1/dbs/orapwORCL112' auxiliary format
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORCL112' ;
}
executing Memory Script

Starting backup at 09-APR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Finished backup at 09-APR-18

contents of Memory Script:
{
 backup as copy current controlfile for standby auxiliary format '/home/oracle/oradata/ORCL112/control1.ctl';
}
executing Memory Script

Starting backup at 09-APR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_ORCL112.f tag=TAG20180409T073109 RECID=9 STAMP=972977474
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 09-APR-18

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

sql statement: alter database mount standby database

contents of Memory Script:
{
 set newname for tempfile 1 to
 "/home/oracle/oradata/ORCL112/temp01.dbf";
 switch clone tempfile all;
 set newname for datafile 1 to
 "/home/oracle/oradata/ORCL112/system01.dbf";
 set newname for datafile 2 to
 "/home/oracle/oradata/ORCL112/sysaux01.dbf";
 set newname for datafile 3 to
 "/home/oracle/oradata/ORCL112/undotbs01.dbf";
 set newname for datafile 4 to
 "/home/oracle/oradata/ORCL112/users01.dbf";
 set newname for datafile 5 to
 "/home/oracle/oradata/ORCL112/example01.dbf";
 backup as copy reuse
 datafile 1 auxiliary format
 "/home/oracle/oradata/ORCL112/system01.dbf" datafile
 2 auxiliary format
 "/home/oracle/oradata/ORCL112/sysaux01.dbf" datafile
 3 auxiliary format
 "/home/oracle/oradata/ORCL112/undotbs01.dbf" datafile
 4 auxiliary format
 "/home/oracle/oradata/ORCL112/users01.dbf" datafile
 5 auxiliary format
 "/home/oracle/oradata/ORCL112/example01.dbf" ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/oradata/ORCL112/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 09-APR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL112/system01.dbf
output file name=/home/oracle/oradata/ORCL112/system01.dbf tag=TAG20180409T073140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL112/sysaux01.dbf
output file name=/home/oracle/oradata/ORCL112/sysaux01.dbf tag=TAG20180409T073140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL112/example01.dbf
output file name=/home/oracle/oradata/ORCL112/example01.dbf tag=TAG20180409T073140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL112/undotbs01.dbf
output file name=/home/oracle/oradata/ORCL112/undotbs01.dbf tag=TAG20180409T073140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL112/users01.dbf
output file name=/home/oracle/oradata/ORCL112/users01.dbf tag=TAG20180409T073140
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 09-APR-18

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=972992212 file name=/home/oracle/oradata/ORCL112/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=972992212 file name=/home/oracle/oradata/ORCL112/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=972992213 file name=/home/oracle/oradata/ORCL112/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=972992213 file name=/home/oracle/oradata/ORCL112/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=972992214 file name=/home/oracle/oradata/ORCL112/example01.dbf
Finished Duplicate Db at 09-APR-18

RMAN>

Now we need to set the LOG_ARCHIVE_DEST_2 parameter on the on-premises database and specify the instance running on the cloud instance.

$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORCL112_STBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL112';

At the Oracle Cloud Instance please execute the following commands:

$ sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Your standby database in the cloud is now ready!

Enjoy! If you liked this article please share it with all your peers and the community. Remember, knowledge is only valuable if shared!

Regards,
Francisco Munoz Alvarez
Oracle ACE DIrector