Prerequisites

  1. Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production should be installed
  2. Download “PUTTY”, it’s a software to connect to Linux server
  3. Invoke PUTTY and then connect to Linux Server
  4. Connect to LINUX server using ORACLE operating system user name and password
  5. Execute the below command to connect the “sys” user. “sys” is an administrative user account that is automatically created when you install Oracle Database. It is created with the password that you supplied upon installation, and it is automatically granted the DBA role.

    sqlplus / as sysdba
  6. The following is the script to create a Tablespace. An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

    SQL> CREATE TABLESPACE [Tablespace Name] DATAFILE '[Database file location]/[File Name].dbf' 
    SIZE 1000M AUTOEXTEND ON NEXT 50M MAXSIZE 30G
    EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT AUTO;

Placeholder Names

Description

[Tablespace Name]

It should be a unique name not greater than 30 characters. It will be used as the default tablespace for the database user.

[Database file location]

A path which was given during oracle installation in the Step 6. The screenshot is given below, if you specify a path other than the one mentioned in the screenshot, an error may appear prompting for permissions on the path. Oracle user needs the permissions to write on the provided file path.

[File Name]

Name of the file to be created. The naming convention of the OS should be followed.




  1. The following is the script to create a user. This user is used to connect to the SigningHub application. When you create user accounts, you can specify limits on these user accounts. You can also set limits on the amount of various system resources available to each user as part of the security domain of that user. Execute below scripts step by step.
    • SQL> CREATE USER [User Name] IDENTIFIED BY [Password] 
      DEFAULT TABLESPACE [Tablespace Name];
    • SQL> ALTER USER [User Name] QUOTA UNLIMITED ON [Tablespace Name];
    • SQL> GRANT UNLIMITED TABLESPACE TO [User Name];        

Placeholder Names

Description

[User Name]

It should be a unique name not greater than 30 characters. It will be used for the SigningHub installation.

[Password]

It should not be greater than 30 characters. It is used to authenticate user.

[Tablespace Name]

Replace this placeholder with the name that you have used to create tablespace in the step 6 above.


  1. Execute the below commands step by step to create SigningHub role and grant this role to the user created above. The roles are created by users (usually administrators) and are used to grant the group level privileges. 

    SQL> CREATE ROLE [Role Name];
    SQL> GRANT CREATE ROLE, RESOURCE, CONNECT, CREATE PUBLIC SYNONYM, CREATE VIEW TO [Role Name];
    SQL> GRANT [Role Name] TO [User Name];

Placeholder Names

Description

[Role Name]

It should be a unique name not greater than 30 characters. It will be used to grant privileges to database user.

[User Name]

Replace this placeholder with the name that you have used to create the User in the step 7 above.

[Tablespace Name]

Replace this placeholder with the name that you have used to create tablespace in the step 6 above.


  1. If the database is a backup of an existing database and not a newly created database, then following database script must  be executed after restoring this database. This is a manadtory step to make SigningHub application works smoothly.


Oracle script (to be executed manually), if a database is restored on database server.



DECLARE 

  v_str                         VARCHAR2 (1000);

  v_table_name   varchar2(500);

  v_str_alter                    VARCHAR2 (1000);

BEGIN

  FOR rec IN (SELECT *  FROM   user_sequences where LOWER(SEQUENCE_NAME) not like LOWER('%ID_SEQ') 

  and  LOWER(SEQUENCE_NAME) not like LOWER('SEQ_%') and  LOWER(SEQUENCE_NAME) not like LOWER('QUEST_%')

  ) LOOP

IF rec.sequence_name NOT IN ('SEQ_COUNTER','SEQ_HASHTABLE', 'SEQ_JOB','SEQ_JOBPARAMETER','SEQ_JOBQUEUE','SEQ_LISTTABLE','SEQ_SETTABLE','SEQ_STATE') THEN 

    v_str                  := 'select SUBSTR('''||rec.sequence_name||''', 1, LENGTH('''||rec.sequence_name||''') - 4) from dual';

      EXECUTE IMMEDIATE v_str

      INTO              v_table_name;

      if v_table_name = 'ADMINSEARCHCRITERIA' then

          v_table_name := 'ADMINISTRATORSEARCHCRITERIA';

      end if;

      if v_table_name = 'CONNECTIONPROVIDERPARAM' then

          v_table_name := 'CONNECTIONPROVIDERPARAMETER';

      end if;

      if v_table_name = 'CONNECTIONPROVIDERPARAM' then

          v_table_name := 'CONNECTIONPROVIDERPARAMETER';

      end if;   
   

      if v_table_name = 'ENTERPRISECERTFILTER'  then

          v_table_name := 'ENTERPRISECERTIFICATEFILTER';

      end if;
      

     if v_table_name = 'ENTERPRISEDOCLIBRARY' then

          v_table_name := 'ENTERPRISEDOCUMENTLIBRARY';

      end if;

      if v_table_name = 'GROUP' then

          v_table_name := '"GROUP"';

      end if;

      if v_table_name = 'USER' then

          v_table_name := '"USER"';

      end if;

        if v_table_name = 'TERMSANDCONDITION' then

          v_table_name := 'TERMANDCONDITION';

      end if;

    -- SET DEFAULT VALUE

    v_str_alter                      := 'alter table '||v_table_name||' modify ID DEFAULT ON NULL '|| rec.sequence_name ||'.nextval NOT NULL ENABLE';

    EXECUTE IMMEDIATE v_str_alter;
    

end if;

  END LOOP;

END;






See also