Creating the Oracle Instance, Tablespace, Schema User, and Tables

You must prepare your Oracle database before using the 3DOrchestrate deployment utility.

This task shows you how to:


Before you begin: Before starting these steps, be sure you have finished installing the 3DOrchestrate Distribution Server but have not yet run the deployment utility.

Context:

This process includes the following actions:

  • Create a base database instance (optional).
  • Create a tablespace to hold the 3DOrchestrate data.
  • Create a schema user that will own the 3DOrchestrate data.
  • Verify that the database is correctly configured before continuing.

Create Database Instance, Tablespace, Schema User, and Tables

The 3DOrchestrate Distribution Server can be configured to use an existing database instance or you can create a new instance for it.

  1. You can create a new database instance using any tools you wish, including the Oracle Database Configuration Assistant. You can use the following command options if desired, which applies default settings:

    $ORACLE_HOME\bin\dbca -silent -createDatabase -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 
    -templateName $ORACLE_HOME\assistants\dbca\templates\General_Purpose.dbc -gdbName %ORACLE_SID% 
    -sid $ORACLE_SID -sysPassword <PSWD> -systemPassword <PSWD> -dbsnmpPassword <PSWD> -sysmanPassword <PSWD> 
    -emConfiguration LOCAL -storageType FS -datafileJarLocation $ORACLE_HOME\assistants\dbca\templates 
    -sampleSchema false -datafileDestination $ORACLE_HOME\oradata -recoveryAreaDestination $ORACLE_HOME\oradata\archlogs

    In this command, substitute your specific values for the following:

    $ORACLE_HOME
    Path to your Oracle software installation.
    $ORACLE_SID
    An identifier for this newly created database instance.
    <PSWD>
    Various passwords as defined in your environment.

  2. Create the tablespace.

    Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statements:

    CREATE SMALLFILE TABLESPACE "<TS_NAME>"
    DATAFILE '<Data_File_Location>/<TS_NAME>.DBF'
    SIZE <size> AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
    LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

    In these commands, substitute your specific values for the following:

    <TS_NAME>
    The name for the tablespace, for example 3DOrch_TS.
    <Data_File_Location>
    Full path to the location of your data file destination.

  3. Create the schema user.

    Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statements:

    ALTER SYSTEM SET PROCESSES=600 SCOPE=SPFILE;
    ALTER SYSTEM SET OPEN_CURSORS=2048 SCOPE=BOTH;
    ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
    CREATE USER <USER> 
      PROFILE "DEFAULT"
      IDENTIFIED BY "<PW>"
      DEFAULT TABLESPACE "<TS_NAME>"
      TEMPORARY TABLESPACE "TEMP" 
      ACCOUNT UNLOCK;
    GRANT CREATE TABLE TO <USER>;
    GRANT CREATE SESSION TO <USER>;
    GRANT UNLIMITED TABLESPACE TO <USER>;
    GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO <USER>;
    GRANT SELECT ON SYS.DBA_2PC_PENDING TO <USER>;
    GRANT SELECT ON SYS.PENDING_TRANS$ TO <USER>;
    GRANT EXECUTE ON SYS.DBMS_XA TO <USER>;

    In these commands, substitute your specific values for the following:

    <USER>
    The username you want, for example 3DOrchAdmin.
    <PW>
    The password you want.
    <TS_NAME>
    Name of the tablespace you just created.

  4. Creat the tables.

    Log in to the database instance as the schema user just created. Execute the following SQL script:

    START "<server_install_dir>\<os>\reffiles\SMAExeServer\database\oracle\createtables.sql"

    In this command, substitute your specific values for the following:

    <server_install_dir>
    Full path to the base installation directory of the 3DOrchestrate Distribution Server. For example, the default on Windows is C:\Program Files\Dassault Systemes\3DOrchestration\V6R2016x\.
    <os>
    The operating system subdirectory in <server_install_dir>. This is either \win_b64\ or /linux_a64/.

    If the 3DOrchestrate Distribution Server installation is not on this machine, copy the createtables.sql file to the Oracle database machine and execute it there.

Verify that the 3DOrchestrate Database is Configured Correctly

  1. Log in to the database instance as a user with SYSDBA privileges and execute the following SQL statement:

    select * from nls_database_parameters;

    The result of this query should show the following lines:

    ...
    NLS_CHARACTERSET AL32UTF8
    ...
    NLS_NCHAR_CHARACTERSET AL16UTF16
  2. Next execute the following statement:

    select TABLESPACE_NAME from DBA_TABLESPACES where TABLESPACE_NAME = '<TS_NAME>';

    Replace <TS_NAME> with the name of the tablespace you created earlier. The result of this query should show that the tablespace exists.

  3. Next execute this statement:

    select USERNAME from DBA_USERS where USERNAME = '<USER>';

    Replace <USER> with the name of the schema user you created earlier. The result of this query should be:

    USERNAME
    ------------------------------
    <USER>
    1 row selected.
  4. Finally, log out and then log back in as the schema user, with the schema user password you defined earlier. Verify that the tables have been properly created by executing the following SQL statement:

    select TABLE_NAME from ALL_TABLES where TABLE_NAME = 'V6FIPERSYS' AND OWNER = '<USER>';

    Replace <USER> with the name of the schema user. The result of this query should be:

    TABLE_NAME
    ------------------------------
    V6FIPERSYS
    1 row selected.