Killing the Oracle DBMS JOB

Summary

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken
3. Kill the Oracle Session

4. Kill the O/S Process
5. Check if the Job is Still Running

6. Determine the Current Number of Job Queue Processes
7. Alter the Job Queue to Zero
8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken
10. Alter the Job Queue to Original Value
11. Validate that DBMS_JOB Is Running

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

What Jobs are Actually Running

Returns sid of the jobs running

running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs
returned values
spid  is the operating system process identifier and sid, serial# which is the session id and session  serial number.

session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down

In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the session_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command  to break the  job. It won’t stop the running job, just marks it broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

NOTE :  you will need to mark the jobs as unbroken when the database comes back up, more on that later.

3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.

5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue

Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken

You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value

Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Hope this Help! Rupam

Restore oracle database on another server

Steps (1 to 10) to restore database on another server

1. create directory structure

2. add database to /etc/oratab or /var/opt/oracl/oratab

3. copy init.ora file from source

4. edit init.ora – replace controlfile name with diskgroup name such as ORADATA

5. startup nomount

6. start restore using restore shell script

  (NOTE : This will restore archive log files too)

7. monitor alert.log and restore log

8. after restore and recover completes

9. edit inot.ora file - replace controlfile parameter with real name

10. startup database; open for business

 Hope this helps! Rupam

SQL Profile – create manually

Symptom

Sql execution is too long

Cause

After automatic statistics collection on table, the execution plan changed. It is no more picking the index and doing full table scan.

solution

Step 1. Find the SQL ID that needs a profile,

               in this example: 50ux45v27k6ab

Step 2. Find the hint that introduces a good plan

               In this example: INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)

Step 3. Run following anonymous PLQSL block

DECLARE

cl_sql_text CLOB; 

BEGIN

SELECT sql_text  

INTO cl_sql_text  

FROM gv$sqlarea where sql_id = '50ux45v27k6ab' and rownum = 1; 

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text,  

profile => sqlprof_attr(‘INDEX(TRAIN_SHEET_OSPOINT PK_TRAIN_SHEET_OSPOINT)'),  

name => 'USE_PK_FOR_UPDATE',  

category => 'DEFAULT', 

force_match => TRUE); 

end; 

/

Hope this helps! Rupam

Flashback Cheatsheet

Enabling/disabling Logging for Flashback Database

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE FLASHBACK OFF;

Estimating Disk Space Requirements for Flashback Database Logs

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

By default, flashback logs are generated for all permanent tablespaces.

SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Backup database

RMAN> backup database plus archivelog;

Backup flash recovery area

RMAN> backup recovery area;

Flashback usage

sql> select * from v$recovery_file_dest;

sql> select * from v$flash_recovery_area_usage;

Create Restore Point

sql> create restore point rp01;

sql> select name from v$restore_point;

sql> drop restore point rp01;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

sql> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

-- Determining the Current Window for Flashback Database

sql> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Restore to Restore Point using RMAN

RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;

RMAN> LIST RESTORE POINT ALL;

RMAN> LIST RESTORE POINT  RP03;

RMAN> flashback database  to  restore point rp01;

RMAN> alter database open resetlogs;

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/20/00','MM/DD/YY')";

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; # VERIFY CHANGES

Restore to Restore Point using SQL

sql> shutdown immediate

sql> startup mount

sql> flashback database  to  restore point rp01;

sql> alter database open resetlogs;

Options After Flashback Database to the Wrong Time

RMAN> FLASHBACK DATABASE TO SCN 42963;  #earlier than current SCN

RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN

RMAN>  RECOVER DATABASE;

Performing Flashback Database to Undo an OPEN RESETLOGS

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

sql> select resetlogs_change# from v$database;

sql> select oldest_flashback_scn from v$flashback_database_log;

Flashback Database To The Right of Open Resetlogs

sql> select oldest_flashback_scn from v$flashback_database_log;

SQL> select prior_incarnation# from v$database_incarnation where status = 'CURRENT';

RMAN> LIST INCARNATION OF DATABASE trgt;

RMAN> RESET DATABASE TO INCARNATION 1;

RMAN> FLASHBACK DATABASE TO SCN 1500;

Flashback Table

   -- Prerequisites for Using Flashback Table

sql> ALTER TABLE table ENABLE ROW MOVEMENT;

sql> SELECT, INSERT, DELETE, and ALTER privileges on the table.

 Performing Flashback Table

sql> FLASHBACK TABLE EMP TO SCN 123456;

sql> FLASHBACK TABLE EMP TO TIMESTAMP

      TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Viewing, Querying and flashback  Objects in the Recycle Bin

SQL> show recyclebin;

SQL> SELECT object_name as recycle_name, original_name, type

     FROM recyclebin;

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

SQL> FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

OR

sql> FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP

     RENAME TO hr.int2_admin_emp;

Hope this helps! Regards Rupam

Rman: Ora-01008 When Connecting To Target in 11.2.0.2 [ID 1280447.1]

It's a bug with a patch. Workaround is to :

SQL> alter system flush shared_pool;

Hope This Helps! Rupam