CODEWITHSUNDEEP
oracle-databaseoracle11gdbms-scheduler
Tomasz
Tomasz

Reputation: 160

Oracle 11g and scheduler

How to start a database with the scheduler jobs disabled?

I have an internal oracle error that causes the database to stop. This error is on the table the job has access to. When I start the database (startup with sqlplus), the database is immediately stopped because the job is started.

Another option is, can I somehow remove the corrupted table or the job when I have the database shut down?

Upvotes: 1

Views: 178

Answers (1)

Joe
Joe

Reputation: 91

Start database in mount mode and set job queue processes to zero

sql>startup mount
sql>show parameter job_queue_processes
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------
job_queue_processes                                          integer     4000
sql>alter system set job_queue_processes=0;
sql> alter database open;

Fix the table causing instance crash and revert back job queue processes to original value and bounce the instance

SQL>ALTER SYSTEM SET job_queue_processes=4000;
SQL>startup force;

Relevant lines in alert.log file

2020-09-24T10:10:17.295728-05:00
Completed: ALTER DATABASE   MOUNT
2020-09-24T10:11:07.875673-05:00
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH; 
2020-09-24T10:13:30.255114-05:00
alter database open
---------
2020-09-24T10:33:10.232345-05:00
ALTER SYSTEM SET job_queue_processes=4000 SCOPE=BOTH;

Upvotes: 3

Related Questions