How to Point an Agent to a different Grid Control OMS and Repository?

Subject:	How to Point an Agent to a different Grid Control OMS and Repository?
	Doc ID:	Note:413228.1	Type:	HOWTO
	Last Revision Date:	25-FEB-2008	Status:	PUBLISHED

In this Document Goal Solution Applies to: Enterprise Manager Grid Control – Version: 10.2.0.0 Information in this document applies to any platform. Goal This Note intends to explain how to point an Oracle Management Agent to a different Grid Control OMS and Repository? NOTE: These steps are NOT necessary if you are simply pointing the Oracle Management Agent to a new OMS that points to the SAME repository.  If simply moving from one OMS to another within the same Grid infrastructure, follow the instructions in the Documentation, Oracle® Enterprise Manager Advanced Configuration, Chapter 12 Reconfiguring the Management Agent and Management Service, 12.1.1 Configuring the Management Agent to Use a New Management Service Solution A. Drop the Agent from the current OMS Note: this assumes that the Agent is still pointing to the old OMS. You will first drop the Agent from the old OMS, then add it to the new OMS. This can be ignored if the OMS doesn’t exist anymore. 1. Leave the monitoring agent up and running in order to delete all the targets, except the host and the agent itself). 2. From the old OMS, drop all the Targets on the host to be removed, except the Host and Agent. Do not remove Host yet, but to confirm all targets are gone, choose Targets > Hosts, highlight the host in the list and hit “Remove” button. Grid will not allow the Host to be dropped until all targets on the Host are gone, so this will list any remaining targets. Again: Do not remove Host yet. All targets except the Host and Agent should now be gone from the OMS. 3. Confirm the targets (except Host and Agent) are removed successfully and completely from the OMS: Click on “Setup” in upper right. Click on the “EM 10g Grid Control Management System” tab You will see this information on the page: –> Management Services and Repository –> Overview page –> General section –> Deleted Targets: < click on the digit listed here > 3a. Click on “Deleted Targets” (Note this only appears once a delete is attempted.) 3b. Confirm that the “Time Delete Completed” column is filled in; once that column is filled in then the delete is complete. 4. Stop the monitoring Agent 5. Drop the Host target from the old OMS. Targets > Hosts, highlight the host in the list and hit “Remove” button. Monitor for the “Time Delete Completed” column. Proceed once the column is updated. 6. Drop the Agent target from the old OMS. Targets > All Targets > [pick Agent from list] > “Remove” button. Monitor for the “Time Delete Completed” column. Proceed once the column is updated. –> At this point the removal from the old OMS is completed. B. Point the Agent at the new OMS 7. Follow the steps to reconfigure the Agent to point to the new OMS. Here is the Documentation reference, which does explain several steps in a clear manner and will help clarify the process needed. Oracle® Enterprise Manager Advanced Configuration, Chapter 12 Reconfiguring the Management Agent and Management Service, 12.1.1 Configuring the Management Agent to Use a New Management Service Quick Summary of steps to point Agent at new OMS To associate the Management Agent with a new Management Service after you have installed the Management Agent: #1 Stop the agent #2 Edit the Agent’s $ORACLE_HOME/sysman/config/emd.properties file #3 Search for REPOSITORY_URL Change this to point to the non-secure Grid Control hostname and port *also change HTTPS://….. to HTTP (non-secure.) #4 Search for EMD_URL This is your Agent’s address – if it has HTTPS, change it to HTTP #5 Modify the value for the emdWalletSrcUrl and emdWalletDest properties. The emdWalletSrcUrl references the new Management Service. The emdWalletDest is the location where the agent saves the wallet received from the OMS, in its own file system. This is normally a directory on the agent node. For example, if the new Management Service is on a host called mgmthost2.acme.com, modify the properties as follows: emdWalletSrcUrl=http://mgmthost2.acme.com:4889/em/wallets/emd emdWalletDest=<AGENT_HOME>/sysman/config/server #6 Save and exit emd.properties. Disclaimer: Clean starting the agent is only to be performed in this specific scenario and should only be used for all other issues under guidance from Oracle Support as this can damage the functionality of Grid Control and may require a re-installation. #7 Clean up Agent: from the Agent $ORACLE_HOME rm -r $ORACLE_HOME/sysman/emd/state/* rm -r $ORACLE_HOME/sysman/emd/collection/* rm -r $ORACLE_HOME/sysman/emd/upload/* rm $ORACLE_HOME/sysman/emd/lastupld.xml rm $ORACLE_HOME/sysman/emd/agntstmp.txt rm $ORACLE_HOME/sysman/emd/blackouts.xml rm $ORACLE_HOME/sysman/emd/protocol.ini #8 Issue an agent clearstate from the Agent $ORACLE_HOME $ORACLE_HOME/bin/emctl clearstate agent #9 Secure the agent $ORACLE_HOME/bin/emctl secure agent *if this fails, double-check OMS information and start from step #2 again #10 Start the agent $ORACLE_HOME/bin/emctl start agent *wait about 30 seconds before going to step 11 – give agent time to “wake up” #11 Force an upload to the OMS $ORACLE_HOME/bin/emctl upload agent After 10-15 minutes the Agent and Host should show up in the new OMS.

WHY ARE MY JOBS NOT RUNNING

ANSWERS TO “WHY ARE MY JOBS NOT RUNNING ?”

Thanks to : http://forums.oracle.com/forums/thread.jspa?threadID=646581&tstart=0

This is one of the most common Scheduler questions asked.
Here we list some of the common problems and their solutions.

1) job_queue_processes may be too low (this is the most common problem)
The value of job_queue_processes limits the total number of dbms_scheduler
and dbms_job jobs that can be running at a given time.
To check whether this is the case check the current value of
job_queue_processes with
SQL> select value from v$parameter where name=’job_queue_processes’;
Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;

If this is the problem you can increase the parameter using
SQL> alter system set job_queue_processes=1000;

2) max_job_slave_processes may be too low
If this parameter is not NULL then it limits how many dbms_scheduler jobs can
be running at a time. To check whether this is the problem, check the current
value using
SQL> select value from dba_scheduler_global_attribute
where attribute_name=’MAX_JOB_SLAVE_PROCESSES’;
Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;

If this is the problem you can increase the number or just NULL it out using
SQL> exec dbms_scheduler.set_scheduler_attribute(‘max_job_slave_processes’,null)

3) sessions may be too low
This parameter limits the number of sessions at any time. Every Scheduler job
requires 2 sessions. To check whether this is the problem, check the current
valule using
SQL> select value from v$parameter where name=’sessions’;
Then check the current number of sessions using
SQL> select count(*) from v$session ;

If the numbers are too close you can increase the maximum using
SQL> alter system set job_queue_processes=200;

4) Have you recently applied a timezone update patch or upgraded the database
to a version with newer timezone information ? If you skipped any steps when
updating the timezone information, jobs may not run. To check whether this
is the case try doing
SQL> select * from sys.scheduler$_job;
and make sure it finishes without errors.

If it throws a timezone warning, reapply the upgrade or
timezone patch making sure to follow all the steps.

5) Is the database running in restricted mode ?
If the database is running in restricted mode then no jobs will run (unless
you are using 11g and use the ALLOW_RUNS_IN_RESTRICTED_MODE attribute).
To check this use
SQL> select logins from v$instance ;

If logins is restricted you can disable the restricted mode using
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

6) Has the Scheduler been disabled ? This is not a supported action
but it is possible that someone has done it anyway. To check this do
SQL> select value from dba_scheduler_global_attribute where attribute_name=’SCHEDULER_DISABLED’

If this query returns TRUE then you can fix this using
SQL> exec dbms_scheduler.set_scheduler_attribute(’scheduler_disabled’,'false’);

Reasons why jobs may run late

1) The first thing to check is the timezone that the job is scheduled with
SQL> select owner, job_name, next_run_date from dba_scheduler_jobs ;

If the jobs are in the wrong timezone they may not run at the expected
time. If the next_run_date is using an absolute timezone offset (like
+08:00) instead of a named timezone (like US/PACIFIC) then the jobs may not
run as expected if daylight savings is in effect – they may run an hour
early or late.

2) It may be that at the time the job was scheduled to run, one of the several
limits above may have been temporarily reached causing the job to be delayed.
Check if the limits above are high enough and if possible check them during
the time that the job is being delayed.

3) One possible reason that one of the above limits may be hit is that a
maintenance window may have come into effect. Maintenance windows are Oracle
Scheduler windows that belong to the window group named
MAINTENANCE_WINDOW_GROUP. During a scheduled maintenance window, several
maintenance tasks are run using jobs. This may cause one of the limits listed
above to be hit and user jobs to be delayed. See the admin guide for more info
about this (chapter 24).

To get a list of maintenance windows use
SQL> select * from dba_scheduler_wingroup_members;

To see when the windows run use
SQL> select * from dba_scheduler_windows;

To fix this you can either increase the limits or reschedule the maintenance
windows to run at more convenient times.

Diagnosing other Problems

If none of this works, here are some further steps you can take to try to
figure out what is going on.

1) Check whether there are any errors in the alert log. If the database is
having trouble allocating memory or has run out of disk space or any other
catastrophic errors have occurred, you should resolve those first. You can
find the location of the alert log by using
SQL> select value from v$parameter where name = ‘background_dump_dest’;
The alert log will be in this directory with a name starting with “alert”.

2) Check whether if a job coordinator trace file and if it does, check if it
contains any errors. If this exists, it will be located in the
‘background_dump_dest’ directory which you can find as above and will look
something like SID-cjq0_nnnn.trc . If there are any errors here they may
hint at why jobs are not running.

3) If either of the above indicates that the SYSAUX tablespace (where the scheduler stores its logging tables) is full, you can use the dbms_scheduler.purge_log procedure to clear out old log entries.

4)try running a simple run-once job and see if it runs
SQL>begin
dbms_scheduler.create_job (
job_name => ‘test_job’,
job_type => ‘plsql_block’,
job_action => ‘null;’,
enabled => true);
end;
/
SQL> — wait a while
SQL> select * from user_scheduler_job_run_details where job_name=’TEST_JOB’;

HOW TO CHECK GROWTH OF THE DATABASE

Thanks to: http://ayyudba.blogspot.com/2007/09/how-to-check-growth-of-database.html

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 “TOTAL SIZE (MB)” from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 “FREE SPACE (MB)” from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total “TOTAL SIZE”,t1.free “FREE SPACE”,(t1.free/t2.total)*100 “FREE (%)” ,(1-t1.free/t2.total)*100 “USED (%)”
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE% “
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total “TOTAL_SIZE”,t1.free “FREE_SPACE”,(t1.free/t2.total)*100 “FREE%”
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format =’dd-mon-yyyy hh24:mi:ss’ ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, ‘begin insert into db_growth select * from v_db_growth;commit;end;’, sysdate, ‘SYSDATE+ 24′, TRUE);
commit;
end;

PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it’s other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth

Tracking oracle Logons

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
(
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE
)
TABLESPACE users;

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
AFTER LOGON ON DATABASE
DECLARE
session_id number;
BEGIN
select sys_context(’USERENV’,'SESSIONID’) into session_id from dual;
IF session_id != 0 –ignore internal connections
THEN
BEGIN
INSERT INTO session_audit (
user_id,
session_id,
host,
program,
logon_time,
logoff_time
)
VALUES(
user,
session_id,
sys_context(’USERENV’,'HOST’),
(SELECT program FROM v$session
WHERE sys_context(’USERENV’,'SESSIONID’) = AUDSID),
sysdate,
NULL
);
END;
END IF;
END;


UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context(’USERENV’,'SESSIONID’) = session_id;
END;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Running SQLPlus and PL/SQL Commands From A Shell Script

I got this from Tim Archer..

http://timarcher.com/?q=node/48

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
sqlplus system/manager@prod_db <<ENDOFSQL
select sysdate from dual;
exit;
ENDOFSQL

Try to create that shell script on your server and run it. Be sure to enter in the correct connect string. You’re screen output should show all of the output from the SQL Plus session that executes.

A More Advanced Example

Now I will show you a more advanced script that I run on my servers. It builds off the simple example above, except it does a few extra things:

• Checks command line parameters for the appropriate number of parameters.
• Exports the ORACLE_SID as passed in from the command line parameters.
• Runs the oraenv script for the specified ORACLE_SID to setup all the Oracle specific environment parameters.
• Checks the return code from SQL Plus to determine if the SQL script successfully ran and takes an appropriate course of action.

Create a file named test.sh and put the following code in it:

#!/bin/sh

# The name of this script
SCRIPT_NAME=test.sh

###################################################################
#
# Check for Invalid Command Line Arguments
# $1 = ORACLE_SID
#
###################################################################
if [ $# -lt 1 ]
then
  echo "Usage: $SCRIPT_NAME <ORACLE_SID>"
  echo "Example: $SCRIPT_NAME DWHSE PROD"
  exit
fi

###################################################################
#
# Setup Script Variables
#
###################################################################
#This is the oracle_sid of the instance that the oracle environment
#will be setup for.
export ORACLE_SID=$1

#
# Initially we need some oracle path in the PATH for the dbhome command
# to be found. We reset the PATH variable to the proper oracle_home/bin
# below
#
export ORACLE_HOME=/usr/local/banner/oracle/product/current
export PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH

export ORAENV_ASK=NO

. /usr/local/bin/oraenv

export ORAENV_ASK=YES

echo "Starting Create PIN for ORACLE_SID: $ORACLE_SID"

###################################################################
#
# Run our SQL Plus Commands
#
###################################################################
sqlplus system/manager <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
select sysdate from dual;
exit;
ENDOFSQL

ERRORCODE=$?

#Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
  echo "********************"
  echo "ERROR: The SQL Plus Command Failed. ErrorCode: $ERRORCODE"
else
  echo "********************"
  echo "SQL Plus Successfully Ran. ErrorCode: $ERRORCODE"
fi

The format to run this script will then be:

./test.sh ORACLE_SID

You’ll need to modify a few sections of the script to work in your environment as described below:

• In the section labeled Check for Invalid Command Line Arguments you will want to change the script to take an ORACLE_SID appropriate for your environment. The SID’s that are valid in my environment are PROD and DWHSE.
• Change the connect string from system/manager to be what is required for your environment. There is no need to specify the Oracle SID in this part of the connect string since the beginning of the script exports the appropriate SID.
• Plug in your own SQL to run within SQL Plus. In the example I gave you it will always succeed since selecting sysdate from dual will always work.

Running PL/SQL Procedures And Passing In Unix Environment Variables

Within the section specifying the SQL to run you can even call PL/SQL functions and procedures. If you want to call a PL/SQL function or procedure, make sure you prefix the procedure call with the keyword exec. Furthermore, you can pass your Unix environment variables into your SQL Plus session. The following example creates the environment variable named TEST_VAR and then runs SQL Plus calling DBMS_OUTPUT to print out the value in the variable.

export TEST_VAR="HELLO WORLD"
sqlplus usfbanner/webster <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
set serveroutput on;
exec dbms_output.put_line('${TEST_VAR}');
exit;
ENDOFSQL

Running The Script Through Cron

Once you are able to get a script created that performs all of the appropriate actions that you need to do, you can automate running it through cron. This is the simple part.

• As the user who will run the script (probably the oracle user), edit the crontab.

  crontab –e

• Add a line to run your script at the time you desire. For example, I have the following entry in the crontab for my oracle user that will run a script to send emails to people who new user accounts have been generated for.

  30 13 * * * /usr/local/banner/scripts/send_new_account_email.shl PROD

  The script will run at 1:30pm every day connecting to the PROD database instance. Lastly, the output from running this script will be emailed to the oracle user for my review. The emailing of any output is standard cron functionality.

Monitor the size of Log Files using OEM

answer got from HarryP

From Grid Control:

Navigate to Hosts.
Select a host you wish to monitor for disk usage/whatever.
Navigate to Metric and Policy Settings (near bottom of the page)
Ta-da! It’s all there for you to play with! You can add new metrics to monitor for.

Got my New 2008 Nissan Versa

I got my new Nissan Versa last May 17, 2008 for a total price of $16,108 from Tom Naquin. It was an automatic with a power package and remote keyless entry. The car was very nice in its super black color with silver kick plates and splash guards. I thought to my self the only missing is the rain guard and so I ordered a new rain guard from Ebay. A full tank of Gas on my nissan versa cost around $50 ~ $55 depending on the gas per gallon with a 13.2 gallon in a tank. This full tank of gas allows me to run my car for a month without replenishing my gas. A sure fuel economy car that gives me around 33 mpg on a highway and 26 on city.

Unfortunately it was June 15, 2008 (Fathers day) when a Hail storm hit our car at the Mishawaka area.  Good thing that me and my wife were inside the store (Best buys) when it happened. After the Hail Storm we immediately checked our car and it got a lot of dimples on the hood, Roof and Drivers side fender. Even my new Rain Guard was broken into pisces. We’ll we can’t do anything about that, It’s nobodys fault. But deep inside me it really hurt seeing my car like that.

So we decided to call my car Insurance (Geico) and they covered my damage claims. I told my self that it would be better if  Nissan Bodyshop will do the repair so I made an arrangement to have my car fix. But I was totally wrong! The guy from Nissan BodyShop named Greg Wittner did a lot of mess in my car. They broke my plastic panel on the Lift gate during the dent repair process, missing plastic clips, Untidy works, so many  tiny scratches made from my car. In short I’m not impressed with their work and I was totally upset. So I called on my dealer and report everything to him and he talked to Greg. The car was then scheduled again for polishing all the issues I have with my car and it stayed for about 2 weeks from them. Guess what? Some scratches were present, and lot of white wax on the edges. Well the plastic clips and panel boards were replaced but the service they provided me didn’t even reach my expectation. They have a very low customer service index.

So what else can we do? I grab my car back and I just polished my car to bring back its natural beauty. You can see its pictures at http://mykelchua.multiply.com

Hmmn… what a relief! Finally my car looks briliant once again and it has a new Rain guard too but its a mugen style  (much better than before). Now that everybody is looking for a fuel economy car, I really love my car. It serves its purpose!