apps-infra

    Forum Stats

  • 3,873,695 Users
  • 2,266,628 Discussions
  • 7,911,601 Comments

Discussions

Generating AWR reports from PDB

Arturo Gutierrez
Arturo Gutierrez Member Posts: 340 Bronze Badge
edited Mar 14, 2019 8:16AM in Multitenant

Hi,

I'm trying to generate an AWR report from a PDB, with this error, using 12c R2 (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)

Connected to a PDB execute:

SQL>  @?/rdbms/admin/awrrpt

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

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

new   1: select 'Type Specified: ',lower(nvl('text','html')) report_type from dual

Type Specified:  text

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

-------------- -------------- -------------- -------------- --------------

2532170184     CODS2P                      1 CODS2P         ODS2P

Root DB Id      Container DB Id AWR DB Id

--------------- --------------- ---------------

    151209790      2532170184      2532170184

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

------------ ---------- ---------    ----------   ------

  3522870585     1      ODS2P        ODS2P        procasdwhx01

Using 2532170184 for database Id

Using          1 for instance number

declare

*

ERROR at line 1:

ORA-20200: Database/Instance 2532170184/1 does not exist in

AWR_PDB_DATABASE_INSTANCE

ORA-06512: at line 27

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

My user has this privs and roles.

SQL> show user

USER is "DX0001"

SQL> select * from session_roleS;

ROLE

--------------------------------------------------------------------------------

SELECT_CATALOG_ROLE

HS_ADMIN_ROLE

HS_ADMIN_SELECT_ROLE

HS_ADMIN_EXECUTE_ROLE

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE TABLE

CREATE PROCEDURE

ADVISOR

Any know hopw fix this issue?

I need some privilege?

Thanks

Arturo

Best Answer

  • Praveen Ch-Oracle
    Praveen Ch-Oracle Member Posts: 12 Employee
    edited Mar 7, 2019 3:44AM Answer ✓

    Hi Arturo,

    Login to the CDB database as SYS and set modifying following parameters:

    $ sqlplus / as sysdba

    Check current values for awr_pdb_autoflush_enabled and awr_snapshot_time_offset parameters.

    --- 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;

    Try generating AWR after the above.

    Thanks

    Praveen

Answers

  • Praveen Ch-Oracle
    Praveen Ch-Oracle Member Posts: 12 Employee
    edited Mar 7, 2019 3:44AM Answer ✓

    Hi Arturo,

    Login to the CDB database as SYS and set modifying following parameters:

    $ sqlplus / as sysdba

    Check current values for awr_pdb_autoflush_enabled and awr_snapshot_time_offset parameters.

    --- 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;

    Try generating AWR after the above.

    Thanks

    Praveen

  • Arturo Gutierrez
    Arturo Gutierrez Member Posts: 340 Bronze Badge
    edited Mar 14, 2019 8:07AM

    Hi Praveen,

    I've try you sugestion, but occur the same error:

    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    SQL> show parameters awr

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    awr_pdb_autoflush_enabled            boolean     FALSE

    awr_snapshot_time_offset             integer     0

    SQL> ALTER SYSTEM SET awr_pdb_autoflush_enabled= TRUE SID='*' SCOPE=BOTH;

    System altered.

    SQL> ALTER SYSTEM SET awr_snapshot_time_offset=1000000 SID='*' SCOPE=BOTH;

    System altered.

    SQL> show pdbs

        CON_ID CON_NAME                       OPEN MODE  RESTRICTED

    ---------- ------------------------------ ---------- ----------

             2 PDB$SEED                       READ ONLY  NO

             3 PDB1                           READ WRITE NO

    Connect to PDB.

    SQL> show con_name

    CON_NAME

    ------------------------------

    PDB1

    SQL> get report

      1* @$ORACLE_HOME/rdbms/admin/awrrpt.sql

    I try execute the report.

    SQL> sta report

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

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

    new   1: select 'Type Specified: ',lower(nvl('text','html')) report_type from dual

    Type Specified:  text

    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

    -------------- -------------- -------------- -------------- --------------

    2414402043     CDB1                        1 cdb1           PDB1

    Root DB Id      Container DB Id AWR DB Id

    --------------- --------------- ---------------

        976607121      2414402043      2414402043

    Instances in this Workload Repository schema

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Using 2414402043 for database Id

    Using          1 for instance number

    declare

    *

    ERROR at line 1:

    ORA-20200: Database/Instance 2414402043/1 does not exist in

    AWR_PDB_DATABASE_INSTANCE

    ORA-06512: at line 27

    Any idea?

    Thanks

    Arturo

  • Arturo Gutierrez
    Arturo Gutierrez Member Posts: 340 Bronze Badge
    edited Mar 14, 2019 8:16AM

    Hi,

    Yes Now work fine, after take a snapshot at PDB level.

    Thanks

    Arturo

apps-infra