IMPLEMENTANDO PHYSICAL STANDBY USANDO RMAN DUPLICATE FROM ACTIVE DATABASE

A continuación detallaré el paso a paso para la implementación de un Physical Standby Database, usando RMAN DUPLICATE DATABASE FROM ACTIVE DATABASE. Este proceso  clonará a través de la red, un ambiente productivo y en base a este generará un physical standby, sin necesidad de tener un backup RMAN previamente generado.

En el presente ejemplo, el origen se encuentra en filesystem y el destino será un Physical Standby sobreASM.

Información de la BD Primaria(origen):
Host: SERVER-PROD
DB_NAME = IRIS
DB_UNIQUE_NAME=IRIS

Primary Database
OS versión-> redhat 6.7
Servidor -> SERVER-PROD
Instancia -> IRIS
ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system: Filesystem

Información de la BD Standby(destino):
Host: SERVER-CONT
DB_NAME = IRIS
DB_UNIQUE_NAME = IRISSBY

Standby Database
OS versión-> redhat 7.2
Servidor-> server-conting
Instancia->IRISSBY
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
Version 12.1.0.2.0
Storage system : ASM (+DG_DATA12C_02)

PRIMARY: Todos los siguientes pasos se ejecutarán en el entorno primario

1)Verificar la BD se encuentre en modo archivelog
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivedata/archivelogs/
Oldest online log sequence 5231
Next log sequence to archive 5234
Current log sequence

2) verificar la BD origen se encuentre en modo force_logging

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

En caso de ser requerido activar de la siguiente manera :

SQL> alter database force logging;
Database altered.

3.-Crear standby redo logfiles

alter database add standby logfile '/oradata1/IRIS/onlinelog/slog1.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog2.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog3.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog4.rdo' size 500M;
alter database add standby logfile '/oradata1/IRIS/onlinelog/slog5.rdo' size 500M;

SQL> select group#,thread#,bytes from v$standby_log;

GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000

SQL> select group#, thread#, bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
———-      ———-      —————
1                  1                   500
2                  1                  500
3                  1                  500
4                  1                  500

SQL> select group#,thread#,bytes from v$standby_log;

GROUP# THREAD# BYTES
———- ———- ———-
5 1 524288000
6 1 524288000
7 1 524288000
8 1 524288000
9 1 524288000

Quedando de la siguiente manera:

select GROUP#,member from v$logfile
 GROUP#       MEMBER
-------- ------------------------------------------------
 4        /oradata1/IRIS/onlinelog/o1_mf_4_cxjbnd5q_.log
 4        /oradata2/IRIS/onlinelog/o1_mf_4_cxjbndhw_.log
 3        /oradata1/IRIS/onlinelog/o1_mf_3_cxjbnbom_.log
 3        /oradata3/IRIS/onlinelog/o1_mf_3_cxjbncbd_.log
 2        /oradata2/IRIS/onlinelog/o1_mf_2_cxjbn9g5_.log
 2        /oradata3/IRIS/onlinelog/o1_mf_2_cxjbn9r6_.log
 1        /oradata1/IRIS/onlinelog/o1_mf_1_cxjbn7lk_.log
 1        /oradata2/IRIS/onlinelog/o1_mf_1_cxjbn7wl_.log
 5        /oradata1/IRIS/onlinelog/slog1.rdo
 6        /oradata1/IRIS/onlinelog/slog2.rdo
 7        /oradata1/IRIS/onlinelog/slog3.rdo
 8        /oradata1/IRIS/onlinelog/slog4.rdo
 9        /oradata1/IRIS/onlinelog/slog5.rdo

4.-Verificar parametros necesarios para armar el dataguard:

DB_NAME:

SQL> show parameter db_name
 NAME             TYPE               VALUE
 ------------ ----------------- ---------------
 db_name          string              IRIS

DB_UNIQUE_NAME:

SQL> show parameter db_unique_name
  NAME               TYPE         VALUE
 ---------------- ------------- ------------
  db_unique_name     string        IRIS

5.-Configurar ARCHIVE_LOG_CONFIG para enviar redo logs de origen a destino usando DB_UNIQUE_NAME.

Antes:

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                    TYPE       VALUE
 ------------------- ----------- ------------
 log_archive_config    string

Cambio:

SQL> alter system set log_archive_config='DG_CONFIG=(IRIS,IRISSBY)';
 System altered.

Después:

SQL> show parameter log_archive_config

NAME                   TYPE               VALUE
 ------------------- ------------   -------------------------
 log_archive_config    string        DG_CONFIG=(IRIS,IRISSBY)

6.-Habilitar LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2

Antes:

SQL> show parameter log_archive_dest_1
NAME                           TYPE         VALUE
--------------------------- ----------- -----------------------------------
log_archive_dest_1            string     LOCATION=/archivedata/archivelogs/
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string

Cambio:

SQL> alter system set log_archive_dest_1='LOCATION=/archivedata/archivelogs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRIS';

System altered.

despues:

SQL> show parameter log_archive_dest_1

NAME                    TYPE                VALUE
-------------------- ----------   ----------------------------------------
log_archive_dest_1     string     LOCATION=/archivedata/archive-
                                  logs/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
                                  DB_UNIQUE_NAME=IRIS
                                                                          
log_archive_dest_10     string
log_archive_dest_11     string
log_archive_dest_12     string
log_archive_dest_13     string
log_archive_dest_14     string
log_archive_dest_15     string
log_archive_dest_16     string
log_archive_dest_17     string
log_archive_dest_18     string
log_archive_dest_19     string

–Seteamos el transporte de redos en el log_archive_dest_2
Antes:

SQL> show parameter log_archive_dest_2
NAME                          TYPE           VALUE
 ----------------------  -------------- ----------------
 log_archive_dest_2           string
 log_archive_dest_20          string
 log_archive_dest_21          string
 log_archive_dest_22          string
 log_archive_dest_23          string
 log_archive_dest_24          string
 log_archive_dest_25          string
 log_archive_dest_26          string
 log_archive_dest_27          string
 log_archive_dest_28          string
 log_archive_dest_29          string

Cambio:

SQL> alter system set log_archive_dest_2='SERVICE=IRISSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=IRISSBY';
 System altered.

Después:

SQL> show parameter log_archive_dest_2
NAME                   TYPE            VALUE
------------------- --------- ----------------------------------
log_archive_dest_2   string   SERVICE=IRISSBY LGWR ASYNC VALID_FOR                                                  
                              =(ONLINE_LOGFILES,PRIMARY_ROLE) 
                              DB_UNIQUE_NAME=IRISSBY
 log_archive_dest_20 string
 log_archive_dest_21 string

7.-Configurar FAL_SERVER y FAL_CLIENT

FAL_SERVER=Fetch archivelog al destino – Primary
FAL_CLIENT=Cliente que recibe los archivelogs – Standby

FAL_SERVER:

SQL> alter system set fal_server=IRIS;
System altered.
SQL> show parameter fal_server
NAME                TYPE                 VALUE
 -------------- ------------------ ------------------
 fal_server           string             IRIS

FAL_CLIENT:

SQL> alter system set fal_client=IRISSBY;
System altered.
SQL> show parameter fal_client
 NAME                   TYPE             VALUE
 -------------- ----------------  ---------------------
 fal_client           string          IRISSBY

8.-Habilitar replicación de SO.

Para que el standby tambien replique operaciones del SO como adición o borrado de archivos(por ejemplo cuando agregas o borras datafiles en PROD,estas operaciones sean replicadas al standby), el parametro standby_file_management debe ser seteado a AUTO.

alter system set standby_file_management=auto;
SQL> show parameter standby_file_management
NAME                         TYPE             VALUE
 -----------------------  --------------- ------------------
 standby_file_management     string            AUTO

9.-Habilitar el login remoto

alter system set remote_loging_passwordfile=exclusive;
SQL> show parameter password
NAME                            TYPE            VALUE
-------------------------- ---------------- ----------------
remote_login_passwordfile     string            EXCLUSIVE

10.-Configurar conectividad de red:

La Instancia primaria debe estar registrada estaticamente con su propio listener

--Listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = IRIS)
 (ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1)
 (SID_NAME = IRIS)
 )
 )

Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en ambos servidores Primario y Standby.

-bash-4.2$ cat tnsnames.ora
IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )

STANDBY: Todos los siguientes pasos se ejecutarán en el entorno standby.

11.-Configuraciones de red en standby

Registrar en el tnsnames.ora las cadenas de conexion de IRIS y IRISSBY en servidor standby.

IRIS =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.44.155)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRIS)
 )
 )

IRISSBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = fcrac06)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = IRISSBY)
 ) (UR=A)
 )

–Registrar la BD standby staticamente en el listener del servidor standby
[grid@fcrac06 admin]$ cat listener.ora
LISTENER_EXT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_EXT)))) # line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=IRISSBY)
(SID_NAME=IRISSBY)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=() # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_EXT=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_EXT=SUBNET # line added by Agent

validar:

 [oracle@fcrac06 admin]$ lsnrctl status | grep IRIS
 Service "IRISSBY" has 1 instance(s).
 Instance "IRISSBY", status UNKNOWN, has 1 handler(s) for this service...

12.-Crear las rutas a nivel de SO que alojaran los archivos de BD.
/u01/app/oracle/admin/IRISSBY/

[oracle@ocm2 ~]$ mkdir oradata
[oracle@ocm2 ~]$ mkdir oradata/pritst
[oracle@ocm2 ~]$ mkdir oradata/pritst/arch
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/adump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/bdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/dpdump
[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/IRISSBY/pfile

13.- Registrar en el oratab del servidor standby

 vi /etc/oratab
 IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate

[oracle@fcrac06 admin]$ cat /etc/oratab | grep IRIS
IRIS:/u01/app/oracle/product/12.1.0/dbhome_1:N # line added by jeanpierre to standbyduplicate
[oracle@fcrac06 admin]$

14.-Crear pfile basico en $ORACLE_HOME/dbs del standby

[oracle@fcrac06 dbs]$ cat initIRISSBY.ora
DB_NAME=IRIS
DB_UNIQUE_NAME=IRISSBY
[oracle@fcrac06 dbs]$

15.-copiar el passwordfile de origen a standby
scp oracle@ocm1:$ORACLE_HOME/dbs/orapwIRIS $ORACLE_HOME/dbs

[oracle@fcrac06 dbs]$ ls -ltr
total 10220
-rw-r–r– 1 oracle oinstall 2992 Jun 26 2017 init.ora
-rw-r—– 1 oracle oinstall 7680 Apr 8 01:47 orapwIRIS —
-rw-r—– 1 oracle oinstall 7680 Apr 8 08:40 orapwIRISSBY
-rw-r—– 1 oracle asmadmin 10403840 Apr 8 10:38 cntrlIRISSBY.dbf
-rw-rw—- 1 oracle asmadmin 1544 Apr 8 10:46 hc_IRISSBY.dat
-rw-r–r– 1 oracle oinstall 1238 Apr 8 10:53 initIRIS.ora.old
-rw-r–r– 1 oracle oinstall 36 Apr 8 13:16 initIRISSBY.ora

16.-Usando el pfile simple subir el standby hasta nomount.

[oracle@fcrac06 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 8 13:32:20 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.

SQL> !pwd
/u01/app/oracle/product/12.1.0/dbhome_1/dbs

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initIRISSBY.ora';
 ORACLE instance started.

Total System Global Area 2466250752 bytes
 Fixed Size 2927240 bytes
 Variable Size 2097153400 bytes
 Database Buffers 318767104 bytes
 Redo Buffers 47403008 bytes

17.-Preparar script para duplicate
[oracle@fcrac06 ~]$ cat dupstby.cmd

 run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit

18.-En el servidor standby conectarse al primario y auxiliay(standby) a traves de RMAN y ejecutar el duplicate database
[oracle@fcrac06 ~]$ rman target sys/oracle@IRIS

Recovery Manager: Release 12.1.0.2.0 – Production on Sun Apr 8 13:52:27 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: IRIS (DBID=1678477687)

RMAN>

RMAN> connect auxiliary sys/oracle@IRISSBY
connected to auxiliary database: IRIS (not mounted)

RMAN>
run {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database
 for standby
 from active database
 dorecover
 spfile
 set db_unique_name='IRISSBY'
 set fal_client='IRIS'
 set fal_server='IRISSBY'
 set log_archive_dest_1='location=+DG_ARCH12C_02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=IRISSBY'
 set log_archive_dest_2='service=IRIS ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=IRIS'
 set db_create_file_dest='+DG_DATA12C_02'
 set db_create_online_log_dest_1='+DG_ARCH12C_02'
 set db_create_online_log_dest_2='+DG_DATA12C_02'
 set db_create_online_log_dest_3='+DG_DATA12C_02'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(IRIS,IRISSBY)'
 set audit_file_dest='/u01/app/oracle/admin/IRISSBY/adump'
 set core_dump_dest='/u01/app/oracle/admin/IRISSBY/cdump'
 set diagnostic_dest='/u01/app/oracle'
 nofilenamecheck;
 }
 exit

19.-Proceso de duplicate inicia:
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 100931731456 bytes

Fixed Size 7656992 bytes
Variable Size 13958646240 bytes
Database Buffers 86704652288 bytes
Redo Buffers 260775936 bytes
allocated channel: aux
channel aux: SID=2401 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/oradata/IRISSBY/controlfile/control01.ctl’;
}
executing Memory Script

Starting backup at 08-APR-18
channel c1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_IRIS.f tag=TAG20180408T165819
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-APR-18

contents of Memory Script:

20.-Iniciar Active Dataguard

—Activar proceso de Sincronización MRP.

SQL> select status from v$instance;
 STATUS
 ------------
 MOUNTED
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
 --------------------
 READ ONLY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

21.-Validación final.
Finalmente generamos 3 archivelogs en el origen y validamos sean aplicados en standby

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
 SELECT sequence#, first_time, next_time, applied, thread# FROM v$archived_log ORDER BY 1,2;
 SEQUENCE#   FIRST_TIME           NEXT_TIME           APPLIED  THREAD#
 ---------- -------------------- -------------------- -------  -------
 5262       08/04/2018 17:18:36  08/04/2018 17:20:20  YES        1
 5263       08/04/2018 17:20:20  08/04/2018 17:21:14  YES        1
 5264       08/04/2018 17:21:14  08/04/2018 17:22:56  YES        1
 5265       08/04/2018 17:22:56  08/04/2018 17:23:15  YES        1
 5266       08/04/2018 17:23:15  08/04/2018 17:23:18  IN-MEMORY  1

como se puede apreciar nuestro nuevo physical standby se encuentra terminado y totalmente funcional!!!.

ALTO CONSUMO DE SHARED POOL, ORA-04031: unable to allocate 63496 bytes of shared memory

Hace unos días, al iniciar mi jornada laboral como DBA, me reportaron el siguiente error,el cual a mas de uno le debe resultar familiar : ORA-04031: unable to allocate 13840 bytes of shared memory.

Este error se presentó en una BD  Container RAC 12.2.0.1 de arquitectura multitenant,la cual tenia mas de un Pluggale Database.(el tratamiento de este error es el mismo en BDs no container ).

Rápidamente, revisando el alert.log de cada nodo encontramos que el siguiente error se presentaba de manera muy recurrente.

Errors in file /u01/app/oracle/diag/rdbms/cdbeai/CDBxxx/trace/CDBxxx_ora_51418.trc (incident=905203) (PDBNAME=AUDxxx):
ORA-04031: unable to allocate 13840 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(2,0)”,”ges resource dynamic”)
AUDxxx(3):Incident details in: /u01/app/oracle/diag/rdbms/cdbxxx/CDBxxxx/incident/incdir_905203/CDBxxx_ora_51418_i905203.trc
AUDxxx(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2018-03-15T09:22:29.074949-05:00

high contention shared pool_2

El mensaje de error es bastante claro,la base de datos requiere mayor memoria en el shared pool de la que hay disponible.

Verificando, la BD tiene 25GB de SGA en total

SQL> show parameter sga

NAME                                          TYPE         VALUE
————————————       ————- ——————————
allow_group_access_to_sga  boolean    FALSE
lock_sga                                    boolean    FALSE
pre_page_sga                           boolean    TRUE
sga_max_size                           biginteger 25G
sga_min_size                            big integer 0
sga_target                                big integer     25G
unified_audit_sga_queue_size integer 1048576

Investigando a mayor detalle desde cloud control,nos encontramos que el 98% de la memoria asignada al SGA es consumida por el shared pool.(Ops eso no suena nada bien!!).

shared_pool_cloudcontrol1

En la distribución de memoria entre buffers del SGA, se puede apreciar que de 25GB que tiene como total el SGA,22.6GB es consumido por el shared pool.

shared_pool_cloudcontrol2

La distribución de memoria entre buffers del SGA tambien puede visualizarse usando el siguiente query:

SQL> SELECT component, current_size/1024/1024/1024 gbytes
FROM v$sga_dynamic_components
ORDER BY 2 DESC
FETCH FIRST 5 ROWS ONLY;

COMPONENT                                                   GBYTES
—————————————————————- ———-
shared pool                                                       22.6875
large pool                                                            1.1875
Shared IO Pool                                                      .5
DEFAULT buffer cache                                     .3125
streams pool                                                        .125

Revisando a detalle el consumo dentro del shared pool, encontramos que había indicios de memoria retenida en el shared pool por “ges resources“:

SQL> SELECT pool, name, bytes/1024/1024/1024 gbytes
FROM v$sgastat
WHERE pool=’shared pool’
ORDER BY BYTES DESC
FETCH FIRST 5 ROWS ONLY;

POOL                     NAME                          GBYTES
————–         ————————–           ——————-
shared pool       ges enqueues                 5.82885164
shared pool       SQLA                               4.86808275
shared pool       ges resource dynamic 2.52088516
shared pool       free memory                 2.33909176
shared pool      gc index split transaction .86762473

Ahora pasaremos a revisar como ha ido cambiando la asignación de memoria entre buffers dentro del SGA ,desde el ultimo reinicio de la instancia de BD.Para lo cual usaremos el siguiente query bastante útil, obtenido del blog de Andrea Salzano

col FINAL_SIZE for 999,999,999,999,999
 set pages 99
 set lines 210
 col component for a20

with gv_sga_resize_ops as (
 select inst_id instance_number , to_char(START_TIME,'yyyymmdd') START_DAY, START_TIME, COMPONENT, OPER_TYPE, OPER_MODE, final_size
 from GV$SGA_RESIZE_OPS
 where COMPONENT in ('DEFAULT buffer cache','shared pool')
 )
 select instance_number inst_id, START_TIME last_start_time, COMPONENT, OPER_TYPE last_OPER_TYPE, OPER_MODE, final_size, oper_cnt from (
 select instance_number, START_DAY, COMPONENT, OPER_TYPE, OPER_MODE, final_size,
 row_number() over (partition by instance_number, START_DAY, COMPONENT, OPER_TYPE order by instance_number, START_TIME desc) rn,
 count(*)over (partition by instance_number, START_DAY, COMPONENT, OPER_TYPE) oper_cnt, START_TIME
 from gv_sga_resize_ops o
 ) where rn=1
 and instance_number=2
 order by 1,3,2;

Claramente, se puede apreciar, en la columna FINAL_SIZE,que desde el ultimo reinicio de la instancia de BD, el 22/DEC/2017 el Data Buffer Cache tenia una tendencia de hacerse mas pequeño(SHRINK),mientras que a la par el Shared Pool fue creciendo(GROW), hasta ocupar casi todo el SGA.

shARed_pool_DETAIL.jpg

Indagando un poco en My Oracle Support, así como también otras páginas de colegas https://dbamarco.wordpress.com/2015/10/29/shared-pool-memory-leak-in-12c-rac/

encontré la siguiente nota técnica, la cual hace referencia a un bug 21373473, debido a bloqueos de tipo DX y BB los cuales están siendo capturados en el shared pool , pero no están siendo limpiados.

ORA-04031 Errors Occurring with High “ges resource dynamic” & “ges enqueues” Memory Usage In The Shared Pool (Doc ID 2063751.1)

Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination.

Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here.

This issue can also be worked around by setting _GES_DIRECT_FREE_RES_TYPE=”CTARAHDXBB” in the instances.

+ otra nota relacionada

NOTE:1951758.1 – ORA-4031 due to Huge “ges resource dynamic” and “ges enqueues” and Instance Terminates: ORA-29770: ‘SGA: allocation forcing component growth’.

Finalmente decidí aplicar el workaround(Doc ID 2063751.1), de setear el parámetro “_ges_direct_free_res_type”, en ambas instancias del RAC:

alter system set “_ges_direct_free_res_type”="CTARAHDXBB" scope=spfile sid=’*’;

…Finalmente

Una semana después del cambio observamos que el comportamiento del shared pool volvió a la normalidad y se ha mantenido estable.

shred_pool_wa

sharedpool_wa2

 

 

PERFORMANCE TUNING : ACTIVE SESSION HISTORY -ASH -PARTE I

INTRODUCCIÓN:

Los problemas de Performance en una Base de datos Productiva,son quizá,los tipo de problema mas críticos, complejos y personalmente considero que son los mas emocionantes de resolver!!!.

¿Cuantas veces nos ha pasado en nuestro día a día como DBAs, que nos han pedido analizar e indicar a detalle que ocasionó un cuello de botella en nuestras BDs en un punto en el pasado?,probablemente de hace unas horas,días o incluso semanas atrás.

Active Session History(ASH), es una excelente herramienta para analizar problemas de performance que suceden en tiempo real,así como también en un punto en el pasado.

ASH brinda la mayoría de información disponible en trace files pero sin necesidad de generar trace files,esta siempre esta disponible.Usar ASH es extremadamente simple, por que no tienes que generar eventos e ir a la ruta en el servidor a revisar las trazas generadas, basta con consultar la vista v$active_session_history.

Cabe resaltar que Active Session History, es un feature disponible desde Oracle 10g release 1 y su uso requiere licencia Diagnostic Pack.

ash-architecture-and-advanced-usage-rmoug2014-7-6381

Cada segundo, Active Session History sensa la base de datos y recolecta importante información de las sesiones activas(sesiones inactivas no son capturadas) y pobla el ASH Buffer,el cual se puede consultar desde la vista v$active_session_history.

  • SESSION:session_id,session_serial#,user_id(puede ser usado contra la dba_users para obtener el username),session_type,session_state,qc_session_id,qc_instance_id
  • WAIT:event,event_id,event#,seq#,p1,p2,p3
  • SQL: sql_id,sql_child_number,sql_plan_hash_value,sql_opcode
  • OBJECT:current_obj#,current_file#,current_block#
  • APPLICATION: Program,Module,action,client_id,service_hash

ash

La información disponible en la v$active_session_history es poblada en base a la información disponible de las vistas v$session y v$session_wait y permite obtener información importante propia de la sesion, wait, sentencia SQL,objeto,aplicacion:

image002

RETENCIÓN:

La retención de active session history, es dependiente de la retención del AWR.

Oracle escribe la información de memoria a disco, desde v$active_session_history hacia la tabla wrh$active_session history, a la cual podemos consultar a través de la vista dba_hist_active_sess_history. Esta acción la realiza cada hora antes de cada snapshot del AWR o cuando el Ash buffer este al 2/3 de su capacidad en el SGA, cabe resaltar que no toda la información baja a disco, solo 1 de cada 10 entradas.

Calcular Retención en dias de ASH:

 select sysdate - a.sample_time ash,
 sysdate - s.begin_interval_time snap,
 c.RETENTION
 from sys.wrm$_wr_control c,
 (
 select db.dbid,
 min(w.sample_time) sample_time
 from sys.v_$database db,
 sys.Wrh$_active_session_history w
 where w.dbid = db.dbid group by db.dbid
 ) a,
 (
 select db.dbid,
 min(r.begin_interval_time) begin_interval_time
 from sys.v_$database db,
 sys.wrm$_snapshot r
 where r.dbid = db.dbid
 group by db.dbid
 ) s
 where a.dbid = s.dbid
 and c.dbid = a.dbid;

retention_ash

SELECT min(sample_time) min_sample_time, max(sample_time) max_sample_time FROM V$ACTIVE_SESSION_HISTORY;

PARÁMETROS:

_ash_size
_ash_enable
_ash_sampling_interval
_ash_disk_write_enable
_ash_disk_filter_ratio

referencias:

 

¿Que relación tiene la partición tmpfs /dev/shm con  AMM memory_target? – ORA-00845: MEMORY_TARGET not supported on this system

¿Se han percatado que cada vez que consultamos los filesystem de nuestros entornos Linux, tenemos un filesystem tmpfs /dev/shm ?.

shm_memory

La partición del sistema /dev/shm, es la memoria compartida del servidor, esta tiene relación directa con la memoria asignada al SGA en bases de datos Oracle cuando usamos Automatic Memory Management(AMM).
Como regla, la cantidad de memoria asignada a la partición /dev/shm debe ser lo suficientemente grande para soportar a los parámetros MEMORY_TARGET y MAX_MEMORY_TARGET,como buena practica /dev/shm, debe ser al menos poco mas de la suma total del parámetro max_memory_target de todas las instancias de Base de Datos que corren en un servidor.

Por ejemplo, si tenemos 3 base de datos desplegadas en un mismo servidor:

tmpfs /dev/shm > max_memory_target BD1 + max_memory_target BD2 + max_memory_target BD3

Es recomendable dimensionar /dev/shm teniendo en cuenta que debe quedar al menos el 5%-10% para el overhead del sistema operativo.

NOTA: En entornos RAC,este cálculo debe realizarse por cada nodo.

Si asignamos al parámetro memory_target mayor valor que el disponible en /dev/shm, oracle nos mostrará el siguiente mensaje de error:
ORA-00845: MEMORY_TARGET not supported on this system
Si creamos una nueva BD en un servidor de base de datos, que ya tiene previamente otras BDs creadas y no hay mas memoria SHM disponible en el servidor, se mostrará el siguiente mensaje:
SOLUCIÓN:
Para superar este inconveniente, se puede optar por dos opciones:
  • A)Disminuir el valor del parámetro memory_target
  • B)Aumentar espacio asignado a la partición tempfs /dev/shm.
La opción A) parece muy obvia , así que vayamos por la opción B):
En caso de requerir adicionar mayor espacio al SHM , tu Administrador de Sistema Operativo, debería realizar los siguientes pasos con el usuario root:

.

# umount tmpfs
# mount -t tmpfs shmfs -o size=15g /dev/shm
En caso se muestre el siguiente mensaje
“umount: /path: device is busy”
Esto es por que la memoria compartida aun esta en uso, para investigar que proceso lo usa podemos usar-> lsof /dev/shm y liberar dichos procesos  ó usar la opción force umount -f.
Recordar, que para hacer persistente este cambio,posteriormente debe ser registrado en el fstab.
[root@oracle-jeanp~]# cat /etc/fstab
tmpfs /dev/shm tmpfs size=15g  0  0