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

Today, when I looked at a SQL that the application responded that it could not be executed, I found that the AWR report in the database was not automatically collected. The most recent one was still on September 26th. Now it is interesting to see that the current time is October 20. How could this happen? the default AWR is collected once an hour and retained for 8 days (11.2.0.4 for this database version).

Let's check the setting of AWR:

SQL > show parameter statistics_levelNAME TYPE VALUE-- statistics_level string TYPICALSQL > select * from dba_hist_wr_control DBID SNAP_INTERVAL RETENTION TOPNSQL--2574787718 + 00000 01purl 0000purl 00.0 + 00008 0000VOG 00.0 DEFAULT

Statistics_level is set to TYPICAL: collects all data automatically managed by the database.

The collection interval snap_interval is 1 hour and the retention time retention is 8 days.

These settings are normal.

Also think of AWR collection and storage in the SYSAUX table space, is the table space full?

TABLESPACE_NAME SIZE_G FREE_G USED_PCT---SYSAUX 2. 78 0.51 81.50%

You can see that although the SYSAUX tablespace is small, there is still space, and it doesn't seem to be a problem with the SYSAUX tablespace.

If you take a look at the recent alert log in the background, it is also normal to switch logs without any error information.

Since there is no error, it is always possible for me to collect AWR manually, so execute the following statement

SQL > exec dbms_workload_repository.modify_snapshot_settings (retention= > 21600); BEGIN dbms_workload_repository.modify_snapshot_settings (retention= > 21600); END;*ERROR at line 1:ORA-13516: AWR Operation failed: only a subset of SQL can be issuedORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222ORA-06512: at line 1

As you can see from the above, if you collect AWR errors manually, you can deal with them according to the error messages.

Let's take a look at what this error means.

SQL >! oerr ora 1351613516, 00000, "AWR Operation failed:% s" / / * Cause: The operation failed because AWR is not available. The// possible causes are: AWR schema not yet created; AWR// not enabled; AWR schema not initialized; or database// not open or is running in READONLY or STANDBY mode.// * Action: check the above conditions and retry the operation.

Later, I started searching on MOS and Baidu, and MOS found a document that was very similar to the error report here, but the corresponding versions 10.1 to 10.2 were inconsistent with my current version. Later, I saw a blog introduction that AWR was collected by the background process MMON, so I looked at the MMON process on the server.

SQL >! ps-ef | grep mmon oracle 15484 2406 1 13:24:07 pts/5 0:00 grep mmon

I can't believe it didn't!

It seems that the MMON process was dropped by DOWN, so when did DOWN fall? I thought that AWR would stay on September 26th, so I went to look at that day's alert log and found something important:

I saw that the shutdown operation on the database was done on the night of the 26th, but soon the shutdown was cancelled again. During this process, the MMON and MMNL processes were down, and the database has not been restarted since then, so the MMON process has not been started, so today's problem was discovered.

Now that we have found the reason, how to solve it? How can the MMON process be started?

I checked a lot on the Internet, but I didn't find a relevant solution. The ultimate solution is to restart the database.

If there is a big god who knows how to start the MMON process without restarting the instance, I would like to give you some advice.

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.