Recriar o schema do AWR

Em alguns momentos, podemos ter a necessidade de recriação do AWR em nosso banco de dados.

Isto ocorre principalmente quando precisamos fazer uma manutenção na tablespace SYSAUX e/ou quando a geração de snapshots do AWR apresenta problema, não sendo gerado de forma adequada.

Antes de fazer a recriação do AWR, precisamos verificar a necessidade de guardarmos ou não os dados atuais do AWR, ou seja, os snapshots criados até este momento.

Se decidirmos por guardar, podemos fazer um extração dos dados e guardá-los em um arquivo para um possível retorno ou import em um outro ambiente.
Mas, para isso, precisamos fazer o export utilizando um script Oracle, que pode ser verificado nesse link:  http://oracle-sql-procedimentos.blogspot.com.br/2017/01/exportar-dados-do-schema-awr.html

Após feito o export e guardado nosso arquivo, vamos recriar o AWR seguindo os passos abaixo:


1) Verificar os parâmetros de configuração do banco de dados.

Antes de iniciarmos a recriação, precisamos obter algumas informações de configuração do banco de dados que serão úteis para o prosseguimento da atividade.

As informações mais inportantes nesse momento são:

- Ambiente é cluster ou single?
- Nível de estatística configurado.
- Valor atribuído ao SGA_TARGET
- Local do spfile


    sqlplus /nolog
    connect / as sysdba
    show parameter cluster_database
    show parameter statistics_level
    show parameter sga_target
    show parameter spfile


É recomendado salvar a versão atual do spfile, pois posteiormente iremos voltar o banco de dados para a configuração original.

    SQL> create pfile='/home/oracle/init.ora' from spfile;



2) Alteração de parâmetros do banco de dados

Para podermos recriar o AWR, os parâmetros abaixo do banco de dados devem ser alterados:

    alter system set shared_pool_size = 200m scope = spfile;
    alter system set db_cache_size = 300m scope = spfile;
    alter system set java_pool_size = 100 scope = spfile;
    alter system set large_pool_size = 50 scope = spfile;
    alter system reset sga_target scope= spfile;
    alter system reset memory_target scope= spfile;
    alter system reset memory_max_target scope=spfile;
    alter system set statistics_level=basic scope=spfile;


Se o banco de dados for um banco cluster, devemos alterar também o parâmetro abaixo:

    alter system set cluster_database=false scope=spfile;



3) Reiniciar o banco de dados

Após a alteração dos parâmetros, devemos reiniciar o banco de dados em modo restrito.

Se o ambiente for um cluster, devemos iniciar apenas uma instância, deixando as demais off-line.

    SQL> startup restrict


Após o banco de dados estar on-line, devemos verificar se os valores da sga_target e memory_target estão iguais a ZERO (0), caso não estejam, devem ser alteradas, conforme comandos abaixo:


    SQL> alter system set sga_target=0 scope= spfile;
    SQL> alter system set memory_target=0 scope= spfile;


IMPORTANTE:

Em alguns casos, durante a reinicialização do banco de dados, podemos receber o erro abaixo e o banco de dados não iniciar.

ORA-01078: failure in processing system parameters
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

Se recebermos o erro acima e o banco não iniciar, devemos parar o banco de dados e novamente reiniciá-lo.

    sqlplus /nolog
    SQL> connect / as sysdba
    SQL> shutdown immediate
    SQL> startup restrict



4) Remover os objetos do AWR

Para remover os objetos do AWR devemos utilizar o script catnoawr.sql, conforme descrito abaixo:

    SQL> @?/rdbms/admin/catnoawr.sql

Em seguida, executar um flush na shared_pool:

    SQL> alter system flush shared_pool;


5) Verificar objetos do AWR

Após a execução do script catnoawr.sql e do flush na shared_pool, devemos verificar se ainda existe algum objeto do AWR no banco de dados, para isso, temos o SQL abaixo:

    SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

Se ainda existir algum objeto, devemo excluí-lo manualmente, conforme o exemplo abaixo:

     drop type AWR_OBJECT_INFO_TABLE_TYPE;
     drop type AWR_OBJECT_INFO_TYPE;
     drop table WRH$_PLAN_OPERATION_NAME;
     drop table WRH$_PLAN_OPTION_NAME;
     drop table WRH$_MV_PARAMETER;
     drop table WRH$_MV_PARAMETER_BL;
     drop table WRH$_DYN_REMASTER_STATS;
     drop table WRH$_PERSISTENT_QMN_CACHE;
     drop table WRH$_DISPATCHER;
     drop table WRH$_SHARED_SERVER_SUMMARY;
     drop table WRM$_WR_USAGE;
     drop table WRM$_SNAPSHOT_DETAILS;


Obs.:
- O Ideal é executar os comandos drop acima, mesmo que não apareçam no select anterior de verificação.
- Se aparecerem uma ou duas tabelas apenas e ao executar o drop aparecer a informação de que o objeto não existe, mesmo sendo exibido, devemos executar a lista de drop acima por completo, que todos os objetos serão removidos.

- Em bancos de dados Oracle 12c, além das tabelas informadas acima, também temos que remover as seguintes tabelas(que não existiam no 11g):

WRH$_REPLICATION_TBL_STATS
WRH$_REPLICATION_TXN_STATS
WRH$_SESS_SGA_STATS

Para isso, utilizaremos os comandos abaixo:

drop table WRH$_REPLICATION_TBL_STATS;
drop table WRH$_REPLICATION_TXN_STATS;
drop table WRH$_SESS_SGA_STATS;


6) Criar os objetos do AWR

Para criarmos os objetos do AWR, devemos executar o script catawrtb.sql, conforme descrito a seguir:

    SQL> @?/rdbms/admin/catawrtb.sql


Após a criação dos objetos, devemos recompilar todos os objetos utilizando o utlrp.sql

    SQL> @?/rdbms/admin/utlrp.sql


Em banco de dados 11G e 12C é necessário também executar o script execsvrm.sql

    SQL> @?/rdbms/admin/execsvrm.sql


A execução do script execsvrm.sql pode gerar os erros abaixo:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been invalidated

Se isso ocorrer, devemos recompilar os seguintes objetos manualmente:

    SQL> alter package dbms_swrf_internal compile;
    SQL> alter package dbms_swrf_internal compile body;


Obs.: É extremamente importante que os objetos dbms_swrf_internal estejam como válidos.

Após esses objetos estarem válidos, devemos executar novamente o script execsvrm.sql



7) Fazer um shirink na tablespace SYSAUX

Após uma manutenção feita na tablespace SYSAUX, é interessante fazer um shrink, para que os dados sejam reordenados e o espaço livre liberado.

O shrink é feito por datafile (No Oracle 12c, podemos fazer por tablespace), portanto precisamos identificar os datafiles que pertencem a tablespace SYSAUX.

Para isso, podemos executar o SQL abaixo:

SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX' order by 1;

FILE_NAME
------------------------------------------------
+DG_01/xpto/sysaux_01.dbf
+DG_01/xpto/sysaux_02.dbf
+DG_01/xpto/sysaux_03.dbf
+DG_01/xpto/sysaux_04.dbf


Em seguida, podemos verificar até quanto poderemos reduzir o tamanho dos datafiles, utilizando o SQL abaixo:


set lines 200
col file_name format a50

SELECT  file_name, CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb" FROM dba_data_files f
LEFT JOIN
    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.file_name like '%sysaux%';


FILE_NAME                       Mb
------------------------------ ------
+DG_01/xpto/sysaux_01.dbf      1024
+DG_01/xpto/sysaux_02.dbf      1024
+DG_01/xpto/sysaux_03.dbf      1024
+DG_01/xpto/sysaux_04.dbf      1024


Com as informações dos data files e o valor mínimo que poderemos diminuir cada data file, vamos fazer o shrink com o comando abaixo:

SQL> alter database datafile 'datafile' resize <VALOR>M;


alter database datafile '+DG_01/xpto/sysaux_01.dbf' resize 1024M;
alter database datafile '+DG_01/xpto/sysaux_02.dbf' resize 1024M;
alter database datafile '+DG_01/xpto/sysaux_03.dbf' resize 1024M;
alter database datafile '+DG_01/xpto/sysaux_04.dbf' resize 1024M;



8) Voltar parâmetros do SPFILE

Agora, que já recriamos o AWR e fizemos o shrink na tablespace SYSAUX, devemos voltar os parâmetros originais do SPFILE, conforme cópia feita no início da atividade, e para isso precisamos:

Parar o banco de dados

    SQL> shutdown immediate

Iniciar o banco com o arquivo pfile - criado no início das atividades

    sqlplus / as sysdba

    SQL> startup pfile='/home/oracle/init.ora' ;


Recriar o spfile original

    SQL> create spfile='+DG_01/xpto/spfile.ora from pfile='/home/oracle/init.ora' ;


9) Reiniciar o banco de dados

Reiniciar o banco de dados em modo normal, com os parâmetros originais

sqlplus /nolog
connect / as sysdba
shutdown immediate
startup


* Neste momento o banco de dados volta em seu modo normal. Se for um ambiente cluster, todas as instâncias de banco de dados deverão ser iniciadas.


10) Verificar objetos inválidos

Após o banco de dados iniciar em modo normal, devemos verificar se existe algum objeto inválido e para isso, podemos verificar os objetos inválidos no banco de dados com os sqls abaixo:


    select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

    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;

    select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;


Se ainda existir objetos inválidos, devemos recompilá-los, manualmente ou através do utlrp.sql.


11) Executar snapshots

Nosso próximo passo é verificar se os snapshots do AWR são gerados de forma correta no banco de dados e para isso, podemos executar o comando abaixo:

    SQL> exec dbms_workload_repository.create_snapshot;

Esperar 5 minutos

    SQL> exec dbms_workload_repository.create_snapshot;




12) Gerar relatório AWR

Para finalizar, após ter gerado pelo menos 2 snapshots, iremos gerar um relatório AWR, e para isso devemos executar o script awrrpt.sql

    SQL> @?/rdbms/admin/awrrpt.sql



Obs.:

Após a recriação dos objetos do AWR ele voltará com as configurações padrão do Oracle para snapshots e tempo de retenção, sendo 1 snapshot a cada 1 hora e armazenamento desses snapshots por 7 dias.




Referência:

https://docs.oracle.com/cd/E18283_01/server.112/e17120/tspaces007.htm
http://stackoverflow.com/questions/15563408/shrink-permanent-tablespace-in-oracle-11g
How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=306121842606596&id=782974.1&_adf.ctrl-state=l4f4s2ae5_110