Shulou(Shulou.com)06/01 Report--

Content introduction

This article describes in detail that a large amount of CPU was consumed at the customer site due to resmgr:cpu quantum waiting events. After confirming the cause and shutting down the Resource Manager explorer feature that will cause CPU to surge, it subsequently caused a large number of automatic timing JOB in the background alert log not to be executed and reported an error:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_OS_OPT_SY_255"

ORA-29373: resource manager is not on

Guest operating system: RHEL6

Database version: 11.2.0.3.11

Below we will describe and explain a series of processes such as Resource Manager Explorer and subsequent resolution processing troubleshooting.

Concept popularization

Resource Manager Explorer is a feature that oracle launched in 10g and perfected in 11g. The resource manager controls the allocation of resources between sessions by controlling execution scheduling tasks within the database. By controlling the session to be run and the length of time for the session to run, Database Explorer can ensure that the resource utilization and allocation is the same as our planned configuration, and the resource is utilized effectively. Of course, the main control resources of Resource Manager include the following:

CPU usage of Oracle session

Parallelism

SQL statement operation execution time

Session idle time

Active sessions (session)

UNDO management

Of course, in a real production environment, Resource Manager has not been widely used, but starting from 11g (11.1.0.6 to 11.1.0.7 and 11gR2), oracle enables Resource Manager plan by default, and the oracle backend log is shown below:

Setting Resource Manager plan SCHEDULER [0x51B5]: DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Thu Feb 05 22:00:03 2009

Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Thu Feb 05 22:00:39 2009

End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

The following shows the different strategies adopted by oracle for Resource Manager in 10g and 11g:

Subject

10G

11G

Maintenance Window

2 windows, WEEK and WEEKEND

Each day has its own window

Resource manager

Not enabled per default

Default resource plan specified

Detailed explanation of fault

After the customer's new system is launched, it is found that there are a large number of waiting events in the database: resmgr:cpu quantum, which is a common waiting event in 11g, which consumes a lot of CPU system performance. After communicating with the customer, it is decided to disable this feature. The following steps are taken to disable the feature:

ALTER SYSTEM SET "_ resource_manager_always_on" = FALSE SCOPE=SPFILE SID='*'

# 1 Magna change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:

Execute dbms_scheduler.set_attribute ('WEEKNIGHT_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('WEEKEND_WINDOW','RESOURCE_PLAN','')

# 2,Since in 11g there are more Maintenance Windows, we should add them too:

Execute dbms_scheduler.set_attribute ('SATURDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('SUNDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('MONDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('TUESDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('WEDNESDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('THURSDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('FRIDAY_WINDOW','RESOURCE_PLAN','')

# 3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

Execute dbms_scheduler.set_attribute ('', 'RESOURCE_PLAN','')

The above effect is really obvious after turning off Resource Manager, the system runs stably, and the CPU is at a normal level. But after running for a period of time, the backend begins to report an error:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_SQ_SQL_SW_186"

ORA-29373: resource manager is not on

In terms of error reporting, it seems to have something to do with Resource Manager. Judging from the name of JOB, this JOB is "SQL Tuning Advisor Job", which is used to diagnose and monitor high-load SQL, and to provide SQL optimization advice for ADDM. For the first time, we shut down the error JOB, that is, SQL Tuning Advisor. The closing process is as follows:

Exec DBMS_AUTO_TASK_ADMIN.DISABLE ('SQL TUNING ADVISOR',NULL, NULL)

After the shutdown, a corresponding new JOB begins to report an error:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_OS_OPT_SY_255"

ORA-29373: resource manager is not on

Let's check the latest JOB execution in the background:

SQL > select client_name,window_name,job_name,job_status from dba_autotask_job_history

CLIENT_NAME WINDOW_NAME JOB_NAME JOB_STATUS

-

Auto optimizer stats collection WEDNESDAY_WINDOW ORA$AT_OS_OPT_SY_102 FAILED

Auto optimizer stats collection FRIDAY_WINDOW ORA$AT_OS_OPT_SY_105 FAILED

Auto optimizer stats collection TUESDAY_WINDOW ORA$AT_OS_OPT_SY_99 FAILED

Auto optimizer stats collection MONDAY_WINDOW ORA$AT_OS_OPT_SY_96 FAILED

In the above script output, we found a large number of JOB errors in the background, and the above JOB errors are basically caused by Resource Manager shutdown in the alarm log. But we know that the opening and closing of Resource Manager should not affect the normal scheduling of JOB, and under the same database version, we did not find a problem on AIX. In the course of checking, we found that although we have closed Resource Manager, the related DBRM process still exists. The DBRM process of ORACLE is the management process of Resource Manager. In some cases, There is a problem with shutting down Resource Manager only to find that the process still exists. To some extent, this gives us some doubt that resource may not be completely closed, and from the call stack trace in the detailed trace file, the current process does not have a function call to resource manager, but has been post message to another process.

Therefore, we suspect that the error is most likely due to the failure of DBRM to shut down properly.

# ps-ef | grep dbrm

Oracle 34920 1 0 Apr18? 00:00:59 ora_dbrm_nfdb1

After tracking and analyzing the problem in detail, we submitted SR,ORACLE feedback to ORACLE to give us the second implicit parameter: _ resource_manager_always_on set to FALSE

After we try to set this parameter and restart the database to take effect, the DBRM process disappears:

# ps-ef | grep dbrm

Root 4650 3647 0 21:58 pts/2 00:00:00 grep dbrm

And the related JOB executes normally. In order to confirm the problem, we carried out three experiments respectively, which are:

1. Set the implied parameter "_ resource_manager_always_on" to close the resource manager windows call plan

two。 Delete the implied parameter and only set the resource manager windows call plan

3. Add 2 implicit parameters to close the resource manager windows call plan

Close the database, adjust the time, set the _ resource_manager_always_on implicit parameters, close the windows plan

Shut down the database:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Adjust time:

[root@rhel6] # date-s 2014-05-14

[root@rhel6 ~] # date-s 21:57:00

[root@rhel6] # clock-w

[root@rhel6 ~] # date

Wed May 14 21:57:35 CST 2014

Add implied parameters and launch to open:

SQL > ALTER SYSTEM SET "_ resource_manager_always_on" = FALSE SCOPE=SPFILE

SQL > startup force (test environment, direct force startup, do not do this in production environment)

Set up the resource manager plan:

Execute dbms_scheduler.set_attribute ('SATURDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('SUNDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('MONDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('TUESDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('WEDNESDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('THURSDAY_WINDOW','RESOURCE_PLAN','')

Execute dbms_scheduler.set_attribute ('FRIDAY_WINDOW','RESOURCE_PLAN','')

When we look at the 22:00 alert message, we do start to report errors:

Wed May 14 22:00:03 2014

Errors in file / oracle/ora11g/base/diag/rdbms/ora11g/ora11g/trace/ora11g_j003_4452.trc:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_OS_OPT_SY_27"

ORA-29373: resource manager is not on

Wed May 14 22:00:03 2014

Errors in file / oracle/ora11g/base/diag/rdbms/ora11g/ora11g/trace/ora11g_j004_4454.trc:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_SA_SPC_SY_28"

ORA-29373: resource manager is not on

Wed May 14 22:00:03 2014

Errors in file / oracle/ora11g/base/diag/rdbms/ora11g/ora11g/trace/ora11g_j005_4456.trc:

ORA-12012: error on auto execute of job "SYS". "ORA$AT_SQ_SQL_SW_29"

ORA-29373: resource manager is not on

Wed May 14 22:00:04 2014

XDB installed.

XDB initialized.

Check that the DBRM process already exists:

[root@rhel6 ~] # ps-ef | grep dbrm

Ora11g 4346 1 0 21:57? 00:00:00 ora_dbrm_ora11g

Check the background JOB execution record view:

SQL > select CLIENT_NAME,WINDOW_NAME,JOB_NAME,JOB_STATUS,JOB_START_TIME from DBA_AUTOTASK_JOB_HISTORY where CLIENT_NAME='auto optimizer stats collection' order by JOB_START_TIME desc

CLIENT_NAME WINDOW_NAME JOB_NAME JOB_STATUS JOB_START_TIME

Auto optimizer stats collection WEDNESDAY_WINDOW ORA$AT_OS_OPT_SY_27 FAILED 14-MAY-14 10.00.03.233570 PM PRC

The simulation process is consistent with the effect of the current environment.

Next, let's do the second simulation:

Close the database, adjust the time, remove the implicit parameters, and close the windows plan.

Shut down the database:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Adjust time:

[root@rhel6] # date-s 2014-05-14

[root@rhel6 ~] # date-s 21:57:00

[root@rhel6] # clock-w

[root@rhel6 ~] # date

Wed May 14 21:57:35 CST 2014

Remove the implied parameters and start to open:

Create pfile from spfile is used to remove implicit parameters.

Delete spfile, edit pfile files, delete implicit parameters, start the database with pfile

Set resource manager plan: (since it has been set before, there is no need to set it again)

We observed the alert message at 22:00 and found that there was no error.

Tue May 13 22:00:03 2014

Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Tue May 13 22:00:05 2014

XDB installed.

XDB initialized.

Check the DBRM process:

[root@rhel6 ~] # ps-ef | grep dbrm

Ora11g 3844 1 0 21:55? 00:00:00 ora_dbrm_ora11g

Note: at this time, resource manager only closes the resource manager plan plan, not really shuts down resource manager, so the process still exists.

Check the background JOB execution view information:

SQL > select CLIENT_NAME,WINDOW_NAME,JOB_NAME,JOB_STATUS,JOB_START_TIME from DBA_AUTOTASK_JOB_HISTORY where CLIENT_NAME='auto optimizer stats collection' order by JOB_START_TIME desc

CLIENT_NAME WINDOW_NAME JOB_NAME JOB_STATUS JOB_START_TIME

- -

Auto optimizer stats collection TUESDAY_WINDOW ORA$AT_OS_OPT_SY_24 SUCCEEDED 13-MAY-14 10.00.02.102741 PM PRC

It indicates that when the implied parameters are removed, the JOB can be executed normally, and no error is reported at the backend.

Simulate the existence of both implied parameters and resource manager plan

Close the database, adjust the time, add implicit parameters, close the windows plan

Shut down the database:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Adjust time:

[root@rhel6] # date-s 2014-05-15

[root@rhel6 ~] # date-s 21:57:00

[root@rhel6] # clock-w

[root@rhel6 ~] # date

Thu May 15 21:57:35 CST 2014

Add implied parameters and launch to open:

SQL > alter system set "_ resource_manager_always_off" = true scope=spfile

SQL > ALTER SYSTEM SET "_ resource_manager_always_on" = FALSE SCOPE=SPFILE

SQL > startup force (test environment, direct force startup, do not do this in production environment)

Set resource manager plan: (since it has been set before, there is no need to set it again)

We observed the alert message at 22:00 and found that there was no error.

Thu May 15 22:00:03 2014

Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Thu May 15 22:00:05 2014

XDB installed.

XDB initialized.

End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Check the DBRM process:

[root@rhel6 ~] # ps-ef | grep dbrm

Root 4650 3647 0 21:58 pts/2 00:00:00 grep dbrm

Description: the DBRM process disappears at this time

Check the background JOB execution view information:

SQL > select CLIENT_NAME,WINDOW_NAME,JOB_NAME,JOB_STATUS,JOB_START_TIME from DBA_AUTOTASK_JOB_HISTORY where CLIENT_NAME='auto optimizer stats collection' order by JOB_START_TIME desc

CLIENT_NAME WINDOW_NAME JOB_NAME JOB_STATUS JOB_START_TIME

Auto optimizer stats collection THURSDAY_WINDOW ORA$AT_OS_OPT_SY_30 SUCCEEDED 15-MAY-14 10.00.02.115232 PM PRC

It indicates that when both implied parameters are added and resource manager is completely blocked, JOB can be executed normally, and no error is reported at the backend.

Technical conclusion

The above test and analysis results show that the reason for the failure of JOB execution is that the DBRM process is still active, while the DBRM process manages the Resource Manager when removing "_ resource_manager_always_off" = true and "_ resource_manager_always_on" = FALSE.

Or add all the two parameters to avoid the error, and the automatic collection of statistics can also be performed automatically.

After analyzing and confirming the problem, we submitted the relevant SR to ORACLE. Finally, ORACLE confirmed that the relevant BUG,BUG number was: Bug 18748456: AUTO TASK JOBS FAILED WITH ORA-29373 ERROR.

Original blog address: http://blog.itpub.net/23732248/

Original author: Ying Feng (frank-ying)

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.