2008/11/30

Clean Uninstall Oracle for Windows

1. Uninstall oracle components using OUI
2. Run regedit and delete the following keys:
Delete the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE key which contains registry entries for all Oracle products by using regedit.
Delete any references to Oracle services in the following registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\.
Looks for the entries that starts with "Ora" which are obviously related to Oracle.
3. Root the system
4. Delete the Oracle home directory
5. Delete the Oracle Program Files directory
6. Delete the Oracle Start Menu shortcuts directory
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
7. Remove Oracle references from the path.
Start -> Settings -> Control Panel -> System -> Advanced -> Environments Variables

OMS Cannot Communicate with OMA on another Host

GC 10.2.0.4 on Windows Server 2003 R2
Agent 10.2.0.4 on RHEL 4

Problem:
Inside Grid Control, click on host and it takes a long time to see home page of that host.
Click on Host Performance page, error show up, something like "oracle.sysman.emSDK.emd.comm.CommException: No route to host: connect".
Check emagent.log, it seems all right. Check emoms.log, the same error is reported.

Solution:
From the error, it's something like no communication between oms and agent.
Restart agent several times, it succeed every time. Agent uploaded file successfully!
The communication from agent to oms seems all right. The problem might be in the communication from oms to agent. Use telnet to contact with agent using "telnet host port", it got refused! Some firewall or proxy might be between oms and agent. Contacted with SA, updated the firewall configuration on that port, it worked. The Host Performance page show up and reponsed quickly.

Configure Database Target Monitoring Credentials for Grid Control

GC 10.2.0.4

After intalling an agent on a linux host, everything in Grid Control is fine except that database status is unknown with a error "Metrics Collection Error". After I googled problem and checked documents, I found this may be caused by incorrect monitoring credentials settings. Oracle uses dbsnmp account to monitor target. In order to monitor database target, dbsnmp account must be valid (not locked) and what OMS expects and each componnet must be aware of the password for this account.

According to Section "Specifying New Target Monitoring Credentials" in "Oracle Enterprise Manager Advanced Configuration", there are two ways to modify monitoring credentials: Grid Control and emctl command.
Trying to configure database inside Grid Control by clicking on "configure", Properties page didn't show up as instructed.
So go to emctl command under AGENT_HOME/bin (on the target host):
emctl config agent credentials [Target_name[:Target_type]]
For example:
emctl config agent credentials :oracle_database

You can list the target names by:
emctl config agent listtargets

After configuration, go to database home page in Grid Control, it worked. Home page show up!

Agent Cannot Upload Data onto OMS

GC 10.2.0.4

Agent is started successfully, but no new target is displayed inside Grid Control.
Using command "emctl status agent", I found that no data was uploaded onto OMS.

Solution: emctl secure agent

Grid Control Loader Trhoughput Critical Error

GC 10.2.0.4

On the home page, I received critical error like this:

Metric Loader Throughput (rows per second)
Loader Name xxxxxxxxxxxxxxxxxxxx:4889_Management_Service,XMLLoader0
Severity Critical

Solution:

Add the following line into the file emoms.properties inside OMS_HOME/sysman/config:
em.loader.threadPoolSize = 2
Don't forget to shutdown oms before any update.

2008/11/10

Set Up OS Authentication

OS authentication allows Oracle to pass control of user authentication to the operating system.

UNIX and LINUX:
1) Use "useradd" and "passwd" command to create OS user.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) CREATE USER ops$user IDENTIFIED EXTERNALLY.
4) Grant privileges to the user.

WINDOWS:
1) Create local user (Start > Programs > Administrative Tools > Computer Management) or domain user in Active Directory.
2) Check OS_AUTHENT_PREFIX initialization parameter (ops$ for example).
3) Set SQLNET.AUTHENTICATOIN_SERVICES=(NTS) option in sqlnet.ora file.
4) Create a new registry parameter only if you are not authenticating a domain name with a user.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, add value, "OSAUTH_PREFIX_DOMAIN" for the name, "REG_EXPAND_SZ" for the data type, "true" for the string value. true enables the server to differentiate between multiple frank usernames, whether they are local user frank, domain user frank on sales, or domain user frank on another domain in your network. Entering false causes the domain to be ignored and local user frank to become the default value of the operating system user returned to the server.
5) CREATE USER ops$user IDENTIFIED EXTERNALLY; local user
CREATE USER "OPS$DOMAIN\USER" IDENTIFIED EXTERNALLY; domain user
"" is required, and characters inside must be in uppercase.

VNI-4040

OMS is out of sync with agent.

Solution:
shutdown Agent, delete the .q files, and start the agent. Refresh the node where the agent is running on.

High CPU Utilization after start OMS

Grid Control 10.2.0.2
Repository Database 10.1.0.4
Windows 2003 R2 SP2

After new installation of 10g Grid Control on this server, CPU keeps running 100%. Every time I stop OMS using "opmnctl stopall", CPU utilization falls back to normal.
Every time "opmnctl startall" is issued, CPU utilization go up to 100% again and never fall down.

After checking Task Manager, I found out oracle.exe occupies near 99% of CPU utilization.
On windows platform, there is only one oracle master process, and it is divided into several oracle threads.
In order to check CPU utilization for each thread, "Performance Monitor" is the best solutoin. "Diagnosing High CPU Utilization on NT" (metalink note: 116236.1) gives a detailed explanation.

After some check on dynamic performance views, using

SELECT ss.sid,se.command,ss.value CPU ,se.username,se.program
FROM v$sesstat ss, v$session se
WHERE ss.statistic# IN
(SELECT statistic#
FROM v$statname
WHERE name = 'CPU used by this session')
AND se.sid=ss.sid
ORDER BY ss.sid;

I'm still unable to identify the problem, even though I can find the suspicious sid.

I run into a discussion on a forum, and found a solution as follows:

Shutdown OEM, login as SYSMAN user and restart the provisioning daemon by executing the two packaged procedures

SYSMAN> execute MGMT_PAF_UTL.STOP_DAEMON
PL/SQL procedure successfully completed.

SYSMAN> execute MGMT_PAF_UTL.START_DAEMON
PL/SQL procedure successfully completed.

Start OEM again and the problem is gone.

And it worked.
It seems like a bug inside oracle.

Relocate tempfile in 10g

database 10.1.0.4

You cannot use "ALTER DATABASE RENAME FILE ... TO ..." to relocate a tempfile in 10g.

If the temporary tablespace to which tempfile belongs is the default tablespace, then
1) Copy the tempfile to the new location using OS command.
2) Create new temporary tablespace
CREATEA TEMPORARY TABLESPACE new_name TEMPFILE 'new_tempfile_name' REUSE;
3) Make it the default temp tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 'new_name';
4) Drop the old tablespace
DROP TABLESPACE 'old_name' including contents and datafiles;

If you want to keep the same temporary tablespace name, then rename the old name first using:

SQL> ALTER TABLESPACE 'old_name' RENAME TO 'new_name';