4 Managing the Database

This chapter describes a number of settings necessary to perform database maintenance and facilitate backups.

4.1 Viewing the Status of RUEI Database Tables

In the event of a database crash, objects may become corrupted. Typically, this reveals itself with ORA-00376 and similar errors reported in the Event Log. It is recommended that you carefully review the information in the Knowledge Base article at the following location:

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1303180.1

In particular, ensure that the indicated tablespaces are set to force logging. You can use the following command to view the status of the database tables:

cop stats %period

where period indicates the required year (2012), month (201203), or day (20120326). The command output appears as follows:

       STRUCTURE      PRESENTATION   DATA ROWS         DATA SIZE
 yuY0aQ  29   11   20   153  204        -     343    2.0 MB   0.1 MB  wg_visit_mo_201203
hash  data dims lvls  pres view   data    desc      data     desc   cube name
------ ---- ---- ----  ---- ----  ------  ------   -------  -------  ----------
fTq7vQ  19   11   22   133  156        0       2    0.1 MB   0.1 MB  c_keypage_mo_201203
u7q+3g   9    4    8    13    7        -     470    0.6 MB   0.1 MB  c_kpi_mo_201203
PMocAw  22    9   17   159  174        -   16960   19.0 MB   4.0 MB  c_page_mo_201203
K/p4ww  12   12   29   123  104        0       0    0.1 MB   0.1 MB  c_service_mo_201203
1S2Ggg  10   19   29    79   90        -     247    2.0 MB   0.1 MB  c_slowurl_mo_201203
lZRuxg  29    5   10   279   61        0       0    0.1 MB   0.1 MB  c_trasta_mo_201203
yuY0aQ  29   11   20   153  204        -     343    2.0 MB   0.1 MB  c_visit_mo_201203
hash  data dims lvls  pres view   data    desc      data     desc   cube name
------ ---- ---- ----  ---- ----  ------  ------   -------  -------  ----------

Note that if the Data column contains a zero value, or there a large number of zeros or dashes, this would indicate corrupted database tables. In this case, you should use the script described in the following Knowledge Base article to restore the database:

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=556733.1

In addition, it is recommended that you issue the following commands to force an update of the RUEI configuration and template tables:

makedatabase @
modr -fn all 

4.2 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 to prevent the reporting of error messages to show. Do the following:

  1. Use SSH to logon to the Reporter system as the RUEI_USER user.

  2. Issue the following command to stop processing:

    project -stop
    
  3. Ensure that the following processes are no longer running before bringing down the database: qjobd, logr, and rsynclogdird. If necessary, use the kill command to stop them.

  4. After completion of database maintenance, restart processing by issuing the following command:

    project -start
    

4.3 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, be aware that changing the tablespace mode to force logging can considerably increase disk I/O.

By default, the database does not support online backups. In order to do so, the database's noarchivelog mode needs to be changed, and a number of operations changed from nologging mode to force logging mode. Do the following:

  1. Logon to the database system as the oracle user:

  2. Stop all processing by issuing the following commands:

    source /etc/ruei.conf
    su - $RUEI_USER
    project -stop
    killall logmsgd
    killall qjobd
    killall rsynclogdird
    
  3. Ensure that the $RUEI_DB_INST setting specifies the RUEI database.

  4. Change the database to archivelog mode by issuing the following commands:

    . oraenv
    sqlplus / as sysdba
    shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    
  5. Issue the following commands to set the required operations to force logging mode:

    alter tablespace USERS force logging;
    alter tablespace UXCONF force logging;
    
  6. Configure and schedule the online backup.

See the Oracle Backup and Recovery User's Guide for further information. It is available at the following location:

http://www.oracle.com/pls/db112/portal.portal_db?selected=14

4.4 Using Redo Logging

By default, redo logging of the RUEI database is disabled. If this is enabled for the complete database, very large redo log archives can be created. Therefore, if you want to use redo logging as part of your backup strategy, you need to make a number of configuration changes. Do the following:

  1. Logon to the database system as the oracle user.

  2. Issue the following commands to set the required logging options in the RUEI database table spaces:

    sqlplus / as sysdba
    SQL> alter tablespace USERS force logging;
    SQL> alter tablespace UXCONF force logging;
    SQL> alter tablespace UXSTAT no force logging;
    SQL> alter tablespace UXTEMP no force logging;
    

Note that the UXSTAT and UXTEMP tablespaces are not set to force logging because they are not relevant to the backup and restore process because they only contain intermediate data.

4.5 Improving KPI Calculation Performance

By default, the degree of parallelism used for KPI calculation-related queries in the database is 1. This is controlled by the db_core_dop_kpi setting. Increasing the number available can improve KPI calculation performance. However, this setting should never be set to a number higher than the amount of cores available from the database server. This setting utilizes the DOP features of the Oracle database. It has no functional impact other than potentially making data processing run faster.

Use the following command to obtain the setting's current value:

execsql config_get_value processor db_core_dop_kpi

Use the following command to modify the setting's value:

execsql config_set_value processor db_core_dop_kpi dop

where dop specifies the degree of parallelism used for KPI queries in processing.

4.6 Managing Subpartitions in RUEI Tables

RUEI tables have subpartitions for their primary partitions and these are set to a default value of two during installation. 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 read the current value, run the following command:

$ execsql config_get_value processor num_subpartitions_kpi_id 

Note that 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.