Skip to main content

Oracle

We will be using a Linux Oracle machine which already has the Oracle database set up

Prerequisites​

You will need sudo privileges for the following steps.

Create a database​

  1. Log in to the oracle user which is already set up, for example, if a user 'oracle' is already set up, then log in as:

    sudo su - oracle

  2. Start the database listener

    lsnrctl start

  3. Create a data directory for the oracle data files (here mount point is assumed to be /u01 , it could be anything like /u02 on your machine)

    mkdir /u01/oradata

  4. Run the Database Creation Assistant:

    dbca -silent \
    -createDatabase \
    -templateName General_Purpose.dbc \
    -gdbname <your_db_name> \
    -sid <your_db_name> \
    -responseFile NO_VALUE \
    -characterSet AL32UTF8 \
    -sysPassword <your_password> \
    -systemPassword <your_password> \
    -createAsContainerDatabase false \
    -databaseType MULTIPURPOSE \
    -automaticMemoryManagement false \
    -storageType FS \
    -datafileDestination "/u02/oradata/" \
    -ignorePreReqs

    Replace <your_db_name> with the name you want for your database, and replace <your_password> with the password you want to set

  5. Set oracle variables required to connect (Here we assume your database name is "tdms_test"):

    export ORACLE_SID=tdms_test

    You should also add the ORACLE_SID variable to the oracle users .bashrc file for future sign-ins using the following command:

    echo "export ORACLE_SID=tdms_test" >> ~oracle/.bashrc

    Here again we assume that the user name is "oracle"

  6. Log in to sqlplus shell:

    sqlplus sys as sysdba

    and enter the password when prompted

Create a user​

  1. Log in to sqlplus shell:

    sqlplus sys as sysdba

    and enter the password when prompted

  2. Create a user:

    SQL> CREATE USER tdms_user IDENTIFIED BY yoursupersecretpassword DEFAULT TABLESPACE users QUOTA UNLIMITED ON users PASSWORD EXPIRE;

    Here we are creating a user named "tdms_user" and password as "yoursupersecretpassword"

  3. Create a role for the same user:

    SQL> CREATE ROLE tdms_user_role;

    Here we are naming the role as "tdms_user_role"

  4. Grant permissions to the role

    SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE TRIGGER to tdms_user_role;

  5. Grant the role to the user

    SQL> GRANT tdms_user_role TO tdms_user;

Allow remote connections on Oracle​

  1. Open your listener.ora file which is located at $ORACLE_HOME/network/admin/, and set the host as the IP of your CloudTDMS server as follows:

    ...
    LISTENER =
        (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = <your_ip_here>)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
            )
        )
    ...

    Here you need to replace "<your_ip_here>" to the IP of your CloudTDMS server

  2. After changing the configuration, restart the listener service:

    lsnrctl start

  3. Additionally you might need to configure firewall on the host. For this tutorial you can shutdown the firewall using the following command

    sudo systemctl stop firewalld

Setting up your Oracle storage on CloudTDMS​

Open the storages tab and insert the following values for your new Oracle storage

  • Name: Name for the storage.
  • Hostname: Domain Name or IPv4 address of your Oracle server.
  • Port: The port on which your Oracle server is listening for connections (default is 1521, cam be found in the listener.ora file).
  • Username: Username of your Oracle server.
  • Password: Password of your Oracle user.
  • Database: The database name.