- August 29, 2017 at 4:53 pm #10987ggoodrichKeymaster
What minimal permissions can I give the HVR user on Oracle?
HVR needs its own schema (called the ‘hub database’) on the hub machine, where it keeps its control information. This schema can be inside an Oracle instance on the hub machine, or it can be on another machine using a TNS connection. This user name may need extra permissions to capture changes from a source database or integrate changes into a target database.
GRANTS FOR HUB SCHEMA
The following steps are sufficient to create a hub database schema called hvrhub with password mypass.
$ sqlplus system/manager SQL> create user hvrhub identified by mypass 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; SQL> grant create session to hvrhub; SQL> grant create table to hvrhub; SQL> grant create trigger to hvrhub; SQL> grant create procedure to hvrhub; # For creating a temporary table. An alternative is just to create this table manually using SQL statement # create global temporary table hvr_sys_table (table_name varchar(128), table_owner varchar(128)).
SQL> grant create table to hvruser; #If action DbSequence is used
SQL> grant select any sequence to hvruser;
SQL> grant grant select on sys.seq$ to hvruser; # If you want HVR needs to add 'supplemental logging' automatically. An alternavive is to add the supplemental logging manually
SQL> grant sysdba to hvruser;
SQL> grant alter any to table to hvruser; # For Oracle 11.2, before Oracle 18.104.22.168 and files in ASM
SQL> grant select any transaction to hvruser; # For refresh with "Select Moment" (option -M)
SQL> grant flashback any table hvruser;
HVR’s hub schema must also be allowed to execute package dbms_alert. This grant can only be given by a user with sysdba privilege (e.g. oracle).
$ sqlplus / as sysdba SQL> grant execute on dbms_alert to hvrhub;
GRANTS FOR TRIGGER-BASED CAPTURE DATABASE
HVR does trigger-based capture if action DbCapture is defined without parameter /LogBased. 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). The database user must have the following privileges: create session, create table, create trigger, create procedure and create sequence. If the HVR user needs to replicate tables which are owned by other schemas (using action TableProperties /Schema) then the following are also needed; select any table, execute any procedure and create any trigger. Trigger-based capture will use package dbms_alert , unless action DbCapture is defined with parameter /ToggleFrequency or action Scheduling is defined with parameters /CaptureStartTimes or /CaptureOnceOnTrigger. This grant can only be given by a user with sysdba privilege (e.g. oracle).
$ sqlplus / as sysdba SQL> grant execute on dbms_alert to capuser;
GRANTS FOR LOG-BASED CAPTURE DATABASE
HVR does log-based capture if action DbCapture /LogBased 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. hvr). The database user that HVR uses must be granted create session privilege. If HVR needs to replicate tables which are owned by other schemas (using action TableProperties /Schema) then the user must be granted select any table privilege. HVR needs grants for ‘supplemental logging’. This is explained in more detail in the next section. This needs two privileges;
The very first time that HVR Load runs it will check if the database allows any supplemental at all. If it is not then HVR Load will attempt statement alter table database supplemental log data. This will succeed if the HVR user has sysdba privilege. Otherwise HVR will write an error message which requests that a different user (who does have this privilege) execute this statement. Note that this statement will hang if other users are changing tables. 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 Load can enable ‘supplemental logging’ on each of the replicated tables. If this privilege is not granted then HVR Load will not be able to execute the alter table…add supplemental log 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.
HVR needs to read the data dictionaries in Oracle’s SYS schema. This can be done by either giving the HVR user sysdba privilege or using statement grant select any dictionary to capuser. NOTE: The steps to give sysdba privilege to an Oracle user (e.g. hvr) are as follows;
On Unix and Linux this can be done by adding the user name used by HVR to the line in /etc/group that begins with dba. On Windows -right-click My Computer -select- Manage > Local Users and Groups > Groups > ora_dba > Add to Group > Add.
CONFIGURING SUPPLEMENTAL LOGGING FOR LOG-BASED CAPTURE
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. The very first time that HVR Load runs it will check if the database allows any supplemental at all. If it is not then HVR Load will attempt statement alter table database supplemental log data (see previous section for the privileges that this requires). 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. HVR Load 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 Load 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;
ColumnProperties /CaptureExpression or Restrict with /CaptureCondition, /HorizColumn or /AddressTo on the capture location. DbIntegrate with /DbProc or /Resilient or ColumnProperties /IntegrateExpression on an integrate location. TableProperties /DuplicateRows or CollisionDetect on any location.
Problems can occur if multiple HVR channels are capturing changes from the same Oracle base table. This is because both HVR Load 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 Load 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 using: SQL>alter database drop supplemental log data;
CONFIGURING REDO FILES AND ARCHIVING FOR LOG-BASED CAPTURE
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 (see section 5.9). 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.
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:
A. Install HVR so it runs as Oracle’s user (e.g. oracle).
B. 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.
C. Create special Access Control Lists (ACLs) on these files so that HVR’s user can read them. 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:r-x $ORACLE_HOME/oradata $ setfacl –R –m u:hvr:r-x,d:u:hvr:r-x $ORACLE_HOME/ora_arch
On HP-UX the commands are as follows;
$ setacl –m u:hvr:r-x $ORACLE_HOME/oradata $ setacl –m u:hvr:r-x $ORACLE_HOME/oradata/SID $ setacl –m u:hvr:r-x $ORACLE_HOME/oradata/SID/* $ setacl –m u:hvr:r-x,d:u:hvr:r-x $ORACLE_HOME/ora_arch $ setacl –m u:hvr:r-x $ORACLE_HOME/ora_arch/*
On Solaris an extra command is needed to initialize the “default ACL”;
$ setfacl –m u:hvr:r-x $ORACLE_HOME/oradata $ setfacl –m u:hvr:r-x $ORACLE_HOME/oradata/SID $ setfacl –m u:hvr:r-x $ORACLE_HOME/oradata/SID/* $ setfacl –m d:u::rwx,d:g::r-x,d:o:---,d:m:rwx $ORACLE_HOME/ora_arch $ setfacl –m u:hvr:r-x,d:u:hvr:r-x $ORACLE_HOME/ora_arch $ setfacl –m u:hvr:r-x $ORACLE_HOME/ora_arch/* Note that sometimes a Unix file system must be mounted in /etc/fstab with option acl otherwise ACLs are not allowed. On Linux the user root can use command mount –o remount,acl to dynamically change this.
GRANTS FOR HVR ON TARGET DATABASE
If HVR will integrate changes into a database, or if HVR Refresh will load data into a database, the HVR user will need following privileges; create session and create table. If the HVR user needs to change tables which are owned by other schemas (using action TableProperties /Schema) then the following are needed; select any table, insert any table, update any table and delete any table. Bulk refresh of tables in other schemas also needs alter any table, lock any table and drop any table (needed for truncate statements). If action DbIntegrate /DbProc is defined then create procedure privilege is needed.
GRANTS FOR HVR ON COMPARE OR REFRESH SOURCE DATABASE
The HVR user always needs create session privilege. If HVR Compare or HVR Refresh needs to read from tables which are owned by other schemas (using action TableProperties /Schema) then HVR will also need select any table privilege.
- The forum ‘Expert Notes’ is closed to new topics and replies.