4 Managing the Database
Suspending Processing When Performing Database Maintenance
When performing maintenance on the database, it is recommended that you manually stop RUEI processing for the time that the database is down. This ensures that the error messages are not displayed during maintenance. To manually stop RUEI processing, do the following:
-
Use SSH to login to the Reporter system as the
RUEI_USER
user. -
To stop processing, run the following command:
project -stop
-
Ensure that the following processes are no longer running:
qjobd
,logr
, andrsynclogdird
. If necessary, run thekill
command to stop them. -
After completion of database maintenance, restart processing by running the following command:
project -start
Enabling Online Tablespace Backups
As of version 12.1.0.3, the USERS
and UXCONF
tablespaces within new installations are set to force
logging
mode. Previously, the default mode was nologging
. The upgrade procedure does not change your database's current setting. However, changing the tablespace mode to force
logging
can considerably increase disk I/O.
By default, the database does not support online backups. To enable online backups, the database's noarchivelog
mode must be changed, and a number of operations changed from nologging
mode to force
logging
mode. To support online backups, do the following:
-
Log in to the database system as the
oracle
user: -
Stop all processing by runnning the following commands:
source /etc/ruei.conf su - $RUEI_USER project -stop killall logmsgd killall qjobd killall rsynclogdird
-
Ensure that the
$RUEI_DB_INST
setting specifies the RUEI database. -
Change the database to
archivelog
mode by running the following commands:. oraenv sqlplus / as sysdba shutdown immediate startup mount alter database archivelog; alter database open;
-
Set the required operations to
force
logging mode by running the following command:alter tablespace USERS force logging; alter tablespace UXCONF force logging;
-
Configure and schedule the online backup.
-
Restart processing by running the following command:
project -start
For more information, see the Oracle Backup and Recovery User's Guide.
Improving KPI Calculation Performance
By default, RUEI uses up to 8 parallel connections to run the KPI value queries. This is controlled by the db_kpi_value_threads
setting. Increasing the number of parallel connections can improve KPI calculation performance. However, the value should not be set to a value higher than the amount of cores available from the database server, and there is no advantage of setting the value higher than the number of configured KPIs. It has no functional impact other than potentially making data processing run faster.
Run the following command to obtain the setting's current value:
execsql config_get_value processor db_kpi_value_threads
Run the following command to modify the setting's value:
execsql config_set_value processor db_kpi_value_threads *nthreads*
Where, *nthreads*
specifies the number of parallel connections to use for KPI value queries.
Managing Subpartitions in RUEI Tables
RUEI tables have subpartitions for their primary partitions and during installation, they are set to a default value of 2.
To read the current value, run the following command:
execsql config_get_value processor num_subpartitions_kpi_id
If you need to change the number of subpartitions, use the following commands:
Note:
Changing the number of subpartitions may require an additional license.
-
KPI tables:
execsql config_set_value processor num_subpartitions_kpi_id 10
-
User flow tables:
execsql config_set_value processor num_subpartitions_user_flow_id 10
-
All other tables:
execsql config_set_value processor num_subpartitions_match_id 10
To apply the new settings, run the following command:
modr all --repartition
The new value will not take effect until a new primary interval partition has been created. Depending on the type of table, a new interval partition may be created only once a day or even once a month.
Enterprise Manager Repository Maintenance
When the Enterprise Manager repository is under maintenance, you can configure RUEI to ensure that no KPI status change information is sent to the non-functioning Enterprise Manager instance.
When the Enterprise Manager repository maintenance starts, run the following command on the RUEI system:
execsql emdb_set_status $host $sid maintenance
When the Enterprise Manager repository maintenance is complete, run the following command on the RUEI system:
execsql emdb_set_status $host $sid up
Removing Unused Columns in Fact Tables
Unused columns in fact tables may consume disk space and fact tables with fewer columns may increase performance.
If there are too many columns, a database error ORA-01792: maximum number of columns in a table or view is 1000
is triggered when a new suite is added.
If a table has too many unused columns, you may see error messages similar to following message in project logs:
Fact table <FACT TABLE> has too many unused columns(unused=mmm, total=nnn)
Run the ruei-upgrade.sh drop_unused_columns <FACT_TABLE>
command on table names shown in above message. For example, F_RT_RTPAGE
.
To remove unused columns in the Fact tables, do the following:
-
Log in to the reporter machine as root.
-
Stop RUEI.
-
Run the following command:
ruei-upgrade.sh drop_unused_columns <FACT_TABLE>, replace <FACT TABLE>
Where,
<FACT TABLE>
is fact table name displayed in warning message. -
Start RUEI.
Note:
If the affected fact table contains large amount of data, then removing the unused columns might take time to process.