签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c PDB AWR 报告ORA-20200 Instance does not exist in AWR_PDB_DATABASE_INSTANCE 错误解决方法

2021-01-24 13:48 359 0 原创 Oracle 19c
作者: Dave

在Oracle 19c 中,对PDB 生成AWR 报告报了如下错误:https://www.cndba.cn/dave/article/4363

ORA-20200: Database/Instance does not exist in AWR_PDB_DATABASE_INSTANCEhttps://www.cndba.cn/dave/article/4363

完整记录如下:https://www.cndba.cn/dave/article/4363

[dave@www.cndba.cn tmp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 24 12:50:58 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DAVE                           READ WRITE NO
         4 USTC                           READ WRITE NO
SQL> alter session set container=DAVE
  2  ;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DAVE
SQL> @?/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual

Type Specified:                                  html



Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Location of AWR Data Specified:
AWR_PDB





Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 3516530670     CNDBA                       1 cndba          DAVE



Root DB Id      Container DB Id AWR DB Id
--------------- --------------- ---------------
    299079762      3516530670      3516530670






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

Using 3516530670 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 3516530670/1 does not exist in
AWR_PDB_DATABASE_INSTANCE
ORA-06512: at line 27


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[dave@www.cndba.cn tmp]$

上MOS 上查了一下,说是如下2个bug 导致的:

Bug 25941188 - ORA-20200 WHEN ADDM REPORT GENERATED FROM A PDB DATABASE USING AWR_PDB OPTION <==closed as duplicate to following internal bug that is still being worked on:
Bug 27747951 : CDB_MA: ADDMRPT.SQL EXECUTION IN PDB LEVELhttps://www.cndba.cn/dave/article/4363

解决方法如下:

使用SYS 连接CDB, 执行如下命令:

$ sqlplus / as sysdba

--- Open all PLUGGABLE databases, if necessary
ALTER PLUGGABLE DATABASE ALL OPEN;
--- Set the following parameters

ALTER SYSTEM SET awr_pdb_autoflush_enabled= TRUE SID='*' SCOPE=BOTH;
ALTER SYSTEM SET awr_snapshot_time_offset=1000000 SID='*' SCOPE=BOTH;

按MOS 的建议,实际上是对所有PDB都开启了自动快照,如果只在PDB级别设置,则只会影响单个PDB。 另外参数 awr_snapshot_time_offset 默认为0. 这里设成1000000主要是为了对不同pdb在创建awr时能有不同的时间偏移量,这样就能避免cpu瞬间增高。https://www.cndba.cn/dave/article/4363https://www.cndba.cn/dave/article/4363

MOS 的解释如下:

Parameter awr_snapshot_time_offset is set to zero by default. Setting it to 1000000 helps to create AWR snapshots with different offset based on database names and avoids CPU spike in the system.
This parameter is not the direct cause but keeping the default value to zero may cause CPU spikes in a system with low resources.

然后就可以正常生成了:

[dave@www.cndba.cn tmp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 24 13:14:07 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DAVE                           READ WRITE NO
         4 USTC                           READ WRITE NO
SQL> alter session set container=DAVE;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DAVE
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

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual

Type Specified:                                  html



Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Location of AWR Data Specified:
AWR_PDB





Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 3516530670     CNDBA                       1 cndba          DAVE



Root DB Id      Container DB Id AWR DB Id
--------------- --------------- ---------------
    299079762      3516530670      3516530670






Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3516530670     1      CNDBA        cndba        dave

Using 3516530670 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:

Listing all Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

cndba        CNDBA                1  24 Jan 2021 13:14    1
                                  2  24 Jan 2021 13:14    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1_2.html
这里要注意一点, PDB的快照和CDB 的快照是分开的。 而快照默认的生成间隔是1小时,所以修改参数之后,如果没有生成PDB快照,就需要手工来生成快照,否则还是会报我们开始一样的错误。
[dave@www.cndba.cn tmp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 24 12:50:58 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

……

SQL> show con_name

NAME_COL_PLUS_PDB_CONTAINER
------------------------------------------------------------------------------
DAVE

SQL> exec dbms_workload_repository.create_snapshot();

SQL> select snap_id,dbid,con_id from awr_pdb_snapshot;

   SNAP_ID       DBID     CON_ID
---------- ---------- ----------
         1 3516530670          3
         2 3516530670          3

参考:https://www.cndba.cn/dave/article/4363https://www.cndba.cn/dave/article/4363

https://www.cndba.cn/dave/article/4363

ORA-20200 Error When Generating AWR or ADDM Report as a PDB DBA User From a 12.2.0.1 and higher CDB Database (Doc ID 2267849.1)https://www.cndba.cn/dave/article/4363

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164773次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群