重建完awr之后,不重启让awr正常收集 | ORACLE故障排查 | ORACLE技术论坛

Please consider registering
guest

Log In

Lost password?
Advanced Search:

— Forum Scope —



— Match —



— Forum Options —




Wildcard usage:
*  matches any number of characters    %  matches exactly one character

Minimum search word length is 4 characters - maximum search word length is 84 characters

Topic RSS
重建完awr之后,不重启让awr正常收集
2016 年 07 月 09 日
00:03
惜分飞
Admin
Forum Posts: 349
Member Since:
2010 年 07 月 31 日
Offline

在11.2中重建awr的朋友可能都知道,重建完成之后,需要重启数据库,不然收集报如下错误

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-02291: integrity constraint (ORA-02291: integrity constraint
(SYS.WRM$_SNAPSHOT_FK) violated – parent key not found
.) violated – parent key not found
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: at line 1

 

根据报错信息,确定表,尝试人工插入数据
SQL> insert into WRM$_SNAPSHOT(SNAP_ID,dbid,INSTANCE_NUMBER,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME) values(1,1390069961,1,(select STARTUP_TIME from v$instance where rownum=1),sysdate,sysdate);
insert into WRM$_SNAPSHOT(SNAP_ID,dbid,INSTANCE_NUMBER,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME) values(1,1390069961,1,(select STARTUP_TIME from v$instance where rownum=1),sysdate,sysdate)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.WRM$_SNAPSHOT_FK) violated – parent key
not found

 

报外键的依赖主键不存在,查找对应对象

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraintS where table_name='WRM$_SNAPSHOT';

CONSTRAINT_NAME C
—————————— -
WRM$_SNAPSHOT_PK P
SYS_C0020931 C
SYS_C0020930 C
SYS_C0020929 C
SYS_C0020928 C
SYS_C0020927 C
SYS_C0020926 C
WRM$_SNAPSHOT_FK R

8 rows selected.

 

SQL> select owner,table_name,r_owner,r_constraint_name from dba_constraintS where CONSTRAINT_NAME='WRM$_SNAPSHOT_FK';

OWNER TABLE_NAME
———————————————————— ——————————
R_OWNER R_CONSTRAINT_NAME
———————————————————— ——————————
SYS WRM$_SNAPSHOT
SYS WRM$_DATABASE_INSTANCE_PK

最后确定是WRM$_DATABASE_INSTANCE表中为插入记录,人工插入记录后测试,awr工作正常

SQL> insert into WRM$_DATABASE_INSTANCE(dbid,INSTANCE_NUMBER,STARTUP_TIME,PARALLEL,VERSION,DB_NAME,INSTANCE_NAME,HOST_NAME,LAST_ASH_SAMPLE_ID,PLATFORM_NAME)
2 values(1390069961,1,(select STARTUP_TIME from v$instance where rownum=1),'NO','11.2.0.1.0','ORCL','orcl','sql-yun','491232','Linux x86 64-bit');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt.sql

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

DB Id DB Name Inst Num Instance
———-- ———— ——-- ————
1390069961 ORCL 1 orcl

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: text

Type Specified: text

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

DB Id Inst Num DB Name Instance Host
———— ——-- ———— ———— ————
* 1390069961 1 ORCL orcl sql-yun

Using 1390069961 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

Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —--
orcl ORCL 1 08 Jul 2016 23:31 1
2 08 Jul 2016 23:32 1

 

根据官方AWR Snapshots Not Generating (Doc ID 308003.1) 建议,还是重启数据库为好,在条件允许情况下,建议尽可能安排重启数据库。

 

Q Q:107644445  

Tel:13429648788

个人Blog(惜分飞)

提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)

    

       
   

2016 年 07 月 09 日
00:04
惜分飞
Admin
Forum Posts: 349
Member Since:
2010 年 07 月 31 日
Offline

Q Q:107644445  

Tel:13429648788

个人Blog(惜分飞)

提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)

    

       
   

评论功能已关闭。