Resetting Timezone of a Grid Agent 10.2

Problem: Operating system Timezone is changed after an Oracle Grid Agent installation. This problem occured on an IBM AIX 5.3 host. Maybe one of our UNIX administrators decided to change the Timezone setting $TZ in our UNIX environment. Our Oracle Grid Agent couldnt be opened after that.

#> $AGENT_HOME/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 ....... failed.
The agentTZRegion value in /oracle/product/10.2.0/agent/agent10g/dwhnserp0/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(, ) to get the value propagated to repository.
Consult the log files in: /oracle/product/10.2.0/agent/agent10g/dwhnserp0/sysman/log


#> echo $TZ
MEST-2MEDT,M3.5.0,M10.5.0


#> export TZ=+2:00

#> $AGENT_HOME/bin/emctl resetTZ agent

emctl resetTZ agent command automatically changes the agentTZRegion entry of the emd.properties file. But here I also changed the last row of the file $AGENT_HOME/sysman/config/emd.properties to '+2:00' manually.
#agentTZRegion=Etc/GMT-3
agentTZRegion=+2:00

Now on the Grid Server run the following command. First parameter is the name of the target, and the second one is timezone value. Name of the target is usually nodename:portnumber.

#> sqlplus "/ as sysdba"
sqlplus> exec mgmt_target.set_agent_tzrgn('dwhnserp0:3872', '-2:00');
sqlplus> exit;
#>

You can also check the registered timezone property of the target with the following query. Again from the Grid Server:

#> sqlplus "/ as sysdba"
sqlplus> select timezone_region from mgmt_targets 
         where target_name = 'dwhserp00:3872';
sqlplus> exit;
#>

#> $AGENT_HOME/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.

#> $AGENT_HOME/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/agent/agent10g/dwhnserp0
Agent binaries    : /oracle/product/10.2.0/agent/agent10g
Agent Process ID  : 525810
Parent Process ID : 143586
Agent URL         : https://dwhnserp0:3872/emd/main
Repository URL    : https://oragrid:1159/em/upload
Started at        : 2008-04-11 16:28:27
Started by user   : oracle
Last Reload       : 2008-04-11 16:28:27
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :        6
Size of XML files pending upload(MB)         :     0.34
Available disk space on upload filesystem    :    41.66%
Last successful heartbeat to OMS             : 2008-04-11 16:28:46
---------------------------------------------------------------
Agent is Running and Ready

You can find the supported Timezone values in a file under sysman directory

cat $AGENT_HOME/sysman/admin/supportedtzs.lst | more
# Timezone regions with +02:00 standard offset from GMT
Africa/Cairo
Africa/Johannesburg
Africa/Khartoum
Africa/Tripoli
Africa/Windhoek
Asia/Beirut
Asia/Damascus
Asia/Gaza
Asia/Istanbul
Asia/Jerusalem
Asia/Nicosia
Asia/Tel_Aviv
EET
Egypt
Etc/GMT-2
Europe/Athens
Europe/Bucharest
Europe/Helsinki
Europe/Istanbul
Europe/Kaliningrad
Europe/Kiev
Europe/Minsk
Europe/Riga
Europe/Simferopol
Europe/Sofia
Europe/Tallinn
Europe/Vilnius
Israel
Libya
Turkey

# Timezone regions with +03:00 standard offset from GMT
Africa/Djibouti
Africa/Mogadishu
Africa/Nairobi
Asia/Aden
Asia/Amman
Asia/Baghdad
Asia/Bahrain
Asia/Kuwait

Scheduling a datapump job in Oracle 10g - Part2

Previous part of this article was about exporting a table data by using dbms_datapump via plsql. You can obtain information about creating a procedure that exports a table or group of table to an operationg system file.

This part includes information about creating a Scheduled Job to run the export procedure periodically. As Scheduled Jobs come with Oracle 10g, scripts in this article will work on databases later than Oracle 10.1.x.x

The sys package we will mainly use is DBMS_SCHEDULER. First we should create a job. You can find the following command that i used to create a job. Alternate parameters are explained with comments.

BEGIN 
    DBMS_SCHEDULER.CREATE_JOB(
       job_name           =>  'log_to_table',
       job_type           =>  'STORED_PROCEDURE',   
                              
                              -- Possible values could be:
                              -- PLSQL_BLOCK, 
                              -- STORED_PROCEDURE, 
                              -- EXECUTABLE, 
                              -- CHAIN.
       job_action         =>  'D_EPEKER.p_test_01', -- procedure name to run
       start_date         =>  'sysdate', -- date_to_start_execution date
       repeat_interval    =>  'FREQ=HOURLY', -- every other day
                              
                              -- Possible values could be:
                              -- "FREQ = YEARLY" | "MONTHLY" | "WEEKLY" | 
                              -- "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" 
                              -- INTERVAL = 1 through 99 
         
       end_date           =>  'sysdate+1',     -- date_to_end_execution
       job_class          =>  'logging_class',  
       comments           =>  'job_logs_something');-- explanation of the job
END;

/*
Jobs are created disabled by default and need to be enabled to run. You can find the
following procedure to alter a Scheduled Jobs attribute. By default a scheduled job
drops itself after its execution ends. You should set "auto_drop" attribute to 
FALSE to hold the Scheduled Job after its execution ends.
*/
-- 
-- enabling a job 
-- 
BEGIN
  SYS.DBMS_SCHEDULER.ENABLE 
    (name => 'D_EPEKER.LOG_TO_TABLE');
END;

-- 
-- altering a job attribute
-- 
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'log_to_table',  -- name of the job
        attribute => 'auto_drop',  -- attribute name
        value => FALSE  -- new value of the attribute
        );
END;

/*
As you can schedule a job, you can also run any Scheduled Job before its Scheduled
time comes. To run or stop a job immediately you can use the following command.
*/
-- 
-- executing a job immediately 
-- 
exec DBMS_SCHEDULER.RUN_JOB('log_to_table',FALSE);
exec DBMS_SCHEDULER.STOP_JOB('log_to_table',TRUE);

/*
The most likely property of this newly "Scheduled Job" is that they can be easily
maintained and monitored by system views. You can find the related view in the
following.
*/
-- schedued jobs whichs owner is D_EPEKER
select * from dba_scheduler_jobs where owner='D_EPEKER';

-- you can also user user_scheduled_jobs view to find out the jobs and their properties
select * from user_scheduler_jobs;

-- run details of the scheduled jobs
select * from DBA_SCHEDULER_JOB_RUN_DETAILS 
  where lower(job_name) = 'log_to_file_external;

/*
There is a lot of way to determine the repeat interval. There is lots of examples 
in the following and you can also find the explanations of the frequencies.
*/
-- 
-- setting repeat_interval 
-- 
--Run every Friday
FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

--Run every other Friday
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

--Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

--Run on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-2;

--Run every 10 days
FREQ=DAILY; INTERVAL=10;

--Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

--Run on the 15th day of every other month
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

--Run on the 29th day of every month
FREQ=MONTHLY; BYMONTHDAY=29;

--Run on the second Wednesday of each month
FREQ=MONTHLY; BYDAY=2WED;

--Run on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;

--Run every 50 hours.
FREQ=HOURLY; INTERVAL=50;

--Run on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

--Run hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;

--Run on the last workday of every month, excluding company holidays 
--(This example references an existing named schedule called Company_Holidays.)
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

--Run at noon every Friday and on company holidays.
FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays