Hubble 2018.0 - Hubble Suite Replication Account ... AWS
Replication Account Installation & Maintenance Guide for Oracle EBS DBAs Version 2018.0 Hubble Suite
Document Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
i
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i Copyright . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i Disclaimer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i Version History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i Customer Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i
Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Accelerator Database Maintenance Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Hubble Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Oracle and Database User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Replication Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Replication Script Breakdown for Reporting in Hubble . . . . . . . . . . . . . . . . . . . . . 4
Document Information | Notices
Document Information Notices Copyright Hubble® is a brand name of the insightsoftware.com Group. insightsoftware.com is a registered trademark of insightsoftware.com Limited. Hubble is a registered trademark of insightsoftware.com International Unlimited. Other product and company names mentioned herein may be the trademarks of their respective owners. The insightsoftware.com Group is the owner or licensee of all intellectual property rights in this document, which are protected by copyright laws around the world. All such rights are reserved. The information contained in this document represents the current view of insightsoftware.com on the issues discussed as of the date of publication. This document is for informational purposes only. insightsoftware.com makes no representation, guarantee or warranty, expressed or implied, that the content of this document is accurate, complete or up to date.
Disclaimer This guide is designed to help you to use the Hubble applications effectively and efficiently. All data shown in graphics are provided as examples only. The example companies and calculations herein are fictitious. No association with any real company or organization is intended or should be inferred.
Version History Date
Software Version
Revision
Comments
4th Jan 2018
1.0
2016.1
Initial issue.
16th Jan 2018
1.1
2018.0
Minor amendments.
Customer Support For more information regarding our products, please contact us at http://gohubble.com/. For product support including Training, Documentation and Customer Support, visit our Hubble Community at https://community.gohubble.com/.
Page i
Procedures Introduction Hubble is an intuitive, real time, SQL-based tool that facilitates reporting on Oracle EBS Applications for end users. To function, Hubble requires a one-off installation of a custom schema, typically called Hubble, on the applications database. The information provided in this document is related to the Hubble schema only. Replication of the EBS data from Oracle to the Hubble accelerator (Actian Vector database) is performed using Attunity. Attunity connects to the oracle database using the user account created by running this script.
Accelerator Database Maintenance Tasks This document is directed to Oracle Applications DBAs and is intended as a reference, overview and description of a Hubble Suite (Accelerator) installation on an Oracle EBS Applications database. The installation process uses a script to set up the Hubble custom schema. The impact on the Oracle EBS Application’s database, security and performance is outlined in this document. Notice Customers should be aware that creating a new schema on an Oracle database can be considered a customization and should ensure that their current software licensing agreement with Oracle Corporation entitles them to perform this activity.
Prerequisites ●
Creation of a tablespace for Hubble (optional)
●
Hubble Replication Script
●
Oracle SYS access
●
Oracle e-Business Suite APPS access (for HR and Payroll, also more generally for EBS R12.2 and above)
Hubble Installation Oracle and Database User This install requires SYS access to your Oracle database and involves the creation or definition of a database user and a corresponding schema normally defined as HUBBLE. Using the HR and Payroll modules in Hubble will require the use of the APPS user in addition to the HUBBLE user in order to allow Oracle HR Security to be upheld. The use of the APPS user is also required to complete the setup script when e-Business Suite is at version R12.2 or above. The key requirement is that online patching is implemented and the APPS.AD_ZD package is installed. This package is used to grant privileges in a way that takes account of the ‘editioning’ functionality introduced by Oracle to support online patching.
Page 1
Procedures | Hubble Installation
Replication Script The script creates a user/schema, necessitating whoever is running it to select a username, password, and default and temporary tablespaces. A pre-existing username is an optional entry. There are various prompts when running the script. 1. Run the script while logged in to the e-Business Suite database with the SYS user. 2. Enter the desired username and password:
3. If you have a pre-existing username you can enter this username in this field to pick up any grants that have been applied (useful for upgrades); leave blank otherwise.
4. Enter default and temporary tablespaces. If the below fields are left blank, then the SYSTEM and TEMP tablespaces will be used.
5. Reply Yes or No (Y/N) to four questions: a. The first question defaults to ‘Y’. Attunity Replicate needs to read from both the online and redo logs this is achieved by using Logminer or (BFILE) Oracle Binary Reader API, each method requires
Page 2
Procedures | Hubble Installation
access to different views and permissions to be granted to the Hubble user. Enter “Y” for Logminer and “N” for BFile.
b. The second question also defaults to ‘Y’. This setting gives the Hubble user the right to ALTER tables to add supplemental log data on a column by column basis. If set to ‘N’, supplemental logging will be added to ALL columns as part of running this script.
c.
The third question also defaults to ‘Y’. This allows access to the standby log view.
d. The fourth questions defaults to ‘N’. When set to ‘Y’ is allows access to the transportable platform view.
Page 3
Procedures | Hubble Installation
6. (R12.2 and above) Switch to APPS user – supply password to continue.
The replication script will perform various GRANT privileges on the newly-created database user, including granting permission to create views. A breakdown of what the script does is detailed below.
Replication Script Breakdown for Reporting in Hubble 1. Create user and grant privileges (Lines 1 to 79): a. Gather new user name and password information. b. Check if the user we want to create exists. If it does not exist, create it. c.
Grant basic connection privileges: i.
Connect.
ii.
Create View. We need to create a view for the replication tool to query, explained below.
2. Grant access to logging information used in replication (Lines 80 to 136): a. Grant LogMiner access (see question 1 of 4 above). b. Grant Standby Database Log access (see question 3 of 4 above). c.
Grant Redo Log access on ASM (see question 4 of 4 above).
3. Grant access to tables, views and procedures needed (Lines 136 to 1713): a. Definition of containers to store tables and/or procedures to grant access to: i.
SELECT_COMMON – Grant select on EBS 11i and R12.
ii.
SELECT_SYS_COMMON – Grant select on EBS 11i and R12 to views belonging to SYS.
iii. SELECT_11I – Grant select on EBS 11i only. iv. SELECT_R12 - Grant select on EBS R12 only. b. Logic to read each of the containers, depending on the EBS version with we are running the script, and Grant Select privileges to the Hubble user. Page 4
Procedures | Hubble Installation
c.
Definition of containers to store tables and/or procedures to grant access to: i.
ALTER_COMMON – Grant alter on EBS 11i and R12.
ii.
ALTER_11I – Grant alter on EBS 11i only.
iii. ALTER_R12 - Grant alter on EBS R12 only. d. Logic to read each of the containers, depending on the EBS version with we are running the script, and Grant Alter privileges to the Hubble user. 4. Grant access to the additional tables which the “old user” has access to (Lines 1715 to 1782). 5. Connect as the Hubble user and create a view for Index Discovery (Lines 1784 to 1809). This view is designed to be selected by Attunity Replicate in place of the SYS_ALL_INDEXES view. It ensures that Attunity does not select a unique key that can have a nullable column. This is achieved by removing the indexes from the result set returned by querying the view, that can have a nullable column in this view. 6. For R12.2 and above, where package APPS.AD_ZD is present, connect as APPS and grant ALTER to various tables using the GRANT_PRIVS() method (Lines 1811 to the end). a. Check for existence of APPS.AD_ZD package and quit if not present by raising an error. b. Connect as APPS, prompting the script runner for the password. c.
Definition of containers to store tables and/or procedures to grant access to: i.
ALTER_R12 –Grant alter on EBS R12.
ii.
Logic to read the container and Grant Alter privileges to the Hubble user.
Page 5
Document Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
i
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i Copyright . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i Disclaimer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i Version History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i Customer Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .i
Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Accelerator Database Maintenance Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Hubble Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Oracle and Database User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Replication Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Replication Script Breakdown for Reporting in Hubble . . . . . . . . . . . . . . . . . . . . . 4
Document Information | Notices
Document Information Notices Copyright Hubble® is a brand name of the insightsoftware.com Group. insightsoftware.com is a registered trademark of insightsoftware.com Limited. Hubble is a registered trademark of insightsoftware.com International Unlimited. Other product and company names mentioned herein may be the trademarks of their respective owners. The insightsoftware.com Group is the owner or licensee of all intellectual property rights in this document, which are protected by copyright laws around the world. All such rights are reserved. The information contained in this document represents the current view of insightsoftware.com on the issues discussed as of the date of publication. This document is for informational purposes only. insightsoftware.com makes no representation, guarantee or warranty, expressed or implied, that the content of this document is accurate, complete or up to date.
Disclaimer This guide is designed to help you to use the Hubble applications effectively and efficiently. All data shown in graphics are provided as examples only. The example companies and calculations herein are fictitious. No association with any real company or organization is intended or should be inferred.
Version History Date
Software Version
Revision
Comments
4th Jan 2018
1.0
2016.1
Initial issue.
16th Jan 2018
1.1
2018.0
Minor amendments.
Customer Support For more information regarding our products, please contact us at http://gohubble.com/. For product support including Training, Documentation and Customer Support, visit our Hubble Community at https://community.gohubble.com/.
Page i
Procedures Introduction Hubble is an intuitive, real time, SQL-based tool that facilitates reporting on Oracle EBS Applications for end users. To function, Hubble requires a one-off installation of a custom schema, typically called Hubble, on the applications database. The information provided in this document is related to the Hubble schema only. Replication of the EBS data from Oracle to the Hubble accelerator (Actian Vector database) is performed using Attunity. Attunity connects to the oracle database using the user account created by running this script.
Accelerator Database Maintenance Tasks This document is directed to Oracle Applications DBAs and is intended as a reference, overview and description of a Hubble Suite (Accelerator) installation on an Oracle EBS Applications database. The installation process uses a script to set up the Hubble custom schema. The impact on the Oracle EBS Application’s database, security and performance is outlined in this document. Notice Customers should be aware that creating a new schema on an Oracle database can be considered a customization and should ensure that their current software licensing agreement with Oracle Corporation entitles them to perform this activity.
Prerequisites ●
Creation of a tablespace for Hubble (optional)
●
Hubble Replication Script
●
Oracle SYS access
●
Oracle e-Business Suite APPS access (for HR and Payroll, also more generally for EBS R12.2 and above)
Hubble Installation Oracle and Database User This install requires SYS access to your Oracle database and involves the creation or definition of a database user and a corresponding schema normally defined as HUBBLE. Using the HR and Payroll modules in Hubble will require the use of the APPS user in addition to the HUBBLE user in order to allow Oracle HR Security to be upheld. The use of the APPS user is also required to complete the setup script when e-Business Suite is at version R12.2 or above. The key requirement is that online patching is implemented and the APPS.AD_ZD package is installed. This package is used to grant privileges in a way that takes account of the ‘editioning’ functionality introduced by Oracle to support online patching.
Page 1
Procedures | Hubble Installation
Replication Script The script creates a user/schema, necessitating whoever is running it to select a username, password, and default and temporary tablespaces. A pre-existing username is an optional entry. There are various prompts when running the script. 1. Run the script while logged in to the e-Business Suite database with the SYS user. 2. Enter the desired username and password:
3. If you have a pre-existing username you can enter this username in this field to pick up any grants that have been applied (useful for upgrades); leave blank otherwise.
4. Enter default and temporary tablespaces. If the below fields are left blank, then the SYSTEM and TEMP tablespaces will be used.
5. Reply Yes or No (Y/N) to four questions: a. The first question defaults to ‘Y’. Attunity Replicate needs to read from both the online and redo logs this is achieved by using Logminer or (BFILE) Oracle Binary Reader API, each method requires
Page 2
Procedures | Hubble Installation
access to different views and permissions to be granted to the Hubble user. Enter “Y” for Logminer and “N” for BFile.
b. The second question also defaults to ‘Y’. This setting gives the Hubble user the right to ALTER tables to add supplemental log data on a column by column basis. If set to ‘N’, supplemental logging will be added to ALL columns as part of running this script.
c.
The third question also defaults to ‘Y’. This allows access to the standby log view.
d. The fourth questions defaults to ‘N’. When set to ‘Y’ is allows access to the transportable platform view.
Page 3
Procedures | Hubble Installation
6. (R12.2 and above) Switch to APPS user – supply password to continue.
The replication script will perform various GRANT privileges on the newly-created database user, including granting permission to create views. A breakdown of what the script does is detailed below.
Replication Script Breakdown for Reporting in Hubble 1. Create user and grant privileges (Lines 1 to 79): a. Gather new user name and password information. b. Check if the user we want to create exists. If it does not exist, create it. c.
Grant basic connection privileges: i.
Connect.
ii.
Create View. We need to create a view for the replication tool to query, explained below.
2. Grant access to logging information used in replication (Lines 80 to 136): a. Grant LogMiner access (see question 1 of 4 above). b. Grant Standby Database Log access (see question 3 of 4 above). c.
Grant Redo Log access on ASM (see question 4 of 4 above).
3. Grant access to tables, views and procedures needed (Lines 136 to 1713): a. Definition of containers to store tables and/or procedures to grant access to: i.
SELECT_COMMON – Grant select on EBS 11i and R12.
ii.
SELECT_SYS_COMMON – Grant select on EBS 11i and R12 to views belonging to SYS.
iii. SELECT_11I – Grant select on EBS 11i only. iv. SELECT_R12 - Grant select on EBS R12 only. b. Logic to read each of the containers, depending on the EBS version with we are running the script, and Grant Select privileges to the Hubble user. Page 4
Procedures | Hubble Installation
c.
Definition of containers to store tables and/or procedures to grant access to: i.
ALTER_COMMON – Grant alter on EBS 11i and R12.
ii.
ALTER_11I – Grant alter on EBS 11i only.
iii. ALTER_R12 - Grant alter on EBS R12 only. d. Logic to read each of the containers, depending on the EBS version with we are running the script, and Grant Alter privileges to the Hubble user. 4. Grant access to the additional tables which the “old user” has access to (Lines 1715 to 1782). 5. Connect as the Hubble user and create a view for Index Discovery (Lines 1784 to 1809). This view is designed to be selected by Attunity Replicate in place of the SYS_ALL_INDEXES view. It ensures that Attunity does not select a unique key that can have a nullable column. This is achieved by removing the indexes from the result set returned by querying the view, that can have a nullable column in this view. 6. For R12.2 and above, where package APPS.AD_ZD is present, connect as APPS and grant ALTER to various tables using the GRANT_PRIVS() method (Lines 1811 to the end). a. Check for existence of APPS.AD_ZD package and quit if not present by raising an error. b. Connect as APPS, prompting the script runner for the password. c.
Definition of containers to store tables and/or procedures to grant access to: i.
ALTER_R12 –Grant alter on EBS R12.
ii.
Logic to read the container and Grant Alter privileges to the Hubble user.
Page 5
