ORA-20200: Database/Instance does not exist in AWR_PDB_DATABASE_INSTANCE

当我在12.2中通过pdb登录生成awr时报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[oracle@xb admin]$ sqlplus sys/oracle@pdb12c as sysdba

sys@PDB12C> @?/rdbms/admin/awrsqrpt.sql

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

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          >>>>====这里选择的AWR_PDB

declare
*
ERROR at line 1:
ORA-20200: Database/Instance 3393322654/1 does not exist in
AWR_PDB_DATABASE_INSTANCE
ORA-06512: at line 27

但是如果选择AWR_ROOT时则正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Location of AWR Data Specified: AWR_ROOT        >>>>====

...

Current Instance
~~~~~~~~~~~~~~~~
...

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  380590835 ORA12C              1 ora12c

...

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
* 380590835      1      ORA12C       ora12c       xb.oracle.co

Using  380590835 for database Id
Using          1 for instance number

Enter value for num_days:

换成CDB登录一样正常,因为AWR Data默认就是CDB。

这是由于参数Awr_pdb_autoflush_enabled默认为FALSE,阻止了AWR相关视图的数据写入，所以接下来就说一下如何开启PDB级别的awr自动快照。

Oracle 12.2开始允许在CDB或PDB级别的AWR快照，CDB级别的快照包括CDB和PDB的统计信息。

AWR报告会列出所有可用容器库的相关信息，下图为CBD级别awr报告中展示的部分sql统计信息，包含PDB和根CDB

自动快照默认是在CDB级别开启，在PDB级别关闭，oracle建议是在PDB级别进行手工收集，或者只对单独有性能问题的PDB开启自动快照。

可以通过将参数Awr_pdb_autoflush_enabled设为TRUE，如果在CDB级别设置这个参数，则会对所有PDB都进行开启自动快照，如果只在PDB级别设置，则只会影响单个PDB。

当设置完参数以后，登录到pdb里面，通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS过程将interval设为大于0的值。

1
2
3
4
5
6
7
8
9
10
sys@ORA12C> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12C                         READ WRITE NO

--cdb级别开启
ALTER SYSTEM SET awr_pdb_autoflush_enabled= TRUE SID='*' SCOPE=BOTH;
ALTER SYSTEM SET awr_snapshot_time_offset=1000000 SID='*' SCOPE=BOTH;

参数 awr_snapshot_time_offset 默认为0. 这里设成1000000主要是为了对不同pdb在创建awr时能有不同的时间偏移量，这样就能避免cpu瞬间增高

将默认值保持为零可能会在资源较低的系统中导致CPU峰值

登录pdb，执行MODIFY_SNAPSHOT_SETTINGS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
sys@ORA12C> alter session set container=pdb12c;

Session altered.

sys@ORA12C> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL        CON_ID
---------- --------------------------------------------------------------------------- ------------------------------------------------ ---------- ----------
3393322654 +40150 00:01:00.0                                                           +00008 00:00:00.0                                                           DEFAULT             3


BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 11520, 
                                                   interval => 60,
                                                   topnsql => 34,
                                                   dbid => 3393322654);
END;
/


sys@ORA12C> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL        CON_ID
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ----------
3393322654 +00000 01:00:00.0                                                           +00008 00:00:00.0                                                                   34          3

当第一个snapshot生成以后，AWR_PDB_DATABASE_INSTANCE则会包含PDB的数据

1
2
3
4
5
6
7
8
9
10
11
sys@ORA12C> show con_name

CON_NAME
------------------------------
PDB12C

sys@ORA12C> select con_id,dbid,INSTANCE_NUMBER,INSTANCE_NAME,CDB,LAST_ASH_SAMPLE_ID from AWR_PDB_DATABASE_INSTANCE;

    CON_ID       DBID INSTANCE_NUMBER INSTANCE_NAME    CDB LAST_ASH_SAMPLE_ID
---------- ---------- --------------- ---------------- --- ------------------
         3 3393322654               1 ora12c           YES             115051

那重新来执行之前报错的awr脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
sys@ORA12C> @?/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: 

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             >>>>====选择AWR_PDB并未报错

...

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
Troubleshooting
#awr
ORA-20200: Database/Instance does not exist in AWR_PDB_DATABASE_INSTANCE
https://www.xbdba.com/2019/04/02/ora-20200-database-instance-does-not-exist-in-awr_pdb_database_instance/
作者
xbdba
发布于
2019年4月2日
许可协议