Prerequisites

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 a Linux server

3. Invoke PUTTY and then connect to the Linux Server

4. Connect to the Linux server using the 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 on 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 the scripts below 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 the 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 the following database script must be executed after restoring this database. This is a mandatory step to make the SigningHub application work smoothly.

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]; 

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

DECLARE
    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

            -- Derive table name from sequence name
            v_table_name := SUBSTR(rec.sequence_name, 1, LENGTH(rec.sequence_name) - 4);

            -- Special case mappings
            IF v_table_name = 'ADMINSEARCHCRITERIA' THEN
                v_table_name := 'ADMINISTRATORSEARCHCRITERIA';
            ELSIF v_table_name = 'CONNECTIONPROVIDERPARAM' THEN
                v_table_name := 'CONNECTIONPROVIDERPARAMETER';
            ELSIF v_table_name = 'ENTERPRISECERTFILTER' THEN
                v_table_name := 'ENTERPRISECERTIFICATEFILTER';
            ELSIF v_table_name = 'ENTERPRISEDOCLIBRARY' THEN
                v_table_name := 'ENTERPRISEDOCUMENTLIBRARY';
            ELSIF v_table_name = 'GROUP' THEN
                v_table_name := '"GROUP"';
            ELSIF v_table_name = 'USER' THEN
                v_table_name := '"USER"';
            ELSIF v_table_name = 'TERMSANDCONDITION' THEN
                v_table_name := 'TERMANDCONDITION';
            END IF;

            -- Build and execute ALTER TABLE statement
            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;