After an installation or an upgrade, and after users are added, Oracle Communications MetaSolv Solution (MSS) requires database adjustments only in response to certain conditions. This chapter provides detailed instructions about the following tasks as they directly relate to MetaSolv Solution:
Performing general database maintenance
Managing scalability
Monitoring and maintaining database performance
Backing up and recovering the database
The Oracle database provides tools that allow you to monitor its behavior and performance. Refer to the Oracle database documentation for information about the standard monitoring tools.
MetaSolv Solution-specific deployment details that affect the database are documented in MetaSolv Solution Installation Guide. To determine the type of deployment in use at your site, review your company's deployment diagram.
The MetaSolv Solution DBA must have a thorough understanding of administering, maintaining, and tuning an OLTP system. Refer to the Oracle database documentation for instructions.
Among the MetaSolv Solution DBA's duties are:
Configuring the database
Upgrading
Adding and deleting databases
Maintaining the database
Managing database connections
Managing scalability
Managing performance
Backing up and restoring the database
General administration tasks
The remaining sections in this chapter address how these tasks relate to MetaSolv Solution. The DBA is expected to be proficient in all of these tasks.
Refer to the Oracle documentation for information about the following items:
Understanding memory and file structure components
Understanding INIT.ORA parameters
Implementing Oracle database patches and upgrades
Understanding platform-specific parameters
Creating databases as needed for production and testing
Understanding database file sizing issues
Implementing security for database access outside of the MetaSolv Solution environment
Oracle expects the DBA to be able to:
Establish and refresh Oracle DBMS_STATS for viewing and modifying optimizer statistics
Correct table and index sizing issues, including chained rows and number of extents
Review Oracle system trace and error logs daily for errors
Rebuild tables, tablespaces, or indexes as needed
Import or export databases as needed
Oracle recommends that an Analyze Statistics job be scheduled once a week to refresh statistics. If significant data input operations are started on a previously empty table or tables, run statistics more frequently.
Refer to the Oracle database documentation for complete instructions on these maintenance tasks.
The following sections describe specific Oracle issues that affect MetaSolv Solution.
Initial DBA authority is assigned by running a_users.sql during a new install, granting the DBA privilege to the ASAP user. This file is not delivered with service packs because the ASAP user is already set up. See MetaSolv Solution Installation Guide for details about a_users.sql.
The MetaSolv Solution database structure includes public synonyms, roles, Oracle users, and privileges granted to roles that must exist to have a fully functional database. These objects are not included when importing or exporting in table or user mode.
When importing a MetaSolv Solution database, MetaSolv Solution does not import the structure separately from the data. The MetaSolv Solution database uses Oracle sequences having a data component which is imported with other Oracle objects. If the imported data does not coincide with the sequence value, data integrity can be lost and MetaSolv Solution functionality can be affected.
Note:
To test an upgrade of an existing database, Compress=N should be used so that existing table sizes are retained in the new environment.When creating a copy of the database, always use Full mode for both the import and export processes. As for any OLTP system, it is important to export with the CONSISTENT=Y option. Exporting a database without using the CONSISTENT=Y parameter may cause data inconsistencies, including primary and foreign key errors.
You can use the Oracle export parameter CONSISTENT=Y, which allows you to export a database while users are still in the system, while not affecting the sequences.
Note:
In a production database, there is no reason for sequences to be out of sync. If you encounter an issue related to sequences, you should contact Oracle Global Customer Support.MetaSolv Solution resides on a database defined with tablespaces that are locally managed or dictionary managed.
Changes to the Job Type table data can have significant undesirable results; change them only under the direction of Oracle Global Customer Support personnel.
Caution:
Check with GCC before making any changes to the Job Type table data.For MetaSolv Solution to handle international data, your Oracle database must support an international character set. An international character set provides character support for local scripts and cultural preference information, such as sorting sequences, date and time conventions, and currency notations.
Caution:
For some customers, the US7ASCII character set does not support extended characters. Refer to the Oracle documentation for instructions.To see what character set your Oracle instance is currently using, execute the following SQL query:
SELECT USERENV ('LANGUAGE') FROM DUAL;
This query returns the current sessions language, territory, and database character set in the following format:
<language>_<territory>.<database character set>
For information on the NLS_LANGUAGE and NLS_TERRITORY variables, refer to the Oracle documentation.
You can alter the NLS_LANGUAGE and NLS_TERRITORY parameters by changing the desired value in the Oracle initialization file and then restarting the instance.
You must change your character set if it does not meet the international standard.
To change your character set:
Create a new instance with the desired character set.
Export your existing database.
Note:
Test this process first to confirm that the new character set supports previous character sets.Import the existing database into the new instance.
By importing the existing database into the new instance with the desired character set, your database is capable of supporting international data.
Refer to the Oracle database documentation for scalability recommendations. Assistance with managing scalability parameters can be obtained through MetaSolv Solution Professional Services.
Consider the following init.ora parameters when managing scalability of the MetaSolv Solution database:
processes
Increase this parameter as the user count increases.
open_cursors
Increase this parameter as the user count increases.
dml_locks
This parameter represents the maximum number of DML locks, one for each table, modified in a transaction. Its value should equal the grand total of locks on tables referenced by all users.
For example, if three users modify data in one table, three entries are required. If three users modify data in two tables, six entries are required. The number needs to increase as the user count increases.
db_block_buffers
As the number of users increases, use the STATSPACK report to monitor the data cache hit ratio. A decrease may indicate a need for more buffers.
Shared_pool_size
As the number of users increases, use the STATSPACK report to monitor the library cache hit ratio. Also, if the pool is set too low, the client application may receive server errors indicating not enough shared pool to load objects.
Following are the init.ora parameters that are specific to MetaSolv Solution performance:
The recommended db_block_size parameter is 8kb. A 2kb block size is not large enough to support the largest index within the block. The block size must be set at 4kb or larger.
The optimizer_features_enable parameter must be set to 9.2.0. The 6.0 release of MetaSolv Solution supports only the 9.2.0 optimizer.
The parallel_max_servers parameter should be set to 1. This parameter is more appropriate for a batch job/data warehousing/reporting database than an OLTP system. There have been significant performance issues reported as a result of setting this parameter above 1.
Set the optimizer_mode parameter to CHOOSE (optimizer_mode=choose). MetaSolv Solution SQL is tuned against Oracle's COST BASED optimizer. Using RULE for the optimizer_mode parameter is not supported.
To maintain database performance for MetaSolv Solution, the DBA is expected to know how to handle these tasks:
Establish benchmarks for your site and monitor database performance
Understand and use STATSPACK reports
Identify and correct fragmentation issues
Monitor and correct database bottlenecks
Adjust memory structures and file placement to improve performance
Implement MetaSolv Solution database patches and upgrades as needed
Access Oracle database documentation, or support, or both to resolve any Oracle database errors
Important tools used to monitor the performance of an Oracle database are the STATSPACK reports. For information on STATSPACK reports, go to My Oracle Support and view the documents on system tuning using STATSPACK reports.
Note:
Oracle recommends running STATSPACK reports multiple times a day during peak periods.To successfully monitor the performance of your database, it is important to have a baseline reference. Create the STATSPACK reports during normal use of your system. This information can be stored for reference in report form, printed form, or moved into tables. Books on Oracle database performance tuning are available that describe how to do this.
To help identify potential database structure and performance problems, schedule the following scripts to run routinely:
o_config.sql
o_analyz.sql
o_granta, o_grante, o_grantb, o_grantj.sql
mdl_cmp.sql
mdl_cur.sql
mdl_upd.cmp
mdl_upd_cur.txt
All references to @@asap.pwd, @@edi.pwd, @@ebond.pwd, @@job.pwd, and @@sys.pwd contained in these scripts can be changed to hard-coded connect strings of user/password@dbname;. If the correct logons are maintained and you are logged on at execution time, remove the commands after reviewing the scripts. All references to @@spooly can be changed to hard-coded spool path\\filename commands. Refer to Oracle database documentation for more information on the spool and connect commands.
Starting in 6.0.2, customers are provided a mdl_upd directory in service packs. This directory contains SQL files that include any modifications to the database structure in a service pack. To apply these updates to the reports, copy the contents of this directory into either the inst_60 or upg_60 directory. After applying the PRODFIXSQL, you can validate the execution of the scripts by running the mdl_upd_cur.sql and/or mdl_upd_cmp.sql. These processes provide the reports mdl_upd_cur.txt and mdl_upd_cmp.txt identical to the mdl_cur.sql and mdl_cmp.sql.
The following sections describes these scripts.
The o_config.sql script identifies table and index objects having:
Storage parameters below the MetaSolv Solution software standards
Objects that are not stored in the correct tablespace
Objects fragmented beyond 100 extents
Note:
To run this script outside of the upgrade process for database administration, type @path file_name followed by the command FORCE, where path is the directory path of the script and file_name is the name of the script.Example:
@C:\MetaSolvSolution\UPGRADE\O_CONFIG.SQL FORCE;
The following output files are stored in the Audits subdirectory:
o_cfggen.sql
o_cfgrpt.txt
This script is generated in the Audits subdirectory by the o_config.sql script and has the following characteristics:
Contains SQL that can be applied to your database
Generated for tables or indexes having storage parameters below the Oracle database standards and for indexes that are not in the indexes tablespace
Note:
Review this script before running it. Any large index rebuild might require a significant amount of run time and system resources.The o_cfgrpt.txt file is a report of objects which are currently being stored with storage settings below minimum standards.
The SQL code can be run to correct the storage.
This report is generated in the Audits subdirectory by the o_config.sql script and contains a list of tables and indexes having more than 100 extents allocated to them. These items are good candidates to be rebuilt (if indexes).
The o_analyz.sql script updates statistics used by the Oracle query optimizer and can affect performance. If the statistics are not updated, the optimizer can incorrectly choose a path to execute a query, resulting in poor performance. Consult the Oracle database documentation for discussions on index statistics and recommended update schedules.
Note:
To run this script outside of the upgrade process for database administration, type @path file_name followed by the command FORCE, where path is the directory path of the script and file_name is the name of the script.Example:
@ c:\MetaSolvSolution\upgrade\o_analyz.sql FORCE;
As with the other maintenance scripts, run these scripts on a scheduled basis to ensure that database permissions are set correctly for all MetaSolv Solution related objects.
You can run the mdl_cmp.sql and mdl_upg.cmp scripts independently of an upgrade to help maintain the database and performance levels. Oracle recommends that you run this script at least once a week. This helps maintain the database structure for primary and foreign keys, indexes, and sequences. The mdl_cmp.sql script provides the option to fix the problems found or only report the differences. When you run the script you have the option to compare the differences for individual sections or for all sections. The default is to display all sections. The script reports about:
Extra or missing indexes
Extra or missing primary keys, foreign keys, and sequences
To run this script outside of the upgrade process for database administration, type
@c:\MetaSolvSolution\upgrade\mdl_cmp.sql;
If you build additional indexes in the schemas, preface each additional index name with PFXI_ or PFXPK_. This naming convention prevents the extra indexes from being dropped during the upgrade process. This is not required, if you choose not to copy these files and run the updated reports you may see extra database objects in the report.
The mdl_cmp.txt and mdl_upd.com.txt reports identify differences in the following data model objects for this release:
Indexes
Primary keys
Sequences
Foreign key exceptions
Reports can be generated on one or more of the above objects. For reports not made on all objects, report summaries for the unselected objects indicate REPORT NOT SELECTED.
Additional details are available in the \audits\mdl_cmp.txt file. If the following statement is the only paragraph in the report, your database is 100 percent correct for these checks:
PLEASE REVIEW THE ABOVE SUMMARIES WHICH IDENTIFY THE DIFFERENCES FOR INDEXES, PRIMARY KEYS, and FOREIGN KEYS (and FK exceptions) FROM THE MODEL FOR THIS RELEASE. ADDITIONAL DETAILS ARE AVAILABLE WITHIN THE \AUDITS\MDL_CMP.TXT FILE. IF THIS IS THE ONLY PARAGRAPH IN THE REPORT, YOUR DATABASE IS 100% CORRECT FOR THESE CHECKS.
You can run the mdl_cur.sql and mdl_upd_cur.sql scripts independently of the upgrade to generate a report in the Audits subdirectory. The resulting mdl_cur.txt file contains a report of the current database compared to the MetaSolv Solution data model. This report identifies missing tables or columns, extra tables or columns, and differences with column data types.
The mdl_cur.txt and mdl_upd_cur.txt reports list six areas where the current database structure can differ from the MetaSolv Solution data model. These areas are:
Change Type: Refers to one of three different types; column change, missing table, or extra table. Column change can be one of three different changes:
Extra column
Missing column
Column difference
Table: Lists the affected table
Column: Lists the affected column
Data Type Column: Lists the affected data
Null: Lists the affected nullability positions
Column Position: Lists the affected column positions
See "Sample mdl_cur.txt or mdl_upd_cur.txt reports" for an example of the mdl_cur.txt report.
Aside from some basic machine configurations and regularly performing analyze statistics on indexes, your biggest gains or losses in performance probably come from changes to the init.ora configuration file.
There are many init.ora parameters. This section focuses on parameters that have the biggest impact on MetaSolv Solution performance:
Major contributors to performance
Parameters related to scalability adjustments
Some parameters overlap between the two. Almost all of the items can be monitored using the STATSPACK report. See "Managing Database Performance" for more information.
The following parameters are major contributors to performance:
db_block_buffers
The value of db_block_buffers multiplied by db_block_size determines the size of the data buffer. The buffer cache stores tables, indexes, clusters, and rollback segments. The higher the number of block buffers, the less I/O takes place, and the better the system performs.
If excessive paging and swapping activity occurs for user processes or if any paging or swapping of the SGA occurs, reduce db_block_buffers to free memory. There is no need to set this above what is necessary.
The key monitoring device for adjustments should be the manually calculated data cache hit ratio in the STATSPACK report.
db_file_multiblock_read_count
Try setting the db_file_multiblock_read_count to 8, 16, or higher. This references the number of blocks that Oracle requests the operating system to read when performing a sequential scan. Therefore, if a query needs to read a single record from a table from disk, Oracle database reads not just the block containing the record, but also the number of blocks set by this parameter. This causes additional reads, but the theory is that you will save more disk reads later if queries request other data contained within the additional blocks that were stored in the data cache.
The parameter can be better tuned when matched with the operating system block size. Larger settings can cause a lower cache hit ratio if the additional blocks that were read aren't used by other queries and data has to get flushed to make room for newly cached data. Watch the cache hit ratio and other STATSPACK info.
shared_pool_size
This pool stores shared SQL and PL/SQL blocks, the data dictionary cache, and (if your site is using Multi-threaded server architecture) some session information. There is no need to set this way above what is necessary. Setting this parameter to high might cause Oracle database to store so many queries in the library that it will cause slower performance. This happens when the Oracle database spends more time searching for a cached copy of the query than it would have spent parsing the query. A good indicator that this is happening is improved performance when the shared pool is manually flushed.
sort_area_size
This is the size, in bytes, that a user process has available for sorting. This setting might be dramatically different for each customer, depending in which parts of the MetaSolv Solution are used. Start with at least 300k. Beyond that, watch for the sorts (disk) in the STATSPACK report as an indicator to increase the size. Ideally, there should be no sorts to disk. Be aware that the memory gets pre-allocated per user connection. This can have a significant effect on total system memory used by the Oracle instance.
sort_area_retained_size
This is the size, in bytes, to which Oracle reduces your sort area if sort data is not being referenced. Memory is reduced only after all the rows have been retrieved from the sort space. Sometimes, a number of concurrent sorts may be required, and each is given its own memory allocation, which is determined by this parameter. Match sort_area_size per several Oracle white papers' recommendations.
Other parameters to adjust
Depending on the performance analysis from the UTLBSTAT, UTLESTAT, and STATSPACK reports, adjust the following parameters:
db_writer: Increase db_writer processes if asynchronous I/O is turned off at your OS level. This parameter is set to 1. Otherwise, try adjusting it according to your tuning statistics report. In Oracle 8 and above, Oracle introduced dbwr_io_slaves, so be sure to read the tuning information on these two parameters and adjust them accordingly.
enqueue_resources: Increase the number of enqueue_resources to reduce the enqueue_waits value in the STATSPACK report. For more information, refer to the Oracle white paper, Enqueue_Resources.txt.
sort_write_buffer_size: Try increasing the sort_write_buffer_size parameter. This is the size of each sort write buffer used for direct writes.
log_checkpoint_interval: If fast recovery time is required, keep a value lower than the size of the redo logs and at a size that is a whole fraction of the redo log size. If performance is the more important consideration, make it equal to or greater than the size of the redo logs.
log_checkpoint_timeout: Set this parameter to zero (consider making it equal to the redo log.)
redo logs: Make sure the redo logs are not switching too often. Oracle recommends having between 15 to 30 minutes between switches. Increase the size of your redo logs if it is less than 15 minutes and keep increasing it until the time is in the 15 to 30 minute range.
To tune non-init.ora files:
Use striped disks to improve the I/O throughput. Here is a sample configuration:
Raid 0+1 (striped and mirrored) for rollback segments, log files, and temp and index tablespaces
Raid 0 (mirrored) for Archive log files
If disk cost is not a problem, use Raid 0+1 (striped and mirrored) for everything. Buy more disks to spread your files across separate disks. This helps with I/O performance, and when you feel a bottleneck is occurring you are more easily able to isolate what is causing the bottleneck.
Note:
Oracle research has shown that in most cases, Raid 0 performs faster than Raid 5.Separate the Oracle database files from the OS, Oracle database program files, and Oracle Archive log files on different disks.
Do not allocate so much memory to the Oracle database and other applications that the OS has to perform memory/disk swapping or paging. A good rule is to not allocate over half the amount of the machine's available physical memory.
Limit the server to database-only use. If the OS is being taxed by other resources, the Oracle database runs slow regardless of how well-tuned your instance is.
For production, run only one instance of the Oracle database per machine. The more Oracle database instances running on a machine, the harder it is to tune the instance with system resources.
Whatever backup and recovery solution you choose, test the recovery process on a regular basis. Do not wait for an outage to test your processes and procedures. The DBA should be able to:
Understand the solutions implemented
Understand backup archive mode
Implement a documented, customer-developed backup and recovery plan that meets your company's standards for acceptable down-time and off-site backup storage and retrieval
Test the database recovery process on a regular basis
For clustering solutions, the MetaSolv Solution database supports an active/passive solution. Any Oracle-supported software vendor that provides an active/passive solution is supported.
Technology management and effectiveness requires specialized experience, skills, and strategies, one of which is the back-up and disaster recovery strategy. It is imperative that a contingency plan be in place to provide continued MetaSolv Solution functionality in the event of server faults, database crashes, other system down-time, as well as scheduled maintenance and updates.
Oracle recommends that the owner of this area come from IT.
Any approach to backup and recovery should, at a minimum, include the following tasks:
Identify database environments requiring periodic back-ups
Identify regular frequency of back-ups
Identify storage area for back-up media
Document the back-up procedure and conduct knowledge transfer sessions with IT personnel
Document the Disaster Recovery Plan and conduct knowledge transfer with IT personnel
Test the Disaster Recovery Plan on a regular basis
This section provides samples of Oracle database reports.
==========================================================
Report Date: 02/09/200x 10:42AM
Company Name: ACME TELCO
MetaSolv Version 6.0
Oracle Version: 9.2.0.3
Database Name: METASOLV SOLUTION PROD
==========================================================
Change Type Table Column Datatype Null
Missing Column: ASAP.ASR_ERROR CURRENT_VALUE VARCHAR2(80) NULL
Missing Column: DOCUMENT_NUMBER NUMBER(9) NOT NULL
Column Difference: LAST_MODIFIED_USERID VARCHAR2(8) NOT NULL
Column Difference: LAST_MODIFIED_DATE DATE NOT NULL
Extra Column: ASAP.ASSIGNED_TEL_NUM SPECIAL_FIELD1 NUMBER(9) NULL
Extra Column: SPECIAL_FIELD2 NUMBER(9) NULL
Column Difference: ASAP.BANK_CODE TRANSACTION_ORIGIN VARCHAR2(10) NULL
(should be varchar2(8) NULL
Column Difference: ASAP.BAN_USAGE LAST_MODIFIED_DATE DATE NULL
(should be not null)
Column Difference: ASAP.DLR_NOTES DLR_NOTES_TEXT VARCHAR2(49) NOT NULL
Column Difference: NOTE_IND CHAR(1) NOT NULL
Missing Table: ASAP.APPLICATION APPLICATION VARCHAR2(40) NOT NULL
Missing Table: STATUS CHAR(1) NOT NULL
Missing Table: AUDIT_CREATE_DATE DATE NOT NULL
Missing Table: AUDIT_CREATE_USERID VARCHAR2(7) NOT NULL
Missing Table: AUDIT_UPDATE_DATE DATE NULL
Missing Table: AUDIT_UPDATE_USERID VARCHAR2(7) NULL
Missing Table: DESCRIPTION VARCHAR2(254) NULL
Missing Table: SECURITY_METHOD NUMBER(1) NULL
Missing Table: TRANSACTION_ORIGIN VARCHAR2(8) NULL
Extra Table: ASAP.tmp_APPLICATION APPLICATION VARCHAR2(40) NOT NULL
Extra Table: STATUS CHAR(1) NOT NULL
Extra Table: AUDIT_CREATE_DATE DATE NOT NULL
Extra Table: AUDIT_CREATE_USERID VARCHAR2(7) NOT NULL
Extra Table: AUDIT_UPDATE_DATE DATE NULL
Extra Table: AUDIT_UPDATE_USERID VARCHAR2(7) NULL
Extra Table: DESCRIPTION VARCHAR2(254) NULL
Extra Table: SECURITY_METHOD NUMBER(1) NULL
Extra Table: TRANSACTION_ORIGIN VARCHAR2(8) NULL
==========================================================
Report Date: 02/09/200x 10:42AM
Company Name: ACME TELCO
MetaSolv Version 6.0
Oracle Version: 9.2.0.3
Database Name: METASOLV SOLUTION PROD
===========================================================
/******************************************/
/**INDEX COMPARE REPORT **/
/******************************************/
IIDX_CAT TABLE_NAME INDEX_NAME FIELD_LIST extra indexes ASAP.ACCESS_CUSTOMER ACCESS_CUSTOMER CUSTOMER_NAME extra indexes ASAP.TRBL_TASK_EXT_REF FK_TTER_PARTY PARTY_ID_REF_TO missing indexes ASAP.ACCESS_CUST PKIDX_ACCESS_CUST PARTY_ID, PARTY_ROLE missing indexes ASAP.ACCESS_PROVIDER FKIDX_AC_PR_SE_CE OPERATING_COMPANY
/******************************************/
/**PRIMARY KEY COMPARE REPORT **/
/******************************************/
PK_CAT TABLE_NAME CONSTRAINT_NAME FIELD_LIST extra pks ASAP.ACCESS_CUSTMER PK_ACCESS_CUSTOMER CUSTOMER_NAME extra pks ASAP.ACCESS_CUSTMER PK_ACCESS_CUSTOMER missing pks ASAP.ACCESS_CUST PK_ACCESS_CUST PARTY_ID, PARTY_ROLE missing pks ASAP.ACTIVATION PK_ACTIVATION_COMAND ACTIVATION_COMAND missing pks ASAP.ADDRESS PK_ADDRESS ADDRESS_ID
/******************************************/
/**FOREIGN KEY COMPARE REPORT **/
/********************************************************/
FK_CAT TABLE_NAME CONSTRAINT_NAME FIELD_LIST extra fks ASAP.ACCESS_CUSTOMER FK_ACBA_CITY CITY_NAME, STATE_CODE extra fks ASAP.ACCESS_CUSTOMER FK_AC_ACNA ACNA REFERENCES missing fks ASAP.ACCESS_CUST FK_AC_CU__PA_RO PARTY_ID, missing fks ASAP.ACCESS_PROVIDER FK_APR_ACCESS_SERVICE DOCUMENT_NUMBER missing fks ASAP.ACCESS_PROVIDER FK_AC_PR_SE_CE__CO OPERATING_COMPANY