Início > Oracle > ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”

– Verificado que estava falhando um JOB em um ambiente SAP chamado BSLN_MAINTAIN_STATS_JOB, segue:

SQL> select * from DBA_SCHEDULER_JOBS where JOB_NAME=’BSLN_MAINTAIN_STATS_JOB’;

OWNER                          JOB_NAME                       JOB_SUBNAME                    JOB_STYLE   JOB_CREATOR
—————————— —————————— —————————— ———– ——————————
CLIENT_ID                                                        GLOBAL_UID
—————————————————————- ——————————–
SYS                            BSLN_MAINTAIN_STATS_JOB                                       REGULAR     SYS
SYS
BSLN_MAINTAIN_STATS_PROG
SYS
BSLN_MAINTAIN_STATS_SCHED
NAMED        25-DEC-11 12.00.00.000000 AM -02:00
DEFAULT_JOB_CLASS              TRUE  FALSE FALSE SCHEDULED                  3         17
5                        0 15-APR-12 12.00.00.882368 AM -02:00
+000000000 00:00:00.884789                                                  22-APR-12 12.00.00.900000 AM -02:00
OFF         FALSE TRUE
TRUE           1
NLS_LANGUAGE=’AMERICAN’ NLS_TERRITORY=’AMERICA’ NLS_CURRENCY=’$’ NLS_ISO_CURRENCY=’AMERICA’ NLS_NUMERIC_CHARACTERS=’.,’ NLS_CALENDAR=’GREGORIAN’ NLS_DATE_FORMAT=’DD-MON-RR’ NLS_DAT
E_LANGUAGE=’AMERICAN’ NLS_SORT=’BINARY’ NLS_TIME_FORMAT=’HH.MI.SSXFF AM’ NLS_TIMESTAMP_FORMAT=’DD-MON-RR HH.MI.SSXFF AM’ NLS_TIME_TZ_FORMAT=’HH.MI.SSXFF AM TZR’ NLS_TIMESTAMP_TZ_FO
RMAT=’DD-MON-RR HH.MI.SSXFF AM TZR’ NLS_DUAL_CURRENCY=’$’ NLS_COMP=’BINARY’ NLS_LENGTH_SEMANTICS=’BYTE’ NLS_NCHAR_CONV_EXCP=’FALSE’                                                                                                                                                    1                                                                          FALSE FALSE
Oracle defined automatic moving window baseline statistics computation job
21005364

– Erros gerados no Alert

Sat Apr 14 23:00:01 2012
Errors in file /oracle/Q01/saptrace/usertrace/diag/rdbms/q01/Q01/trace/Q01_j001_39387218.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Detalhes pode ser pego no trace gerado referente ao erro
/oracle/Q01/saptrace/usertrace/diag/rdbms/q01/Q01/trace/Q01_j001_39387218.trc

– Agora para resolvermos e verificarmos sua causa

Erro devido a tabela DBSNMP.BSLN_BASELINES contem informações inconsistentes, pois foi feito um clone do ambiente de produção para este ambiente de qualidade, então há registros na tabela DBSNMP.BSLN_BASELINES que está gerando conflito com as novas informações inseridas pelo ambiente de qualidade, neste caso o usuario DBSNMP precisa ser dropado e recriado, então vamos lá:

SQL> @?/rdbms/admin/catnsnmp.sql – Dropa o user

SQL> @?/rdbms/admin/catsnmp.sql – Cria o user

Bom então vamos lá, vamos dropar primeiramente:

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

User dropped.

drop role SNMPAGENT
*
ERROR at line 1:
ORA-01919: role ‘SNMPAGENT’ does not exist

Role dropped.

Role dropped.

Neste caso ao criar o usuario DBSNMP o ambiente está configurado a parte de segurança então não deixa criar o usuario com a senha igual ao user, neste caso eu criei antes o user, segue:

SQL> create user DBSNMP identified by dqm10vnc DEFAULT TABLESPACE SYSAUX PASSWORD EXPIRE ACCOUNT LOCK;

User created.

Agora vamos criar o user:

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

PL/SQL procedure successfully completed.

Role created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Role created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

PL/SQL procedure successfully completed.

create user DBSNMP identified by dbsnmp DEFAULT TABLESPACE SYSAUX PASSWORD EXPIRE ACCOUNT LOCK
*
ERROR at line 1:
ORA-01920: user name ‘DBSNMP’ conflicts with another user or role name

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Session altered.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Table created.

Sequence created.

Sequence created.

View created.

Package created.

Package body created.

No errors.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

drop type bsln_metric_set
*
ERROR at line 1:
ORA-04043: object BSLN_METRIC_SET does not exist

Type created.

Grant succeeded.

Type created.

Grant succeeded.

drop type bsln_variance_set
*
ERROR at line 1:
ORA-04043: object BSLN_VARIANCE_SET does not exist

Type created.

Grant succeeded.

Type created.

Grant succeeded.

drop type bsln_observation_set
*
ERROR at line 1:
ORA-04043: object BSLN_OBSERVATION_SET does not exist

Type created.

Grant succeeded.

Type created.

Grant succeeded.

drop type bsln_statistics_set
*
ERROR at line 1:
ORA-04043: object BSLN_STATISTICS_SET does not exist

Type created.

Grant succeeded.

Type created.

Grant succeeded.

Table created.

Comment created.

Grant succeeded.

Table created.

Comment created.

Grant succeeded.

Table created.

Comment created.

Grant succeeded.

Table created.

Comment created.

Grant succeeded.

Table created.

Comment created.

Grant succeeded.

Package created.

Synonym created.

Grant succeeded.

Package created.

Grant succeeded.

Package body created.

Package body created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

View created.

Comment created.

Grant succeeded.

View created.

Comment created.

Grant succeeded.

View created.

Comment created.

Grant succeeded.

View created.

Comment created.

Grant succeeded.

View created.

Comment created.

Grant succeeded.

View created.

Comment created.

Grant succeeded.

Table created.

Table created.

Table created.

Procedure created.

Grant succeeded.

Session altered.

create user APPQOSSYS identified by “APPQOSSYS”
*
ERROR at line 1:
ORA-01920: user name ‘APPQOSSYS’ conflicts with another user or role name

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

PL/SQL procedure successfully completed.

Session altered.

CREATE TABLE wlm_metrics_stream
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

CREATE TABLE wlm_classifier_plan
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

CREATE TABLE wlm_mpa_stream
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

CREATE TABLE wlm_violation_stream
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Synonym created.

Grant succeeded.

Synonym created.

Grant succeeded.

Synonym created.

Grant succeeded.

Synonym created.

Grant succeeded.

CREATE SYNONYM DBMS_WLM FOR SYS.DBMS_WLM
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Session altered.

Bom é isso, fiz isso e resolveu e voltou a executar o job normalmente.
abraço!

Categorias:Oracle
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

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

%d blogueiros gostam disto: