Hitachi

JP1 Version 12 JP1/Performance Management - Agent Option for Oracle Description, User's Guide and Reference

3.1.4 Setting up PFM - Agent for Oracle

This subsection describes how to set up PFM - Agent for Oracle for operation.

[Figure] 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.

Organization of this subsection

(1) Set the LANG environment variable

The following table lists the LANG environment variable values supported by PFM - Agent for Oracle. If you set a language other than the ones indicated in the table below (such as German, French, Spanish, Korean, or Russian), C is assumed as the value of the LANG environment variable.

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.

Notes:

The language of the common message log is determined by the value set in the LANG environment variable at the time of service startup or command execution. This may result in character strings in multiple language encodings, such as Japanese and English.

Table 3‒2: LANG environment variables that can be used in PFM - Agent for Oracle

OS

Language and code

LANG value

HP-UX#

Japanese

Shift-JIS

  • ja_JP.SJIS

  • japanese

EUC

  • ja_JP.eucJP

  • japanese.euc

UTF-8

  • ja_JP.utf8

English

  • C

Simplified-Chinese

UTF-8

  • zh_CN.utf8

GB18030

  • zh_CN.gb18030

Solaris#

Japanese

Shift-JIS

  • ja_JP.PCK

EUC

  • ja

  • japanese

  • ja_JP.eucJP

UTF-8

  • ja_JP.UTF-8

English

  • C

Simplified-Chinese

UTF-8

  • zh_CN.UTF-8

  • zh_CN.UTF-8@pinyin

  • zh_CN.UTF-8@radical

  • zh_CN.UTF-8@stroke

  • zh.UTF-8

GB18030

  • zh_CN.GB18030

  • zh_CN.GB18030@pinyin

  • zh_CN.GB18030@radical

  • zh_CN.GB18030@stroke

AIX#

Japanese

Shift-JIS

  • Ja_JP

  • Ja_JP.IBM-932

EUC

  • ja_JP

  • ja_JP.IBM-eucJP

UTF-8

  • JA_JP

  • JA_JP.UTF-8

English

  • C

Simplified-Chinese

UTF-8

  • ZH_CN

  • ZH_CN.UTF-8

GB18030

  • Zh_CN

  • Zh_CN.GB18030

Linux#

Japanese

Shift-JIS

nothing

EUC

nothing

UTF-8

  • ja_JP.UTF-8

  • ja_JP.utf8

English

  • C

Simplified-Chinese

UTF-8

  • zh_CN.utf8

  • zh_CN.UTF-8

GB18030

  • zh_CN.gb18030
#

The ASCII 7-bit character set is supported, except for the following language types:

- Japanese

- English

- Simplified-Chinese

To Page Top

(2) Register PFM - Agent for Oracle [Figure]

To perform integrated management of PFM - Agent using PFM - Manager and PFM - Web Console, you must register PFM - Agent for Oracle with PFM - Manager and PFM - Web Console.

If PFM - Agent for Oracle is already registered in PFM - Manager and PFM - Web Console, you do not have to follow the procedure described below. If PFM - Agent for Oracle is not registered yet, manually register PFM - Agent for Oracle according to the procedure.

You can determine whether manual registration of PFM - Agent for Oracle is necessary by referring to the conditions described below.

Manually registering PFM - Agent for Oracle in PFM - Manager

When all of the following conditions apply, manually register PFM - Agent for Oracle in PFM - Manager:

  • The PFM - Agent for Oracle to be installed is of a product version that is not specified in the Release Notes for PFM - Manager.

  • PFM - Agent for Oracle is installed on a host other than PFM - Manager.

Manually registering PFM - Agent for Oracle in PFM - Web Console

When the following condition applies, manually register PFM - Agent for Oracle in PFM - Web Console:

  • The PFM - Agent for Oracle to be installed is of a product version that is not specified in the Release Notes for PFM - Web Console.

If, however, the Release Notes for PFM - Agent for Oracle state that it is necessary to execute the setup command, execute the setup command.

The following figure shows the flow of PFM - Agent registration.

Figure 3‒3: Flow of PFM - Agent for Oracle registration

[Figure]
Notes:

  • Register PFM - Agent before setting up an instance environment.

  • You do not need to register a new instance of an already registered version of PFM - Agent for Oracle when you add it to the Performance Management system.

  • When you have installed different versions of PFM - Agent for Oracle on different hosts, set up the older version before the newer version.

  • When you install PFM - Agent on the same host as PFM - Manager, the jpcconf agent setup command is executed automatically, and the following message is output to the common message log: KAVE05908-I New agent setup (pfm-agent-service-key) ended successfully. (version=version). Check the result of command execution in the log file. If execution was unsuccessful, try it again. For details about how to execute commands, see the chapter on commands in the manual JP1/Performance Management Reference.

  • Registration of PFM - Agent for Oracle creates the Oracle folder in Reports window and Alarms window of PFM - Web Console. If the Oracle file or folder already exists on the Reports window, rename the file or folder before registering PFM - Agent for Oracle.

(a) Copy the PFM - Agent for Oracle setup files

Copy the setup files from the host on which you installed PFM - Agent for Oracle to the hosts on which PFM - Manager and PFM - Web Console are installed.

To copy the files:

  1. If PFM - Web Console is running, stop it before copying the files.

  2. Copy the PFM - Agent setup files in binary mode.

    The following table shows the location of the setup files and where they should be copied.

    Table 3‒3: Setup files to be copied

    PFM - Agent for Oracle setup file

    Destination

    PFM program name

    OS

    Destination folder

    /opt/jp1pc/setup/jpcagtow.EXE

    PFM - Manager

    Windows

    installation-folder\setup

    /opt/jp1pc/setup/jpcagtou.Z

    UNIX

    /opt/jp1pc/setup/

    /opt/jp1pc/setup/jpcagtow.EXE

    PFM - Web Console

    Windows

    installation-folder\setup

    /opt/jp1pc/setup/jpcagtou.Z

    UNIX

    /opt/jp1pcwebcon/setup/

(b) Execute the setup command on the PFM - Manager host

On the PFM - Manager host, execute the following command to set up PFM - Agent for Oracle:

jpcconf agent setup -key Oracle

Although an example of interactive command execution is shown here, the jpcconf agent setup command can be also executed non-interactively. For details about the jpcconf agent setup command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

Notes on executing the command:

If any Performance Management programs or services are still running on the local host when you execute the jpcconf agent setup command, an error may occur. If an error occurs, make sure that all Performance Management programs and services have completely stopped, and then execute the jpcconf agent setup command again.

You can then delete the PFM - Agent setup files remaining on the PFM - Manager host.

(c) Execute the setup command on the PFM - Web Console host

On the PFM - Web Console host, execute the following command to set up PFM - Agent for Oracle:

jpcwagtsetup

You can then delete the PFM - Agent setup files remaining on the PFM - Web Console host.

To Page Top

(3) Create an Oracle account to be used in PFM - Agent for Oracle

To monitor an Oracle Database and collect performance data by using PFM - Agent for Oracle, you must perform either of the following setting tasks:

If the monitoring target is Oracle Database 12c Release 2 or later in a CDB configuration, PDBs and the root container (CDB$ROOT) can be monitored. To operate PFM - Agent for Oracle in a CDB configuration, use one of the following Oracle accounts for monitoring:

If you create Oracle accounts by executing the mk_user.sql script, the following users can be created:

The following table lists the operations that PFM - Agent 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 3‒4: Operations for the Oracle Database and required system privileges

Operations that PFM - Agent for Oracle performs for the Oracle Database

System privileges required to perform the operations

  • Searching the static data dictionary view

  • Searching the dynamic performance view

  • Executing the listener control utility

  • Acquiring the execution schedule of the selected SQL

  • Executing a stored package specific to PFM - Agent for Oracle

  • CREATE SESSION

  • CREATE TABLE

  • CREATE PROCEDURE

  • SELECT ANY DICTIONARY

  • SELECT ANY TABLE

  • INSERT ANY TABLE

  • DELETE ANY TABLE

  • UPDATE ANY TABLE

  • CREATE ANY INDEX

  • ALTER ANY INDEX

  • UNLIMITED TABLESPACE (This privilege is not needed when an assignment limit that allows writing to the default tablespace of the account used for monitoring has been set.)

The sys account has the system privileges shown in Table 3-4. When the sys account cannot be used for Oracle Database monitoring due to security requirements, use an Oracle account with the system privileges shown in Table 3-4.

To use an Oracle account that can use PFM - Agent for Oracle without use of the mk_user.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_user.sql script. Check the information before starting setup operations.

Table 3‒5: 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 PFMAGTO.

Note:

If you specify an existing account in the database as an account to be used by an instance of PFM - Agent for Oracle, a script error occurs.

Always check the account names existing in the database in advance, and specify an account that will be used only for the instance of PFM - Agent for Oracle.

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.

Note:

Do not specify the SYSTEM or INDEX tablespace as the default tablespace.

Before specifying the default tablespace, make sure that no problem occurs when a package for PFM - Agent for Oracle is registered in the tablespace. Alternatively, create an exclusive tablespace for PFM - Agent for Oracle, and then specify the tablespace as the default tablespace.

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.

Note:

Do not specify the SYSTEM, INDEX, or USERS tablespace as the default temporary tablespace.

Before specifying the default temporary tablespace, make sure that no problem will occur if the tablespace is used as the default temporary tablespace. Alternatively, create an exclusive tablespace for PFM - Agent for Oracle, and then specify the tablespace as the default temporary tablespace.

Notes:

  • Make sure that the value of each item consists of only 7-bit ASCII alphanumeric characters that do not exceed 30 bytes. If the value is longer than 30 bytes or includes a character that is not a 7-bit ASCII alphanumeric character, the script may operate incorrectly.

  • Make sure that the value of each parameter is a nonquoted identifier described in the Schema Object Naming Rules. If you specify a value that is not a nonquoted identifier, the script may operate incorrectly. For details about the Schema Object Naming Rules and nonquoted identifiers, see your Oracle documentation.

  • If you create a common user, by executing the mk_user.sql script, for monitoring the root container (CDB$ROOT) of Oracle Database 12c Release 2 or later in a CDB configuration, the default tablespace and default temporary tablespace that are specified in the mk_user.sql script must be included in all containers that belong to the CDB. For that reason, before executing the mk_user.sql script, create the default tablespace and default temporary tablespace in all containers that belong to the CDB. If the mk_user.sql script is executed without satisfying the requirements for creating a common user, the creation might fail and the KAVF12506-E message might appear. For details about the requirements for creating a common user, see the Oracle Database documentation.

  • If you want to check the details of an account created by mk_user.sql, see DBA_USERS, which is a static dictionary view for the monitoring-target Oracle Database.

    The following example shows how to view the tablespace for the account A4O in the static data dictionary view DBA_USERS. If it is clear from the execution results of this SQL statement that the account has been created in the wrong tablespace, delete the account, and then re-create it using mk_user.sql.

Example:

To check the details of Oracle account A4O in UNIX:

  1. From the command prompt, use the sys account to connect to SQL*Plus.

    sqlplus "sys/sys-account-password AS SYSDBA"

  2. Use SQL*Plus to execute the following SQL statement:

    SQL>select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from DBA_USERS where USERNAME='A4O';

  3. Check the execution results. For example, you can check the default tablespace from the DEFAULT_TABLESPACE column and the default temporary tablespace from the TEMPORARY_TABLESPACE column.

Note:

The method for connecting to SQL*Plus with the sys account may differ according to the Oracle version. For details, see the Oracle documentation.

Use SYSDBA privileges to connect to the Oracle Database that you want to monitor.

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:

  1. Set up an environment where the sqlplus Oracle command can be executed.

    For details about Oracle environment setup, see your Oracle documentation.

  2. Navigate to the following folder, which contains mk_user.sql provided by PFM - Agent for Oracle: 

    /opt/jp1pc/agto/agent/sql

  3. Execute the mk_user.sql script for the monitoring-target Oracle Database.

    Example:

    If the monitoring target is Oracle Database 12c Release 1 or earlier, or Oracle Database 12c Release 2 or later in a non-CDB configuration

    sqlplus Oracle-account/password-for-the-Oracle-account@net-service-name-for-the-monitoring-target-database @mk_user.sql

    If the monitoring target is Oracle Database 12c Release 2 or later in a CDB configuration

    - If you create a local user for a PDB

    sqlplus Oracle-account/password-for-the-Oracle-account@net-service-name-for-the-monitoring-target-PDB @mk_user.sql

    - If you create a common user for the root container (CDB$ROOT)

    sqlplus Oracle-account/password-for-the-Oracle-account @mk_user.sql

    Notes:

    • The sqlplus command is provided by Oracle Corporation.

    • The Oracle account with which the mk_user.sql script is executed must be granted the CREATE USER, CREATE SESSION and GRANT ANY PRIVILEGE system privileges before the script is executed.

    • If the SYS account is used to execute mk_user.sql script, an error may occur unless the AS SYSDBA option is specified.

    • Establish either a SYSDBA connection to the monitoring-target Oracle Database.

      The following shows an example of the mk_user.sql script:

      Example:

      If the monitoring target is Oracle Database 12c Release 1 or earlier, or Oracle Database 12c Release 2 or later in a non-CDB configuration

      sqlplus "sys/password-for-the-sys@net-service-name-for-the-monitoring-target-database AS SYSDBA" @mk_user.sql

      If the monitoring target is Oracle Database 12c Release 2 or later in a CDB configuration

      - If you create a local user for a PDB

      sqlplus "sys/password-for-the-sys@net-service-name-for-the-monitoring-target-PDB AS SYSDBA" @mk_user.sql

      - If you create a common user for the root container (CDB$ROOT)

      sqlplus "sys/password-for-the-sys AS SYSDBA" @mk_user.sql

    • When the mk_user.sql script is executed, the execution results are output to a spool file. Note that the spool file cannot be created in the following cases:

    • • The current directory has not been changed to the folder shown in step 2 when the mk_user.sql script is executed.

    • • A user without root privileges uses SQL*Plus to execute the mk_user.sql script.

  4. Set the parameters that are required to create an Oracle account.

    Enter the values for the items listed in Table 3-5 as prompted by the command. All items are mandatory. To use the default value displayed for an item, only press the Enter key.

When all values have been entered, the Oracle account is created.

Notes:

  • Before creating an account, check whether you want to acquire the value of the Explain Plan (EXPLAIN_PLAN) field in the SQL Text (PD_PDSQ) record for operations on the objects that belong to the SYS schema. If you want to do so, use sys as the account to be used by PFM - Agent for Oracle. If you use an account other than sys, you will no longer be able to acquire the value of that field. If the value of the EXPLAIN_PLAN field cannot be acquired, message Explain Plan Failed is stored in the field.

  • If the account used by PFM - Agent for Oracle has no privileges to access, or fails to reference, an object that belongs to a schema of the user who executed SQL, the following value cannot be acquired:

    The value of the Explain Plan (EXPLAIN_PLAN) field in the SQL Text (PD_PDSQ) record

  • If the value of the EXPLAIN_PLAN field cannot be acquired, message Explain Plan Failed is stored in the field. If you want to acquire the value of the Explain Plan (EXPLAIN_PLAN) field, execute the SQL for manipulating the field in the owner.table-name format.

  • Any Oracle account created using the mk_user.sql script is granted UPDATE ANY TABLE or another system privilege that can freely manipulate objects of other schemas. Manage such Oracle accounts with special care.

    The following table lists the privileges granted to Oracle accounts and the assignment limits of tablespaces.

    Table 3‒6: Privileges granted by mk_user.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 3-14).

    CREATE PROCEDURE

    Required when registering a procedure needed to monitor the Oracle Database, for the monitored Oracle Database (see the package in Table 3-14).

    SELECT ANY DICTIONARY

    Required when registering information needed to monitor the Oracle Database in the monitored Oracle Database (see Table 3-14) and when collecting information.

    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 3-14) and when obtaining the Explain Plan field of the PD_PDSQ record.

#

Any created account is granted a privilege to write to the default tablespace without any limit. To change the size of the tablespace allocated to an account after you have created the account, issue the ALTER USER statement in an environment where the sqlplus Oracle command can be executed. Note that any Oracle account with which you execute the ALTER USER statement must be granted the ALTER USER system privilege.

The following shows an example of changing the size of tablespace allocated to an account.

Example:
ALTER USER Oracle-account QUOTA maximum-tablespace-allocation-size ON tablespace-name;

For details about the ALTER USER statement, see your Oracle documentation.

To Page Top

(4) Set up an instance environment

The following table lists the instance information items that are to be specified. You can set up multiple instance environments by repeating the procedure for each instance.

This section describes the procedures for each of the actions.

(a) Set up instance information

You must specify instance information for the Oracle that is to be monitored by the PFM - Agent for Oracle. Specify instance information on the PFM - Agent 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 3‒7: PFM - Agent 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:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

The value specified for the -inst option of the jpcconf inst setup command

oracle_home#1

Oracle home folder used by PFM - Agent for Oracle (the same value as the value of the ORACLE_HOME environment variable)

A character string of 255 or fewer bytes that does not include the following characters:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

--

oracle_version#1

Version number of Oracle Database used by PFM - Agent for Oracle

When monitoring target is Oracle Database 12c or later (about Oracle Database 18c), 12 is set.

A two-digit number.

  • Oracle 10g: 10

  • Oracle 11g: 11

  • Oracle 12c or later: 12

In HP-UX: 10

In OSs other than HP-UX: 11

oracle_user#2

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 - Agent for Oracle.

A character string of 255 or fewer bytes that does not include the following characters:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

sys

oracle_passwd#2, #3

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:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

--

sqlnet#1,#4

Specify Y for any of the following three cases:

  1. If using an Oracle RAC configuration

    For details on the RAC configuration, see the Oracle documentation.

  2. If using a PD_PDIA record to monitor the availability of a listener.

  3. If the monitoring target is PDB in a CDB configuration of Oracle Database 12c R2 or later.

If you specify N for the case 1 or 3, above, Oracle might encounter an error.

{ Y | N }

N

net_service_name#1

The net service name of a monitoring-target database.

This value is enabled if you specified Y in sqlnet.

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:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

Instance name (the value of oracle_sid)

listener_home#1

Specify the environment variable ORACLE_HOME of the Oracle component containing the listener you want to monitor.

Specify environment variable GRID_HOME in the case of Oracle RAC environment.

A character string of 255 or fewer bytes that does not include the following characters:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

Instance name (the value of oracle_home)

listener_name

Specify the name of one listener that you want to monitor using the PDLS record. If a PDLS record is not being used to monitor the listener, specify the default listener name "LISTENER". This is because even though the specified value is not used, a blank is not permitted.

A character string of 255 or fewer bytes that does not include the following characters:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

Default listener name (LISTENER)

log_path#5

The full path name of the directory for storing agent log information

A character string of 245 or fewer bytes that does not include the following characters:

  • Spaces

  • Tabs

  • The following symbols:

    , < >

Notes:

  • You can specify the path to a directory under the installation directory only when the default directory is set.

  • You cannot specify the path to a directory that is used as the output destination of another instance.

/opt/jp1pc/agto/agent/instance-name/log

log_size#5

The maximum size of one agent log file#6

1 to 32 (in megabytes).

The recommended value is 16 or greater.

16

timeout#5,#7

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 3.4.3 Cancellation facility for Oracle access during record collection.

0

sql_option#5,#8

When Y is specified, information about the following items#8 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#9

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.#10

0

startup_always

PFM - Agent for Oracle may stop due to an Oracle connection error, such as when the monitored Oracle is still starting up when PFM - Agent 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

Store Version#11

Specify the version of the Store database to be used. For details about the version of the Store database, see 3.4.4 Updating the Store version to 2.0.

{ 1.0 | 2.0 }

2.0

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 - Agent for Oracle and Oracle Database.

Character code set:

Specifiable values vary depending on the combination of the OS LANG and NLS_CHARACTER_SET of Oracle Database. For details, see Table 3-9.

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, PD_PCTS, and PD_CDTS 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 }

Y
Legend:

--: None

#1

Depending on the OS, the required settings differ. The following table shows the OS and how to specify the settings.

OS

Method for specifying settings

Linux

Specify settings according to the explanation in the following section Settings in Linux.

AIX

Specify settings according to the explanation in the following section Settings in AIX or Solaris.

HP-UX(IPF)

Specify the settings according to the explanation in Table 3-7.

Solaris

Specify settings according to the explanation in the following section Settings in AIX or Solaris.

Setting in Linux

For monitoring of the Oracle database versions in a Linux environment, the 32-bit Oracle Client has been the required product in PFM - Agent for Oracle 10-50 or earlier. However, in PFM - Agent for Oracle 11-00 or later, the Oracle Client 64-bit library is used for monitoring.

Because instance information settings have been changed in version 11-00 or later as shown in the following table, make sure that the information is set up correctly:

Item

PFM - Agent for Oracle

10-50 or earlier

11-00 or later

oracle_home

Specify the Oracle home folder for Oracle Client 32-bit.

Specify the Oracle home folder for Oracle Database.

oracle_version

Specify the version of Oracle Client 32-bit.

Specify the version of Oracle Database.

When monitoring target is Oracle Database 12c or later (about Oracle Database 18c), 12 is set.

sqlnet

Specify Y

Specify a value according to the conditions described in Table 3-7.

net_service_name

Specify the net service name that can be used to connect to the monitoring-target Oracle Database specified in Oracle Client 32-bit.

If you specify Y for sqlnet, specify this item. Specify the name of the net service that you set in the Oracle database to be monitored and that can connect to the Oracle database.

Notes:

  • PFM - Agent for Oracle 11-00 or later uses the Oracle Client 64-bit library included with the Oracle database, so you do not need to install the 64-bit Oracle Client.

  • To upgrade PFM - Agent for Oracle 10-50 or earlier to 11-00 or later, instance information is subject to change, and must therefore be updated before starting the PFM - Agent for Oracle service.

    Note that Oracle Client 32-bit is no longer needed, and can be uninstalled if it is not used by any product other than PFM - Agent for Oracle.

  • If you specify ORACLE_HOME for Oracle Client 32-bit and start PFM - Agent for Oracle, the KAVF12011-E and KAVF12021-E message appear.

Setting in AIX or Solaris

For monitoring of the Oracle database versions in an AIX environment or a Solaris environment, the 32-bit Oracle Client has been the required product.

The following table shows the instance information for which you need to specify the environment settings, and the settings themselves.

Item

Description

oracle_home

Specify the Oracle home folder for Oracle Client 32-bit.

oracle_version

Specify the version of Oracle Client 32-bit.

When monitoring target is Oracle Database 12c or later (about Oracle Database 18c), 12 is set.

sqlnet

Specify Y

net_service_name

Specify the net service name that can be used to connect to the monitoring-target Oracle Database specified in Oracle Client 32-bit.

listener_name

Specify the Oracle home folder for the monitoring-target Oracle Database. In the case of Oracle RAC environment, specify the home of Oracle Grid Infrastructure.

Notes:

  • If you specify ORACLE_HOME from the Oracle database or ORACLE_HOME from the 64-bit Oracle Client for the instance information item oracle_home to start PFM - Agent for Oracle while the installation of the 32-bit Oracle Client and the creation of the environment are not complete, the KAVF12011-E and KAVF12021-E messages appear.

  • You need to install the 32-bit Oracle Client by specifying Administrator or run time for the installation type. Instant Client is not supported.

#2

PFM - Agent for Oracle runs using Oracle password authentication.

#3

If the expiration date is set on oracle_passwd, once the password is out of date connections to Oracle fail so that PFM - Agent for Oracle cannot collect the performance information. In order to avoid connection errors perform either of the following procedures before the password is expired:

  • Unset the expiration date of the password

  • After updating password, execute the jpcconf inst setup command to update oracle_passwd.

Note that the Oracle default profile is applied to the Oracle account created by mk_user.sql.

#4

Specify whether to use Oracle network services.

  • If Y is specified:

    PFM - Agent for Oracle connects to Oracle via a listener that is made up of Oracle network services.

    In this case, you must set the Oracle network service definitions (such as tnsnames.ora and listener.ora).

    When monitoring Oracle Database instances in an Oracle RAC configuration, set up the PFM - Agent for Oracle so that it monitors Oracle Database instances on each node.

    When using a CDB configuration of Oracle Database 12c Release 2 or later, set this setting in case of monitoring each PDB. In case of monitoring the root container (CDB$ROOT), this setting is not required.

    For details about how to set up, see the Oracle documentation.

    Store the tnsnames.ora file in the following directory.

    oracle_home/network/admin

    If you store the tnsnames.ora file in any other location, must therefore set to TNS_ADMIN environment variable in starting user of PFM - Agent for Oracle before starting the PFM - Agent for Oracle service. For details about the TNS_ADMIN environment variable, see the Oracle documentation.

  • If N is specified:

    PFM - Agent for Oracle connects to the local database without using the Oracle network services.

#5

When PFM - Agent for Oracle is upgraded from a version earlier than 08-00, the default values are set.

#6

A maximum of 4 agent log files are collected for one instance. Before specifying the log_size value, make sure that the value satisfies the following condition (this condition also applies when log_path is set to the default): 

Amount of free space on the disk containing the directory specified in log_path (MB) > log_size * 4

If the free disk space is insufficient, agent log cannot be output. For details about the agent log, see 8.3 Log information.

#7

Set the timeout value according to the time needed to collect records during heavy load (peak time).

#8

To obtain each piece of Oracle segment-related information, PFM - Agent for Oracle searches Oracle's static data dictionary views DBA_SEGMENTS. If a large number of segments (more than hundreds of thousands) exist for Oracle, information collection requires a significant amount of time. As such, when a large number of segments exist, and the information listed in the following table no longer needs to be collected, set the sql_option to Y during operation.

Table 3‒8: 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.

#9

When displayed in PFM - Web Console, this item indicates whether the values set in each field in #8 are values collected from the Oracle Database, or fixed values.

#10

If the field format for each record is float or double, since the data is a floating-point number, it may be rounded depending on the specified value.

Example:

When numeric_10 is set to 32767, it may be displayed as 32760.

#11

You can specify the Store Version item only when setting up a new instance environment. You cannot specify this item when updating an existing instance environment.

#12

When localtemp_option is set to N, collect free space of the locally managed temporary tablespace and information about the extents from v$temp_space_header of the dynamic performance view. The displayed values of the size of free space are the size of the unallocated space. Since the allocated space is not freed until the temporary tablespace is reconstructed or recreated, the displayed values of free space do not increase until the space is freed.

When localtemp_option is set to Y, collect free space of the locally managed temporary tablespace and information about the extents from v$sort_segment or v$temp_extent_pool of the dynamic performance view. The displayed values of the size of free space are calculated from the size of the used space.

When issuing query to v$temp_extent_pool view, the Oracle instance goes to sleep. Since this may have effect on the performance of the Oracle instance, you need adequate consideration before localtemp_option is set to Y. For details, see your Oracle documentation.

The following records use v$temp_extent_pool view:

  • Data File (PD_PDDF)

  • Data File Interval (PI_PIDF)

#13

The following figure shows the relationship among data, environment variable, and instance information handled by PFM - Agent for Oracle.

Figure 3‒4: Relationship between data and the setting values

[Figure]

PFM - Agent for Oracle can collect performance data in SJIS format (for Japanese environment), EUC format (for Japanese environment), UTF-8 format (for Japanese and Chinese environment) and GB18030 format (for Chinese environment) as well as 7-bit ASCII format. To collect performance data in a format other than 7-bit ASCII format, you need to set the nls_lang instance information of PFM - Agent for Oracle.

Acceptable values for the nls_lang instance information depend on the combination of the language environment of the OS and the database character set of the monitored Oracle. The following table lists acceptable values for the nls_lang instance information for each combination.

Table 3‒9: Relationship between the combination of the OS language environment and the database character set of the monitored Oracle and the nls_lang settings

OS

OS language (LANG)

NLS_CHARACTERSET of the monitored Oracle (Database character set)

nls_lang instance information (this item)

Linux

ja_JP.UTF-8, ja_JP.utf8

AL32UTF8

AMERICAN_AMERICA.AL32UTF8 or AMERICAN_AMERICA.US7ASCII#

zh_CN.UTF-8, zh_CN.utf8

zh_CN.gb18030

ZHS16GBK

AMERICAN_AMERICA.ZHS32GB18030 or AMERICAN_AMERICA.US7ASCII#

Other than the above

notdependent

AMERICAN_AMERICA.US7ASCII#

AIX

Ja_JP, Ja_JP.IBM-932

JA16SJISTILDE

AMERICAN_AMERICA.JA16SJISTILDE or AMERICAN_AMERICA.US7ASCII#

JA16SJIS

ja_JP, ja_JP.IBM-eucJP

JAEUCTILDE

AMERICAN_AMERICA.JA16EUCTILDE or AMERICAN_AMERICA.US7ASCII#

JAEUC

JA_JP, JA_JP.UTF-8

AL32UTF8

AMERICAN_AMERICA.AL32UTF8 or AMERICAN_AMERICA.US7ASCII#

ZH_CN, ZH_CN.UTF-8

Zh_CN, Zh_CN.GB18030

ZHS16GBK

AMERICAN_AMERICA.ZHS32GB18030 or AMERICAN_AMERICA.US7ASCII#

Other than the above

notdependent

AMERICAN_AMERICA.US7ASCII#

HP-UX

ja_JP.SJIS, japanese

JA16SJISTILDE

AMERICAN_AMERICA.JA16SJISTILDE or AMERICAN_AMERICA.US7ASCII#

JA16SJIS

ja_JP.eucJP, japanese.euc

JAEUCTILDE

AMERICAN_AMERICA.JA16EUCTILDE or AMERICAN_AMERICA.US7ASCII#

JAEUC

ja_JP.utf8

AL32UTF8

AMERICAN_AMERICA.AL32UTF8 or AMERICAN_AMERICA.US7ASCII#

zh_CN.utf8

zh_CN.gb18030

ZHS16GBK

AMERICAN_AMERICA.ZHS32GB18030 or AMERICAN_AMERICA.US7ASCII#

Other than the above

notdependent

AMERICAN_AMERICA.US7ASCII#

Solaris

ja_JP.PCK

JA16SJISTILDE

AMERICAN_AMERICA.JA16SJISTILDE or AMERICAN_AMERICA.US7ASCII#

JA16SJIS

ja, Japanese, ja_JP.eucJP

JAEUCTILDE

AMERICAN_AMERICA.JA16EUCTILDE or AMERICAN_AMERICA.US7ASCII#

JAEUC

ja_JP.UTF-8

AL32UTF8

AMERICAN_AMERICA.AL32UTF8 or AMERICAN_AMERICA.US7ASCII#

zh_CN.UTF-8, zh_CN.UTF-8@pinyin, zh_CN.UTF-8@radical, zh_CN.UTF-8@stroke, zh.UTF-8

zh_CN.GB18030, zh_CN.GB18030@pinyin, zh_CN.GB18030@radical, zh_CN.GB18030@stroke

ZHS16GBK

AMERICAN_AMERICA.ZHS32GB18030 or AMERICAN_AMERICA.US7ASCII#

Other than the above

notdependent

AMERICAN_AMERICA.US7ASCII#
#

Performance data is collected within the scope of 7-bit ASCII characters, likely resulting in unreadable characters.

Notes:

For any other combination, performance data is collected within the scope of 7-bit ASCII characters, likely resulting in unreadable characters.

When you specify an invalid character code set for the nls_lang instance information, the message KAVF12302-W with errcode 12705 is output, and the connection with Oracle will fail.

In the following cases as well, unreadable characters might occur in the performance data:

  1. The Oracle column length is exceeded.

    If you store data that exceeds the Oracle column length, the last character might become unreadable. If you use PFM - Agent for Oracle to collect data in Oracle and that data contains unreadable characters, the last character of the performance data will be unreadable.

  2. The field size of PFM - Agent for Oracle is exceeded.

    PFM - Agent for Oracle collects performance data of the record field size from Oracle. Therefore, if Oracle contains data that exceeds the field size, the last character of the performance data might be unreadable. The following table lists the applicable fields:

    Table 3‒10: Applicable fields (Unreadable characters caused by cases where a field size of PFM - Agent for Oracle is exceeded)

    Record name

    Field name

    Field size (Unit: bytes)

    Database Object Cache (PD_PDDO)

    Object Name

    100

    Errorlog Detail (PD_PDEL)

    Message

    512

    Parameter Values (PD_PDP)

    Value

    512

    SQL Text (PD_PDSQ)

    Explain Plan

    30,000

    SQL Text

    30,000

    SQL Text - Performance Based (PD_PDES)

    SQL Text

    10,000

    Table Access (PD_PDTA)

    Object

    100

  3. Unreadable or lack of characters in different between database character set of the Oracle and nls_lang instance variable of the PFM - Agent for Oracle.

    A data of 2 bytes on the basis of Oracle may be collected at 3 bytes when you set AMERICAN_AMERICA.AL32UTF8 in nls_lang and a database character set for the monitoring is not UTF-8. Therefore, if performance data takes from ORACLE that exceeds the field size, the last character of the performance data might be unreadable. The following table lists the applicable fields:

    Table 3‒11: Applicable fields (Unreadable or lack of characters caused by cases where the database character set differs from nls_lang)

    Record name

    Field name

    Field size (Unit: bytes)

    Circuit (PD_PDCI)

    User

    30

    Database Object Cache (PD_PDDO)

    Object Name

    100

    Owner

    64

    CDB Tablespace (PD_CDTS)

    Tablespace Name

    30

    Collection Instance 2 (PD_PCI)

    Recovery File Dest

    513

    Collection Tablespace 2 (PD_PCTS)

    Tablespace Name

    30

    Data File (PD_PDDF)

    File Name

    513

    Tablespace Name

    30

    Data File Interval (PI_PIDF)

    File Name

    513

    Tablespace Name

    30

    Database (PD_PDDB)

    DB Name

    9

    Database Interval (PI_PIDB)

    DB Name

    9

    Instance (PD_PDI)

    Host

    30

    Latch (PD_PDLA)

    OS User

    30

    Program

    48

    User

    30

    Latch Interval (PI_PILA)

    OS User

    30

    Program

    48

    User

    30

    Lock (PD_PDLO)

    Program

    48

    User

    30

    Lock Interval (PI_PILO)

    Program

    48

    User

    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

    Process Detail (PD_PDOP)

    Program

    48

    User

    15

    Rollback Segment (PD_PDRS)

    Tablespace Name

    30

    Rollback Segment Interval (PI_PIRS)

    Tablespace Name

    30

    Segment Detail (PD_PDSM)

    Owner

    30

    Segment Name

    81

    Tablespace Name

    30

    Session Detail (PD_PDS)

    Machine

    64

    Module

    48

    OS User

    30

    Program

    64

    Schema Name

    30

    User

    30

    Session Event (PD_PDEV)

    Program

    64

    User

    30

    Session Event Interval (PI_PIEV)

    Program

    64

    User

    30

    Session I/O Interval (PI_PIIO)

    User

    30

    Session Statistics Summary (PD_PDS2)

    Program

    48

    User

    30

    Session Stat Summary Interval (PI_PIS2)

    Program

    48

    User

    30

    Session Statistics (PD_PDSS)

    Program

    48

    User

    30

    Session Wait (PD_PDWA)

    Program

    48

    User

    30

    Shared Cursor Cache (PD_PDC)

    SQL Text

    1,000

    Sort Segment (PD_PDSR)

    Tablespace Name

    31

    Sort Segment Interval (PI_PISR)

    Tablespace Name

    31

    SQL Text (PD_PDSQ)

    Explain Plan

    30,000

    SQL Text

    30,000

    SQL Text - Performance Based (PD_PDES)

    Parsing User

    30

    SQL Text

    10,000

    Table Access (PD_PDTA)

    Object

    100

    Owner

    64

    Program

    48

    User

    30

    Tablespace Fragmentation (PD_PDTF)

    Tablespace Name

    30

    Tablespace Interval (PI_PITS)

    Tablespace Name

    30

    Tablespace (PD_PDTS)

    Tablespace Name

    30

    Transaction (PD_PDTR)

    User

    30

    Transaction Interval (PI_PITR)

    User

    30

    Transaction Lock (PD_PDTL)

    Object Name

    30

    Owner

    30

    User

    30
#14

When undospace_option is set to N, the size of the unallocated space is collected as the amount of free space in the UNDO tablespace. Space in the UNDO tablespace that becomes available because its retention period has expired is treated as allocated space until it is released.

When undospace_option is set to Y, the size of the free space is collected as the amount of free space in the UNDO tablespace. Space of the UNDO tablespace that becomes available because its retention period has expired is included in the size of the free space.

The following table shows the fields whose values change depending on the specification of the undospace_option:

Table 3‒12: the fields whose values change depending on the specification of the undospace_option

Record name

Field name

CDB Tablespace (PD_CDTS)

Free Mbytes

Free %

Used Mbytes

Max Extend Free %

Max Extend Free Mbytes

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

Max Extend Free %

Max Extend Free Mbytes

Collection Tablespace 2

(PD_PCTS)

Free Mbytes
Notes:

  • The PFM - Agent for Oracle services can be started only when an instance environment has been set up.

  • When you use the jpcconf inst setup command to create an instance environment, the command terminates normally even if an instance name that does not exist in Oracle is specified. However, if you then start record collection, message KAVF12401-W is output to the common message log, and you cannot connect to the monitored Oracle. If this problem occurs, check whether you specified the correct instance name, and re-execute the jpcconf inst setup command with the correct instance name specified.

  • Do not use PFM - Agent for Oracle and PFM - RM for Oracle to monitor the same Oracle instance. If so, the normal operation of PFM - Agent for Oracle is not guaranteed.

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:

  1. Execute the jpcconf inst setup command with a service key and instance name specified.

    For example, when you want to create the instance environment for the PFM - Agent for Oracle instance named SDC, use the following command line: 

    jpcconf inst setup -key Oracle -inst SDC

    Note that you cannot use sql as an instance name.

    Although an example of interactive command execution is shown here, the jpcconf inst setup command can be also executed non-interactively. For details about the jpcconf inst setup command, see the chapter on commands in the manual JP1/Performance Management Reference.

  2. Set up Oracle instance information.

    Enter the values for the items listed in Table 3-7 as prompted by the command. All items are mandatory. To use the default value displayed for an item, only press the Enter key.

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 3.4.2 Updating an instance environment.

The following describes the created instance environment.

  • Directory configuration of the instance environment

    The instance environment is set up in the following directory:

    For a physical host: /opt/jp1pc/agto

    For a logical host: environment-directory#/jp1pc/agto

    #

    The environment directory is a directory on the shared disk specified when the logical host was created.

    The following table describes the directory configuration of the created instance environment.

    Table 3‒13: Directory configuration of the instance environment

    Directory and file

    Description

    agent

    instance-name

    jpcagt.ini

    Agent Collector service startup initialization file

    jpcagt.ini.model#

    Model file for the Agent Collector service startup initialization file

    log

    Storage directory for log files

    store

    instance-name

    jpcsto.ini

    Agent Store service startup initialization file

    jpcsto.ini.model#

    Model file for the Agent Store service startup initialization file

    *.DAT

    Data model definition file

    dump

    Export destination directory

    import

    Standard database import destination directory (for Store version 2.0)

    backup

    Backup destination directory

    log

    Storage directory for log files

    partial

    Standard database partial backup destination directory (for Store version 2.0)

    STPD

    Performance data storage destination directory for records of the PD record type(for Store version 2.0)

    STPI

    Performance data storage destination directory for records of the PI record type (for Store version 2.0)
    #

    This file is used to reset all values to the initial values set when the instance environment was created.

  • Service ID for the instance environment

    The service for the instance environment has the following format:

    • Agent Collector service:

      OAinstance-number instance-name [host-name]

    • Agent Store service:

      OSinstance-number instance-name [host-name]

    In PFM - Agent for Oracle, the instance name specified in the jpcconf inst setup command is displayed.

    For example, if you execute the command with host name host1 and instance name SDC, the service IDs will be as follows:

    • Agent Collector service:

      OA1SDC [host1]

    • Agent Store service:

      OS1SDC [host1]

    For details about the service ID, see the naming rules described in Appendix in the JP1/Performance Management Planning and Configuration Guide.

(b) Registering objects in the Oracle Database

To use PFM - Agent for Oracle to monitor an Oracle Database, you must register the objects provided by PFM - Agent for Oracle in the Oracle Database. The objects are registered by using an SQL script provided by PFM - Agent 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.

If Oracle Database 12c Release 2 or later is to be monitored in a CDB configuration, register the objects only when instances of PFM - Agent for Oracle for monitoring PDBs have been created. If instances of PFM - Agent for Oracle for monitoring the root container (CDB$ROOT) have been created, there is no need to register the objects. Similarly, there is no need to register the objects when PDBs are to be monitored from the root container (CDB$ROOT) to which they belong, because, in this case, instances of PFM - Agent for Oracle are created for the root container (CDB$ROOT).

Monitoring target

Object registration necessary?

Root container (CDB$ROOT)

Note that the monitoring target is the root container (CDB$ROOT) when all PDBs are monitored from the root container (CDB$ROOT) to which they belong.

No.

PDB

Yes.

Execute sp_inst.sql on the PDBs to be monitored.

When the sys account is to be used for monitoring, execute sp_inst.sql for each PDB to be monitored.

  1. Set up an environment where the sqlplus Oracle command can be executed.

    For details about Oracle environment setup, see your Oracle documentation.

  2. Navigate to the following directory, which contains the sp_inst.sql file provided by PFM - Agent for Oracle: 

    /opt/jp1pc/agto/agent/sql

  3. Execute the sp_inst.sql script for the Oracle Database that you want to monitor.

    Connect to the Oracle Database by using the account specified by oracle_user in the instance information, and then execute the sp_inst.sql script.

    The sp_inst.sql script will register with Oracle the objects (procedures for monitoring and tables for operation) PFM - Agent for Oracle needs to perform Oracle monitoring.

    Note:

    Do not use the sp_inst_seg2.sql script.

    Example:

    If the monitoring target is Oracle Database 12c Release 1 or earlier, or Oracle Database 12c Release 2 or later in a non-CDB configuration

    sqlplus Oracle-account/password-for-the-Oracle-account@net-service-name-for-the-monitoring-target-database @sp_inst.sql

    If the monitoring target is Oracle Database 12c Release 2 or later in a CDB configuration

    sqlplus Oracle-account/password-for-the-Oracle-account@net-service-name-for-the-monitoring-target-PDB @sp_inst.sql

    • The sqlplus command is provided by Oracle Corporation.

    • Specify the oracle_user value as the Oracle account. The objects are created in the database with the Oracle account used here. You must specify the same Oracle account when setup of the instance environment is canceled.

    • When you use a SYS user for the Oracle account, executing the sp_inst.sql script without specifying the AS SYSDBA option may result in an error. If an error occurs, execute the script with the AS SYSDBA option specified.

    When the above command is executed, the table and packages shown in the following table are created.

    Table 3‒14: Table and packages to be created

    Table

    Package

    LSC_13_PLAN_TABLE#

    LSC_13_PDAS, LSC_13_PICS, LSC_13_73_PDDB, LSC_13_PDDB2, LSC_13_PDI, LSC_13_717273_PDMT, LSC_13_PDS3, LSC_13_73_PIDB, LSC_13_PIDB2, LSC_13_PIDB3

#

LSC_13_PLAN_TABLE is only used during collection of the SQL Text (PD_PDSQ) record. Therefore, when you collect the SQL Text (PD_PDSQ) record, make sure that at least 5 megabytes of free space is allocated to the default tablespace.

(c) Setting up the Oracle Database

To use the records provided by PFM - Agent 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 3‒15: Items that can be collected only when TIMED_STATISTICS=TRUE is set

Record

Field

Remarks

ASM Disk (PD_PDDK)

Read Time (READ_TIME)

--

Write Time (WRITE_TIME)

--

Block Contention Interval (PI_PIBC)

--

Entire record

Block Contention Statistics (PD_PDBC)

--

Entire record

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 Event (PD_PDEV)

Avg Wait (AVERAGE_WAIT)

--

Avg Wait String (AVERAGE_WAIT_STRING)

--

Time Waited (TIME_WAITED)

--

Time Waited String (TIME_WAITED_STRING)

--

Session Event Interval (PI_PIEV)

Avg Wait (AVERAGE_WAIT)

--

Avg Wait String (AVERAGE_WAIT_STRING)

--

Time Waited (TIME_WAITED)

--

Time Waited String (TIME_WAITED_STRING)

--

Session Stat Summary Interval (PI_PIS2)

Statement CPU (STATEMENT_CPU)

--

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)

--

Session Wait (PD_PDWA)

Wait Time (WAIT_TIME)

--

Wait Time String (WAIT_TIME_STRING)

--

System Event (PD_PDSE)

Avg Wait (AVERAGE_WAIT)

--

Time Waited (TIME_WAITED)

--

System Event Interval (PI_PISE)

Avg Wait(AVERAGE_WAIT)

--

Time Waited (TIME_WAITED)

--
Legend:

--: None

Notes:

  • If you modify the initialization parameters file, you must restart the instance's database.

  • Starting from Oracle 9i, the server parameters file is supported to store Oracle parameter information.A value change you make in the server parameters file may take precedence over a change made to the initialization parameters file.

  • Setting the TIMED_STATISTICS initialization parameter to TRUE may have adverse effects on the performance of the Oracle Database. If you plan to use this setting, you should first evaluate the possible effects. For details, see your Oracle documentation.

To Page Top

(5) Specifying network settings [Figure]

You must specify the network settings according to the configuration in which Performance Management is used.

The following are the two network setting items:

To Page Top

(6) Change the size of log files [Figure]

Performance Management outputs its operating status to a set of proprietary log files called a common message log. Perform this setting if you want to change the default file size.

For details, see the chapter on installation and setup in the JP1/Performance Management Planning and Configuration Guide.

To Page Top

(7) Change the storage location of performance data [Figure]

Perform this setting if you want to change the folders where the database of performance data managed by PFM - Agent for Oracle is saved, backed up, or exported.

By default, performance data is saved in the following locations:

Note:

For the default save destination for logical host operation, replace /opt/jp1pc with environment-directory/jp1pc.

#

This folder can be set only when the Store version is 2.0.

For details, see 3.4.1 Changing the storage location of performance data.

To Page Top

(8) Set the connection-target PFM - Manager of PFM - Agent for Oracle

On each host on which a PFM - Agent program is installed, set the connection-target PFM - Manager program that is to manage the PFM - Agent. Use the jpcconf mgrhost define command to set the connection-target PFM - Manager.

Notes:

  • When multiple instances of PFM - Agent are installed on a single host, you can specify only one PFM - Manager as their connection target. You cannot have a different instance of PFM - Manager as the connection target for each PFM - Agent.

  • When PFM - Agent is installed on the same host as PFM - Manager, the connection-target PFM - Manager will be the PFM - Manager on the local host. You cannot change this.

To set the connection-target PFM - Manager:

  1. Stop all Performance Management programs and services.

    Stop all active Performance Management programs and services on the host before beginning setup. For details about how to stop services, see the chapter on starting and stopping Performance Management in the JP1/Performance Management User's Guide.

    If any Performance Management programs or services are running when you execute the jpcconf mgrhost define command, you will be prompted by a message to stop the programs or services.

  2. Execute the jpcconf mgrhost define command with the host name of the PFM - Manager host you wish to use specified as the connection target.

    For example, if the PFM - Manager you wish to use as the connection target resides on host host01, execute the command as follows: 

    jpcconf mgrhost define -host host01

Although an example of interactive command execution is shown here, the jpcconf mgrhost define command can be also executed non-interactively. For details about the jpcconf mgrhost define command, see the chapter on commands in the manual JP1/Performance Management Reference.

To Page Top

(9) Setting up the action log [Figure]

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.

To Page Top

Copyright (C) 2019, 2020, Hitachi, Ltd.

Copyright (C) 2019, 2020, Hitachi Solutions, Ltd.