Support for Oracle Cloud

Dbvisit Replicate works with Oracle Cloud.

Dbvisit Replicate can be used with the Oracle Database Cloud - Database as a Service - with either the pre-installed or the Virtual Image options. 
This can be on-premise to cloud, cloud to cloud, or cloud back to on-premise configurations.

In this article we focus on configuring an Oracle to Oracle replication – powered by Dbvisit Replicate.
This software ensures that the changes made in our local “on-premise” Oracle database, for the schema/table subsets we are interested in, are synchronized in real time with an Oracle database running in the Oracle Cloud.

Here are some of the key specifications for the test (on-premise) system used in this example:

Configuring Dbvisit Replicate

Once your new Oracle Cloud account has been created, and a new Oracle Cloud Database Service called “dbvrep-se-two” provisioned, then you should generate the keys for the SSH communication channel. From this point, we need to configure settings on both the on-premise and Oracle Cloud servers to ensure they can talk to each other, and that the databases can be accessed from either side. Following on from that, it will be a matter of checking that all the Dbvisit Replicate prerequisites have been completed, before actually installing and configuring the application – and conducting some basic test loads.

1. Add an entry to the hosts file on the source server. This should point to the Oracle Cloud VM. On *nix this is found under /etc/hosts, and something like C:\Windows\System32\Drivers\etc\hosts on Windows. This should be done as root/Administrator. In the example below, you can see that I have paired the IP for the Oracle Cloud VM (exposed on the Services page for the Database service) with the name that I allocated to that VM during the service creation process: 

> oracle@dbvlin828[/home/oracle]: cat /etc/hosts 
127.0.0.1         localhost.localdomain   localhost 
::1               localhost6.localdomain6 localhost6  
129.191.2.40      dbvrep-se-two

2. Establish SSH connectivity from the source server. As instructed, this is performed by running the command below, from my source (on-premise) server, making sure you remember the password you entered earlier for the SSH public key!

$ ssh -i private-key-file user-name@node-ip-address

And the interaction which results looks like this:

oracle@dbvlin828[/home/oracle]: ssh -i /home/oracle/.ssh/oraclecloud oracle@dbvrep-se-two 
The authenticity of host 'dbvrep-se-two (129.191.2.40)' can't be established. 
RSA key fingerprint is 80:e2:b5:fa:2e:2f:71:f3:fd:bf:87:4f:31:06:4b:60. 
Are you sure you want to continue connecting (yes/no)? yes 
Warning: Permanently added 'dbvrep-se-two,129.191.2.40' (RSA) to the list of known hosts. 
Enter passphrase for key '/home/oracle/.ssh/oraclecloud': 
Authorized uses only. All activity may be monitored and reported. 
[oracle@dbvrep-se-two ~]$

One point worth raising again about Oracle Cloud VM connectivity, is that the user oracle is provided to perform most operations, but does not have root access. The user opc does, however, (“sudo -s” once logged on) and so can be used to perform operations like backup and patching on the VM which require this.

3. Add an entry to the Oracle Cloud VM hosts file. Once you have logged onto the Oracle Cloud server (dbvrep-se-two in my case) you can then add an entry, pointing back to your on-premise server, in the machine’s /etc/hosts file. To do so, log on as opc and sudo to root:

Then using VI (or some other text editor), add a line for the on-premise server (dbvlin828 – IP has been altered):

127.0.0.1             localhost localhost.localdomain localhost4 localhost4.localdomain4 
::1                   localhost localhost.localdomain localhost6 localhost6.localdomain6 
10.196.248.26         dbvrep-se-two.compute-nzdbvisits.oraclecloud.internal dbvrep-se-two 
117.22.99.981         dbvlin828

Once saved you should be able to ping this named source server from the Oracle Cloud VM, i.e.:

> ping dbvlin828

4. Configure database connectivity on the source server. Next, we need to add an entry to the tnsnames.ora file in $ORACLE_HOME/network/admin (standard location) of our source database (which is in my case named “eleven”, running on dbvlin828), pointing to the listener on the Oracle Cloud VM by which we can establish a connection to the database service there. The name of this is ORCLB (which I specified back in the service creation Details screen) and should look something like the following. Note that the value for SERVICE_NAME can be found on the Oracle Cloud Database Service instance details screen:

ORCLB = 
  (DESCRIPTION =    
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvrep-se-two)(PORT = 1521))    
      (CONNECT_DATA =      
        (SERVER = DEDICATED)      
        (SERVICE_NAME = ORCLB.nzdbvisits.oraclecloud.internal)    
      ) 
  )

It probably goes without saying but don’t forget to ensure that any ports used for connecting/receiving connections are open on your source server, so this should be 1521 (default) for Oracle listener connections, and 7901 and 7902 for Dbvisit Replicate…but more on that later.
You will also need to confirm that you can establish a connection to the local database running on the source server via a TNS listener connection, and a quick test for this (beyond actually trying to establish a connection) is to run the TNSPING utility, as follows:

5. Unblock the Oracle Net Listener port. By default the listener port (1521) on the Oracle Cloud Database Service VM is blocked when first created, so in order to TNSPING or establish a connection to it from our source server we need to unblock it. The details for this can be referenced under “Enabling Access to a Compute Node Port” but, in short, we need to DISABLE the ora_p2_dblistener Security Rule, which controls access to port 1521, the port used by SQL*Net. This is as simple as locating the rule in the Services Console, under Oracle Compute Cloud Service > Network > Security Rules – and then setting it to Enabled (to allow communications on this port) and saving this with Update:

Once this has been done, we should now be able to TNSPING our target Oracle Cloud Database Service from the source server:

If this is successful, then we can go ahead and try to establish a connection proper, via SQL*Plus using the SYSTEM database account:

6. Configure database connectivity on the source server. Once the source server has been configured, we need to make sure that everything works “back the other way” – that is, from our Oracle Cloud VM to our source database. So we need to set up an entry in the Oracle Cloud VM tnsnames file to point to our source database, and also check that a TNS listener connection can be established to the Cloud database from the Cloud VM itself.

On the first task you should add an entry, similar to the following in structure but using your particular system details, to the tnsnames.ora file in $ORACLE_HOME/network/admin (default location) on the Oracle Cloud VM:

ELEVEN = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvlin828)(PORT = 1522)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = eleven) 
    ) 
  )

NB: I have used a non-standard port for this TNS listener connection port to my test server, but remember that 1521 is the default.
So once added, try using the TNSPING utility to check the connections to the local and remote databases, as per the following (which both complete successfully):

From there, you can try establishing a connection proper to the source database from the Oracle Cloud VM via SQL*Plus, using an account such as SYSTEM (which I am using here as an example as it is required in the upcoming Dbvisit Replicate configuration section): 

7. Dbvisit Replicate – prerequisite configuration checklist Once database and server connectivity have been established, we need to check that the key steps, outlined in the Dbvisit Replicate Installation Prerequisite Checklist, have been completed. Each of these are critical to the success of an implementation and so need to be verified.

At this point, there is one remaining task for us, and that is the Prerequisite Checklist item #10. This states that Dbvisit Replicate requires that ports 7901 and 7902 (defaults) are open for enabling communication between the application running on the source and target servers. This means we should:

1. Ensure these ports are open on our source system (server/firewall, etc)
2. We need to configure a Security rule which opens these ports, blocked by default, on the Oracle Cloud service VM. So once again we head to the Services Console > Oracle Compute Cloud Service > Network > Security Application list.

Click on “Create Security Application” and fill out the details as follows. You can add your own name and description as you see fit, but all this is required in order to open up TCP communication on ports 7901 and 7902. Make sure to save this with “Create”:

And once completed, we can see it successfully setup under the Security Applications list.
As it turns out, we also need to adjust the Inbound Policy for the VM to Permit rather than drop all inbound connections. This can be done by updating the Inbound Policy setting (set to Permit and then click Update) for the Security List for this machine, as follows:

8. Installing Dbvisit Replicate. We are now at the stage where all of the prerequisites have been covered off and so we are good to proceed with the installation of Dbvisit Replicate. This needs to be performed on both servers, then we can configure, instantiate and finally start up our replication.

Installation is actually really straight forward and can be broken down as follows, with the first step being that you need to get the software up onto both servers:

1. Download it from the Dbvisit website
2. Load it onto your source server
3. From there copy it to the Oracle Cloud VM. Alternatively you could establish another SSH connection to the Oracle Cloud VM (i.e. from your local machine/laptop) and upload from that machine. I chose this latter method and use the FileZilla FTP application for the transfer. Having this connection from my laptop is also beneficial if I want to download log files/support packages, etc, from that machine, later on for review.

Then RUN the install command. In my example which follows, I make use of the RPM method (there are also zip file and Windows installer options) which requires root access – so you need to connect as OPC to the Oracle Cloud VM (sudo -s) to do the same. 
The RPM install command takes this format:

> rpm -ivh (path_to_dbvisit_replicate_rpm_package)

Once this has been run successfully, you can invoke the “dbvrep” command to test out running the application. In my example, below, you can see that we are running version 2.7.18 – the latest production release:

As a logical next, you should then create a “home” directory for your Dbvisit Replicate installation. So on both servers, create a “home” directory for the replication configuration, as the oracle user. Here I have created one named dbvrep-se-two – the name of my replication configuration itself:

9. Replication configuration. The good news is that, at this point, all the hard work has been done and from here there is nothing specific or unusual about connecting Dbvisit Replicate up to an Oracle Database running in the Oracle Cloud. From the perspective of our application this is basically a standard Oracle to Oracle configuration and so the standard step by step details should be worked through.

For the purposes of this replication test, I have created a user called HR in both the source and target databases, and the following empty tables (HR.TEST1 and HR.TEST2) on both sides:

create table HR.TEST1 ( 
  test_number NUMBER, 
  test_float FLOAT, 
  test_varchar2 VARCHAR2(100), 
  test_varchar VARCHAR(100), 
  test_char CHAR, 
  test_nvarchar2 NVARCHAR2(100), 
  test_nchar NCHAR, 
  test_date DATE, 
  test_raw RAW(1000), 
  test_long LONG, 
  CONSTRAINT TEST1_PK PRIMARY KEY (test_number) 
);


create table HR.TEST2 ( 
  test_number NUMBER, 
  test_long_raw LONG RAW, 
  CONSTRAINT TEST2_PK PRIMARY KEY (test_number) 
);

Step 1: Specify Databases – add the local on-premise database as 1 and the remote Oracle Cloud Database Service as 2 (make sure you remember the passwords for SYS and SYSTEM which you specified for this back when creating the service!). Note that archive log mode does not need to be enabled on the target database – UNLESS you intend to configure bi-directional replication, or use this as a source for another replication configuration.

Step 2: Specify Replication Pairs – select 1 (on-premise) as source and 2 (Oracle Cloud database) as target. Accept the defaults, including the DDL replication option. For the purposes of this example, we are going to start with empty tables on both sides so the instantiation options will not come into play here – but you should review these to learn more about the types available. 

Step 3: Specify Schemas and Tables – again, select all the defaults to keep things simple, and enter HR as the schema to replicate.

Step 4: Configure Processes – accept all the defaults for 1, the MINE side configuration. For 2 APPLY side, pay attention to the “Directory with DDC file and default where to create log files etc” question. This is the location created on the Oracle Cloud VM as the home directory for the replication configuration, and this may be different to what you have for the MINE/source side (see 8.iii above). It is also highly likely that the TNS_ADMIN location on your Oracle Cloud will be different to that of your on-premise server, so choose the option to review all the parameters, and edit TNS_ADMIN accordingly. This location is $ORACLE_HOME/network/admin by default and should look something like this:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

Step 5: Final Wrap Up – once completed, then exit out of the Dbvisit Replicate setup wizard, and run the .all script as directed, and follow the other detailed instructions. 

Demo Videos building on the configuration example above

Demo 1: in this short clip, we walk through Steps 1-5 outlined above to configure an “on-premise Oracle to Oracle Cloud database” replication pair, working with Dbvisit Replicate. 

Demo 2: in this second short clip, we turn our attention to seeing the replication we have just configured put to work in real time, and try it out with some basic test loads. The assumption is, at this stage, that all the configuration steps listed above (and walked through in Demo 1) have been completed AND that the instruction set produced by the ALL script (*all.sh) has been actioned. Amongst other things, this requires that the APPLY.ddc file and the script to run the APPLY process on the target side have been transferred across.

As explained earlier, to keep things simple in this test, we are working with empty tables, TEST1 and TEST2, under the HR schema – which has also been cleaned out of any other tables that may have existed prior. We are going to startup the Dbvisit Replicate processes, and then create a test load by inserting data into these tables on the source (on-premise) database (and you can get the script for this here). Once this has been replicated across to the target side, the Oracle Cloud database, we create a new table on the source side under the HR schema, TEST3, using a CTAS statement (logged in as SYS):

create table HR.TEST3 as select * from dba_tables;