Oracle

The Zephyr database will be referenced during the installation. However, it must be installed separately and before the installation. These instructions will help you connect your Zephyr installation to an Oracle database.

Supported Databases

Oracle

    • Oracle Database Versions.

      • 19c

    • JDBC Driver Versions. Download JDBC: Here

      • 19c (19.24.0.0)

Prerequisites

  • Check whether your version of Oracle is supported above.

  • Download the JDBC and have this available on the application node where Zephyr is installed.

  • Have the Zephyr installer ready and a new instance for Oracle installation running.

  • Some instructions below will use Oracle SQL Developer to illustrate the steps; however, they are not required.

Configure the Oracle Database

The following steps will need to be followed:

Step 1: After installation, Set Collation and DB Parameters:

The default collation is Binary for Oracle and the one that is supported. The following query can be used to validate the collation for the user(database)

SELECT COALESCE(value, (SELECT value
                                                FROM nls_database_parameters
                                                WHERE parameter = 'NLS_SORT'))
FROM nls_session_parameters
WHERE parameter = 'NLS_SORT'

For this below setup, connect through a command console:

Type : sqlplus

Enter USER Name : sys/sysdba

Enter Password : *******

Run the below commands for Oracle database parameters setup:

Oracle must be set up with the AL32UTF8 parameter to support UTF-8 characters before installation is done. To check your charset setting:

SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

Step 2: Setup your database charset:

  1. shutdown immediate;

  2. startup restrict;

  3. Select name from v$database;

  4. View code below:

    ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

  5. View code below:

    SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter=’NLS_CHARACTERSET’;

  6. shutdown immediate;

  7. startup;

Step 3: Connect in SQL Developer:

1558436770.png

Run the Below Command for User creation:

Create Table Space:

CREATE tablespace jilo
DATAFILE 'jilo.dbf'
SIZE 2120M
AUTOEXTEND ON;

Oracle User Creation:

create user ITCC identified by zephyr default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
grant connect to ITCC;
grant create table to ITCC;
grant create a sequence to ITCC;
grant create a trigger to ITCC;
grant create a session to ITCC;
grant create view to ITCC;

****** If user was created on a default table space of not know , execute the following to give unlimited space on the current user table space *********
grant unlimited tablespace to ITCC;
************--------------------------------************
create user DVERSION identified by zephyr default tablespace <tablespace_name> quota unlimited on <tablespace_name>;
grant connect to DVERSION;
grant create table to DVERSION;
grant create sequence to DVERSION;
grant create trigger to DVERSION;
grant create session to DVERSION;


----------------------------------
Internal note: if there are any issues in permissions, use grant all for now.
----------------------------------

CREATE USER itcc IDENTIFIED BY zephyr;
GRANT ALL PRIVILEGES TO itcc;
CREATE USER dversion IDENTIFIED BY zephyr;
GRANT ALL PRIVILEGES TO dversion;

If running into table space issue try: For data files below, find the path of data files and use path value and replace the value "<example>/u01/app/oracle/oradata/XE/system.dbf<example>" below.

SELECT * FROM DBA_DATA_FILES;
ALTER DATABASE DATAFILE '<example>/u01/app/oracle/oradata/XE/system.dbf<example>' AUTOEXTEND ON MAXSIZE UNLIMITED;
In this section:
© 2024
Publication date: