Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: May 2014

Materials and scripts from DOAG 2014 presentation

Here is scripts that was presented on DOAG 2014 in Dusseldorf.

For EM Cloud Control 12c Version and

Script to convert EM Jobs to crontab format

First script resync_em_to_cron.sh is used to convert Enterprise Manager active jobs to crontab execution list format. It can list it on the screen with “list” option or add it to local crontab with “add” option.

Be aware that scripts requires direct connection to EM repository from host where it will be executed. Connection details must be specified inside the script, username by default is sysman. You also need to specify password of sysman user in this script, that is why it is better to revoke any privileges from all users except owner “chmod 700 resync_em_to_cron.sh“. Or, you can use Oracle Wallet to store the password, in this case you will need to adjust the script.

Also consider that as input argument to this script for add and list options you need to use host name as it registered in Enterprise Manager, target name of the host.

Usage examples:

Let’s assume that you are scheduled some jobs on EM Cloud Control to execute scripts on hosts saturn, neptune and jupiter. This jobs can be scheduled on host targets or on database targets or any other, at the end script will actually be executed on these nodes. At some point in time you require long downtime of your OMS. To let your scripts continue running, you want to reschedule them on local crontabs of saturn, neptune and jupiter hosts. Using this script you can do it very easy. Let’s first login to saturn host and execute resync_em_to_cron.sh with add option, you just need to manually specify name of the host target as it is registered in EM, for example if saturn host registered in EM as “saturn.example.com” then you need to use this name. First you can use “list” option of the script to list on the screen converted crontab entries:

-bash-$ > ./resync_em_to_cron.sh list saturn.example.com

*/15 * * * * /u01/shared/scripts/script3.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1
0 */2 * * * /u01/shared/scripts/rman_backup.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1 archivelog
0 */8 * * * /u01/shared/scripts/script2.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1
30 19 * * 1,2,4,5,6 /u01/shared/scripts/rman_backup.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1 level 1
*/30 * * * * /u01/shared/scripts/script1.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1
0 13 * * 0 /u01/shared/scripts/rman_validate.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1
0 22 * * 3,0 /u01/shared/scripts/rman_backup.sh salesdb_1:/u01/app/oracle/product/11.2.0/db_1 level 0

As you can see from output, it will convert all jobs scheduled to run on this host and to crontab entries format, it will automatically replace all variables used in the Job definition. For example script rman_backup.sh  was defined in the job as:

/u01/shared/scripts/rman_backup.sh %SID%:%OracleHome% archivelog

script replaced variables %SID% and %OracleHome% with the current actual values.

If output is Ok, you can deploy them to crontab manually or better by using “add” option of the script:

-bash-$ > ./resync_em_to_cron.sh add saturn.example.com

this command will add all entries into local crontab, you must execute it on host satrun. After each operation with crontab it will send email with report and before each operation, script will backup crontab file to the /tmp folder, filename format is crontab.bkp.DDMMYY-HH24MISS

To delete automatically added entries use “del” option of the script on local host:

-bash-$ > ./resync_em_to_cron.sh del

There is also option “all”, it will list all hosts which have some scripts scheduled on them with corresponding converted crontab entries. This option can be executed on daily basis and used in case of unplanned OMS downtimes.

In cases when you have tens or hundreds targets, then it will be not so easy to go to each host to execute this script! In this case special job can be created in Cloud Control Job Library and used to deploy all entries to all hosts in one go. How to do it I described in my DOAG presentation, which you can find on this blog.

Download: resync_em_to_cron.sh


Script to verify EM Administration Groups

This script will help to identify Targets placement problems. Check inside the script for more details. It must be executed against EM Repository database.

Download: verify_admin_groups.sql


Presentation itself, will be also shared soon. In case of any questions, contact me.