修复oracle中的awr出现多个实例记录和不能自动生成awr快照问题

原来rac数据库所在服务器主机名：lxsu1、lxsu2；现在数据库所在服务器主机名xmsu1 、xmsu2

1、ORACLE AWR报告生成过程出现多个实例记录修复

获取awr报告报错

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name	     Inst Num Instance
----------- ------------ -------- ------------
 2034871735 XMSU		    1       xmsu1 


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: 

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name	   Instance	Host
------------ -------- ------------ ------------ ------------
  2034871735	    2  XMSU	   xmsu2	lxsu2        -- 数据库迁移前 主机名
  2034871735	    1  XMSU	   xmsu1	lxsu1        -- 数据库迁移前 主机名
  2034871735	    2  XMSU	   xmsu2	xmsu2
* 2034871735	    1  XMSU	   xmsu1	xmsu1

Using 2034871735 for database Id
Using	       1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots


Specify the Begin and End Snapshot Ids                   --  无snap ip 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 
Begin Snapshot Id specified:

查看host存放记录

SQL> SELECT HOST_NAME, MAX(STARTUP_TIME) FROM dba_hist_database_instance GROUP BY HOST_NAME;

HOST_NAME
----------------------------------------------------------------
MAX(STARTUP_TIME)
---------------------------------------------------------------------------
xmsu2
28-APR-24 09.45.42.000 AM
lxsu2                            -- 数据库原来前主机名
29-SEP-23 05.34.35.000 PM
lxsu1                            -- 数据库原来前主机名
29-SEP-23 05.34.59.000 PM
xmsu1
07-APR-24 09.25.29.000 AM

根据时间删除过期记录

SQL> DELETE FROM dba_hist_database_instance WHERE STARTUP_TIME <=TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

SQL> COMMIT;

2、调整awr产生快照的频率及保留策略

--  以分钟为单位：每60分钟做次快照，保留7天
SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 7*24*60 );

查看awr策略

SQL> select * from dba_hist_wr_control; 

      DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
2034871735
+00000 01:00:00.0         --  如果为+00000 00:00:00.0，不会自动生成awr快照
+00007 00:00:00.0         --  如果为+00000 00:00:00.0，不会自动生成awr快照
DEFAULT

3、解决awr不自动生成

适用于：已经配置awr产生快照的频率及保留策略还是不能自动生成awr快照

查看mmon进程

oracle@xmusu1:~:SID=xmusu1:>ps -ef |grep mmon
grid      14404      1  0 Mar25 ?     00:04:03 asm_mmon_+ASM1
oracle    21398      1  0 Apr07 ?     00:31:27 ora_mmon_xmsu1  -- 当前时间20240429，进程时间滞后 (xmsu1实例名)
oracle   434239 176275  0 16:48 pts/0 00:00:00 grep mmon

重启mmon

-- 两个语句最好一起复制
sql>
alter system enable restricted session;
alter system disable restricted session;

4、附加

手动生成awr快照

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT() ;

查看有多少awr快照

-- SNAP_ID会出现重复因为rac有多个节点
select  *  from   DBA_HIST_SNAPSHOT  order by  SNAP_ID ;

---

引用：

1、ORACLE AWR报告生成过程出现多个实例记录分析

2、ORACLE之awr无snap_id

3、oracle快照没有自动生成的原因及解决办法_oracle awr快照不生成-CSDN博客