Oracle – Página: 7 – Rodrigo Silva e Silva

Oracle – Statspack Idle events (LGWR worker group idle CPU time heartbeat redo informer lreg timer AQPC idle)

Publicado em 7 de março de 2019 by rodrigosilvaesilva

Após instalar o Statspack Oracle 11.2.0.4 me deparei com os seguintes eventos

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle                          12,929        41.0
CPU time                                            12       6,923 ######   22.0
heartbeat redo informer                          3,604       3,604   1000   11.4
lreg timer                                       1,201       3,607   3003   11.4
AQPC idle                                          120       3,601  30008   11.4

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

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

LGWR worker group idle 12,929 41.0

CPU time 12 6,923 ###### 22.0

heartbeat redo informer 3,604 3,604 1000 11.4

lreg timer 1,201 3,607 3003 11.4

AQPC idle 120 3,601 30008 11.4

Resolvendo Atualização tabela de eventos Statspack

SQL> 
delete from STATS$IDLE_EVENT;
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';
insert into STATS$IDLE_EVENT values('log file parallel write');
insert into STATS$IDLE_EVENT values('target log write size');
commit;

SQL>

delete from STATS$IDLE_EVENT;

insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';

insert into STATS$IDLE_EVENT values('log file parallel write');

insert into STATS$IDLE_EVENT values('target log write size');

commit;

Statspack ERRO ORA-20200 , ORA-06512

Publicado em 31 de dezembro de 2018 by rodrigosilvaesilva

Erro ao gerar report statspack ORA-20200: Database/Instance 2251672657/1 does not exist in STATS$DATABASE_INSTANCE ORA-06512: at line 23

11:47:51 PERFSTAT@ORCL > @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2251672657 ORCL               1  ORCL

Elapsed: 00:00:00.01


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 2251672657 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 2251672657/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23


Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

11:47:51 PERFSTAT@ORCL > @?/rdbms/admin/spreport

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

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

2251672657 ORCL 1 ORCL

Elapsed: 00:00:00.01

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 2251672657 for database Id

Using 1 for instance number

declare

ERROR at line 1:

ORA-20200: Database/Instance 2251672657/1 does not exist in

STATS$DATABASE_INSTANCE

ORA-06512: at line 23

Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Causa. Erro gerado após o statspack instalado. Solução. Aguardar gerar o Snap Automático ou gerar manualmente.

12:01:52 SYS@ORCL > @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2251672657  ORCL              1 ORCL 

Elapsed: 00:00:00.01


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 2251672657        1 ORCL        ORCL        bdora-01.
                                               local

Using 2251672657 for database Id
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.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL        ORCL                1 31 Dec 2018 12:00     5

12:01:52 SYS@ORCL > @?/rdbms/admin/spreport

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

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

2251672657 ORCL 1 ORCL

Elapsed: 00:00:00.01

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

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

2251672657 1 ORCL ORCL bdora-01.

local

Using 2251672657 for database Id

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.

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

ORCL ORCL 1 31 Dec 2018 12:00 5

ERRO ORA-16179: incremental changes to “log_archive_dest_1” not allowed with SPFILE

Publicado em 30 de dezembro de 2018 by rodrigosilvaesilva

Erro ao configurar o “log_archive_dest_1” Verificando o parâmetro log_archive_dest_1

10:34:01 SYS@ORCL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string

10:34:01 SYS@ORCL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string

Erro ORA-16179 ao definir o parâmetro.

10:34:10 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/';
ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


Elapsed: 00:00:00.00

10:34:10 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/';

ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/'

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Elapsed: 00:00:00.00

Solução. Adicionar o LOCATION

10:34:43 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog/';

System altered.

Elapsed: 00:00:00.01

10:34:43 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog/';

System altered.

Elapsed: 00:00:00.01

Verificando o parâmetro

SYS@ORCL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u02/fra/ORCL/archi
                                                 velog/

SYS@ORCL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string LOCATION=/u02/fra/ORCL/archi

velog/

Adicionando novo DATAFILE

Publicado em 1 de novembro de 2018 by rodrigosilvaesilva

Verificando tamanho total datafile

select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
from dba_data_files
group by tablespace_name
order by sum(bytes);

TABLESPACE_NAME                TAMANHO(MB)
------------------------------ -----------
USERS                                    5
INTRANET                               100
NFELOB                                 200
RHMETA_INDEX                           600
NFEINDICES                             900
SYSAUX                                1450
NFEDADOS                              1600
SYSTEM                                2010
ORCLINDICES                           2700
REORG_LOB                             7400
TOOLS                                 8300
UNDOTBS1                              8405
REORG_DADOS                          12800
RHMETA                          32767.9844
REORG_INDICES                        42600
ORCLDADOS                           141300

16 rows selected.

select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"

from dba_data_files

group by tablespace_name

order by sum(bytes);

TABLESPACE_NAME TAMANHO(MB)

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

USERS 5

INTRANET 100

NFELOB 200

RHMETA_INDEX 600

NFEINDICES 900

SYSAUX 1450

NFEDADOS 1600

SYSTEM 2010

ORCLINDICES 2700

REORG_LOB 7400

TOOLS 8300

UNDOTBS1 8405

REORG_DADOS 12800

RHMETA 32767.9844

REORG_INDICES 42600

ORCLDADOS 141300

16 rows selected.

Verificando espaço disponível DATAFILES

select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
from dba_free_space
group by tablespace_name
order by sum(bytes);

TABLESPACE_NAME                TAMANHO(MB)
------------------------------ -----------
SYSTEM                               .1875
USERS                               3.0625
NFEINDICES                              56
RHMETA                             60.0625
SYSAUX                             76.4375
RHMETA_INDEX                       77.3125
NFEDADOS                           86.0625
NFELOB                              88.375
INTRANET                            94.875
ORCLINDICES                         220.75
REORG_LOB                            403.5
TOOLS                               419.25
REORG_DADOS                        669.125
REORG_INDICES                    2053.6875
ORCLDADOS                         3503.375
UNDOTBS1                           8043.25

16 rows selected.

select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"

from dba_free_space

group by tablespace_name

order by sum(bytes);

TABLESPACE_NAME TAMANHO(MB)

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

SYSTEM .1875

USERS 3.0625

NFEINDICES 56

RHMETA 60.0625

SYSAUX 76.4375

RHMETA_INDEX 77.3125

NFEDADOS 86.0625

NFELOB 88.375

INTRANET 94.875

ORCLINDICES 220.75

REORG_LOB 403.5

TOOLS 419.25

REORG_DADOS 669.125

REORG_INDICES 2053.6875

ORCLDADOS 3503.375

UNDOTBS1 8043.25

16 rows selected.

Adicionando DATAFILE

SYS@ORCL > ALTER TABLESPACE RHMETA ADD DATAFILE '/u01/oracle/oradata/ORCL/tbs_rhmeta_02.dbf' size 100m AUTOEXTEND ON;

Tablespace altered.

SYS@ORCL > ALTER TABLESPACE RHMETA ADD DATAFILE '/u01/oracle/oradata/ORCL/tbs_rhmeta_02.dbf' size 100m AUTOEXTEND ON;

Tablespace altered.

Verificando novamente espaço disponível DATAFILES

SYS@ORCL >
select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
from dba_free_space
group by tablespace_name
order by sum(bytes);

TABLESPACE_NAME                TAMANHO(MB)
------------------------------ -----------
SYSTEM                               .1875
USERS                               3.0625
NFEINDICES                              56
SYSAUX                             76.4375
RHMETA_INDEX                       77.3125
NFEDADOS                           86.0625
NFELOB                              88.375
INTRANET                            94.875
ORCLINDICES                         220.75
REORG_LOB                            403.5
TOOLS                               419.25
REORG_DADOS                        669.125
RHMETA                           1695.0625
REORG_INDICES                    1989.6875
ORCLDADOS                         3503.375
UNDOTBS1                           8113.25

16 rows selected.

SYS@ORCL >

select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"

from dba_free_space

group by tablespace_name

order by sum(bytes);

TABLESPACE_NAME TAMANHO(MB)

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

SYSTEM .1875

USERS 3.0625

NFEINDICES 56

SYSAUX 76.4375

RHMETA_INDEX 77.3125

NFEDADOS 86.0625

NFELOB 88.375

INTRANET 94.875

ORCLINDICES 220.75

REORG_LOB 403.5

TOOLS 419.25

REORG_DADOS 669.125

RHMETA 1695.0625

REORG_INDICES 1989.6875

ORCLDADOS 3503.375

UNDOTBS1 8113.25

16 rows selected.

Alterar local ARCHIVELOG

Publicado em 1 de novembro de 2018 by rodrigosilvaesilva

Verificando local archivelog

SYS@ORCL > show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string

SYS@ORCL > show parameter log_archive_dest_1

NAME TYPE VALUE

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

log_archive_dest_1 string

Alterando local archivelog

SYS@ORCL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog' scope=both;

System altered.

Elapsed: 00:00:00.04

SYS@ORCL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog' scope=both;

System altered.

Elapsed: 00:00:00.04

Verificando local archivelog

SYS@ORCL > show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u02/fra/ORCL/archivelog

SYS@ORCL > show parameter log_archive_dest_1

NAME TYPE VALUE

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

log_archive_dest_1 string LOCATION=/u02/fra/ORCL/archivelog

← Posts recentes Posts antigos →