Configure Oracle external database

1.1 Edition

Note: when the jar package version is lower than 2019-05-20, the user can only migrate data when configuring the external database, and cannot directly enable the new database.

1.2 Function introduction

After the BI system is configured with an external database, it can ensure the stability of the BI system in case of a cluster environment or a large amount of data.

The user can configure the external database in "initialization" or configure the external database of Oracle version in "platform".

Note: if an external database is configured, do not easily modify the user name and password of the external database, otherwise the project will fail to start.

       For modification, please refer to：Modify the external database account password .

2.1 Database version

The types and versions supported by the external database are shown in the following table:

2.2 hardware configuration

Note 1: the occupied space needs to be adjusted in combination with the usage. It can be configured according to the size of the webapps / Webroot / WEB-INF / embedded / finedb folder, and the future use size can be estimated according to the actual size, increasing by 1-2 times.

Note 2: the recommended memory is the memory reserved for the engineering configuration library.

Note 3: the external database shall be in the same network segment as the Bi project as much as possible to avoid network fluctuation and other problems.

2.3 New database

2.3.1 New account

For Oracle database, it is better to create a separate account before configuration (create a new tablespace and specify it as the default tablespace for the user).

New user：

CREATE USER "FINEDB" IDENTIFIED BY "123456" ACCOUNT UNLOCK DEFAULT TABLESPACE "USERS"
Note: the user name of oracle12c version needs to be prefixed with C # #.
Grant permission：
GRANT "CONNECT","RESOURCE" TO "FINEDB"
ALTER USER "FINEDB" QUOTA UNLIMITED ON "USERS"

2.3.2 New database

Create a new finedb database in Oracle for storing migration data. The database name in the subsequent external database configuration needs to be filled with the database name corresponding to the migration storage.

Note 1: different finebi projects cannot share the same external database, otherwise the data may be disordered.

Note 2: when migrating the finedb database, the tablespace required should be empty.

2.4 Replace the drive

This method is applicable to configuring external database when logging in data decision system for the first time.

3.1 Enter the initialization interface

The user can configure the external database when entering the platform for the first time, as shown in the following figure:

3.2 Configure external database

Click "configure database" to enter the external database configuration interface and enter the corresponding information of the actual database. As shown in the following figure:

The description of each setting item is shown in the following table:

Setting item	Explain
Database type	Select oracle
Drive	Automatic configuration without modification
Database name	Section 2.3 new database name It is forbidden to share the database with other projects. It is recommended to create a new database The database name can only contain numbers, letters, underscores and "."
User name / host / password / port	Fill in according to the actual situation of the local database The host name can only contain numbers, letters, underscores, "-" and "." Users need to have create, delete, alter, update, select and insert permissions
pattern	1）Bi projects of version 5.1.15 and later only support drop-down selection mode After completing the above settings correctly, click "connect to database" in the mode drop-down box. The system will automatically connect to the database and read the mode. Select it (try to select the mode with the same name as the database user) Note: if you are prompted that the database connection fails, please check the above settings. 2）Bi projects before 5.1.15 only support manual input mode The name of the schema is the same as the name of the database user (upper case is recommended for Oracle user name and schema)
URL	The database connection URL supports three writing methods: 1）Oracle stand alone jdbc:oracle:thin:@<host>:<port>:<SID>   2）Oracle cluster Writing method I：jdbc:oracle:thin:@//<host>:<port>/<service_name> Writing method II：jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.2)(PORT=1521)))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxrac))) Note: projects after version 10.0.11 support cluster writing method 2.

3.3 Configuration succeeded

Wait for some time, and you will be prompted that Successfully enabled the new database . As shown in the following figure:

Note: do not refresh the page during migration.

This method is applicable to configuring external database for the first time after entering the data decision system.

4.1Access platform

The administrator logs in to the data decision system and clicks "manage> System  > General > external database > to be configured", as shown in the following figure:

4.2 Configure external database

Input the corresponding information of the actual database and configure the external database. As shown in the following figure:

The description of each setting item is shown in the following table:

Setting item	Explain
Database type	Select oracle
Drive	Automatic configuration without modification
Database name	Section 2.3 new database name It is forbidden to share the database with other projects. It is recommended to create a new database The database name can only contain numbers, letters, underscores and "."
User name / host / password / port	Fill in according to the actual situation of the local database The host name can only contain numbers, letters, underscores, "-" and "." Users need to have create, delete, alter, update, select and insert permissions
pattern	1）Bi projects of version 5.1.15 and later only support drop-down selection mode After completing the above settings correctly, click "connect to database" in the mode drop-down box. The system will automatically connect to the database and read the mode. Select it (try to select the mode with the same name as the database user) Note: if you are prompted that the database connection fails, please check the above settings. 2）5.1.15 之前版本的 BI 工程，仅支持手动输入模式 The name of the schema is the same as the name of the database user (upper case is recommended for Oracle user name and schema)
URL	The database connection URL supports three writing methods: 1）Oracle stand alone jdbc:oracle:thin:@<host>:<port>:<SID> 2）Oracle cluster Writing method I：jdbc:oracle:thin:@//<host>:<port>/<service_name> 写法二：jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.2)(PORT=1521)))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxrac))) Note: projects after version 5.1.8 support cluster writing method 2.

4.3 Configuration succeeded

After waiting for a period of time, a pop-up prompt will pop up: the target database has been successfully enabled. Click OK. As shown in the following figure:

Note 1: do not refresh the page during migration.

Note 2: there is no need to restart after the database configuration is successful.

This method is applicable to migrating to a new database after the external database has been configured.

5.1 Access platform

The administrator logs in to the data decision system and clicks "Manage > System > General > external database > configured", as shown in the following figure:

5.2 Configure external database

Input the corresponding information of the actual database and configure the external database. As shown in the following figure:

The description of each setting item is shown in the following table:

Setting item	Explain
Database type	Select  oracle
Drive	Automatic configuration without modification
Database name	Section 2.3 new database name It is forbidden to share the database with other projects. It is recommended to create a new database The database name can only contain numbers, letters, underscores and "."
User name / host / password / port	Fill in according to the actual situation of the local database The host name can only contain numbers, letters, underscores, "-" and "." Users need to have create, delete, alter, update, select and insert permissions
pattern	1）Bi projects of version 5.1.15 and later only support drop-down selection mode After completing the above settings correctly, click "connect to database" in the mode drop-down box. The system will automatically connect to the database and read the mode. Select it (try to select the mode with the same name as the database user) Note: if you are prompted that the database connection fails, please check the above settings. 2）Bi projects before 5.1.15 only support manual input mode The name of the schema is the same as the name of the database user (upper case is recommended for Oracle user name and schema)
URL	The database connection URL supports three writing methods: 1）Oracle stand alone jdbc:oracle:thin:@<host>:<port>:<SID> 2）Oracle cluster Writing method I：jdbc:oracle:thin:@//<host>:<port>/<service_name> Writing method II：jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.2)(PORT=1521)))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxrac))) Note: projects after version 10.0.11 support cluster writing method 2.

Description of the "migrate data to the database to be enabled" button:

1）Check "migrate data to the database to be enabled"

• When the database is switched, the data of the original external database will be imported into the new database, and the historical data backup file will not be generated under the project.

• If platform data already exists in the new database, the original platform data will be cleared before importing data. The prompt is shown in the following figure:

2）Uncheck "migrate data to the database to be enabled"

• If the new external database is empty, it prompts that the current data must be migrated to the target database.

• If the new external database is not empty

       Before migration, the configuration information of the original database will be automatically backed up, and the db.properties.bak file will be generated in the webapps \ Webroot \ WEB-INF \ config directory. As shown in the following figure:

       The Bi project of version 5.1.15 and later will automatically detect whether the jar package version corresponding to the data in the target database is consistent with the jar package version of the current project.

       If it is inconsistent, a prompt will pop up: unable to enable! Project version required to enable the new database: XXX. Current project version: XXX. Please ensure the project version is consistent!

       If the jar package version is consistent, directly enable the new database. After the migration is successful, the prompt is as shown in the following figure:

5.3 Migration succeeded

After waiting for a period of time, a pop-up prompt will pop up: successfully switched to the target database! If there is data difference between the old and new databases, the system operation may be affected. It is recommended to restart the project to ensure normal use.

Click "detemine" and restart the Bi project. The migration is successful. As shown in the following figure:

Note: do not refresh the page during migration.

Tips: