8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Dbvisit Reporting Installation on Oracle Linux 5 and 6
Dbvisit Reporting is an alternative to Oracle Streams or Golden Gate for replicating databases. It is a cut-down version of Dbvisit Replicate, targeted specifically for delivering real-time offload reporting databases. In this article I'll focus on a basic setup. This is definitely not meant as a replacement for reading the Dbvisit Reporting 2.4 User Guide.
Assumptions
- You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 6.3 and Oracle Database 11.2.0.3. The Oracle versions can be different.
- The servers are pingable using their hostnames (dbvisit1 and dbvisit2).
- A database is running in archivelog mode on the primary server. A database is running on the secondary server in archivelog or noarchivelog mode.
- The servers can make SQL*Net connections to each other. In this case the SQL*Net connection aliases are PRODA and PRODB.
The following information may help you to tailor the installation for your own installation.
ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 Primary hostname : dbvisit1.localdomain ORACLE_SID=PRODA Secondary hostname: dbvisit2.localdomain ORACLE_SID=PRODB
The "tnsnames.ora" file on each server contains the following entries.
PRODA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PRODA.WORLD) ) ) PRODB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PRODB.WORLD) ) )
The following user is created in both databases.
CONN / AS SYSDBA CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE TO test; CONN test/test CREATE TABLE tab1 ( id NUMBER, description VARCHAR2(50), CONSTRAINT tab1_pk PRIMARY KEY (id) );
If you are trying this out on virtual servers I would suggest you complete the basic setup of the OS and database on the servers, then take snapshots of them so you can revert back at any point and start again.
Installation
Install the Dbvisit Reporting RPM on both servers as the "root" user.
# rpm -Uvh dbvisit_reporting-2.4.06-1.x86_64.rpm Preparing... ########################################### [100%] 1:dbvisit_reporting ########################################### [100%] #
As the "oracle" user, run the following commands on the first server.
$ cd /usr/dbvisit/reporting $ dbvreport
When requested, enter "setup wizard" at the prompt and answer the questions. There are quite a lot of questions to answer. Be careful when typing responses to the questions! If you need any explanation of these responses, check the Dbvisit Reporting 2.4 User Guide.
For this test I set up schema replication from TEST@PRODA to TEST@PRODB. The replication process was called "PROD" and the configuration directory used on both servers was "/home/oracle/Documents/PROD".
Once the configuration is complete, navigate to the directory you chose to hold the configuration scripts and run the top-level script.
$ cd /home/oracle/Documents/PROD $ ./PROD-all.sh Setting up Dbvisit Reporting configuration Configure database PRODA... Configure database PRODB... Object grants for database PRODA... Object grants for database PRODB... Setting up the configuration Initializing......done WARN-1850: No DDC DB available, dictionary table does not exist. DDC loaded from database (0 variables). Dbvisit Reporting version 2.4.06.2303 Copyright (C) Dbvisit Software Limited. All rights reserved. DDC file /home/oracle/Documents/PROD/PROD-onetime.ddc loaded. MINE: Cannot determine Dbvisit Reporting dictionary version. (no dictionary exists) APPLY: Cannot determine Dbvisit Reporting dictionary version. (no dictionary exists) dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR EXIT Variable ON_ERROR set to EXIT for process *. dbvrep> ENGINE SWITCH_REDOLOG dbvrep> ENGINE SETUP MINE DROP DICTIONARY 0 dictionary objects dropped. dbvrep> ENGINE SETUP MINE CREATE DICTIONARY dbvrep> ENGINE SETUP MINE LOAD DICTIONARY Supplemental logging on database set. Loading dictionary table DBRSCOL$ Loading dictionary table DBRSIND$ Loading dictionary table DBRSOBJ$ Loading dictionary table DBRSTAB$ Loading dictionary table DBRSUSER$ Loading dictionary table DBRSV_$DATABASE dbvrep> ENGINE SETUP APPLY DROP DICTIONARY 0 dictionary objects dropped. dbvrep> ENGINE SETUP APPLY CREATE DICTIONARY dbvrep> ENGINE SETUP APPLY LOAD DICTIONARY dbvrep> ENGINE PREPARE_DP SETUP CLEAR dbvrep> ENGINE SETUP PAIR MINE AND APPLY ID of mine proces is 52B515F0-46EA-11E2-9C57-1B24F84AAED1. If not using DDC in database, set MINE_UNIQUE_ID to this value. 1 applier SCN set. dbvrep> SET APPLY.INSTANTIATE_SCN NOW Variable INSTANTIATE_SCN set to NOW for process APPLY. dbvrep> ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST ENABLE PRIMARY KEY dbvrep> ENGINE SWITCH_REDOLOG dbvrep> #single-scn instantiation: lock all tables and schemas dbvrep> ENGINE LOCK SCHEMAS TEST Locking all schemas. Lock done. dbvrep> #single-scn instantiation: unlock all tables and schemas, but keep the SCN dbvrep> ENGINE LOCK RELEASE LOCKS dbvrep> dbvrep> #prepare the tables (we use OFFLINE as neither MINE not APPLY is running; with OFFLINE we won't wait on network timeout) dbvrep> PREPARE OFFLINE SCHEMA TEST NODDL dbvrep> #single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE statements) dbvrep> ENGINE LOCK CLEAR SCN dbvrep> ENGINE SWITCH_REDOLOG dbvrep> #prepare script for instantiation dbvrep> ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/Documents/PROD/APPLY.sh DBLINK PRODA USERID SYSTEM/password@PRODB Created Data Pump script /home/oracle/Documents/PROD/APPLY.sh, using network import. dbvrep> create ddcdb from ddcfile DDC loaded into database (210 variables). dbvrep> set ON_WARNING SKIP Variable ON_WARNING set to SKIP for process *. dbvrep> set ON_ERROR SKIP Variable ON_ERROR set to SKIP for process *. OK-0: Completed successfully. These steps are required after the PROD-all.sh script runs: 1) Create the necessary directory(ies) on the servers: dbvisit2.localdomain: /home/oracle/Documents/PROD 2) Copy the DDC files to the server(s) where the processes will run: dbvisit2.localdomain: /home/oracle/Documents/PROD/PROD-APPLY.ddc dbvisit1.localdomain: /home/oracle/Documents/PROD/PROD-MINE.ddc 3) Review that path to dbvreport executable is correct in the run scripts: /home/oracle/Documents/PROD/PROD-run-dbvisit1.localdomain.sh /home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh 4) Copy the run script to the server(s) where the processes will run: /home/oracle/Documents/PROD/PROD-run-dbvisit1.localdomain.sh /home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh 5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes. 6) Make sure the data on apply are in sync as of time when setup was run. Scripts for Data Pump/export/DDL were created as requested: /home/oracle/Documents/PROD/APPLY.sh Create referenced database links (if any) before running the scripts. 7) Start the replication processes on all servers: /home/oracle/Documents/PROD/PROD-run-dbvisit1.localdomain.sh /home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh 8) Start the console to monitor the progress: chmod 0750 /home/oracle/Documents/PROD/start-console.sh /home/oracle/Documents/PROD/start-console.sh The above list is stored in /home/oracle/Documents/PROD/Nextsteps.txt. $
On successful completion the script lists 8 steps you should take to complete the setup. Since this is a simple test case they can be summarised as follows.
On the second server make the configuration directory and copy across the necessary files. The following operations are performed as the "oracle" user.
$ mkdir -p /home/oracle/Documents/PROD $ scp oracle@dbvisit1:/home/oracle/Documents/PROD/PROD-APPLY.ddc /home/oracle/Documents/PROD/PROD-APPLY.ddc $ scp oracle@dbvisit1:/home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh /home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh
In this case the databases are already in sync, so we can ignore step 6. Run the following scripts as the "oracle" user on their respective servers.
$ # Run on dbvisit1 $ /home/oracle/Documents/PROD/PROD-run-dbvisit1.localdomain.sh $ # Run on dbvisit2 $ /home/oracle/Documents/PROD/PROD-run-dbvisit2.localdomain.sh
Start the console on the primary server to monitor the replication.
$ chmod 0750 /home/oracle/Documents/PROD/start-console.sh $ /home/oracle/Documents/PROD/start-console.sh
Provided you have no errors, replication is now configured and running.
Test
Run the following script from SQL*Plus to populate the table in the primary database.
CONN test/test@PRODA INSERT INTO tab1 SELECT level, 'Description for ' || level FROM dual CONNECT BY level < 1000; COMMIT;
The console screen will now show the replication has taken place.
/MINE IS running. Currently at plog 34 and SCN 1078608 (12/15/2012 20:27:16). APPLY IS running. Currently at plog 34 and SCN 1078606 (12/15/2012 20:27:14). Progress of replication PROD:MINE->APPLY: total/this execution -------------------------------------------------------------------------------------------------------------------------------------------- TEST.TAB1: 100% Mine:999/999 Unrecov:0/0 Applied:999/999 Conflicts:0/0 Last:15/12/2012 20:25:06/OK -------------------------------------------------------------------------------------------------------------------------------------------- 1 tables listed.
For more information see:
- Dbvisit.com
- Dbvisit Reporting 2.4 User Guide
- Dbvisit Replicate Installation on Oracle Linux 5 and 6
- Dbvisit Standby Installation on Oracle Linux 5 and 6
Hope this helps. Regards Tim...