This subsection describes how to set up PFM - RM for Oracle for operation.
indicates an item that may or may not be required depending on your operating environment, or an optional item that you can set if you do not wish to use the default.
Table 2-14 lists the LANG environment variable values supported by PFM - RM for Oracle.
Before you specify the LANG environment variable, make sure that the language environment you want to set has been installed and set up correctly. If you fail to check, characters may not be displayed correctly or definition data may be overwritten.
Table 2-14 LANG environment variables that can be used in PFM - RM for Oracle
OS | Language and code | LANG value |
---|---|---|
Linux | English | C |
Japanese (Shift-JIS) | nothing | |
Japanese (EUC) | nothing | |
Japanese (UTF-8) | ja_JP.UTF-8, ja_JP.utf8 | |
Chinese (GB18030) | zh_CN.gb18030 | |
Chinese (UTF-8) | zh_CN.UTF-8, zh_CN.utf8 |
To perform integrated management of PFM - RM for Oracle using PFM - Manager and PFM - Web Console, you must register PFM - RM for Oracle with PFM - Manager and PFM - Web Console.
For details about the data model version of an instance of PFM - RM for Oracle, see J. Version Compatibility.
The following figure shows the flow of PFM - RM for Oracle registration.
Figure 2-9 Flow of PFM - RM for Oracle registration
Copy the setup files from the host on which you installed PFM - RM for Oracle to the hosts on which PFM - Manager and PFM - Web Console are installed.
To copy the files:
Table 2-15 Setup files to be copied (UNIX)
PFM - RM for Oracle setup file | Destination | ||
---|---|---|---|
PFM program name | OS | Destination folder | |
/opt/jp1pc/setup/jpcagt1w.EXE | PFM - Manager | Windows | installation-folder\setup |
/opt/jp1pc/setup/jpcagt1u.Z | UNIX | /opt/jp1pc/setup/ | |
/opt/jp1pc/setup/jpcagt1w.EXE | PFM - Web Console | Windows | installation-folder\setup |
/opt/jp1pc/setup/jpcagt1u.Z | UNIX | /opt/jp1pcwebcon/setup/ |
On the PFM - Manager host, execute the following command to set up PFM - RM for Oracle:
jpcconf agent setup -key RMOracle
You can then delete the PFM - RM for Oracle setup files remaining on the PFM - Manager host.
On the PFM - Web Console host, execute the following command to set up PFM - RM for Oracle:
jpcwagtsetup
You can then delete the PFM - RM setup files remaining on the PFM - Web Console host.
To monitor an Oracle Database and collect performance data by using PFM - RM for Oracle, you must perform either of the following setting tasks:
The following table lists the operations that PFM - RM for Oracle performs for the Oracle Database to collect performance data. The table also lists the system privileges required to perform the operations with the Oracle account.
Table 2-16 Operations for the Oracle Database and required system privileges
Operations that PFM - RM for Oracle performs for the Oracle Database | System privileges required to perform the operations |
---|---|
|
|
Table 2-16 lists the system privileges for the sys account. If the sys account is not being used to monitor the Oracle Database from PFM - RM for Oracle, use an Oracle account that has the system privileges listed in Table 2-16.
An Oracle account having the system privileges listed in Table 3-4 can be created by executing the mk_rmus.sql script provided by PFM - RM for Oracle.
To use an Oracle account that can use PFM - RM for Oracle without use of the mk_rmus.sql script, grant the privileges necessary for operations. By granting these privileges, the minimum necessary privileges can be granted to the Oracle account. When granting privileges according to role, do so explicitly (using GRANT privileges...). For details about the privileges needed for each operation, see I. Precautions Regarding Permissions.
The following table lists the information needed to create an Oracle account by executing the mk_rmus.sql script. Check the information before starting setup operations.
Table 2-17 Information required to create an Oracle account
Item | Description |
---|---|
Enter username | Specifies the name of the account to be created. The value you specify must consist of 7-bit ASCII alphanumeric characters, must not exceed 30 bytes, and must be specifiable in the user parameter of the CREATE USER statement. Note that a character string that begins with a number and includes an alphabetic character cannot be specified. The default is RMAGT1.
|
Enter password | Specifies the password for the account to be created. The value you specify must consist of 7-bit ASCII alphanumeric characters, must not exceed 30 bytes, and must be specifiable in the BY password parameter of the IDENTIFIED clause in the CREATE USER statement. Note that a character string that begins with a number and includes an alphabetic character cannot be specified. This is a mandatory item. |
Enter default tablespace | Specifies the default tablespace used by the account to be created. The value you specify must consist of 7-bit ASCII alphanumeric characters, must not exceed 30 bytes, and must be specifiable in the DEFAULT TABLESPACE clause in the CREATE USER statement. Note that a character string that begins with a number and includes an alphabetic character cannot be specified. This is a mandatory item.
|
Enter default temporary tablespace | Specifies the default temporary tablespace used by the account to be created. The value you specify must consist of 7-bit ASCII alphanumeric characters, must not exceed 30 bytes, and must be specifiable in the TEMPORARY TABLESPACE clause in the CREATE USER statement. Note that a character string that begins with a number and includes an alphabetic character cannot be specified. This is a mandatory item.
|
For details about the CREATE USER statement, see your Oracle documentation.
The following procedure shows how to create an Oracle account. Before creating an Oracle account, make sure that the tablespaces and other required resources have been prepared.
To create an Oracle account:
/opt/jp1pc/agt1/agent/sql
When all values have been entered, the Oracle account is created.
Table 2-18 Privileges granted by mk_rmus.sql to Oracle accounts and the assignment limits of tablespaces
Type | Privileges granted / assignment limits | Description |
---|---|---|
System privilege | CREATE SESSION | Required to establish a session with the monitored Oracle Database. |
CREATE TABLE | Required when registering a table needed to monitor the Oracle Database, for the monitored Oracle Database (see the table in Table 2-24). | |
CREATE PROCEDURE | Required when registering a procedure needed to monitor the Oracle Database, for the monitored Oracle Database (see the package in Table 2-24). | |
SELECT ANY DICTIONARY | Required when registering information needed to monitor the Oracle Database in the monitored Oracle Database (see Table 2-24) and when collecting information. | |
UNLIMITED TABLESPACE | Required when registering information needed to monitor the Oracle Database, for the monitored Oracle Database (see the Table 2-24). | |
SELECT ANY TABLE | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
INSERT ANY TABLE | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
UPDATE ANY TABLE | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
DELETE ANY TABLE | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
CREATE ANY INDEX | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
ALTER ANY INDEX | Required to obtain the Explain Plan (EXPLAIN_PLAN) field in a SQL Text (PD_PDSQ) record. | |
Assignment limits of tablespaces | Unlimited assignment for the default tablespace# | Required when registering information needed to monitor the Oracle Database in the monitored Oracle Database (see Table 2-24) and when obtaining the Explain Plan field of the PD_PDSQ record. |
ALTER USER Oracle-account QUOTA maximum-tablespace-allocation-size ON tablespace-name;
PFM - RM for Oracle requires configurations of the instance environment and the monitoring target. There is a one-to-one match between the configuration of the instance environment and the configuration of the monitoring target.
Note that in PFM - RM for Oracle you can associate one instance environment with only one monitoring target.
You can set up multiple instance environments and monitoring targets by repeating the procedure for each instance.
For example, if you monitor three Oracle instances, repeat these procedures three times.
When you create an environment where there are multiple instances, the number of instances depends on the system configuration. As a guide, use three to five instances for the number of instances. You can increase the number of instances by reducing the number of records to be collected or lengthening the collection interval. Consider this carefully before operation.
This section describes the procedures for each of the actions.
You must specify instance information for the Oracle that is to be monitored by the PFM - RM for Oracle. Specify instance information on the PFM - RM host.
The following table lists the instance information items that are to be specified. You should check this information before you start the setup procedure. For details about the Oracle instance information items, see your Oracle documentation.
Table 2-19 PFM - RM for Oracle instance information
Item | Description | Specifiable value | Default |
---|---|---|---|
oracle_sid | Monitoring-target Oracle system identifier (the same value as the value of the ORACLE_SID environment variable) | A character string of 255 or fewer bytes that does not include the following characters:
| The value specified for the -inst option of the jpcconf inst setup command |
oracle_home#1 | Oracle home folder of Oracle Client used by PFM - RM for Oracle (the same value as the value of the ORACLE_HOME environment variable) #2 | A character string of 255 or fewer bytes that does not include the following characters:
| -- |
oracle_version#1 | Version number of Oracle Client used by PFM - RM for Oracle |
| 11 |
oracle_user#3 | An account for monitoring Oracle For details about accounts that can be specified and the required privileges, see (3) Create an Oracle account to be used in PFM - RM for Oracle. | A character string of 255 or fewer bytes that does not include the following characters:
| sys |
oracle_passwd#3#4 | A password for the account that was specified in oracle_user | A character string of 255 or fewer bytes that does not include the following characters:
| -- |
net_service_name#2#5 | The net service name of a monitoring-target database. For details about the net service name of a monitoring-target database, see your Oracle documentation. | A character string of 255 or fewer bytes that does not include the following characters:
| Instance name (the value of oracle_sid) |
log_path#6 | The full path name of the directory for storing agent log information | A character string within 245 bytes that does not include the following characters:
| /opt/jp1pc/agt1/agent/instance-name/log |
log_size | The maximum size of one agent log file#7 | 1 to 32 (in megabytes). The recommended value is 16 or greater. | 16 |
timeout#8 | The timeout period for Oracle access during a query. | 0, or 10 to 3600 (in seconds). When 0 is specified, timeout monitoring is not performed. When a value from 1 to 9 is specified, it is changed to 10 at runtime. For details about timeouts, see 2.6.5 Cancellation facility for Oracle access during record collection. | 0 |
sql_option#9 | When Y is specified, information about the following items#7 is not collected for PI_PIDB and PD_PDTS records, and the value specified by 0 or numeric_10 is set. | { Y | N } | N |
numeric_10#10 | When sql_option is set to Y, the value specified is set for items for which information is not collected. If sql_option is set to N, this specification is disregarded. | 0 to 99999. Note that if the value specified exceeds the maximum value for the data format of the set field (32767 for short and 65535 for ushort), the maximum value for the data format is set.#11 | 0 |
startup_always | PFM - RM for Oracle may stop due to an Oracle connection error, such as when the monitored Oracle is still starting up when PFM - RM for Oracle starts up. If Y is specified, start processing continues even if a connection error occurs. If N is specified, start processing will stop if an error occurs. | { Y | N } | Y |
localtemp_option#12 | Option for switching the display of the free space of the locally managed temporary tablespace of PD_PDDB, PI_PIDB, PD_PDDF, PI_PIDF, PD_PDTF, PD_PDTS, and PD_PCTS records. If Y is specified, display the size of the free space. If N is specified, display the size of the unallocated space. | { Y | N } | N |
nls_lang#13 | Option for specifying the character encoding used for communication between PFM - RM for Oracle and Oracle Database. | Character code set: According to LANG of the OS when at the start time of the PFM - RM for Oracle.
| AMERICAN_AMERICA.US7ASCII |
undospace_option#14 | Option for switching how the value displayed as the free space in the UNDO tablespace of the PD_PDDB, PI_PIDB, PD_PDDF, PI_PIDF, PD_PDTS, and PD_PCTS records is determined. If N is specified, the size of the unallocated space is displayed. If Y is specified, the size of the free space is displayed. | { Y | N } | N |
Amount of free space on the disk containing the directory specified in log_path (MB) > log_size x 4
Table 2-20 Record names and the values specified for numeric_10 (setting up instance information)
Record name | PFM - View name | Value specified for numeric_10 |
---|---|---|
PD_PDTS | Segments | Enabled |
Extents | Enabled | |
PI_PIDB | DB Files % | Enabled |
Log Files % | Enabled | |
NextAlloc Fails | Enabled | |
Tablespaces | Enabled | |
Rollback Segments | Enabled | |
Rollback Segments Trans | Enabled | |
Blocks | Enabled | |
Segments | Enabled | |
Extents | Enabled | |
Free Mbytes | Enabled | |
Overextended | Enabled | |
High Max Extent | Enabled | |
Datafiles | Enabled | |
Mbytes | Enabled | |
Free Extents | Enabled | |
Free% | Enabled | |
Free Change | Enabled | |
Write% | Enabled | |
Write/sec | Enabled | |
Redo Files | Enabled | |
Links | Enabled | |
Links Logged On | Enabled | |
Links In Tran | Enabled | |
Links Open Cursors | Enabled | |
Used Change | Enabled | |
Used Mbytes | Enabled | |
Rollback Segments Hit% | Enabled | |
Sort Segments | Enabled | |
Sorting Users | Enabled | |
Physical Blocks Read | Always set to 0 because it is a delta item. | |
Physical Blocks Written | Always set to 0 because it is a delta item. | |
Physical Reads | Always set to 0 because it is a delta item. | |
Physical Writes | Always set to 0 because it is a delta item. |
Figure 2-10 Relationship between data and the setting values
LANG environment variable of at the start time of PFM - RM for Oracle | nls_lang instance information (this item) |
---|---|
ja_JP.UTF-8, ja_JP.utf-8, zh_CN.UTF-8, zh_CN.utf8 | AMERICAN_AMERICA.AL32UTF8 or AMERICAN_AMERICA.US7ASCII# |
zh_CN.gb18030 | AMERICAN_AMERICA.ZHS32GB18030 or AMERICAN_AMERICA.US7ASCII# |
other LANG | AMERICAN_AMERICA.US7ASCII# |
Record name | Field name | Field size (Unit: bytes) |
---|---|---|
Collection Instance 2 (PD_PCI) | Host | 30 |
Data File (PD_PDDF) | File Name | 255 |
Data File Interval (PI_PIDF) | File Name | 255 |
Instance (PD_PDI) | Host | 30 |
Minimum Data File Interval 2 (PI_PMDF) | File Name | 255 |
Parameter Values (PD_PDP) | Value | 512 |
SQL Text (PD_PDSQ) | Explain Plan | 30000 |
SQL Text | 30000 |
Record name | Field name | Field size (Unit: bytes) |
---|---|---|
Collection Tablespace 2(PD_PCTS) | Tablespace Name | 30 |
Data File(PD_PDDF) | File Name | 255 |
Tablespace Name | 30 | |
Data File Interval(PI_PIDF) | File Name | 255 |
Tablespace Name | 30 | |
Database(PD_PDDB) | DB Name | 9 |
Database Interval(PI_PIDB) | DB Name | 9 |
Instance(PD_PDI) | Host | 30 |
Lock Waiters(PD_PDLW) | Holding User | 30 |
Waiting User | 30 | |
Minimum Database Interval 2(PI_PMDB) | DB Name | 9 |
Minimum Data File Interval 2(PI_PMDF) | File Name | 513 |
Minimum Tablespace Interval 2(PI_PMTS) | Tablespace Name | 30 |
Open Cursor(PD_PDOC) | Program | 48 |
SQL Text | 60 | |
Parameter Values(PD_PDP) | Value | 512 |
Session Detail(PD_PDS) | Machine | 64 |
Module | 48 | |
OS User | 30 | |
Program | 64 | |
Schema Name | 30 | |
User | 30 | |
Session I/O Interval(PI_PIIO) | User | 30 |
Session Statistics Summary(PD_PDS2) | Program | 48 |
User | 30 | |
SQL Text(PD_PDSQ) | Explain Plan | 30,000 |
SQL Text | 30,000 | |
Tablespace Flagmentation(PD_PDTF) | Tablespace Name | 30 |
Tablespace Interval(PI_PITS) | Tablespace Name | 30 |
Tablespace(PD_PDTS) | Tablespace Name | 30 |
Transaction(PD_PDTR) | User | 30 |
Tranasction Lock(PD_PDTL) | Object Name | 30 |
Owner | 30 | |
User | 30 |
Record name | Field name |
---|---|
Data File (PD_PDDF) | Free % |
Free Mbytes | |
Used Mbytes | |
Data File Interval (PI_PIDF) | Free % |
Free Change | |
Free Mbytes | |
Used Change | |
Used Mbytes | |
Database (PD_PDDB) | Free % |
Free Mbytes | |
Used Mbytes | |
Database Interval (PI_PIDB) | Free % |
Free Change | |
Free Mbytes | |
Used Change | |
Used Mbytes | |
Tablespace (PD_PDTS) | Free % |
Free Mbytes | |
Used Mbytes | |
Collection Tablespace 2 (PD_PCTS) | Free Mbytes |
An instance environment is created by using the jpcconf inst setup command. The following procedure shows how to create an instance environment.
To create an instance environment:
jpcconf inst setup -key RMOracle -inst SDC
When all values have been entered, the instance environment is created. If you want to change the instance information, re-execute the jpcconf inst setup command to update the instance environment. For details about updating an instance environment, see 2.6.3 Updating an instance environment.
The following describes the created instance environment.
Table 2-21 Directory configuration of the instance environment
Directory and file | Description | ||
---|---|---|---|
agent | instance-name | jpcagt.ini | Remote Monitor Collector service startup initialization file |
jpcagt.ini.model# | Model file for the Remote Monitor Collector service startup initialization file | ||
status.dat | Relay file for internal processing | ||
tstatuses.dat | Status information file for virtual agent | ||
targetlist.ini | List file for monitoring target | ||
grouplist.ini | List file for monitoring group | ||
GARULES.DAT | List file containing a description of the grouping rules | ||
targets | Storage directory for remote agent | ||
groups | Storage directory for group agent | ||
log | Storage directory for log files | ||
store | instance-name | jpcsto.ini | Remote Monitor Store service startup initialization file |
jpcsto.ini.model# | Model file for the Remote Monitor Store service startup initialization file | ||
*.DB | Performance data file | ||
*.IDX | Index file for performance data file | ||
*.LCK | Lock file for performance data file | ||
status.dat | Relay file for internal processing | ||
*.DAT | Data model definition file | ||
dump | Export destination directory | ||
import | Standard database import destination directory | ||
backup | Backup destination directory | ||
log | Storage directory for log files | ||
partial | Standard database partial backup destination directory | ||
STPD | Performance data storage destination directory for records of the PD record type | ||
STPI | Performance data storage destination directory for records of the PI record type |
When you set the monitoring target, you associate the instance that you specify in (a) Set up instance information with the information about the monitoring target host.
Set the monitoring target on PFM - RM host.
You must specify the information shown in the following table. Before you set the monitoring target, check the information in advance.
Table 2-22 Configuration for the monitoring target of PFM - RM for Oracle
Item | Description | Specifiable value | Default value | Changeable |
---|---|---|---|---|
Target Host | Oracle host name for monitoring target. If the Oracle host is a logical host, specify the logical host. | Host names can consist of 1 to 32 alphanumeric characters and hyphen. Note that you cannot specify a (logical) host name beginning with a hyphen. Physical and logical host names must be unique within the system.#1 | --#2 | Changeable |
In order to set up the monitoring target environment, execute the jpcconf target setup command. To set up the monitoring target environment:
jpcconf target setup -key RMOracle -inst instance-name -target monitoring-target-name
After you have finished entering the information, the monitoring target environment is set up in instlallation-directory/agt1. If you want to change the monitoring target information, re-execute the jpcconf target setup command and update the monitoring target environment. For details about updating the monitoring target environment, see 2.6.2 Updating a monitoring target.
Table 2-23 shows the organization of the monitoring target environment folder:
Table 2-23 Organization of the monitoring target environment folder
Directory name and file name | Description | |||
---|---|---|---|---|
agent | Instance name | targets | Monitoring-target-name.ini | Configuration file for the monitoring target |
Monitoring-target-name.ini.model | Model configuration file for the monitoring target |
To use PFM - RM for Oracle to monitor an Oracle Database, you must register the objects provided by PFM - RM for Oracle in the Oracle Database. The objects are registered by using an SQL script provided by PFM - RM for Oracle. The following procedure shows how to execute the SQL script. Note that the procedure is used only once for each account with which the Oracle Database instance is to be monitored.
To execute the SQL script:
/opt/jp1pc/agt1/agent/sql
Table 2-24 Table and packages to be created
Table | Package |
---|---|
LSC_14_PLAN_TABLE# | LSC_14_PDAS, LSC_14_73_PDDB, LSC_14_PDDB2, LSC_14_PDI, LSC_14_73_PIDB, LSC_14_PIDB2, LSC_14_PIDB3 |
To use the records provided by PFM - RM for Oracle to collect the performance data items listed in the following table, you must set the TIMED_STATISTICS Oracle Database initialization parameter to TRUE.
Table 2-25 Items that can be collected only when TIMED_STATISTICS=TRUE is set
Record | Field |
---|---|
Data File Interval (PI_PIDF) | Write Time (WRITE_TIME) |
Session Detail (PD_PDS) | Avg Wait (AVERAGE_WAIT) |
Avg Wait String (AVERAGE_WAIT_STRING) | |
Time Waited (TIME_WAITED) | |
Time Waited String (TIME_WAITED_STRING) | |
Session Statistics Summary (PD_PDS2) | Statement CPU (STATEMENT_CPU) |
System Stat Summary (PD) | Session CPU Usage (SESSION_CPU_USAGE) |
System Stat Summary Interval (PI) | Session CPU Usage (SESSION_CPU_USAGE) |
You must specify the network settings according to the configuration in which Performance Management is used.
The following are the two network setting items:
Performance Management outputs its operating status to a set of proprietary log files called a common message log. By default, the common message log consists of two 2,048 KB files. Perform this setting if you want to change the default file size.
For details, see the chapter on installation and setup in the Job Management Partner 1/Performance Management Planning and Configuration Guide.
Perform this setting if you want to change the folders where the database of performance data managed by PFM - RM for Oracle is saved, backed up, or exported.
By default, performance data is saved in the following locations:
For details, see 2.6.1 Changing the storage location of performance data.
On each host on which a PFM - RM for Oracle program is installed, set the connection-target PFM - Manager program that is to manage the PFM - RM for Oracle. Use the jpcconf mgrhost define command to set the connection-target PFM - Manager.
To set the connection-target PFM - Manager:
jpcconf mgrhost define -host host01
This setting is required to output action logs when alarms occur. The action log stores history information that is output in conjunction with the alarms for thresholds related to system load and other conditions.
For details about how to set up the action log, see K. Outputting Action Log Information.