Sys scheması altında yer alan aşağıdaki job sürekli olarak failed olup alert loga da aşağıdaki hata satırlarını basması durumunda;
Job hata aldığında alert.log’ a aşağıdaki hata satırları düşer ;
Errors in file /u01/oracle/diag/rdbms/pdt/PDT/trace/pdt_j000_32733.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06550: line 1, column 807:
PLS-00201: identifier ‘DBSNMP.BSLN_INTERNAL’ must be declared
ORA-06550: line 1, column 807:
PL/SQL: Statement ignored
Oluşan trace logun içeriğine baktığımız da ;
Trace file /u01/oracle/diag/rdbms/pdt/PDT/trace/pdt_j000_32733.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/db/11.2.0
System name: Linux
Node name: pdtsrv
Release: 2.6.32-358.32.3.el6.x86_64
Version: #1 SMP Fri Jan 17 08:42:31 EST 2014
Machine: x86_64
Instance name: pdt
Redo thread mounted by this instance: 1
Oracle process number: 341
Unix process pid: 32733, image: oracle@pdtsrv (J000)
*** 2014-10-13 11:03:46.113
*** SESSION ID:(805.40459) 2014-10-13 11:03:46.113
*** CLIENT ID:() 2014-10-13 11:03:46.113
*** SERVICE NAME:(SYS$USERS) 2014-10-13 11:03:46.113
*** MODULE NAME:(DBMS_SCHEDULER) 2014-10-13 11:03:46.113
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2014-10-13 11:03:46.113
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06550: line 1, column 807:
PLS-00201: identifier ‘DBSNMP.BSLN_INTERNAL’ must be declared
ORA-06550: line 1, column 807:
PL/SQL: Statement ignored
Sorunu gidermek için ;
Önce bu job ‘ a ait logları kontrol edelim ;
SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;
LOG_DATE STATUS
————————————————————- —————————–
05-OCT-14 10.00.03.003858 AM +03:00 FAILED
13-OCT-14 08.34.46.282802 AM +03:00 FAILED
14-SEP-14 10.00.02.621196 AM +03:00 FAILED
12-OCT-14 10.00.02.890921 AM +03:00 FAILED
28-SEP-14 10.00.01.785845 AM +03:00 FAILED
21-SEP-14 10.00.01.811075 AM +03:00 FAILED
6 rows selected.
sürekli failed olduğunu gördük.Şimdi sırası ile, sys ile sisteme bağlanıyoruz;
SQL> sqlplus / as sysdba
— Drop the DBSNMP user by executing catnsnmp.sql script.
SQL>@E:\oracle\product\11.2.0\db\RDBMS\ADMIN\catnsnmp.sql
SQL>Rem
SQL>Rem NAME
SQL>Rem catnsnmp.sql
SQL>Rem FUNCTION
SQL>Rem Deletes the SNMPAgent role and DBSNMP user
SQL>Rem (Reverses catsnmp.sql)
SQL>Rem NOTES
SQL>Rem MODIFIED
SQL>Rem nachen 02/03/05 – add drop role OEM_ADVISOR
SQL>Rem dholail 04/12/99 –
> Rem
SQL>Rem OWNER
SQL>Rem ebosco
SQL>Rem
SQL>
SQL>
SQL>
SQL>drop user DBSNMP cascade;
User dropped.
SQL>
SQL>drop role SNMPAGENT;
drop role SNMPAGENT
*
ERROR at line 1:
ORA-01919: role ‘SNMPAGENT’ does not exist
SQL>
SQL>drop role OEM_MONITOR;
Role dropped.
SQL>
SQL>drop role OEM_ADVISOR;
Role dropped.
— Create the DBSNMP user by executing catsnmp.sql
SQL>@E:\oracle\product\11.2.0\db\RDBMS\ADMIN\catsnmp.sql
Outputu çok uzun olduğu için buraya ekleyemiyorum. Job’ ı şimdi manuel olarak tekrar run edelim ve sonucunu gözlemleyelim.
— Re-execute the job
SQL> exec dbms_scheduler.run_job(‘BSLN_MAINTAIN_STATS_JOB’,false);
PL/SQL procedure successfully completed.
Son durumda logları kontrol ettiğimizde ;
SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;
LOG_DATE STATUS
————————————————————————— ——————————
28-SEP-14 10.00.01.785845 AM +03:00 FAILED
12-OCT-14 10.00.02.890921 AM +03:00 FAILED
13-OCT-14 08.34.46.282802 AM +03:00 FAILED
21-SEP-14 10.00.01.811075 AM +03:00 FAILED
05-OCT-14 10.00.03.003858 AM +03:00 FAILED
14-SEP-14 10.00.02.621196 AM +03:00 FAILED
18-NOV-14 08.40.05.257070 AM +03:00 SUCCEEDED
Sorunun düzelmiş olduğunu görebiliriz.
