Requirements for Oracle
This section describes the requirements, access privileges, and other features of HVR when using Oracle for replication. For information about the capabilities supported by HVR on Oracle, see Capabilities for Oracle.
For information about compatibility and supported versions of Oracle with HVR platforms, see Platform Compatibility Matrix.
To quickly setup replication using Oracle, see Quick Start for HVR on Oracle
- 1 Location Connection
- 2 Hub
- 3 Capture
- 3.1 Grants for Log-Based Capture
- 3.2 Supplemental Logging
- 3.3 Accessing Redo and Archive
- 3.3.1 Managing Archive/Redo Logfiles
- 3.3.2 Data Guard
- 3.3.3 Log Read Method - Direct (Default)
- 3.3.4 Log Read Method - SQL (LogMiner)
- 3.3.5 Capturing from Oracle RAC
- 3.3.6 Pluggable Databases
- 3.4 Trigger-Based Capture
- 4 Integrate and Refresh Target
- 5 Compare and Refresh Source
This section lists and describes the connection details required for creating Oracle location in HVR.
|Oracle_Home||The directory path where Oracle is installed.
|Oracle_SID||The unique name identifier of the instance/database.
|TNS||The connection string required for connecting to Oracle database. The format for the connection string is <host>:<port>/<service_name>.
Alternatively, you can add the connection details into the clients tnsnames.ora file and use that net service name in this field.
|RAC||Parameters for connecting to RAC using SCAN configuration.
|User||The username to connect HVR to the Oracle database.
|Password||The password of the User to connect HVR to the Oracle database.|
|Above is for Redo access only; either for Data Guard (non-Active) Standby or Root Container for LogMiner||Show/hide Primary Connection for selecting data.
|Primary Connection for selecting data|
|TNS||The connection string required for connecting to the primary Oracle database. The format for the connection string is <host>:<port>/<service_name>.
|User||The username to connect HVR to the primary Oracle database.
|Password||The password of the User to connect HVR to the primary Oracle database.|
HVR allows you to create hub database (schema) in Oracle. The hub database is a small database which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction.
Grants for Hub Schema
The hub database (schema) can be located inside an Oracle instance on the hub machine, or it can be located on another machine and connected using a TNS connection. The following grants are required for hub database user (e.g. hvruser):
grant create session to hvruser; grant create table to hvruser; grant create trigger to hvruser; grant create procedure to hvruser;
Grants for Log-Based Capture
HVR does log-based capture if action Capture is defined. HVR can either connect to the database as the owner of the replicated tables, or it can connect as a special user (e.g. hvruser).
- The database User that HVR uses must be granted create session privilege. For example,
- To improve the performance of HVR Initialize for channels with large number of tables (more than 150), HVR creates a temporary table (hvr_sys_table) within a schema. For HVR to automatically create this temporary table, the User should be granted create table privilege. For example,
- To replicate tables which are owned by other schemas (using action TableProperties /Schema) the User must be also granted select any table privilege. For example,
- The User must be granted select privilege for the following data dictionary objects:
grant create session to hvruser;
grant create table to hvruser;
grant select any table to hvruser;
grant select on V$ARCHIVE_DEST to hvruser
|Related Topics||Extra Grants for Supplemental Logging, Extra Grants For Accessing Redo Files Over TNS, Extra Grants for LogMiner, Extra Grants for Amazon RDS for Oracle|
Background: HVR also needs Oracle's "supplemental logging" feature enabled on replicate tables that it must replicate. Otherwise when an update is done Oracle will only log the columns which are changed. But HVR also needs other data (e.g. the key columns) so that it can generate a full update statement on the target database. Oracle supplemental logging can be set at database level and on specific tables. In certain cases this requirement can be dropped. This requires ColumnProperties /CaptureFromRowId to be used, and is explained below.
The very first time that HVR Initialize runs it will check if the database allows any supplemental logging at all. If it is not then HVR Initialize will attempt statement alter database add supplemental log data (see Extra Grants for Supplemental Logging to execute this statement). Note that this statement will hang if other users are changing tables. This is called 'minimal supplemental logging'; it does not actually cause extra logging; that only happens once supplemental logging is also enabled on a specific table. Note that this statement hangs if other users are changing tables. To see the status of supplemental logging, perform query select log_group_type from all_log_groups where table_name='mytab'.
The current state of supplemental logging can be checked with query select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database. This query should return at least ['YES', 'NO', 'NO'].
HVR Initialize will normally only enable supplemental logging for the key columns of each replicated table, using statement alter table tab1 add supplemental log data (primary key) columns. But in some cases HVR Initialize will instead perform alter table tab1 add supplemental log data (all) columns. This will happen if the key defined in the replication channel differs from the Oracle table's primary key, or if one of the following actions is defined on the table;
- On the capture location:
- On any location:
If none of the above requirements force HVR to enable supplemental logging on all columns, the requirement for supplemental logging on key columns can be removed if the channel is configured with ColumnProperties /CaptureFromRowId and ColumnProperties /SurrogateKey. When these actions are defined, HVR will consider the the oracle rowid column as part of the table and will use it as the key column during replication, and integrate it in to the target database.
Problems can occur if multiple HVR channels are capturing changes from the same Oracle base table. This is because both HVR Initialize commands will enable supplemental logging (alter table…add) but if one channel is dropped then it will disable supplemental logging (alter table…drop) which will accidentally affect the other channel. Such problems can be avoided either by (a) taking care with using HVR Initialize to drop a channel (e.g. when dropping, unselect option –ol (supplemental logging) or (b) overriding table level settings by enabling logging at the database level (e.g. alter database add supplemental log data primary key columns).
Supplemental logging can be easily disabled (alter database drop supplemental log data).
Extra Grants for Supplemental Logging
The User must have the privileges mentioned in sections Grants for Log-Based Capture and the following grants for using supplemental logging:
- To execute alter database add supplemental log data the User must have sysdba privilege. Otherwise HVR will write an error message which requests that a different user (who does have this privilege) execute this statement.
- If HVR needs to replicate tables which are owned by other schemas, then optionally the HVR user can also be granted alter any table privilege, so that HVR Initialize can enable supplemental logging on each of the replicated tables. If this privilege is not granted then HVR Initialize will not be able to execute the alter table…add supplemental log data statements itself; instead it will write all the statements that it needs to execute into a file and then write an error message which requests that a different user (who does have this privilege) execute these alter table statements.
Accessing Redo and Archive
The Oracle instance must have archiving enabled, otherwise (a) HVR will lose changes if it falls behind or it is suspended for a time, and (b) HVR will not capture changes made with Oracle insert statements with 'append hints'.
Archiving can be enabled by running the following statement as sysdba against a mounted but unopened database: alter database archivelog. The current state of archiving can be checked with query select log_mode from v$database.
The current archive destination can be checked with query select destination, status from v$archive_dest. By default this will return values USE_DB_RECOVERY_FILE_DEST, VALID, which means that HVR will read changes from within the flashback recovery area. Alternatively, an archive destination can be defined with the following statement: alter system set log_archive_dest_1='location=/disk1/arch' and then restart the instance.
Often Oracle's RMAN will be configured to delete archive files after a certain time. But if they are deleted too quickly then HVR may fail if it falls behind or it is suspended for a time. This can be resolved either by (a) reconfiguring RMAN so that archive files are guaranteed to be available for a specific longer period (e.g. 2 days), or by configuring hvrlogrelease. Note that if HVR is restarted it will need to go back to the start oldest transaction that was still open, so if the system has long running transactions then archive files will need to be kept for longer.
Managing Archive/Redo Logfiles
If log–based capture is defined for an Oracle database (action Capture) then HVR may need to go back to reading the Oracle archive/redo files. But each site has an existing backup/recovery regime (normal RMAN) that periodically deletes these Oracle archive files. There are two ways to ensure that these archive files are available for HVR:
- Configure RMAN so that the archive files are always available for sufficient time for the HVR capture job(s). The 'sufficient time' depends on the replication environment; how long could replication be interrupted for, and after what period of time would the system be reset using an HVR Refresh.
- Install command hvrlogrelease on the source machine to make cloned copies of the archive files so that HVR's capture is not affected when these files are purged by the site's backup/recovery regime. When the capture job no longer needs these cloned files, then hvrlogrelease will delete them again.
HVR can do log-based capture from an Data Guard standby database as well as from Active Data Guard one.
Active Data Guard
To capture from an Active Data Guard standby database, the following steps are necessary;
- Configure the standby database as the HVR location (not the primary one) and define action Capture.
- Configure archiving on the standby database.
- Set the necessary log-based capture grants and configure supplemental logging on the primary database.
HVR can also capture from an Oracle database that was previously a Data Guard target. If HVR was capturing changes from one primary Oracle database and a role transition occur (so that a different Data Guard target becomes the new primary) then HVR can continue capturing from the new primary, including capturing any changes which occurred before the transition. This process is automatic, providing that the HVR location is connecting to Oracle in a way which 'follows the primary'
When the capture job connects to the database again, it will continue to capture from its last position (which is saved on the hub machine).
Non-Active Data Guard
To capture from Non-Active Data Guard standby database, the same steps as for Active Data Guard should be done. But the HVR source location with two connections instead of one has to be created.
In location creation screen, the lower part ('Primary connection for selecting data') describes the connection to a primary database. HVR needs a connection to a primary DB to do all standard operations (like HVR Initialize, HVR Refresh and HVR Compare). It is assumed that the primary DB is reachable from the host of the standby DB through a regular TNS connection. The user for this connection does not require any special privileges.
The upper part describes the connection to a standby database. HVR needs a connection to this DB to query system views about the current state of redo files. It is necessary to provide HVR with a user with sysdba privilege.
Log Read Method - Direct (Default)
By default, HVR captures changes using the DIRECT log read method (Capture /LogReadMethod=DIRECT). In this method, HVR reads transaction log records directly from the DBMS log file using file I/O. This method is very fast in capturing changes from Oracle database. The DIRECT log read method requires that HVR agent is installed on the source database machine.
Oracle parameter DB_BLOCK_CHECKSUM=OFF is not supported by log-based capture. Values TYPICAL (the default) and FULL (unnecessarily high) are supported by HVR.
Extra Grants For Accessing Redo Files Over TNS
For certain Oracle versions (184.108.40.206, 220.127.116.11, and 18.104.22.168), HVR reads the redo and archive files directly through its SQL connection, provided those files are on ASM storage or the connection to the source database is over TNS.
The User must have the privileges mentioned in sections Grants for Log-Based Capture and the following grant for HVR to read the redo and archive files directly through its SQL connection, select any transaction privilege. For example,
grant select any transaction to hvruser;.
Native Access to Redo Files
HVR's capture job needs permission to read Oracle's redo and archive files at the Operating System level. There are three different ways that this can be done;
- Install HVR so it runs as Oracle's user (e.g. oracle).
- Install HVR under a username (e.g. hvr) which is a member of Oracle's default Operating System group (typically either oinstall or dba).
- On Unix and Linux the default group of user oracle can be seen in the 4th field of its line in /etc/passwd. The HVR user be made a member of that group by adding its name to file /etc/group (e.g. line oinstall:x:101:oracle,hvr).
- On Windows, right click My Computer and select Manage ▶ Local Users and Groups ▶ Groups ▶ ora_dba ▶ Add to Group ▶ Add.
Note that adding HVR's user to group dba will also give HVR sysdba privilege.
- On Linux the following commands can be run as user oracle to allow user hvr to see redo files in $ORACLE_HOME/oradata/SID and archive files in $ORACLE_HOME/ora_arch. Note that an extra "default ACL" is needed for the archive directory, so that future archive files will also inherit the directory's permissions.
$ setfacl –R –m u:hvr:rx $ORACLE_HOME/oradata $ setfacl –R –m u:hvr:rx,d:u:hvr:rx $ORACLE_HOME/ora_arch
- On HP UX the commands are as follows;
$ setacl –m u:hvr:rx $ORACLE_HOME/oradata $ setacl –m u:hvr:rx $ORACLE_HOME/oradata/SID $ setacl –m u:hvr:rx $ORACLE_HOME/oradata/SID/* $ setacl –m u:hvr:rx,d:u:hvr:rx $ORACLE_HOME/ora_arch $ setacl –m u:hvr:rx,d:u:hvr:rx $ORACLE_HOME/ora_arch/* $ setacl –m u:hvr:rx $ORACLE_HOME/ora_arch/*/*
- On Solaris an extra command is needed to initialize the "default ACL";
$ setfacl –m u:hvr:rx $ORACLE_HOME/oradata $ setfacl –m u:hvr:rx $ORACLE_HOME/oradata/SID $ setfacl –m u:hvr:rx $ORACLE_HOME/oradata/SID/* $ setfacl –m d:u::rwx,d:g::rx,d:o:,d:m:rwx $ORACLE_HOME/ora_arch $ setfacl –m u:hvr:rx,d:u:hvr:rx $ORACLE_HOME/ora_arch $ setfacl –m u:hvr:rx,d:u:hvr:rx $ORACLE_HOME/ora_arch/* $ setfacl –m u:hvr:rx $ORACLE_HOME/ora_arch/*/*
Accessing Oracle ASM
HVR supports log-based capture from Oracle databases whose redo and archive files are located on ASM storage.
To configure this, define environment variable $HVR_ASM_CONNECT to a username/password pair such as sys/sys. The user needs sufficient privileges on the ASM instance; sysdba for Oracle version 10 and sysasm for Oracle 11+. If the ASM is only reachable through a TNS connection, you can use username/password@TNS as the value of $HVR_ASM_CONNECT. If HVR is not able to get the correct value for the $ORACLE_HOME of the ASM instance (e.g. by looking into /etc/oratab), then that path should be defined with environment variable $HVR_ASM_HOME. These variables should be configured using environment actions on the Oracle location.
The password can be encrypted using the hvrcrypt command.
|Unix & Linux|
$ export HVR_ASM_CONNECT="myuser/`hvrcrypt myuser mypass`"
Archive Log Only
HVR allows you to capture data from archived redo files in directory defined using action Capture/ArchiveLogPath. It does not read anything from online redo files or the 'primary' archive destination.
This allows the HVR process to reside on a different machine than the Oracle DBMS and read changes from files that are sent to it by some remote file copy mechanism (e.g. FTP). The capture job still needs an SQL connection to the database for accessing dictionary tables, but this can be a regular connection.
Replication in this mode can have longer delays in comparison with 'online' one. To control the delays, it is possible to force Oracle to issue an archive once per predefined period of time.
On RAC systems, delays are defined by the slowest or the less busy node. This is because archives from all threads have to be merged by SCNs in order to generate replicated data flow.
Capturing Encrypted (TDE) Tables
HVR supports capturing tables that are encrypted using Oracle Transparent Data Encryption (TDE). Capturing tables located in encrypted tablespace and tables with encrypted columns are supported for Oracle version 11 and higher.
HVR supports software and hardware (HSM) wallets. If the wallet is not configured as auto-login (Oracle internal file cwallet.sso), using command hvrlivewallet set the password for the wallet on HVR Live Wallet port.
Software wallets can be located in ASM or in a local filesystem. If the wallet is located in a local filesystem then HVR either needs permission to read the wallet file or an HVR trusted executable should be created in $HVR_HOME/sbin with chmod +4750. If the wallet located in a local filesystem is configured as auto-login, then HVR or the trusted executable must be run as the user who created the wallet (usually the oracle user).
In Oracle 12, for replicating encrypted columns, hvruser should have explicit select privileges on sys.user$ and sys.enc$ tables.
grant select on sys.user$ to hvruser; grant select on sys.enc$ to hvruser;
Further channel configuration changes are not required; HVR automatically detects encryption and opens the wallet when it is encountered.
HVR does not support capturing encrypted (TDE) tables on HP-UX platform.
Log Read Method - SQL (LogMiner)
HVR captures changes using the SQL log read method (Capture /LogReadMethod=SQL). In this method, HVR reads transaction log records using a special SQL function. This method reads change data over an SQL connection and does not require HVR agent to be installed on the source database machine. However, the SQL log read method is slower than the DIRECT log read method and exposes additional load on the source database.
The SQL log read method enables capture from LogMiner.
Limitations of SQL Log Read Method
- Only Oracle version 22.214.171.124 and above is supported for capturing changes from LogMiner.
- Updates that only change LOB columns is not supported.
- Capture from XML Data Type columns is not supported.
- Index Organized Tables (IOT) with an overflow segment is not supported.
- Capturing DDL (using AdaptDDL) changes such as add table as..., drop table... and alter table..., including partition operations are not supported.
Extra Grants for LogMiner
The User must have the privileges mentioned in section Grants for Log-Based Capture and the following grants for using LogMiner:
- The User must be granted execute on dbms_logmnr privilege. For example,
- The User must be granted select any transaction privilege. For example,
- The User must be granted execute_catalog_role privilege.
grant execute_catalog_role to hvruser;
- For Oracle 12.1 and later, the User must be granted logmining system privilege. For example,
grant execute on dbms_logmnr to hvruser;
grant select any transaction to hvruser;
grant logmining to hvruser;.
|Related Topics||Extra Grants for Amazon RDS for Oracle|
Amazon RDS for Oracle
HVR supports log-based capture and integrate into Amazon RDS for Oracle database. This section provides information required for replicating changes in Amazon RDS for Oracle.
The following logging modes should be enabled for the Amazon RDS DB instances. You can use the Amazon RDS procedure mentioned below to enable/disable the logging modes.
- Force Logging - Oracle logs all changes to the database except changes in temporary tablespaces and temporary segments (NOLOGGING clauses are ignored).
exec rdsadmin.rdsadmin_util.force_logging(p_enable => ''true'');
- Supplemental Logging - To ensure that LogMiner and products that use LogMiner have sufficient information to support chained rows and storage arrangements such as cluster tables.
- Switch Online Log Files - To prevent the following error in HVR: Log scanning error F_JZ1533. The scanner could not locate RBA file sequence number in thread.
- Retaining Archive Redo Logs - To retain archived redo logs on your Amazon RDS DB instance, database backups must be enabled by setting the retention period to greater than 0 (zero) days and and the archivelog retention hours should be set to greater than 0 (zero) hours. Enabling database backup can be done while creating the instance or after by going to Instances > Modify > Backup and set the number of days.
The following example retains 24 hours of redo logs
begin rdsadmin.rdsadmin_util.set_configuration (name => 'archivelog retention hours', value => '24'); end; /
Extra Grants for Amazon RDS for Oracle
- The User must be granted select any transaction privilege. For example,
- For Amzon RDS for Oracle 12 and later, the User must be granted logmining system privilege. For example,
- The User must be granted logminer specific grants by using the Amazon RDS procedure:
grant select any transaction to hvruser;
grant logmining to hvruser;.
begin rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name => 'DBMS_LOGMNR', p_grantee => 'HVRUSER', p_privilege => 'EXECUTE', p_grant_option => true); end; /
For more information about Amazon RDS for Oracle-specific DBA tasks, refer to Common DBA Log Tasks for Oracle DB Instances.
Location Connection - Amazon RDS for Oracle
For better performance it is recommended to install HVR on Amazon Elastic Cloud 2 (EC2) instance in the same region of the RDS instance. For more information about installing HVR image on AWS, see New Installation of HVR Image for AWS.
Following are the connection details required for creating an Amazon RDS for Oracle location in HVR:
Connect to HVR on remote machine
Node is the Public DNS of the EC2 instance.
Port is the HVR Remote Listener port.
Login is the operating system username for the EC2 instance of the HVR Remote Listener.
Password can be anything. This password is not validated as HVR Remote Listener is started without password validation (option -N).
SslRemoteCertificate is the SSL certificate created on EC2 instance.
ORACLE_HOME is the ORACLE HOME path of the EC2 intance . For example, /usr/lib/oracle/12.1/client64.
TNS is the connection string required for connecting to RDS database. The format for the connection string is AWS Endpoint:Port/DB Name. Alternatively, you can add the connection details into the clients tnsnames.ora file and use that net service name in this field.
User is the username to connect HVR to the Amazon RDS for Oracle database. For example, hvruser.
Password is the password of the User.
Capturing from Amazon RDS for Oracle
HVR uses Logminer to capture from Amazon RDS for Oracle. DDL changes are not captured since Logminer is used for capture.
To capture from Amazon RDS for Oracle, the following action definitions are required:
|Amazon RDS for Oracle||*||Capture /LogReadMethod=SQL|
|Amazon RDS for Oracle||*||Environment /Name=TZ /Value=UTC|
Capturing from Oracle RAC
When capturing from Oracle RAC, HVR will typically connect with its own protocol to an HVR listener. On Unix this could be configured using inetd daemon. This listener should be configured to run inside all cluster nodes simultaneously. The hub then connects to one of its remote locations by first interacting with the Oracle RAC 'scan' address.
The HVR channel only needs one location for its RAC and there is only one job at runtime. This capture job connects to just one node and keeps reading changes from the shared redo archives for all nodes.
Directory $HVR_HOME and $HVR_CONFIG should exist on both machines, but does not normally need to be shared. If $HVR_TMP is defined, then it should not be shared.
Prior to HVR 5.5.0/8, capture from Oracle RAC is only supported using the DIRECT mode (Capture /LogReadMethod=DIRECT). However, since HVR 5.5.0/8, capture from Oracle RAC is also supported using the SQL mode (Capture /LogReadMethod=SQL).
HVR supports capturing from Oracle's pluggable database (PDB). To enable capturing from PDB, the connection should point to the database service of the PDB. You need to have the remotelistener running on the database server (either on primary or standby server if using dataguard). The connection method is always TNS to the PDB.
Location connection details required for pluggable databases:
- TNS is the connection to the database server, port, and database service (for example, myserver:1521/HVR1210).
- User is the username to connect HVR to container database (where the PDB is located). The user should be an Oracle common user (for example c##skcen). The privileges required for User is same as the log-based capture grants.
- Password is the password of the User to connect HVR to container database.
HVR allows you to perform trigger-based capture when action Capture is defined with parameter /TriggerBased. HVR can either connect to the database as the owner of the replicated tables, or it can connect as a special user (e.g. hvr).
Grants for Trigger-Based Capture
- The database User must have the following privileges:
- create session
- create table
- create trigger
- create procedure
- create sequence
- select any table
- execute any procedure
- create any trigger
grant select any dictionary to hvruser;
An alternative to this specific grant is to provide sysdba privilege to User.
grant execute on dbms_alert to hvruser;
Integrate and Refresh Target
Grants for HVR on Target Database
- To Integrate changes into a database, or to load data into a database using HVR Refresh, the User must be granted the following privileges:
- create session
- create table
- select any table
- insert any table
- update any table
- delete any table
- alter any table
- lock any table
- drop any table (needed for truncate statements)
- create any table
- create any index
- drop any index
- alter any table
- drop any table
- create any sequence
- select any sequence
- drop any sequence
Compare and Refresh Source
Grants for Compare or Refresh (Source Database)
- To perform HVR Compare and HVR Refresh, the User must be granted create session privilege.
- If HVR Compare or HVR Refresh needs to read from tables which are owned by other schemas (using action TableProperties /Schema) the User must be granted select any table privilege.
- If the Select Moment feature (option -M in HVR Compare and HVR Refresh) is used then the User must be granted the following privileges:
- flashback any table
- select any transaction