Symantec recommends the creation of database scripts to maintain the size of the archive files and user login tables.
Next, set up two databases, one for the primary DB and the other for the MDM Core functionality. The names you give these databases is your choice.
Set up the Mobility Suite databases in Oracle
Open a terminal session to the Oracle instance you're using for your mobility suite installation and log in
Enter, CREATE DATABASE <name for the primary database>
Enter, CREATE DATABASE <name for the MDM Core database>
Set up the Mobility Suitedatabases in MySQL
Open a terminal session to the MySQL instance you're using for your mobility suite installation and log in.
Enter, CREATE DATABASE <name for the primary database> CHARACTER SET utf8 COLLATE utf8_bin;
Enter, CREATE DATABASE <name for the MDM core database> CHARACTER SET utf8 COLLATE utf8_bin;
Recommendations and best-practices
Review the following recommendations prior to setting up the databases:
Database: How do I setup an Oracle DB for Mobility Manager
Always use the patched version of oracle as the Database Repository for Mobility Manager.
It is recommended to install Oracle 18.104.22.168.
Oracle Database Software comes in many editions; Express, Standard and Enterprise. It is best to install the Enterprise Edition as it provides additional features such as :
Online Index Rebuild
Oracle Database Diagnostic Pack - AWR and ADDM for Performance analysis
Parallel index scans
Parallel DML :-This is the ability to perform database changes (inserts, updates, deletes) in parallel
Oratop utility which is similar to top in Linux the only difference is it shows top CPU consuming oracle processes or sessions
TheMobility Suite installation creates the tables and the related objects into the schema (user) which is provided on the configurator page.
Never use SYSTEM as the schema since it has the oracle server base tables. The schema which is used should have a dedicated role with limited privileges assigned to it.
Create a schema which would have its own default tablespace for storage.
Moreover the Tablespace should have sufficient no of datafiles each ideally of 2GB in size instead of keeping a single datafile with auto extend on. This reduces fragmentation on the disk.
The Mobility Suite installation creates the tables and the related indexes (TOTAL :- 811 application Indexes) in a single tablespace . Ideally, the indexes should have there own tablespace on a different disk. This plays an important role in performance.
Since currently we do not have any provision to provide a different tablespace for indexes during installation, it is advisable to move the indexes after installation to a different dedicated tablespace.
Timely statistics to gather jobs should be maintained for the database optimizer to select the best execution plan for queries. Moreover, the fixed object stats in database should be gathered whenever required.
The Redo logs (i.e., the transaction log of the database) should be multiplexed and should be on a different disk than the actual database for better performance.
Post Mobility Suite installation parameter changes
Check the processes and the sessions parameter in the database .(Default is 150 which is not adequate). Processes should be at least set to 750 for Mobility Suite.
Set the SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file.
Make Sure the Profile for the schema used has the attribute PASSWORD_LIFE_TIME to unlimited.
This is a temporary work around to prevent the oracle schema password from expiring. This prevents Mobility Suite from working.
The Listener, as recommended by Oracle, should not run on port 1521 port . This is the default port and is prone to database attacks.
The database should be configured for failover. Use data guard for database failover and Oracle RAC for node failover.
If possible, use the Oracle Automatic Storage Manager (ASM) for datafile management to reduce hotspots in files during read and write.
Additional Oracle best practices
The database should be created on a different disk than the disk used for the actual Oracle software. Performance is made more consistent if you can spread the database across multiple disks, to prevent read/write "hotspots."
Assign at least 1GB of memory to the database (memory_target=1G)
Use the command: alter system set memory_target=1G scope=spfile ; (a system restart is required).
Use a dedicated user as the default schema or user for the creation of application objects. Do not use SYSTEM or SYS schema. Using SYSTEM results in a SYSTEM tablespace which is never recommended.
Oracle recommends creating different tablespaces for indexes and table objects. Do not keep the tables and their corresponding indexes in the same tablespace.
For best performance, the Redo (i.e., "transaction") logs should be multiplexed and reside on a different disk than the database.
To improve the execution of search queries by the database optimizer, run statistics-gathering jobs on a timely basis.
Use the SQL*Plus utility or SQL Developer tool to perform the following recommended tasks:
Commonly used database administrator tasks
Check Oracle database account password expiration
If the password associated with your Oracle database account expires, certain Mobility Manager functions will throw exceptions. One example of this is that the Work Hub cannot build. If the Oracle database has expired or is set to expire, the following action must be taken:
Update password within Oracle
Update Mobility Suite. Access /usr/local/nukona/etc/settings.cfg using your preferred editor (i.e, vi) and update the Oracle password value.
Rerun load_settings: python -m load_settings -d
Save the modified settings.cfg. You can now copy the updated settings.cfg to any front-end server as needed.
Queries to check database size
To retrieve the total database size: select sum(bytes/1024/1024/1024) "GB" from dba_data_files;
To retrieve the space used by the objects in the database excluding the free space in the database: select sum(bytes/1024/1024/1024) "GB" from dba_segments ;
Monitoring database sessions
To retrieve information about long running DML and select statements in the database:
set lines 180
col event for a30
col program for a40
set pages 1000
select PROGRAM,SID,SERIAL#,status,event,to_char(LOGON_TIME, 'dd-mon-rrrr hh24:mi:ss') logontime from v$session where username is not null order by program desc;
select * from v$sql_monitor :
To check only the active sessions in the database
select sid,serial#,program,status,logon_time,blocking_session from gv$session where username is not null and status='ACTIVE' ;
Subscribing will provide email updates when this Article is updated. Login is required.