rodrigosilvaesilva – Página: 12 – Rodrigo Silva e Silva

Statspack Idle Events Data Guard: Timer, pman timer, Data Guard: Gap Manager, log file sync e CPU time

Publicado em 10 de outubro de 2018 by rodrigosilvaesilva

Ao analisar o Statspack me deparei com os seguintes eventos

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
Data Guard: Gap Manager                            180      10,800  60001   25.8
Data Guard: Timer                                   18      10,800 ######   25.8
pman timer                                       3,600      10,799   3000   25.8
CPU time                                                     6,565          15.7
db file parallel read                           11,095       1,329    120    3.2
          -------------------------------------------------------------

Top 5 Timed Events Avg %Total

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

Event Waits Time (s) (ms) Time

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

Data Guard: Gap Manager 180 10,800 60001 25.8

Data Guard: Timer 18 10,800 ###### 25.8

pman timer 3,600 10,799 3000 25.8

CPU time 6,565 15.7

db file parallel read 11,095 1,329 120 3.2

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

Causa Este evento está sendo investigado bug: Bug 28523746 Solução Adicionar as seguintes linhas de eventos ociosos manualmente Oracle 12.2

insert into STATS$IDLE_EVENT (event) values ('ASM cluster membership changes');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Comm SGA setup wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Servlet wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Surrogate wait');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting for work');
insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting restore start');
insert into STATS$IDLE_EVENT (event) values ('DNFS disp IO slave idle');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: Gap Manager');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: Timer');
insert into STATS$IDLE_EVENT (event) values ('Data Guard: controlfile update');
insert into STATS$IDLE_EVENT (event) values ('IMFS defer writes scheduler');
insert into STATS$IDLE_EVENT (event) values ('LMS CR slave timer');
insert into STATS$IDLE_EVENT (event) values ('OFS Receive Queue');
insert into STATS$IDLE_EVENT (event) values ('PL/SQL lock timer');
insert into STATS$IDLE_EVENT (event) values ('SCM slave idle');
insert into STATS$IDLE_EVENT (event) values ('Sharded  Queues : Part Truncate idle');
insert into STATS$IDLE_EVENT (event) values ('Streams AQ: opt idle');
insert into STATS$IDLE_EVENT (event) values ('block compare coord process idle');
insert into STATS$IDLE_EVENT (event) values ('gopp msg');
insert into STATS$IDLE_EVENT (event) values ('nologging fetch slave idle');
insert into STATS$IDLE_EVENT (event) values ('pman timer');
insert into STATS$IDLE_EVENT (event) values ('recovery coordinator idle');
insert into STATS$IDLE_EVENT (event) values ('recovery logmerger idle');
insert into STATS$IDLE_EVENT (event) values ('recovery receiver idle');
insert into STATS$IDLE_EVENT (event) values ('recovery sender idle');
commit;

insert into STATS$IDLE_EVENT (event) values ('ASM cluster membership changes');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Comm SGA setup wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Servlet wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance Surrogate wait');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting for work');

insert into STATS$IDLE_EVENT (event) values ('Backup Appliance waiting restore start');

insert into STATS$IDLE_EVENT (event) values ('DNFS disp IO slave idle');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: Gap Manager');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: Timer');

insert into STATS$IDLE_EVENT (event) values ('Data Guard: controlfile update');

insert into STATS$IDLE_EVENT (event) values ('IMFS defer writes scheduler');

insert into STATS$IDLE_EVENT (event) values ('LMS CR slave timer');

insert into STATS$IDLE_EVENT (event) values ('OFS Receive Queue');

insert into STATS$IDLE_EVENT (event) values ('PL/SQL lock timer');

insert into STATS$IDLE_EVENT (event) values ('SCM slave idle');

insert into STATS$IDLE_EVENT (event) values ('Sharded Queues : Part Truncate idle');

insert into STATS$IDLE_EVENT (event) values ('Streams AQ: opt idle');

insert into STATS$IDLE_EVENT (event) values ('block compare coord process idle');

insert into STATS$IDLE_EVENT (event) values ('gopp msg');

insert into STATS$IDLE_EVENT (event) values ('nologging fetch slave idle');

insert into STATS$IDLE_EVENT (event) values ('pman timer');

insert into STATS$IDLE_EVENT (event) values ('recovery coordinator idle');

insert into STATS$IDLE_EVENT (event) values ('recovery logmerger idle');

insert into STATS$IDLE_EVENT (event) values ('recovery receiver idle');

insert into STATS$IDLE_EVENT (event) values ('recovery sender idle');

commit;

Oracle 18.8

insert into STATS$IDLE_EVENT (event) values ('DBWR timer');
insert into STATS$IDLE_EVENT (event) values ('LogMiner merger: idle');
insert into STATS$IDLE_EVENT (event) values ('memoptimize write drain idle');
commit;

insert into STATS$IDLE_EVENT (event) values ('DBWR timer');

insert into STATS$IDLE_EVENT (event) values ('LogMiner merger: idle');

insert into STATS$IDLE_EVENT (event) values ('memoptimize write drain idle');

commit;

Fonte: Oracle Support

ERROR ORA-01555

Publicado em 9 de outubro de 2018 by rodrigosilvaesilva

Hoje um cliente reportou o seguinte erro ORA-01555 segue abaixo: Analisando verifiquei que o UNDO está em 900 por padrão.

 SYS@ORCL > show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

SYS@ORCL > show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 900

Alterando para 3600

SYS@ORCL> alter system set undo_retention=3600 scope=both;

System altered.

SYS@ORCL> alter system set undo_retention=3600 scope=both;

System altered.

Verificando parameter undo_retention

SYS@ORCL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     3600

SYS@ORCL> show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 3600

Em conversa com o cliente o mesmo estava realizando cargas de campanhas.

Replicação AD Site Primário para Site Remoto

Publicado em 9 de outubro de 2018 by rodrigosilvaesilva

Forçando replicação AD primário para os demais sites remotos.

repadmin /syncall /e /A /P /d /q

1	repadmin /syncall /e /A /P /d /q

/syncall: Sincroniza o domain controller principal com todos os parceiros de replicação; /e: Enterprise… indica que a replicação será estendida a todos os sites remotos; /A: All; Sincroniza todas as partições do home server; /P: Indica que as mudanças serão replicadas a partir do servidor…

Erro impdp ORA-39168: Object path STATISTICS was not found

Publicado em 27 de setembro de 2018 by rodrigosilvaesilva

Erro

[oracle@bdora bkp_logico]$ time impdp system/password schemas=ORCL directory=BKPDIR dumpfile=bkp2509.dmp logfile=imp2509.log EXCLUDE=STATISTICS

Import: Release 12.2.0.1.0 - Production on Thu Sep 27 16:28:19 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39168: Object path STATISTICS was not found.


real    0m22.293s
user    0m0.037s
sys     0m0.041s

[oracle@bdora bkp_logico]$ time impdp system/password schemas=ORCL directory=BKPDIR dumpfile=bkp2509.dmp logfile=imp2509.log EXCLUDE=STATISTICS

Import: Release 12.2.0.1.0 - Production on Thu Sep 27 16:28:19 2018

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

ORA-39002: invalid operation

ORA-39168: Object path STATISTICS was not found.

real 0m22.293s

user 0m0.037s

sys 0m0.041s

Causa

Iniciando "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=BKPDIR dumpfile=bkp2509.dmp logfile=bkp2509.log exclude=statistics full=y reuse_dumpfiles=y

1	Iniciando "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=BKPDIR dumpfile=bkp2509.dmp logfile=bkp2509.log exclude=statistics full=y reuse_dumpfiles=y

Analisando o export foi utilizado o parâmetro EXCLUDE=STATISTICS Solução Removido o parâmetro EXCLUDE=STATISTICS da rotina de import. Import realizado com sucesso.

Erro ORA-39002, ORA-31694, ORA-02354, ORA-39773 ao realizar impdp

Publicado em 27 de setembro de 2018 by rodrigosilvaesilva

Erro abaixo ao executar o impdp

ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39773: parse of metadata stream failed

ORA-39002: invalid operation

ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" failed to load/unload

ORA-02354: error in exporting/importing data

ORA-39773: parse of metadata stream failed

Causa Analisando o .dmp identifiquei que o mesmo estava corrompido. Provavelmente ocorreu falha ao gerar o binário Solução realizado um novo expdp e impdp com sucesso!

← Posts recentes Posts antigos →