Tuesday, January 29, 2008

When the Grid Agent couldnt find any target on host...

Subject: Manually adding targets to EM Grid Agent installed on a host.

Problem: As the installation procedure finishes successfully on a target host, EM agent examines "/etc/oratab" file (on UNIX operating system) to find the installed oracle products on the host. What if the specified file doesnt exist or somehow agent couldnt read the file?

Solution: Before the installation, you should check "/etc/oratab" file if it exists. If not, check the "/var/oracle" directory for the oratab file and make symbolic link named oratab under "/etc" directory.

If the agent cannot find any target on the host in all circumstances you can try adding the targets manually after installation. You will find configuration file, template entry for a "database" and a "listener".

EM Agent stores target information in targets.xml file located at "$AGENT_HOME/sysman/emd" directory. A sample file without any targets can be find as follows.



<Targets AGENT_TOKEN="e7244b84afad62c94aad53fe407af2514e3b7f04">
<Target TYPE="oracle_emd" NAME="oragrid:3872"/>
<Target TYPE="host" NAME="oragrid"/>
</Targets>



You can add a listener as a target to targets.xml file by using the following template. (values between the three stars - *** *** - are system specific. You should use your own systems parameters instead of *** *** values)



<Target TYPE="oracle_listener" NAME="***LISTENER_NAME***">
<Property NAME="ListenerOraDir" VALUE="$ORACLE_HOME/network/admin"/>
<Property NAME="LsnrName" VALUE="***LISTENER_NAME***"/>
<Property NAME="Machine" VALUE="***HOSTNAME***"/>
<Property NAME="OracleHome" VALUE="***$ORACLE_HOME***"/>
<Property NAME="Port" VALUE="***LISTENER_PORT***"/>
</Target>



The following template is for manually entering database target to a EM Agent. (values between the three stars - *** *** - are system specific. You should use your own systems parameters instead of *** ... *** values)



<Target TYPE="oracle_database" NAME="***DB_NAME***"/>
<Property NAME="OracleHome" VALUE="***$ORACLE_HOME***"/>
<Property NAME="UserName" VALUE="dbsnmp"/>
<Property NAME="MachineName" VALUE="***HOSTNAME***"/>
<Property NAME="Port" VALUE="1521"/>
<Property NAME="SID" VALUE="***$ORACLE_SID***"/>
<Property NAME="ServiceName" VALUE="***$SERVICE_NAME***"/>
<Property NAME="password" VALUE="***PASSWORD***" ENCRYPTED="FALSE"/>
<Property NAME="Role" VALUE="NORMAL"/>
</Target>



After you are done with your "targets.xml" file you should run emctl reload agent to read the configuration files from scratch. You can check the defined targets by running emctl config agent listtargets

Sunday, January 13, 2008

Installing Grid Agent to a target host

Subject: Installing Oracle Enterprise Manager Grid Control 10.2.0.3 agent on a target host.

I will examine the installation process in three main part. Preinstallation part, is a group of work should be done before executing runInstaller installation script. There is some work to to on OMS Server and on the TARGET side. The second part includes some screenshots and it is actually the time after you execute runInstaller script. Tha last part is the postinstallation part stands for some settings.


PREINSTALLATION

OMS

First you should enter a host record for the target host. IP and HOSTNAME record should be entered to /etc/hosts operating system file. One thing to remember, IP number should be the one listener executes on. Ping should answer after the configuration.


[oracle@oragrid ~]$ ping fmsserp1
ping: unknown host fmsserp1
[oracle@oragrid ~]$ su -
Password:
[root@oragrid ~]# cat /etc/hosts | grep fmsserp1
[root@oragrid ~]# vi /etc/hosts
[root@oragrid ~]# ping fmsserp1
PING fmsserp1 (10.4.9.83) 56(84) bytes of data.
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=0 ttl=254 time=0.219 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=1 ttl=254 time=0.197 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=2 ttl=254 time=0.198 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=3 ttl=254 time=0.193 ms
64 bytes from fmsserp1 (10.4.9.83): icmp_seq=4 ttl=254 time=0.197 ms

--- fmsserp1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.193/0.200/0.219/0.020 ms, pipe 2
[root@oragrid ~]# cat /etc/hosts | grep fmsserp1
10.4.9.83 fmsserp1
[root@oragrid ~]#


AGENT

Agent should also know the IP HOSTNAME pair of the OMS Server. You can also find the listening IP address to enter the OMS Server host file.

$AGENT_HOME should be exported. It is not a must but nice to have especially while searching the setup directory of the agent.

Be sure /etc/oratab file exists. On some operating systems the file exists on /var/oracle directory, if so you should create a soft link /etc/oratab for /var/oracle/oratab because agent will find the targets installed on the host by looking this file.

You can now go the installer directory to run the runInstaller. Be sure you have enough space in /tmp directory. If not you can delete older oracle installer logs.


$ hostname
fmsserp1
$ netstat -ni
Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll
lan0:1 1500 10.4.9.0 10.4.9.83 31833330 0 2849543 0 0
lan2* 1500 none none 0 0 0 0 0
lan1 1500 192.168.0.0 192.168.0.2 6331944 0 11558318 0 0
lan0 1500 10.4.9.0 10.4.9.82 2445705484 0 1271175018 0 0
lo0 4136 127.0.0.0 127.0.0.1 206103084 0 206104962 0 0
$ ping oragrid
sh: ping: not found.
$ su -
Password:
fmsserp1:/#ping oragrid
ping: unknown host oragrid
fmsserp1:/#
fmsserp1:/#cat /etc/hosts | grep oragrid
fmsserp1:/#
fmsserp1:/#cat /etc/hosts | grep oragrid
10.4.46.70 oragrid
fmsserp1:/#tail -2 /etc/hosts
#EM Agent
10.4.46.70 oragrid

fmsserp1:/#
fmsserp1:/#id
uid=0(root) gid=3(sys) groups=0(root),1(other),2(bin),4(adm),5(daemon),6(mail),7(lp),20(users)
fmsserp1:/#exit
logout root
$ id
uid=106(oradet) gid=104(dba)
$ cd $ORACLE_HOME/network/admin
$ cat listener.ora
LISTENER_ARCHIVE =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.9.83)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.9.83)(PORT = 1526))
)


SID_LIST_LISTENER_ARCHIVE =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AVEA93_C )
(ORACLE_HOME = /fmsarch_ora/product/9.2.0 )
(SID_NAME = AVEA93_C)
)
)


$
$ lsnrctl status LISTENER_ARCHIVE

LSNRCTL for HPUX: Version 9.2.0.6.0 - Production on 10-JAN-2008 11:42:22

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=10.4.9.83)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_ARCHIVE
Version TNSLSNR for HPUX: Version 9.2.0.6.0 - Production
Start Date 20-NOV-2007 06:19:08
Uptime 51 days 5 hr. 23 min. 14 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /fmsarch_ora/product/9.2.0/network/admin/listener.ora
Listener Log File /fmsarch_ora/product/9.2.0/network/log/listener_archive.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.9.83)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.4.9.83)(PORT=1526)))
Services Summary...
Service "AVEA93_C" has 1 instance(s).
Instance "AVEA93_C", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$ echo $AGENT_HOME

$ cd
$ export AGENT_HOME=/fmsarch_ora/product/agent10g/agent10g
$ echo $AGENT_HOME
/fmsarch_ora/product/agent10g/agent10g
$ cat /etc/oratab
#

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/fmsarch_ora/product/9.2.0:N
$
$ cd oracle_setup
$ ls
HPI_Grid_Control_agent_download_10_2_0_3_0.zip hpi
agent_download.rsp.bak instructions.txt
agentdeployroot.sh
$ cd hpi
$ ls
addons agentDownload.hpi agentdeploy prereqs
agent agent_scripts.jar oui response
$ cd agent
$ ls
install runInstaller stage
$ DISPLAY=10.1.22.210:0.0
$ export DISPLAY
$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be B.11.23. Actual B.11.23
Passed

Checking swap space: must be greater than 250 MB. Actual 4096 MB Passed
Checking temp space: 257 MB available, 500 MB required. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,at which time they will be rechecked.

Continue? (y/n) [n] n

User Selected: No
$ cd /tmp
$ ls -l | grep Ora
drwxrwxr-x 4 fmsarch fms 96 Jun 7 2006 OraInstall2006-06-07_01-03-59PM
drwxrwxr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-08-20PM
drwxrwxr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-27-41PM
drwxr-xr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-34-00PM
drwxr-xr-x 4 oraarch fms 8192 Jun 7 2006 OraInstall2006-06-07_01-50-37PM
drwxrwxr-x 4 fmsarch fms 8192 Jun 7 2006 OraInstall2006-06-07_11-42-22AM
drwxrwxr-x 4 fmsarch fms 8192 Jun 7 2006 OraInstall2006-06-07_11-44-54AM
drwxrwx--- 2 oraarch dba 96 Jan 10 09:49 OraInstall2008-01-10_09-49-20AM
$ ls -l | grep Ora | awk '{print $9}'
OraInstall2006-06-07_01-03-59PM
OraInstall2006-06-07_01-08-20PM
OraInstall2006-06-07_01-27-41PM
OraInstall2006-06-07_01-34-00PM
OraInstall2006-06-07_01-50-37PM
OraInstall2006-06-07_11-42-22AM
OraInstall2006-06-07_11-44-54AM
OraInstall2008-01-10_09-49-20AM
$ ls -l | grep Ora | awk '{print $9}' | xargs rm –r


RUNINSTALLER

This is the easiest part of the installation once you pass the "system check" screen. You should only know the IP address of the OMS Server and registration password, the one you obtain while installing the Grid Server.

After the root.sh script runs successfully there is only a few things to do.


$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be B.11.23. Actual B.11.23
Passed

Checking swap space: must be greater than 250 MB. Actual 4096 MB Passed
Checking Temp space: must be greater than 500 MB. Actual 640 MB Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2008-01-10_09-52-44AM. Please wait ...























fmsserp1:/fmsarch_ora/product/agent10g/agent10g #
$ id
uid=109(oraarch) gid=104(dba) groups=105(fms)
$ su - root
Password:
fmsserp1:/#
fmsserp1:/#cd /fmsarch_ora/product/agent10g/agent10g
fmsserp1:/fmsarch_ora/product/agent10g/agent10g#./root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oraarch
ORACLE_HOME= /fmsarch_ora/product/agent10g/agent10g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.






POSTINSTALLATION

Agent software uses dbsnmp user to obtain information from the database instances. So you should unlock the user if it is locked. Here I also change the password for security.

After unlocking and chaning the password of the dbsnmp user you should also inform the agent software about the changes. Rest of the subject is as follows :)


fmsserp1:/fmsarch_ora/product/agent10g/agent10g#exit
logout root
$ id
uid=109(oraarch) gid=104(dba) groups=105(fms)
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Jan 10 10:17:18 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production

SQL> select name from v$database;

NAME
---------
AVEA93_C

SQL>
SQL> alter user dbsnmp account unlock;

User altered.

SQL> alter user dbsnmp identified by ****;

User altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

$ ./emctl config agent listtargets
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
[fmsserp1:3872, oracle_emd]
[fmsserp1, host]
[LISTENER_ARCHIVE, oracle_listener]
[AVEA93_C, oracle_database]
$ ./emctl config agent credentials AVEA93_C:oracle_database
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Name = AVEA93_C, Type = oracle_database
Want to change for "UserName" (y/n):n
Want to change for "password" (y/n):y
Enter the new value for "password" :*******************
Re-enter the new value for "password" :*******************
EMD reload completed successfully
$ ./emctl reload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD reload completed successfully
$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully


When the works done successfully everything should be fine. Now is the time to wait a little while for Grid Server (OMS) to discovery newly installed target.

Friday, January 11, 2008

Mining Archived Logs with DBMS_LOGMNR_D

Oracle Database Logminer utility helps you to read your archived logs for what is running in your database at a specific time period.

-- following script comes with a fresh installation of oracle database. You can find it under your ORACLE_HOME/rdbms/admin directory. Script simply creates DBMS_LOGMNR_D package under SYS schema.
--@?/rdbms/admin/dbmslogmnrd.sql;

-- Contents of the mined archived logs can be examined via the following view.
SELECT * FROM V$LOGMNR_CONTENTS;

-- To determine which redo logs are being analyzed in the current Logminer session
SELECT * FROM V$LOGMNR_LOGS;

-- Shows information about the Logminer DICTIONARY FILE, provided the DICTIONARY was created USING the STORE_IN_FLAT_FILE OPTION.
-- The information shown includes the DATABASE name AND status information.
SELECT * FROM V$LOGMNR_DICTIONARY;

SELECT * FROM V$LOGMNR_PARAMETERS


/*
The TYPE OF CHANGE made TO the DATABASE (INSERT, UPDATE, DELETE, OR DDL).
The SCN AT which a CHANGE was made (SCN COLUMN).
The SCN AT which a CHANGE was committed (COMMIT_SCN COLUMN).
The TRANSACTION TO which a CHANGE belongs (XIDUSN, XIDSLT, AND XIDSQN COLUMNS).
The TABLE AND SCHEMA name OF the modified OBJECT (SEG_NAME AND SEG_OWNER COLUMNS).
The name OF the USER who issued the DDL OR DML STATEMENT TO make the CHANGE (USERNAME COLUMN).

Reconstructed SQL statements showing SQL that IS equivalent (but NOT necessarily identical) TO the SQL used TO generate the redo records (SQL_REDO COLUMN). IF a PASSWORD IS part OF the STATEMENT IN a SQL_REDO COLUMN, the PASSWORD IS encrypted.

Reconstructed SQL statements showing the SQL statements needed TO undo the CHANGE (SQL_UNDO COLUMN). SQL_UNDO COLUMNS that
correspond TO DDL statements are always NULL. Similarly, the SQL_UNDO COLUMN may be NULL FOR SOME datatypes AND FOR rolled back operations

You will need to add supplemental logging to mine your logs. By this way oracle database stores updated data by updated row data instead of ROWID basis. Logical update information is more meaningful rather than obtaining ROWID basis update statements. If a primary key or a unique index exists on the table, mined data will have these values as keys. In absence of these keys values will have all the row to identifiy updated data.

Another key factor is; if you mine your archived logs in a different database instead of mining in the production database ROWID information will worth nothing. In this kind of situation one needs to know the logical statements to mine meaningfully.
*/

----------------------------------------
-- adding supplemental logging and types
----------------------------------------
-- minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Identification key logging enables database-wide before-image logging of primary keys or unique indexes (in the absence of primary keys)
-- for all updates. With this type of logging, an application can identify updated rows logically rather than resorting to ROWIDs
-- i.e. logical standby
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


-- table supplemental logging
-- These columns will be logged every time an UPDATE statement
-- is executed on scott.emp, regardless of whether or not the update affected them
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp_parttime (empno, ename, deptno) ALWAYS;

-- before images of the columns will be logged only if at least one of the columns is updated.
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP emp_parttime (empno, ename, deptno);
ALTER TABLE scott.emp DROP SUPPLEMENTAL LOG GROUP emp_parttime;


-- to create all logminer objects in to the specified tablespace
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');

------------------------
-- to extract dictionary
------------------------
-- extracting to a flat file
-- You could also specify a filename and location without specifying the STORE_IN_FLAT_FILE option. The result would be the same.
-- alter system set UTL_FILE_DIR=; should be executed first
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

-- to extract dictionary to online redologs
-- database must be in archive mode and archiving should be enabled
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';


-- dont need to extract dictionary
-- with this command logminer uses online dictionary which is in the schema sys
-- The online catalog option is not valid with the DDL_DICT_TRACKING option.
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

-- tracking ddl statements
-- applies ddl commands in logminer internal dictionary
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);

-- With this option set, LogMiner applies any DDL statements seen in the redo logs to its internal dictionary.
-- For example, to see all the DDLs executed by user SYS, you could issue the following query
SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = 'SYS' AND OEPRATION = 'DDL';

-----------------------------
-- filtering redo_log actions
-----------------------------
-- If long-running transactions are present in the redo logs being analyzed,
-- use of this option may cause an "Out of Memory" error.
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION);
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/dictionary.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/oracle/dictionary.ora',
STARTSCN => 100,
ENDSCN => 150);


-----------------------------
-- querying v$logmnr_contents
-----------------------------
SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'ORDERS' AND
OPERATION = 'DELETE' AND USERNAME = 'RON';


-- MINE_VALUE example
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE
SEG_NAME = 'emp' AND
SEG_OWNER = 'SCOTT' AND
OPERATION = 'UPDATE' AND
DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'SCOTT.EMP.SAL') >
2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'SCOTT.EMP.SAL');


-- COLUMN_PRESENT example
SELECT
(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
(DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'SCOTT.EMP.SAL') -
DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'SCOTT.EMP.SAL')) AS INCR_SAL
FROM V$LOGMNR_CONTENTS
WHERE
DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, 'SCOTT.EMP.SAL') = 1 AND
DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, 'SCOTT.EMP.SAL') = 1 AND
OPERATION = 'UPDATE';


--------------------
-- a session example
--------------------
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora', OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'orcldict.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));


/*
--START_LOGMNR options
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
-- If you specify DICT_FROM_REDO_LOGS, LogMiner expects to find a dictionary in the redo logs
-- that you specified with the DBMS_LOGMNR.ADD_LOGFILE procedure before starting logmnr
DBMS_LOGMNR.DICT_FROM_REDO_LOGS
DBMS_LOGMNR.COMMITTED_DATA_ONLY
DBMS_LOGMNR.SKIP_CORRUPTION
DBMS_LOGMNR.DDL_DICT_TRACKING
-- DBMS_LOGMNR.ADD_LOGFILE options
DBMS_LOGMNR.NEW, DBMS_LOGMNR.ADDFILE, AND DBMS_LOGMNR.REMOVEFILE -- See Specify Redo Logs for Analysis
DBMS_LOGMNR.NO_SQL_DELIMITER
DBMS_LOGMNR.PRINT_PRETTY_SQL
DBMS_LOGMNR.CONTINUOUS_MINE
*/

SELECT * FROM v$logmnr_contents;

-- table hit statistics
SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM
V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY
SEG_OWNER, SEG_NAME;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

Saturday, January 5, 2008

Endless Status Pending in OMS Console

Problem: When I logged in the OMS console, i realized that one of the cluster instances has a problem with one of its nodes. Node_1 seems to be up but when i examine Node_2 the status seems like "Status Pending" in the OMS console of the Node_2.

When i check the agent on the target host, there was not any problem and the agent uploads metrics successfully.


[oracle@be02 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.3.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.2.0
Agent Home : /oracle/product/10.2.0/agent10g/ACTV_ACTV2
Agent binaries : /oracle/product/10.2.0/agent10g
Agent Process ID : 28158
Parent Process ID : 28049
Agent URL : https://be02:3872/emd/main
Repository URL : https://oragrid:1159/em/upload
Started at : 2008-01-04 10:47:17
Started by user : oracle
Last Reload : 2008-01-04 10:47:17
Last successful upload : 2008-01-04 10:49:05
Total Megabytes of XML files uploaded so far : 22.89
Number of XML files pending upload : 741
Size of XML files pending upload(MB) : 35.85
Available disk space on upload filesystem : 70.56%
Collection Status : Disabled by Upload Manager
Last successful heartbeat to OMS : 2008-01-04 10:48:30
---------------------------------------------------------------
Agent is Running and Ready


While i was scratching the OMS console to find a clue about the problem i realized that the target host has been restarted for a week ago and the last upload made by target agent was further then the current date . OMS console shows the last upload date four days future. How could it be? I decided to check the date and TZ environment variable for both target and OMS hosts.


-- target op sys date
[oracle@be02 bin]$ date
Fri Jan 4 14:53:38 EET 2008

-- OMS op sys date
[oracle@oragrid ~]$ date
Fri Jan 4 14:55:40 EET 2008

-- TZ Parameter of target
[oracle@be02 bin]$ echo $TZ

[oracle@be02 bin]$


It seems there is not any problem about the date settings of the host operationg systems. Let's have a look at the agent and OMS timezone regions.


-- Agents Timezone value
[oracle@be02 bin]$ ./emctl config agent getTZ
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Turkey

--emd.properties Timezone parameter value
[oracle@be02 config]$ tail -10 emd.properties
# at startup and reload. Currently this applies only to "Critical" marked dynamic
# properties. The following two values are applicable per target and not per
# dynamic property
#
# dynamicPropReComputeInterval --> time difference between a failed dynamic property
# computation and the next try to compute the property in seconds. The default value is 120 seconds.
#
# dynamicPropReComputeMaxTries --> maximum number of reties for calculating failed
# dynamic properties. The default value is 4 retires.
agentTZRegion=Turkey


Timezone values are also the same. There should not be any problem. Then what is the "Status Pending" state and the last upload date of the target in OMS. I decided to reset timezone settings of the agent and the oms.


-- Changing agents timezone
[oracle@be02 bin]$ ./emctl resetTZ agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Agent is running. Stop the agent and rerun the command.

[oracle@be02 bin]$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

[oracle@be02 bin]$ ./emctl resetTZ agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Updating /oracle/product/agent10g/agent10g/sysman/config/emd.properties...
Successfully updated /oracle/product/agent10g/agent10g/sysman/config/emd.properties.

Login as the em repository user and run the script:
exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey')
This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey')


[oracle@be02 bin]$ ./emctl start agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ..... started.

[oracle@be02 bin]$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

-- Executing OMS Script
[oracle@oragrid ~]$ sqlplus sysman/

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 4 14:19:19 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exec mgmt_target.set_agent_tzrgn('be02:3872','Turkey');

PL/SQL procedure successfully completed.



Lets check if everything is OK. When i check the OMS console for the status of the Node_2 of my cluster instance it seems to be recovered from the "Status Pending". Node_2 seems to be up in the cluster instances home page also. Everything went fine but one thing to notice. When i click to Node_2 home page in the OMS console weird thing i noticed. Latest Data Collected changed further than before. It now shows 11 days future for the last upload date. Everything goes fine but i still could not find an answer for this strange issue. Maybe somehow target hosts operating system date changed temporarily by unix administrators or by developers for accidentally or for test purposes and then reset to the current date.


You can check the following Oracle document for details of the commands used here:
Oracle® Enterprise Manager Advanced Configuration
10g Release 2 (10.2)
Part Number B16242-02
Chapter 10 - Reconfiguring the Management Agent and Management Service