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

Solution

 

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

Solution

<1span style="font-size:16px;font-family:" color:blue;"="">References

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.

-  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 '%%';

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(''); 

OR 

exec mgmt_job_engine.stop_all_executions_with_id(''); 

Check the output of :

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
where job_id ='' ; 

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('',TRUE);

-  From EMDIAG, you can use :

exec mgmt_diag.stopjob(''); 

- You can also stop the job using Emcli : 

Setup emcli as doumented in Installation and Setup of emcli  (click here) 
From the command line run :

emcli stop_job -name= 

-  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(''); 

+  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 ='';

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
HTTP://DOCS.ORACLE.COM/CD/B16240_01/DOC/EM.102/B40004/TOC.HTM  
BUG:6084150  - CANNOT STOP AND DELETE JOB FROM GRID CONTROL