We're here to help!

Configure Dbvisit Replicate with Data Vault

Follow

Read our docs This article describes how to configure Dbvisit Replicate when the source database uses Data Vault option.


Problem Details

Problem Statement

Example setup of role separation

Applicable Error Code

n/a

Affected Versions

All

Affected Platforms

All

 


Description

Oracle Database Vault implements powerful security controls within Oracle Database. These unique security controls restrict access to application data by privileged database users, reducing the risk of insider and outside threats and addressing common compliance requirements.

Dbvisit Replicate connects with DBA and SYSDBA privileges during the setup phase so Data Vault manager needs to explicitely grant privileges as shown later in the example.

In my example I use the following Vault setup:

DV OWNER is DV_DBVOWNER
DV MANAGER is DV_DBVMANAGER
password for both is "Welcome123."

Steps Performed
  1. Check Data Vault status
    SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 22 01:08:23 2017
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
    Oracle Database Vault and Real Application Testing options
    
    SQL> select comp_id,status from dba_registry where comp_id in ('OLS','DV');
    
    COMP_ID 		       STATUS
    ------------------------------ -----------
    DV			       VALID
    OLS			       VALID
    
    SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');
    
    PARAMETER
    ----------------------------------------------------------------
    VALUE
    ----------------------------------------------------------------
    Oracle Label Security
    TRUE
    
    Oracle Database Vault
    TRUE
  2. Replicate Setup Wizard example
    Following databases are now configured:
    1: Oracle SRCDB, SYS/***, SYSTEM/***, dbvrep1/***, USERS/TEMP, dbvrep1/, ASM:No, TZ: +00:00
    2: Oracle SRCDB, SYS/***, SYSTEM/***, dbvrep2/***, USERS/TEMP, dbvrep2/, ASM:No, TZ: +00:00
    
    Following replication pairs are now configured:
    1: SRCDB (Oracle) ==> SRCDB (Oracle), DDL: Yes, fetcher: No, process suffix: (no suffix), compression: No, encryption: No, network
    timeout: 60, prepare type: single-scn, data load: none
    
    Following tables are defined for replication pairs:
    1: SRCDB (Oracle) ==> SRCDB (Oracle), DDL: Yes, suffix: (no suffix), prepare: single-scn
      No tables defined.
                                                                                                                                          Enter number of replication pair to modify it, or "done": [1] done
    
    Following processes are defined:
    1: MINE on SRCDB
      Host: dbvlin216.dbvisit.co.nz, SMTP: No, SNMP: No
    2: APPLY on SRCDB
      Host: dbvlin216.dbvisit.co.nz, SMTP: No, SNMP: No
  3. Execute *-all.sh
    oracle@dbvlin216[/home/oracle/RQ3704]: ./RQ3704-all.sh 
    Setting up Dbvisit Replicate configuration
    Configure database SRCDB...
    
    This check fails if the DBID is not the expected one...
    
    
    Ok, check passed.
    create user dbvrep1 identified by "dbvpasswd" default tablespace USERS temporary tablespace TEMP
                                      *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    Error encountered, not starting Dbvisit Replicate.
    oracle@dbvlin216[/home/oracle/RQ3704]: 
    It is an expected behaviour because SYS user needs a privilege granted by DV Manager to be able to create other users.
  4. Fix User Access Management
    SQL> conn dv_dbvmanager/Welcome123.
    Connected.
    SQL> grant dv_acctmgr to sys;
    
    Grant succeeded.
  5. Execute *-all.sh again
    oracle@dbvlin216[/home/oracle/RQ3704]: ./RQ3704-all.sh 
    Setting up Dbvisit Replicate configuration
    Configure database SRCDB...
    
    This check fails if the DBID is not the expected one...
    
    
    Ok, check passed.
    Configure database SRCDB...
    
    This check fails if the DBID is not the expected one...
    
    
    Ok, check passed.
    Object grants for database SRCDB...
    
    Object grants for database SRCDB...
    
    Setting up the configuration
    Initializing......done
    DDC loaded from database (0 variables).
    Dbvisit Replicate version 2.9.00
    Copyright (C) Dbvisit Software Limited. All rights reserved.
    DDC file /home/oracle/RQ3704/config/RQ3704-onetime.ddc loaded.
    MINE: Cannot determine Dbvisit Replicate dictionary version. (no
    dictionary exists)
    APPLY: Cannot determine Dbvisit Replicate dictionary version. (no
    dictionary exists)
    dbvrep> #clear the no-DDC-DB-available warning
    dbvrep> process clear previous warnings
    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> 
    dbvrep> # Configuring default processes
    dbvrep> choose process MINE
    Process type MINE set to: MINE.
    dbvrep> choose process APPLY
    Process type APPLY set to: APPLY.
    dbvrep> PROCESS SWITCH_REDOLOG
    Redo log switch requested.dbvrep> PROCESS SETUP MINE DROP DICTIONARY
    0 dictionary objects dropped.
    dbvrep> PROCESS SETUP MINE CREATE DICTIONARY
    dbvrep> PROCESS SETUP MINE LOAD DICTIONARY
    Oldest active transaction SCN: 1739217 (no active transaction)
    Supplemental logging on database set.
    dbvrep> PROCESS SETUP APPLY DROP DICTIONARY
    0 dictionary objects dropped.
    dbvrep> PROCESS SETUP APPLY CREATE DICTIONARY
    dbvrep> PROCESS SETUP APPLY LOAD DICTIONARY
    dbvrep> PROCESS PREPARE_DP SETUP CLEAR
    dbvrep> PROCESS SETUP PAIR MINE AND APPLY
    Applier SCN set (start=1739240, current=1739240).
    dbvrep> SET APPLY.INSTANTIATE_SCN NOW
    Variable INSTANTIATE_SCN set to NOW for process APPLY.
    dbvrep> SET MINE._PREPARE_SUPLOG_TYPE PK
    Variable _PREPARE_SUPLOG_TYPE set to PK for process MINE.
    dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables
    created by Compression Advisor
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables
    created by Compression Advisor
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.SCHEDULER$_% #Ignore tables created by
    Oracle scheduler (also used by schema/full expdp/impdp)
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.CMP1$% #Ignore tables created by
    Compression Advisor since 11.2.0.4
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.CMP2$% #Ignore tables created by
    Compression Advisor since 11.2.0.4
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.CMP3$% #Ignore tables created by
    Compression Advisor since 11.2.0.4
    Exclude rule created.
    dbvrep> EXCLUDE CREATE TABLE %.CMP4$% #Ignore tables created by
    Compression Advisor since 11.2.0.4
    Exclude rule created.
    dbvrep> PROCESS SWITCH_REDOLOG
    Redo log switch requested.dbvrep> PROCESS WAIT_SCN_FLIP
    Waited 3 seconds until scn_to_timestamp changed.
    dbvrep> #single-scn instantiation: lock all tables and schemas
    dbvrep> #single-scn instantiation: unlock all tables and schemas, but
    keep the SCN
    dbvrep> PROCESS LOCK RELEASE LOCKS
    dbvrep> 
    dbvrep> #prepare the tables (we use OFFLINE as neither MINE nor APPLY
    is running; with OFFLINE we won't wait on network timeout)
    dbvrep> PROCESS SWITCH_REDOLOG
    Redo log switch requested.dbvrep> #prepare script for instantiation
    dbvrep> create ddcdb from ddcfile
    DDC loaded into database (427 variables).
    dbvrep> load ddcdb
    DDC loaded from database (427 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.
    WARN-1850: No DDC DB available, dictionary table does not exist.
    These steps are required after the RQ3704-all.sh script runs:
    
    1) Create the necessary directory(ies) on the servers:
    
    2) Copy the DDC files to the server(s) where the processes will run:
    dbvlin216.dbvisit.co.nz: /home/oracle/RQ3704/RQ3704-APPLY.ddc
    dbvlin216.dbvisit.co.nz: /home/oracle/RQ3704/RQ3704-MINE.ddc
    
    Ensure that the parameter TNS_ADMIN (in the ddc file) is pointing to the correct TNS_ADMIN path on each of the servers.
    
    3) Review that path to dbvrep executable is correct in the run scripts:
    /home/oracle/RQ3704/RQ3704-run-dbvlin216.dbvisit.co.nz.sh
    
    4) Copy the run script to the server(s) where the processes will run:
    dbvlin216.dbvisit.co.nz: /home/oracle/RQ3704/RQ3704-run-dbvlin216.dbvisit.co.nz.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.
    
    7) Start the replication processes on all servers:
    dbvlin216.dbvisit.co.nz: /home/oracle/RQ3704/RQ3704-run-dbvlin216.dbvisit.co.nz.sh
    
    8) Start the console to monitor the progress:
    /home/oracle/RQ3704/start-console.sh
    
    The above list is stored in /home/oracle/RQ3704/Nextsteps.txt.
  6. Start Replicate processes
    oracle@dbvlin216[/home/oracle/RQ3704]: ./RQ3704-run-dbvlin216.dbvisit.co.nz.sh 
    Initializing......done
    DDC loaded from database (427 variables).
    Dbvisit Replicate version 2.9.00
    Copyright (C) Dbvisit Software Limited. All rights reserved.
    DDC file /home/oracle/RQ3704/RQ3704-APPLY.ddc loaded.
    Starting process APPLY...Created directory /home/oracle/RQ3704/ddc_backup
    Created directory /home/oracle/RQ3704/log/trace/
    Created directory /home/oracle/RQ3704/apply
    started
    Initializing......done
    DDC loaded from database (427 variables).
    Dbvisit Replicate version 2.9.00
    Copyright (C) Dbvisit Software Limited. All rights reserved.
    DDC file /home/oracle/RQ3704/RQ3704-MINE.ddc loaded.
    Starting process MINE...Created directory /home/oracle/RQ3704/mine/
    started
  7. Prepare testing table and run transaction load
    Dbvisit Replicate 2.9.00(MAX edition) - Evaluation License expires in 30 days
    MINE is running. Currently at plog 14 and SCN 1755809 (09/22/2017 01:31:14).
    APPLY is running. Currently at plog 14 and SCN 1755778 (09/22/2017 01:31:12).
    Progress of replication RQ3704:MINE->APPLY: total/this execution
    -------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------
    0 tables listed.
                                                                                                                                          dbvrep> prepare table scott.x1 rename to scott.x2
    Prepare enabled supplemental logging, waiting for SCN advance enough to prevent ORA-01466 during consistent export....Waited 1 seconds until scn_to_timestamp changed.
    Connecting to running apply [SCOTT.X2]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).]
    Connecting to running mine [SCOTT.X1]: [Table prepared (1 internal records).]
    Table SCOTT.X1 instantiated at SCN 1755779
                                                                                                                                          dbvrep> list prepare
    List of prepared schemas:
    none
    List of prepared tables:
    DBVREP1.DBRSCOMMON_HEARTBEAT -> DBVREP2 (schema rename)
    DBVREP1.DBRSOBJ$ -> DBVREP2 (schema rename)
    DBVREP1.DBRSUSER$ -> DBVREP2 (schema rename)
    SCOTT.X1 -> SCOTT.X2 (table rename) (DDL)

    SQL> insert into x1 values (1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from x2;
    
    no rows selected
    
    SQL> /
    
    no rows selected
    
    SQL> /
    
    no rows selected
    
    SQL> /
    
    	ID
    ----------
    	 1
  8. Check Replicate status
    Dbvisit Replicate 2.9.00(MAX edition) - Evaluation License expires in 30 days
    MINE is running. Currently at plog 14 and SCN 1756395 (09/22/2017 01:33:07).
    APPLY is running. Currently at plog 14 and SCN 1756386 (09/22/2017 01:33:06).
    Progress of replication RQ3704:MINE->APPLY: total/this execution
    -------------------------------------------------------------------------------------------------------------------------------------
    SCOTT.X1/SCOTT.X2:            100%  Mine:1/1             Unrecov:0/0         Applied:1/1         Conflicts:0/0       Last:22/09/2017 01:32:23/OK
    -------------------------------------------------------------------------------------------------------------------------------------
    1 tables
  9. Revoke privileges
    oracle@dbvlin216[/home/oracle/RQ3704]: sqlplus
    
    SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 22 01:37:17 2017
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Enter user-name: dv_dbvmanager
    Enter password: 
    Last Successful login time: Fri Sep 22 2017 01:23:01 +12:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
    Oracle Database Vault and Real Application Testing options
    
    SQL> revoke dv_acctmgr from sys;
    
    Revoke succeeded.
Have more questions? Submit a request

Comments