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%';
---空
第二天查看，数据库已经不在自动重启了；
官方文档具体内容如下：
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
APPLIES TO:
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
APPLIES TO:
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.
GOAL
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
SOLUTION
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 (click here)
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
HTTP://DOCS.ORACLE.COM/CD/B16240_01/DOC/EM.102/B40004/TOC.HTM
BUG:6084150 - CANNOT STOP AND DELETE JOB FROM GRID CONTROL
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2156188/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2156188/
864
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
