Set Up a New Aternity Oracle Database Server (for DBAs only)

Note:

The procedure in this article refers to Oracle 11 and 12 ONLY. The procedure for Oracle 18c is different (coming soon).

Use the advanced setup for the Aternity Oracle Database Server if you are a licensed Oracle DBA and want to integrate the Aternity database into your enterprise's database infrastructure, like using a specific operating system, or custom security settings. Follow these guidelines to ensure your database is compatible with the Aternity Oracle database requirements.

Tip:

If you are not a licensed Oracle DBA, and you only need a trial or small deployment, use the express database setup.

The Aternity Oracle Database Server is an Oracle database which hosts the Aternity system settings, data model and performance data, after the Data Warehouse Server summarized (aggregated) it.

Use Oracle 18.0.0.0 or 12.2.0.1 or 12.1.0.2 or 11.2.0.4 (including support for Oracle Data Guard), specifically:
  • Oracle Database version 12c Enterprise Edition 64-bit, 12.2.0.1 or 12.1.0.2 or 11.2.0.4.

  • Oracle Database version 18c Enterprise Edition 64-bit, 18.0.0.0

Select your Oracle database deployment

Oracle 12 has a root or container database (CDB), and one or more pluggable databases (PDB), one for each tenant, which plug into the root database.

Deployment Description

Non-CDB

Deploy the Aternity Oracle Database Server as a standalone legacy database, in a non-CDB architecture in Oracle 11, Oracle 12, or Oracle 18c.

CDB single tenant

Deploy the Aternity Oracle Database Server as a PDB (pluggable database) within a CDB engine in a single tenant configuration.

CDB multi-tenant

Deploy the Aternity Oracle Database Server as one of several PDBs (pluggable databases or tenants) within a CDB engine, in a multi-tenant environment.

Note:

Aternity v10.x is always a single database and does not use Oracle 12's multi-tenancy.

If you have Oracle RAC (Real Application Clusters) deployed, configure the connection string of Aternity's database service to use only a one single instance in the cluster. You can configure the failover option to use another server if required.

Therefore Aternity does NOT support RAC's load balancing and high availability features. We recommend you contact Customer Services to discuss your overall DR / high availability architecture.

When using the wizard to configure the database (Oracle Database Configuration Assistant or DBCA), you can optionally use an Aternity's template file (DBT) customized for the size of your deployment, which preconfigures default values or disables some options.

Contact customer services to download the DBT file for your deployment size.

(Optional) Use a DBT to preconfigure Oracle's configuration assistant

The Aternity Oracle Database Server needs two schemas, which Aternity creates later in the deployment, when running the setup of the Aternity Management Server (learn more).

Before you begin

The Aternity Oracle Database Server must be the first server you install when setting up Aternity on-premise, before you install the Aternity Dashboard Server or the Aternity Management Server.

This requires knowledge of a licensed Oracle DBA, and requires the following specifications:

Attribute Requirement

Oracle version for Aternity Oracle Database Server

Use Oracle 18.0.0.0 or 12.2.0.1 or 12.1.0.2 or 11.2.0.4 (including support for Oracle Data Guard), specifically:
  • Oracle Database version 12c Enterprise Edition 64-bit, 12.2.0.1 or 12.1.0.2 or 11.2.0.4.

  • Oracle Database version 18c Enterprise Edition 64-bit, 18.0.0.0

Note:

You do not need to update your Oracle if you already have one of these versions.

The Oracle license MUST allow for Partitioning, Parallel, and Advanced Compression. However the other licensing features are optional, depending on your enterprise's policy and requirements: (Diagnostics and Tuning Pack and the Active Data Guard).

You can download either version of Oracle from the Useful Links of the Aternity Support Site.

Operating system for Aternity Oracle Database Server

Windows Server 64 bit 2016 or 2012 R2.

Set the date and time formats to Control Panel > Regions > Formats > English (United States).

You can also install the Oracle Database Server on Linux CentOS or Linux Red Hat Fedora.

Hardware

Hardware specifications depend on the size of your Aternity on-premise deployment. Choose the sizing and hardware specifications for your deployment size.

Permissions

Ensure you have local administrator privileges on this computer.

Time zone

Synchronize all Aternity components to have the same date, time AND time zone.

Virtual memory

Set the pagefile size to 1.5 times the RAM in the operating system of the Aternity Oracle Database Server.

Network

Ensure this server has a static IP v4 address, and that you open the required ports.

A script automatically creates the two schemas of the Aternity Oracle Database Server during the setup of the Aternity Management Server. The two schemas are:

Schemas in the Aternity Oracle Database Server
Field Description
ATERNITY schema

This is the name of the business data schema, which stores the performance measurements and device data over the past year or two, along with contextual data, like device details and user details.

GR schema

This is the name of the system settings schema, which stores Aternity's system settings.

Procedure

  1. Step 1 Download the latest Aternity on-premise's main setup package from the Aternity Support Site by selecting Software (size) (learn more).

    Sign in to Aternity Support Site, then go to Product Downloads > Aternity Server > Version and select the required version to download. Note that only logged in users with certain permissions can see the download option.

    The setup package is for all Aternity servers.

    Download Aternity on-premise from the website
    There are also entries in the Additional Packages which are required for database setup and others:
    • Tableau Package

    • Oracle Packages

    • Install Scripts

    • DB Schema Scripts

    Refer to Additional Packages for the necessary Oracle images and other setup packages.
  2. Step 2 Select the set of files for your version of Oracle, which you downloaded from the Useful Links section.
    • For Oracle 12: Winx64_12102_database_1of2.zip and Winx64_12102_database_2of2.zip

    • For Oracle 11: Oracle_11.2.0.4.part1.exe and Oracle_11.2.0.4.part2.exe

  3. Step 3 Log in to the computer as a local administrator.

    To verify you are a local administrator on this computer, not a domain administrator, open the System control panel, select Advanced system settings > Computer Name, and confirm the Change button is active.

  4. Step 4 If you do not yet already have Oracle in your enterprise, set up the underlying Oracle server database software (Enterprise Edition), using the Oracle Universal setup 11 or 12.

    You need one of the EXACT versions compatible with Aternity 9.x:

    • Oracle Database version 12c Enterprise Edition 64-bit, 12.2.0.1 or 12.1.0.2 or 11.2.0.4.

    • Oracle Database version 18c Enterprise Edition 64-bit, 18.0.0.0

    This sets up the Oracle server only, not the Aternity database.

  5. Step 5 Launch the Oracle Database Configuration Assistant (DBCA) to create and configure the Aternity database, either with a predefined database template (DBT), or manually by entering the settings.

    (Optional) To use a DBT, contact customer services to download the DBT for your deployment size, containing the recommended default values or disabled options in the configuration wizard. If needed, you can tweak it in a plain text editor to fit your enterprise's needs.

    Copy your DBT into Oracle's assistants\dbca\templates folder.

    (Optional) Use a DBT to preconfigure the configuration assistant

    The configuration wizard displays the list of DBTs at the start, where you can choose the Aternity template if required.

    Select the Aternity DBT to create the database

    Without a DBT, you are free to manually enter the parameters during setup, giving you full control of the database setup.

  6. Step 6 Disable archive logs unless you implement a clone database for hot backups or a backup database.
    Tip:

    If you do enable these archives, ensure the disk space does not fill up, by moving them regularly.

    Disable archiving unless you have a failover database
  7. Step 7 In the configuration wizard's Initialization Parameters, select Character Sets.
    Set the character sets
    Field Description
    NLS_CHARACTERSET

    Set to AL32UTF8

    NLS_NCHAR_CHARACTERSET

    Set to AL16UTF16 (National Character Set)

  8. Step 8 Still in the configuration wizard's Initialization Parameters, select Memory > All Initialization Parameters.

    Set the following recommended values, which Aternity has tested and approved, to maximize cache performance. The cache should be large enough for repeated queries of PL/SQL.

    Set the initialization parameters
    Field Description

    compatible

    Use the default value (the version of the Oracle database). Must be 11.2.0.4 or newer.

    open_cursors

    Set to at least 1000.

    Session Cached Cursors

    Set to at least 800.

    processes

    Set to at least 500.

    parallel_max_servers

    Set to between 16 and 100.

    sessions

    Set to at least 600.

    db_block_size

    Set to at least 8KB.

    pga_aggregate_target

    Set this value based on your deployment size. See your DBT for guidance.

    sga_max_size

    Set this value based on your deployment size. See your DBT for guidance.

    sga_target

    Set this value based on your deployment size. See your DBT for guidance.

    commit_logging

    Set this value to BATCH, to improve database performance when Aternity performs heavy insert statements, such as large aggregations.

    commit_wait

    Set this value to NOWAIT, to improve database performance when Aternity performs heavy insert statements, such as large aggregations.

    recyclebin

    Set this value to off, to save storage space consumed by the recycle bin option. Aternity does not rely on the flashback drop option for recovering dropped tables.

    undo_retention

    Set this value to at least 1800.

    nls_length_semantics

    Set this value to BYTE.

    deferred_segment_creation

    Set this value to false.

  9. Step 9 Manually add two initialization parameters in an SQLPlus session.

    Add the following parameters to the All Initialization Parameters list by typing the following in an SQLPlus session:

    ALTER SYSTEM SET "_partition_large_extents"=false scope=spfile sid='*';
    ALTER SYSTEM SET "_index_partition_large_extents"=false  scope=spfile sid='*';
    Field Description

    _partition_large_extents

    Set this value to false to prevent a bloated schema containing many large extents. Aternity uses many partitions and sub-partitions in its data warehouse tables,

    _index_partition_large_extents

    Set this value to false to prevent a bloated schema containing many large extents. Aternity uses many partitions and sub-partitions in its data warehouse tables,

  10. Step 10 You can manually set up the Aternity database's tablespace properties if required.

    Most of the Aternity database stores the historical data and its indexes. The following settings ensure the smooth and efficient running of the Aternity database:

    Field Description
    Managed extents

    Set this value to local.

    Autoextend

    Set this value to on.

    Segment space management

    Set this value to Auto.

    tablespace datafile

    Define as one of the following (Aternity supports both):

    • Set this value to SMALLFILE (default) to add as many data files as needed, each up to 32GB. Monitor the size of your data files to ensure you have enough space as your stored data grows.

    • Set this value to BIGFILE to use a single file in each tablespace which can grow beyond 32GB.

  11. Step 11 Create tablespaces with the following names in the Aternity database.

    Use these default tablespace names unless your enterprise's policy requires you to change them.

    Tablespace Description
    ATERNITY_DATA

    Define ATERNITY_DATA as the default tablespace for the ATERNITY and GR users/schemas.

    It uses about 2% of the total storage.

    ATERNITY_IDX

    Define ATERNITY_IDX as the tablespace which stores all the remaining Aternity indexes which are not in ATERNITY_HD_IDX.

    It uses about 1% of the total storage.

    ATERNITY_HD

    Define ATERNITY_HD as the tablespace which stores the Aternity historical performance data and device data.

    It uses about 45% of the total storage.

    ATERNITY_HD_IDX

    Define ATERNITY_HD_IDX as the tablespace which stores the indexes of Aternity's historical data.

    It uses about 50% of the total storage.

    LOB_LARGE

    Define LOB_LARGE as the tablespace which stores large objects (BLOB/CLOB).

    It uses about 2% of the total storage.

    TEMP

    Define TEMP as the default temporary tablespace. You can configure it as one of the following (Aternity supports both):

    • SMALLFILE (default): you can have as many as needed, each up to 32GB. Monitor the size of your data files, to ensure you have enough space as Aternity increases in size. We recommend that the initial size is 2GB for each file.

    • BIGFILE: can grow beyond 32GB, but you only have a single BIGFILE in each tablespace.

    undo tablespace

    Define undo tablespace as the tablespace which caches actions which you can undo if needed. You can configure it as one of the following (Aternity supports both):

    • SMALLFILE (default): you can have as many as needed, each up to 32GB. Monitor the size of your data files, to ensure you have enough space as Aternity increases in size. We recommend that the initial size is 2GB for each file.

    • BIGFILE: can grow beyond 32GB, but you only have a single BIGFILE in each tablespace.

  12. Step 12 Continue installing the other servers, up to and including installing the Aternity Management Server, which contains a script to automatically create two schemas in the database.
    Tip:

    Contact customer services to receive the standalone script for schema creation which you (a licensed DBA) can run independently. This can be useful if your enterprise security policy discourages giving these credentials to the person installing the Management Server. The script requires the database's SYSTEM level credentials.

    Aternity database schemas
    Field Description
    ATERNITY schema

    This is the name of the business data schema, which stores the performance measurements and device data over the past year or two, along with contextual data, like device details and user details.

    GR schema

    This is the name of the system settings schema, which stores Aternity's system settings.

  13. Step 13 Configure the security permissions of the two schemas in the Aternity database.
    • Define the lifetime of passwords for both schemas (ATERNITY and GR) as Unlimited, so they do not expire. By default, the passwords expire after 180 days.

    • Grant the following privileges DIRECTLY (not via a role) to the ATERNITY and GR schemas: CREATE TABLE, CREATE VIEW, and CREATE JOB.

    • Grant the following privileges either directly or via a role to the ATERNITY and GR schemas: CREATE SESSION, CREATE SEQUENCE, CREATE CLUSTER, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, CREATE OPERATOR, and CREATE INDEXTYPE.

    • The ATERNITY schema must have EXECUTE privileges on the following Oracle supplied packages: DBMS_SQL, DBMS_LOB, DBMS_STATS, OWA_Util , DBMS_RANDOM, DBMS_OUTPUT, and DBMS_SCHEDULER.

  14. Step 14 Disable the default Oracle task for gathering statistics, as Aternity has its own.

    Use any SQL client connected to the database to perform the following command:

    exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

  15. Step 15 Run the Aternity Database Verification Utility from the Aternity Management Server to ensure the database has the required schemas and is ready for Aternity.

    To run this utility from a different computer, your Oracle Database Server must have a Listener (the express database setup does this automatically). Learn more by searching about adding a Listener in Oracle's documentation.