3 Replies Latest reply on Mar 14, 2019 12:16 PM by Arturo Gutierrez

    Generating AWR reports from PDB

    Arturo Gutierrez

      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

        • 1. Re: Generating AWR reports from PDB
          Praveen Ch-Oracle

          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

          • 2. Re: Generating AWR reports from PDB
            Arturo Gutierrez

            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

            • 3. Re: Generating AWR reports from PDB
              Arturo Gutierrez

              Hi,

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

               

              Thanks

              Arturo