ORACLE Place: novembro 2013

segunda-feira, 18 de novembro de 2013

Recriando o repositório do AWR manualmente

O repositório do AWR (Automatic Workload Repository) é gerado automaticamente durante a criação do banco de dados, porém algumas vezes o DBA pode ter a necessidade de recriá-lo, neste artigo a demonstraremos os passos necessários.

1. Conectar-se no SQL*Plus

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 4 10:43:53 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

2. Verificar os seguintes parâmetros (cluster_database, statistics_level, sga_target),

SQL> show parameter cluster_database;

NAME TYPE VALUE

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

cluster_database boolean TRUE

cluster_database_instances integer 2

SQL> show parameter statistics_level

NAME TYPE VALUE

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

statistics_level string TYPICAL

SQL> show parameter sga_target

NAME TYPE VALUE

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

sga_target big integer 8000M

Importante: Caso os valores sejam diferentes dos apresentados acima, deve-se alterar para os valores propostos para que o AWR funcione corretamente.

3. “Restartar” o banco de dados em modo restrito.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shutdown.

SQL> startup restrict;

ORACLE instance started.

4. Para remover o catálogo do AWR executar o script abaixo:

SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sql

5. Para recriar o catálogo do AWR executar.

SQL> alter system flush shared_pool;

System altered.

SQL> @$ORACLE_HOME/rdbms/admin/catawrtb.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @$ORACLE_HOME/rdbms/admin/execsvrm.sql

6. “Restartar” o banco de dados.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shutdown.

SQL> startup;

ORACLE instance started.

7. Verificar objetos inválidos no banco de dados, caso você encontre deve-se recompilar os objetos.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

8. Verificar os “status” dos componentes do banco de dados, executar a consulta abaixo:

SQL> SET PAGESIZE 500

SQL> SET LINESIZE 100

SQL> SELECT SUBSTR(COMP_NAME,1,40) COMP_NAME, STATUS, SUBSTR(VERSION,1,10) VERSION

FROM DBA_REGISTRY

ORDER BY COMP_NAME;

COMP_NAME STATUS VERSION

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

JServer JAVA Virtual Machine VALID 11.2.0.1.0

Oracle Database Catalog Views VALID 11.2.0.1.0

Oracle Database Java Packages VALID 11.2.0.1.0

Oracle Database Packages and Types VALID 11.2.0.1.0

Oracle Enterprise Manager VALID 11.2.0.1.0

Oracle Expression Filter VALID 11.2.0.1.0

Oracle Real Application Clusters VALID 11.2.0.1.0

Oracle Rules Manager VALID 11.2.0.1.0

Oracle Text VALID 11.2.0.1.0

Oracle Workspace Manager VALID 11.2.0.1.0

Oracle XDK VALID 11.2.0.1.0

Oracle XML Database VALID 11.2.0.1.0

12 rows selected.

SQL> SELECT SUBSTR(OBJECT_NAME,1,40) OBJECT_NAME, SUBSTR(OWNER,1,15) OWNER,OBJECT_TYPE

FROM DBA_OBJECTS

WHERE STATUS='INVALID'

ORDER BY OWNER, OBJECT_TYPE;

no rows selected

SQL> SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM DBA_OBJECTS

WHERE STATUS='INVALID'

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

no rows selected

Importante: Caso algum componente ou objeto esteja inválido no banco de dados, deve-se providenciar a validação para o funcionamento adequado do AWR.

9. Gerar o primeiro “snapshot” no AWR, usar:

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Com o primeiro “snapshot” as tabelas do AWR irão armazenar as estatísticas do banco de dados.

10. Verificar a periodicidade de atualização do AWR, executar a consulta abaixo:

SQL> SET LINES 200

SQL> COL SNAP_INTERVAL FOR A20

SQL> COL RETENTION FOR A20

SQL>

SQL> SELECT * FROM DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL

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

9999000001 +00000 01:00:00.0 +00020 00:00:00.0 DEFAULT

O resultado da consulta mostra que o AWR será executado em intervalos de uma hora e a retenção dos dados é de vinte dias (em vermelho).

Referências: Oracle^® Database Performance Tuning Guide 11gR2 (11.2)

Oracle^® Database Concepts 11gR2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com

sábado, 2 de novembro de 2013

Expurgando dados do AWR manualmente

Mesmo com as rotinas de retenção que são configuradas no AWR, algumas vezes, pode ser que o repositório do AWR necessite ser expurgado manualmente. Neste artigo vamos demonstrar um procedimento para o expurgo controlado do repositório do AWR.

Importante: Para a execução deste procedimento, certifique-se que seus dados foram “salvos” em um “backup” ou mesmo que você não irá utilizar as informações que serão removidas.

A primeira coisa que devemos verificar é a quantidade de informação armazenada pelo AWR, deve-se executar a consulta abaixo:

SQL> set lines 200

SQL> col Nome for a10

SQL> col Descrição for a60

SQL>

SQL> SELECT OCCUPANT_NAME NOME,

2 OCCUPANT_DESC DESCRIÇÃO,

3 ROUND( SPACE_USAGE_KBYTES/1024) "ESPAÇO (M)"

4 FROM V$SYSAUX_OCCUPANTS

5 WHERE OCCUPANT_NAME LIKE 'SM/%'

6 ORDER BY 1 ;

NOME DESCRIÇÃO ESPAÇO (M)

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

SM/ADVISOR Server Manageability - Advisor Framework 323

SM/AWR Server Manageability - Automatic Workload Repository 4187

SM/OPTSTAT Server Manageability - Optimizer Statistics History 344

SM/OTHER Server Manageability - Other Components 8

A linha em destaque é a informação que desejamos saber, ou seja, o tamanho do repositório de dados do AWR, para este exemplo, o tamanho do repositório é de 4 MB.

Depois vamos verificar os intervalos de meses que estão armazenados no repositório para que posteriormente executemos o expurgo.

SQL> COL BEGIN_INTERVAL_TIME FOR A30

SQL> COL END_INTERVAL_TIME FOR A30

SQL>

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

2 FROM SYS.WRM$_SNAPSHOT

3 WHERE SNAP_ID = (SELECT MIN (SNAP_ID) FROM SYS.WRM$_SNAPSHOT)

4 UNION

5 SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

6 FROM SYS.WRM$_SNAPSHOT

7 WHERE SNAP_ID = (SELECT MAX (SNAP_ID) FROM SYS.WRM$_SNAPSHOT)

8 ORDER BY 2

9 /

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

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

156971 27-SEP-13 12.00.29.580 AM 27-SEP-13 12.10.31.001 AM

160065 18-OCT-13 11.40.27.729 AM 18-OCT-13 11.50.28.105 AM

Os valores apresentados pela coluna SNAP_ID, indicam as informações gravadas no repositório do AWR. Neste exemplo temos informações do mês de Setembro e Outubro.

Antes de removermos os dados, vamos verificar os registros de um determinado intervalo, para esse exemplo vamos listar todos os registros do mês de Setembro.

SQL> col begin_interval_time for a30

SELECT snap_id, begin_interval_time

FROM SYS.WRM$_SNAPSHOT

WHERE instance_number = 1

AND begin_interval_time like '%SEP%'

order by 1;

/

SNAP_ID BEGIN_INTERVAL_TIME

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

156971 27-SEP-13 12.00.29.580 AM

156972 27-SEP-13 12.10.31.001 AM

156973 27-SEP-13 12.20.31.381 AM

156974 27-SEP-13 12.30.31.722 AM

.

157543 30-SEP-13 11.20.24.685 PM

157544 30-SEP-13 11.30.27.979 PM

157545 30-SEP-13 11.40.28.395 PM

157546 30-SEP-13 11.50.34.756 PM

De posse das informações a serem expurgadas (SNAP_ID), deve-se executar o passo abaixo:

SQL> execute dbms_workload_repository.drop_snapshot_range(low_snap_id => 156971, high_snap_id=>157546);

PL/SQL procedure successfully completed.

Para a execução da DBMS_WORKLOAD_REPOSITORY, deve-se informar o intervalo inicial e final, em destaque no texto e apresentado na relação (coluna SNAP_ID). Para esse exemplo a rotina irá remover as linhas referentes ao mês de Setembro.

Após o expurgo, podemos verificar agora que temos somente o mês de Outubro.

SQL> COL END_INTERVAL_TIME FOR A30

SQL>

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

2 FROM SYS.WRM$_SNAPSHOT

3 WHERE SNAP_ID = (SELECT MIN (SNAP_ID) FROM SYS.WRM$_SNAPSHOT)

4 UNION

5 SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

6 FROM SYS.WRM$_SNAPSHOT

7 WHERE SNAP_ID = (SELECT MAX (SNAP_ID) FROM SYS.WRM$_SNAPSHOT)

8 ORDER BY 2

9 /

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

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

157547 01-OCT-13 12.00.35.054 AM 01-OCT-13 12.10.38.520 AM

160075 18-OCT-13 01.20.39.554 PM 18-OCT-13 01.30.39.997 PM

Referências: Oracle^® Database Performance Tuning Guide 11gR2 (11.2)

Oracle^® Database Concepts 11gR2 (11.2)

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com