Tag Archives: enable

linux, Oracle 10g, RAC, unix

Enabling Oracle RDBMS RAC features

Leave a comment

The determination of Oracle binary support for Real Application Clustering is made during installation on all supported platforms. Thus, an installation made for a single instance database will not support creation of RAC by default. However, you can modify the binaries to support these features after installation. This article details the steps to modify your Oracle binaries to support Oracle RAC.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directory to your ORACLE_HOME/rdbms/lib.

cd $ORACLE_HOME/rdbms/lib

3. Use make to recreate your Oracle binaries with the option rac_on

make -f ins_rdbms.mk rac_on

4. Change directory to your ORACLE_HOME/bin.

5. Execute the utility relink with option all, to recreate all binary files.

relink all

6. This completes altering your Oracle RDBMS binaries to support Oracle RAC features.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Disabling and Enabling Oracle Jobs with DBMS_JOB

Leave a comment

Oracle provides the ability to schedule predetermined jobs via the package DBMS_JOB, just as you would in a UNIX environment with CRON or the Windows environment with SCHEDULER. Normally, a production database will have several dozen pre-scheduled jobs executing at various times of the day. There are times when you will need to disable a JOB or all jobs due to various events. This article explains the use of procedure DBMS_JOB.BROKEN which allows you to disable a job and re-enable it once you are finished. This procedure will work in Oracle 8, 8i, 9i, 10g, and 11g.

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 – Production on Tue May 25 10:19:38 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g

Disabling and Enabling Oracle job with DBMS_SCHEDULER.

Leave a comment

Oracle 10g and higher provides the DBMS_SCHEDULER package to create pre-defined jobs for execution, like executing schedule jobs in UNIX with CRON and in Windows with the SCHEDULER. There are times during maintenance when you need to disable scheduled jobs from executing in order to perform other tasks. The disabling of scheduled jobs is performed by the procedure DBMS_SCHEDULER.DISABLE. In this article we will review the use of DBMS_SCHEDULER in disabling and re-enabling pre-defined Oracle jobs. This procedure will work in Oracle 10g and 11g.

1. Connect to SQL*PLUS as sysdba.

Mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 21 13:05:00 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL to build the statements to disable the jobs:
“select ‘exec bms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”

SQL> select ‘exec dbms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’
from dba_scheduler_jobs;

‘EXECDBMS_SCHEDULER.DISABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————–
exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.disable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’PERSTAT_SNAP’);

7 rows selected.

SQL>

3. Take the output from the previous step and execute it.

SQL> exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>

4. Execute the following SQL to build the statements to re-enable the jobs.
“select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”

SQL> select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””
dba_scheduler_jobs;

‘EXECDBMS_SCHEDULER.ENABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————-
exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);

7 rows selected.

SQL>

5. Take the output from the previous step to re-enable jobs.

SQL> exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);

PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

That completes disabling and enabling jobs with DBMS_SCHEDULER in 10g and 11g.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com