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!
Vitor Eduardo 