CNDBA社区
Oracle 19c PDB AWR 报告ORA-20200 Instance does not exist in AWR_PDB_DATABASE_INSTANCE 错误解决方法
作者：
dave
在Oracle 19c 中，对PDB 生成AWR 报告报了如下错误：
ORA-20200: Database/Instance does not exist in AWR_PDB_DATABASE_INSTANCE
完整记录如下：
[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 LEVEL
解决方法如下：
使用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瞬间增高。
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
[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
参考：
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)
版权声明：本文为博主原创文章，未经博主允许不得转载。
