Oracle sysman.mgmt_jobs導致資料庫自動重啟

Oracle sysman.mgmt_jobs 導致資料庫自動重啟

問題現象；

新接手的資料庫，檢查告警日誌，發現資料庫每天 凌晨 2 點自動重啟；

Tue Jun 12 02:00:28 2018

Shutting down instance (immediate)

Stopping background process SMCO

Shutting down instance: further logons disabled

 

資料庫環境 ：

OS:Windws Server 2012

DB:Oracle 11.2.0.1.0

 

問題分析：

沒有同事設定過資料庫自動重啟的任務；

作業系統計劃任務，資料庫均沒有發現 2 點的 JOB ；

自動重啟之前，告警日誌沒有報錯；

檢視 2 點生成的所有 trace 日誌，發現每天的 trace 日誌都和 OEM 有關，部分內容如下：

*** MODULE NAME:( OEM.CacheModeWaitPool)

--------Dumping Sorted Master Trigger List --------

Trigger Owner : SYSMAN

Trigger Name : JOB_SUMM_INS_TRIGGER2

Trigger Owner : SYSMAN

Trigger Name : JOB_SUMM_INS_TRIGGER

……

檢視對應的觸發器也沒有發現異常；

 

問題原因：

最後分析，很有可能是同事在使用 DBCA 建立例項時，第四步，選擇了 ” 配置 Enterprise Manager” ，這個配置會有一個每日磁碟備份的選項，預設時間就是每天 2 點，正是因為這個原因導致資料庫每天 2 點自動重啟，因為資料庫是非歸檔模式， Oracle 每天 2 點透過 RMAN 自動 停庫做冷備；

透過 OEM 自動備份的檔案如下：

SELECT start_time, end_time, operation, status

  FROM V$RMAN_STATUS

 order by start_time desc;
---透過OEM自動的RMAN備份大多都是失敗的，因為閃回區不足了；

解決方案：

How to stop Database Autobackup Every Day At 02:00 Am which was configured during Database creation using DBCA ? ( 文件 ID 1992075.1)

-- Identify the Backup Job name

select JOB_OWNER, JOB_NAME

  from SYSMAN.MGMT_JOB

 where JOB_NAME like '%BACKUP%';

-- Delete the Job from DB Control Jobs repository

begin

  sysman.mgmt_jobs.delete_job('BACKUP_EASORCL_000001', 'SYS');

end;

刪除時會報如下錯誤：

 

--- 透過下面的文件可以解決；

How to Force Stop a Stuck Job in Grid Console ( 文件 ID 430626.1)

begin

  sysman.mgmt_job_engine.stop_all_executions_with_id('F01FFCEEC1DD42EB94B6D12622E25EB4',

                                                     TRUE);

end;

begin

  sysman.mgmt_jobs.delete_job('BACKUP_EASORCL_000001', 'SYS');

end;

select JOB_OWNER, JOB_NAME,JOB_ID

  from SYSMAN.MGMT_JOB

 where JOB_NAME like '%BACKUP%';

---空

 第二天檢視，資料庫已經不在自動重啟了；

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

官方文件具體內容如下：

How to stop Database Autobackup Every Day At 02:00 Am which was configured during Database creation using DBCA ? ( 文件 ID 1992075.1)

In this Document

 

Enterprise Manager for Oracle Database - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.

The Database Configuration assistant (DBCA) was used to create the database.

In step 4, Management options screen, you have the option to choose "Configure the database with Enterprise Manager". If you choose this , then you have the option to use Grid control or Database control.

If you choose "Use Database Control for Database Management" ,then there is an option to setup automatic database daily backups. Hence the Database might automatically shutdown daily for backup.

Is there a way to disable the automatic database shutdown for backup ?
 

We would need to delete the Backup job schedule from the DB Console.

If the DB console is down and unable to access, We can delete the Backup job from the DBconsole repository. 

-- Identify the Backup Job name

select JOB_OWNER, JOB_NAME from SYSMAN.MGMT_JOB where JOB_NAME like '%BACKUP%';

JOB_OWNER             JOB_NAME
---------------- --------------------------------------------------------
SYS                   BACKUP_QASEPOMS.BWI40G.VZBI.CAAS_000001

-- Delete the Job from DB Control Jobs repository

begin 
sysman.mgmt_jobs.delete_job('BACKUP_QASEPOMS.BWI40G.VZBI.CAAS_000001','SYS');
end; 

 

How to Force Stop a Stuck Job in Grid Console ( 文件 ID 430626.1)

In this Document

Enterprise Manager Base Platform - Version 10.2.0.1 to 11.1.0.1 [Release 10.2 to 11.1]
Information in this document applies to any platform.

Using 10.2 Grid Console.
A scheduled Job Run is stuck in the 'Running' / 'Status Pending' status for many days without any progress. This may occur if the target or the agent on that target machine is down and cannot be reached.

-  Trying to Suspend / Stop this job returns :

All executions of the job were stopped successfully. Currently running steps will not be stopped.
<Job Name is displayed here>

-  After this, the particular Job Execution remains in 'Stop Pending' status. 
   Trying to Stop this job execution returns :

The specified job, job run or execution is still active. It must finish running, or be stopped before it can be deleted. Filter on status 'Active' to see active executions.

-  Choosing the Job and clicking on 'Retry' returns :

"The specified job can not be re-submitted. It either has active executions, or it has no failed executions."

This document provides the steps to forcefully stop the executions of this job and delete it.

 

 

Note:

See the following document for assistance in troubleshooting the EM 12c job system:

Note 1546696.1  EM 12c: Troubleshooting the Enterprise Manager 12c Cloud Control Job System

 

 

 

Follow these steps to stop the job executions from the repository and delete the job :

+ Install the latest version of EMDIAG REPVFY ( Note 421053.1 ). 
   Run the following command to obtain more details on the problems with the Job system :

$ ./repvfy verify jobs
$ ./repvfy verify jobs -detail

If the stuck job is seen in the above output, please log an SR with Oracle Support to obtain more information regarding these tests.
If the job is not shown in the above output, then follow the remaining steps in this document.
It is important to exercise caution when running any manual commands against the repository database, if necessary a valid backup can be taken.

+ Connect to database as SYSMAN and run:

select job_id, job_name, job_owner from mgmt_job where job_name like '%<name of job as seen in the console>%';

Make note of the JOB_ID returned by the above query.

 +  Stop all the executions and the current runs of the job :

exec mgmt_diag.stopcurrentjobexec('<job ID as returned by above query>'); 

OR 

exec mgmt_job_engine.stop_all_executions_with_id('<job ID as returned by above query>'); 

Check the output of :

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
where job_id ='<job ID returned by the 1st query>' ; 

If the execution has been stopped successfully, the status should have a value : 18 i.e SKIPPED.

- You can also try stopping the executions forcefully using :

exec mgmt_job_engine.stop_all_executions_with_id('<job ID as returned by above query>',TRUE);

-  From EMDIAG, you can use :

exec mgmt_diag.stopjob('<job ID as returned by above query>'); 

- You can also stop the job using Emcli : 

Setup emcli as doumented in Installation and Setup of emcli 
From the command line run :

emcli stop_job -name=<specify the name of the job> 

-  If EM level auditing has been enabled, then you may face ORA-01422 from the above commands.
    To resolve these errors, refer to :

Note.401479.1  : Problem Setting Up the Auditing System for Enterprise Manager prevents job deletion

+  Check in the Grid console, that the Job Run / Execution is no longer in Running / Stop Pending status respectively.
    The Job Execution should have a 'Skipped' status.

+  Choose this job and click on the 'Delete' button in the Grid console. The Job deletion should go fine now.
    If you still face any issue, login to the repository as the SYSMAN user and run :

SQL> exec mgmt_job_engine.delete_job('<job ID as returned by above query>'); 

+  If the job still cannot be deleted :

-  Shutdown the OMS and take a valid backup of the repository database. This is mandatory as the next step will perform manual operations against the repository and it is essential to have a backup, to revert back to, incase of any problems.

-  In the repository database, login as the sysman user and execute :

SQL> UPDATE mgmt_job_exec_summary SET status = 8, 
end_time = (sysdate - 1) WHERE job_id ='<job ID as returned by above query>';

Then 1 row should be updated. If no errors do a commit, else rollback. 

-  Once this update is successful, try to delete the jobs from the Console and it should go through 
without any errors.

If using 10.2.0.5 Grid Control, there is an option to 'Force Stop' the job.

Note 838857.1  : New Features related to Jobs in 10.2.0.5 Enterprise Manager Grid Control 

Also refer to : 

Note 413005.1  : Problem: Unable To Delete Job From Grid Control ORA-20414 
Note 605071.1  : Grid Control Job Is stuck in 'Scheduled' status and unable 
to Remove / Stop the Job 
Note 401479.1  : Problem: Setting Up the Auditing System for Enterprise Manager prevents job deletion

NOTE:401479.1  - Problem: Setting Up the Auditing System for Enterprise Manager prevents job deletion
NOTE:413005.1  - Problem: Unable To Delete Job From Grid Control ORA-20414
NOTE:421053.1  - EMDIAG Troubleshooting Kits Master Index 
NOTE:605071.1  - Grid Control Job Is stuck in 'Scheduled' status and unable to Remove / Stop the Job
 
 - CANNOT STOP AND DELETE JOB FROM GRID CONTROL