Page Comparison

Dbvisit Replicate works with Oracle Cloud.

...

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

Configuring Dbvisit Replicate

...

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:

...

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:

...

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.