Cómo generar un reporte AWR de una BD Standby

Antes de poder generar un reporte de AWR para una BD Standby en versiones < 12.2.0.1, se debe configurar el mecanismo de AWR para Standby siguiendo los pasos en este artículo:

http://oracle-help.com/oracle-12c/oracle-12cr2/generating-awr-report-from-active-dataguard/

Una vez ya se tenga configurado el mecanismo AWS Para standby, se procede a generar un reporte de la siguiente manera:

Ingresar a sqlplus en la BD Primary:

sqlplus / as sysdba

Ejecutar script de reporte AWS para Standby:

@?/rdbms/admin/awrrpti

Especificar el tipo del reporte:

Enter value for report_type: html

Ingresar los siguientes parametros remarcados en rojo:

En la columna "DB Id" se puede obtener el ID de la base de datos standby.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1730117407     1      DB01          DB01      SRV01
* 3790024872     1      DB01          DB01      SRV02

Enter value for dbid: 1730117407
Using 1730117407 for database Id
Enter value for inst_num: 1
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.

Aqui el valor puede variar dependiendo de la fecha a cubrir en el reporte AWR:

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

DB01      DB01              1  11 Feb 2022 10:47    1
                                  2  11 Feb 2022 12:00    1
                                  3  11 Feb 2022 13:00    1
                                  4  11 Feb 2022 14:00    1
                                  5  11 Feb 2022 15:00    1
                                  6  11 Feb 2022 16:00    1
                                  7  11 Feb 2022 17:00    1


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

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



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

Enter value for report_name: standby_13_14_11022022.html

El archivo se encontrará en el directorio actual:

SQL> !ls -ltr standby*
-rw-r--r--    1 oracle   oinstall     695800 Feb 11 17:43 standby_13_14_11022022.html

How to use Oracle CX to perform SQL operations in Oracle Database

 The first step in order to use Oracle CX is to create a AWS Layer that can be use with our Lambda.

In this example I created the Layer called "cx_oracle", I configured my Lambda to use Python 3.9 and Architecture x86-64, As you can see in the following image:

 

 If you don't know how to create an AWS Layer for Python 3.9 you can check my previous article:

How to create a Lambda Layer for Oracle Database

NOTE: The following lines of code are for Python.

The first step is to import the library:

import cx_Oracle

Creating a Connection:

I am using Environment Variables in the Lambda to set the connection values, I have the following Env Variables:

• DB_USERNAME
• DB_PASSWORD
• DB_SERVER
• DB_PORT  
• ORACLE_SID

To create a new connection to the oracle database you can use the following line:

connection = cx_Oracle.connect(user=os.environ.get('DB_USERNAME'), password=os.environ.get('DB_PASSWORD'), dsn=os.environ.get('DB_SERVER')+':'+os.environ.get('DB_PORT')+'/'+os.environ.get('ORACLE_SID'), encoding="UTF-8")

Opening a cursor:

cursor_days = connection.cursor()

Submitting a SELECT statement to the Oracle Database:

cursor_days.execute("SELECT DISTINCT LOG_START_DATE ROW_DAY FROM SCHEMA01.TABLE_NAME where LOG_START_DATE < trunc(SYSDATE) ORDER BY 1")

Iterating the results:

rows_day = cursor_days.fetchall()
for ROW_DAY in rows_day:
    --Logic to handle each day--

Don't forget to close the cursor and connection:

cursor.close()
connection.close()