PASSOS PARA RECRIAR O AWR

Steps to recreate AWR Repository(Automatic Workload)

We normally do cloning of our databases which sometimes add multiple DBID’s in the repository.
We can check the multiple dbid’s while generating the awr report using @?/rdbms/admin/awrrpt
To delete all the unwanted dbid’s we have to recreate the repository.

DB_ID DB_NAME HOST_PLATFORM INST
 —————– ———– ——————————————————– ———–
 826177187 PRODDEV ifebdbdev – AIX-Based Systems (64-bit) 1
 *3309173529 EBSDBPRD IFEBPRD1 – AIX-Based Systems (64-bit) 1
 3309173529 EBSDBPRD IFEBPRD2 – AIX-Based Systems (64-bit) 2
 212389454 PRODDDB1 PRODDB – AIX-Based Systems (64-bit) 1

The doc is based on the RAC 2 node database using pfile.

Steps to recreate the AWR (Automatic Workload Repository):

1) shut down application services
 2) shut down node 1 database
 $ sqlplus
 SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 4 14:50:28 2011
 Copyright (c) 1982, 2009, Oracle. All rights reserved.
 Enter user-name: / as sysdba
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
 With the Partitioning, Real Application Clusters, OLAP, Data Mining
 and Real Application Testing options
SQL> create spfile from pfile;
 File created.
SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
3) shut down node 2 database
 SQL> shu immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
4) Perform rest of the activites on node 1 database
 SQL> startup
 SQL> select name from v$database;
 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 4G
SQL> alter system set sga_target=0 scope=spfile;
 System altered.
SQL> alter system set cluster_database=false scope=spfile;
 System altered.
SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
SQL> startup restrict
 ORACLE instance started.
 Total System Global Area 509485056 bytes
 Fixed Size 2208088 bytes
 Variable Size 444599976 bytes
 Database Buffers 50331648 bytes
 Redo Buffers 12345344 bytes
 Database mounted.
 Database opened.
SQL> show parameter cluster_database
 NAME TYPE VALUE
 ———————————— ———– ——————————
 cluster_database boolean FALSE
 cluster_database_instances integer 1
SQL> show parameter statistics_level
 NAME TYPE VALUE
 ———————————— ———– ——————————
 statistics_level string TYPICAL
SQL> show parameter sga_target
 NAME TYPE VALUE
 ———————————— ———– ——————————
 sga_target big integer 0
SQL> alter system set statistics_level = basic scope=both;
 System altered.

SQL> show parameter statistics_level
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string BASIC

SQL> @?/rdbms/admin/catnoawr.sql
 SQL> alter system flush shared_pool;
 SQL> @?/rdbms/admin/catawr.sql
 SQL> @?/rdbms/admin/utlrp.sql
 sql> @?/rdbms/admin/execsvrm.sql

Remove the spfile created in the previous step and bounce the database with the existing initebsprod1.ora file

 SQL> show parameter sga_target
 NAME TYPE VALUE
 ———————————— ———– ——————————
 sga_target big integer 4G
SQL> show parameter statistics_level
 NAME TYPE VALUE
 ———————————— ———– ——————————
 statistics_level string TYPICAL
SQL> show parameter cluster_database;
 NAME TYPE VALUE
 ———————————— ———– ——————————
 cluster_database boolean TRUE
 cluster_database_instances integer 2
Please check invalid objects exists are not , if exists then please compile it manually
 To Create the AWR report, run below script
 sql> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
5) startup node2

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em AWR e marcado , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s