Install Oracle and PostgreSQL databases

The whitepaper describes which parameters to set so that customers and partners can install Oracle, PostgreSQL, and AWS-RDS PostgreSQL databases on their own.

Parameter for Oracle database 

General recommendations 

Please follow these general recommendations for each Oracle database installation: 

1. Parameter "NLS_CHARACTERSET" value must be "AL32UTF8".

2. Parameter “NLS_LENGTH_SEMANTICS” value must be “BYTE” and must also be specified as “Byte” by default.

3. The application server uses only a dedicated server process and has its own connection pool mechanism. No dispatchers.

4. Parameter “PROCESSES” must be extended from default 300 to (number of censhare Servers * censhare parameter max-open JDBC connections + number of background Oracle processes).Example: for 3 censhare Servers, the number of Oracle processes should be set to 500.

5. Also, the parameter “open-cursors” must be extended and set from the default of 50 to 300 in the database.

6. Operating system “Timezone” should be the same as the master “censhare server”. It should be “UTC” or “local timezone”.

7. At least 5 x 200MB redo log groups. For mission-critical environments the groups should be multiplexed.

8. SGA can be ranged from 1GB for small DEV servers to 60 GB for globally distributed environments with several hundred concurrent censhare users. Key factor for the SGA sizing is the number of censhare assets, features and relations.

9. Censhare v5 only requires one tablespace, which we usually name CORPUS. Sizing estimates range from 20 to 100 GB growth per year.

10. Grant connect, resource, select_catalog_role on the application user named "corpus".

11. Strict separation on the login to PROD from DEV schemas (aka. sourceIP -> DBschema pairs). For example: If PROD and DEV schemas are in the same database, then a login trigger should allow login to the PROD schema only from the IP on the PROD servers. If DEV and CONS schemas are in different databases, then firewall or listener rules (validnode_checking) on the PROD database should allow login only from the IP address on the PROD censhare servers.

12. Monitoring with thresholds, alerts and usage charts on Tablespace and FRA usage, listener and database availability. For example, Icinga, Enterprise Manager etc. 

Since censhare version 2019.3 and if Oracle is version 12c, also set the parameter below in the Oracle database:

MAX_STRING_SIZE: Extended

This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database. We can set it to either STANDARD or EXTENDED. The default value is STANDARD.

https://dbaclass.com/article/max_string_size-parameter-oracle-12c/

Note:  Except all of the above parameters, we assume the default values according to the following Oracle database reference documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/initialization-parameters-2.html

Parameter for PostgreSQL database 

1. “timezone” and “log_timezone” parameter values in the “postgresql.conf” file should be identical to the master “censhare server”. It should be “UTC” or “local timezone”. 

2. While creating the PostgreSQL database, we also need to create a “type cast” in the database from the “super” user like “postgres”.

   XML

   --implicit type casts
   -- known case: master data removing, cast rowid varchar in Java to int in PG
   CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
   -- known case: #3128479
   CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;  

Since  censhare version 2019.2, we no longer use the implicit type casts. You should only use encoding UTF8 in the “postgresql-create-user” script. So it is no longer necessary to create type casts in the database since censhare version 2019.2.  “postgresql-creat-user.sql” script for community version of PostgreSQL.

“postgresql-creat-user.sql” script for community version of PostgreSQL

CREATE USER corpus PASSWORD 'corpus';
CREATE DATABASE corpus OWNER corpus TEMPLATE template0 ENCODING 'UTF8';

\c corpus-- implicit type casts
-- known case: master data removing, cast rowid varchar in Java to int in PG
CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
-- known case: #3128479
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;[corpus@cs-test20192-css01 database]$

“postgresql-creat-user.sql” script for AWS RDS PostgreSQL or AWS Aurora PostgreSQL

CREATE USER corpus PASSWORD 'corpus'; 
grant corpus to postgres; 
CREATE DATABASE corpus OWNER corpus TEMPLATE template0 ENCODING 'UTF8'; \c corpus postgres  
-- implicit type casts 
-- known case: master data removing, cast rowid varchar in Java to int in PG alter type varchar owner to postgres; 
CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT; 
-- known case: #3128479
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;